Hi,
I have a large table of immutable events that need to be aggregated regularly to derive statistics. To improve the performance, that table is rolled up every 15minutes, so that online checks can aggregate rolled up data and combine it with latest events created after the last roll up.
To implement this a query is executed that selects only events after the time of the last rollup.
That time is determined dynamically based on a log table.
When using a sub select or CTE to get the latest roll up time, the query planner fails to recognize that a most of the large table would be filtered out by the condition and tries a sequential scan instead of an index scan.
When using the literal value for the WHERE condition, the plan correctly uses an index scan, which is much faster.
I analyzed the involved tables and increased the collected histogram, but the query plan did not improve. Is there a way to help the query planner recognize this in the dynamic case?
Best Regards
Chris
==== Original query with a CTE to get the timestamp to filter on
EXPLAIN (ANALYZE, BUFFERS) WITH current_rollup AS (
SELECT COALESCE(MAX(window_end), '-infinity') AS cutoff
FROM exchange.ledger_zerosum_rollup
)
SELECT *
FROM exchange.ledger
WHERE created > (SELECT cutoff FROM current_rollup);
==== Query with literal value
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM exchange.ledger
WHERE created > '2020-02-10T08:54:39.857789Z';