Postgres Pro
Downloads
Home   >   mailing lists

Re: Deleting more efficiently from large partitions - Mailing list pgsql-admin

From Wells Oliver
Subject Re: Deleting more efficiently from large partitions
Date
Msg-id CAOC+FBVn_n5_QQKfFygrwv0SD4rN7neWRRa2xJNMaUVRfXTCWQ@mail.gmail.com
Whole thread Raw
In response to Re: Deleting more efficiently from large partitions  (Greg Spiegelberg <gspiegelberg@gmail.com>)
List pgsql-admin
Tree view
So the PK is date + uuid + two other columns. I also have an index (btree) on uuid. When I try to delete via DELETE FROM mpd WHERE uuid IN (SELECT uuid FROM temptable), it takes a long long time as it scans every partition. The explain output is huge. Here is some of it.


This will take hours if I put a couple of thousand uuid records in the temp table. It's untenable. The delete here does NOT seem to be using the index on uuid

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on mpd  (cost=0.42..264953274.12 rows=5828371861 width=12) (actual time=286791.526..286791.526 rows=0 loops=1)
   Delete on mpd_2015_01
  ...
   Delete on mpd_2021_12
   Buffers: shared hit=834134 read=4148792 dirtied=16844, local read=463956 written=1023
   ->  Nested Loop Semi Join  (cost=0.42..526.20 rows=550 width=12) (actual time=0.010..0.010 rows=0 loops=1)
         ->  Seq Scan on mpd_2015_01  (cost=0.00..21.00 rows=1100 width=22) (actual time=0.008..0.008 rows=0 loops=1)
         ->  Index Scan using uuid_idx on temptable  (cost=0.42..120.11 rows=4088 width=22) (never executed)
               Index Cond: (uuid = mpd_2015_01.uuid)
   ->  Nested Loop Semi Join  (cost=0.42..526.20 rows=550 width=12) (actual time=0.005..0.005 rows=0 loops=1)
         ->  Seq Scan on mpd_2015_02  (cost=0.00..21.00 rows=1100 width=22) (actual time=0.005..0.005 rows=0 loops=1)
         ->  Index Scan using uuid_idx on temptable  (cost=0.42..120.11 rows=4088 width=22) (never executed)
               Index Cond: (uuid = mpd_2015_02.uuid)
   ->  Nested Loop Semi Join  (cost=0.42..526.20 rows=550 width=12) (actual time=0.002..0.003 rows=0 loops=1)
         ->  Seq Scan on mpd_2015_03  (cost=0.00..21.00 rows=1100 width=22) (actual time=0.002..0.002 rows=0 loops=1)
         ->  Index Scan using uuid_idx on temptable  (cost=0.42..120.11 rows=4088 width=22) (never executed)
               Index Cond: (uuid = mpd_2015_03.uuid)
   ->  Nested Loop  (cost=20606.19..7121506.57 rows=149367744 width=12) (actual time=7934.416..7934.416 rows=0 loops=1)
         Buffers: shared hit=478 read=558, local read=10086 written=1023
         ->  HashAggregate  (cost=20306.34..20308.34 rows=200 width=22) (actual time=7555.857..7556.264 rows=259 loops=1)
               Group Key: temptable.uuid
               Buffers: local read=10086 written=1023
               ->  Seq Scan on temptable  (cost=0.00..18262.27 rows=817627 width=22) (actual time=7257.016..7399.478 rows=817627 loops=1)
                     Buffers: local read=10086 written=1023
         ->  Bitmap Heap Scan on mpd_2015_04  (cost=299.85..35376.78 rows=12921 width=22) (actual time=1.457..1.457 rows=0 loops=259)
               Recheck Cond: (uuid = temptable.uuid)
               Buffers: shared hit=478 read=558
               ->  Bitmap Index Scan on mpd_2015_04_uuid_idx  (cost=0.00..296.62 rows=12921 width=0) (actual time=1.455..1.455 rows=0 loops=259)
                     Index Cond: (uuid = temptable.uuid)
                     Buffers: shared hit=478 read=558
  ...
   ->  Hash Join  (cost=20310.84..646068.95 rows=10984718 width=12) (actual time=13374.218..13374.219 rows=0 loops=1)
         Hash Cond: (mpd_2020_02.uuid = temptable.uuid)
         Buffers: shared read=226189, local read=10086
         ->  Seq Scan on mpd_2020_02  (cost=0.00..445883.36 rows=21969436 width=22) (actual time=2.129..10268.765 rows=21969107 loops=1)
               Buffers: shared read=226189
         ->  Hash  (cost=20308.34..20308.34 rows=200 width=22) (actual time=292.617..292.617 rows=259 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 22kB
               Buffers: local read=10086
               ->  HashAggregate  (cost=20306.34..20308.34 rows=200 width=22) (actual time=292.501..292.552 rows=259 loops=1)
                     Group Key: temptable.uuid
                     Buffers: local read=10086
                     ->  Seq Scan on temptable  (cost=0.00..18262.27 rows=817627 width=22) (actual time=0.023..136.102 rows=817627 loops=1)
                           Buffers: local read=10086
   ->  Hash Join  (cost=20310.84..390240.09 rows=6494055 width=12) (actual time=8221.586..8221.586 rows=0 loops=1)
         Hash Cond: (mpd_2020_03.uuid = temptable.uuid)
         Buffers: shared read=133708, local read=10086
         ->  Seq Scan on mpd_2020_03  (cost=0.00..263589.10 rows=12988110 width=22) (actual time=2.118..6207.373 rows=12987440 loops=1)
               Buffers: shared read=133708
         ->  Hash  (cost=20308.34..20308.34 rows=200 width=22) (actual time=289.169..289.169 rows=259 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 22kB
               Buffers: local read=10086
               ->  HashAggregate  (cost=20306.34..20308.34 rows=200 width=22) (actual time=289.054..289.103 rows=259 loops=1)
                     Group Key: temptable.uuid
                     Buffers: local read=10086
                     ->  Seq Scan on temptable  (cost=0.00..18262.27 rows=817627 width=22) (actual time=0.018..135.729 rows=817627 loops=1)
                           Buffers: local read=10086
   ->  Nested Loop Semi Join  (cost=0.42..526.20 rows=550 width=12) (actual time=0.007..0.007 rows=0 loops=1)
         ->  Seq Scan on mpd_2020_04  (cost=0.00..21.00 rows=1100 width=22) (actual time=0.006..0.006 rows=0 loops=1)
         ->  Index Scan using uuid_idx on temptable  (cost=0.42..120.11 rows=4088 width=22) (never executed)
               Index Cond: (uuid = mpd_2020_04.uuid)
  ...
   ->  Nested Loop Semi Join  (cost=0.42..526.20 rows=550 width=12) (actual time=0.003..0.003 rows=0 loops=1)
         ->  Seq Scan on mpd_2021_12  (cost=0.00..21.00 rows=1100 width=22) (actual time=0.002..0.002 rows=0 loops=1)
         ->  Index Scan using uuid_idx on temptable  (cost=0.42..120.11 rows=4088 width=22) (never executed)
               Index Cond: (uuid = mpd_2021_12.uuid)
 Planning Time: 473.612 ms
 JIT:
   Functions: 932
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 99.333 ms, Inlining 9.228 ms, Optimization 4079.459 ms, Emission 3159.415 ms, Total 7347.435 ms
 Execution Time: 286901.250 ms
(843 rows)

On Tue, Jun 16, 2020 at 6:57 AM Greg Spiegelberg <gspiegelberg@gmail.com> wrote:
On Mon, Jun 15, 2020 at 7:39 PM Wells Oliver <wells.oliver@gmail.com> wrote:
Hi all. I have a partitioned table (by month from a date column), where each partition contains something like 400m rows.

Each partition is defined by a PK with a uuid and date field (the parent table is partitioned by range on the date), and two other columns.

In doing a delete for a specific date, e.g. DELETE FROM t WHERE date = '2019-09-01' AND uuid IN (SELECT uuid FROM temptable), it runs very efficiently.

I am trying to write a processing script that deletes for potentially multiple dates & uuid values, and it just takes hours, trying:

DELETE FROM t WHERE date = (SELECT DISTINCT date from temp) AND uuid IN (select uuid from tempuuds) -- no go, hours.

Tried USING, e.g. DELETE FROM t USING temp WHERE t.date = temp.date AND t.uuid = temp.uuid -- no go, hours.

I just can't delete from this table without an explicit date and a set of uuids using a WHERE IN approach, but I need to.I was thinking of making a plpgsql function or something that loops through dates and makes a more explicit DELETE statement, but I'm thinking there must be some better way using indexing or something.

Appreciate any tips.

Have you considered partitioning by day instead of month?  Could eliminate an index you may have on the date column.
How many days are in the many-days DELETE?  Could you simply wrap it in a transaction and do one DELETE per day?

You could potentially get better performance removing the JOIN/sub-SELECT using
DELETE FROM mytable WHERE date_col = ANY( ARRAY['2020-01-01', '2020-01-13']::date[] );

HTH
-Greg


--

pgsql-admin by date:

Previous
From: Achilleas Mantzios
Date:
Subject: please help! losing my subscriber node
Next
From: Bruce Momjian
Date:
Subject: Re: Upgrade streaming replication and log-shipping standby servers