DELETE / UPDATE from partition not optimized (11.0)

[Dave E Martin <postgresql2-to.dave@xxxxxxx>]

If SELECT is confident enough to limit itself to one partition, why isn't DELETE (or UPDATE)?

Also, I note in the query plan shown below it thinks the rows in the irrelevant partitions is something other than 0, which is impossible. (presumably, SELECT correctly determined this, and eliminated the irrelevant partitions from the plan, but DELETE doesn't seem to be doing this).

And, if it isn't impossible for some reason, then why isn't SELECT checking all partitions?

It also appears UPDATE has the same problem.

This is for HASH partitions, I don't know if this issue is present with the other types.

PostgreSQL 11.0 (Ubuntu 11.0-1.pgdg16.04+2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit

explain select * from history where itemid=537021;
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Append  (cost=4.79..143.63 rows=48 width=21)
   ->  Bitmap Heap Scan on history_0028  (cost=4.79..143.39 rows=48 width=21)
         Recheck Cond: (itemid = 537021)
         ->  Bitmap Index Scan on history_0028_itemid_clock_idx  (cost=0.00..4.78 rows=48 width=0)
               Index Cond: (itemid = 537021)
(5 rows)

explain delete from history where itemid=537021;
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Delete on history  (cost=4.77..13987.62 rows=4629 width=6)
   Delete on history_0000
   Delete on history_0001
   Delete on history_0002
   Delete on history_0003
   Delete on history_0004
   Delete on history_0005
   Delete on history_0006
...

  ->  Bitmap Heap Scan on public.history_0000  (cost=4.79..144.20 rows=48 width=6)
         Output: history_0000.ctid
         Recheck Cond: (history_0000.itemid = 537021)
         ->  Bitmap Index Scan on history_0000_itemid_clock_idx  (cost=0.00..4.78 rows=48 width=0)
               Index Cond: (history_0000.itemid = 537021)
   ->  Bitmap Heap Scan on public.history_0001  (cost=4.79..148.77 rows=48 width=6)
         Output: history_0001.ctid
         Recheck Cond: (history_0001.itemid = 537021)
         ->  Bitmap Index Scan on history_0001_itemid_clock_idx  (cost=0.00..4.78 rows=48 width=0)
               Index Cond: (history_0001.itemid = 537021)
...

 \d+ history
                                     Table "public.history"
 Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
 itemid | bigint        |           | not null |         | plain   |              |
 clock  | integer       |           | not null | 0       | plain   |              |
 value  | numeric(16,4) |           | not null | 0.0     | main    |              |
 ns     | integer       |           | not null | 0       | plain   |              |
Partition key: HASH (itemid)
Indexes:
    "history_itemid_clock_idx" btree (itemid, clock) WITH (fillfactor='20')
Partitions: history_0000 FOR VALUES WITH (modulus 100, remainder 0),
            history_0001 FOR VALUES WITH (modulus 100, remainder 1),
            history_0002 FOR VALUES WITH (modulus 100, remainder 2),
            history_0003 FOR VALUES WITH (modulus 100, remainder 3),
            history_0004 FOR VALUES WITH (modulus 100, remainder 4),
            history_0005 FOR VALUES WITH (modulus 100, remainder 5),
...

select count(*),count(distinct itemid),tableoid from history group by tableoid order by tableoid;
 count  | count | tableoid
--------+-------+----------
 64,762 |   356 |   20,531
 80,649 |   351 |   20,537
 61,424 |   340 |   20,543
 57,290 |   365 |   20,549
 69,146 |   344 |   20,555
 68,357 |   372 |   20,561
 69,319 |   329 |   20,567
 60,846 |   332 |   20,573
 62,021 |   346 |   20,579
 66,328 |   362 |   20,585
 69,385 |   361 |   20,591
 63,304 |   332 |   20,597

...

 select count(*),count(distinct itemid) from history;
   count   | count
-----------+--------
 6,607,298 | 34,885
(1 row)
...

explain verbose update history set clock =4 where itemid=537021;
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Update on public.history  (cost=4.80..15043.41 rows=4992 width=27)
   Update on public.history_0000
   Update on public.history_0001
   Update on public.history_0002
   Update on public.history_0003
   Update on public.history_0004