PostgreSQL中IP地址查询优化及关联查询性能问题求助

我正尝试优化基于免费IP数据集的IP地址地理位置查询，原始表结构如下：

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查询耗时50-120秒，速度极慢。

改造为IPRange表并添加GIST索引 #

参考指南将表迁移为带GIST索引的ip_geolocations表：

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查询耗时降至20-200ms，但批量查询100个IP仍需1.5-3秒，速度未达预期。批量查询示例：

select *
from unnest(array[
    inet '<ip_address_string>'
]) ip_address
left join ip_geolocations g on g.ip_segment @> ip_address;

优化过程中遇到的问题 #

1. GIN索引创建失败 #

尝试创建GIN索引时报错：

create index test_idx_geolocations on ip_geolocations using gin (ip_segment);

ERROR:  operator class "inet_ops" does not exist for access method "gin"
Time: 2.173 ms

尝试自定义操作符类也失败：

create operator class 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

但实际查询中@>运算符可正常使用，对此存在困惑。

2. PostgreSQL配置调整无查询性能提升 #

调整以下配置后，仅提升了索引创建速度，查询执行时间无明显改善：

alter table ip_geolocations set (parallel_workers = 4);

set max_parallel_maintenance_workers to 4;

set maintenance_work_mem to '1 GB';

3. 多表基于join_key关联查询速度极慢 #

另有3个类似结构的IP地址表，迁移为带iprange字段和GIST索引的表后，基于join_key的关联查询速度极慢，单IP耗时约59秒且未使用相关GIST索引。

查询执行计划对比 #

• 基于ip_segment关联的查询：单IP耗时约26ms，100个IP首次查询约11秒，后续缓存后降至1.8秒。
• 基于join_key关联的查询：单IP耗时约59秒，未命中GIST索引。

当前对比同数据源API的100个IP批量查询耗时<650ms，仍有较大优化空间，寻求可行的优化方案及相关文档指导。

内容的提问来源于stack exchange，提问作者Akhil