pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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:55:14 -0700 (PDT)
Message-ID: <20040720125453.W31688@megazone.bigpanda.com> (raw)
In-Reply-To: <20040720123547.J31189@megazone.bigpanda.com>
References: <200407201919.i6KJJBw08728@pillette.com>
	<20040720123547.J31189@megazone.bigpanda.com>


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.



view thread (6+ messages)  latest in thread

Message-ID: <20040720125453.W31688@megazone.bigpanda.com>
Permalink:  ../20040720125453.W31688@megazone.bigpanda.com/
Also on:    postgresql.org/message-id/20040720125453.W31688@megazone.bigpanda.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 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: <20040720125453.W31688@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