Re: [HACKERS] path toward faster partition pruning
От | Rajkumar Raghuwanshi |
---|---|
Тема | Re: [HACKERS] path toward faster partition pruning |
Дата | |
Msg-id | CAKcux6n2nPOis4kx42uxsKxp9-zH+f1nrRkT1M=0VXO9S9aE1Q@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: [HACKERS] path toward faster partition pruning (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>) |
Ответы |
Re: [HACKERS] path toward faster partition pruning
(Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>)
|
Список | pgsql-hackers |
On Mon, Oct 30, 2017 at 12:20 PM, Amit Langote <Langote_Amit_f8@lab.ntt.co.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
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
В списке pgsql-hackers по дате отправления: