Re: PG11 - Multiple Key Range Partition

Hi David,

First of all, thank you for the quick response, I highly appreciate it!

Currently, I'm trying to test out different query patterns to understand
the key constraints and make the planner recognize the RANGE.

The example you suggested:

explain select * from scores where NOT(recent = true) and NOT(deleted =
true) and played_at = '2018-03-02'
explain select * from scores where NOT(recent) and NOT(deleted) and
played_at = '2018-03-02'

Yield the same result:

[image: Screenshot 2019-07-09 at 09.42.46.png]

It does a search over all partitions, mainly because I think the planner
still cannot match the where clause to the range.

I can not find any queries that output the desired results. And for the
moment I like our strategy of having a partition for each year, plus the
extra two (recent and deleted). In terms of Insert / Update / Deleted /
Upsert / etc.. operations all works fine. I populated a DB with around 600
million rows and the partitions are populated correctly + operations except
SELECT are working perfectly.

Furthermore, I am a bit confused about the fact that with 2 key range
partitions (recent and played_at), all works perfectly as expected (event
SELECT).
I saw you recommended sub-partitioning, we are defining each partition as
in the example from the documentation.

I'm not sure what is your timeline, but can you provide an example with 3
key Range (2 booleans and 1 date) partition with a working select statement
on the date?

Thank you very much and have a great day!
Rares

On Tue, Jul 9, 2019 at 12:49 AM David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

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