Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 2DB1E9FB335 for ; Tue, 16 Jan 2007 17:39: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 88221-06 for ; Tue, 16 Jan 2007 17:39:11 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 4267D9FB319 for ; Tue, 16 Jan 2007 17:39:14 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l0GLd7Th026413; Tue, 16 Jan 2007 16:39:07 -0500 (EST) To: "Jeremy Haile" cc: pgsql-performance@postgresql.org Subject: Re: PG8.2.1 choosing slow seqscan over idx scan In-reply-to: <1168982580.8275.1169588211@webmail.messagingengine.com> References: <1168982580.8275.1169588211@webmail.messagingengine.com> Comments: In-reply-to "Jeremy Haile" message dated "Tue, 16 Jan 2007 16:23:00 -0500" Date: Tue, 16 Jan 2007 16:39:07 -0500 Message-ID: <26412.1168983547@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/245 X-Sequence-Number: 22846 "Jeremy Haile" writes: > Running PostgreSQL 8.2.1 on Win32. The query planner is choosing a seq > scan over index scan even though index scan is faster (as shown by > disabling seqscan). Table is recently analyzed and row count estimates > seem to be in the ballpark. Try reducing random_page_cost a bit. Keep in mind that you are probably measuring a fully-cached situation here, if you repeated the test case. If your database fits into memory reasonably well then that's fine and you want to optimize for that case ... but otherwise you may find yourself pessimizing the actual behavior. regards, tom lane