在 postgres 中索引時間戳列的推薦方法？

我有一個名為 ticket_details 的 PostgreSQL 表，它有很多列並且只在當天的行上不斷更新，它還插入了當天的數千行在幾列中有重複數據的行，特別是一個名為 td_date 類型的列時間戳 在該表的所有查詢中用於查找特定日期的行，尤其是當天。

目前我在進行查詢時遇到性能問題，因為在時間戳類型的 td_date 列中，我執行強制轉換以將其轉換為日期格式，並且當我嘗試創建以下索引以至少優化當天的數據時，我收到以下錯誤：

CREATE INDEX queries_recent_idx
                 ON sl01.tickets_details (td_date)
                 WHERE td_date::DATE = '2022-06-19';
ERROR:  functions in index predicate must be marked IMMUTABLE 

什麼是創建此索引的正確方法或有關如何分區此表的任何建議，因為它由於事務的數量而增長得非常快，並且插入每一行我必須對 x 產品的銷售求和並驗證幾個當天已經插入的行的參數，我願意通過驗證當天的數據和報告查詢的索引來幫助我有效地執行交易的建議，我將不勝感激。

當我進行查詢時，我使用(timezone('AST'::text, now()))::DATE.

該表具有的眾多查詢之一是：

SELECT 
COALESCE(SUM(t.td_amount), 0) AS sales, 
COALESCE(SUM(t.td_commission::FLOAT)::INT, 0) AS commission, 
COALESCE(SUM(p.prize), 0) AS prize,
COALESCE(SUM(t.td_amount), 0) - COALESCE(SUM(t.td_commission::FLOAT)::INT, 0) - COALESCE(SUM(p.prize), 0) AS sumary 
FROM sl01.tickets_details t
LEFT JOIN LATERAL (SELECT tx_id, numbers, draw_id, username, COALESCE(SUM(prize), 0) AS prize FROM sl01.prizes GROUP BY 1, 2, 3, 4) p ON p.tx_id  = t.tx_id AND p.numbers = t.number_played AND p.draw_id = t.draws_id AND p.username = t.user_id
WHERE t.td_date::DATE = timezone('AST', now())::DATE
AND t.td_status IN ('APROBADO', 'PAGADO', 'NO PAGADO')

實際上有 40 多個函式使用該表中的數據進行更新、插入和求和查詢，它們都有插入行的日期，目前有超過 300 萬行。

td_date 是 timestamptz 類型。

您將無法為該查詢創建索引，而且該查詢確實不是很可靠，因為它取決於參數的目前設置timezone。

您不應該使用AST，但您的 IANA 時區名稱America/Santo_Domingo並更改查詢如下：

WHERE CAST(t.td_date AT TIME ZONE 'America/Santo_Domingo' AS date) =
     CAST(current_timestamp AT TIME ZONE 'America/Santo_Domingo' AS date)

以下索引可以支持這一點：

CREATE INDEX ON sl01.tickets_details (CAST(td_date AT TIME ZONE 'America/Santo_Domingo' AS date));

或者，您可以將查詢更改為

WHERE t.td_date BETWEEN (date_trunc(
                            'day',
                            current_timestamp AT TIME ZONE 'America/Santo_Domingo'
                        ) AT TIME ZONE 'America/Santo_Domingo')
               AND (date_trunc(
                       'day',
                       current_timestamp AT TIME ZONE 'America/Santo_Domingo'
                    ) AT TIME ZONE 'America/Santo_Domingo')
                   + INTERVAL '1 day'

這可以通過一個簡單的索引來支持sl01.tickets_details(td_date)。

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