X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 79800329E01 for ; Tue, 14 Sep 2004 17:44:04 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40241-09 for ; Tue, 14 Sep 2004 16:44:02 +0000 (GMT) Received: from mail.logi-track.com (burro.logi-track.com [213.239.193.212]) by svr1.postgresql.org (Postfix) with ESMTP id D036D329D86 for ; Tue, 14 Sep 2004 17:44:01 +0100 (BST) Received: from kingfisher.intern.logi-track.com (G6863.g.pppool.de [80.185.104.99]) by mail.logi-track.com (Postfix) with ESMTP id 82B06305A3; Tue, 14 Sep 2004 18:44:04 +0200 (CEST) Received: from kingfisher.intern.logi-track.com (localhost [127.0.0.1]) by kingfisher.intern.logi-track.com (Postfix) with SMTP id BA621AB31B; Tue, 14 Sep 2004 18:43:58 +0200 (CEST) Date: Tue, 14 Sep 2004 18:43:58 +0200 From: Markus Schaber To: Stephen Crowley Cc: pgsql-performance@postgresql.org Subject: Re: Large # of rows in query extremely slow, not using Message-ID: <20040914184358.08e271fe@kingfisher.intern.logi-track.com> In-Reply-To: <3f71fdf104091317515c756512@mail.gmail.com> References: <3f71fdf104091317515c756512@mail.gmail.com> Organization: logi-track ag, =?ISO-8859-15?Q?z=FCrich?= X-Mailer: Sylpheed-Claws 0.9.12a (GTK+ 1.2.10; i386-pc-linux-gnu) X-Face: Nx5T&>Nj$VrVPv}sC3IL&)TqHHOKCz/|)R$i"*r@w0{*I6w; UNU_hdl1J4NI_m{IMztq=>cmM}1gCLbAF+9\#CGkG8}Y{x%SuQ>1#t:; Z(|\qdd[i]HStki~#w1$TPF}:0w-7"S\Ev|_a$K wrote: > Does postgres cache the entire result set before it begins returning > data to the client? >=20 > 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 --=20 markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 z=FCrich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:schabios@logi-track.com | www.logi-track.com