PostgreSQL 12.4 上的計劃時間很慢
我有一個包含 Openstreetmap 位置數據的數據庫，特別是有一個包含大約 5 億行的多邊形表。
我為所有查詢創建了索引，包括
using gist(way) where <filter of query restrictions>. 索引執行良好，查詢規劃器為查詢選擇了正確的索引，但規劃時間總是遠高於執行時間，規劃時間可能為 112 毫秒，而執行時間為 1.7 毫秒。這是使用相同的開放連接，據我所知不存在分區，沒有功能，並且
analyze沒有vacuum區別（無論如何都不會添加或刪除數據）。我已經嘗試了高度修改的配置以及沒有更改的庫存配置。
我可以通過刪除我的專用索引並保留沒有過濾器的通用索引來顯著加快速度
USING gist( way )，但計劃時間仍然超過執行時間。在此更改之後，最慢的查詢從大約 35 毫秒的計劃時間變為大約 3 毫秒，執行時間相似。所以主要的問題是這是否正常，如果是這樣，有沒有一種方法可以改進索引或規劃，而不僅僅是在路欄位上設置索引？
我有一個範例查詢是這樣的：
SELECT way FROM planet_osm_polygon WHERE building IS NOT NULL AND building != 'no' AND way_area > 0 AND z(17061.8) >= 14 AND way && ST_SetSRID('BOX3D(-7529047.28608986 9450979.925292334,-7523543.820053328 9456483.391328866)'::box3d, 3857);在我有專門針對這種情況的索引之前，它的計劃時間為 2ms，執行時間為 0.7ms：
CREATE INDEX planet_osm_polygon_buildings ON planet_osm_polygon USING GIST(way) WHERE (building IS NOT NULL) AND (building <> 'no'::text) AND (way_area > '0'::double PRECISION);當我刪除查詢特定索引時，它更改為 1ms 計劃時間，0.7 exec 並在 上使用通用索引
way，但當然為 and 添加了過濾building器way_area。我嘗試使用 where 過濾器在 building 列上添加一個索引，但它並沒有改進查詢計劃或執行。我目前對該查詢的分析
way僅具有一般索引，如下所示：Index Scan using planet_osm_polygon_way_idx on planet_osm_polygon (cost=0.55..8.57 rows=1 width=215) (actual time=0.658..0.658 rows=0 loops=1) Index Cond: (way && '01030000A0110F00000100000005000000D84B4FD295B85CC1ABFE9B7DBC0662410000000000000000D84B4FD295B85CC11DC4856C6C0962410000000000000000F4C07BF435B35CC11DC4856C6C0962410000000000000000F4C07BF435B35CC1ABFE9B7DBC0662410000000000000000D84B4FD295B85CC1ABFE9B7DBC0662410000000000000000'::geometry) Filter: ((building IS NOT NULL) AND (building <> 'no'::text) AND (way_area > '0'::double precision)) Rows Removed by Filter: 11 Planning Time: 0.967 ms Execution Time: 0.691 ms{ "Startup Cost": 0.55, "Total Cost": 8.57, "Plan Rows": 1, "Plan Width": 215, "Actual Startup Time": 0.745, "Actual Total Time": 0.746, "Actual Rows": 0, "Actual Loops": 1, "Shared Hit Blocks": 29, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "I/O Read Time": 0.000, "I/O Write Time": 0.000 }, "Planning Time": 1.239, "Triggers": [ ], "Execution Time": 0.789非常感謝任何幫助或解釋。
編輯：添加了我的 postgresql 配置，我也嘗試過刪除設置，然後在重新啟動 pgsql 後進行真空和分析，但無濟於事。
# Add settings for extensions here max_connections = 280 superuser_reserved_connections = 3 # Memory Settings shared_buffers = 40GB work_mem = '100 MB' maintenance_work_mem = 4GB huge_pages = try # NB! requires also activation of huge pages via kernel params, see here for more: # https://www.postgresql.org/docs/current/static/kernel-resources.html#LINUX-HUGE-PAGES effective_cache_size = '64 GB' effective_io_concurrency = 200 #Storage is an Intel DC NVME # Monitoring shared_preload_libraries = 'pg_stat_statements' # per statement resource usage stats track_io_timing=on # measure exact block IO times track_functions=pl # track execution times of pl-language procedures if any # Replication wal_level = minimal # consider using at least 'replica' max_wal_senders = 0 synchronous_commit = off wal_keep_segments = 130 # Checkpointing: checkpoint_timeout = 1d checkpoint_completion_target = 0.9 max_wal_size = '10240 MB' min_wal_size = '5120 MB' default_statistics_target = 100 #Report of using higher causing slow planning time in 12.2. Previously set at 500 # WAL writing wal_compression = on wal_buffers = -1 # auto-tuned by Postgres till maximum of segment size (16MB by default) # Background writer bgwriter_delay = 200ms bgwriter_lru_maxpages = 100 bgwriter_lru_multiplier = 2.0 bgwriter_flush_after = 0 # Parallel queries: max_worker_processes = 32 max_parallel_workers = 32 #postgres12 features max_parallel_maintenance_workers = 16 parallel_leader_participation = on # Advanced features enable_partitionwise_join = on enable_partitionwise_aggregate = on jit=off #Causes major slowdown in 12 max_parallel_workers_per_gather = 0 #Causes major slowdown in 12 logging_collector = off log_directory = 'pg_log' log_min_duration_statement = 50 statement_timeout = 0
在解決了這個問題數週之後，甚至換了一個全新的伺服器後，我想出了一些可能對某些人有用的解決方案。這很可能是，我真的希望是，一個不同問題的症狀。儘管如此，我目前的回答是帶有 postgis 查詢計劃器的 PostgreSQL 12 非常慢並且優化很糟糕。
所以這裡有一些我發現的東西：
- 檢查每個函式，即使它們是簡單的數學運算。例如，標準 log() 函式（對數，不寫入日誌）可能需要 0.1-0.2ms 才能執行，具體取決於基本 cpu 速度。您最好使用更快的不同函式進行計算。
- PostgreSQL 將對空數據進行處理。例如，一個簡單的 select where 語句需要 2ms 來處理並返回 0 個結果。用更複雜的 where 語句包裝該選擇以過濾掉這些結果增加了 40 毫秒。另一個例子是數組的 .each()。與讓 .each() 處理空數據相比，使用其他函式循環遍歷數組以確保它包含所需的結果更快。
- 鑄造成不同的類型。我有一個簡單的“where real_column > 1000”查詢。將其更改為“where real_column::float > 1000”可將規劃器縮短 20 毫秒。TEXT IS NULL 也受益於 TEXT::varchar IS NULL。這可能只是幫助計劃者避免某些索引的好方法。
- 最後，正如 Laurenz Albe 所指出的，小心索引。有時，通過類似索引的計劃者的權重超過了執行。不幸的是，由於規劃器無法匹配類似於包含兩者的單個索引的查詢，這使情況變得更糟。
如您所見，在一個表上擁有許多相似的索引並不是一個好主意：數據修改時間和查詢計劃時間會增加（後者是因為優化器必須考慮所有索引）。
對於像這樣使用單個 GiST 索引的簡單查詢，3 毫秒的計劃時間非常高，至少如果你沒有做一些瘋狂的事情，比如降低
geqo_threshold到 1。加快計劃時間的最終方法是使用準備好的語句。然後，您可能會從第六次執行開始獲得通用計劃，並且計劃時間應該幾乎為零。