Recommended way to index a timestamp column in postgres?

Question

I have a PostgreSQL table called tickets_details, which has many columns and is constantly updated only on the rows of the current day, it also inserts thousands of rows of the current day that have duplicate data in several columns, especially a column called td_date of type timestamp the which is used in all queries of this table to find rows for specific dates, especially the current day.

Currently I have performance problems when making queries because in the td_date column of type timestamp I perform a cast to convert it to date format and when I try to create the following index to optimize at least the data of the current day I got the following error:

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 

What would be the correct way to create this index or any suggestions on how to partition this table, since it grows very fast due to the number of transactions and to insert each row I must make a sum of the sale of x product and validate several parameters of the rows already inserted on that day, I am open to suggestions that can help me to carry out the transactions efficiently by validating the data of the current day and the index for queries of reports, I would appreciate any help.

When I make the queries I use (timezone('AST'::text, now()))::DATE.

One of the many queries this table has is this:

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')

There are actually more than 40 functions that use data from this table for updates, inserts and sum queries, they all have in common the date the rows were inserted, it currently has more than 3 million rows.

td_date is of type timestamptz.

Answer 1

You won't be able to create an index for that query, and indeed the query is not very reliable, because it depends on the current setting of the parameter timezone.

You shouldn't be using AST, but your IANA time zone name America/Santo_Domingo and change the query as follows:

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

That can be supported by the following index:

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

Alternatively, you could change the query to

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'

That could be supported by a simple index on sl01.tickets_details(td_date).