Re: LIMIT clause optimization

Further, I think if your query has an order by clause then the whole query
is executed, sorted, then all but the limit'd rows are truncated. Hence
there is no performance improvement.

A very VERY smart database engine could perhaps in some cases use an index
to determine in advance the sort and get the rows in the correct order, and
hence stop when the limit was reached. But that would be a rare case at
best, and I doubt anyone has gone to the brain damage of implementing such
complexity considering the very limited payback.

Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry(at)greatgulfhomes(dot)com

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Bruno
> Wolff III
> Sent: Tuesday, January 07, 2003 8:17 AM
> To: Felipe Schnack
> Cc: Holger Klawitter; pgsql-general
> Subject: Re: [GENERAL] LIMIT clause optimization
>
>
> On Tue, Jan 07, 2003 at 10:44:15 -0200,
> Felipe Schnack <felipes(at)ritterdosreis(dot)br> wrote:
> > Why? I don't understand. If I create a query and want
> just the first
> > row from it, wouldn't speed up things a lot just adding
> "LIMIT 1" in the
> > end of the query?
>
> That depends on the query. For some queries you will only see a small
> speed up. It is also possible for limit to effect the
> optimization results
> in a way that ends up slowing things down (though that should be rare)
> because an inferior plan ends up being chosen.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo(at)postgresql(dot)org)
>