對大量行進行慢速求和/分組

請您幫助我們處理大量數據（約 500K 行）的慢速查詢。我們在 Postgres 10 上。

SELECT mike_romeo, SUM(hotel) as counts
FROM whiskey_lima
WHERE
   yankee = 'kilo'
AND
   whiskey_india = 'a_value'
AND
   echo = 'romeo_november'
GROUP BY
   mike_romeo;

我的桌子看起來像這樣：

   Column     |            Type             | Collation | Nullable |                        Default
---------------+-----------------------------+-----------+----------+--------------------------------------------------------
id            | bigint                      |           | not null | nextval('id_seq'::regclass)
yankee        | mike_hotel.quebec           |           | not null |
whiskey_india | character varying           |           | not null |
echo          | mike_hotel.romeo_yankee     |           | not null |
mike_romeo    | character varying           |           | not null |
oscar         | timestamp without time zone |           | not null |
hotel         | integer                     |           | not null |
papa          | timestamp without time zone |           |          |
Indexes:
   "whiskey_lima_pkey" PRIMARY KEY, btree (id)
   "index_unique" UNIQUE, btree (echo, mike_romeo, yankee, whiskey_india, oscar)
   "index_query" btree (yankee, whiskey_india, echo)
   "index_on_oscar" btree (oscar)

我們的EXPLAIN (ANALYSE, BUFFERS)樣子是這樣的：https ://explain.depesz.com/s/4lU

GroupAggregate  (cost=77294.010..78398.740 rows=38882 width=69) (actual time=56343.468..59194.302 rows=527807 loops=1)
   Group Key: mike_romeo
   Buffers: shared hit=61428 read=68664, temp read=23413 written=23450
 ->  Sort  (cost=77294.010..77532.650 rows=95455 width=65) (actual time=56343.461..58848.379 rows=530929 loops=1)
         Sort Key: mike_romeo
         Sort Method: external merge  Disk: 111488kB
         Buffers: shared hit=61428 read=68664, temp read=23413 written=23450
       ->  Index Scan using mike_quebec on whiskey_lima  (cost=0.560..67104.720 rows=95455 width=65) (actual time=1.647..38814.509 rows=530929 loops=1)
               Index Cond: ((yankee = 'kilo'::mike_hotel.quebec) AND ((whiskey_india)::text = 'juliet'::text) AND (echo = 'romeo_november'::mike_hotel.romeo_yankee))
               Buffers: shared hit=61425 read=68664

問題

我們應該關注什麼——有什麼明顯的解決方案嗎？

我們可以避免這種情況嗎？

為什麼查詢計劃器低估了行 - 這會導致一些緩慢嗎？我們正在嘗試啟用擴展統計資訊

我們正在考慮增加work_mem以減輕磁碟排序 - 這會使整個查詢變得高效嗎？

我還缺少什麼？

非常感謝你的幫助

我會嘗試使用如下索引：

echo, yankee, whiskey_india, mike_romeo

也許您可以將 index_unique 替換為：

"index_unique" UNIQUE, btree (echo, yankee, whiskey_india, mike_romeo, oscar)  

這個想法是將謂詞首先放在索引中，然後是按列分組。

上的索引(yankee, whiskey_india, echo, mike_romeo, hotel)可能是最佳的。前 3 列滿足相等條件——它們不必按那個順序排列，只要這 3 列佔據索引中的前 3 個槽即可。添加 mike_romeo 可以通過以預排序順序讀取滿足前 3 個索引的部分來避免排序。添加hotel可以讓整個事情成為一個index-only scan，因為所有滿足查詢的數據都可以從index中獲取，不需要查詢表，會產生大量的隨機IO。

計劃者假設所有三個平等條件都是獨立的，因此它們的個體選擇性可以成倍增加，但顯然這不是真的。但無論如何修復它可能無濟於事，因為它還有什麼其他計劃可以使用？除非它要對整個表進行 seq-scan …

增加 work_mem 可以使排序更快或更慢（這取決於要預測的事情太多，例如合併排序是否比巨型快速排序對您的 CPU 記憶體結構更友好），或者它可能允許規劃器從排序切換改為進行雜湊聚合。只有一種方法可以找出答案。

還有兩件事需要關注——我假設您的查詢是 IO 綁定的。如果您立即重複查詢，由於數據已經在記憶體中，第二次是否更快？你能打開track_io_timing並重做EXPLAIN (ANALYSE, BUFFERS)

有時會EXPLAIN (ANALYSE)施加足夠的儀器成本，從而扭曲了測量的時間。你能做一個EXPLAIN (ANALYSE, TIMING OFF)看看這是否使整體執行時間比完整的分析更快嗎？您可能希望在此之間來回切換幾次，以確保它不是記憶體效果。

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