0

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

or

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 partition_range
  • 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?

Obvious ideas:

  • staying with the above, the first thing that comes to mind is adding partition_date to the indexes, i.e.
    CREATE INDEX ON large_table (partition_date, prime_entity, attribute_1); --or without attributes?
    
    which would certainly increase index size, but might change the planners lookup strategy beneficially
  • however, what really interests me is: if I change to List partitioning on the DATE column instead, could OPTION 2 filters 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.
7
  • What makes you think that the check on partition_date is responsible for the long duration? Please add the complete EXPLAIN (ANALYZE, BUFFERS) output to the question. Jan 11 at 12:26
  • @LaurenzAlbe okay, I added in the depesz links to the question body. As I tried to say before I accidentally broke-deleted the question was: by comparing the Bitmap Heap Scan nodes, mainly, of a comparison between queries on the partitioned table with range filter and the partitions directly with no range filter.
    – t.ry
    Jan 11 at 13:20
  • 1
    The plans are the same, and the difference is only that one query had to read lots of data from a slow disk. Jan 11 at 13:57
  • @LaurenzAlbe interesting. The DB was under heavy load when I queried, so I assume this is due to resource management? Since the two plans query the same table. Anyways, obviously I have a filter condition in one plan that, if not used to verify page hits or whatever else that I do not know, seems to be applied on millions of rows - a tiny, yet numerous operation that is not necessary, strictly speaking. Which brings us back to my question.
    – t.ry
    Jan 11 at 14:47
  • I don't believe the filter makes a difference. Try running the queries several times and see if you see much difference when everything is cached. Jan 11 at 15:19

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy