Partition Pruning (Hash Partitions) Support for DELETEs in PostgreSQL11 and 12 - Mailing list pgsql-performance

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