pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedUnbearably slow cascading deletes
6+ messages / 3 participants
[nested] [flat]
* Unbearably slow cascading deletes
@ 2004-07-20 19:19 andrew@pillette.com
0 siblings, 2 replies; 6+ messages in thread
From: andrew@pillette.com @ 2004-07-20 19:19 UTC (permalink / raw)
To: pgsql-performance
I have (among other things) a parent table with 200 records and a child table with 20MM or more. I set up referential integrity on the FK with ON DELETE CASCADE.
It appears that when a DELETE is done on the parent table, the child table deletion is done with a sequential scan. I say this because it took over four minutes to delete a parent record THAT HAD NO CHILDREN. The DB is recently analyzed and SELECTs in the child table are done by the appropriate index on the FK.
Let me guess, the cascade trigger's query plan is decided at schema load time, when the optimizer has no clue. Is there a way to fix this without writing my own triggers, using PL/PGSQL EXECUTE to delay the planner?
And by the way, if FK conditions like IN (1,3,4) could be handled in a single invocation of the trigger, so much the better.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Unbearably slow cascading deletes
@ 2004-07-20 19:45 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: andrew@pillette.com
1 sibling, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2004-07-20 19:45 UTC (permalink / raw)
To: andrew@pillette.com; +Cc: pgsql-performance
On Tue, 20 Jul 2004 andrew@pillette.com wrote:
> I have (among other things) a parent table with 200 records and a child
> table with 20MM or more. I set up referential integrity on the FK with
> ON DELETE CASCADE.
>
> It appears that when a DELETE is done on the parent table, the child
> table deletion is done with a sequential scan. I say this because it
> took over four minutes to delete a parent record THAT HAD NO CHILDREN.
> The DB is recently analyzed and SELECTs in the child table are done by
> the appropriate index on the FK.
>
> Let me guess, the cascade trigger's query plan is decided at schema load
> time, when the optimizer has no clue. Is there a way to fix this without
> writing my own triggers, using PL/PGSQL EXECUTE to delay the planner?
The query plan should be decided at the first cascaded delete for the key
in the session. However, IIRC, it's using $arguments for the key values,
so it's possible that that is giving it a different plan than it would get
if the value were known. What do you get if you prepare the query with an
argument for the key and use explain execute?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Unbearably slow cascading deletes
@ 2004-07-20 19:55 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2004-07-20 19:55 UTC (permalink / raw)
To: andrew@pillette.com; +Cc: pgsql-performance
On Tue, 20 Jul 2004, Stephan Szabo wrote:
>
> On Tue, 20 Jul 2004 andrew@pillette.com wrote:
>
> > I have (among other things) a parent table with 200 records and a child
> > table with 20MM or more. I set up referential integrity on the FK with
> > ON DELETE CASCADE.
> >
> > It appears that when a DELETE is done on the parent table, the child
> > table deletion is done with a sequential scan. I say this because it
> > took over four minutes to delete a parent record THAT HAD NO CHILDREN.
> > The DB is recently analyzed and SELECTs in the child table are done by
> > the appropriate index on the FK.
> >
> > Let me guess, the cascade trigger's query plan is decided at schema load
> > time, when the optimizer has no clue. Is there a way to fix this without
> > writing my own triggers, using PL/PGSQL EXECUTE to delay the planner?
>
> The query plan should be decided at the first cascaded delete for the key
> in the session. However, IIRC, it's using $arguments for the key values,
> so it's possible that that is giving it a different plan than it would get
> if the value were known. What do you get if you prepare the query with an
> argument for the key and use explain execute?
To be clear, I mean prepare/explain execute an example select/delete from
the fk.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Unbearably slow cascading deletes
@ 2004-07-20 19:59 andrew@pillette.com
0 siblings, 0 replies; 6+ messages in thread
From: andrew@pillette.com @ 2004-07-20 19:59 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-performance
PREPARE c(int4) AS DELETE FROM childtable WHERE fk=$1;
EXPLAIN EXECUTE c(-1);
gives an index scan.
PREPARE c2(int4) AS DELETE FROM parenttable WHERE key=$1;
EXPLAIN EXECUTE c2(1);
gives a seq scan on the parent table (itself a little curious) and no explanation of what the triggers are doing.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Unbearably slow cascading deletes
@ 2004-07-20 20:06 andrew@pillette.com
0 siblings, 0 replies; 6+ messages in thread
From: andrew@pillette.com @ 2004-07-20 20:06 UTC (permalink / raw)
To: andrew@pillette.com; +Cc: pgsql-performance
I FOUND IT!
A second trigger that doesn't belong......
OK, we're set now, and thanks for showing me some ways to check what the planner is up to. Is there a way of seeing what the triggers will do?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Unbearably slow cascading deletes
@ 2004-07-21 01:37 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: andrew@pillette.com
1 sibling, 0 replies; 6+ messages in thread
From: Christopher Kings-Lynne @ 2004-07-21 01:37 UTC (permalink / raw)
To: andrew@pillette.com; +Cc: pgsql-performance
> I have (among other things) a parent table with 200 records and a child table with 20MM or more. I set up referential integrity on the FK with ON DELETE CASCADE.
>
> It appears that when a DELETE is done on the parent table, the child table deletion is done with a sequential scan. I say this because it took over four minutes to delete a parent record THAT HAD NO CHILDREN. The DB is recently analyzed and SELECTs in the child table are done by the appropriate index on the FK.
Do you have an index on the foreign key field?
Chris
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-07-21 01:37 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-20 19:19 Unbearably slow cascading deletes andrew@pillette.com
2004-07-20 19:45 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-07-20 19:55 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-07-21 01:37 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-07-20 19:59 Re: Unbearably slow cascading deletes andrew@pillette.com
2004-07-20 20:06 Re: Unbearably slow cascading deletes andrew@pillette.com
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