優化對一系列時間戳（一列）的查詢

我正在通過 Heroku 使用 Postgres 9.3。

我有一個表，“流量”，有 100 萬多條記錄，每天都有很多插入和更新。我需要在不同的時間範圍內跨此表執行 SUM 操作，這些呼叫可能需要長達 40 秒，我很想听聽有關如何改進它的建議。

我在這張桌子上有以下索引：

CREATE INDEX idx_traffic_partner_only ON traffic (dt_created) WHERE campaign_id IS NULL AND uuid_self <> uuid_partner;

這是一個範例 SELECT 語句：

SELECT SUM("clicks") AS clicks, SUM("impressions") AS impressions
FROM "traffic"
WHERE "uuid_self" != "uuid_partner"
AND "campaign_id" is NULL
AND "dt_created" >= 'Sun, 29 Mar 2015 00:00:00 +0000'
AND "dt_created" <= 'Mon, 27 Apr 2015 23:59:59 +0000' 

這是解釋分析：

Aggregate  (cost=21625.91..21625.92 rows=1 width=16) (actual time=41804.754..41804.754 rows=1 loops=1)
 ->  Index Scan using idx_traffic_partner_only on traffic  (cost=0.09..20085.11 rows=308159 width=16) (actual time=1.409..41617.976 rows=302392 loops=1)
     Index Cond: ((dt_created >= '2015-03-29'::date) AND (dt_created <= '2015-04-27'::date))
Total runtime: 41804.893 ms

http://explain.depesz.com/s/gGA

這個問題與 SE 上的另一個問題非常相似，但其中一個使用了跨兩個列時間戳範圍的索引，並且該查詢的索引規劃器的估計值相差甚遠。主要建議是創建一個排序的多列索引，但對於沒有太大影響的單列索引。其他建議是使用 CLUSTER / pg_repack 和 GIST 索引，但我還沒有嘗試過，因為我想看看使用正常索引是否有更好的解決方案。

優化對一系列時間戳（兩列）的查詢

作為參考，我嘗試了以下數據庫未使用的索引：

INDEX idx_traffic_2 ON traffic (campaign_id, uuid_self, uuid_partner, dt_created);
INDEX idx_traffic_3 ON traffic (dt_created);
INDEX idx_traffic_4 ON traffic (uuid_self);
INDEX idx_traffic_5 ON traffic (uuid_partner);

編輯： Ran EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS) 這些是結果：

Aggregate  (cost=20538.62..20538.62 rows=1 width=8) (actual time=526.778..526.778 rows=1 loops=1)
 Output: sum(clicks), sum(impressions)
 Buffers: shared hit=47783 read=29803 dirtied=4
 I/O Timings: read=184.936
 ->  Index Scan using idx_traffic_partner_only on public.traffic  (cost=0.09..20224.74 rows=313881 width=8) (actual time=0.049..431.501 rows=302405 loops=1)
     Output: id, uuid_self, uuid_partner, impressions, clicks, dt_created... (other fields redacted)
     Index Cond: ((traffic.dt_created >= '2015-03-29'::date) AND (traffic.dt_created <= '2015-04-27'::date))
     Buffers: shared hit=47783 read=29803 dirtied=4
     I/O Timings: read=184.936
Total runtime: 526.881 ms

http://explain.depesz.com/s/7Gu6

表定義：

CREATE TABLE traffic (
   id              serial,
   uuid_self       uuid not null,
   uuid_partner    uuid not null,
   impressions     integer NOT NULL DEFAULT 1,
   clicks          integer NOT NULL DEFAULT 0,
   campaign_id     integer,
   dt_created      DATE DEFAULT CURRENT_DATE NOT NULL,
   dt_updated      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
)

id 是主鍵，uuid_self、uuid_partner 和campaign_id 都是外鍵。dt_updated 欄位使用 postgres 函式進行更新。

這裡有兩件事很奇怪：

1. 該查詢從具有 1M+ 行的表中選擇 300k 行。對於 30 %（或任何超過 5 % - 取決於行大小和其他因素），通常根本不需要使用索引。我們應該看到順序掃描。

例外情況是僅索引掃描，我在這裡看不到。如果您從中獲得僅索引掃描，那麼@Craig 建議的多列索引將是最佳選擇。像你提到的那樣有很多更新，這可能行不通，在這種情況下，沒有額外的列你會更好 - 只是你已經擁有的索引。您可能可以通過更積極的表自動真空設置使其為您工作。您可以調整各個表的參數。 2. 雖然 Postgres 將使用索引，但我當然希望看到對那麼多行進行**點陣圖索引掃描，**而不是普通索引掃描，這通常是低百分比行的更好選擇。一旦 Postgres 預計每個數據頁有多個命中（從它在表上的統計數據來看），它通常會切換到點陣圖索引掃描。

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

據此判斷，我懷疑您的成本設置不足（也可能是表格統計資訊）。您可能已經設置random_page_cost和/或太低，相對於. 按照連結並閱讀手冊。cpu_index_tuple_cost seq_page_cost

正如我們在評論中得出的那樣，也符合**冷記憶體是重要因素的觀察。**您是否正在訪問長時間沒有人接觸過的（部分）表，或者您正在執行在尚未填充記憶體的測試系統上？

否則，您只是沒有足夠的 RAM 來記憶體數據庫中的大部分相關數據。因此，當數據駐留在記憶體中時，隨機訪問比順序訪問要昂貴得多。根據實際情況，您可能需要進行調整以獲得更好的查詢計劃。

對於第一次只讀的緩慢響應，必須提到另一個因素：提示位。閱讀Postgres Wiki 中的詳細資訊和此相關問題：

• 為什麼 SELECT 語句會在 Postgres 中弄髒記憶體緩衝區？

或者表非常臃腫，在這種情況下，索引掃描是有意義的，我會CLUSTER在你引用的我之前的答案中引用/ pg_repack。（或者只是VACUUM FULL)調查您的VACUUM設置。這些對於many inserts and updates every day.

根據UPDATE模式，還可以考慮FILLFACTOR低於 100。如果您主要只更新新添加的行，請在壓縮表格FILLFACTER 後設置較低的值，以便只有新頁面為更新保留一些擺動空間。

• 記憶體表的填充因子是多少？
• 在 9.1 下是否仍建議使用正常 VACUUM ANALYZE？

架構

campaign_id是 99%+ NULL 並且dt_updated是 0% NULL。

稍微調整列的順序，以每行節省 8 個字節（在 99% 的情況下campaign_id為 NULL）：

CREATE TABLE traffic (
   uuid_self       uuid not null REFERENCES ... ,
   uuid_partner    uuid not null REFERENCES ... ,
   id              serial PRIMARY KEY,
   impressions     integer NOT NULL DEFAULT 1,
   clicks          integer NOT NULL DEFAULT 0,
   campaign_id     integer,
   dt_created      DATE DEFAULT CURRENT_DATE NOT NULL,
   dt_updated      TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
);

詳細解釋和更多連結：

• 為讀取性能配置 PostgreSQL

測量：

• 測量 PostgreSQL 表行的大小

在我看來，您正在查詢大索引中的大量數據，因此速度很慢。那裡沒有什麼明顯的錯誤。

如果您使用的是 PostgreSQL 9.3 或 9.4，您可以嘗試通過將其設置為各種覆蓋索引來查看是否可以獲得僅索引掃描。

CREATE INDEX idx_traffic_partner_only 
ON traffic (dt_created, clicks, impressions)
WHERE campaign_id IS NULL 
 AND uuid_self <> uuid_partner;

PostgreSQL 沒有真正的覆蓋索引或支持只是值的索引項，而不是 b 樹的一部分，因此這比使用這些功能可能更慢且更昂貴。如果真空執行的頻率足以使可見性地圖保持最新，那麼它可能仍然比普通索引掃描更勝一籌。

---

理想情況下，PostgreSQL 將支持索引中的輔助數據欄位，例如 MS-SQL Server（這種語法在 PostgreSQL 中不起作用）：

-- This will not work in PostgreSQL (at least 9.5)
-- it's an example of what I wish did work. Don't
-- comment to say it doesn't work.
--
CREATE INDEX idx_traffic_partner_only 
ON traffic (dt_created)
INCLUDING (clicks, impressions) -- auxillary data columns
WHERE campaign_id IS NULL 
 AND uuid_self <> uuid_partner;

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