Skip site navigation (1) Skip section navigation (2)

Re: Delete query takes exorbitant amount of time

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Karim A Nassar <Karim(dot)Nassar(at)NAU(dot)EDU>,Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>,Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>,pgsql-performance(at)postgresql(dot)org
Subject: Re: Delete query takes exorbitant amount of time
Date: 2005-03-29 18:53:26
Message-ID: 1112122406.11750.1055.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, 2005-03-29 at 12:31 -0500, Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)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


In response to

pgsql-performance by date

Next:From: Michael FuhrDate: 2005-03-29 19:08:15
Subject: Re: Million of rows
Previous:From: Vinicius BernardiDate: 2005-03-29 18:33:24
Subject: Million of rows

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group