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 C7C9553325 for ; Thu, 12 May 2005 12:23:48 -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 63369-01 for ; Thu, 12 May 2005 15:23:40 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id F032953313 for ; Thu, 12 May 2005 12:23:37 -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:23:36 -0500 Subject: Re: Query Plan - Index Scan & Seq Scan From: Scott Marlowe To: dbadmin@nqadmin.com Cc: pgsql-admin@postgresql.org In-Reply-To: <428370AC.3070207@nqadmin.com> References: <428370AC.3070207@nqadmin.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1115911416.3868.265.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:23:36 -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/150 X-Sequence-Number: 17579 On Thu, 2005-05-12 at 10:05, Prasanth wrote: > When joining two tables the query plan is doing a seq scan rather than index > scan. I do have indexes on the columns used for joining the tables. > > Example: > SELECT a.id FROM a, b WHERE a.id = b.id; > > QUERY PLAN > ----------------------------------------------------------------------------------------- > Hash Join (cost=13865.30..326413.23 rows=6451 width=18) > Hash Cond: ("outer".id = "inner".id) > -> Seq Scan on a (cost=0.00..125076.37 rows=6450937 width=18) > -> Hash (cost=10168.64..10168.64 rows=500664 width=4) > -> Seq Scan on b (cost=0.00..10168.64 rows=500664 width=4) > (5 rows) > > > The planner used to perform a index scan. I have added a lot of data in those > two tables. Right now both tables have millions of records. After adding the new > records the planner is going for a seq scan while doing the join. > > Is there any tunning I can do so that the query planner would do a index scan? > > I did a vacuum analyze but no change. try this: explain analyze SELECT a.id FROM a, b WHERE a.id = b.id; set enable_seqscan=off; explain analyze SELECT a.id FROM a, b WHERE a.id = b.id; and see which is faster. It's quite likely that using an index here makes no sense, since there's no selectivity happening, and you need all the data anyway.