From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: andrew@pillette.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Unbearably slow cascading deletes
Date: Tue, 20 Jul 2004 12:45:10 -0700 (PDT)
Message-ID: <20040720123547.J31189@megazone.bigpanda.com> (raw)
In-Reply-To: <200407201919.i6KJJBw08728@pillette.com>
References: <200407201919.i6KJJBw08728@pillette.com>
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?
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-performance@postgresql.org
Cc: sszabo@megazone.bigpanda.com, andrew@pillette.com
Subject: Re: Unbearably slow cascading deletes
In-Reply-To: <20040720123547.J31189@megazone.bigpanda.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