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 7F08B329E8D for ; Tue, 14 Sep 2004 02:11:22 +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 32166-03 for ; Tue, 14 Sep 2004 01:11:15 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 7CB7D329E85 for ; Tue, 14 Sep 2004 02:11:16 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i8E1B7N1028089; Mon, 13 Sep 2004 21:11:07 -0400 (EDT) To: Stephen Crowley Cc: pgsql-performance@postgresql.org Subject: Re: Large # of rows in query extremely slow, not using index In-reply-to: <3f71fdf104091317515c756512@mail.gmail.com> References: <3f71fdf104091317515c756512@mail.gmail.com> Comments: In-reply-to Stephen Crowley message dated "Mon, 13 Sep 2004 19:51:22 -0500" Date: Mon, 13 Sep 2004 21:11:07 -0400 Message-ID: <28088.1095124267@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/97 X-Sequence-Number: 8225 Stephen Crowley 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