Thread: Partition Pruning (Hash Partitions) Support for DELETEs in PostgreSQL11 and 12
Hello All,
While doing some tests with hash partitioning behavior in PG11 and 12, I have found that PG11 is not performing partition pruning with DELETEs (explain analyze returned >2000 lines). I then ran the same test in PG12 and recreated the objects using the same DDL, and it worked
Here are the tests:
1) PG11 Hash Partitioning, no partition pruning:
postgres=> \timing
Timing is on.
postgres=> select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)
Time: 33.325 ms
postgres=> create table hp ( foo text ) partition by hash (foo);
CREATE TABLE
Time: 40.810 ms
postgres=> create table hp_0 partition of hp for values with (modulus 3, remainder 0);
CREATE TABLE
Time: 43.990 ms
postgres=> create table hp_1 partition of hp for values with (modulus 3, remainder 1);
CREATE TABLE
Time: 43.314 ms
postgres=> create table hp_2 partition of hp for values with (modulus 3, remainder 2);
CREATE TABLE
Time: 43.447 ms
postgres=> insert into hp values ('shayon');
INSERT 0 1
Time: 42.975 ms
postgres=> select * from hp;
foo
--------
shayon
(1 row)
Time: 40.210 ms
postgres=> select * from hp_0;
foo
--------
shayon
(1 row)
Time: 38.898 ms
postgres=> insert into hp values ('shayon1'), ('shayon2'), ('shayon3');
INSERT 0 3
Time: 40.359 ms
postgres=> select * from hp_0;
foo
--------
shayon
(1 row)
Time: 39.105 ms
postgres=> select * from hp_1;
foo
---------
shayon2
(1 row)
Time: 37.292 ms
postgres=> select * from hp_2;
foo
---------
shayon1
shayon3
(2 rows)
Time: 38.604 ms
postgres=> explain select * from hp where foo = 'shayon2';
QUERY PLAN
------------------------------------------------------------
Append (cost=0.00..27.04 rows=7 width=32)
-> Seq Scan on hp_1 (cost=0.00..27.00 rows=7 width=32)
Filter: (foo = 'shayon2'::text)
(3 rows)
Time: 39.581 ms
postgres=> explain delete from hp where foo = 'shayon2';
QUERY PLAN
-----------------------------------------------------------
Delete on hp (cost=0.00..81.00 rows=21 width=6)
Delete on hp_0
Delete on hp_1
Delete on hp_2
-> Seq Scan on hp_0 (cost=0.00..27.00 rows=7 width=6)
Filter: (foo = 'shayon2'::text)
-> Seq Scan on hp_1 (cost=0.00..27.00 rows=7 width=6)
Filter: (foo = 'shayon2'::text)
-> Seq Scan on hp_2 (cost=0.00..27.00 rows=7 width=6)
Filter: (foo = 'shayon2'::text)
(10 rows)
Time: 38.749 ms
postgres=> \timing
Timing is on.
postgres=> select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)
Time: 33.325 ms
postgres=> create table hp ( foo text ) partition by hash (foo);
CREATE TABLE
Time: 40.810 ms
postgres=> create table hp_0 partition of hp for values with (modulus 3, remainder 0);
CREATE TABLE
Time: 43.990 ms
postgres=> create table hp_1 partition of hp for values with (modulus 3, remainder 1);
CREATE TABLE
Time: 43.314 ms
postgres=> create table hp_2 partition of hp for values with (modulus 3, remainder 2);
CREATE TABLE
Time: 43.447 ms
postgres=> insert into hp values ('shayon');
INSERT 0 1
Time: 42.975 ms
postgres=> select * from hp;
foo
--------
shayon
(1 row)
Time: 40.210 ms
postgres=> select * from hp_0;
foo
--------
shayon
(1 row)
Time: 38.898 ms
postgres=> insert into hp values ('shayon1'), ('shayon2'), ('shayon3');
INSERT 0 3
Time: 40.359 ms
postgres=> select * from hp_0;
foo
--------
shayon
(1 row)
Time: 39.105 ms
postgres=> select * from hp_1;
foo
---------
shayon2
(1 row)
Time: 37.292 ms
postgres=> select * from hp_2;
foo
---------
shayon1
shayon3
(2 rows)
Time: 38.604 ms
postgres=> explain select * from hp where foo = 'shayon2';
QUERY PLAN
------------------------------------------------------------
Append (cost=0.00..27.04 rows=7 width=32)
-> Seq Scan on hp_1 (cost=0.00..27.00 rows=7 width=32)
Filter: (foo = 'shayon2'::text)
(3 rows)
Time: 39.581 ms
postgres=> explain delete from hp where foo = 'shayon2';
QUERY PLAN
-----------------------------------------------------------
Delete on hp (cost=0.00..81.00 rows=21 width=6)
Delete on hp_0
Delete on hp_1
Delete on hp_2
-> Seq Scan on hp_0 (cost=0.00..27.00 rows=7 width=6)
Filter: (foo = 'shayon2'::text)
-> Seq Scan on hp_1 (cost=0.00..27.00 rows=7 width=6)
Filter: (foo = 'shayon2'::text)
-> Seq Scan on hp_2 (cost=0.00..27.00 rows=7 width=6)
Filter: (foo = 'shayon2'::text)
(10 rows)
Time: 38.749 ms
2) PG12 hash prune, pruning works:
dev=> \timing
Timing is on.
dev=> select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)
Time: 29.786 ms
dev=> CREATE TABLE hp ( foo text ) PARTITION BY HASH (foo);
CREATE TABLE
Time: 30.680 ms
dev=> CREATE TABLE hp_0 PARTITION OF hp FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE
Time: 122.791 ms
dev=> CREATE TABLE hp_1 PARTITION OF hp FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE
Time: 32.053 ms
dev=> CREATE TABLE hp_2 PARTITION OF hp FOR VALUES WITH (MODULUS 3, REMAINDER 2);
CREATE TABLE
Time: 31.839 ms
dev=> insert into hp values ('shayon1'), ('shayon2'), ('shayon3'), ('shayon');
INSERT 0 4
Time: 27.887 ms
dev=> select * from hp_1;
foo
---------
shayon2
(1 row)
Time: 27.697 ms
dev=> select * from hp_2;
foo
---------
shayon1
shayon3
(2 rows)
Time: 27.845 ms
dev=> select * from hp_0;
foo
--------
shayon
(1 row)
Time: 27.679 ms
dev=> explain delete from hp where foo = 'shayon2';
QUERY PLAN
-----------------------------------------------------------
Delete on hp (cost=0.00..27.00 rows=7 width=6)
Delete on hp_1
-> Seq Scan on hp_1 (cost=0.00..27.00 rows=7 width=6)
Filter: (foo = 'shayon2'::text)
(4 rows)
Time: 30.490 ms
dev=> \timing
Timing is on.
dev=> select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)
Time: 29.786 ms
dev=> CREATE TABLE hp ( foo text ) PARTITION BY HASH (foo);
CREATE TABLE
Time: 30.680 ms
dev=> CREATE TABLE hp_0 PARTITION OF hp FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE
Time: 122.791 ms
dev=> CREATE TABLE hp_1 PARTITION OF hp FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE
Time: 32.053 ms
dev=> CREATE TABLE hp_2 PARTITION OF hp FOR VALUES WITH (MODULUS 3, REMAINDER 2);
CREATE TABLE
Time: 31.839 ms
dev=> insert into hp values ('shayon1'), ('shayon2'), ('shayon3'), ('shayon');
INSERT 0 4
Time: 27.887 ms
dev=> select * from hp_1;
foo
---------
shayon2
(1 row)
Time: 27.697 ms
dev=> select * from hp_2;
foo
---------
shayon1
shayon3
(2 rows)
Time: 27.845 ms
dev=> select * from hp_0;
foo
--------
shayon
(1 row)
Time: 27.679 ms
dev=> explain delete from hp where foo = 'shayon2';
QUERY PLAN
-----------------------------------------------------------
Delete on hp (cost=0.00..27.00 rows=7 width=6)
Delete on hp_1
-> Seq Scan on hp_1 (cost=0.00..27.00 rows=7 width=6)
Filter: (foo = 'shayon2'::text)
(4 rows)
Time: 30.490 ms
Is this a bug, somewhat related to MergeAppend? https://github.com/postgres/postgres/commit/5220bb7533f9891b1e071da6461d5c387e8f7b09
If it is, anyone know if we have a workaround for DELETEs to use hash partitions in PG11?
Thanks,
Shayon
Re: Partition Pruning (Hash Partitions) Support for DELETEs inPostgreSQL 11 and 12
From
Justin Pryzby
Date:
On Sun, Mar 22, 2020 at 11:45:53PM -0400, Ronnie S wrote: > Hello All, > > While doing some tests with hash partitioning behavior in PG11 and 12, I > have found that PG11 is not performing partition pruning with DELETEs > (explain analyze returned >2000 lines). I then ran the same test in PG12 > and recreated the objects using the same DDL, and it worked > Is this a bug, somewhat related to MergeAppend? > https://github.com/postgres/postgres/commit/5220bb7533f9891b1e071da6461d5c387e8f7b09 > If it is, anyone know if we have a workaround for DELETEs to use hash > partitions in PG11? I think due to this commit to pg12: https://commitfest.postgresql.org/22/1778/ https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=428b260f87e8861ba8e58807b69d433db491c4f4 ... https://www.postgresql.org/message-id/5c83dbca-12b5-1acf-0e85-58299e464a26%40lab.ntt.co.jp https://www.postgresql.org/message-id/4f049572-9440-3c99-afa1-f7ca7f38fe80%40lab.ntt.co.jp -- Justin
Re: Partition Pruning (Hash Partitions) Support for DELETEs inPostgreSQL 11 and 12
From
Ronnie S
Date:
Thanks!
On Mon, Mar 23, 2020 at 12:10 AM Justin Pryzby <pryzby@telsasoft.com> wrote:
On Sun, Mar 22, 2020 at 11:45:53PM -0400, Ronnie S wrote:
> Hello All,
>
> While doing some tests with hash partitioning behavior in PG11 and 12, I
> have found that PG11 is not performing partition pruning with DELETEs
> (explain analyze returned >2000 lines). I then ran the same test in PG12
> and recreated the objects using the same DDL, and it worked
> Is this a bug, somewhat related to MergeAppend?
> https://github.com/postgres/postgres/commit/5220bb7533f9891b1e071da6461d5c387e8f7b09
> If it is, anyone know if we have a workaround for DELETEs to use hash
> partitions in PG11?
I think due to this commit to pg12:
https://commitfest.postgresql.org/22/1778/
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=428b260f87e8861ba8e58807b69d433db491c4f4
...
https://www.postgresql.org/message-id/5c83dbca-12b5-1acf-0e85-58299e464a26%40lab.ntt.co.jp
https://www.postgresql.org/message-id/4f049572-9440-3c99-afa1-f7ca7f38fe80%40lab.ntt.co.jp
--
Justin