Deleting more efficiently from large partitions
От | Wells Oliver |
---|---|
Тема | Deleting more efficiently from large partitions |
Дата | |
Msg-id | CAOC+FBWgX0D=WJBT43wTHUp=t=7xvBZB_ryVgK-QpURNrTJQ=w@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Deleting more efficiently from large partitions
Re: Deleting more efficiently from large partitions Re: Deleting more efficiently from large partitions |
Список | pgsql-admin |
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.
В списке pgsql-admin по дате отправления: