Postgresql 極慢計數（帶索引，簡單查詢）

我需要在具有數百萬行的表上執行這些簡單的查詢：

SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123;

SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123 AND "subscriptions"."trashed_at" IS NULL;

對於項目 123，這兩個查詢的計數結果約為 5M。

我有一個索引project_id，還有另一個索引(project_id, trashed_at)：

"index_subscriptions_on_project_id_and_created_at" btree (project_id, created_at DESC)
"index_subscriptions_on_project_id_and_trashed_at" btree (project_id, trashed_at DESC)

問題是這兩個查詢都非常慢，每個查詢大約需要 17 秒。

這些是結果EXPLAIN ANALIZE：

     QUERY PLAN                                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=2068127.29..2068127.30 rows=1 width=0) (actual time=17342.420..17342.420 rows=1 loops=1)
  ->  Bitmap Heap Scan on subscriptions  (cost=199573.94..2055635.23 rows=4996823 width=0) (actual time=1666.409..16855.610 rows=4994254 loops=1)
        Recheck Cond: (project_id = 123)
        Rows Removed by Index Recheck: 23746378
        Heap Blocks: exact=131205 lossy=1480411
        ->  Bitmap Index Scan on index_subscriptions_on_project_id_and_trashed_at  (cost=0.00..198324.74 rows=4996823 width=0) (actual time=1582.717..1582.717 rows=4994877 loops=1)
              Index Cond: (project_id = 123)
Planning time: 0.090 ms
Execution time: 17344.182 ms
(9 rows)

     QUERY PLAN                                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=2047881.69..2047881.70 rows=1 width=0) (actual time=17557.218..17557.218 rows=1 loops=1)
  ->  Bitmap Heap Scan on subscriptions  (cost=187953.70..2036810.19 rows=4428599 width=0) (actual time=1644.966..17078.378 rows=4994130 loops=1)
        Recheck Cond: ((project_id = 123) AND (trashed_at IS NULL))
        Rows Removed by Index Recheck: 23746273
        Heap Blocks: exact=131144 lossy=1480409
        ->  Bitmap Index Scan on index_subscriptions_on_project_id_and_trashed_at  (cost=0.00..186846.55 rows=4428599 width=0) (actual time=1566.163..1566.163 rows=4994749 loops=1)
              Index Cond: ((project_id = 123) AND (trashed_at IS NULL))
Planning time: 0.084 ms
Execution time: 17558.522 ms
(9 rows)

問題是什麼？

我可以做些什麼來提高性能（即在幾秒鐘內計數）？

獲取和計算 500 萬行是一項緩慢的業務。

有兩個問題：

• 點陣圖堆掃描花費的時間比必要的要長，因為work_mem它太小以至於不能包含每個表行一個位的點陣圖。

然後它會降級為每 8KB 塊儲存一個位，這會導致在點陣圖堆掃描階段進行更多的堆提取以排除誤報。

所以提高work_mem會加快執行速度，但根據你的意見，不會很多。

• 與其查詢表本身，這很慢，PostgreSQL 還可以只讀取其中一個索引，因為它們包含所有需要的數據。

那會快得多，那麼為什麼 PostgreSQL 不這樣做呢？

解釋是 PostgreSQL 表和索引包含可見的行版本（元組）和不可見的元組（因為它們已被刪除或更新但尚未清理）。PostgreSQL 多版本實現需要這些“死元組”。

現在，哪些元組可見，哪些不可見的資訊僅儲存在表（堆）中，而不儲存在索引中。所以 PostgreSQL 必須查詢堆來確定哪些元組要計數，哪些不計數。

如果你VACUUM在表上執行，PostgreSQL 將更新一個名為“可見性映射”的好東西，它每 8KB 塊包含一個位，指示塊中的所有元組是否**對每個人都是可見的。

如果可見性映射有一個塊是全部可見的，則索引掃描不必查詢表來確定它是否可以看到塊中的元組。如果這適用於大多數元組，那麼您將獲得一個僅索引掃描，這顯然要快得多。

所以你應該看到桌子經常被吸塵。

為此，讓 autovacuum 在此表上執行得更快、更頻繁：

ALTER TABLE subscriptions SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_delay = 2
);

如果你有一個索引，你會更快project_id。

TL;DR 我已經通過執行以下命令解決了：

vacuum analyze subscriptions;

在該命令之後，查詢只需要 ~1s 而不是 ~17s。有關詳細說明，請參閱@Laurenz answer。

現在我使用以下設置更頻繁地執行 autovacuum postgresql.conf：

autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.01

更新：隨著表的大小增加，上述設置是不夠的（因為隨著表的增長，真空變得不那麼頻繁）。對於非常大的表（即 10M+ 記錄），我使用了這個：

ALTER TABLE subscriptions SET (autovacuum_vacuum_scale_factor = 0, autovacuum_analyze_scale_factor = 0,  autovacuum_vacuum_threshold = 10000, autovacuum_analyze_threshold = 10000);

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