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

我們目前有一個 PostgreSQL 表，它儲存audit類似於以下的日誌：

該表還有另外 8 列，我們擁有的界面允許使用者指定any過濾器的組合，即created_atwith action、actiononly 、actionwithcaused_by_id等

我們遇到的問題是count(*)or count(1)。我們有大約 800 萬行並且還在增長，它目前需要在1 second and 900ms16vCPU 伺服器上響應，該伺服器具有 128 GB 記憶體和大約 6000 IOPS。

所有其他查詢都很好，因為我們有分頁，使用者無法分頁超過 200 條記錄。

我們得到了一個緩慢的查詢，我們將其優化到EXPLAIN (ANALYZE, BUFFERS)顯示 anINDEX ONLY SCAN已被使用的級別。

請記住：

• 每次使用者使用過濾器搜尋時，我們都應該有適當的btree索引來實現索引只掃描，否則查詢會非常慢。由於在創建索引時列排序的事實，將所有組合創建為索引是不可能的。
• 我們的索引最終可能與主表具有完全相同的大小。

問題：

• 有沒有辦法優化聚合COUNT查詢以更快地為我們提供結果，或者我們應該轉移到不同的基礎設施？
• 像 Salesforce / Freshdesk 這樣的大公司如何在應用任何類型的過濾器的同時，在亞秒級的時間內為您提供數百萬條記錄的結果？他們是否使用不同類型的數據庫？

額外細節：

PostgreSQL 版本：13.7 伺服器：16vCPU - 128 GB RAM - 6400 IOPS 平台：Microsoft Azure 靈活伺服器

桌子audit

create table audit
(
id             bigint,
portal_id      integer,
action         varchar(255),
caused_by_id   integer,
caused_by_type varchar(255),
created_at     timestamp
);

索引：

We tried multiple combinations. A sample will be the following:

create index audit_caused_by_id_portal_id
on audit_2 (caused_by_id, portal_id);

詢問：

select count(1)
from (select "portal_id"
 from "crm"."audit"
 where (("portal_id" = 1) and (not (lower("action") = 'send-email')))
   and (not ("caused_by_id" in (1)))) x2;

解釋（分析，緩衝區）結果： https ://explain.tensor.ru/archive/explain/01145f871a9ed0d60962da821669d9e7:0:2022-07-13#parsed

要回答 PostgreSQL 方面的問題：

如果您計算很多行，那總是很慢。

為了有可能獲得僅索引掃描（也不會很快減輕），您必須創建一個專門針對該單個查詢量身定制的索引：

CREATE INDEX ON crm.audit (portal_id, lower(action), caused_by_id);

那你就得VACUUM上桌了。

真正的解決方案在別處：不要執行該查詢。不顯示查詢結果的總數。沒有人需要那個。

“其他系統”也不這樣做。你相信網路搜尋引擎的點擊數是一個準確的數字嗎？如果您需要顯示一個數字，請顯示您可以使用EXPLAINPostgreSQL 中的語句獲得的估計結果計數。

他們是否使用不同類型的數據庫？

不，是的，也許——這取決於你這個問題的意思。大公司確實使用 PostgreSQL，但並非所有公司都使用。如果不這樣做，他們可能仍然使用相同類型的數據庫系統，即關係數據庫管理系統 (RDBMS)。有些人將列式數據庫系統結合使用或作為其 RDBMS 的子集，以提高聚合性能。

像 Salesforce / Freshdesk 這樣的大公司如何在應用任何類型的過濾器的同時，在亞秒級的時間內為您提供數百萬條記錄的結果？

他們可以通過三種方式實現這一目標：

1. 以業務可接受的速率預聚合數據（例如數據倉庫實施）。根據業務和案例，有時每小時甚至每天是可以接受的。對於已經足夠快以在 2 秒內計算的指標，在您的案例中，理論上您可以每分鐘將該聚合結果記憶體到一個表中。（儘管我建議在將此類更改發佈到生產之前進行適當的完整系統測試。）
2. 返回估計的聚合。一些數據庫系統提供了獲得估計聚合以提高性能的方法。例如，APPROX_COUNT_DISTINCT在多個數據庫系統（SQL Server、Oracle SQL、Snowflake、Google BigQuery 等）中實現的通用函式通常在返回估計的不同計數時更快。

還有其他功能和方法可用於完成跨每個特定數據庫系統的估計聚合。這是一個可接受的解決方案，具體取決於業務和案例。例如，當您對術語進行 Google 搜尋時，dancing cat meme您會注意到 Google 在頁面頂部提供了一些指標：About 18,300,000 results (0.45 seconds). 因此，即使是一些最大的公司，使用估計的指標，仍然需要半秒鐘的時間來計算。

3. 嘗試列式儲存，它通常在計算聚合時性能更高。列儲存實現列級壓縮，這通常比 RDBMS 中的傳統行儲存更有效地壓縮（因為通常列內的值比行內的值更通用）。不同的數據庫系統提供不同的功能和解決方案來利用列式儲存。SQL Server 有一個稱為列儲存索引的功能，它是針對行儲存表創建的簡單的基於列的索引。Snowflake 是一個完全柱狀的數據庫系統。PostgreSQL 具有人們創建的擴展，用於向其添加列式儲存。對於正確的案例，列式儲存可以將聚合性能大致提高 10 倍到 100 倍或更多。

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