PostgreSQL索引不用于范围查询

发布时间：2020-12-13 16:30:34 所属栏目：百科来源：网络整理

导读：我正在使用PostgreSQL(9.2.0)并拥有一个IP范围表.这是SQL： CREATE TABLE ips( id serial NOT NULL,begin_ip_num bigint,end_ip_num bigint,country_name character varying(255),CONSTRAINT ips_pkey PRIMARY KEY (id )) 我在begin_ip_num和end_ip_num上添

我正在使用PostgreSQL(9.2.0)并拥有一个IP范围表.这是SQL：

CREATE TABLE ips
(
  id serial NOT NULL,begin_ip_num bigint,end_ip_num bigint,country_name character varying(255),CONSTRAINT ips_pkey PRIMARY KEY (id )
)

我在begin_ip_num和end_ip_num上添加了索引：

CREATE INDEX index_ips_on_begin_ip_num
  ON ips
  USING btree
  (begin_ip_num );

CREATE INDEX index_ips_on_end_ip_num
  ON ips
  USING btree
  (end_ip_num );

使用的查询是：

SELECT "ips".* FROM "ips" WHERE (3065106743 BETWEEN begin_ip_num AND end_ip_num);

问题是我的BETWEEN查询仅使用begin_ip_num上的索引.使用索引后,它使用end_ip_num过滤结果.这是EXPLAIN ANALYZE结果：

Index Scan using index_ips_on_begin_ip_num on ips  (cost=0.00..2173.83 rows=27136 width=76) (actual time=16.349..16.350 rows=1 loops=1)
Index Cond: (3065106743::bigint >= begin_ip_num)
Filter: (3065106743::bigint <= end_ip_num)
Rows Removed by Filter: 47596
Total runtime: 16.425 ms

我已经尝试了各种索引组合,包括在begin_ip_num和end_ip_num上添加复合索引.

尝试使用 multicolumn index,但第二列的顺序相反：

CREATE INDEX index_ips_begin_end_ip_num ON ips (begin_ip_num,end_ip_num DESC);

对于单列索引,排序大多无关紧要,因为它几乎可以快速向后扫描.但它对多列索引很重要.

根据我提出的索引,Postgres可以扫描第一列并找到地址,其中索引的其余部分满足第一个条件.然后,对于第一列的每个值,它可以返回满足第二个条件的所有行,直到第一个列失败.然后跳转到第一列的下一个值,等等.
这仍然不是很有效,Postgres可能更快,只需扫描第一个索引列并过滤第二个索引列.很大程度上取决于您的数据分布.

真正有用的是GiST index for a int8range column,自PostgreSQL 9.2起可用.

除此之外,你可以看看这个closely related answer on dba.SE与一个相当复杂的制度与部分索引.高级的东西,但它提供了很好的性能.

无论哪种方式,CLUSTER使用上面的多列索引可以帮助提高性能：

CLUSTER ips USING index_ips_begin_end_ip_num

这样,满足您的第一个条件的候选人被打包到相同或相邻的数据页面上.如果第一列的每个值有很多行,可以帮助提高性能.否则它几乎没有效果.

此外,是autovacuum正在运行还是你在桌面上运行ANALYZE？您需要Postgres的当前统计信息来选择适当的查询计划.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!