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.
(timezone('AST'::text, now()))::DATE
doesn't require theCAST
(::
) toTEXT
- it works fine without it!SELECT * FROM pg_timezone_names
- maybeAmerica/Halifax
might do for you? Then, you don't have to worry about which timezone applies at what part of the year - the timezone functionality of PostgreSQL will handle that for you! I don't know if you sell stuff around midnight - but it could be an issue around the change from DST to Standard Time for that zone!