為什麼我在 Postgres 13 中的空間查詢比在 Postgres 11 中慢？

October 14, 2021

Postgres 版本

x86_64-pc-linux-musl 上的 PostgreSQL 13.2，由 gcc (Alpine 10.2.1_pre1) 10.2.1 20201203 編譯，64 位
x86_64-pc-linux-gnu 上的 PostgreSQL 11.11 (Debian 11.11-1.pgdg90+1)，由 gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516，64 位編譯

詢問

SELECT count(*) FROM "addresses" AS a1 WHERE ST_DWithin('0101000020E61000001EE1B4E0455F0340E92807B309664840'::geography, a1.geom, '25000');

結果是預期的結果。但在 PG11 上慢（1.1s），在 PG13 上更慢（2.9s）

表定義

                                                             Table "public.addresses"
  Column    |              Type              | Collation | Nullable |                Default                | Storage  | Stats target | Description 
-------------+--------------------------------+-----------+----------+---------------------------------------+----------+--------------+-------------
id          | bigint                         |           | not null | nextval('addresses_id_seq'::regclass) | plain    |              | 
description | text                           |           |          |                                       | extended |              | 
country     | character varying(255)         |           |          |                                       | extended |              | 
locality    | character varying(255)         |           |          |                                       | extended |              | 
region      | character varying(255)         |           |          |                                       | extended |              | 
postal_code | character varying(255)         |           |          |                                       | extended |              | 
street      | text                           |           |          |                                       | extended |              | 
geom        | geometry                       |           |          |                                       | main     |              | 
inserted_at | timestamp(0) without time zone |           | not null |                                       | plain    |              | 
updated_at  | timestamp(0) without time zone |           | not null |                                       | plain    |              | 
url         | character varying(255)         |           | not null |                                       | extended |              | 
origin_id   | character varying(255)         |           |          |                                       | extended |              | 
type        | character varying(255)         |           |          |                                       | extended |              | 
Indexes:
   "addresses_pkey" PRIMARY KEY, btree (id)
   "addresses_origin_id_index" UNIQUE, btree (origin_id)
   "addresses_url_index" UNIQUE, btree (url)
   "idx_addresses_geom" gist (geom)
Referenced by:
   TABLE "actors" CONSTRAINT "actors_physical_address_id_fkey" FOREIGN KEY (physical_address_id) REFERENCES addresses(id)
   TABLE "events" CONSTRAINT "events_physical_address_id_fkey" FOREIGN KEY (physical_address_id) REFERENCES addresses(id)
Access method: heap

基數

SELECT ST_SRID(geom) AS srid, count(*) from addresses group by 1;
srid | count  
------+--------
     |      1
4326 | 265011

查詢計劃

PG11

Finalize Aggregate  (cost=52276.51..52276.52 rows=1 width=8) (actual time=1082.209..1095.487 rows=1 loops=1)
 Buffers: shared hit=3444 read=4611
 -&gt;  Gather  (cost=52276.29..52276.50 rows=2 width=8) (actual time=1082.180..1095.468 rows=3 loops=1)
       Workers Planned: 2
       Workers Launched: 2
       Buffers: shared hit=3444 read=4611
       -&gt;  Partial Aggregate  (cost=51276.29..51276.30 rows=1 width=8) (actual time=897.135..897.138 rows=1 loops=3)
             Buffers: shared hit=3444 read=4611
             -&gt;  Parallel Seq Scan on addresses a1  (cost=0.00..51272.61 rows=1472 width=0) (actual time=6.796..894.827 rows=1960 loops=3)
                   Filter: (((geom)::geography && '0101000020E61000001EE1B4E0455F0340E92807B309664840'::geography) AND ('0101000020E61000001EE1B4E0455F0340E92807B309664840'::geography && _st_expand((geom)::geography, '25000'::double precision)) AND _st_dwithin('0101000020E61000001EE1B4E0455F0340E92807B309664840'::geography, (geom)::geography, '25000'::double precision, true))
                   Rows Removed by Filter: 86378
                   Buffers: shared hit=3444 read=4611
Planning Time: 1.167 ms
Execution Time: 1095.650 ms

PG13（jit = 關閉）

Finalize Aggregate  (cost=2770301.18..2770301.19 rows=1 width=8) (actual time=2801.774..2830.421 rows=1 loops=1)
 Buffers: shared hit=8227
 -&gt;  Gather  (cost=2770300.97..2770301.18 rows=2 width=8) (actual time=2800.661..2830.384 rows=3 loops=1)
       Workers Planned: 2
       Workers Launched: 2
       Buffers: shared hit=8227
       -&gt;  Partial Aggregate  (cost=2769300.97..2769300.98 rows=1 width=8) (actual time=2639.846..2639.849 rows=1 loops=3)
             Buffers: shared hit=8227
             -&gt;  Parallel Seq Scan on addresses a1  (cost=0.00..2769300.94 rows=11 width=0) (actual time=63.094..2637.528 rows=1960 loops=3)
                   Filter: st_dwithin('0101000020E61000001EE1B4E0455F0340E92807B309664840'::geography, (geom)::geography, '25000'::double precision, true)
                   Rows Removed by Filter: 86378
                   Buffers: shared hit=8227
Planning Time: 0.424 ms
Execution Time: 2830.699 ms

似乎有兩個問題：1/ 未使用空間索引 2/ PG13 在同一台機器上慢 3 倍，相同的數據。

更緊迫的問題是錯誤的查詢。在任何一種情況下都不應超過 1 秒。您的過濾器在a1."geom"::geography. 此演員表禁用任何普通索引addresses.geom- 包括您擁有的空間索引：
"idx_addresses_geom" gist (geom)
所以我們ST_DWithin()只看到FILTER它應該是快速辨識合格地址的索引條件。您需要一個表達式索引。看：
PostGIS 視窗功能沒有獲取索引？
您的查詢計算給定點 25 公里範圍內的事件數。的第三個參數ST_DWithin()是距離。**以米為geography**單位測量。但對於geometry，單位由參考系統 (SRID) 定義。PostGis 手冊：
For geometry：距離以幾何空間參考系統定義的單位指定。要使此函式有意義，源幾何圖形必須位於相同的座標系中（具有相同的 SRID）。
您的範例輸入是一個geography包含 SRID 4326 的點：
SELECT ST_SRID ('0101000020E61000001EE1B4E0455F0340E92807B309664840'::geography);  -- 4326
SRID 4326 的測量單位是度。使用geography（更精確，更昂貴）或使用geometry不同的 SRID 來繼續使用儀表。
解決方案
最好的解決方案取決於更多未公開的資訊。我的範例解決方案對索引和查詢中的轉換幾何進行操作：
CREATE INDEX idx_addresses_geom_27571 ON pg_temp.addresses USING gist (ST_Transform(geom, 27571));

SELECT count(*)  -- ①
FROM   addresses a
JOIN   events e ON e.physical_address_id = a.id
WHERE  ST_DWithin(ST_Transform('0101000020E61000001EE1B4E0455F0340E92807B309664840664840'::geometry, 27571)
               , ST_Transform(a.geom, 27571)
               , '25000');  -- ②
①小幅改進。假設events.begins_onis defined NOT NULL,count(*)在這個查詢中是等價的，而且速度要快一點。
②描述的主要問題。
我選擇了SRID 27571來適應法國北部的米幾何測量（如您的範例所示）。選擇最合適的參考系統取決於您。
顯然，您只需要轉換還沒有正確 SRID 的輸入幾何。
有關的：
ST_DWithin Calc Meters - 轉換或轉換？
Postgres 13 / PostGIS 中的性能回歸？？？
對於固定查詢，我希望 Postgres 13 在等效且配置正確的伺服器上比 Postgres 11 快一點。
但為什麼使用 Postgres 11 的原始錯誤查詢更快？
Postgres 11：
Filter: (((geom)::geography && '0101000020E61000001EE1B4E0455F0340E92807B309664840'::geography) AND ('0101000020E61000001EE1B4E0455F0340E92807B309664840'::geography && _st_expand((geom)::geography, '25000'::double precision)) AND _st_dwithin('0101000020E61000001EE1B4E0455F0340E92807B309664840'::geography, (geom)::geography, '25000'::double precision, true))
Postgres 13：
Filter: st_dwithin('0101000020E61000001EE1B4E0455F0340E92807B309664840'::geography, (geom)::geography, '25000'::double precision, true)
在 Postgres 中，索引支持它綁定到操作符，而不是函式。舊版本的 PostGIS 必須通過使用內聯函式來“破解”系統，以引入索引使用所需的運算符。所以 ST_DWithin(geog1, geog2, 25000)被替換((geog2 && geog1) AND (geog1 && _st_expand(geog2, 25000)) AND _st_dwithin(geog1, geog2, 25000, true))為添加邊界框操作符。看：
PostgreSQL 可以索引數組列嗎？
PostGIS 不使用帶有 ST_Intersects 的空間索引
在 Postgres 12 和 PostGIS 3.0 中，整個架構發生了重大變化。引用PostGis 3.0 的發行說明：
4341, 在 PgSQL 12+ 中使用“支持函式”API 代替 SQL 內聯作為在 ST_Intersects 等下提供索引支持的機制
從那時起，應該使用索引的 PostGis 函式具有附加的“支持函式”，而不是使用函式內聯重寫。Postgres手冊：
對於返回的目標函式，boolean可以將出現的函式呼叫轉換WHERE為可索引的運算符子句或子句。轉換後的子句可能完全等同於函式的條件，或者它們可能更弱一些（也就是說，它們可能接受一些函式條件不接受的值）。在後一種情況下，索引條件被稱為有損；它仍然可以用於掃描索引，但是必須為索引返回的每一行執行函式呼叫，以查看它是否真的通過了WHERE條件。要創建這樣的條件，支持函式必須實現 SupportRequestIndexCondition請求類型。
在Postgres 13 和 PostGIS 3.1.3中，我的適配查詢 + 索引（如上所述）的查詢計劃對我來說如下所示：
'Aggregate  (cost=52.48..52.48 rows=1 width=8)'
'  -&gt;  Index Scan using idx_addresses_geom_27571 on addresses a  (cost=0.26..52.47 rows=1 width=0)'
'        Index Cond: (st_transform(geom, 27571) && st_expand(''0101000020B36B00006E51041D4B8022411FF9F0303F1E3141''::geometry, ''25000''::double precision))'
'        Filter: st_dwithin(''0101000020B36B00006E51041D4B8022411FF9F0303F1E3141''::geometry, st_transform(geom, 27571), ''25000''::double precision)'
注意添加的索引條件(st_transform(geom, 27571) && st_expand(''0101000020B36B00006E51041D4B8022411FF9F0303F1E3141''::geometry, ''25000''::double precision))'但該Filter行堅持原來的功能表達式。
在沒有索引支持的情況下，在 Postgres 11 中使用邊界框運算符執行重寫的函式表達式似乎比 Postgres 13 中的新實現便宜得多。
要麼您沒有**為 Postgres 13 (???) 使用適當的 PostGIS 版本，**要麼您發現了一個回歸，這可能值得研究。
旁白
timestamp(0) without time zone可能不是最好的選擇。該表達式對時間戳*進行四捨五入。*四捨五入時，從技術上講，時間戳可能在未來。在同一個事務中執行類似inserted_at <= now()的操作可能會讓你失敗，而且你永遠也找不到原因。嗯，直到現在。看：
從時間戳中丟棄毫秒部分
timestamptz通常也是國際數據的更好選擇。看：
在 Rails 和 PostgreSQL 中完全忽略時區
varchar(255)幾乎沒有意義。看：
固定寬度的行會提高 PostgreSQL 的讀取性能嗎？
使用數據類型“文本”儲存字元串有什麼缺點嗎？
你有一個單行geom IS NULL。您可能會解決這個問題並聲明該列NOT NULL。

引用自：https://dba.stackexchange.com/questions/300292

為什麼我在 Postgres 13 中的空間查詢比在 Postgres 11 中慢？

Postgres 版本

詢問

表定義

基數

查詢計劃

PG11

PG13（jit = 關閉）

解決方案

Postgres 13 / PostGIS 中的性能回歸？？？

旁白

相關問答

即使使用“僅索引掃描”，“count(*)”查詢也太慢了

如果 PostgreSQL DB 或伺服器異常關閉，臟數據會發生什麼？

Canonical-or-best data type to store a Flake ID in PostgresSQL for two query patterns

PostgreSQL（大數據）：按標籤搜尋並按時間戳排序

提高 postgres 中的查詢性能

使用帶有 pg_restore 的並行恢復沒有加速