Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

PostgreSQL 百亿地理位置数据 近邻查询性能

4 minute read

背景

本文主要要展示的是PostgreSQL在位置信息近邻（KNN）查询方面的性能。

测试类型point，索引类型GiST。

（PostGIS同样支持KNN查询，性能和本文的测试差不多）

测试数据量大于100亿。

测试结果

64个并发，随机选点，单次KNN查询请求的平均响应时间为0.848毫秒。

测试环境和优化请参考

http://blog.163.com/digoal@126/blog/static/16387704020160941345888/

测试细节

创建测试表

postgres=# create table tbl_point(id serial8, poi point);  
CREATE TABLE  
postgres=# \d tbl_point  
                      Table "benchmarksql.tbl_point"  
 Column |  Type  |                       Modifiers                          
--------+--------+--------------------------------------------------------  
 id     | bigint | not null default nextval('tbl_point_id_seq'::regclass)  
 poi    | point  |   
  
postgres=# alter sequence tbl_point_id_seq cache 10000;  
ALTER SEQUENCE

生成测试数据：

point的x和y的取值范围都是-50000到50000，所以一共可以生成100亿个不同的point。

和测试数据量相符。

vi test.sql  
insert into tbl_point(poi) select point(trunc(100000*(0.5-random())), trunc(100000*(0.5-random()))) from generate_series(1,10000);

使用pgbench 每秒 约插入233万位置信息。

pgbench -M prepared -n -r -f ./test.sql -P 1 -c 96 -j 96 -T 1100  
tps = 233.018365 (including connections establishing)  
tps = 233.150940 (excluding connections establishing)

数据量

postgres=# select count(*) from tbl_point;  
   count      
------------  
 2532820000  
(1 row)

当前表大小：

postgres=# \dt+  
 benchmarksql | tbl_point  | table | postgres | 123 GB   |

在point类型上创建GiST索引

postgres=# create index idx_tbl_point on tbl_point using gist(poi) with (buffering=on);  
  
postgres=# \d+ tbl_point  
                                         Table "benchmarksql.tbl_point"  
 Column |  Type  |                       Modifiers                        | Storage | Stats target | Description   
--------+--------+--------------------------------------------------------+---------+--------------+-------------  
 id     | bigint | not null default nextval('tbl_point_id_seq'::regclass) | plain   |              |   
 poi    | point  |                                                        | plain   |              |   
Indexes:  
    "idx_tbl_point" gist (poi) WITH (buffering='on')

索引大小：

\di+  
 benchmarksql | idx_tbl_point      | index | postgres | tbl_point  | 170 GB  |

新建完索引后，插入性能会下降，现在每秒约插入55万条位置信息。

pgbench -M prepared -n -r -f ./test.sql -P 1 -c 96 -j 96 -T 100  
transaction type: Custom query  
scaling factor: 1  
query mode: prepared  
number of clients: 96  
number of threads: 96  
duration: 100 s  
number of transactions actually processed: 5587  
latency average: 1726.947 ms  
latency stddev: 118.223 ms  
tps = 55.390665 (including connections establishing)  
tps = 55.419003 (excluding connections establishing)  
statement latencies in milliseconds:  
        1726.946947     insert into tbl_point(poi) select point(trunc(100000*(0.5-random())), trunc(100000*(0.5-random()))) from generate_series(1,10000);

持续插入100亿记录

pgbench -M prepared -n -r -f ./test.sql -P 1 -c 64 -j 64 -T 13600

表大小

500GB

索引大小

720GB

knn检索例子

postgres=# select *,poi <-> point(1000,1000) dist from tbl_point where poi <-> point(1000,1000) < 100 order by poi <-> point(1000,1000) limit 10;  
     id     |     poi     |       dist         
------------+-------------+------------------  
  399588621 | (1000,999)  |                1  
 1030719903 | (1001,999)  |  1.4142135623731  
 2698052191 | (1001,1001) |  1.4142135623731  
 3291219762 | (999,999)   |  1.4142135623731  
 2757190006 | (1002,1000) |                2  
 2862610530 | (998,1001)  | 2.23606797749979  
 3450459141 | (998,1001)  | 2.23606797749979  
 3124756442 | (1002,1001) | 2.23606797749979  
 3105439886 | (1001,998)  | 2.23606797749979  
  473144305 | (998,1002)  | 2.82842712474619  
(10 rows)

KNN执行计划

排序和检索都走了GiST索引。

例如，一个查询如下，扫描了16个数据块，在shared buffer命中8个，读8个块(可能在OS CACHE或直接读block dev)。

postgres=# explain (analyze,verbose,buffers,timing,costs) select *,poi <-> point(10090,10090) dist from tbl_point where poi <-> point(10090,10090) < 100 order by poi <-> point(10090,10090) limit 10;  
                                                                           QUERY PLAN                                                                             
----------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=0.56..13.15 rows=10 width=24) (actual time=0.469..1.309 rows=10 loops=1)  
   Output: id, poi, ((poi <-> '(10090,10090)'::point))  
   Buffers: shared hit=8 read=8 dirtied=1  
   ->  Index Scan using idx_tbl_point on benchmarksql.tbl_point  (cost=0.56..1510464450.86 rows=1199422376 width=24) (actual time=0.468..1.306 rows=10 loops=1)  
         Output: id, poi, (poi <-> '(10090,10090)'::point)  
         Order By: (tbl_point.poi <-> '(10090,10090)'::point)  
         Filter: ((tbl_point.poi <-> '(10090,10090)'::point) < '100'::double precision)  
         Buffers: shared hit=8 read=8 dirtied=1  
 Planning time: 0.084 ms  
 Execution time: 1.347 ms  
(10 rows)

KNN检索压力测试

测试在100亿地理位置数据中，postgresql 的knn查询性能。

测试脚本如下，随机生成一个point，然后查找这个point附近距离为100以内，按距离排序，取出1条。

vi test.sql  
\setrandom x -50000 50000  
\setrandom y -50000 50000  
select * from tbl_point where poi <-> point(:x,:y) <100 order by poi <-> point(:x,:y) limit 1;

测试结果

单次请求的平均响应时间为0.848毫秒。

pgbench -M prepared -n -r -f ./test.sql -P 1 -c 64 -j 64 -T 100  
transaction type: Custom query  
scaling factor: 1  
query mode: prepared  
number of clients: 64  
number of threads: 64  
duration: 100 s  
number of transactions actually processed: 7418337  
latency average: 0.858 ms  
latency stddev: 0.564 ms  
tps = 74151.604194 (including connections establishing)  
tps = 74184.255934 (excluding connections establishing)  
statement latencies in milliseconds:  
        0.007518        \setrandom x -50000 50000  
        0.002193        \setrandom y -50000 50000  
        0.847847        select * from tbl_point where poi <-> point(:x,:y) <100 order by poi <-> point(:x,:y) limit 1;

因为数据量较大，内存加索引超过1TB，远远超越了内存大小，要做到0.858毫秒的响应，得益于AliFlash pci-E SSD卡的性能，单次请求平均0.01毫秒完成。队列等待0.19毫秒。

avg-cpu:  %user   %nice %system %iowait  %steal   %idle  
          69.54    0.00   24.11    5.87    0.00    0.47  
Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util  
dfa               0.00     0.00 26100.00 2096.00 417600.00 33536.00    16.00     5.05    0.18   0.03  98.00  
dfb               0.00     0.00 26150.00 2038.00 418400.00 32600.00    16.00     5.01    0.18   0.03  98.40  
dfc               0.00     0.00 25931.00 2026.00 414896.00 32384.00    16.00     6.15    0.22   0.04  99.70  
dm-0              0.00     0.00 78178.00 6160.00 1250848.00 98520.00    16.00    16.73    0.19   0.01 101.00

其他优化手段1

1. http://blog.163.com/digoal@126/blog/static/16387704020137610534650/

当请求的数据距离不在给予范围内时，被扫描的GiST索引PAGE会被放大，所以优化手段可以先order by limit，然后再过滤举例满足条件的。

例子

这个查询可能要跑很久很久才能出结果，并且结果可能是0记录。

explain (analyze,verbose,buffers,timing,costs) select *,poi <-> point(10090,10090000) dist from tbl_point where poi <-> point(10090,10090000) < 100 order by poi <-> point(10090,10090000) limit 10;

优化手段，先order by limit，然后再过滤举例满足条件的。

postgres=# explain (analyze,verbose,buffers,timing,costs) select * from (select *,poi <-> point(10090,10090000) dist from tbl_point order by poi <-> point(10090,10090000) limit 1000 ) t where poi <-> point(10090,10090000) < 100 limit 10;   
                                                                                  QUERY PLAN                                                                                    
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=0.56..13.51 rows=10 width=32) (actual time=3.769..3.769 rows=0 loops=1)  
   Output: t.id, t.poi, t.dist  
   Buffers: shared hit=1174  
   ->  Subquery Scan on t  (cost=0.56..432.00 rows=333 width=32) (actual time=3.769..3.769 rows=0 loops=1)  
         Output: t.id, t.poi, t.dist  
         Filter: ((t.poi <-> '(10090,10090000)'::point) < '100'::double precision)  
         Rows Removed by Filter: 1000  
         Buffers: shared hit=1174  
         ->  Limit  (cost=0.56..417.00 rows=1000 width=24) (actual time=0.106..3.596 rows=1000 loops=1)  
               Output: tbl_point.id, tbl_point.poi, ((tbl_point.poi <-> '(10090,10090000)'::point))  
               Buffers: shared hit=1174  
               ->  Index Scan using idx_tbl_point on benchmarksql.tbl_point  (cost=0.56..1498470227.10 rows=3598267127 width=24) (actual time=0.105..3.505 rows=1000 loops=1)  
                     Output: tbl_point.id, tbl_point.poi, (tbl_point.poi <-> '(10090,10090000)'::point)  
                     Order By: (tbl_point.poi <-> '(10090,10090000)'::point)  
                     Buffers: shared hit=1174  
 Planning time: 0.069 ms  
 Execution time: 3.793 ms  
(17 rows)

PostGIS也是这么玩的：

digoal=# select * from (select *,ST_Distance(jwd, ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163)) AS dist from cust_jw order by jwd <-> ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163) limit 1000) t where dist<15000;  
  dz  |                        jwd                         |       dist         
------+----------------------------------------------------+------------------  
 杭州 | 0101000020730800004C94087D5D4F54C173AA7759E8FB5D41 |                0  
 余杭 | 0101000020730800000E6E5A20494854C121FC688DA9EF5D41 | 14483.9823187612  
(2 rows)  
Time: 0.634 ms

其他优化手段2

更小气(更优、更节约资源)的玩法如下，这是为节约资源优化到极致的方法，使用游标解决以上问题，最多多一个PAGE的扫描。

digoal=# do language plpgsql $$  
declare  
  v_rec record;  
  v_limit int := 1000;  
begin  
  set enable_seqscan=off;  -- 强制索引, 因为扫描行数够就退出.  
  for v_rec in select *,ST_Distance(jwd, ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163)) AS dist from cust_jw order by jwd <-> ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163) loop  
    if v_limit <=0 then   
      raise notice '已经取足数据';  
      return;  
    end if;  
    if v_rec.dist > 20000 then   
      raise notice '满足条件的点已输出完毕';  
      return;  
    else  
      raise notice 'do someting, v_rec:%', v_rec;  
    end if;  
    v_limit := v_limit -1;  
  end loop;  
end;  
$$;  
NOTICE:  do someting, v_rec:(杭州,0101000020730800004C94087D5D4F54C173AA7759E8FB5D41,0)  
NOTICE:  do someting, v_rec:(余杭,0101000020730800000E6E5A20494854C121FC688DA9EF5D41,14483.9823187612)  
NOTICE:  满足条件的点已输出完毕  
DO

参考

1. http://www.postgresql.org/docs/9.5/static/gist-intro.html

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...