HTAP数据库 PostgreSQL 场景与性能测试之 47 - (OLTP) 空间应用 - 高并发空间位置更新、多属性KNN搜索并测（含空间索引）末端配送、新零售类项目
背景
PostgreSQL是一个历史悠久的数据库，历史可以追溯到1973年，最早由2014计算机图灵奖得主，关系数据库的鼻祖Michael_Stonebraker 操刀设计，PostgreSQL具备与Oracle类似的功能、性能、架构以及稳定性。
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流计算、分布式并行计算、时序处理、基因测序、化学分析、图像分析 等。
在各种应用场景中都可以看到PostgreSQL的应用：
PostgreSQL近年来的发展非常迅猛，从知名数据库评测网站dbranking的数据库评分趋势，可以看到PostgreSQL向上发展的趋势：
从每年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 ]》](../201212/20121226_01.md) |
《函数稳定性讲解 - 函数索引思考, 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》](../201212/20121224_01.md) |
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;
升华4，模糊查询引入
挑战， like 查询和时空查询结合（在匹配数据量非常大时），返回可能较慢。 建议like的场景加公里数限制，减少集合大小（即使filter, cpu sort也不会有太大性能损伤）
挑战处理方法， 2套索引（gin(普通字段+LIKE字段)，CPU SORT）， （gist(普通字段+gis字段），CPU filter like） 。优化器看成本，自动选择。
本文用到插件
pg_trgm DATABASE的lc_Ctype不能为C，如果已经是这样，可以新建一个DB，改掉它。 create database db with template template0 encoding 'UTF8' LC_CTYPE 'en_US.UTF8';
btree_gist
btree_gin
intarray
参考
《PostgreSQL、Greenplum 应用案例宝典《如来神掌》 - 目录》
《PostgreSQL 使用 pgbench 测试 sysbench 相关case》
https://www.postgresql.org/docs/10/static/pgbench.html