HTAP数据库 PostgreSQL 场景与性能测试之 47 - (OLTP) 空间应用 - 高并发空间位置更新、多属性KNN搜索并测(含空间索引)末端配送类项目

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
简介: 标签 PostgreSQL , HTAP , OLTP , OLAP , 场景与性能测试 背景 PostgreSQL是一个历史悠久的数据库,历史可以追溯到1973年,最早由2014计算机图灵奖得主,关系数据库的鼻祖Michael_Stonebraker 操刀设计,PostgreSQL具备与Oracle类似的功能、性能、架构以及稳定性。

标签

PostgreSQL , HTAP , OLTP , OLAP , 场景与性能测试


背景

PostgreSQL是一个历史悠久的数据库,历史可以追溯到1973年,最早由2014计算机图灵奖得主,关系数据库的鼻祖Michael_Stonebraker 操刀设计,PostgreSQL具备与Oracle类似的功能、性能、架构以及稳定性。

pic

PostgreSQL社区的贡献者众多,来自全球各个行业,历经数年,PostgreSQL 每年发布一个大版本,以持久的生命力和稳定性著称。

2017年10月,PostgreSQL 推出10 版本,携带诸多惊天特性,目标是胜任OLAP和OLTP的HTAP混合场景的需求:

《最受开发者欢迎的HTAP数据库PostgreSQL 10特性》

1、多核并行增强

2、fdw 聚合下推

3、逻辑订阅

4、分区

5、金融级多副本

6、json、jsonb全文检索

7、还有插件化形式存在的特性,如 向量计算、JIT、SQL图计算、SQL流计算、分布式并行计算、时序处理、基因测序、化学分析、图像分析 等。

pic

在各种应用场景中都可以看到PostgreSQL的应用:

pic

PostgreSQL近年来的发展非常迅猛,从知名数据库评测网站dbranking的数据库评分趋势,可以看到PostgreSQL向上发展的趋势:

pic

从每年PostgreSQL中国召开的社区会议,也能看到同样的趋势,参与的公司越来越多,分享的公司越来越多,分享的主题越来越丰富,横跨了 传统企业、互联网、医疗、金融、国企、物流、电商、社交、车联网、共享XX、云、游戏、公共交通、航空、铁路、军工、培训、咨询服务等 行业。

接下来的一系列文章,将给大家介绍PostgreSQL的各种应用场景以及对应的性能指标。

环境

环境部署方法参考:

《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(适合新用户)》

阿里云 ECS:56核,224G,1.5TB*2 SSD云盘

操作系统:CentOS 7.4 x64

数据库版本:PostgreSQL 10

PS:ECS的CPU和IO性能相比物理机会打一定的折扣,可以按下降1倍性能来估算。跑物理主机可以按这里测试的性能乘以2来估算。

场景 - 高并发空间位置更新、多属性KNN搜索并测(含空间索引)

1、背景

越来越多的应用正在接入空间数据属性,例如 物联网、车辆轨迹管理、公安系统的稽侦系统、O2O应用、LBS交友应用、打车应用等等。

被管理的对象携带空间属性,对象的运动形成了轨迹,最后一个位置点表示对象的最终位置。

PostgreSQL在空间数据库管理领域有这几十年的发展历程,例如PostGIS空间数据库,pgrouting路由插件等,GiST空间索引,SP-GiST空间分区索引等。

本文要测试的是空间数据的合并更新性能(携带空间索引),例如,更新对象的最终空间位置,同时根据用户输入,搜索附近N米内满足条件的对象(用到了btree_gist插件以及GiST索引)。

2、设计

2000万个被跟踪对象,2000万个点,含空间索引。

1、实时合并更新被跟踪对象的位置。

2、同时根据用户输入,搜索附近N米内满足条件的对象。

3、准备测试表

创建测试表、索引。

create table tbl_pos(id int primary key, att1 int, att2 int, att3 int, mod_time timestamp, pos geometry);        
        
create extension btree_gist;        
        
create index idx_tbl_pos_1 on tbl_pos using gist(att1, att2, pos);        

查询为多维度搜索,除了空间相近,还需要满足某些查询条件,例如:

注意,order by的两侧需要对齐类型,例如geometry <-> geometry,这样才会走索引哦,否则效率差的很。

explain select *, ST_DistanceSpheroid(pos , st_setsrid(st_makepoint(120,60), 4326), 'SPHEROID["WGS84",6378137,298.257223563]') from tbl_pos -- 120, 60表示经纬度       
where att1=3 and att2<=3 and       
st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint(120, 60), 4326)),5000)), pos)   -- 5000 表示5000米      
order by pos <-> st_setsrid(st_makepoint(120, 60), 4326) limit 100;   -- 这里不要使用pos <-> geography(st_setsrid(st_makepoint(120, 60), 4326))     
      
                                                      QUERY PLAN                                                               
-----------------------------------------------------------------------------------------------------------------------        
 Limit  (cost=0.42..469.18 rows=100 width=72)      
   ->  Index Scan using idx_tbl_pos_1 on tbl_pos  (cost=0.42..7125.52 rows=1520 width=72)      
         Index Cond: ((att1 = 3) AND (att2 <= 3) AND ('0103000020E61000000100000021000000267F6991BA055E40F420D353BCFF4D40AE7F7C0791055E4004D7AB0D9FFE4D405B8C22C830055E409DBC6E5F8FFD4D40D24FE28E9D045E40FB4032B697FC4D4043F65109DD035E40B3AD  6691C1FB4D407907729EF6025E40CD022A2615FB4D40FCD5B525F3015E401498630F99FA4D40FD598C90DC005E400571980D52FA4D40C3B0A789BDFF5D40F14DD0D842FA4D40F12  00DA1FE5D408B  38066CFA4D4059C7410092FD5D40C53C7702CCFA4D40AFC2A9C  AFC5D40A28FEA205FFB4D40D  F6AD2D2C4FB5D408  A3DBF1FFC4D40B73C6B6518FB5D400FF2147C06FD4D40112D1B1F9CFA5D40D0CACF7E0AFE4D404E26B0CD54FA5D40CCE2BECD21FF4D4087E3C53845FA5D403B51A0AF41004E408079E9046EFA5D40CDB8BA145F014E40CF0290ABCDFA5D406B5C9D036F024E409222728860FB5D  40D84C5A0567034E40DB9B09FC20FC5D409F06088D3D044E40B06412A207FD5D40FBB07A56EA044E40B78A179A0BFE5D40963273B866054E40A25449DF22FF5D40D0DCF9E6AD054E4024FC28AC42005E402D883723BD054E4025240AE65F015E40A173DCD693054E4033F60B8C6F025E4068D3F799330  54E4023F6012567035E4013FB0323A0044E4069F3D1273D045E40E1C5CE21DF034E40D515105AE9045E402E7AC106F8024E407F25172165055E40579ED4B8F3014E40D5FF79C2AB055E40E0132A3DDC004E40267F6991BA055E40F420D353BCFF4D40'::geometry ~ pos))      
         Order By: (pos <-> '0101000020E61000000000000000005E400000000000004E40'::geometry)      
         Filter: _st_contains('0103000020E61000000100000021000000267F6991BA055E40F420D353BCFF4D40AE7F7C0791055E4004D7AB0D9FFE4D405B8C22C830055E409DBC6E5F8FFD4D40D24FE28E9D045E40FB4032B697FC4D4043F65109DD035E40B3AD6691C1FB4D407907729EF602  5E40CD022A2615FB4D40FCD5B525F3015E401498630F99FA4D40FD598C90DC005E400571980D52FA4D40C3B0A789BDFF5D40F14DD0D842FA4D40F12  00DA1FE5D408B  38066CFA4D4059C7410092FD5D40C53C7702CCFA4D40AFC2A9C  AFC5D40A28FEA205FFB4D40DF6AD2D2C4FB5D408  A3DBF1  FFC4D40B73C6B6518FB5D400FF2147C06FD4D40112D1B1F9CFA5D40D0CACF7E0AFE4D404E26B0CD54FA5D40CCE2BECD21FF4D4087E3C53845FA5D403B51A0AF41004E408079E9046EFA5D40CDB8BA145F014E40CF0290ABCDFA5D406B5C9D036F024E409222728860FB5D40D84C5A0567034E40DB9B09  FC20FC5D409F06088D3D044E40B06412A207FD5D40FBB07A56EA044E40B78A179A0BFE5D40963273B866054E40A25449DF22FF5D40D0DCF9E6AD054E4024FC28AC42005E402D883723BD054E4025240AE65F015E40A173DCD693054E4033F60B8C6F025E4068D3F79933054E4023F6012567035E4013F  B0323A0044E4069F3D1273D045E40E1C5CE21DF034E40D515105AE9045E402E7AC106F8024E407F25172165055E40579ED4B8F3014E40D5FF79C2AB055E40E0132A3DDC004E40267F6991BA055E40F420D353BCFF4D40'::geometry, pos)      
(5 rows)      
    id    | att1 | att2 | att3 |          mod_time          |                        pos                         | st_distancespheroid       
----------+------+------+------+----------------------------+----------------------------------------------------+---------------------      
  8892973 |    3 |    3 |    1 | 2018-07-10 17:44:08.386618 | 0101000020E61000000000031067FF5D4000001A383C014E40 |    1194.64901625583      
  2083046 |    3 |    1 |    2 | 2018-07-17 15:42:55.031903 | 0101000020E610000000002B99AE005E400000E007D5014E40 |    1701.93484541633      
 13441821 |    3 |    2 |    3 | 2018-07-10 17:41:49.504894 | 0101000020E610000000006675EF005E400000E04E74FB4D40 |    40  .84460729299      
  8662140 |    3 |    1 |    4 | 2018-07-17 15:41:42.761599 | 0101000020E61000000000D0F49AFF5D400000DC3C0BFB4D40 |    4327.54163693541      
  78  452 |    3 |    3 |    1 | 2018-07-17 15:42:15.954411 | 0101000020E61000000000CA94E7FF5D400000F27727054E40 |    4487.02042256402      
 16796301 |    3 |    2 |    4 | 2018-07-10 17:15:10.231126 | 0101000020E6100000000008F571025E400000D2A562024E40 |    2975.  699500948      
  1587379 |    3 |    1 |    1 | 2018-07-10 17:53:31.308692 | 0101000020E61000000000ABBFBBFC5D400000EC0B93FB4D40 |    4791.49425173447      
  8560096 |    3 |    3 |    2 | 2018-07-17 15:41:46.907464 | 0101000020E610000000001B707F035E4000002A5B09FC4D40 |     4605.4604334459      
  5540068 |    3 |    1 |    2 | 2018-07-10 17:42:29.689334 | 0101000020E610000000004C330C055E400000F02624FE4D40 |    4689.80080183583      
 17813180 |    3 |    1 |    5 | 2018-07-10 17:50:18.297117 | 0101000020E61000000000B88C95FA5D400000A6C915004E40 |    4722.45290664137      
  6424827 |    3 |    3 |    4 | 2018-07-10 17:50:54.958542 | 0101000020E61000000000564E8EFA5D4000002C28BA004E40 |    4788.20027459238      
(11 rows)      

4、准备测试函数(可选)

5、准备测试数据

6、准备测试脚本

测试仅使用一般的CPU资源(28核)。

1、更新用户的最终位置,由于空间移动有一定的速比,所以更新后是基于原有位置的一个相对位移位置。

vi test.sql          
          
\set att1 random(1,5)        
\set att2 random(1,5)        
\set att3 random(1,5)        
\set id random(1,20000000)        
\set x random(120,130)        
\set y random(70,80)        
insert into tbl_pos (id, att1, att2, att3, mod_time, pos) values (:id, :att1, :att2, :att3, now(), st_setsrid(st_makepoint(:x,:y), 4326)) on conflict (id) do update set pos=st_setsrid(st_makepoint( st_x(tbl_pos.pos)+5-random()*10, st_y(tbl_pos.pos)+5-random()*10), 4326), mod_time=excluded.mod_time;         

压测

CONNECTS=20        
TIMES=120          
export PGHOST=$PGDATA          
export PGPORT=1999          
export PGUSER=postgres          
export PGPASSWORD=postgres          
export PGDATABASE=postgres          
          
pgbench -M prepared -n -r -f ./test.sql -P 5 -c $CONNECTS -j $CONNECTS -T $TIMES          

2、根据用户输入的att1, att2条件,搜索满足条件的附近5公里内的对象,根据距离顺序返回100条。

vi test1.sql        
        
\set att1 random(1,5)        
\set att2 random(1,5)        
\set x random(120,130)        
\set y random(70,80)        
select * from tbl_pos where att1=:att1 and att2=:att2 and st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint(:x,:y), 4326)),5000)), pos) order by pos <-> st_setsrid(st_makepoint(:x,:y), 4326) limit 100;        

压测

CONNECTS=8        
TIMES=120          
export PGHOST=$PGDATA          
export PGPORT=1999          
export PGUSER=postgres          
export PGPASSWORD=postgres          
export PGDATABASE=postgres          
          
# pgbench -M prepared -n -r -f ./test1.sql -P 5 -c $CONNECTS -j $CONNECTS -T $TIMES        
# 由于这里用了makepoint,并且绑定的是里面的浮点数,而不是geometry本身。所以导致prepared的问题,使用simple query或extend协议,防止prepare后执行计划不准确导致性能的问题。  
# 详见末尾  
pgbench -M extended -n -r -f ./test1.sql -P 5 -c $CONNECTS -j $CONNECTS -T $TIMES     

7、测试

1、更新对象位置

transaction type: ./test.sql        
scaling factor: 1        
query mode: prepared        
number of clients: 20        
number of threads: 20        
duration: 120 s        
number of transactions actually processed: 13271261        
latency average = 0.181 ms        
latency stddev = 0.196 ms        
tps = 110592.138000 (including connections establishing)        
tps = 110597.618184 (excluding connections establishing)        
script statistics:        
 - statement latencies in milliseconds:        
         0.001  \set att1 random(1,5)        
         0.000  \set att2 random(1,5)        
         0.000  \set att3 random(1,5)        
         0.000  \set id random(1,20000000)        
         0.000  \set x random(120,130)        
         0.000  \set y random(70,80)        
         0.178  insert into tbl_pos (id, att1, att2, att3, mod_time, pos) values (:id, :att1, :att2, :att3, now(), st_setsrid(st_makepoint(:x,:y), 4326)) on conflict (id) do update set pos=st_setsrid(st_makepoint( st_x(tbl_pos.pos)+5-random()*10, st_y(tbl_pos.pos)+5-random()*10), 4326), mod_time=excluded.mod_time;        

TPS: 110592

平均响应时间: 0.178 毫秒

2、根据用户输入的att1, att2条件,搜索满足条件的附近5公里内的对象,根据距离顺序返回100条。

transaction type: ./test1.sql        
scaling factor: 1        
query mode: prepared        
number of clients: 8        
number of threads: 8        
duration: 120 s        
number of transactions actually processed: 1136703        
latency average = 0.845 ms        
latency stddev = 0.3   ms        
tps = 9472.446079 (including connections establishing)        
tps = 9472.793841 (excluding connections establishing)        
script statistics:        
 - statement latencies in milliseconds:        
         0.002  \set att1 random(1,5)        
         0.000  \set att2 random(1,5)        
         0.000  \set x random(120,130)        
         0.000  \set y random(70,80)        
         0.842  select * from tbl_pos where att1=:att1 and att2=:att2 and st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint(:x,:y), 4326)),5000)), pos) order by pos <-> st_setsrid(st_makepoint(:x,:y), 4326) limit 100;       

TPS: 9472

平均响应时间: 0.842 毫秒

小结1

1、注意,为了提高过滤性能,同时为了支持米为单位的距离,我们存储时使用4326 srid, 同时距离过滤时使用以下表达式

st_contains(      
  geometry(      
    ST_Buffer(  -- 圆形转换为对称多边形,所以边缘部分会有一定的缺失,不过大部分场景不是非得要一个圆形覆盖,调整半径即可放大多边形覆盖的区域。    
      geography(      
        st_setsrid(st_makepoint(:x,:y), 4326)  -- 中心点      
      ),      
      5000   -- st_buffer生成以某个POINT为中心的,半径距离为5000米的polygon空间对对象      
    )      
  ),    -- 将geography转换为geometry      
  pos   -- st_contains, polygon包含point       
)      

st_buffer输出的多边形精度(边的锯齿),可以通过第三个参数指定

http://postgis.net/docs/manual-2.4/ST_Buffer.html

geometry ST_Buffer(geometry g1, float radius_of_buffer);    
    
geometry ST_Buffer(geometry g1, float radius_of_buffer, integer num_seg_quarter_circle);    
    
geometry ST_Buffer(geometry g1, float radius_of_buffer, text buffer_style_parameters);    
    
geography ST_Buffer(geography g1, float radius_of_buffer_in_meters);    
    
geography ST_Buffer(geography g1, float radius_of_buffer, integer num_seg_quarter_circle);    
    
geography ST_Buffer(geography g1, float radius_of_buffer, text buffer_style_parameters);    
postgres=# select ST_Buffer(  -- 圆形转换为对称多边形,所以边缘部分会有一定的缺失,不过大部分场景不是非得要一个圆形覆盖,调整半径即可放大多边形覆盖的区域。    
postgres(#       geography(    
postgres(#         st_setsrid(st_makepoint(:x,:y), 4326)  -- 中心点    
postgres(#       ),    
postgres(#       5000   -- st_buffer生成以某个POINT为中心的,半径距离为5000米的polygon空间对对象    
postgres(#     );    
ERROR:  syntax error at or near ":"    
LINE 3:         st_setsrid(st_makepoint(:x,:y), 4326)      
                                        ^    
postgres=# select st_astext(ST_Buffer(  -- 圆形转换为对称多边形,所以边缘部分会有一定的缺失,不过大部分场景不是非得要一个圆形覆盖,调整半径即可放大多边形覆盖的区域。    
      geography(    
        st_setsrid(st_makepoint(120,60), 4326)  -- 中心点    
      ),    
      5000   -- st_buffer生成以某个POINT为中心的,半径距离为5000米的polygon空间对对象    
    ));    
                                                                                                                                                                                                                                                                                              
                                                                                      st_astext                                                                                          
-----------------------------------------------------------------------------------------------------------------    
 POLYGON((120.089512208008 59.997934797373,120.086976882535 59.9892289246618,120.081102403391 59.9809378901202,120.072116585739 59.9733798737888,120.060365991631 59.9668447257385,120.046302424771 59.9615829186565,120.030465533804 59.9577    
960239696,120.013462197363 59.9556290621131,119.995943464051 59.9551650063621,119.978579893839 59.9564216398749,119.962036194074 59.9593508798803,119.946946063787 59.9638405938451,119.933888154493 59.9697188425826,119.923364023899 59.976    
7603971742,119.915778900661 59.9846952929125,119.911425992986 59.9932191068707,119.91047496152 60.0020045788574,119.912965038302 60.0107141410568,119.918803110729 60.0190128820767,119.927766906223 60.0265814486426,119.939513215431 60.033    
1283845128,119.953590887034 60.0384014224128,119.969458125105 60.0421972811211,119.98650343095 60.0443695755035,120.004069366478 60.0448345204498,120.021478185562 60.0435741974281,120.038058292085 60.0406372509086,120.053170444408 60.036    
1369866788,120.066232638388 60.0302469501146,120.076742664059 60.0231941647256,120.084297440151 60.0152503050019,120.088608378544 60.0067211585481,120.089512208008 59.997934797373))    
(1 row)    
    
postgres=# \df st_buffer    
                                   List of functions    
 Schema |   Name    | Result data type |         Argument data types          |  Type      
--------+-----------+------------------+--------------------------------------+--------    
 public | st_buffer | geography        | geography, double precision          | normal    
 public | st_buffer | geography        | geography, double precision, integer | normal    
 public | st_buffer | geography        | geography, double precision, text    | normal    
 public | st_buffer | geometry         | geometry, double precision           | normal    
 public | st_buffer | geometry         | geometry, double precision, integer  | normal    
 public | st_buffer | geometry         | geometry, double precision, text     | normal    
 public | st_buffer | geometry         | text, double precision               | normal    
 public | st_buffer | geometry         | text, double precision, integer      | normal    
 public | st_buffer | geometry         | text, double precision, text         | normal    
(9 rows)    
    
postgres=# select st_astext(ST_Buffer(  -- 圆形转换为对称多边形,所以边缘部分会有一定的缺失,不过大部分场景不是非得要一个圆形覆盖,调整半径即可放大多边形覆盖的区域。    
      geography(    
        st_setsrid(st_makepoint(120,60), 4326)  -- 中心点    
      ),    
      5000   -- st_buffer生成以某个POINT为中心的,半径距离为5000米的polygon空间对对象    
, 2    ));    
                                                                                                                                                         st_astext                                                                               
                                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
------------------------------------------------------------------------------    
 POLYGON((120.089512208008 59.997934797373,120.060365991631 59.9668447257385,119.995943464051 59.9551650063621,119.933888154493 59.9697188425826,119.91047496152 60.0020045788574,119.939513215431 60.0331283845128,120.004069366478 60.04483    
45204498,120.066232638388 60.0302469501146,120.089512208008 59.997934797373))    
(1 row)    
    
postgres=# select st_astext(ST_Buffer(  -- 圆形转换为对称多边形,所以边缘部分会有一定的缺失,不过大部分场景不是非得要一个圆形覆盖,调整半径即可放大多边形覆盖的区域。    
      geography(    
        st_setsrid(st_makepoint(120,60), 4326)  -- 中心点    
      ),    
      5000   -- st_buffer生成以某个POINT为中心的,半径距离为5000米的polygon空间对对象    
, 20    ));    
                                                                                                                                                                                                                                                 
          st_astext                                                                                                                                                
---------------------------------------------------------------------------------------------------------------------    
 POLYGON((120.089512208008 59.997934797373,120.088908111921 59.9944238512618,120.087756162016 59.9909474584064,120.086063625849 59.9875270364661,120.083841092477 59.9841836517995,120.081102403391 59.9809378901202,120.077864563782 59.9778    
097302563,120.074147634755 59.9748184217725,120.069974607239 59.9719823671917,120.065371258421 59.9693190095193,120.060365991631 59.9668447257385,120.054989660701 59.9645747269098,120.049275379886 59.9625229654624,120.043258320518 59.960    
7020502247,120.036975495624 59.9591231696903,120.030465533804 59.9577960239696,120.023768443702 59.956728765823,120.016925370471 59.9559279511195,120.009978345645 59.9553984990076,120.002970031881 59.9551436620311,119.995943464051 59.955    
1650063621,119.988941788165 59.9554624022667,119.982007999661 59.9560340248591,119.975184682535 59.9568763651408,119.968513750845 59.9579842512615,119.962036194074 59.9593508798803,119.955791827821 59.9609678574459,119.949819051296 59.96    
28252511582,119.944154613012 59.9649116493158,119.938833386091 59.9672142306985,119.933888154493 59.9697188425826,119.929349411479 59.9724100869304,119.925245171517 59.9752714142513,119.921600796809 59.9782852245804,119.918438839511 59.9    
814329749779,119.915778900661 59.9846952929125,119.913637506731 59.9880520948514,119.912028004629 59.9914827093477,119.910960475872 59.9949660038842,119.910441670538 59.9984805147068,119.91047496152 60.0020045788574,119.911060319439 60.0    
055164676015,119.912194308496 60.0089945204305,119.913870103388 60.0124172788112,119.916077527281 60.0157636188547,119.918803110729 60.0190128820767,119.922030171247 60.02214500343,119.925738913146 60.0251406358055,119.929906547094 60.02    
7981270212,119.934507428714 60.030649350874,119.939513215431 60.0331283845128,119.944893040613 60.0354030431106,119.950613703968 60.0374592594989,119.956639877008 60.0392843151536,119.962934322297 60.0408669196299,119.969458125105 60.042    
1972811211,119.976170935976 60.0432671676831,119.983031222659 60.0440699587263,119.989996529766 60.0446006864375,119.997023744464 60.0448560668633,120.004069366478 60.0448345204498,120.011089780615 60.044536181907,120.018041530037 60.043    
9628993317,120.024881588488 60.043118222597,120.03156762968 60.042007381085,120.038058292085 60.0406372509086,120.044313437398 60.0390163118395,120.050294400996 60.0371545942238,120.055964232773 60.0350636162323,120.061287926806 60.03275    
63118559,120.066232638388 60.0302469501146,120.070767887067 60.027551046003,120.074865744419 60.0246852637505,120.078501005406 60.0216673130169,120.081651342285 60.0185158386919,120.084297440151 60.0152503050019,120.086423113343 60.01189    
08746616,120.088015402048 60.0084582838374,120.0890646486 60.0049737137097,120.089564553084 60.0014586594418,120.089512208008 59.997934797373))    
(1 row)    

2、本文使用的插件btree_gin, btree_gist用于加速数组搜索,空间数据与其他普通字段的搜索。

3、使用的索引接口gist用于KNN搜索,距离排序。

4、unionall用于普通字段(可枚举)+gis字段的复合排序输出。

需求升华1

除了空间搜索,还引入普通字段筛选条件

1、如果业务方要求按普通字段(当可以枚举时) + 空间字段排序,可以这样来操作,以达到最好的性能。

例如先返回空闲状态的骑手,其次返回最近7天活跃的骑手,其次。。。。

with       
a as (      
  select * from tbl_pos where att1=1 and     -- 普通条件(假设att1可枚举)(首先输出att1=1,然后att1=2,然后att1=3)      
  att2<=3 and st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint(120,60), 4326)),5000)), pos)       
  order by pos <-> st_setsrid(st_makepoint(120,60), 4326) limit 100    -- 只按空间排      
),      
b as (      
  select * from tbl_pos where att1=2 and       
  att2<=3 and st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint(120,60), 4326)),5000)), pos)       
  order by pos <-> st_setsrid(st_makepoint(120,60), 4326) limit 100      
),      
c as (      
  select * from tbl_pos where att1=3 and       
  att2<=3 and st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint(120,60), 4326)),5000)), pos)       
  order by pos <-> st_setsrid(st_makepoint(120,60), 4326) limit 100      
)      
select * from a union all select * from b union all select * from c limit 100;  -- 按指定顺序写ALIAS union all,执行计划会从先到后对query进行append      
      
以上效果等效如下SQL,但是以上SQL性能比下面这条高很多很多。      
      
select * from tbl_pos where att2<=3 and st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint(120,60), 4326)),5000)), pos)       
order by att1, pos <-> st_setsrid(st_makepoint(120,60), 4326)   -- 按att1, knn 复合排序。      
limit 100;      

效果:

explain (analyze,verbose,timing,costs,buffers) with       
a as (      
  select * from tbl_pos where att1=1 and -- 普通条件(首先输出1,然后2,然后3)      
  att2<=3 and st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint(120,60), 4326)),5000)), pos) order by pos <-> st_setsrid(st_makepoint(120,60), 4326) limit 100      
),      
b as (      
  select * from tbl_pos where att1=2 and       
  att2<=3 and st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint(120,60), 4326)),5000)), pos) order by pos <-> st_setsrid(st_makepoint(120,60), 4326) limit 100      
),      
c as (      
  select * from tbl_pos where att1=3 and       
  att2<=3 and st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint(120,60), 4326)),5000)), pos) order by pos <-> st_setsrid(st_makepoint(120,60), 4326) limit 100      
)      
select * from a union all select * from b union all select * from c limit 100;      

自动跳过不需要执行的SQL,类似如下

 Limit  (cost=1282.26..1284.26 rows=100 width=56) (actual time=0.663..2.295 rows=32 loops=1)      
   Output: a.id, a.att1, a.att2, a.att3, a.mod_time, a.pos      
   Buffers: shared hit=324      
   CTE a      
     ->  Limit  (cost=0.42..427.51 rows=100 width=64) (actual time=0.651..0.768 rows=10 loops=1)      
           Output: tbl_pos.id, tbl_pos.att1, tbl_pos.att2, tbl_pos.att3, tbl_pos.mod_time, tbl_pos.pos, ((tbl_pos.pos <-> '0101000020E61000000000000000005E400000000000004E40'::geometry))      
           Buffers: shared hit=97      
           ->  Index Scan using idx_tbl_pos_1 on public.tbl_pos  (cost=0.42..3481.20 rows=815 width=64) (actual time=0.650..0.766 rows=10 loops=1)      
                 Output: tbl_pos.id, tbl_pos.att1, tbl_pos.att2, tbl_pos.att3, tbl_pos.mod_time, tbl_pos.pos, (tbl_pos.pos <-> '0101000020E61000000000000000005E400000000000004E40'::geometry)      
                 Index Cond: ((tbl_pos.att1 = 1) AND (tbl_pos.att2 <= 3) AND ('0103000020E61000000100000021000000267F6991BA055E40F420D353BCFF4D40AE7F7C0791055E4004D7AB0D9FFE4D405B8C22C830055E409DBC6E5F8FFD4D40D24FE28E9D045E40FB4032B697FC  4D4043F65109DD035E40B3AD6691C1FB4D407907729EF6025E40CD022A2615FB4D40FCD5B525F3015E401498630F99FA4D40FD598C90DC005E400571980D52FA4D40C3B0A789BDFF5D40F14DD0D842FA4D40F12  00DA1FE5D408B  38066CFA4D4059C7410092FD5D40C53C7702CCFA4D40AFC2A9C    AFC5D40A28FEA205FFB4D40DF6AD2D2C4FB5D408  A3DBF1FFC4D40B73C6B6518FB5D400FF2147C06FD4D40112D1B1F9CFA5D40D0CACF7E0AFE4D404E26B0CD54FA5D40CCE2BECD21FF4D4087E3C53845FA5D403B51A0AF41004E408079E9046EFA5D40CDB8BA145F014E40CF0290ABCDFA5D406B5C9D  036F024E409222728860FB5D40D84C5A0567034E40DB9B09FC20FC5D409F06088D3D044E40B06412A207FD5D40FBB07A56EA044E40B78A179A0BFE5D40963273B866054E40A25449DF22FF5D40D0DCF9E6AD054E4024FC28AC42005E402D883723BD054E4025240AE65F015E40A173DCD693054E4033F  60B8C6F025E4068D3F79933054E4023F6012567035E4013FB0323A0044E4069F3D1273D045E40E1C5CE21DF034E40D515105AE9045E402E7AC106F8024E407F25172165055E40579ED4B8F3014E40D5FF79C2AB055E40E0132A3DDC004E40267F6991BA055E40F420D353BCFF4D40'::geometry ~ tb  l_pos.pos))      
                 Order By: (tbl_pos.pos <-> '0101000020E61000000000000000005E400000000000004E40'::geometry)      
                 Filter: _st_contains('0103000020E61000000100000021000000267F6991BA055E40F420D353BCFF4D40AE7F7C0791055E4004D7AB0D9FFE4D405B8C22C830055E409DBC6E5F8FFD4D40D24FE28E9D045E40FB4032B697FC4D4043F65109DD035E40B3AD6691C1FB4D407907  729EF6025E40CD022A2615FB4D40FCD5B525F3015E401498630F99FA4D40FD598C90DC005E400571980D52FA4D40C3B0A789BDFF5D40F14DD0D842FA4D40F12  00DA1FE5D408B  38066CFA4D4059C7410092FD5D40C53C7702CCFA4D40AFC2A9C  AFC5D40A28FEA205FFB4D40DF6AD2D2C4FB5D408    A3DBF1FFC4D40B73C6B6518FB5D400FF2147C06FD4D40112D1B1F9CFA5D40D0CACF7E0AFE4D404E26B0CD54FA5D40CCE2BECD21FF4D4087E3C53845FA5D403B51A0AF41004E408079E9046EFA5D40CDB8BA145F014E40CF0290ABCDFA5D406B5C9D036F024E409222728860FB5D40D84C5A0567034E  40DB9B09FC20FC5D409F06088D3D044E40B06412A207FD5D40FBB07A56EA044E40B78A179A0BFE5D40963273B866054E40A25449DF22FF5D40D0DCF9E6AD054E4024FC28AC42005E402D883723BD054E4025240AE65F015E40A173DCD693054E4033F60B8C6F025E4068D3F79933054E4023F60125670  35E4013FB0323A0044E4069F3D1273D045E40E1C5CE21DF034E40D515105AE9045E402E7AC106F8024E407F25172165055E40579ED4B8F3014E40D5FF79C2AB055E40E0132A3DDC004E40267F6991BA055E40F420D353BCFF4D40'::geometry, tbl_pos.pos)      
                 Rows Removed by Filter: 3      
                 Buffers: shared hit=97      
   CTE b      
     ->  Limit  (cost=0.42..427.17 rows=100 width=64) (actual time=0.624..0.758 rows=11 loops=1)      
           Output: tbl_pos_1.id, tbl_pos_1.att1, tbl_pos_1.att2, tbl_pos_1.att3, tbl_pos_1.mod_time, tbl_pos_1.pos, ((tbl_pos_1.pos <-> '0101000020E61000000000000000005E400000000000004E40'::geometry))      
           Buffers: shared hit=114      
           ->  Index Scan using idx_tbl_pos_1 on public.tbl_pos tbl_pos_1  (cost=0.42..3478.46 rows=815 width=64) (actual time=0.623..0.756 rows=11 loops=1)      
                 Output: tbl_pos_1.id, tbl_pos_1.att1, tbl_pos_1.att2, tbl_pos_1.att3, tbl_pos_1.mod_time, tbl_pos_1.pos, (tbl_pos_1.pos <-> '0101000020E61000000000000000005E400000000000004E40'::geometry)      
                 Index Cond: ((tbl_pos_1.att1 = 2) AND (tbl_pos_1.att2 <= 3) AND ('0103000020E61000000100000021000000267F6991BA055E40F420D353BCFF4D40AE7F7C0791055E4004D7AB0D9FFE4D405B8C22C830055E409DBC6E5F8FFD4D40D24FE28E9D045E40FB4032B6  97FC4D4043F65109DD035E40B3AD6691C1FB4D407907729EF6025E40CD022A2615FB4D40FCD5B525F3015E401498630F99FA4D40FD598C90DC005E400571980D52FA4D40C3B0A789BDFF5D40F14DD0D842FA4D40F12  00DA1FE5D408B  38066CFA4D4059C7410092FD5D40C53C7702CCFA4D40AFC2A  9C  AFC5D40A28FEA205FFB4D40DF6AD2D2C4FB5D408  A3DBF1FFC4D40B73C6B6518FB5D400FF2147C06FD4D40112D1B1F9CFA5D40D0CACF7E0AFE4D404E26B0CD54FA5D40CCE2BECD21FF4D4087E3C53845FA5D403B51A0AF41004E408079E9046EFA5D40CDB8BA145F014E40CF0290ABCDFA5D406B  5C9D036F024E409222728860FB5D40D84C5A0567034E40DB9B09FC20FC5D409F06088D3D044E40B06412A207FD5D40FBB07A56EA044E40B78A179A0BFE5D40963273B866054E40A25449DF22FF5D40D0DCF9E6AD054E4024FC28AC42005E402D883723BD054E4025240AE65F015E40A173DCD693054E4  033F60B8C6F025E4068D3F79933054E4023F6012567035E4013FB0323A0044E4069F3D1273D045E40E1C5CE21DF034E40D515105AE9045E402E7AC106F8024E407F25172165055E40579ED4B8F3014E40D5FF79C2AB055E40E0132A3DDC004E40267F6991BA055E40F420D353BCFF4D40'::geometry   ~ tbl_pos_1.pos))      
                 Order By: (tbl_pos_1.pos <-> '0101000020E61000000000000000005E400000000000004E40'::geometry)      
                 Filter: _st_contains('0103000020E61000000100000021000000267F6991BA055E40F420D353BCFF4D40AE7F7C0791055E4004D7AB0D9FFE4D405B8C22C830055E409DBC6E5F8FFD4D40D24FE28E9D045E40FB4032B697FC4D4043F65109DD035E40B3AD6691C1FB4D407907  729EF6025E40CD022A2615FB4D40FCD5B525F3015E401498630F99FA4D40FD598C90DC005E400571980D52FA4D40C3B0A789BDFF5D40F14DD0D842FA4D40F12  00DA1FE5D408B  38066CFA4D4059C7410092FD5D40C53C7702CCFA4D40AFC2A9C  AFC5D40A28FEA205FFB4D40DF6AD2D2C4FB5D408    A3DBF1FFC4D40B73C6B6518FB5D400FF2147C06FD4D40112D1B1F9CFA5D40D0CACF7E0AFE4D404E26B0CD54FA5D40CCE2BECD21FF4D4087E3C53845FA5D403B51A0AF41004E408079E9046EFA5D40CDB8BA145F014E40CF0290ABCDFA5D406B5C9D036F024E409222728860FB5D40D84C5A0567034E  40DB9B09FC20FC5D409F06088D3D044E40B06412A207FD5D40FBB07A56EA044E40B78A179A0BFE5D40963273B866054E40A25449DF22FF5D40D0DCF9E6AD054E4024FC28AC42005E402D883723BD054E4025240AE65F015E40A173DCD693054E4033F60B8C6F025E4068D3F79933054E4023F60125670  35E4013FB0323A0044E4069F3D1273D045E40E1C5CE21DF034E40D515105AE9045E402E7AC106F8024E407F25172165055E40579ED4B8F3014E40D5FF79C2AB055E40E0132A3DDC004E40267F6991BA055E40F420D353BCFF4D40'::geometry, tbl_pos_1.pos)      
                 Rows Removed by Filter: 5      
                 Buffers: shared hit=114      
   CTE c      
     ->  Limit  (cost=0.42..427.58 rows=100 width=64) (actual time=0.624..0.720 rows=11 loops=1)      
           Output: tbl_pos_2.id, tbl_pos_2.att1, tbl_pos_2.att2, tbl_pos_2.att3, tbl_pos_2.mod_time, tbl_pos_2.pos, ((tbl_pos_2.pos <-> '0101000020E61000000000000000005E400000000000004E40'::geometry))      
           Buffers: shared hit=113      
           ->  Index Scan using idx_tbl_pos_1 on public.tbl_pos tbl_pos_2  (cost=0.42..3464.70 rows=811 width=64) (actual time=0.623..0.717 rows=11 loops=1)      
                 Output: tbl_pos_2.id, tbl_pos_2.att1, tbl_pos_2.att2, tbl_pos_2.att3, tbl_pos_2.mod_time, tbl_pos_2.pos, (tbl_pos_2.pos <-> '0101000020E61000000000000000005E400000000000004E40'::geometry)      
                 Index Cond: ((tbl_pos_2.att1 = 3) AND (tbl_pos_2.att2 <= 3) AND ('0103000020E61000000100000021000000267F6991BA055E40F420D353BCFF4D40AE7F7C0791055E4004D7AB0D9FFE4D405B8C22C830055E409DBC6E5F8FFD4D40D24FE28E9D045E40FB4032B6  97FC4D4043F65109DD035E40B3AD6691C1FB4D407907729EF6025E40CD022A2615FB4D40FCD5B525F3015E401498630F99FA4D40FD598C90DC005E400571980D52FA4D40C3B0A789BDFF5D40F14DD0D842FA4D40F12  00DA1FE5D408B  38066CFA4D4059C7410092FD5D40C53C7702CCFA4D40AFC2A  9C  AFC5D40A28FEA205FFB4D40DF6AD2D2C4FB5D408  A3DBF1FFC4D40B73C6B6518FB5D400FF2147C06FD4D40112D1B1F9CFA5D40D0CACF7E0AFE4D404E26B0CD54FA5D40CCE2BECD21FF4D4087E3C53845FA5D403B51A0AF41004E408079E9046EFA5D40CDB8BA145F014E40CF0290ABCDFA5D406B  5C9D036F024E409222728860FB5D40D84C5A0567034E40DB9B09FC20FC5D409F06088D3D044E40B06412A207FD5D40FBB07A56EA044E40B78A179A0BFE5D40963273B866054E40A25449DF22FF5D40D0DCF9E6AD054E4024FC28AC42005E402D883723BD054E4025240AE65F015E40A173DCD693054E4  033F60B8C6F025E4068D3F79933054E4023F6012567035E4013FB0323A0044E4069F3D1273D045E40E1C5CE21DF034E40D515105AE9045E402E7AC106F8024E407F25172165055E40579ED4B8F3014E40D5FF79C2AB055E40E0132A3DDC004E40267F6991BA055E40F420D353BCFF4D40'::geometry   ~ tbl_pos_2.pos))      
                 Order By: (tbl_pos_2.pos <-> '0101000020E61000000000000000005E400000000000004E40'::geometry)      
                 Filter: _st_contains('0103000020E61000000100000021000000267F6991BA055E40F420D353BCFF4D40AE7F7C0791055E4004D7AB0D9FFE4D405B8C22C830055E409DBC6E5F8FFD4D40D24FE28E9D045E40FB4032B697FC4D4043F65109DD035E40B3AD6691C1FB4D407907  729EF6025E40CD022A2615FB4D40FCD5B525F3015E401498630F99FA4D40FD598C90DC005E400571980D52FA4D40C3B0A789BDFF5D40F14DD0D842FA4D40F12  00DA1FE5D408B  38066CFA4D4059C7410092FD5D40C53C7702CCFA4D40AFC2A9C  AFC5D40A28FEA205FFB4D40DF6AD2D2C4FB5D408    A3DBF1FFC4D40B73C6B6518FB5D400FF2147C06FD4D40112D1B1F9CFA5D40D0CACF7E0AFE4D404E26B0CD54FA5D40CCE2BECD21FF4D4087E3C53845FA5D403B51A0AF41004E408079E9046EFA5D40CDB8BA145F014E40CF0290ABCDFA5D406B5C9D036F024E409222728860FB5D40D84C5A0567034E  40DB9B09FC20FC5D409F06088D3D044E40B06412A207FD5D40FBB07A56EA044E40B78A179A0BFE5D40963273B866054E40A25449DF22FF5D40D0DCF9E6AD054E4024FC28AC42005E402D883723BD054E4025240AE65F015E40A173DCD693054E4033F60B8C6F025E4068D3F79933054E4023F60125670  35E4013FB0323A0044E4069F3D1273D045E40E1C5CE21DF034E40D515105AE9045E402E7AC106F8024E407F25172165055E40579ED4B8F3014E40D5FF79C2AB055E40E0132A3DDC004E40267F6991BA055E40F420D353BCFF4D40'::geometry, tbl_pos_2.pos)      
                 Rows Removed by Filter: 3      
                 Buffers: shared hit=113      
   ->  Append  (cost=0.00..6.00 rows=300 width=56) (actual time=0.663..2.288 rows=32 loops=1)      
         Buffers: shared hit=324      
         ->  CTE Scan on a  (cost=0.00..2.00 rows=100 width=56) (actual time=0.663..0.786 rows=10 loops=1)      
               Output: a.id, a.att1, a.att2, a.att3, a.mod_time, a.pos      
               Buffers: shared hit=97      
         ->  CTE Scan on b  (cost=0.00..2.00 rows=100 width=56) (actual time=0.625..0.766 rows=11 loops=1)      
               Output: b.id, b.att1, b.att2, b.att3, b.mod_time, b.pos      
               Buffers: shared hit=114      
         ->  CTE Scan on c  (cost=0.00..2.00 rows=100 width=56) (actual time=0.625..0.728 rows=11 loops=1)      
               Output: c.id, c.att1, c.att2, c.att3, c.mod_time, c.pos      
               Buffers: shared hit=113      
 Planning time: 1.959 ms      
 Execution time: 2.362 ms      
(49 rows)      
    id    | att1 | att2 | att3 |          mod_time          |                        pos                               
----------+------+------+------+----------------------------+----------------------------------------------------      
  5097942 |    1 |    3 |    4 | 2018-07-10 17:51:32.653585 | 0101000020E610000000007B4FFB005E4000006056A2004E40      
 16158515 |    1 |    1 |    5 | 2018-07-17 15:43:00.621385 | 0101000020E61000000000C32AFE005E40000068FD69034E40      
 11518286 |    1 |    2 |    4 | 2018-07-17 15:42:34.189407 | 0101000020E61000000000A89FF6005E400000104E7EFC4D40      
 13313866 |    1 |    2 |    4 | 2018-07-10 17:40:  .385905 | 0101000020E610000000001F3097005E4000008C9E2C044E40      
  7959337 |    1 |    2 |    1 | 2018-07-10 17:53:14.8  877 | 0101000020E610000000002D5A60FF5D400000AC3B8AFB4D40      
 12076193 |    1 |    2 |    2 | 2018-07-17 15:37:19.79298  | 0101000020E61000000000A0F570025E4000009658EEFF4D40      
  3666469 |    1 |    2 |    3 | 2018-07-17 15:41:21.49508  | 0101000020E6100000000075875DFD5D4000003CC529024E40      
 11836353 |    1 |    2 |    4 | 2018-07-17 15:41:49.73175  | 0101000020E610000000005A636A025E400000420506FC4D40      
  2562725 |    1 |    3 |    5 | 2018-07-17 15:42:43.744631 | 0101000020E6100000000022EEBF025E40000088  65044E40      
  2433530 |    1 |    1 |    5 | 2018-07-10 17:49:04.626915 | 0101000020E610000000004F0226FC5D400000BE99C7FE4D40      
  5129924 |    2 |    2 |    1 | 2018-07-17 15:42:13.010257 | 0101000020E610000000000468CD005E400000D4ACB9FC4D40      
  5759027 |    2 |    3 |    5 | 2018-07-17 15:42:37.054746 | 0101000020E61000000000C1ADEF005E4000002A5751FC4D40      
  7844609 |    2 |    2 |    5 | 2018-07-10 17:42:32.851153 | 0101000020E61000000000E9F593025E4000005864A0FE4D40      
 12243642 |    2 |    3 |    5 | 2018-07-17 15:41:33.378954 | 0101000020E61000000000D2AAF9005E4000009E7352054E40      
   347785 |    2 |    3 |    5 | 2018-07-17 15:42:28.101822 | 0101000020E610000000003EFC6BFE5D4000001801F2044E40      
 16587252 |    2 |    1 |    3 | 2018-07-17 15:43:00.253373 | 0101000020E610000000006EF1ADFD5D40000012A1D0034E40      
 15918799 |    2 |    2 |    2 | 2018-07-10 17:43:54.153194 | 0101000020E610000000009B1005FE5D400000944F79FB4D40      
  8183081 |    2 |    2 |    4 | 2018-07-17 15:41:29.903525 | 0101000020E61000000000608A70FC5D400000D0E3F8FB4D40      
  5698100 |    2 |    1 |    2 | 2018-07-17 15:42:56.053288 | 0101000020E610000000002FAEB6FB5D400000B21B47004E40      
  1806142 |    2 |    2 |    4 | 2018-07-10 17:53:06.627044 | 0101000020E61000000000D8479A045E400000BC7331FE4D40      
   386427 |    2 |    2 |    5 | 2018-07-10 17:52:26.568013 | 0101000020E610000000001E428BFA5D400000087438014E40      
  8892973 |    3 |    3 |    1 | 2018-07-10 17:44:08.386618 | 0101000020E61000000000031067FF5D4000001A383C014E40      
  2083046 |    3 |    1 |    2 | 2018-07-17 15:42:55.031903 | 0101000020E610000000002B99AE005E400000E007D5014E40      
 13441821 |    3 |    2 |    3 | 2018-07-10 17:41:49.504894 | 0101000020E610000000006675EF005E400000E04E74FB4D40      
  8662140 |    3 |    1 |    4 | 2018-07-17 15:41:42.761599 | 0101000020E61000000000D0F49AFF5D400000DC3C0BFB4D40      
  78  452 |    3 |    3 |    1 | 2018-07-17 15:42:15.954411 | 0101000020E61000000000CA94E7FF5D400000F27727054E40      
 16796301 |    3 |    2 |    4 | 2018-07-10 17:15:10.231126 | 0101000020E6100000000008F571025E400000D2A562024E40      
  1587379 |    3 |    1 |    1 | 2018-07-10 17:53:31.308692 | 0101000020E61000000000ABBFBBFC5D400000EC0B93FB4D40      
  8560096 |    3 |    3 |    2 | 2018-07-17 15:41:46.907464 | 0101000020E610000000001B707F035E4000002A5B09FC4D40      
  5540068 |    3 |    1 |    2 | 2018-07-10 17:42:29.689334 | 0101000020E610000000004C330C055E400000F02624FE4D40      
 17813180 |    3 |    1 |    5 | 2018-07-10 17:50:18.297117 | 0101000020E61000000000B88C95FA5D400000A6C915004E40      
  6424827 |    3 |    3 |    4 | 2018-07-10 17:50:54.958542 | 0101000020E61000000000564E8EFA5D4000002C28BA004E40      
(32 rows)      

需求升华2

除了空间搜索,普通字段筛选条件,还引入数组字段过滤条件

如果业务上还有数组条件的包含查询过滤,可以创建intarray插件,把数组、普通字段、空间字段放到一个GIST索引里面

异或使用单独的gin+gist索引(看哪种效率高)

《PostgreSQL bitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》

例子(使用数组、普通字段、空间字段放到一个GIST索引里面):

1、建表,索引

create table tbl_pos(id int primary key, att1 int, att2 int, att3 int, att4 int[], mod_time timestamp, pos geometry);        
        
create extension btree_gist;       
    
create extension intarray;    
        
create index idx_tbl_pos_1 on tbl_pos using gist(att1, att2, att4, pos);        
    
假设ATT4的取值空间在0到29, 随机选取6个元素    

2、更新用户的最终位置(同样2000万骑手),由于空间移动有一定的速比,所以更新后是基于原有位置的一个相对位移位置。

vi test.sql          
          
\set att1 random(1,5)        
\set att2 random(1,5)        
\set att3 random(1,5)        
\set id random(1,20000000)        
\set x random(120,130)        
\set y random(70,80)        
insert into tbl_pos (id, att1, att2, att3, att4, mod_time, pos) values (:id, :att1, :att2, :att3, array(select (random()*29)::int from generate_series(1,6)), now(), st_setsrid(st_makepoint(:x,:y), 4326)) on conflict (id) do update set pos=st_setsrid(st_makepoint( st_x(tbl_pos.pos)+5-random()*10, st_y(tbl_pos.pos)+5-random()*10), 4326), mod_time=excluded.mod_time;         

压测

pgbench -M prepared -n -r -f ./test.sql -P 5 -c 28 -j 28 -T 120          

数据样本

postgres=# select * from tbl_pos limit 10;    
    id    | att1 | att2 | att3 |        att4         |          mod_time          |                        pos                             
----------+------+------+------+---------------------+----------------------------+----------------------------------------------------    
 10688124 |    1 |    5 |    4 | {20,5,24,29,4,13}   | 2018-07-25 17:57:37.846193 | 0101000020E61000000000000000C05E400000000000C05140    
 11168933 |    3 |    5 |    3 | {14,26,3,25,3,14}   | 2018-07-25 17:57:37.920361 | 0101000020E61000000000000000C05F400000000000805240    
 18166259 |    3 |    2 |    3 | {16,28,3,22,1,0}    | 2018-07-25 17:57:37.920514 | 0101000020E61000000000000000C05E400000000000405340    
   285635 |    5 |    4 |    4 | {20,16,1,20,8,27}   | 2018-07-25 17:57:37.920668 | 0101000020E61000000000000000005E400000000000405240    
 16686877 |    3 |    4 |    1 | {25,15,7,8,20,2}    | 2018-07-25 17:57:37.920848 | 0101000020E61000000000000000805F400000000000005240    
 13619811 |    5 |    5 |    5 | {12,5,4,28,3,6}     | 2018-07-25 17:57:37.921826 | 0101000020E610000000000000000060400000000000C05140    
 19075025 |    1 |    2 |    1 | {22,4,7,27,24,23}   | 2018-07-25 17:57:37.921984 | 0101000020E61000000000000000805F400000000000405340    
 14410958 |    2 |    1 |    4 | {15,2,25,14,27,11}  | 2018-07-25 17:57:37.922141 | 0101000020E61000000000000000C05F400000000000405340    
   280895 |    3 |    4 |    5 | {27,16,20,12,28,24} | 2018-07-25 17:57:37.922475 | 0101000020E610000000000000000060400000000000C05240    
  2697548 |    3 |    5 |    5 | {28,27,22,20,3,29}  | 2018-07-25 17:57:37.922745 | 0101000020E61000000000000000005E400000000000C05340    
(10 rows)    

3、根据用户输入的att2, att4, 空间 条件,搜索满足条件的附近5公里内的对象,根据距离顺序返回100条。

create or replace function get_res(i_att2 int, i_att4_1 int, i_att4_2 int, dis int)     
returns setof tbl_pos as $$    
declare    
  x float8 := 120+random()*1;    
  y float8 := 59+random()*1;    
begin    
  return query     
with       
a as (      
  select * from tbl_pos where att1=1 and      
  att2<=i_att2 and     
  st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint(x,y), 4326)),dis)), pos) and    
  att4 @> array[i_att4_1, i_att4_2]    
  order by pos <-> st_setsrid(st_makepoint(x,y), 4326) limit 100      
),      
b as (      
  select * from tbl_pos where att1=2 and       
  att2<=i_att2 and     
  st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint(x,y), 4326)),dis)), pos) and    
  att4 @> array[i_att4_1, i_att4_2]    
  order by pos <-> st_setsrid(st_makepoint(x,y), 4326) limit 100      
),      
c as (      
  select * from tbl_pos where att1=3 and       
  att2<=i_att2 and     
  st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint(x,y), 4326)),dis)), pos) and    
  att4 @> array[i_att4_1, i_att4_2]    
  order by pos <-> st_setsrid(st_makepoint(x,y), 4326) limit 100      
)      
select * from a union all select * from b union all select * from c limit 100;       
end;    
$$ language plpgsql strict;    

执行计划

load 'auto_explain';    
set auto_explain.log_buffer =on;    
set auto_explain.log_min_duration =0;    
set auto_explain.log_nested_statements =on;    
set auto_explain.log_timing =on;    
set auto_explain.log_verbose =on;    
set client_min_messages =log;    
    
postgres=# select * from get_res(1,2,9,500000);    
LOG:  duration: 0.020 ms  plan:    
Query Text: SELECT proj4text FROM public.spatial_ref_sys WHERE srid = 4326 LIMIT 1    
Limit  (cost=0.28..2.50 rows=1 width=116) (actual time=0.017..0.017 rows=1 loops=1)    
  Output: proj4text    
  ->  Index Scan using spatial_ref_sys_pkey on public.spatial_ref_sys  (cost=0.28..2.50 rows=1 width=116) (actual time=0.016..0.016 rows=1 loops=1)    
        Output: proj4text    
        Index Cond: (spatial_ref_sys.srid = 4326)    
LOG:  duration: 0.010 ms  plan:    
Query Text: SELECT proj4text FROM public.spatial_ref_sys WHERE srid = 4326 LIMIT 1    
Limit  (cost=0.28..2.50 rows=1 width=116) (actual time=0.007..0.007 rows=1 loops=1)    
  Output: proj4text    
  ->  Index Scan using spatial_ref_sys_pkey on public.spatial_ref_sys  (cost=0.28..2.50 rows=1 width=116) (actual time=0.007..0.007 rows=1 loops=1)    
        Output: proj4text    
        Index Cond: (spatial_ref_sys.srid = 4326)    
LOG:  duration: 0.985 ms  plan:    
Query Text: SELECT public.geography(public.ST_Transform(public.ST_Buffer(public.ST_Transform(public.geometry($1), public._ST_BestSRID($1)), $2), 4326))    
Result  (cost=0.00..0.03 rows=1 width=32) (actual time=0.982..0.982 rows=1 loops=1)    
  Output: geography(st_transform(st_buffer(st_transform(geometry($1), _st_bestsrid($1, $1)), $2), 4326))    
LOG:  duration: 0.009 ms  plan:    
Query Text: SELECT proj4text FROM public.spatial_ref_sys WHERE srid = 4326 LIMIT 1    
Limit  (cost=0.28..2.50 rows=1 width=116) (actual time=0.007..0.007 rows=1 loops=1)    
  Output: proj4text    
  ->  Index Scan using spatial_ref_sys_pkey on public.spatial_ref_sys  (cost=0.28..2.50 rows=1 width=116) (actual time=0.007..0.007 rows=1 loops=1)    
        Output: proj4text    
        Index Cond: (spatial_ref_sys.srid = 4326)    
LOG:  duration: 0.008 ms  plan:    
Query Text: SELECT proj4text FROM public.spatial_ref_sys WHERE srid = 4326 LIMIT 1    
Limit  (cost=0.28..2.50 rows=1 width=116) (actual time=0.007..0.007 rows=1 loops=1)    
  Output: proj4text    
  ->  Index Scan using spatial_ref_sys_pkey on public.spatial_ref_sys  (cost=0.28..2.50 rows=1 width=116) (actual time=0.006..0.006 rows=1 loops=1)    
        Output: proj4text    
        Index Cond: (spatial_ref_sys.srid = 4326)    
LOG:  duration: 0.524 ms  plan:    
Query Text: SELECT public.geography(public.ST_Transform(public.ST_Buffer(public.ST_Transform(public.geometry($1), public._ST_BestSRID($1)), $2), 4326))    
Result  (cost=0.00..0.03 rows=1 width=32) (actual time=0.522..0.522 rows=1 loops=1)    
  Output: geography(st_transform(st_buffer(st_transform(geometry($1), _st_bestsrid($1, $1)), $2), 4326))    
LOG:  duration: 0.010 ms  plan:    
Query Text: SELECT proj4text FROM public.spatial_ref_sys WHERE srid = 4326 LIMIT 1    
Limit  (cost=0.28..2.50 rows=1 width=116) (actual time=0.007..0.007 rows=1 loops=1)    
  Output: proj4text    
  ->  Index Scan using spatial_ref_sys_pkey on public.spatial_ref_sys  (cost=0.28..2.50 rows=1 width=116) (actual time=0.007..0.007 rows=1 loops=1)    
        Output: proj4text    
        Index Cond: (spatial_ref_sys.srid = 4326)    
LOG:  duration: 0.009 ms  plan:    
Query Text: SELECT proj4text FROM public.spatial_ref_sys WHERE srid = 4326 LIMIT 1    
Limit  (cost=0.28..2.50 rows=1 width=116) (actual time=0.007..0.007 rows=1 loops=1)    
  Output: proj4text    
  ->  Index Scan using spatial_ref_sys_pkey on public.spatial_ref_sys  (cost=0.28..2.50 rows=1 width=116) (actual time=0.007..0.007 rows=1 loops=1)    
        Output: proj4text    
        Index Cond: (spatial_ref_sys.srid = 4326)    
LOG:  duration: 0.550 ms  plan:    
Query Text: SELECT public.geography(public.ST_Transform(public.ST_Buffer(public.ST_Transform(public.geometry($1), public._ST_BestSRID($1)), $2), 4326))    
Result  (cost=0.00..0.03 rows=1 width=32) (actual time=0.548..0.548 rows=1 loops=1)    
  Output: geography(st_transform(st_buffer(st_transform(geometry($1), _st_bestsrid($1, $1)), $2), 4326))    
LOG:  duration: 8.519 ms  plan:    
Query Text: with       
a as (      
  select * from tbl_pos where att1=1 and      
  att2<=i_att2 and     
  st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint(x,y), 4326)),dis)), pos) and    
  att4 @> array[i_att4_1, i_att4_2]    
  order by pos <-> st_setsrid(st_makepoint(x,y), 4326) limit 100      
),      
b as (      
  select * from tbl_pos where att1=2 and       
  att2<=i_att2 and     
  st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint(x,y), 4326)),dis)), pos) and    
  att4 @> array[i_att4_1, i_att4_2]    
  order by pos <-> st_setsrid(st_makepoint(x,y), 4326) limit 100      
),      
c as (      
  select * from tbl_pos where att1=3 and       
  att2<=i_att2 and     
  st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint(x,y), 4326)),dis)), pos) and    
  att4 @> array[i_att4_1, i_att4_2]    
  order by pos <-> st_setsrid(st_makepoint(x,y), 4326) limit 100      
)      
select * from a union all select * from b union all select * from c limit 100    
Limit  (cost=103.79..104.27 rows=24 width=88) (actual time=0.579..8.479 rows=100 loops=1)    
  Output: a.id, a.att1, a.att2, a.att3, a.att4, a.mod_time, a.pos    
  CTE a    
    ->  Limit  (cost=0.42..34.60 rows=8 width=109) (actual time=0.575..7.345 rows=85 loops=1)    
          Output: tbl_pos.id, tbl_pos.att1, tbl_pos.att2, tbl_pos.att3, tbl_pos.att4, tbl_pos.mod_time, tbl_pos.pos, ((tbl_pos.pos <-> '0101000020E61000000080A216312D5E400000B15EF8FC4D40'::geometry))    
          ->  Index Scan using idx_tbl_pos_1 on public.tbl_pos  (cost=0.42..34.60 rows=8 width=109) (actual time=0.575..7.329 rows=85 loops=1)    
                Output: tbl_pos.id, tbl_pos.att1, tbl_pos.att2, tbl_pos.att3, tbl_pos.att4, tbl_pos.mod_time, tbl_pos.pos, (tbl_pos.pos <-> '0101000020E61000000080A216312D5E400000B15EF8FC4D40'::geometry)    
                Index Cond: ((tbl_pos.att1 = 1) AND (tbl_pos.att2 <= 1) AND (tbl_pos.att4 @> '{2,9}'::integer[]) AND ('0103000020E6100000010000002100000008956E948B34604005B6F7880FEA4D406AD812B7D429604033471D5EB97B4D4088C40953AC156040B4751DAF65134D40775090B7A5F25F40A738CEDB85B44C401511453031AC5F40B867373A13624C40688DF186A75A5F40C706A1C6891E4C408B8A2558B7005F402385A953E3EB4B40553DF2680DA15E40260EB9C892CB4B4087862191533E5E40C342B6ED7FBE4B40EADA06E730DB5D40ED5B41A204C54B4005DF006E4A7A5D40BF19753CECDE4B403B6DA206441E5D40FB6AB34E750B4C4024BB556BC1C95C402B536B7655494C40693A2B9F667F5C40515B3A56BE964C40B976E367D6415C4015E8CAB362F14C40155E7D4BAC135C40100997027B564D4054B2BA3A6EF75B409B3A047FC9C24D4098594DE570EF5B404ED001599F324E40F1925A4DA9FD5B40DC4ACF26E6A14E4075FF27B267235C409837C69D320C4F401283CEECFC605C40BFF7D3BBE66C4F40D7CD8F2C55B55C408949A20C69BF4F40BAC94AC0A01D5D40ACCED43073FF4F40C0B7C4A62F955D408416E63EB9145040EB780B55A4155E403901B1E0751D50400592A9238C975E40DD1387B065195040F7229DCF43135F40DB128194C50850400C5C1A29EA815F40CA3AD2F121D94F401BA10DD11BDE5F405474586DC28C4F4094A4631E2712604052096DA47C304F401625C60A652960406E5A3804DAC84E40FB9EF3C3B13460409548397D865A4E4008956E948B34604005B6F7880FEA4D40'::geometry ~ tbl_pos.pos))    
                Order By: (tbl_pos.pos <-> '0101000020E61000000080A216312D5E400000B15EF8FC4D40'::geometry)    
                Filter: _st_contains('0103000020E6100000010000002100000008956E948B34604005B6F7880FEA4D406AD812B7D429604033471D5EB97B4D4088C40953AC156040B4751DAF65134D40775090B7A5F25F40A738CEDB85B44C401511453031AC5F40B867373A13624C40688DF186A75A5F40C706A1C6891E4C408B8A2558B7005F402385A953E3EB4B40553DF2680DA15E40260EB9C892CB4B4087862191533E5E40C342B6ED7FBE4B40EADA06E730DB5D40ED5B41A204C54B4005DF006E4A7A5D40BF19753CECDE4B403B6DA206441E5D40FB6AB34E750B4C4024BB556BC1C95C402B536B7655494C40693A2B9F667F5C40515B3A56BE964C40B976E367D6415C4015E8CAB362F14C40155E7D4BAC135C40100997027B564D4054B2BA3A6EF75B409B3A047FC9C24D4098594DE570EF5B404ED001599F324E40F1925A4DA9FD5B40DC4ACF26E6A14E4075FF27B267235C409837C69D320C4F401283CEECFC605C40BFF7D3BBE66C4F40D7CD8F2C55B55C408949A20C69BF4F40BAC94AC0A01D5D40ACCED43073FF4F40C0B7C4A62F955D408416E63EB9145040EB780B55A4155E403901B1E0751D50400592A9238C975E40DD1387B065195040F7229DCF43135F40DB128194C50850400C5C1A29EA815F40CA3AD2F121D94F401BA10DD11BDE5F405474586DC28C4F4094A4631E2712604052096DA47C304F401625C60A652960406E5A3804DAC84E40FB9EF3C3B13460409548397D865A4E4008956E948B34604005B6F7880FEA4D40'::geometry, tbl_pos.pos)    
                Rows Removed by Filter: 37    
  CTE b    
    ->  Limit  (cost=0.42..34.60 rows=8 width=109) (actual time=0.469..1.018 rows=15 loops=1)    
          Output: tbl_pos_1.id, tbl_pos_1.att1, tbl_pos_1.att2, tbl_pos_1.att3, tbl_pos_1.att4, tbl_pos_1.mod_time, tbl_pos_1.pos, ((tbl_pos_1.pos <-> '0101000020E61000000080A216312D5E400000B15EF8FC4D40'::geometry))    
          ->  Index Scan using idx_tbl_pos_1 on public.tbl_pos tbl_pos_1  (cost=0.42..34.60 rows=8 width=109) (actual time=0.468..1.015 rows=15 loops=1)    
                Output: tbl_pos_1.id, tbl_pos_1.att1, tbl_pos_1.att2, tbl_pos_1.att3, tbl_pos_1.att4, tbl_pos_1.mod_time, tbl_pos_1.pos, (tbl_pos_1.pos <-> '0101000020E61000000080A216312D5E400000B15EF8FC4D40'::geometry)    
                Index Cond: ((tbl_pos_1.att1 = 2) AND (tbl_pos_1.att2 <= 1) AND (tbl_pos_1.att4 @> '{2,9}'::integer[]) AND ('0103000020E6100000010000002100000008956E948B34604005B6F7880FEA4D406AD812B7D429604033471D5EB97B4D4088C40953AC156040B4751DAF65134D40775090B7A5F25F40A738CEDB85B44C401511453031AC5F40B867373A13624C40688DF186A75A5F40C706A1C6891E4C408B8A2558B7005F402385A953E3EB4B40553DF2680DA15E40260EB9C892CB4B4087862191533E5E40C342B6ED7FBE4B40EADA06E730DB5D40ED5B41A204C54B4005DF006E4A7A5D40BF19753CECDE4B403B6DA206441E5D40FB6AB34E750B4C4024BB556BC1C95C402B536B7655494C40693A2B9F667F5C40515B3A56BE964C40B976E367D6415C4015E8CAB362F14C40155E7D4BAC135C40100997027B564D4054B2BA3A6EF75B409B3A047FC9C24D4098594DE570EF5B404ED001599F324E40F1925A4DA9FD5B40DC4ACF26E6A14E4075FF27B267235C409837C69D320C4F401283CEECFC605C40BFF7D3BBE66C4F40D7CD8F2C55B55C408949A20C69BF4F40BAC94AC0A01D5D40ACCED43073FF4F40C0B7C4A62F955D408416E63EB9145040EB780B55A4155E403901B1E0751D50400592A9238C975E40DD1387B065195040F7229DCF43135F40DB128194C50850400C5C1A29EA815F40CA3AD2F121D94F401BA10DD11BDE5F405474586DC28C4F4094A4631E2712604052096DA47C304F401625C60A652960406E5A3804DAC84E40FB9EF3C3B13460409548397D865A4E4008956E948B34604005B6F7880FEA4D40'::geometry ~ tbl_pos_1.pos))    
                Order By: (tbl_pos_1.pos <-> '0101000020E61000000080A216312D5E400000B15EF8FC4D40'::geometry)    
                Filter: _st_contains('0103000020E6100000010000002100000008956E948B34604005B6F7880FEA4D406AD812B7D429604033471D5EB97B4D4088C40953AC156040B4751DAF65134D40775090B7A5F25F40A738CEDB85B44C401511453031AC5F40B867373A13624C40688DF186A75A5F40C706A1C6891E4C408B8A2558B7005F402385A953E3EB4B40553DF2680DA15E40260EB9C892CB4B4087862191533E5E40C342B6ED7FBE4B40EADA06E730DB5D40ED5B41A204C54B4005DF006E4A7A5D40BF19753CECDE4B403B6DA206441E5D40FB6AB34E750B4C4024BB556BC1C95C402B536B7655494C40693A2B9F667F5C40515B3A56BE964C40B976E367D6415C4015E8CAB362F14C40155E7D4BAC135C40100997027B564D4054B2BA3A6EF75B409B3A047FC9C24D4098594DE570EF5B404ED001599F324E40F1925A4DA9FD5B40DC4ACF26E6A14E4075FF27B267235C409837C69D320C4F401283CEECFC605C40BFF7D3BBE66C4F40D7CD8F2C55B55C408949A20C69BF4F40BAC94AC0A01D5D40ACCED43073FF4F40C0B7C4A62F955D408416E63EB9145040EB780B55A4155E403901B1E0751D50400592A9238C975E40DD1387B065195040F7229DCF43135F40DB128194C50850400C5C1A29EA815F40CA3AD2F121D94F401BA10DD11BDE5F405474586DC28C4F4094A4631E2712604052096DA47C304F401625C60A652960406E5A3804DAC84E40FB9EF3C3B13460409548397D865A4E4008956E948B34604005B6F7880FEA4D40'::geometry, tbl_pos_1.pos)    
  CTE c    
    ->  Limit  (cost=0.42..34.60 rows=8 width=109) (never executed)    
          Output: tbl_pos_2.id, tbl_pos_2.att1, tbl_pos_2.att2, tbl_pos_2.att3, tbl_pos_2.att4, tbl_pos_2.mod_time, tbl_pos_2.pos, ((tbl_pos_2.pos <-> '0101000020E61000000080A216312D5E400000B15EF8FC4D40'::geometry))    
          ->  Index Scan using idx_tbl_pos_1 on public.tbl_pos tbl_pos_2  (cost=0.42..34.60 rows=8 width=109) (never executed)    
                Output: tbl_pos_2.id, tbl_pos_2.att1, tbl_pos_2.att2, tbl_pos_2.att3, tbl_pos_2.att4, tbl_pos_2.mod_time, tbl_pos_2.pos, (tbl_pos_2.pos <-> '0101000020E61000000080A216312D5E400000B15EF8FC4D40'::geometry)    
                Index Cond: ((tbl_pos_2.att1 = 3) AND (tbl_pos_2.att2 <= 1) AND (tbl_pos_2.att4 @> '{2,9}'::integer[]) AND ('0103000020E6100000010000002100000008956E948B34604005B6F7880FEA4D406AD812B7D429604033471D5EB97B4D4088C40953AC156040B4751DAF65134D40775090B7A5F25F40A738CEDB85B44C401511453031AC5F40B867373A13624C40688DF186A75A5F40C706A1C6891E4C408B8A2558B7005F402385A953E3EB4B40553DF2680DA15E40260EB9C892CB4B4087862191533E5E40C342B6ED7FBE4B40EADA06E730DB5D40ED5B41A204C54B4005DF006E4A7A5D40BF19753CECDE4B403B6DA206441E5D40FB6AB34E750B4C4024BB556BC1C95C402B536B7655494C40693A2B9F667F5C40515B3A56BE964C40B976E367D6415C4015E8CAB362F14C40155E7D4BAC135C40100997027B564D4054B2BA3A6EF75B409B3A047FC9C24D4098594DE570EF5B404ED001599F324E40F1925A4DA9FD5B40DC4ACF26E6A14E4075FF27B267235C409837C69D320C4F401283CEECFC605C40BFF7D3BBE66C4F40D7CD8F2C55B55C408949A20C69BF4F40BAC94AC0A01D5D40ACCED43073FF4F40C0B7C4A62F955D408416E63EB9145040EB780B55A4155E403901B1E0751D50400592A9238C975E40DD1387B065195040F7229DCF43135F40DB128194C50850400C5C1A29EA815F40CA3AD2F121D94F401BA10DD11BDE5F405474586DC28C4F4094A4631E2712604052096DA47C304F401625C60A652960406E5A3804DAC84E40FB9EF3C3B13460409548397D865A4E4008956E948B34604005B6F7880FEA4D40'::geometry ~ tbl_pos_2.pos))    
                Order By: (tbl_pos_2.pos <-> '0101000020E61000000080A216312D5E400000B15EF8FC4D40'::geometry)    
                Filter: _st_contains('0103000020E6100000010000002100000008956E948B34604005B6F7880FEA4D406AD812B7D429604033471D5EB97B4D4088C40953AC156040B4751DAF65134D40775090B7A5F25F40A738CEDB85B44C401511453031AC5F40B867373A13624C40688DF186A75A5F40C706A1C6891E4C408B8A2558B7005F402385A953E3EB4B40553DF2680DA15E40260EB9C892CB4B4087862191533E5E40C342B6ED7FBE4B40EADA06E730DB5D40ED5B41A204C54B4005DF006E4A7A5D40BF19753CECDE4B403B6DA206441E5D40FB6AB34E750B4C4024BB556BC1C95C402B536B7655494C40693A2B9F667F5C40515B3A56BE964C40B976E367D6415C4015E8CAB362F14C40155E7D4BAC135C40100997027B564D4054B2BA3A6EF75B409B3A047FC9C24D4098594DE570EF5B404ED001599F324E40F1925A4DA9FD5B40DC4ACF26E6A14E4075FF27B267235C409837C69D320C4F401283CEECFC605C40BFF7D3BBE66C4F40D7CD8F2C55B55C408949A20C69BF4F40BAC94AC0A01D5D40ACCED43073FF4F40C0B7C4A62F955D408416E63EB9145040EB780B55A4155E403901B1E0751D50400592A9238C975E40DD1387B065195040F7229DCF43135F40DB128194C50850400C5C1A29EA815F40CA3AD2F121D94F401BA10DD11BDE5F405474586DC28C4F4094A4631E2712604052096DA47C304F401625C60A652960406E5A3804DAC84E40FB9EF3C3B13460409548397D865A4E4008956E948B34604005B6F7880FEA4D40'::geometry, tbl_pos_2.pos)    
  ->  Append  (cost=0.00..0.48 rows=24 width=88) (actual time=0.579..8.461 rows=100 loops=1)    
        ->  CTE Scan on a  (cost=0.00..0.16 rows=8 width=88) (actual time=0.578..7.413 rows=85 loops=1)    
              Output: a.id, a.att1, a.att2, a.att3, a.att4, a.mod_time, a.pos    
        ->  CTE Scan on b  (cost=0.00..0.16 rows=8 width=88) (actual time=0.471..1.028 rows=15 loops=1)    
              Output: b.id, b.att1, b.att2, b.att3, b.att4, b.mod_time, b.pos    
        ->  CTE Scan on c  (cost=0.00..0.16 rows=8 width=88) (never executed)    
              Output: c.id, c.att1, c.att2, c.att3, c.att4, c.mod_time, c.pos    
LOG:  duration: 12.365 ms  plan:    
Query Text: select * from get_res(1,2,9,500000);    
Function Scan on public.get_res  (cost=0.25..10.25 rows=1000 width=88) (actual time=12.175..12.185 rows=100 loops=1)    
  Output: id, att1, att2, att3, att4, mod_time, pos    
  Function Call: get_res(1, 2, 9, 500000)    

压测脚本

vi test1.sql        
          
\set att2 random(1,5)        
\set att4_1 random(0,29)    
\set att4_2 random(0,29)    
select * from get_res(:att2, :att4_1, :att4_2, 500000);    

压测

pgbench -M prepared -n -r -f ./test1.sql -P 5 -c 56 -j 56 -T 300          

压测结果

1、更新对象位置

transaction type: ./test.sql    
scaling factor: 1    
query mode: prepared    
number of clients: 28    
number of threads: 28    
duration: 120 s    
number of transactions actually processed: 11605219    
latency average = 0.290 ms    
latency stddev = 0.957 ms    
tps = 96708.804105 (including connections establishing)    
tps = 96714.596970 (excluding connections establishing)    
script statistics:    
 - statement latencies in milliseconds:    
         0.002  \set att1 random(1,5)        
         0.000  \set att2 random(1,5)        
         0.000  \set att3 random(1,5)        
         0.000  \set id random(1,20000000)        
         0.000  \set x random(120,130)        
         0.000  \set y random(70,80)        
         0.286  insert into tbl_pos (id, att1, att2, att3, att4, mod_time, pos) values (:id, :att1, :att2, :att3, array(select (random()*29)::int from generate_series(1,6)), now(), st_setsrid(st_makepoint(:x,:y), 4326)) on conflict (id) do update set pos=st_setsrid(st_makepoint( st_x(tbl_pos.pos)+5-random()*10, st_y(tbl_pos.pos)+5-random()*10), 4326), mod_time=excluded.mod_time;    

TPS: 96708

平均响应时间: 0.286 毫秒

2、根据用户输入的att2, att4,空间过滤组合条件,搜索满足条件的附近500000米内的对象,根据距离顺序返回100条。

transaction type: ./test1.sql    
scaling factor: 1    
query mode: prepared    
number of clients: 56    
number of threads: 56    
duration: 120 s    
number of transactions actually processed: 600815    
latency average = 11.184 ms    
latency stddev = 2.410 ms    
tps = 5005.566075 (including connections establishing)    
tps = 5006.063092 (excluding connections establishing)    
script statistics:    
 - statement latencies in milliseconds:    
         0.003  \set att2 random(1,5)        
         0.001  \set att4_1 random(0,29)    
         0.000  \set att4_2 random(0,29)    
        11.182  select * from get_res(:att2, :att4_1, :att4_2, 500000);    

TPS: 5005

平均响应时间: 11 毫秒

小结2

通过intarray , btree_gist插件,使得gist索引接口同时支持了普通类型、数组类型、空间类型的复合索引。查询效率上大幅提升。

在st_makepoint中使用绑定变量引入的执行计划问题导致的性能问题优化

《PostgreSQL Oracle 兼容性之 - PL/SQL DETERMINISTIC 与PG函数稳定性(immutable, stable, volatile)》

《PostgreSQL 函数稳定性与constraint_excluded分区表逻辑推理过滤的CASE》

《函数稳定性讲解 - retalk PostgreSQL function's [ volatile|stable|immutable ]》

《函数稳定性讲解 - 函数索引思考, pay attention to function index used in PostgreSQL》

《函数稳定性讲解 - Thinking PostgreSQL Function's Volatility Categories》

当使用绑定变量时,例如本例中用到了ST函数,构建随机点,作为输入。那么绑定变量是用x,y坐标的float8呢,还是用geometry呢?

例如

prepare s (float8,float8,float8,float8) as   
select * from tbl_pos   
where att1=1 and att2<5 and   
st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint($1,$2), 4326)),5000)), pos)   
order by pos <-> st_setsrid(st_makepoint($3,$4), 4326) asc   
limit 100;  

或者

prepare s (int,int,geometry,float8) as   
select * from tbl_pos   
where att1=$1 and att2<$2 and   
st_contains(geometry(ST_Buffer(geography($3),$4)), pos)     
order by pos <-> $3 limit 100;  

差别

以上两种bind方法,实际上在优化器端看到的是有非常巨大的差别的,操作符能不能用索引过滤,取决于输入条件是不是常量,或者stable、immutable function。

如果BIND在st_makepoint里面,可能导致在使用prepared statement generic plan时,不能使用正确的索引。

postgres=# \d tbl_pos  
                         Table "public.tbl_pos"  
  Column  |            Type             | Collation | Nullable | Default   
----------+-----------------------------+-----------+----------+---------  
 id       | integer                     |           | not null |   
 att1     | integer                     |           |          |   
 att2     | integer                     |           |          |   
 att3     | integer                     |           |          |   
 att4     | integer[]                   |           |          |   
 mod_time | timestamp without time zone |           |          |   
 pos      | geometry                    |           |          |   
Indexes:  
    "tbl_pos_pkey" PRIMARY KEY, btree (id)  
    "idx_tbl_pos_1" gist (att1, att2, att4, pos)  

1、BIND到st_makepoint里面

prepare s (float8,float8,float8,float8) as   
select * from tbl_pos   
where att1=1 and att2<5 and   
st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint($1,$2), 4326)),5000)), pos)   
order by pos <-> st_setsrid(st_makepoint($3,$4), 4326) asc   
limit 100;  

前五次执行计划都是custom plan,过滤条件过滤att1,att2条件,而且过滤空间条件。

postgres=# explain execute s(120,60,120,60);  
  
 Limit  (cost=0.42..418.66 rows=100 width=96)  
   ->  Index Scan using idx_tbl_pos_1 on tbl_pos  (cost=0.42..4002.96 rows=957 width=96)  
         Index Cond: ((att1 = 1) AND (att2 < 5) AND ('0103000020E61000000100000021000000267F6991BA055E40F420D353BCFF4D40AE7F7C0791055E4004D7AB0D9FFE4D405B8C22C830055E409DBC6E5F8FFD4D40D24FE28E9D045E40FB4032B697FC4D4043F65109DD035E40B3AD6  
691C1FB4D407907729EF6025E40CD022A2615FB4D40FCD5B525F3015E401498630F99FA4D40FD598C90DC005E400571980D52FA4D40C3B0A789BDFF5D40F14DD0D842FA4D40F123900DA1FE5D408B3938066CFA4D4059C7410092FD5D40C53C7702CCFA4D40AFC2A9C39AFC5D40A28FEA205FFB4D40DF  
6AD2D2C4FB5D40839A3DBF1FFC4D40B73C6B6518FB5D400FF2147C06FD4D40112D1B1F9CFA5D40D0CACF7E0AFE4D404E26B0CD54FA5D40CCE2BECD21FF4D4087E3C53845FA5D403B51A0AF41004E408079E9046EFA5D40CDB8BA145F014E40CF0290ABCDFA5D406B5C9D036F024E409222728860FB5D4  
0D84C5A0567034E40DB9B09FC20FC5D409F06088D3D044E40B06412A207FD5D40FBB07A56EA044E40B78A179A0BFE5D40963273B866054E40A25449DF22FF5D40D0DCF9E6AD054E4024FC28AC42005E402D883723BD054E4025240AE65F015E40A173DCD693054E4033F60B8C6F025E4068D3F7993305  
4E4023F6012567035E4013FB0323A0044E4069F3D1273D045E40E1C5CE21DF034E40D515105AE9045E402E7AC106F8024E407F25172165055E40579ED4B8F3014E40D5FF79C2AB055E40E0132A3DDC004E40267F6991BA055E40F420D353BCFF4D40'::geometry ~ pos))  
         Order By: (pos <-> '0101000020E61000000000000000005E400000000000004E40'::geometry)  
         Filter: _st_contains('0103000020E61000000100000021000000267F6991BA055E40F420D353BCFF4D40AE7F7C0791055E4004D7AB0D9FFE4D405B8C22C830055E409DBC6E5F8FFD4D40D24FE28E9D045E40FB4032B697FC4D4043F65109DD035E40B3AD6691C1FB4D407907729EF602  
5E40CD022A2615FB4D40FCD5B525F3015E401498630F99FA4D40FD598C90DC005E400571980D52FA4D40C3B0A789BDFF5D40F14DD0D842FA4D40F123900DA1FE5D408B3938066CFA4D4059C7410092FD5D40C53C7702CCFA4D40AFC2A9C39AFC5D40A28FEA205FFB4D40DF6AD2D2C4FB5D40839A3DBF1  
FFC4D40B73C6B6518FB5D400FF2147C06FD4D40112D1B1F9CFA5D40D0CACF7E0AFE4D404E26B0CD54FA5D40CCE2BECD21FF4D4087E3C53845FA5D403B51A0AF41004E408079E9046EFA5D40CDB8BA145F014E40CF0290ABCDFA5D406B5C9D036F024E409222728860FB5D40D84C5A0567034E40DB9B09  
FC20FC5D409F06088D3D044E40B06412A207FD5D40FBB07A56EA044E40B78A179A0BFE5D40963273B866054E40A25449DF22FF5D40D0DCF9E6AD054E4024FC28AC42005E402D883723BD054E4025240AE65F015E40A173DCD693054E4033F60B8C6F025E4068D3F79933054E4023F6012567035E4013F  
B0323A0044E4069F3D1273D045E40E1C5CE21DF034E40D515105AE9045E402E7AC106F8024E407F25172165055E40579ED4B8F3014E40D5FF79C2AB055E40E0132A3DDC004E40267F6991BA055E40F420D353BCFF4D40'::geometry, pos)  
(5 rows)  

5次后,generic plan变成如下执行计划,过滤条件变成只过滤att1,att2条件,而不能过滤空间条件

《执行计划选择算法 与 绑定变量 - PostgreSQL prepared statement: SPI_prepare, prepare|execute COMMAND, PL/pgsql STYLE: custom & generic plan cache》

                                                                                                                                                  QUERY PLAN                                                                                   
                                                                    
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------------  
 Limit  (cost=0.42..167.38 rows=100 width=96)  
   ->  Index Scan using idx_tbl_pos_1 on tbl_pos  (cost=0.42..1597125.56 rows=956603 width=96)  
         Index Cond: ((att1 = 1) AND (att2 < 5))  
         Order By: (pos <-> st_setsrid(st_makepoint($3, $4), 4326))  
         Filter: st_contains(geometry(geography(st_transform(st_buffer(st_transform(geometry(geography(st_setsrid(st_makepoint($1, $2), 4326))), _st_bestsrid(geography(st_setsrid(st_makepoint($1, $2), 4326)), geography(st_setsrid(st_make  
point($1, $2), 4326)))), '5000'::double precision), 4326))), pos)  
(5 rows)  

2、BIND为geometry类型

postgres=# prepare s (int,int,geometry,float8) as   
select * from tbl_pos   
where att1=$1 and att2<$2 and   
st_contains(geometry(ST_Buffer(geography($3),$4)), pos)     
order by pos <-> $3 limit 100;  
PREPARE  

一直都是一样的执行计划,索引作为了att1,att2,以及空间三个过滤条件

 Limit  (cost=0.42..418.66 rows=100 width=96)  
   ->  Index Scan using idx_tbl_pos_1 on tbl_pos  (cost=0.42..4002.96 rows=957 width=96)  
         Index Cond: ((att1 = 1) AND (att2 < 5) AND ('0103000020E61000000100000021000000267F6991BA055E40F420D353BCFF4D40AE7F7C0791055E4004D7AB0D9FFE4D405B8C22C830055E409DBC6E5F8FFD4D40D24FE28E9D045E40FB4032B697FC4D4043F65109DD035E40B3AD6  
691C1FB4D407907729EF6025E40CD022A2615FB4D40FCD5B525F3015E401498630F99FA4D40FD598C90DC005E400571980D52FA4D40C3B0A789BDFF5D40F14DD0D842FA4D40F123900DA1FE5D408B3938066CFA4D4059C7410092FD5D40C53C7702CCFA4D40AFC2A9C39AFC5D40A28FEA205FFB4D40DF  
6AD2D2C4FB5D40839A3DBF1FFC4D40B73C6B6518FB5D400FF2147C06FD4D40112D1B1F9CFA5D40D0CACF7E0AFE4D404E26B0CD54FA5D40CCE2BECD21FF4D4087E3C53845FA5D403B51A0AF41004E408079E9046EFA5D40CDB8BA145F014E40CF0290ABCDFA5D406B5C9D036F024E409222728860FB5D4  
0D84C5A0567034E40DB9B09FC20FC5D409F06088D3D044E40B06412A207FD5D40FBB07A56EA044E40B78A179A0BFE5D40963273B866054E40A25449DF22FF5D40D0DCF9E6AD054E4024FC28AC42005E402D883723BD054E4025240AE65F015E40A173DCD693054E4033F60B8C6F025E4068D3F7993305  
4E4023F6012567035E4013FB0323A0044E4069F3D1273D045E40E1C5CE21DF034E40D515105AE9045E402E7AC106F8024E407F25172165055E40579ED4B8F3014E40D5FF79C2AB055E40E0132A3DDC004E40267F6991BA055E40F420D353BCFF4D40'::geometry ~ pos))  
         Order By: (pos <-> '0101000020E61000000000000000005E400000000000004E40'::geometry)  
         Filter: _st_contains('0103000020E61000000100000021000000267F6991BA055E40F420D353BCFF4D40AE7F7C0791055E4004D7AB0D9FFE4D405B8C22C830055E409DBC6E5F8FFD4D40D24FE28E9D045E40FB4032B697FC4D4043F65109DD035E40B3AD6691C1FB4D407907729EF602  
5E40CD022A2615FB4D40FCD5B525F3015E401498630F99FA4D40FD598C90DC005E400571980D52FA4D40C3B0A789BDFF5D40F14DD0D842FA4D40F123900DA1FE5D408B3938066CFA4D4059C7410092FD5D40C53C7702CCFA4D40AFC2A9C39AFC5D40A28FEA205FFB4D40DF6AD2D2C4FB5D40839A3DBF1  
FFC4D40B73C6B6518FB5D400FF2147C06FD4D40112D1B1F9CFA5D40D0CACF7E0AFE4D404E26B0CD54FA5D40CCE2BECD21FF4D4087E3C53845FA5D403B51A0AF41004E408079E9046EFA5D40CDB8BA145F014E40CF0290ABCDFA5D406B5C9D036F024E409222728860FB5D40D84C5A0567034E40DB9B09  
FC20FC5D409F06088D3D044E40B06412A207FD5D40FBB07A56EA044E40B78A179A0BFE5D40963273B866054E40A25449DF22FF5D40D0DCF9E6AD054E4024FC28AC42005E402D883723BD054E4025240AE65F015E40A173DCD693054E4033F60B8C6F025E4068D3F79933054E4023F6012567035E4013F  
B0323A0044E4069F3D1273D045E40E1C5CE21DF034E40D515105AE9045E402E7AC106F8024E407F25172165055E40579ED4B8F3014E40D5FF79C2AB055E40E0132A3DDC004E40267F6991BA055E40F420D353BCFF4D40'::geometry, pos)  
(5 rows)  

小结3

当使用BIND时,建议在明确使用索引的操作符的最外层,作为整个BIND的变量,而不要放到表达式里面的变量中。

如本例的

prepare s (float8,float8,float8,float8) as   
select * from tbl_pos   
where att1=1 and att2<5 and   
st_contains(geometry(ST_Buffer(geography(st_setsrid(st_makepoint($1,$2), 4326)),5000)), pos)   
order by pos <-> st_setsrid(st_makepoint($3,$4), 4326) asc   
limit 100;  

可以改成如下:

prepare s (int,int,geometry,float8) as   
select * from tbl_pos   
where att1=$1 and att2<$2 and   
st_contains(geometry(ST_Buffer(geography($3),$4)), pos)     
order by pos <-> $3 limit 100;  

参考

《PostgreSQL、Greenplum 应用案例宝典《如来神掌》 - 目录》

《数据库选型之 - 大象十八摸 - 致 架构师、开发者》

《PostgreSQL 使用 pgbench 测试 sysbench 相关case》

《数据库界的华山论剑 tpc.org》

https://www.postgresql.org/docs/10/static/pgbench.html

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
4月前
|
SQL NoSQL 关系型数据库
实时数仓Hologres发展问题之实时数仓的类数据库化与HTAP数据库的差异如何解决
实时数仓Hologres发展问题之实时数仓的类数据库化与HTAP数据库的差异如何解决
56 2
|
6月前
|
存储 关系型数据库 MySQL
深入OceanBase内部机制:高性能分布式(实时HTAP)关系数据库概述
深入OceanBase内部机制:高性能分布式(实时HTAP)关系数据库概述
|
7月前
|
存储 JSON 关系型数据库
PostgreSQL Json应用场景介绍和Shared Detoast优化
PostgreSQL Json应用场景介绍和Shared Detoast优化
|
7月前
|
SQL 关系型数据库 C语言
PostgreSQL【应用 03】Docker部署的PostgreSQL扩展SQL之C语言函数(编写、编译、载入)计算向量余弦距离实例分享
PostgreSQL【应用 03】Docker部署的PostgreSQL扩展SQL之C语言函数(编写、编译、载入)计算向量余弦距离实例分享
96 0
|
7月前
|
SQL 关系型数据库 数据库
PostgreSQL【应用 02】扩展SQL之C语言函数(编写、编译、载入)实例分享
PostgreSQL【应用 02】扩展SQL之C语言函数(编写、编译、载入)实例分享
222 0
|
25天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
34 1
|
27天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
38 4
|
2月前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
68 3
Mysql(4)—数据库索引
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
189 1
|
1月前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
92 2

相关产品

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