Unix 時間戳與 timestamptz 索引性能

我是數據庫管理員世界的新手，並且在 Postgres 數據庫中有一個包含多種貨幣的加密貨幣報價數據的大表（20 億行）。我的大部分查詢將涉及分​​別為每種貨幣選擇一個移動時間視窗 - 為此，我目前在我的“時間戳”和符號列上有一個索引。

查詢似乎很慢，大約需要 100 秒來查詢最近 2 天的數據以獲取 1 個符號（約 130,000 行）（我檢查了解釋分析並且正在命中復合索引）

這是踢球者 - 我將時間戳儲存為 Unix 時間戳作為 int8 例如 1611751286078083

將這些轉換為timestamptz，將它們儲存在新列中，然後將其用作索引是否值得/加速查詢？我讀到使用已經在另一列中可用的數據創建一個新列是不好的做法。

如果是這樣，添加這個新列是否可能會破壞我現有的（不斷執行的）INSERT 腳本，該腳本只插入目前定義的列？

tldr;

是一個timestamptz明顯比儲存為int8.

Index Scan using huobi_timestamp_symbol_idx on huobi  (cost=0.58..438627.67 rows=177735 width=48) (actual time=0.041..101131.528 rows=488785 loops=1)
 Index Cond: (("timestamp" > '1611751286078083'::bigint) AND (symbol = 'YFII'::text))
 Buffers: shared hit=68973 read=219478
 I/O Timings: read=99426.796
Planning Time: 13.925 ms
Execution Time: 101179.934 ms

我在 (timestamp desc, symbol) 上有 1 個索引，主鍵在 (timestamp, market) 上，並且正在使用 AWS 上託管的 Postgres 12.3。

回答您的基本問題：否。timestamptz在內部儲存為 64 位整數（與 相同int8）。如果使用正確，其上的索引與bigint( ) 列上的索引執行相同。int8有關的：

• 在 Rails 和 PostgreSQL 中完全忽略時區

如果有疑問，請使用timestamptz. 它是為此目的而建造的。支持 Unix 時間戳的唯一理由是您已經擁有它們並在其他地方使用它們。

至於後面添加的查詢計劃。考慮一個像 bbaird 建議的多列索引。看：

• 多列索引和性能

您可能會物理地重寫表以幫助此特定查詢。CLUSTER需要一個排他鎖。要處理並發寫入負載，請考慮pg_rewriteor pg_squeeze。要麼需要額外的儲存空間和大桌子的時間。看：

• 為讀取性能配置 PostgreSQL

我將回答問題的精神，即“會timestampz修復我的查詢性能嗎？”

既然您已經提供了索引定義，我可以肯定地說，您的索引針對您正在執行的搜尋類型設置錯誤。

現在您的索引按時間戳排序。這對於在某個時間點或某個時間間隔內查找所有行很有用。但是，如果您正在尋找某個符號，那麼您必須閱讀大量的索引，而這是不必要的。

您要求在某個symbol時間點之後查找某個特定的行，這意味著您的索引應該是(symbol,timestamp)，這將首先組織 b 樹symbol，然後是timestamp。

這將解決部分問題，並且很容易實現。但是，還有另外兩個考慮因素：

1. 當在 Postgres 中插入數據時，它被附加到堆中，沒有邏輯順序。這可能會導致您希望的行分佈在堆中的多個頁面上，從而導致無論您選擇什麼索引，查詢性能都很差。
2. 您的主鍵(market,timestamp)意味著存在某種關係，market並且symbol可能會從表中刪除一個（並且該關係在單獨的表中維護）。

我的建議是：

1. 用作timestamp您定義的任何 b 樹中的最後一列。
2. 如果查詢性能仍然很差，請根據您最常執行的搜尋對錶進行 CLUSTER，並定期執行 CLUSTER 以保持性能。在一張大桌子上，第一次可能需要一段時間 - 您可能可以搜尋如何優化該操作。

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