From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Karim A Nassar <Karim.Nassar@NAU.EDU>
Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Delete query takes exorbitant amount of time
Date: Tue, 29 Mar 2005 19:53:26 +0100
Message-ID: <1112122406.11750.1055.camel@localhost.localdomain> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: simon@2ndquadrant.com, tgl@sss.pgh.pa.us, Karim.Nassar@NAU.EDU, sszabo@megazone.bigpanda.com, chriskl@familyhealth.com.au
Subject: Re: Delete query takes exorbitant amount of time
In-Reply-To: <1112122406.11750.1055.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox