Re: Unbearably slow cascading deletes

On Tue, 20 Jul 2004, Stephan Szabo wrote:

>
> On Tue, 20 Jul 2004 andrew(at)pillette(dot)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.