我的大表的 Postgresql 查詢非常慢

我的數據庫版本是 postgresql 9.5。

create table if not exists request_log
(
   id               bigserial not null constraint app_requests_pkey  primary key,
   request_date     timestamp not null,
   ip               varchar(50),
   start_time       timestamp,
   application_name varchar(200),
   request_path     text,
   display_url      text,
   username         varchar(50)
);

我有一個包含傳入 http 請求資訊的表。該id列是主鍵和索引。該表沒有關係。

所以我在這個表中有 72320081 行。當我執行計數查詢以獲取表計數時，select count(id) from request_log;查詢需要 3-5 分鐘。

此請求的explain(analyze, buffers, format text)結果是：

Aggregate  (cost=3447214.71..3447214.72 rows=1 width=0) (actual time=135575.947..135575.947 rows=1 loops=1)
 Buffers: shared hit=96 read=2551303
 ->  Seq Scan on request_log  (cost=0.00..3268051.57 rows=71665257 width=0) (actual time=2.517..129032.408 rows=72320081 loops=1)
       Buffers: shared hit=96 read=2551303
Planning time: 0.067 ms
Execution time: 135575.988 ms

這對我來說是非常糟糕的表現。由於性能問題，我無法從 Web 應用程序的表中獲取報告。

我的伺服器硬體來源是：

• 作業系統：Linux ubuntu 伺服器 16，在 Vmware 上
• 4核CPU
• 記憶體 6Gb
• 硬碟 120 Gb

我在晚上執行查詢，數據庫上沒有使用者，但速度很慢。如何解決這個問題？

計數行很慢，因為必須訪問表的所有行。

計數id甚至更慢，因為 PostgreSQL 首先必須檢查是否id為 NULL（不計算 NULL 值）。

有幾個選項可以加快速度：

• 使用更新版本的 PostgreSQL。

然後您可以獲得併行查詢，這將使執行更加昂貴，但速度更快。

• 使用索引id並保持桌子吸塵良好。

然後您可以獲得僅索引掃描。

• 使用帶有計數器的額外表，該計數器使用觸發器在大表上的每個數據修改語句上更新。

請查看我的部落格文章以進行深入討論。

您可以嘗試的其他方法是使用 MAX(id) 因為 id 欄位是具有範圍索引的自動遞增整數

這假設記錄永遠不會被刪除，並且 id 中沒有間隙。

select Max(id) from request_log where id >70,000,000

附加說明索引僅掃描工作在 9.6 或更高版本..

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