PostgreSQL - (declarative) List vs. Range partitioning for DATE's; avoid recheck

Question

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.