Re: Interesting query plan change linked to the LIMIT parameter

On Tue, Jan 20, 2009 at 10:45 AM, Yannick Le Guédart
<yannick(at)over-blog(dot)com> wrote:

>
> The second query scans the whole comment table which is very dangerous for
> production servers.

That's not quite true. The second does an index scan- the planner
seems to be guessing that it'll fulfill the required limit early in
the index scan; only with a pathologically bad case would it actually
have to scan the entire thing. Basically, the second query is
optimized to spit out the first few rows quickly, since that's all you
asked for with the limit.

Note that your first query has a final cost estimate of "Limit
(cost=10261.19..10263.69 rows=1000 width=8)", indicating an estimated
10261.19 to emit the first row; the second has "Limit
(cost=0.00..3588.42 rows=1 width=8)" estimating 0.00 (basically,
instant) to emit the first - and only desired - row.

That all said, an explain analyze would give us a better idea of
what's going on- we can't tell if the planner is making bad estimates
without the knowledge of what the real timing and row count results of
plan stages were.

--
- David T. Wilson
david(dot)t(dot)wilson(at)gmail(dot)com