IP地理定位查询太慢

Question

最近，我开始在工作中使用PostgreSQL &我正在尝试根据用户的IP地址优化一个查询，以定位用户。我不太清楚如何从解释分析中读取输出。自从上次更新以来，所有的表都被清空了，所以我知道这不是缓慢的原因。

我有以下表格：

session_ipaddress:

存储访问者的IP地址，有250,000行。有关栏和索引：

session_id VARCHAR PRIMARY KEY,
ip_address INET,
ip_int BIGINT

BTREE INDEX on ip_int

ipblocks_201601:

MaxMind GeoLite2 City阻塞了从http://dev.maxmind.com/geoip/geoip2/geolite2/获得的数据库，其中附加了两列min_ip & max_ip，它们一起保存了CIDR块中IP地址的范围。有关的栏和索引如下：

network CIDR PRIMARY KEY,
geoname_id INTEGER,
min_ip BIGINT,
max_ip BIGINT

BTREE INDEX ON geoname_id
BTREE INDEX ON min_ip
BTREE INDEX ON max_ip

ipgeolookup_201601:

用于GeoLite2区域设置的en位置数据库。有关栏和索引：

geoname_id INTEGER PRIMARY KEY,
country_name VARCHAR,
subdivision_1_name VARCHAR,
city_name VARCHAR

BTREE INDEX ON country_name
BTREE INDEX ON subdivision_1_name
BTREE INDEX ON city_name

这是我正在运行的查询，它需要20多个时间才能完成。

SELECT
  geo.country_name
, geo.subdivision_1_name region_name
, geo.city_name
, COUNT(s.session_id) location_unresolved
FROM session_ipaddress s
JOIN ipblocks_201601 ip ON ip.min_ip <= s.ip_int AND ip.max_ip >= s.ip_int
JOIN ipgeolookup_201601 geo ON geo.geoname_id = ip.geoname_id
WHERE geo.country_name = 'United States' OR geo.country_name = 'Canada'
GROUP BY 1, 2, 3;

总运行时: 22192.814 ms &这是来自EXPLAIN ANALYZE：http://explain.depesz.com/s/DNcV的输出

Answer 1

您应该尝试添加复合索引。

ipblocks_201601包含(geoname_id, min_ip, max_ip)的一个

ipgeolookup_201601包含(country_name, geoname_id)的另一个

OP：编辑：

最大的改进来源是将work_mem从默认的1MB增加到4MB。数据库位于一台内存为2GB的机器上。

执行时间从20岁下降到5岁

添加复合索引进一步缩短了执行时间。