Re: PG11 - Multiple Key Range Partition

On Mon, 8 Jul 2019 at 21:17, Rares Salcudean
<rares(dot)salcudean(at)takeofflabs(dot)com> wrote:
> Currently, I'm trying to define a table with a range partition of 3 keys Recent, Deleted and Played At.
>
> There are multiple partitions:
>
> 1. scores_2018 (recent - false, deleted - false, played_at in (2018-01-01 / 2019-01-01)
> 2. scores_2017 (recent - false, deleted - false, played_at in (2017-01-01 / 2018-01-01)
> 3. scores_recent (recent - true, deleted - false, played_at in (1990-01-01 / 2090-01-01)
> 4. scores_deleted (recent - false, deleted - true, played_at in (1990-01-01 / 2090-01-01)
>
> When inserting all works fine, but when doing a select statement it does not correctly query over the correct partition, It searches on all partitions.
>
> explain select * from scores where played_at = '2018-03-01'

RANGE partitioning pruning works by the planner having knowledge that
your WHERE clause cannot yield rows that are within a partition's
range. Take your scores_2017 partition as an example, the range there
is (false, false, '2017-01-01') to (false, false, '2018-01-01'). The
planner cannot match your WHERE clause to that range since it's
missing any predicate that matches a prefix of the range. This is
similar to how a btree index on (recent, deleted, played_at) couldn't
be used efficiently to give you just rows with played_at on any given
date. You'd need something like: WHERE NOT recent AND NOT deleted AND
played_at = '2018-03-01' for it to know only the scores_2018 partition
can match.

(There was a bug fixed recently that caused some partitions in a range
partitioned table to be pruned accidentally, but you're not
complaining about that.)

You might want to look into sub-partitioning the table, however, see
the note in [1] about that.

[1] https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-BEST-PRACTICES

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services