管理和加速對超過 3 萬億行的 PostgreSQL 表的查詢

我有跨越 10 年的時間序列數據，有超過 3 萬億行和 10 列。

目前我使用具有 128GB RAM 的 PCIe SSD，我發現查詢需要大量時間。例如，執行以下命令需要 15 分鐘以上：

SELECT * FROM tbl WHERE column_a = 'value1' AND column_b = 'value2';

該表主要用於讀取。唯一一次寫入表是在每週更新期間插入大約 1500 萬行。

管理這麼大的表的最佳方法是什麼？您會建議按年拆分嗎？

表大小為 542 GB，外部大小為 109 GB。

EXPLAIN (BUFFERS, ANALYZE)輸出：

"Seq Scan on table  (cost=0.00..116820941.44 rows=758 width=92) (actual time=0.011..1100643.844 rows=667 loops=1)"
"  Filter: (("COLUMN_A" = 'Value1'::text) AND ("COLUMN_B" = 'Value2'::text))"
"  Rows Removed by Filter: 4121893840"
"  Buffers: shared hit=2 read=56640470 dirtied=476248 written=476216"
"Total runtime: 1100643.967 ms"

該表是使用以下程式碼創建的：

CREATE TABLE tbl (
 DATE     timestamp with time zone,
 COLUMN_A text,
 COLUMN_B text,
 VALUE_1  double precision,
 VALUE_2  double precision,
 VALUE_3  double precision,
 VALUE_4  double precision,
 VALUE_5  double precision,
 VALUE_6  double precision,
 VALUE_7  double precision,
);

CREATE INDEX ix_table_name_date ON table_name (DATE);

您現有的索引DATE顯然對查詢毫無用處。查詢的第一個明顯步驟：

SELECT * FROM tbl WHERE column_a = 'value1' AND column_b = 'value2';

是or的索引（哪個更具選擇性）或可能是 上的多列索引，例如：column_a``column_b``(column_a, column_b)

CREATE INDEX tbl_a_b_idx ON tbl(column_a, column_b);

細節：

• 複合索引是否也適用於第一個欄位的查詢？

接下來，如果您可以安全地從查詢中排除大部分錶，我會考慮部分索引。或者將您的表劃分為 100 個分區。並使用約束排除。

DATE正如您所設想的那樣，“按年拆分”與您對給定查詢的索引一樣無用（甚至有害） 。分區需要基於查詢謂詞 (column_a和column_b) 中的列才有幫助。在這方面，僅針對一列的謂詞會容易得多。如果您對各種列有重要的查詢過濾，那麼分區可能不是要走的路。（部分索引仍然可能。）

如果分區不好，那麼侵入性較小的措施是CLUSTER基於新索引的數據（您不能為此使用部分索引）。或者只是從查詢的排序輸出中創建一個新表。這特別有趣，因為您的表大多是只讀的。至少這樣做一次應該是值得的，但那一次將非常昂貴：必須以一種或另一種方式重寫整個表。您需要足夠的可用空間、盡可能多的 RAM 以及表上的排他鎖。或者使用pg_repack, 來避免排他鎖：

• 為讀取性能配置 PostgreSQL
• 在大表中填充新列的最佳方法？

確保執行最新版本的 Postgres。即將發布的Postgres 9.5可能對您來說特別有趣，因為它引入了BRIN 索引（塊範圍索引），它可以顯著減少非常大的表的索引大小。可能正是您正在尋找的。

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