我试图优化以下原始模式的 IP地址地理位置查询（数据集是来自https://ipinfo.io/免费试用）：
CREATE TABLE raw_geolocations (
start_ip INET NOT NULL,
end_ip INET NOT NULL,
join_key CIDR NOT NULL,
city TEXT NOT NULL,
region TEXT,
country TEXT NOT NULL,
lat NUMERIC NOT NULL,
lng NUMERIC NOT NULL,
postal TEXT,
timezone TEXT NOT NULL
);
只是查询：
select *
from unnest(array[
inet '<ip_address_string>'
]) ip_address
left join raw_geolocations g on ip_address between start_ip and end_ip;
非常慢（对于单个 ip 地址查询，在50s和120s之间）。
因此，我遵循“指南”here并将raw_geolocations迁移到一个名为ip_geolocations的新表，该表在ip_segment字段上创建了gist索引：
create type iprange as range (subtype=inet);
create table ip_geolocations(
id bigserial primary key not null,
ip_segment iprange not null,
join_key cidr not null,
city TEXT NOT NULL,
region TEXT,
country TEXT NOT NULL,
lat NUMERIC NOT NULL,
lng NUMERIC NOT NULL,
postal TEXT,
timezone TEXT NOT NULL
);
insert into ip_geolocations(ip_segment, join_key, city, region, country, lat, lng, postal, timezone)
select iprange(start_ip, end_ip, '[]'), join_key, city, region, country, lat, lng, postal, timezone
from raw_geolocations;
create index gist_idx_ip_geolocations_ip_segment on ip_geolocations USING gist (ip_segment);
这对于单个 ip 地址查询非常有用，使执行时间介于20ms和200ms之间，但是对于批量查询来说相当慢，一次大约需要 100 个 ip 地址的1.5s到3s。
示例查询：
select *
from unnest(array[
inet '<ip_address_string>'
]) ip_address
left join ip_geolocations g on g.ip_segment @> ip_address;
一些问题，因为我潜入其他优化：
然后，我决定查看gin索引，但在运行以下命令时遇到以下 postgres 错误：
create index test_idx_geolocations on ip_geolocations using gin (ip_segment);
ERROR: operator cl "inet_ops" does not exist for access method "gin"
Time: 2.173 ms
我很难理解如何使用inet_ops作为我定义的iprange类型甚至inet和cidr类型的运算符类的说明。此外，我试图定义自己的运算符类无济于事：
create operator cl gin_iprange_ops default for type iprange
using gin as
OPERATOR 7 @> (iprange, inet),
OPERATOR 8 <@ (inet, iprange);
ERROR: operator does not exist: iprange @> inet
这个错误对我来说没有多大意义，因为上面的示例查询与@>运算符一起使用。
虽然被阻止，我决定看看是否只是玩 postgres 配置会改善查询时间，所以我做了以下更改：
alter table ip_geolocations set (parallel_workers = 4);
set max_parallel_maintenance_workers to 4;
set maintenance_work_mem to '1 GB';
这似乎非线性地改善了索引创建时间，但在查询执行时间方面没有任何明显的影响。
最后，还有其他类似形式的 IP地址表：
CREATE TABLE raw_<other_table> (
start_ip INET NOT NULL,
end_ip INET NOT NULL,
join_key CIDR NOT NULL,
... <other_fields>
);
我对这些其他表（其中的3）执行了一组类似的步骤，并将它们迁移到具有iprange字段和gist索引的ip_<other_table>表。
不幸的是，在join_key上连接这些表仍然非常慢。
任何帮助都非常感谢。我对 IP地址查询（范围包含查询），自定义运算符类，索引类型（gist和gin）的世界非常陌生，所以任何可能帮助我的指针或文档都会很棒。
查询计划，我只是检查ip_address在ip_segment: iprange范围内：
explain yze select *
from unnest(array[
inet '98.237.137.99'
]) ip_address
left join ip_geolocations g on g.ip_segment @> ip_address
left join ip_companies c on c.ip_segment @> ip_address
left join ip_carriers cr on cr.ip_segment @> ip_address
left join ip_privacy_detections pd on pd.ip_segment @> ip_address;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=1930.48..7972624532924773.00 rows=931561551811655 width=412) (actual time=562.324..589.083 rows=1 loops=1)
-> Nested Loop Left Join (cost=23.78..1160195946065.69 rows=116337334725 width=356) (actual time=287.962..314.719 rows=1 loops=1)
-> Nested Loop Left Join (cost=0.84..30592828311.99 rows=1694915933 width=293) (actual time=282.013..308.768 rows=1 loops=1)
-> Nested Loop Left Join (cost=0.42..515233.65 rows=27965 width=179) (actual time=270.532..297.172 rows=1 loops=1)
-> Function Scan on unnest ip_address (cost=0.00..0.01 rows=1 width=32) (actual time=0.006..0.008 rows=1 loops=1)
-> Index Scan using idx_ip_companies_ip_segment on ip_companies c (cost=0.42..513835.38 rows=139826 width=147) (actual time=270.519..297.157 rows=1 loops=1)
Index Cond: (ip_segment @> ip_address.ip_address)
-> Index Scan using idx_ip_geolocations_ip_segment on ip_geolocations g (cost=0.42..1090919.64 rows=303041 width=114) (actual time=11.474..11.588 rows=1 loops=1)
Index Cond: (ip_segment @> ip_address.ip_address)
-> Bitmap Heap Scan on ip_carriers cr (cost=22.94..663.04 rows=343 width=63) (actual time=5.939..5.939 rows=0 loops=1)
Recheck Cond: (ip_segment @> ip_address.ip_address)
-> Bitmap Index Scan on test_idx_cr (cost=0.00..22.85 rows=343 width=0) (actual time=5.935..5.935 rows=0 loops=1)
Index Cond: (ip_segment @> ip_address.ip_address)
-> Bitmap Heap Scan on ip_privacy_detections pd (cost=1906.70..68119.89 rows=40037 width=56) (actual time=274.352..274.353 rows=0 loops=1)
Recheck Cond: (ip_segment @> ip_address.ip_address)
-> Bitmap Index Scan on idx_ip_privacy_detections_ip_segment (cost=0.00..1896.69 rows=40037 width=0) (actual time=274.349..274.350 rows=0 loops=1)
Index Cond: (ip_segment @> ip_address.ip_address)
Planning Time: 0.739 ms
Execution Time: 589.823 ms
(19 rows)
注意对于要查询的 4 个表，我在它们的ip_segment字段上构建了一个gist索引。
这些left join中的每一个都可以是在应用程序层中关联在一起的单独查询，但是在理想的世界中，我可以让 rds 为我处理关联（连接）。
仅对 1 个 ip 地址运行上述查询需要26.376 ms。对于大约 100 个随机 ip 地址，它需要大约11309.123 ms（11 秒）（并在运行几次相同的查询后提高到大约 1.8 秒）。
查询计划，其中我使用提供的cidrjoin_key字段进行表连接：
explain yze select *
from unnest(array[
inet '98.237.137.99'
]) ip_address
left join ip_geolocations g on g.ip_segment @> ip_address
left join ip_companies c on c.join_key = g.join_key
left join ip_carriers cr on cr.join_key = g.join_key
left join ip_privacy_detections pd on pd.join_key = g.join_key;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Right Join (cost=13413427.68..762295480.26 rows=49873343109 width=412) (actual time=53725.909..53726.119 rows=121 loops=1)
Merge Cond: ((c.join_key)::inet = (g.join_key)::inet)
-> Sort (cost=10678179.38..10748092.42 rows=27965218 width=147) (actual time=45444.278..46291.865 rows=4939342 loops=1)
Sort Key: c.join_key
Sort Method: external merge Disk: 4415120kB
-> Seq Scan on ip_companies c (cost=0.00..910715.18 rows=27965218 width=147) (actual time=0.014..4597.313 rows=27965218 loops=1)
-> Materialize (cost=2735248.30..3478041.50 rows=41149314 width=265) (actual time=6963.430..6963.598 rows=121 loops=1)
-> Merge Left Join (cost=2735248.30..3375168.21 rows=41149314 width=265) (actual time=6963.426..6963.502 rows=121 loops=1)
Merge Cond: ((g.join_key)::inet = (pd.join_key)::inet)
-> Merge Left Join (cost=1112932.44..1115281.67 rows=124973 width=209) (actual time=80.721..80.725 rows=1 loops=1)
Merge Cond: ((g.join_key)::inet = (cr.join_key)::inet)
-> Sort (cost=1103325.02..1103476.54 rows=60608 width=146) (actual time=28.093..28.094 rows=1 loops=1)
Sort Key: g.join_key
Sort Method: quicksort Memory: 25kB
-> Nested Loop Left Join (cost=0.42..1093950.06 rows=60608 width=146) (actual time=27.717..28.086 rows=1 loops=1)
-> Function Scan on unnest ip_address (cost=0.00..0.01 rows=1 width=32) (actual time=0.016..0.017 rows=1 loops=1)
-> Index Scan using idx_ip_geolocations_ip_segment on ip_geolocations g (cost=0.42..1090919.64 rows=303041 width=114) (actual time=27.695..28.062 rows=1 loops=1)
Index Cond: (ip_segment @> ip_address.ip_address)
-> Materialize (cost=9607.42..9950.61 rows=68639 width=63) (actual time=44.198..50.257 rows=20609 loops=1)
-> Sort (cost=9607.42..9779.01 rows=68639 width=63) (actual time=44.195..47.308 rows=20609 loops=1)
Sort Key: cr.join_key
Sort Method: external merge Disk: 5120kB
-> Seq Scan on ip_carriers cr (cost=0.00..1510.39 rows=68639 width=63) (actual time=0.486..12.759 rows=68639 loops=1)
-> Materialize (cost=1622315.86..1662352.94 rows=8007417 width=56) (actual time=5143.369..6417.708 rows=4015488 loops=1)
-> Sort (cost=1622315.86..1642334.40 rows=8007417 width=56) (actual time=5143.366..5843.105 rows=4015488 loops=1)
Sort Key: pd.join_key
Sort Method: external merge Disk: 439120kB
-> Seq Scan on ip_privacy_detections pd (cost=0.00..156763.17 rows=8007417 width=56) (actual time=0.802..845.180 rows=8007417 loops=1)
Planning Time: 12.618 ms
Execution Time: 54800.482 ms
(30 rows)
注意对于要查询的 4 个表，我在一对字段(ip_segment range_ops, join_key inet_ops)字段上构建了一个gist索引。
这个查询花费了大约 59 秒来执行一个 IP地址，这是非常糟糕的。我想知道它是否与使用range_ops而不是inet_ops为ip_segmentgist 索引字段。
让我困惑的是，为什么我在join_key和ip_segment上构建的gist索引甚至没有用于不是ip_geolocations的 3 个表。
有一点要注意的是，我比较这个ipinfo的实际 API，对于同一组 IP地址在 650 毫秒为每个完整的请求（每批 100 个 IP地址）（这意味着有很大的改进空间）。
1
我认为主要的问题是，您正在搜索字符串值之间的 IP地址值。
我会尝试将 IP地址的大整数值与地理位置表中的其他信息一起存储。
将 IP 地址转换为大整数后，您将检查开始 IPint 和结束 IPint 之间的 IPint
以下是解析数据的示例：
on 1860329659 between 1449189901 and 4278870277
而不是
on '110.226.96.187' between '86.96.226.13' and '255.10.97.5'
这比字符串值比较好。
我已经包含了一个临时表来测试测试数据这是如何将 IP 转换为大整数：
Declare @TestData TABLE
(
IP varchar(20)
);
INSERT INTO @TestData (IP) select '255.10.97.5'
INSERT INTO @TestData (IP) select '113.7.99.253'
INSERT INTO @TestData (IP) select '96.25.111.85'
Declare @NewData TABLE
(
IP varchar(20),
IPint bigint
);
INSERT INTO @NewData (IP, IPint)
select t1.IP,
(( convert(bigint,pname(t1.ip, 4))*power(256,3)) + (pname(t1.ip, 3) * power(256,2) ) + (pname(t1.ip, 2) * power(256,1)) + (pname(t1.ip, power(256,0) ))) as IPint
from @TestData as t1
select n1.* from @NewData as n1
结果是：
0
事实证明，最好的方法实际上是在将 ip 列转换为INET后，仅在start_ip字段上创建 BTREE 索引。然后查询是对<=或BETWEEN的简单检查。
本站系公益性非盈利分享网址，本文来自用户投稿，不代表边看边学立场，如若转载，请注明出处
评论列表（24条）