Re: path toward faster partition pruning

Thanks for the test case.

On 2017/10/30 17:09, Rajkumar Raghuwanshi wrote:
> I am getting wrong output when default is sub-partitioned further, below is
> a test case.
>
> CREATE TABLE lpd(a int, b varchar, c float) PARTITION BY LIST (a);
> CREATE TABLE lpd_p1 PARTITION OF lpd FOR VALUES IN (1,2,3);
> CREATE TABLE lpd_p2 PARTITION OF lpd FOR VALUES IN (4,5);
> CREATE TABLE lpd_d PARTITION OF lpd DEFAULT PARTITION BY LIST(a);
> CREATE TABLE lpd_d1 PARTITION OF lpd_d FOR VALUES IN (7,8,9);
> CREATE TABLE lpd_d2 PARTITION OF lpd_d FOR VALUES IN (10,11,12);
> CREATE TABLE lpd_d3 PARTITION OF lpd_d FOR VALUES IN (6,null);
> INSERT INTO lpd SELECT i,i,i FROM generate_Series (1,12)i;
> INSERT INTO lpd VALUES (null,null,null);
>
> --on HEAD
> postgres=# EXPLAIN (COSTS OFF) SELECT tableoid::regclass, * FROM lpd WHERE
> a IS NOT NULL ORDER BY 1;
> QUERY PLAN
> ---------------------------------------------
> Sort
> Sort Key: ((lpd_p1.tableoid)::regclass)
> -> Result
> -> Append
> -> Seq Scan on lpd_p1
> Filter: (a IS NOT NULL)
> -> Seq Scan on lpd_p2
> Filter: (a IS NOT NULL)
> -> Seq Scan on lpd_d3
> Filter: (a IS NOT NULL)
> -> Seq Scan on lpd_d1
> Filter: (a IS NOT NULL)
> -> Seq Scan on lpd_d2
> Filter: (a IS NOT NULL)
> (14 rows)
>
> postgres=#
> postgres=# SELECT tableoid::regclass, * FROM lpd WHERE a IS NOT NULL ORDER
> BY 1;
> tableoid | a | b | c
> ----------+----+----+----
> lpd_p1 | 1 | 1 | 1
> lpd_p1 | 2 | 2 | 2
> lpd_p1 | 3 | 3 | 3
> lpd_p2 | 4 | 4 | 4
> lpd_p2 | 5 | 5 | 5
> lpd_d1 | 7 | 7 | 7
> lpd_d1 | 8 | 8 | 8
> lpd_d1 | 9 | 9 | 9
> lpd_d2 | 12 | 12 | 12
> lpd_d2 | 10 | 10 | 10
> lpd_d2 | 11 | 11 | 11
> lpd_d3 | 6 | 6 | 6
> (12 rows)
>
>
> --on HEAD + v8 patches
>
> postgres=# EXPLAIN (COSTS OFF) SELECT tableoid::regclass, * FROM lpd WHERE
> a IS NOT NULL ORDER BY 1;
> QUERY PLAN
> ---------------------------------------------
> Sort
> Sort Key: ((lpd_p1.tableoid)::regclass)
> -> Result
> -> Append
> -> Seq Scan on lpd_p1
> Filter: (a IS NOT NULL)
> -> Seq Scan on lpd_p2
> Filter: (a IS NOT NULL)
> (8 rows)
>
> postgres=# SELECT tableoid::regclass, * FROM lpd WHERE a IS NOT NULL ORDER
> BY 1;
> tableoid | a | b | c
> ----------+---+---+---
> lpd_p1 | 1 | 1 | 1
> lpd_p1 | 2 | 2 | 2
> lpd_p1 | 3 | 3 | 3
> lpd_p2 | 4 | 4 | 4
> lpd_p2 | 5 | 5 | 5
> (5 rows)

I found bugs in 0003 and 0005 that caused this. Will post the patches
containing the fix in reply to the Dilip's email which contains some code
review comments [1].

Also, I noticed that the new pruning code was having a hard time do deal
with the fact that the default "range" partition doesn't explicitly say in
its partition constraint that it might contain null values. More
precisely perhaps, the default range partition's constraint appears to
imply that it can only contain non-null values, which confuses the new
pruning code.

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/CAFiTN-thYsobXxPS6bwOA_9erpax_S=iztSn3RtUxKKMKG4V4A@mail.gmail.com