Stephen Crowley <stephen.crowley@gmail.com> writes:
> Does postgres cache the entire result set before it begins returning
> data to the client?
The backend doesn't, but libpq does, and I think JDBC does too.
I'd recommend using a cursor so you can FETCH a reasonable number of
rows at a time.
> Also, why would it choose not to use the index?
Selecting 1/10th of a table is almost always a poor candidate for an
index scan. You've got about 100 rows per page (assuming the planner's
width estimate is credible) and so on average every page of the table
has about ten rows that need to be picked up and returned. You might as
well just seqscan and be sure you don't read any page more than once.
regards, tom lane