HTAP資料庫 PostgreSQL 場景與性能測試之 47 - (OLTP) 空間應用 - 高并發空間位置更新、多屬性KNN搜尋并測（含空間索引）末端配送類項目

标簽

PostgreSQL , HTAP , OLTP , OLAP , 場景與性能測試

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#%E8%83%8C%E6%99%AF 背景

PostgreSQL是一個曆史悠久的資料庫，曆史可以追溯到1973年，最早由2014計算機圖靈獎得主，關系資料庫的鼻祖

Michael_Stonebraker

操刀設計，PostgreSQL具備與Oracle類似的功能、性能、架構以及穩定性。

HTAP資料庫 PostgreSQL 場景與性能測試之 47 - (OLTP) 空間應用 - 高并發空間位置更新、多屬性KNN搜尋并測（含空間索引）末端配送類項目

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的各種應用場景以及對應的性能名額。

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#%E7%8E%AF%E5%A2%83 環境

環境部署方法參考：

《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來估算。

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#%E5%9C%BA%E6%99%AF---%E9%AB%98%E5%B9%B6%E5%8F%91%E7%A9%BA%E9%97%B4%E4%BD%8D%E7%BD%AE%E6%9B%B4%E6%96%B0%E5%A4%9A%E5%B1%9E%E6%80%A7knn%E6%90%9C%E7%B4%A2%E5%B9%B6%E6%B5%8B%E5%90%AB%E7%A9%BA%E9%97%B4%E7%B4%A2%E5%BC%95 場景 - 高并發空間位置更新、多屬性KNN搜尋并測（含空間索引）

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#1%E8%83%8C%E6%99%AF 1、背景

越來越多的應用正在接入空間資料屬性，例如物聯網、車輛軌迹管理、公安系統的稽偵系統、O2O應用、LBS交友應用、打車應用等等。

被管理的對象攜帶空間屬性，對象的運動形成了軌迹，最後一個位置點表示對象的最終位置。

PostgreSQL在空間資料庫管理領域有這幾十年的發展曆程，例如PostGIS空間資料庫,pgrouting路由插件等，GiST空間索引，SP-GiST空間分區索引等。

本文要測試的是空間資料的合并更新性能（攜帶空間索引），例如，更新對象的最終空間位置，同時根據使用者輸入，搜尋附近N米内滿足條件的對象（用到了btree_gist插件以及GiST索引）。

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#2%E8%AE%BE%E8%AE%A1 2、設計

2000萬個被跟蹤對象，2000萬個點，含空間索引。

1、實時合并更新被跟蹤對象的位置。

2、同時根據使用者輸入，搜尋附近N米内滿足條件的對象。

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#3%E5%87%86%E5%A4%87%E6%B5%8B%E8%AF%95%E8%A1%A8 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)

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#4%E5%87%86%E5%A4%87%E6%B5%8B%E8%AF%95%E5%87%BD%E6%95%B0%E5%8F%AF%E9%80%89 4、準備測試函數(可選)

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#5%E5%87%86%E5%A4%87%E6%B5%8B%E8%AF%95%E6%95%B0%E6%8D%AE 5、準備測試資料

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#6%E5%87%86%E5%A4%87%E6%B5%8B%E8%AF%95%E8%84%9A%E6%9C%AC 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

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#7%E6%B5%8B%E8%AF%95 7、測試

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#1%E6%9B%B4%E6%96%B0%E5%AF%B9%E8%B1%A1%E4%BD%8D%E7%BD%AE 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;

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#tps-110592 TPS： 110592

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#%E5%B9%B3%E5%9D%87%E5%93%8D%E5%BA%94%E6%97%B6%E9%97%B4-0178-%E6%AF%AB%E7%A7%92 平均響應時間： 0.178 毫秒

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#2%E6%A0%B9%E6%8D%AE%E7%94%A8%E6%88%B7%E8%BE%93%E5%85%A5%E7%9A%84att1-att2%E6%9D%A1%E4%BB%B6%E6%90%9C%E7%B4%A2%E6%BB%A1%E8%B6%B3%E6%9D%A1%E4%BB%B6%E7%9A%84%E9%99%84%E8%BF%915%E5%85%AC%E9%87%8C%E5%86%85%E7%9A%84%E5%AF%B9%E8%B1%A1%E6%A0%B9%E6%8D%AE%E8%B7%9D%E7%A6%BB%E9%A1%BA%E5%BA%8F%E8%BF%94%E5%9B%9E100%E6%9D%A1

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;

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#tps-9472 TPS： 9472

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#%E5%B9%B3%E5%9D%87%E5%93%8D%E5%BA%94%E6%97%B6%E9%97%B4-0842-%E6%AF%AB%E7%A7%92 平均響應時間： 0.842 毫秒

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#%E5%B0%8F%E7%BB%931 小結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字段的複合排序輸出。

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#%E9%9C%80%E6%B1%82%E5%8D%87%E5%8D%8E1 需求升華1

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#%E9%99%A4%E4%BA%86%E7%A9%BA%E9%97%B4%E6%90%9C%E7%B4%A2%E8%BF%98%E5%BC%95%E5%85%A5%E6%99%AE%E9%80%9A%E5%AD%97%E6%AE%B5%E7%AD%9B%E9%80%89%E6%9D%A1%E4%BB%B6 除了空間搜尋，還引入普通字段篩選條件

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)

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#%E9%9C%80%E6%B1%82%E5%8D%87%E5%8D%8E2 需求升華2

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#%E9%99%A4%E4%BA%86%E7%A9%BA%E9%97%B4%E6%90%9C%E7%B4%A2%E6%99%AE%E9%80%9A%E5%AD%97%E6%AE%B5%E7%AD%9B%E9%80%89%E6%9D%A1%E4%BB%B6%E8%BF%98%E5%BC%95%E5%85%A5%E6%95%B0%E7%BB%84%E5%AD%97%E6%AE%B5%E8%BF%87%E6%BB%A4%E6%9D%A1%E4%BB%B6 除了空間搜尋，普通字段篩選條件，還引入數組字段過濾條件

如果業務上還有數組條件的包含查詢過濾，可以建立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

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#%E5%8E%8B%E6%B5%8B%E7%BB%93%E6%9E%9C 壓測結果

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#1%E6%9B%B4%E6%96%B0%E5%AF%B9%E8%B1%A1%E4%BD%8D%E7%BD%AE-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;

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#tps-96708 TPS： 96708

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#%E5%B9%B3%E5%9D%87%E5%93%8D%E5%BA%94%E6%97%B6%E9%97%B4-0286-%E6%AF%AB%E7%A7%92 平均響應時間： 0.286 毫秒

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#2%E6%A0%B9%E6%8D%AE%E7%94%A8%E6%88%B7%E8%BE%93%E5%85%A5%E7%9A%84att2-att4%E7%A9%BA%E9%97%B4%E8%BF%87%E6%BB%A4%E7%BB%84%E5%90%88%E6%9D%A1%E4%BB%B6%E6%90%9C%E7%B4%A2%E6%BB%A1%E8%B6%B3%E6%9D%A1%E4%BB%B6%E7%9A%84%E9%99%84%E8%BF%91500000%E7%B1%B3%E5%86%85%E7%9A%84%E5%AF%B9%E8%B1%A1%E6%A0%B9%E6%8D%AE%E8%B7%9D%E7%A6%BB%E9%A1%BA%E5%BA%8F%E8%BF%94%E5%9B%9E100%E6%9D%A1 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);

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#tps-5005 TPS： 5005

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#%E5%B9%B3%E5%9D%87%E5%93%8D%E5%BA%94%E6%97%B6%E9%97%B4-11-%E6%AF%AB%E7%A7%92 平均響應時間： 11 毫秒

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#%E5%B0%8F%E7%BB%932 小結2

通過intarray , btree_gist插件，使得gist索引接口同時支援了普通類型、數組類型、空間類型的複合索引。查詢效率上大幅提升。

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#%E5%9C%A8st_makepoint%E4%B8%AD%E4%BD%BF%E7%94%A8%E7%BB%91%E5%AE%9A%E5%8F%98%E9%87%8F%E5%BC%95%E5%85%A5%E7%9A%84%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92%E9%97%AE%E9%A2%98%E5%AF%BC%E8%87%B4%E7%9A%84%E6%80%A7%E8%83%BD%E9%97%AE%E9%A2%98%E4%BC%98%E5%8C%96 在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;

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#%E5%B7%AE%E5%88%AB 差别

以上兩種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)

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#%E5%B0%8F%E7%BB%933 小結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;

https://github.com/digoal/blog/blob/master/201711/20171107_48.md#%E5%8F%82%E8%80%83 參考

《PostgreSQL、Greenplum 應用案例寶典《如來神掌》 - 目錄》《資料庫選型之 - 大象十八摸 - 緻架構師、開發者》《PostgreSQL 使用 pgbench 測試 sysbench 相關case》《資料庫界的華山論劍 tpc.org》 https://www.postgresql.org/docs/10/static/pgbench.html