Re: Large # of rows in query extremely slow, not using index

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Stephen Crowley <stephen(dot)crowley(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Large # of rows in query extremely slow, not using index
Date: 2004-09-14 01:11:07
Message-ID: 28088.1095124267@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Stephen Crowley <stephen(dot)crowley(at)gmail(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Rod Taylor 2004-09-14 01:11:13 Re: Large # of rows in query extremely slow, not using
Previous Message Stephen Crowley 2004-09-14 00:51:22 Large # of rows in query extremely slow, not using index