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
