Re: [HACKERS] path toward faster partition pruning

 

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