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

Re: The usual sequential scan, but with LIMIT !

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Pierre-Frédéric Caillaud <lists(at)boutiquenumerique(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: The usual sequential scan, but with LIMIT !
Date: 2004-09-06 16:40:41
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-performance
=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= <lists(at)boutiquenumerique(dot)com> writes:
> Now, if I LIMIT the query to 10 rows, the index should be used all the  
> time, because it will always return few rows... well, it doesn't !

Not at all.  From the planner's point of view, the LIMIT is going to
reduce the cost by about a factor of 10/1403, since the underlying plan
step will only be run partway through.  That's not going to change the
decision about which underlying plan step is cheapest: 10/1403 of a
cheaper plan is still always less than 10/1403 of a more expensive plan.

Later, you note that LIMIT with ORDER BY does affect the plan choice
--- that's because in that situation one plan alternative has a much
higher startup cost than the other (namely the cost of a sort step).
A small LIMIT can allow the fast-startup plan to be chosen even though
it would be estimated to be the loser if run to completion.

			regards, tom lane

In response to


pgsql-performance by date

Next:From: Pierre-Frédéric CaillaudDate: 2004-09-07 06:51:54
Subject: Re: The usual sequential scan, but with LIMIT !
Previous:From: Markus SchaberDate: 2004-09-06 15:56:18
Subject: Re: Multiple Uniques

pgsql-general by date

Next:From: Joshua D. DrakeDate: 2004-09-06 17:22:12
Subject: Re: RAD with postgreSQL
Previous:From: Tom LaneDate: 2004-09-06 16:25:11
Subject: Re: tg_relation doesn't seem to have the attribute names!

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