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

On Tue, 2005-03-29 at 12:31 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > That implies to me that LIMIT queries are not considered correctly in
> > the M&L formula and thus we are more likely to calculate a too-high cost
> > for using an index in those circumstances....and thus more likely to
> > SeqScan for medium sized relations?
> 
> You misunderstand how LIMIT is handled.  

Huh? Well, not this time. (Though my error rate is admittedly high.)

> The plan structure is
> 
> 	LIMIT ...
> 		regular plan ...
> 
> and so the strategy is to plan and cost the regular plan as though it
> would be carried out in full, and then take an appropriate fraction
> of that at the LIMIT stage.

To cost it as if it would be carried out in full and then not execute in
full is the same thing as saying it overestimates the actual execution
cost. Which can lead to selection of SeqScan plan when the IndexScan
would have been cheaper, all things considered.

...it could work like this

	LIMIT ....
		regular plan (plan chosen knowing that LIMIT follows)

so that the LIMIT would be considered in the M&L formula.

Not that I am driven by how other systems work, but both DB2 and Oracle
allow this form of optimization.

There's not a huge benefit in sending LIMIT 1 through on the FK check
queries unless they'd be taken into account in the planning.

Anyway, I'm not saying I know how to do this yet/ever, just to say it is
possible to use the information available to better effect.

This looks like a TODO item to me? Thoughts?

Best Regards, Simon Riggs