Subject: Re: Delete query takes exorbitant amount of time
From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>,
	Karim A Nassar <Karim.Nassar@NAU.EDU>,
	Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
	pgsql-performance@postgresql.org
In-Reply-To: <22527.1112110310@sss.pgh.pa.us>
References: <Pine.SOL.4.21.0503290120550.5642-100000@coruscant.cet.nau.edu>
	<1112095776.11750.958.camel@localhost.localdomain>
	<20050329054714.V50612@megazone.bigpanda.com>
	<20050329061837.A51620@megazone.bigpanda.com>
	<21616.1112108201@sss.pgh.pa.us>
	<1112109880.11750.977.camel@localhost.localdomain>
	<22527.1112110310@sss.pgh.pa.us>
Content-Type: text/plain
Organization: 2nd Quadrant
Date: Tue, 29 Mar 2005 17:01:27 +0100
Message-Id: <1112112087.11750.984.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Tue, 2005-03-29 at 10:31 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > If action is NO ACTION or RESTRICT then 
> > 	we need to SELECT at most 1 row that matches the criteria
> > 	which means we can use LIMIT 1
> 
> > If action is CASCADE, SET NULL, SET DEFAULT then
> > 	we need to UPDATE or DELETE all rows that match the criteria
> > 	which means we musnt use LIMIT and need to use FOR UPDATE
> 
> Huh?  UPDATE/DELETE don't use FOR UPDATE.  I think you have failed
> to break down the cases sufficiently.  In particular it matters which
> side of the RI constraint you are working from ...

OK... too quick, sorry. I'll hand over to Stephan for a better and more
exhaustive explanation/analysis... but AFAICS we *can* always know the
correct formulation of the query prepare time, whether or not we do
currently.

Best Regards, Simon Riggs