Re: How to keep Postgres from thrashing for a long time using "LIMIT" (or is there a better way?)!

"Nick Fankhauser" <nickf(at)ontko(dot)com> writes:
> We're using "LIMIT" to do this, but it appears that Postgres is going out &
> retrieving everything first and THEN applying the limit. (An EXPLAIN of the
> query with & without LIMIT bears this theory out...)

> select ...
> order by case_id limit 200,2000;

Do you have indexes on the case_id fields? If so, try specifying the
ORDER BY as an ORDER BY one or the other input case_id fields (ie,
qualify the name), rather than the output case_id field.

The only way to produce sorted output in the general case is to form
the whole query result, sort it, then return just the requested rows.
As a moment's thought will show, it's impossible for a sort step to
produce any output until it's examined all its input.

For a query that orders by a table column that has an index, a possible
query plan is to scan the table via the index until 2200 rows have been
produced. For a sufficiently small limit, I'd expect that plan to be
chosen. Hard to tell how small is sufficiently small, however,
especially given the complexity of the query.

You could try experimenting with SET ENABLE_SORT = OFF to discourage
the planner from using an explicit sort, too. Not clear whether you'd
get a better plan that way or not, but it's worth trying.

I'd be interested to hear your results --- both the EXPLAIN output for
different possibilities, and the actual timings.

BTW, I hope you are using 7.0.something. 6.5 and before didn't have
any ability to adjust the plan depending on presence of a LIMIT.

regards, tom lane