X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3A74D475D91 for ; Thu, 17 Apr 2003 09:51:09 -0400 (EDT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 06F10475C3D for ; Thu, 17 Apr 2003 09:51:08 -0400 (EDT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 276C9D613; Thu, 17 Apr 2003 06:51:12 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 18E865C03; Thu, 17 Apr 2003 06:51:12 -0700 (PDT) Date: Thu, 17 Apr 2003 06:51:12 -0700 (PDT) From: Stephan Szabo To: Rajesh Kumar Mallah Cc: Subject: Re: planner question.. In-Reply-To: <200304171404.10306.mallah@trade-india.com> Message-ID: <20030417064926.C87131-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Spam-Status: No, hits=-26.0 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REPLY_WITH_QUOTES autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/265 X-Sequence-Number: 12966 On Thu, 17 Apr 2003, Rajesh Kumar Mallah wrote: > For a distribution of data like below why does the planner > choses to do an index scan by default for source = 'REGIS' when > 50% > of the rows are having source='REGIS'. It's not estimating that though. > tradein_clients=# SET enable_indexscan=off; > tradein_clients=# explain analyze SELECT count(*) from profile_master where source='REGIS'; > +------------------------------------------------------------------------------------------------------------------------------+ > | QUERY PLAN | > +------------------------------------------------------------------------------------------------------------------------------+ > | Aggregate (cost=23982.58..23982.58 rows=1 width=0) (actual time=22872.97..22872.97 rows=1 loops=1) | > | -> Seq Scan on profile_master (cost=0.00..23970.40 rows=4871 width=0) (actual time=328.44..22730.69 rows=102090 loops=1) | It's expecting that about 5000 rows match not 100000. Does it get better if you raise the statistics target for the column (see ALTER TABLE) from 10 to say 50 or 100?