PostgreSQL 12.8 - AWS Aurora
Given a large data-set (~10B rows / 5TB size) residing in a partitioned table
CREATE TABLE large_table ( partition_date DATE, prime_entity INT, attribute_1 BIGINT, attribute_2 FLOAT, attribute_3 TEXT, value INT ) PARTITION BY RANGE (partition_date); CREATE INDEX ON large_table (prime_entity, attribute_1); CREATE INDEX ON large_table (prime_entity, attribute_2); CREATE INDEX ON large_table (prime_entity, attribute_3);
with daily partitioning
CREATE TABLE large_table__2023-01-01 PARTITION OF large_table FOR VALUES FROM ('2023-01-01') TO ('2023-01-02') :
and one of the most frequent access pattern (in conceptualized SQL code)
SELECT partition_date, prime_entity, <AGG>(value) AS agg_attribute FROM large_table WHERE -- mutually exclusive -- OPTION 1: partition_date BETWEEN '<start_date>' AND '<end_date>' -- OPTION 2: partition_date = '<date>' AND prime_entity = <int> AND attribute_[1,2,3] IN (<values>) GROUP BY 1, 2 ;
I see a non-trivial amount of overall execution time spent on either
Filter: ((partition_date >= '<start_date>'::date) AND (partition_date <= '<end_date>'::date))
Recheck Cond: ((partition_date >= '<start_date>'::date) AND (partition_date <= '<end_date>'::date))
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) for a
COUNT(*) with the above query structure and for all data in a single partition:
- here on the partitioned table, using a range filter on
- here on the partition itself without range filter
Naturally, the engine needs to validate actual column values against a range filter (as in
OPTION 1 above) when querying a ranged partition scheme.
However, one may come to think that it make less sense in the case of the
OPTION 2 filter, considering that individual partition bounds get hit fully inclusive - and subsequently even for
OPTION 1 when taking fully inclusive bounds into account.
Now, I am aware that optimizing arbitrary setups is close to impossible, and I do not expect the planner to guesstimate my intentions here - especially since I chose Range partitioning, as a natural scheme for date/time. But in my case I am defining clear partition bounds by
DATE in a daily partitioning scheme, and would want to benefit from the fact that a recheck on
partition_date is unnecessary.
Can I improve the partition scheme or setup otherwise, to improve on the given access pattern and overall performance?
- staying with the above, the first thing that comes to mind is adding
partition_dateto the indexes, i.e.
which would certainly increase index size, but might change the planners lookup strategy beneficially
CREATE INDEX ON large_table (partition_date, prime_entity, attribute_1); --or without attributes?
- however, what really interests me is: if I change to List partitioning on the
DATEcolumn instead, could
OPTION 2filters be applied without rechecks when partitions are defined by a list value? But at the same time solve range filters equally performant?
BTW.: I understand that
partition_range = <int>is not working well for Range partitioning, while I probably need to use
partition_range IN ('<dates>')for 'range' filters in List partitioning.