On Tue, 20 Jul 2004 [EMAIL PROTECTED] 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
> 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?

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

Reply via email to