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 AD9875345F for ; Thu, 12 May 2005 12:48:36 -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 66984-06 for ; Thu, 12 May 2005 15:48:35 +0000 (GMT) Received: from mail.liquidcore.com (mail.liquidcore.net [68.17.96.60]) by svr1.postgresql.org (Postfix) with ESMTP id E3FC753454 for ; Thu, 12 May 2005 12:48:34 -0300 (ADT) Received: (from root@localhost) by mail.liquidcore.com (8.13.1/8.12.10) id j4CFmiVf021291; Thu, 12 May 2005 10:48:44 -0500 Received: from [192.168.0.81] (ppasalalinux.nqadmin.com [192.168.0.81]) by mail.liquidcore.com (8.13.1/8.12.10) with ESMTP id j4CFmhhs021257; Thu, 12 May 2005 10:48:43 -0500 Message-ID: <42837B95.2090908@nqadmin.com> Date: Thu, 12 May 2005 10:51:49 -0500 From: Prasanth Reply-To: dbadmin@nqadmin.com User-Agent: Mozilla Thunderbird 1.0.2-1.3.2 (X11/20050324) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Scott Marlowe Cc: pgsql-admin@postgresql.org Subject: Re: Query Plan - Index Scan & Seq Scan References: <428370AC.3070207@nqadmin.com> <1115911416.3868.265.camel@state.g2switchworks.com> In-Reply-To: <1115911416.3868.265.camel@state.g2switchworks.com> X-Enigmail-Version: 0.90.2.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-scanner: scanned for malicious content X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.051 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200505/153 X-Sequence-Number: 17582 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 Thanks, -Prasanth. Scott Marlowe wrote: > 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. > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > >