From: Markus Schaber <schabios@logi-track.com>
To: Stephen Crowley <stephen.crowley@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Large # of rows in query extremely slow, not using
Date: Tue, 14 Sep 2004 18:43:58 +0200
Message-ID: <20040914184358.08e271fe@kingfisher.intern.logi-track.com> (raw)
In-Reply-To: <3f71fdf104091317515c756512@mail.gmail.com>
References: <3f71fdf104091317515c756512@mail.gmail.com>
Hi, Stephen,
On Mon, 13 Sep 2004 19:51:22 -0500
Stephen Crowley <stephen.crowley@gmail.com> wrote:
> Does postgres cache the entire result set before it begins returning
> data to the client?
>
> I have a table with ~8 million rows and I am executing a query which
> should return about ~800,000 rows. The problem is that as soon as I
> execute the query it absolutely kills my machine and begins swapping
> for 5 or 6 minutes before it begins returning results. Is postgres
> trying to load the whole query into memory before returning anything?
> Also, why would it choose not to use the index? It is properly
> estimating the # of rows returned. If I set enable_seqscan to off it
> is just as slow.
As you get about 10% of all rows in the table, the query will hit every
page of the table.
Maybe it helps to CLUSTER the table using the index on your query
parameters, and then set enable_seqscan to off.
But beware, that you have to re-CLUSTER after modifications.
HTH,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com
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: schabios@logi-track.com, stephen.crowley@gmail.com
Subject: Re: Large # of rows in query extremely slow, not using
In-Reply-To: <20040914184358.08e271fe@kingfisher.intern.logi-track.com>
* 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