我正在使用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
尝试使用
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的当前统计信息来选择适当的查询计划.
