Re: PG11 - Multiple Key Range Partition

Hi David,

I tested out your example and all works fine for me as well. But I live the
main issue lies in the following example:

create table rangep (a bool, b bool, c date) partition by range (a,b,c);

create table rangep1_recent partition of rangep for values from (*true*,false,
'1990-01-01') to (*true*,false,'2090-01-01');
create table rangep1_deleted partition of rangep for values from (false,
*true*, '1990-01-01') to (false,*true*,'2090-01-01');
create table rangep1_2019 partition of rangep for values from (false,false,
'2019-01-01') to (false,false,'2020-01-01');
create table rangep1_2018 partition of rangep for values from (false,false,
'2018-01-01') to (false,false,'2019-01-01');
create table rangep1_2017 partition of rangep for values from (false,false,
'2017-01-01') to (false,false,'2018-01-01');

explain select * from rangep where not a and not b and c = '2019-07-10';

[image: Screenshot 2019-07-10 at 09.32.14.png]

When doing:
explain select * from rangep where a and not b an and c = '2019-07-10';
or simply
explain select * from rangep where a and c = '2019-07-10';

It successfully searches only in the 'recent' partition.
I believe the planner gets confused when we introduce the following two
combinations: (true, false, '1990-01-01') - (true, false, '2090-01-01') and
(false, true, '1990-01-01') - (false, true, '2090-01-01').

My current Postgres version is PostgreSQL 11, 2.2.4. ("PostgreSQL 11.4 on
x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 8.1.0
(clang-802.0.42), 64-bit")

Thank you for the quick response, I’m eager to receive your feedback.

Have a great day!
Rares

On Wed, Jul 10, 2019 at 2:33 AM David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> On Tue, 9 Jul 2019 at 18:53, Rares Salcudean
> <rares(dot)salcudean(at)takeofflabs(dot)com> wrote:
> > 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:
>
> Works okay for me with:
>
> create table rangep (a bool, b bool, c date) partition by range (a,b,c);
>
> create table rangep1 partition of rangep for values from (false,
> false, '2019-01-01') to (false,false,'2020-01-01');
> create table rangep2 partition of rangep for values from (true, true,
> '2019-01-01') to (true,true,'2020-01-01');
>
> explain select * from rangep where not a and not b and c = '2019-07-10';
> QUERY PLAN
> --------------------------------------------------------------
> Seq Scan on rangep1 (cost=0.00..40.00 rows=3 width=6)
> Filter: ((NOT a) AND (NOT b) AND (c = '2019-07-10'::date))
> (2 rows)
>
> That's on master, but v10 and v11 still prune away rangep2.
>
> It might help if you share which version you're using and a cutdown
> version of the schema, just enough to show the issue.
>
> --
> David Rowley http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>