Re: LIMIT clause optimization

  So, generally selecting all rows from a table an fetching only the
first one is probably faster than limiting the query to its first row?

On Tue, 2003-01-07 at 11:14, terry@ashtonwoodshomes.com wrote:
> 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@greatgulfhomes.com
>
>
>
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.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@ritterdosreis.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@postgresql.org)
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--

Felipe Schnack
Analista de Sistemas
felipes@ritterdosreis.br
Cel.: (51)91287530
Linux Counter #281893

Centro Universitário Ritter dos Reis
http://www.ritterdosreis.br
ritter@ritterdosreis.br
Fone/Fax.: (51)32303341