Re: path toward faster partition pruning

On Mon, Oct 30, 2017 at 12:20 PM, Amit Langote <
Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:

> In the previous versions, RT index of the table needed to be passed to
> partition.c, which I realized is no longer needed, so I removed that
> requirement from the interface. As a result, patches 0002 and 0003 have
> changed in this version.
>

Thanks for the fix.

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)

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation