pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPartition Pruning (Hash Partitions) Support for DELETEs in PostgreSQL 11 and 12
3+ messages / 2 participants
[nested] [flat]
* Partition Pruning (Hash Partitions) Support for DELETEs in PostgreSQL 11 and 12
@ 2020-03-23 03:45 Ronnie S <ronniesan967@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Ronnie S @ 2020-03-23 03:45 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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
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
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Partition Pruning (Hash Partitions) Support for DELETEs in PostgreSQL 11 and 12
@ 2020-03-23 04:10 Justin Pryzby <pryzby@telsasoft.com>
parent: Ronnie S <ronniesan967@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Justin Pryzby @ 2020-03-23 04:10 UTC (permalink / raw)
To: Ronnie S <ronniesan967@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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=428b260f87e8861ba8e58807b69d433db491c...
...
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Partition Pruning (Hash Partitions) Support for DELETEs in PostgreSQL 11 and 12
@ 2020-03-23 14:18 Ronnie S <ronniesan967@gmail.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 3+ messages in thread
From: Ronnie S @ 2020-03-23 14:18 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org
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=428b260f87e8861ba8e58807b69d433db491c...
> ...
>
> 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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-03-23 14:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-23 03:45 Partition Pruning (Hash Partitions) Support for DELETEs in PostgreSQL 11 and 12 Ronnie S <ronniesan967@gmail.com>
2020-03-23 04:10 ` Justin Pryzby <pryzby@telsasoft.com>
2020-03-23 14:18 ` Ronnie S <ronniesan967@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox