From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephen Crowley <stephen.crowley@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Large # of rows in query extremely slow, not using index
Date: Mon, 13 Sep 2004 21:11:07 -0400
Message-ID: <28088.1095124267@sss.pgh.pa.us> (raw)
In-Reply-To: <3f71fdf104091317515c756512@mail.gmail.com>
References: <3f71fdf104091317515c756512@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, stephen.crowley@gmail.com
Subject: Re: Large # of rows in query extremely slow, not using index
In-Reply-To: <28088.1095124267@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox