pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: g.fouet@gmail.com, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>
Subject: Re: BUG #17590: [OPTIMIZER] DELETE never ends on a small table, found a workaround which makes it instant
Date: Fri, 19 Aug 2022 18:17:45 +0200
Message-ID: <5b0e5bab-08ac-0942-3bad-9b122b513f94@enterprisedb.com> (raw)
In-Reply-To: <17590-80fab927fee1ddf2@postgresql.org>
References: <17590-80fab927fee1ddf2@postgresql.org>



On 8/19/22 17:13, PG Bug reporting form wrote:
> 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.

It's probably better to include the query plan. Anyway, the union
essentially creates a new relation, making indexes (on the base
relations unusable).

> 
> 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.

Yeah. The temporary table means we can use the indexes again.

I'm not sure I'd call this a bug, it's simply how we deal with this sort
of queries. Maybe try splitting the one "NOT IN" condition into a
separate condition per table. I mean, something like

DELETE FROM address
  WHERE address_id NOT IN (SELECT address_id FROM company)
    AND address_id NOT IN (SELECT address_id FROM contact)
    AND address_id NOT IN (SELECT address_id FROM trip)

or something like that.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company





view thread (2+ messages)

Message-ID: <5b0e5bab-08ac-0942-3bad-9b122b513f94@enterprisedb.com>
Permalink:  ../5b0e5bab-08ac-0942-3bad-9b122b513f94@enterprisedb.com/
Also on:    postgresql.org/message-id/5b0e5bab-08ac-0942-3bad-9b122b513f94@enterprisedb.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: tomas.vondra@enterprisedb.com, noreply@postgresql.org
  Subject: Re: BUG #17590: [OPTIMIZER] DELETE never ends on a small table, found a workaround which makes it instant
  In-Reply-To: <5b0e5bab-08ac-0942-3bad-9b122b513f94@enterprisedb.com>
  References: <17590-80fab927fee1ddf2@postgresql.org> <5b0e5bab-08ac-0942-3bad-9b122b513f94@enterprisedb.com>

* 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