Re: Query does not return rows unless a LIMIT statement is used.

Richard Kut <rkut(at)intelerad(dot)com> writes:
> Note the LIMIT 5324 statement. I empirically determined that this magic
> number is the maximum row count before the query performance degrades
> completely. It appears to be a sudden performance degradation. If I increase
> the value in LIMIT beyond 5324, or if I remove the LIMIT altogether, then the
> query performance is horrible.

This is really, really hard to believe considering that you're showing
the exact same query plan in both cases. Are you certain you didn't
get confused about which EXPLAIN output was which? It's entirely
possible for the planner to pick a different underlying plan depending
on the LIMIT setting --- small LIMIT values will make it tend to prefer
fast-start query plans, since a slow-overall plan with nil startup cost
can beat a fast-overall plan with high startup cost if you're not
intending to run it to completion. Up until I got to the query plans
I thought you were describing a situation where the planner had switched
over to a high-startup-cost plan much sooner than it should do, but your
EXPLAIN printouts don't reflect any such thing.

If it is a question of bad choice of query plan, the answer is probably
that you need to VACUUM and/or ANALYZE the tables involved so that the
planner has more accurate statistics to work with. Do the estimated
row counts shown by EXPLAIN look reasonable at all?

> From observation using the TOP utility, I have noticed that when the query is
> running without the LIMIT statement, that the postmaster process resident
> memory size is increasing at a steady rate. It would seem that the query
> results are being gathered in memory first before being displayed in PSQL. Is
> there a way around this?

The postmaster doesn't gather up query results, but psql itself will.
If you want incremental fetch of results, use a cursor and FETCH some
appropriate number of rows at a time.

> commit_delay = 100000

This is unlikely to be a good idea. Your other parameter settings look
reasonable though.

regards, tom lane