sql - 加快检查 CIDR 范围内的 IP 地址成员资格，适用于大型数据集

Question

在 Postgres 数据库中，我需要过滤表 A 中的一组数十万行，方法是仅包括那些行中的 IP 地址列(inet 类型)与数千个 IP 地址 block 中的任何一个相匹配的行(cidr 类型)在另一个表 B 中。我在第一个表中的 inet 地址和第二个表中的 cidr 范围上尝试了各种索引，但无论我做什么，规划器都会进行嵌套顺序扫描，应用 << 运算符到每对 IP 地址和前缀。

有没有办法通过索引或其他巧妙的技巧来加快速度？ (我可以求助于外部过程脚本，但我想知道它在 Postgres 中是否可行。)

谢谢!

Answer 1

这是一个老问题，但在 Google 搜索结果中很突出，所以在这里发表我的 2 美分:

在 Postgres 9.4 及更高版本中，您可以为 inet 和 cidr 使用 GIST 索引:https://www.postgresql.org/docs/current/static/gist-builtin-opclasses.html

例如以下查询将使用要点索引(假设来自 MaxMind 的免费数据集的表):

create index on geolite2_city_ipv4_block using gist (network inet_ops);

select * from geolite2_city_ipv4_block where network >>= '8.8.8.8';