Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id E9EB39FA2C9 for ; Wed, 17 Jan 2007 01:47:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 72144-07 for ; Wed, 17 Jan 2007 01:47:12 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 3B8C09F9856 for ; Wed, 17 Jan 2007 01:47:13 -0400 (AST) Received: from 10.10.1.93 ([10.10.1.93]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Wed, 17 Jan 2007 05:47:05 +0000 Received: from localhost.localdomain by mail.g2switchworks.com; 16 Jan 2007 23:46:07 -0600 Subject: Re: PG8.2.1 choosing slow seqscan over idx scan From: Scott Marlowe To: Jeremy Haile Cc: Chad Wagner , Tom Lane , pgsql-performance@postgresql.org In-Reply-To: <1169002739.19758.1169636285@webmail.messagingengine.com> References: <1168982580.8275.1169588211@webmail.messagingengine.com> <26412.1168983547@sss.pgh.pa.us> <1168986053.16393.1169598917@webmail.messagingengine.com> <81961ff50701161444l3f932755m5a6e15ad2d948130@mail.gmail.com> <1169002739.19758.1169636285@webmail.messagingengine.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Tue, 16 Jan 2007 23:46:07 -0600 Message-Id: <1169012767.6271.7.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.2.2 (2.2.2-5) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/250 X-Sequence-Number: 22851 On Tue, 2007-01-16 at 21:58 -0500, Jeremy Haile wrote: > Hey Chad, > > The table is heavily inserted and deleted from. Recently I had done a > very large delete. I still keep wondering if this table is bloated with dead tuples. Even if you vacuum often if there's a connection with an idle transaction, the tuples can't be reclaimed and the table would continue to grow. Anyway, what does vacuum analyze tablename say (where tablename is, of course, the name of the table we're looking at)? Pay particular attention to DETAIL statements. Assuming the table's NOT bloated, you may do well to increase the effective_cache_size, which doesn't allocate anything, but just tells the query planner about how big your operating systems file cache is as regards postgresql. It's a bit of a course setting, i.e. you can make rather large changes to it without blowing things up. If you've got a couple gigs on your machine, try setting it to something like 512MB or so. If your table is bloating, and you don't have idle transactions hanging of the database, it could be that your fsm settings are too low.