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
