PostgreSQL忽略时间戳列上的索引

Question

我已经创建了以下表格和索引：

CREATE TABLE cdc_auth_user
(
  cdc_auth_user_id bigint NOT NULL DEFAULT nextval('cdc_auth_user_id_seq'::regclass),
  cdc_timestamp timestamp without time zone DEFAULT ('now'::text)::timestamp without time zone,
  cdc_operation text,
  id integer,
  username character varying(30)
);

CREATE INDEX idx_cdc_auth_user_cdc_timestamp
          ON cdc_auth_user
       USING btree (cdc_timestamp);

然而，当我使用时间戳字段进行选择时，索引被忽略了，我的查询需要近10秒才能返回：

EXPLAIN SELECT *
          FROM cdc_auth_user
         WHERE cdc_timestamp BETWEEN '1900/02/24 12:12:34.818'
                             AND '2012/02/24 12:17:45.963';


Seq Scan on cdc_auth_user  (cost=0.00..1089.05 rows=30003 width=126)
  Filter: ((cdc_timestamp >= '1900-02-24 12:12:34.818'::timestamp without time zone) AND (cdc_timestamp <= '2012-02-24 12:17:45.963'::timestamp without time zone))

- exodar

---

你的表里有多少条数据？这可能是因为使用索引而不是表扫描会增加成本。 - Luc M

Answer 1

如果结果很多，B树可能比仅进行表扫描要慢。B树索引并不是为这种“范围选择”查询设计的；条目被放置在一个大的未排序文件中，索引建立在该未排序组上，因此每个结果在找到它后都可能需要磁盘查找。当然，B树可以很容易地按顺序读取，但结果仍然需要从磁盘中提取出来。 聚集索引通过根据B树中的内容对实际数据库记录进行排序来解决这个问题，因此它们实际上有助于像这样的范围查询。考虑使用聚集索引来看看它的效果如何。

- Billy ONeal

---

PostgreSQL中的CLUSTER命令与“聚集索引”只有松散的关联。该命令的作用是根据索引的排序方式重新排列表的行。它不会改变索引的任何内容，而且这是一次性操作。表格与索引没有任何联系。后续的写入将像往常一样进行。CLUSTER仍然非常有用。 - Erwin Brandstetter

@Edwin：嗯...那很烦人。在PostgreSQL中是否有任何类型的聚集B树索引？ - Billy ONeal

PostgreSQL没有像Oracle那样的“聚集索引”。但是，如果您需要该功能并定期在cronjob中安排CLUSTER或由某些事件触发，则在大多数情况下应该具有大部分好处。例外情况是写入频繁的表，在这种情况下，您没有好的方法来决定何时重新进行聚集。还有clusterdb（http://www.postgresql.org/docs/current/interactive/app-clusterdb.html）shell实用程序。准确地说：表保存了使用哪个索引进行下一个CLUSTER的信息，而没有参数。 - Erwin Brandstetter

@ErwinBrandstetter：我不考虑Oracle；我在考虑MSSQL或MySQL。:叹气:我很惊讶它不支持这种事情。 - Billy ONeal