10 億行表上 select 查詢的非確定性性能，從 1 秒到 60 秒
我正在嘗試調查為什麼此查詢的性能如此不確定。它可能需要 1 秒到 60 秒及以上的任何時間。查詢的本質是選擇一個“時間視窗”，並從該時間視窗內獲取所有行。
這是有問題的查詢，在大約 10 億行的表上執行：
SELECT CAST(extract(EPOCH from ts)*1000000 as bigint) as ts , ticks , quantity , side FROM order_book WHERE ts >= TO_TIMESTAMP(1618882633073383/1000000.0) AND ts < TO_TIMESTAMP(1618969033073383/1000000.0) AND zx_prod_id = 0 ORDER BY ts ASC, del desc;這就是創建表的方式
CREATE TABLE public.order_book ( ts timestamp with time zone NOT NULL, zx_prod_id smallint NOT NULL, ticks integer NOT NULL, quantity integer NOT NULL, side boolean NOT NULL, del boolean NOT NULL )
TO_TIMESTAMP當我走遍整張桌子時，其中的值將繼續向前滑動。這是EXPLAIN ANALYZE兩個不同時間視窗上相同查詢的輸出：性能緩慢
Gather Merge (cost=105996.20..177498.48 rows=586308 width=18) (actual time=45196.559..45280.769 rows=539265 loops=1) Workers Planned: 6 Workers Launched: 6 Buffers: shared hit=116386 read=42298 -> Sort (cost=104996.11..105240.40 rows=97718 width=18) (actual time=45169.717..45176.775 rows=77038 loops=7) Sort Key: (((date_part('epoch'::text, _hyper_16_214_chunk.ts) * '1000000'::double precision))::bigint), _hyper_16_214_chunk.del DESC Sort Method: quicksort Memory: 9327kB Worker 0: Sort Method: quicksort Memory: 8967kB Worker 1: Sort Method: quicksort Memory: 9121kB Worker 2: Sort Method: quicksort Memory: 9098kB Worker 3: Sort Method: quicksort Memory: 9075kB Worker 4: Sort Method: quicksort Memory: 9019kB Worker 5: Sort Method: quicksort Memory: 9031kB Buffers: shared hit=116386 read=42298 -> Result (cost=0.57..96897.07 rows=97718 width=18) (actual time=7.475..45131.932 rows=77038 loops=7) Buffers: shared hit=116296 read=42298 -> Parallel Index Scan using _hyper_16_214_chunk_order_book_ts_idx on _hyper_16_214_chunk (cost=0.57..95187.01 rows=97718 width=18) (actual time=7.455..45101.670 rows=77038 loops=7) Index Cond: ((ts >= '2021-04-22 01:34:31.357179+00'::timestamp with time zone) AND (ts < '2021-04-22 02:34:31.357179+00'::timestamp with time zone)) Filter: (zx_prod_id = 0) Rows Removed by Filter: 465513 Buffers: shared hit=116296 read=42298 Planning Time: 1.107 ms JIT: Functions: 49 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 9.273 ms, Inlining 0.000 ms, Optimization 2.008 ms, Emission 36.235 ms, Total 47.517 ms Execution Time: 45335.178 ms快速性能
Gather Merge (cost=105095.94..170457.62 rows=535956 width=18) (actual time=172.723..240.628 rows=546367 loops=1) Workers Planned: 6 Workers Launched: 6 Buffers: shared hit=158212 -> Sort (cost=104095.84..104319.16 rows=89326 width=18) (actual time=146.702..152.849 rows=78052 loops=7) Sort Key: (((date_part('epoch'::text, _hyper_16_214_chunk.ts) * '1000000'::double precision))::bigint), _hyper_16_214_chunk.del DESC Sort Method: quicksort Memory: 11366kB Worker 0: Sort Method: quicksort Memory: 8664kB Worker 1: Sort Method: quicksort Memory: 8986kB Worker 2: Sort Method: quicksort Memory: 9116kB Worker 3: Sort Method: quicksort Memory: 8858kB Worker 4: Sort Method: quicksort Memory: 9057kB Worker 5: Sort Method: quicksort Memory: 6611kB Buffers: shared hit=158212 -> Result (cost=0.57..96750.21 rows=89326 width=18) (actual time=6.145..127.591 rows=78052 loops=7) Buffers: shared hit=158122 -> Parallel Index Scan using _hyper_16_214_chunk_order_book_ts_idx on _hyper_16_214_chunk (cost=0.57..95187.01 rows=89326 width=18) (actual time=6.124..114.023 rows=78052 loops=7) Index Cond: ((ts >= '2021-04-22 01:34:31.357179+00'::timestamp with time zone) AND (ts < '2021-04-22 02:34:31.357179+00'::timestamp with time zone)) Filter: (zx_prod_id = 4) Rows Removed by Filter: 464498 Buffers: shared hit=158122 Planning Time: 0.419 ms JIT: Functions: 49 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 10.405 ms, Inlining 0.000 ms, Optimization 2.185 ms, Emission 39.188 ms, Total 51.778 ms Execution Time: 274.413 ms我將此輸出解釋為該並行索引掃描的大部分責任。
起初，我嘗試將記憶體
work_mem增加到 1 GB 和shared_buffers24 GB，認為它可能無法滿足 RAM 中所需的所有內容，但這似乎沒有幫助。接下來，我嘗試在 上創建索引
(zx_prod_id, ts)，認為並行索引掃描上的過濾器可能需要一段時間，但這似乎也沒有任何作用。我不是數據庫專家，所以我已經用盡了我的知識極限。
解釋
兩個查詢計劃之間最重要的區別是
read=xyz在慢版本的多個位置添加了位。慢的：
Buffers: shared hit=116296 read=42298快速地：
Buffers: shared hit=158122這告訴你 Postgres 遇到了尚未記憶體的數據（或索引）頁面。重複慢速查詢（可能不止一次，直到添加的
read=xyz內容消失），然後您將在同等記憶體的數據頁上看到相當的性能。有關的：
值得注意的是，增加**
work_mem**實際上會損害您的情況，因為work_mem記憶體記憶體（作業系統記憶體和 Postres 自己的共享記憶體緩衝區）是可用 RAM 的競爭對手。設置work_mem得更高會導致數據更快地從（然後更小的）記憶體中清除，因此您的“慢查詢”變得更有可能發生。不要忘記 Postgres 可以使用work_mem. 手冊：請注意，對於復雜的查詢，可能會並行執行多個排序或散列操作；在開始將數據寫入臨時文件之前，通常允許每個操作使用此值指定的記憶體量。此外，幾個正在執行的會話可以同時執行此類操作。因此，使用的總記憶體可能是值的許多倍
work_mem；您的查詢使用 6 個並行工作人員，這已經是 6 倍
work_mem- 可能是最壞的情況；並非每個工人都會使用其最大津貼。如果不足，您會在輸出的各種上下文中
work_mem看到提到的內容。這就是額外的順序。disk``EXPLAIN``work_mem增加**
shared_buffers**可能更有意義。不過，也不要設置得太高。手冊：如果您有一個具有 1GB 或更多 RAM 的專用數據庫伺服器，則 shared_buffers 的合理起始值是系統中記憶體的 25%。在某些工作負載中，更大的設置
shared_buffers也很有效，但由於 PostgreSQL 還依賴於作業系統記憶體，分配超過 40% 的 RAM 不太可能比較shared_buffers小的數量更好。如果您有足夠的 RAM 來儲存所有內容（很難想像*“具有 10 億行的表”*），那麼所缺少的只是所討論的數據頁還沒有被記憶體。您可能對
pg_prewarm. 看：另外：第二個查詢計劃顯示
Filter: (zx_prod_id = 4)，它與顯示的查詢不匹配，但我認為這對問題無關緊要。其他改進
您已經嘗試過**索引，
(zx_prod_id, ts)**但它…似乎什麼也沒做。
不過，該索引通常看起來很適合查詢。
多列索引比僅以下索引大 50% 左右
(ts)：4 + 8 + 16 = 28 字節與每個索引元組 4 + 8 + 8 = 20 字節，並且“索引重複數據刪除”的壓縮可能更少。由於 RAM 似乎是一種有爭議的資源，這是一個缺點。看：我看
rows=539265vsRows Removed by Filter: 465513.. 所以查詢讀取了它需要的兩倍行。根據數據分佈，該指數可能會或可能不會有所改善。如果您的表在該索引上物理聚集，那肯定是這樣。然後大多數數據頁包含具有相同zx_prod_id值的行，這使得該索引更加有效。再次，請參閱：您的查詢似乎總是過濾單個值
zx_prod_id. 如果這集中在幾個值上，或者如果開始時只有幾個不同的值，那麼幾個部分索引可能是有意義的。就像，如果只有zx_prod_id IN (1,4)目標：CREATE INDEX order_book_ts_zx1_idx ON order_book (ts) WHERE zx_prod_id = 1; CREATE INDEX order_book_ts_zx4_idx ON order_book (ts) WHERE zx_prod_id = 4;然後查詢可以使用（小得多）的索引，並且不必過濾一半的行。
ts顯然是類型**timestamptz**。您仍然將時間戳作為bigint代表微秒的數字來處理。這種拆分方法有缺點和陷阱。通常最好在整個食物鏈中使用時間戳。為每一行執行CAST(extract(EPOCH from ts)*1000000 as bigint)不是免費的。加起來有 50 萬行。