Speeding up DELETEs on table with FKs ... - Mailing list pgsql-hackers

From Marc G. Fournier
Subject Speeding up DELETEs on table with FKs ...
Date
Msg-id 20041010190658.Q54093@ganymede.hub.org
Whole thread Raw
Responses Re: Speeding up DELETEs on table with FKs ...
List pgsql-hackers
I posted to -sql the other day about an atrociously slow DELETE on a table 
that has two FKs to a 'parent' table ... if the # of records in the table 
that match the condition is 1, its fast ... in the sample I'm working 
with, there are 1639 records in the table ...

Now, I'm making a guess that for each row that needs to be DELETEd, the FK 
forces a 'SELECT * FROM fk_table WHERE fk = value', to check for its 
existence ... so, in this case, we're talking about 1639*2 SELECTs to the 
backend ... is this correct?

If this is correct ... is 7.4/8.0 any smarter when it comes to 
'duplicates'?  Somehow keeping a list of 'fk = value's that have already 
been checked, instead of re-issuing a new SELECT for each row?  In the 
case of the table I'm working on, all row DELETEs would have the same 
result, as the delete is *on* the FK value itself, so the first check of 
the fk_table should be all that is required ...

Not sure if this is even possible ... or is already done ...

Note that I'm working on a 7.3 database right now, so if this is something 
that is improved with 7.4, please let me know ..
 ----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664


pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Status ofTrigger Firing Order and 'FOR EACH STATEMENT'?
Next
From: Andrew Dunstan
Date:
Subject: cvs tip broken build for plpython