Re: Limit changes query plan

"Gaetano Mendola" <mendola(at)bigfoot(dot)com> writes:

> I don't get why a limit is going to change the query plan and most of all decreasing
> the performances.

Until we see the explain analyze it won't be clear what exactly is going on.
But in theory a LIMIT can definitely change the plan because the planner knows
it won't need to generate all the rows to satisfy the LIMIT.

In the plans you gave note that the plan for the unlimited query has a Sort so
it has to produce all the records every time. The second query produces the
records in order so if the LIMIT is satisfied quickly then it can save a lot
of work.

It's evidently guessing wrong about the limit being satisfied early. The
non-indexed restrictions might be pruning out a lot more records than the
planner expects. Or possibly the table is just full of dead records.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!