PostgreSQL 單列多條件查詢優化 - 多個多邊形查詢4倍提升的技巧

postgresql , postgis , 多邊形合并 , 條件合并 , 菜鳥

在某些空間資料查詢需求中，有一些這樣的請求，例如查找與某些多邊形中的任意一個相交的空間對象。

比如在菜鳥、新零售的業務中，查詢某幾個商場多邊形，或者某幾個小區多邊形内覆寫的點。

sql寫法可能是這樣的

postgresql支援空間索引，同時支援bitmapand, bitmapor index scan。也就是說隻要geo_point字段有索引，不管多少個查詢條件，都可以走index scan。

這個查詢有什麼優化空間麼？

在講這個空間優化前，我們來看另一個例子。

單列組合條件查詢與前面提到的需求類似，即一個字段，多個查詢條件。是不是類似于一個字段，多個多邊形比對呢？

表結構如下

查詢需求如下，id字段上有4個條件，任意條件滿足即傳回結果。

postgresql根據統計資訊，基于cbo成本優化，采用了bitmapor，重複使用了多次單列索引，合并所有條件對應的資料塊。最後進行一次recheck得到所要的結果。

bitmapand,bitmapor是postgresql資料庫獨有的特性，可以在多個查詢條件的組合查詢中使用多個索引進行資料塊的合并和消除掃描，非常贊。

而實際上這4個條件，我們可以在邏輯上合成一個條件。

合成後，走精确索引掃描，性能提升幾十倍。

實際上，空間類型的資料，也是一樣的，而且合成起來更友善，通過st_union即可。

優化為

性能提升也是非常明顯的。

<a href="http://postgis.net/docs/manual-2.3/st_union.html">http://postgis.net/docs/manual-2.3/st_union.html</a>

<a href="https://www.postgresql.org/docs/9.6/static/indexes-bitmap-scans.html">https://www.postgresql.org/docs/9.6/static/indexes-bitmap-scans.html</a>