From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: g.fouet@gmail.com
Subject: BUG #17590: [OPTIMIZER] DELETE never ends on a small table, found a workaround which makes it instant
Date: Fri, 19 Aug 2022 15:13:59 +0000
Message-ID: <17590-80fab927fee1ddf2@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17590
Logged by: Guillaume FOUET
Email address: g.fouet@gmail.com
PostgreSQL version: 14.5
Operating system: Windows 10 - 64x
Description:
Tested with Postgres 13.5, 13.8 and 14.5 (I updated to see if that was a
bug).
I have four tables: ADDRESS, COMPANY, CONTACT, TRIP
ADDRESS has ~130000 rows
COMPANY has a FK toward ADDRESS and ~16000 rows
CONTACT has a FK toward ADDRESS and ~12000 rows
TRIP has a FK toward ADDRESS and ~137500 rows
We wanted to purge the address table from old, unused addresses:
DELETE FROM address WHERE address_id NOT IN (
SELECT DISTINCT address_id FROM company
UNION
SELECT DISTINCT address_id FROM contact
UNION
SELECT DISTINCT address_id FROM trip
);
This query above never ends (I waited 15 minutes and it was still going, HDD
doing nothing, one core CPU used).
The EXPLAIN says it materializes the address_ids aggregates then scans
ADDRESS for deletion.
After many tries, I made this query instead:
CREATE TEMPORARY TABLE used_address_id AS (
SELECT DISTINCT address_id FROM company
UNION
SELECT DISTINCT address_id FROM contact
UNION
SELECT DISTINCT address_id FROM trip
);
CREATE UNIQUE INDEX ON used_address_id (address_id);
DELETE FROM used_address_id WHERE address_id IS NULL;
DELETE FROM address WHERE address_id NOT IN (SELECT address_id FROM
used_address_id);
This was resolved in 500ms (basically instantly).
I have the feeling there's an O(n²) somewhere in the first query.
Thanks.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, g.fouet@gmail.com
Subject: Re: BUG #17590: [OPTIMIZER] DELETE never ends on a small table, found a workaround which makes it instant
In-Reply-To: <17590-80fab927fee1ddf2@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox