X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8C13253421 for ; Thu, 12 May 2005 12:51:33 -0300 (ADT) 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 67421-06 for ; Thu, 12 May 2005 15:51:30 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id A32B653236 for ; Thu, 12 May 2005 12:51:27 -0300 (ADT) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 12 May 2005 10:51:26 -0500 Subject: Re: Query Plan - Index Scan & Seq Scan From: Scott Marlowe To: dbadmin@nqadmin.com Cc: pgsql-admin@postgresql.org In-Reply-To: <42837B95.2090908@nqadmin.com> References: <428370AC.3070207@nqadmin.com> <1115911416.3868.265.camel@state.g2switchworks.com> <42837B95.2090908@nqadmin.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1115913086.22291.1.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Thu, 12 May 2005 10:51:26 -0500 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/154 X-Sequence-Number: 17583 On Thu, 2005-05-12 at 10:51, Prasanth wrote: > I agree with you. > > But I have the where conditions on the tables I was expecting the planner to > user index scan but it went for seq scan. > > I did a little testing using what you said. > > Below are the results. > > SELECT a.id FROM a,b WHERE a.id = b.id AND a.code >2 AND b.account_id = 16221; > > Total runtime: 18194.936 ms > > Then I set the seqscan off and ran the same query. > > Total runtime: 27.554 ms Good! This tells us two things, 1: Your database can use the indexes (sometimes indexes can't be used for various reasons, which are quickly disappearing by the way.) and 2: Your database is making the wrong choice about when to use a seq scan versus an index. What does the explain analyze output from that query say about row estimates versus actual rows returned?