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

背景

本文主要要展示的是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
				


				
时间： 2024-09-29 04:51:28

		
		


		


	

	
	

		


		
PostgreSQL 百亿地理位置数据 近邻查询性能的相关文章


								

		

			

                PostgreSQL 百亿级数据范围查询, 分组排序窗口取值 极致优化 case
			

		

		

									

				本文将对一个任意范围按ID分组查出每个ID对应的最新记录的CASE做一个极致的优化体验.优化后性能维持在可控范围内,任意数据量,毫秒级返回,性能平稳可控.比优化前性能提升1万倍. CASE 有一张数据表,结构: CREATE TABLE target_position ( target_id varchar(80), time bigint, content text ); 数据量是 100 亿条左右 target_id 大约 20 万个 数据库使用的是 PostgreSQL 9.4 需求: 查			

		

	

				

		

			

                PostgreSQL 百亿数据 秒级响应 正则及模糊查询
			

		

		

									

				正则匹配和模糊匹配通常是搜索引擎的特长,但是如果你使用的是 PostgreSQL 数据库照样能实现,并且性能不赖,加上分布式方案 (譬如 plproxy, pg_shard, fdw shard, pg-xc, pg-xl, greenplum),处理百亿以上数据量的正则匹配和模糊匹配效果杠杠的,同时还不失数据库固有的功能,一举多得. 物联网中有大量的数据,除了数字数据,还有字符串类的数据,例如条形码,车牌,手机号,邮箱,姓名等等. 假设用户需要在大量的传感数据中进行模糊检索,甚至规则表达式匹配			

		

	

				

		

			

                百度投上百亿建大数据中心 内部称产出十分微小
			

		

		

									

				时下,"大数据"这词儿挺时髦,好友相聚,三句话不提"大数据"似乎都不好意思.然而,大数据到底是咋回事,这玩意究竟有何用途?恐怕连投巨资开发大数据的百度.腾讯和阿里,也不一定说得清楚.因此,业内有持怀疑论者认为,"大数据"迟早会成为一"大笑话",就像当年神奇的http://www.aliyun.com/zixun/aggregation/13840.html">超级计算机如今几乎成了一堆废铁. 果真如此吗?近日,			

		

	

				

		

			

                PostgreSQL 标签系统 bit 位运算 查询性能
			

		

		

									

				在标签系统中,通常会有多个属性,每个属性使用一个标签标示,最简单的标签是用0和1来表示,代表true和false.我们可以把所有的标签转换成比特位,例如系统中一共有200个标签,5000万用户.那么我们可以通过标签的位运算来圈定特定的人群.这样就会涉及BIT位的运算.那么我们来看看PostgreSQL位运算的性能如何?PostgreSQL 9.5 postgres=# create table t_bit2 (id bit(200)); CREATE TABLE Time: 1.018 ms p			

		

	

				

		

			

                如何设计一个数据库中间件（支持百亿级别数据存储）
			

		

		

									

				继<如何设计开发一个可用的web容器>之后又一如何系列文章,<如何设计一个数据库中间件> ==========广告时间========== 鄙人的新书<Tomcat内核设计剖析>已经在京东预售了,有需要的朋友可以到 https://item.jd.com/12185360.html 进行预定.感谢各位朋友. =========================			

		

	

				

		

			

                中文模糊查询性能优化 by PostgreSQL trgm
			

		

		

									

				前模糊,后模糊,前后模糊,正则匹配都属于文本搜索领域常见的需求.PostgreSQL在文本搜索领域除了全文检索,还有trgm是一般数据库没有的,甚至可能很多人没有听说过.对于前模糊和后模糊,PG则与其他数据库一样,可以使用btree来加速,后模糊可以使用反转函数的函数索引来加速.对于前后模糊和正则匹配,则可以使用trgm,TRGM是一个非常强的插件,对这类文本搜索场景性能提升非常有效,100万左右的数据量,性能提升有500倍以上. 例子:生成100万数据 postgres=# create ta			

		

	

				

		

			

                PostgreSQL 如何潇洒的处理每天上百TB的数据增量
			

		

		

									

				本文主要介绍并测试PostgreSQL 在中高端x86服务器上的数据插入速度(目标表包含一个时间字段的索引),帮助企业用户了解PostgreSQL在这类场景下的性能表现.这类场景常见于 : 运营商网关数据,金融行业数据,产生量大,并且要求快速插入大数据库中持久化保存.另外, 用户如果需要流式实时处理,可以参考基于PostgreSQL的流式处理方案,一天处理1万亿的实时流式处理是如何实现的?https://yq.aliyun.com/articles/166 TEST CASE .1. 平均每条记			

		

	

				

		

			

                PostgreSQL 全表 全字段 模糊查询的毫秒级高效实现 - 搜索引擎也颤抖了
			

		

		

									

				标签 PostgreSQL , 分词 , 全文检索 , 全字段检索 , 任意字段检索 , 下拉框选择 , 搜索引擎 背景 在一些应用程序中,可能需要对表的所有字段进行检索,有些字段可能需要精准查询,有些字段可能需要模糊查询或全文检索. 比如一些前端页面下拉框的勾选和选择. 这种需求对于应用开发人员来说,会很蛋疼,因为写SQL很麻烦,例子: 之前写过一篇文章来解决这个问题 <PostgreSQL 行级 全文检索> 使用的是全文检索,而当用户的需求为模糊查询时? 如何来解决呢? 不难想到我之前写过			

		

	

				

		

			

                MySQL架构优化实战系列2：主从复制同步与查询性能调优
			

		

		

									

				一.主从复制同步部署   1.概念 主从复制:2台以上mysql服务器, 做负载均衡, 主服务器负责增删改 , 从服务器负责查询 同步原理:mysql开启bin-log日志,主服务器所有的增删改操作会记录到bin-log日志:然后主服务器把bin-log日志发送 给 从服务器 , 从服务器重放bin-log日志 确保数据同步 2.开启bin-log日志 配置 my.cnf 文件 并重启 mysql [root@localhost etc]# vim /etc/my.cnf     [root@l