加速 GROUP BY，HAVING COUNT 查詢

我正在嘗試在 Postgres 9.4 中加速此查詢：

SELECT "groupingsFrameHash", COUNT(*) AS nb
FROM "public"."zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"
GROUP BY "groupingsFrameHash"
HAVING COUNT(*) > 1
ORDER BY nb DESC LIMIT 10

我有一個索引"groupingsFrameHash"。我不需要精確的結果，模糊近似就足夠了。

這是查詢計劃：

Limit  (cost=17207.03..17207.05 rows=10 width=25) (actual time=740.056..740.058 rows=10 loops=1)
 ->  Sort  (cost=17207.03..17318.19 rows=44463 width=25) (actual time=740.054..740.055 rows=10 loops=1)
       Sort Key: (count(*))
       Sort Method: top-N heapsort  Memory: 25kB
       ->  GroupAggregate  (cost=14725.95..16246.20 rows=44463 width=25) (actual time=615.109..734.740 rows=25977 loops=1)
             Group Key: "groupingsFrameHash"
             Filter: (count(*) > 1)
             Rows Removed by Filter: 24259
             ->  Sort  (cost=14725.95..14967.07 rows=96446 width=25) (actual time=615.093..705.507 rows=96026 loops=1)
                   Sort Key: "groupingsFrameHash"
                   Sort Method: external merge  Disk: 3280kB
                   ->  Seq Scan on "zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"  (cost=0.00..4431.46 rows=96446 width=25) (actual time=0.007..33.813 rows=96026 loops=1)
Planning time: 0.080 ms
Execution time: 740.877 ms

我不明白為什麼它需要進行 Seq Scan。

您需要10 個最常見的值及其"groupingsFrameHash"各自的計數（不包括唯一值）——這是一項常見任務。不過，這個規範引起了我的注意：

模糊近似就足夠了

這允許從根本上更快的解決方案。Postgres 恰好將那些近似值儲存在系統目錄中，總計數pg_class和最常見的值在pg_statistic. 關於這些數字性質的手冊：

條目ANALYZE由查詢計劃程序創建並隨後由其使用。請注意，所有統計數據本質上都是近似的，即使假設它是最新的。

你被警告了。

還要考慮手冊中規劃器使用的統計資訊一章。

如果你已經正確設置了 autovacuum 並且你的表的內容沒有太大的變化，那麼這些估計應該是好的。如果您在對錶進行重大更改後立即執行此查詢（因此 autovacuum 沒有機會啟動），ANALYZE請先執行（或者如果您可以騰出時間更好）。VACUUM ANALYZE您還可以微調精度，這超出了這個問題的範圍……

有安全方面的考慮。再次引用手冊：

pg_statistic不應該被公眾閱讀，因為即使是關於表格內容的統計資訊也可能被認為是敏感的。（例如：工資列的最小值和最大值可能非常有趣。）**pg_stats**是一個公開可讀的視圖 pg_statistic，僅公開有關目前使用者可讀的那些表的資訊。

考慮到所有這些，您可以快速估算：

SELECT v."groupingsFrameHash", (c.reltuples * freq)::int AS estimate_ct
FROM   pg_stats s
CROSS  JOIN LATERAL
  unnest(s.most_common_vals::text::text[]  -- use your actual data type
       , s.most_common_freqs) WITH ORDINALITY v ("groupingsFrameHash", freq, ord)
CROSS  JOIN (
  SELECT reltuples FROM pg_class
  WHERE oid = regclass 'public.zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876'
  ) c
WHERE  schemaname = 'public'
AND    tablename  = 'zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876'
AND    attname    = 'groupingsFrameHash'  -- case sensitive
ORDER  BY v.ord
LIMIT  10;

此查詢中有幾個值得注意的功能：

• 提供所有未轉義且區分大小寫的標識符字元串。

• unnest()對於多個數組需要 Postgres 9.4或更高版本。細節：

  • 將數組傳遞給 Postgres 中的儲存過程

• pg_stats.most_common_vals是具有數據偽類型的特殊列anyarray（在使用者表中不可用）。它可以儲存任何類型的數組。要分解，text先轉換為列類型的數組類型，然後再轉換為數組類型。假設text[]在範例中：

s.most_common_vals::text::text[]

替換為您的實際數據類型。

• 我添加WITH ORDINALITY到unnest()（Postgres 9.4或更高版本）以保留元素的原始順序。由於數組中的數字是按頻率降序排列的，我們可以立即使用該排序順序。考慮：

  • 帶有元素編號的 PostgreSQL unnest()

這需要大約 1 毫秒或更短的時間 - 無論您的表中有多少行。

實驗優化

如果您仍然需要擠出更多性能並且您具有超級使用者訪問權限，則可以pg_statistic直接使用：

SELECT v."groupingsFrameHash", (c.reltuples * freq)::int AS estimate_ct
FROM   pg_attribute a 
JOIN   pg_class     c ON  c.oid = a.attrelid
JOIN   pg_statistic s ON  s.starelid = a.attrelid
                     AND s.staattnum = a.attnum
    , unnest(s.stavalues1::text::text[]
           , s.stanumbers1) WITH ORDINALITY v ("groupingsFrameHash", freq, ord)
WHERE  a.attrelid = regclass 'public.zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876'
AND    a.attname  = 'groupingsFrameHash'
ORDER  BY v.ord
LIMIT  10;

隨著我們越來越接近 Postgres 的核心，您需要知道自己在做什麼。我們依賴於可能在主要 Postgres 版本中發生變化的實現細節（儘管不太可能）。閱讀pg_statistics手冊中的詳細資訊和原始碼中的註釋。

要擠出最後一滴，您甚至可以硬編碼列的屬性編號（如果您更改表中列的位置，它會更改！）並依賴於返回的行的順序unnest()，這通常有效：

SELECT v."groupingsFrameHash", (c.reltuples * freq)::int AS estimate_ct
FROM   pg_class     c
JOIN   pg_statistic s ON s.starelid = c.oid
    , unnest(s.stavalues1::text::text[], s.stanumbers1) v("groupingsFrameHash", freq)    
WHERE  c.oid = regclass 'public.zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876'
AND    s.staattnum = int2 '6'  -- hard-coded pg_attribute.attnum
LIMIT  10;

得到你自己的估計

使用 Postgres 9.5 中的新TABLESAMPLE功能，您可以將聚合基於（或多或少）表的隨機樣本：

SELECT birthday, 10 * count(*) AS estimate
FROM   big
TABLESAMPLE SYSTEM (10)
GROUP  BY 1
ORDER  BY estimate DESC
LIMIT  10;

細節：

• 發現表行數的快速方法

準確計數

如果您需要精確計數，最佳查詢取決於數據分佈和值頻率。模擬鬆散的索引掃描（如@Mihai 已評論）可以很好地提高性能 - 但是，以有限的方式（如@ypercube 已評論），因為您需要考慮排序順序的所有不同值。對於相對較少的不同值，該技術仍然可以支付，但對於您的範例，在約 100k 行的表中具有約 25k 不同值的機會很小。基本：

• 優化 GROUP BY 查詢以檢索每個使用者的最新記錄

但首先您可能需要調整成本設置。UsingSET LOCAL enable_seqscan = off;主要用於調試問題。在您的交易中使用它是最後的手段。它似乎可以解決您手頭的問題，但以後可能會咬您一口。

而是解決根本問題。我有根據的猜測是，您的設置為**random_page_cost**不切實際地高。如果您的大部分數據庫（或至少大部分相關部分）適合可用記憶體，則預設設置 4.0 通常太高了。根據完整的圖片，它可以低至 1.1 甚至 1.0。

Postgres 錯誤地估計順序掃描更快，而使用索引快十倍的事實將是錯誤配置的典型指標：

• 防止 PostgreSQL 有時選擇錯誤的查詢計劃

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