PostgreSQL IP范围 查询优化

Question

这是表结构:

CREATE TABLE "ip_loc" (
"start_ip" inet,
"end_ip" inet,
"iso2" varchar(4),
"state" varchar(100),
"city" varchar(100) 
);
CREATE INDEX "index_ip_loc" on ip_loc using gist(iprange(start_ip,end_ip));

数据有500W+，查询一次要1秒多

EXPLAIN ANALYZE select * from ip_loc where iprange(start_ip,end_ip)@>'180.167.1.25'::inet;

  Recheck Cond: (iprange(start_ip, end_ip) @> '180.167.1.25'::inet)
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on index_ip_loc  (cost=0.00..1073.53 rows=28948 width=0) (actual time=1039.411..1039.411 rows=1 loops=1)
        Index Cond: (iprange(start_ip, end_ip) @> '180.167.1.25'::inet)
Planning time: 0.090 ms
Execution time: 1039.466 ms

iprange是自定义的range:

CREATE TYPE iprange AS RANGE (
    SUBTYPE = inet
)

跪求优化方案...

Answer 1

把IP转换成LONG类型。PHP有ip2long。然后直接存INT进数据库。