Обсуждение: DELETE / UPDATE from partition not optimized (11.0)

Поиск
Список
Период
Сортировка

DELETE / UPDATE from partition not optimized (11.0)

От
Dave E Martin
Дата:
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

Re: DELETE / UPDATE from partition not optimized (11.0)

От
Justin Pryzby
Дата:
On Thu, Oct 25, 2018 at 10:43:10AM -0600, Dave E Martin wrote:
> If SELECT is confident enough to limit itself to one partition, why isn't
> DELETE (or UPDATE)?

Because of this limitation:

https://www.postgresql.org/docs/current/static/ddl-partitioning.html#DDL-PARTITION-PRUNING
|Currently, pruning of partitions during the planning of an UPDATE or DELETE
|command is implemented using the constraint exclusion method (however, it is
|controlled by the enable_partition_pruning rather than constraint_exclusion) —
|see the following section for details and caveats that apply.

Justin

Re: DELETE / UPDATE from partition not optimized (11.0)

От
Justin Pryzby
Дата:
On Fri, Oct 26, 2018 at 10:45:40AM -0500, Justin Pryzby wrote:
> On Thu, Oct 25, 2018 at 10:43:10AM -0600, Dave E Martin wrote:
> > If SELECT is confident enough to limit itself to one partition, why isn't
> > DELETE (or UPDATE)?
> 
> Because of this limitation:
> 
> https://www.postgresql.org/docs/current/static/ddl-partitioning.html#DDL-PARTITION-PRUNING
> |Currently, pruning of partitions during the planning of an UPDATE or DELETE
> |command is implemented using the constraint exclusion method (however, it is
> |controlled by the enable_partition_pruning rather than constraint_exclusion) —
> |see the following section for details and caveats that apply.

I meant to add that one can use a redundant constraints in addition to the
partition bounds, both specifying the same condition.  That also allows
detaching and re-attaching the partition without a table scan (which is why we
do it).

Justin