為讀取性能配置 PostgreSQL
我們的系統寫入大量數據（一種大數據系統）。寫入性能足以滿足我們的需求，但讀取性能確實太慢了。
我們所有表的主鍵（約束）結構都是相似的：
timestamp(Timestamp) ; index(smallint) ; key(integer).一個表可以有數百萬行，甚至數十億行，而一個讀取請求通常是針對特定時期（時間戳/索引）和標籤的。查詢返回大約 200k 行是很常見的。目前，我們每秒可以讀取大約 15k 行，但我們需要快 10 倍。這可能嗎？如果可以，怎麼做？
注意： PostgreSQL 與我們的軟體打包在一起，因此硬體因客戶端而異。
它是一個用於測試的虛擬機。VM 的主機是具有 24.0 GB RAM 的 Windows Server 2008 R2 x64。
伺服器規範（虛擬機 VMWare）
Server 2008 R2 x64 2.00 GB of memory Intel Xeon W3520 @ 2.67GHz (2 cores)
postgresql.conf優化shared_buffers = 512MB (default: 32MB) effective_cache_size = 1024MB (default: 128MB) checkpoint_segment = 32 (default: 3) checkpoint_completion_target = 0.9 (default: 0.5) default_statistics_target = 1000 (default: 100) work_mem = 100MB (default: 1MB) maintainance_work_mem = 256MB (default: 16MB)表定義
CREATE TABLE "AnalogTransition" ( "KeyTag" integer NOT NULL, "Timestamp" timestamp with time zone NOT NULL, "TimestampQuality" smallint, "TimestampIndex" smallint NOT NULL, "Value" numeric, "Quality" boolean, "QualityFlags" smallint, "UpdateTimestamp" timestamp without time zone, -- (UTC) CONSTRAINT "PK_AnalogTransition" PRIMARY KEY ("Timestamp" , "TimestampIndex" , "KeyTag" ), CONSTRAINT "FK_AnalogTransition_Tag" FOREIGN KEY ("KeyTag") REFERENCES "Tag" ("Key") MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITH ( OIDS=FALSE, autovacuum_enabled=true );詢問
該查詢在 pgAdmin3 中執行大約需要 30 秒，但如果可能的話，我們希望在 5 秒內得到相同的結果。
SELECT "AnalogTransition"."KeyTag", "AnalogTransition"."Timestamp" AT TIME ZONE 'UTC', "AnalogTransition"."TimestampQuality", "AnalogTransition"."TimestampIndex", "AnalogTransition"."Value", "AnalogTransition"."Quality", "AnalogTransition"."QualityFlags", "AnalogTransition"."UpdateTimestamp" FROM "AnalogTransition" WHERE "AnalogTransition"."Timestamp" >= '2013-05-16 00:00:00.000' AND "AnalogTransition"."Timestamp" <= '2013-05-17 00:00:00.00' AND ("AnalogTransition"."KeyTag" = 56 OR "AnalogTransition"."KeyTag" = 57 OR "AnalogTransition"."KeyTag" = 58 OR "AnalogTransition"."KeyTag" = 59 OR "AnalogTransition"."KeyTag" = 60) ORDER BY "AnalogTransition"."Timestamp" DESC, "AnalogTransition"."TimestampIndex" DESC LIMIT 500000;解釋 1
"Limit (cost=0.00..125668.31 rows=500000 width=33) (actual time=2.193..3241.319 rows=500000 loops=1)" " Buffers: shared hit=190147" " -> Index Scan Backward using "PK_AnalogTransition" on "AnalogTransition" (cost=0.00..389244.53 rows=1548698 width=33) (actual time=2.187..1893.283 rows=500000 loops=1)" " Index Cond: (("Timestamp" >= '2013-05-16 01:00:00-04'::timestamp with time zone) AND ("Timestamp" <= '2013-05-16 15:00:00-04'::timestamp with time zone))" " Filter: (("KeyTag" = 56) OR ("KeyTag" = 57) OR ("KeyTag" = 58) OR ("KeyTag" = 59) OR ("KeyTag" = 60))" " Buffers: shared hit=190147" "Total runtime: 3863.028 ms"解釋 2
在我最近的測試中，我花了 7 分鐘來選擇我的數據！見下文：
"Limit (cost=0.00..313554.08 rows=250001 width=35) (actual time=0.040..410721.033 rows=250001 loops=1)" " -> Index Scan using "PK_AnalogTransition" on "AnalogTransition" (cost=0.00..971400.46 rows=774511 width=35) (actual time=0.037..410088.960 rows=250001 loops=1)" " Index Cond: (("Timestamp" >= '2013-05-22 20:00:00-04'::timestamp with time zone) AND ("Timestamp" <= '2013-05-24 20:00:00-04'::timestamp with time zone) AND ("KeyTag" = 16))" "Total runtime: 411044.175 ms"
數據對齊和儲存大小
實際上，每個索引元組的成本是元組標頭的 8 個字節加上項目標識符的 4 個字節。
有關的：
我們有三列作為主鍵：
PRIMARY KEY ("Timestamp" , "TimestampIndex" , "KeyTag") "Timestamp" timestamp (8 bytes) "TimestampIndex" smallint (2 bytes) "KeyTag" integer (4 bytes)結果是：
頁眉中的項目標識符 4 個字節（不計入 8 個字節的倍數） 索引元組頭 8 個字節 8 字節“時間戳” 2 個字節“時間戳索引” 用於數據對齊的 2 個字節填充 4 字節“KeyTag” 0 填充到最接近的 8 個字節的倍數 ----- 每個索引元組 28 個字節；加上一些字節的成本。關於在此相關答案中測量對像大小：
多列索引中的列順序
閱讀這兩個問題和答案以了解：
您擁有索引（主鍵）的方式，您可以在沒有排序步驟的情況下檢索行，這很有吸引力，尤其是使用
LIMIT. 但是檢索行似乎非常昂貴。通常，在多列索引中，“平等”列應該放在最前面，“範圍”列應該放在最後：
因此，嘗試使用反向列順序的附加索引：
CREATE INDEX analogransition_mult_idx1 ON "AnalogTransition" ("KeyTag", "TimestampIndex", "Timestamp");這取決於數據分佈。但是
millions of row, even billion of rows這樣可能會更快。由於數據對齊和填充，元組大小增加了 8 個字節。如果您使用它作為普通索引，您可能會嘗試刪除第三列
"Timestamp"。可能會更快或沒有（因為它可能有助於排序）。您可能希望保留這兩個索引。根據許多因素，您的原始索引可能更可取 - 特別是對於較小的
LIMIT.autovacuum 和表統計資訊
您的表統計資訊需要是最新的。我相信你有autovacuum執行。
由於您的表似乎很大並且統計資訊對於正確的查詢計劃很重要，因此我將大大增加相關列的統計資訊目標：
ALTER TABLE "AnalogTransition" ALTER "Timestamp" SET STATISTICS 1000;…甚至更高，有數十億行。最大值為 10000，預設為 100。
WHERE對orORDER BY子句中涉及的所有列執行此操作。然後執行ANALYZE。表格佈局
在此過程中，如果您應用您所學到的有關數據對齊和填充的知識，這種優化的表格佈局應該會節省一些磁碟空間並有助於提高性能（忽略 pk 和 fk）：
CREATE TABLE "AnalogTransition"( "Timestamp" timestamp with time zone NOT NULL, "KeyTag" integer NOT NULL, "TimestampIndex" smallint NOT NULL, "TimestampQuality" smallint, "UpdateTimestamp" timestamp without time zone, -- (UTC) "QualityFlags" smallint, "Quality" boolean, "Value" numeric );
CLUSTER/ pg_repack / pg_squeeze要優化使用某個索引（無論是您的原始索引還是我建議的替代索引）的查詢的讀取性能，您可以按照索引的物理順序重寫表。
CLUSTER這樣做，但它相當具有侵入性，並且在操作期間需要獨占鎖。
pg_repack是一種更複雜的替代方法，它可以在沒有對錶的排他鎖的情況下做同樣的事情。
pg_squeeze是後來的類似工具（尚未使用它）。這對大表有很大幫助，因為需要讀取的表塊要少得多。
記憶體
通常，2GB 的物理 RAM 不足以快速處理數十億行。更多的記憶體可能會有很長的路要走——伴隨著調整的設置：顯然一
effective_cache_size開始就更大。