PostgreSQL忽略timestamp列上的索引 [英] PostgreSQL ignoring index on timestamp column
问题描述
我创建了以下表和索引:
I have the following table and index created:
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);
但是,当我使用timestamp字段执行select时,将忽略索引并且我的查询需要将近10秒钟返回:
However, when I perform a select using the timestamp field, the index is being ignored and my query takes almost 10 seconds to return:
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))
推荐答案
如果有很多结果,btree可能比只进行表扫描要慢。 btree索引真的不是为你在这里做的那种范围选择类型的查询而设计的;条目放在一个大的未排序文件中,索引是针对该未排序的组构建的,因此每个结果可能需要在btree中找到磁盘后查找。当然,btree可以按顺序轻松读取,但结果仍然需要从磁盘中提取。
If there are a lot of results, the btree can be slower than just doing a table scan. btree indices are really not designed for this kind of "range-selection" kind of query you're doing here; the entries are placed in a big unsorted file and the index is built against that unsorted group, so every result potentially requires a disk seek after it is found in the btree. Sure, the btree can be easily read in order but the results still need to get pulled from the disk.
群集索引通过根据btree中的内容排序实际数据库记录来解决此问题,因此它们实际上对远程查询有帮助喜欢这个。请考虑使用聚集索引,看看它是如何工作的。
Clustered indices solve this problem by ordering the actual database records according to what's in the btree, so they actually are helpful for ranged queries like this. Consider using a clustered index instead and see how it works.
这篇关于PostgreSQL忽略timestamp列上的索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!