PostgreSQL索引未用于IP范围查询

Question

PostgreSQL索引未用于IP范围查询

postgresqlindexingdatabase-designrangegist-index

10

我正在使用 PostgreSQL 9.2，并有一个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上添加了普通的B树索引：

CREATE INDEX index_ips_on_begin_ip_num ON ips (begin_ip_num);
CREATE INDEX index_ips_on_end_ip_num ON ips (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 上添加复合指数。

- Zain Zafar

如果IP范围对数据库的目标业务有重大影响，那么您应该考虑使用ip4r扩展。 - Clodoaldo Neto

4个回答

7

我在maxmind.com的免费地理位置表中遇到了与此几乎相同的问题。我采用了Erwin的技巧使用范围类型和GiST索引解决了这个问题。GiST索引是关键。如果没有它，我最多每秒查询约3行。有了它，我在不到10秒钟内查询了近50万行！由于Erwin没有发布如何做到这一点的详细说明，因此我想在这里添加它们...

首先，必须添加一个新列，它具有范围类型，注意bigint类型需要int8range。接下来，适当设置其值，'[]'参数指示在下限和上限处使范围包含（参见文档）。最后添加索引，注意GiST索引是所有性能优势的来源。

alter table ips add column iprange int8range;
update ips set iprange=int8range(begin_ip_num, end_ip_num, '[]');
create index index_ips_on_iprange on ips using gist (iprange);

在打好基础之后，您现在可以使用'<@'包含运算符来针对表格搜索特定的地址。请参见http://www.postgresql.org/docs/9.2/static/functions-range.html

SELECT "ips".* FROM "ips" WHERE (3065106743::bigint <@ iprange);

- pbnelson

这个很好用！但你需要将整数转换为bigint（我得到了“没有匹配给定名称和参数类型的操作员。您可能需要添加显式类型转换。”）从 "ips" 中选择 "ips".* WHERE (cast(3065106743 as bigint) <@ iprange); - Adam Berlinsky-Schine

谢谢，Adam。好主意。我按照你的建议添加了一个 bigint 转换，但使用了 ::type "语法糖"，因为它更加简洁。 - pbnelson

5

我有点晚来到这个聚会，但这对我非常有效。

考虑安装ip4r扩展程序。它基本上允许您定义一个可以容纳IP范围的列。扩展程序的名称意味着它仅适用于IPv4，但目前也支持IPv6。

在您使用该列填充表格后，您只需要创建GIST索引：

CREATE INDEX ip_zip_ip4_range ON ip_zip USING gist (ip4_range);

我数据库中有近1000万个范围，但查询只需要几毫秒的时间：

region=> select count(*) from ip_zip ;

  count  
---------
 9566133

region=> explain analyze select * from ip_zip where '8.8.8.8'::ip4 <<= ip4_range;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on ip_zip  (cost=234.55..25681.29 rows=9566 width=22) (actual time=0.085..0.086 rows=1 loops=1)
   Recheck Cond: ('8.8.8.8'::ip4r <<= ip4_range)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on ip_zip_ip4_range  (cost=0.00..232.16 rows=9566 width=0) (actual time=0.055..0.055 rows=1 loops=1)
         Index Cond: ('8.8.8.8'::ip4r <<= ip4_range)
 Planning time: 0.106 ms
 Execution time: 0.118 ms
(7 rows)

region=> explain analyze select * from ip_zip where '254.50.22.54'::ip4 <<= ip4_range;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on ip_zip  (cost=234.55..25681.29 rows=9566 width=22) (actual time=0.059..0.059 rows=1 loops=1)
   Recheck Cond: ('254.50.22.54'::ip4r <<= ip4_range)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on ip_zip_ip4_range  (cost=0.00..232.16 rows=9566 width=0) (actual time=0.048..0.048 rows=1 loops=1)
         Index Cond: ('254.50.22.54'::ip4r <<= ip4_range)
 Planning time: 0.102 ms
 Execution time: 0.145 ms
(7 rows)

- Derek

0

我认为你的查询看起来像这样：WHERE [constant] BETWEEN begin_ip_num AND end_ipnum 或者

据我所知，Postgres没有“AND-EQUAL”访问计划，因此你需要像Erwin Brandstetter建议的那样在两个列上添加一个组合索引。

- a1ex07

我尝试使用复合索引，但结果是顺序扫描。 - Zain Zafar

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

尝试使用多列索引，但是在第二列上反转排序顺序。

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

对于单列索引来说，排序通常不太重要，因为它可以快速地反向扫描。但是对于多列索引来说，排序很重要。

使用我提出的索引，Postgres 可以扫描第一列并找到地址，在这个地址后面的索引满足第一个条件。然后对于每个第一列的值，返回所有满足第二个条件的行，直到第一个条件不满足为止。然后跳转到下一个第一列的值，以此类推。
这仍然不太高效，并且在数据分布上非常依赖。Postgres 可能只需扫描第一个索引列并过滤第二个索引，从而更快。

无论如何，使用上面提到的多列索引，CLUSTER 可以提高性能：

CLUSTER ips USING index_ips_begin_end_ip_num

这种方式可以让符合第一个条件的候选人被打包到同一个或相邻的数据页中。如果第一列的每个值都有很多行，这可以大大提高性能。否则，它几乎没有效果。
（也有非阻塞的外部工具可用于此目的：pg_repack 或 pg_squeeze。）

另外，请确认自动清理是否正在运行并已正确配置，或者是否已对表运行了ANALYZE？Postgres需要当前的统计信息来选择适当的查询计划。

在这里真正有用的是将一个int8range列使用GiST索引，从PostgreSQL 9.2开始可用。请参见：

优化时间戳范围（两列）的查询

如果您的IP范围可以使用其中一个内置的网络类型inet或cidr来覆盖，请考虑替换您的两个bigint列。或者更好的是，查看Andrew Gierth的附加模块ip4r（不在标准分发中）。索引策略也会相应地改变。

如果没有这样的情况，您可以查看dba.SE上使用部分索引的复杂方案的相关答案。高级内容，但它可以提供出色的性能：

空间索引是否有助于“范围-排序-限制”查询？