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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 本文主要要展示的是PostgreSQL在位置信息近邻(KNN)查询方面的性能。 测试类型point,索引类型GiST。 (PostGIS同样支持KNN查询,性能和本文的测试差不多) 测试数据量大于100亿。

背景

本文主要要展示的是PostgreSQL在位置信息近邻(KNN)查询方面的性能。
测试类型point,索引类型GiST。
(PostGIS同样支持KNN查询,性能和本文的测试差不多) 
测试数据量大于100亿。

结果

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

测试环境和优化请参考


创建测试表

 
 

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

当请求的数据距离不在给予范围内时,被扫描的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


[参考]
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
4月前
|
缓存 关系型数据库 数据库
PostgreSQL性能
【8月更文挑战第26天】PostgreSQL性能
73 1
|
14天前
|
存储 关系型数据库 数据库
【赵渝强老师】PostgreSQL的数据文件
PostgreSQL的物理存储结构主要包括数据文件、日志文件等。数据文件按oid命名,超过1G时自动拆分。通过查询数据库和表的oid,可定位到具体的数据文件。例如,查询数据库oid后,再查询特定表的oid及relfilenode,即可找到该表对应的数据文件位置。
|
1月前
|
SQL 关系型数据库 数据库
PostgreSQL性能飙升的秘密:这几个调优技巧让你的数据库查询速度翻倍!
【10月更文挑战第25天】本文介绍了几种有效提升 PostgreSQL 数据库查询效率的方法,包括索引优化、查询优化、配置优化和硬件优化。通过合理设计索引、编写高效 SQL 查询、调整配置参数和选择合适硬件,可以显著提高数据库性能。
270 1
|
3月前
|
缓存 关系型数据库 数据库
如何优化 PostgreSQL 数据库性能?
如何优化 PostgreSQL 数据库性能?
145 2
|
2月前
|
存储 关系型数据库 MySQL
四种数据库对比MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景
四种数据库对比 MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景
|
3月前
|
缓存 关系型数据库 数据库
PostgreSQL的性能
PostgreSQL的性能
178 2
|
4月前
|
缓存 关系型数据库 数据库
PostgreSQL 查询性能
【8月更文挑战第5天】PostgreSQL 查询性能
86 8
|
4月前
|
关系型数据库 Java 数据库
PostgreSQL性能
【8月更文挑战第5天】PostgreSQL性能
107 7
|
4月前
|
监控 关系型数据库 数据库
如何优化PostgreSQL的性能?
【8月更文挑战第4天】如何优化PostgreSQL的性能?
266 7
|
4月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
470 0

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版