X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id C99A247610C
	for <pgsql-sql@postgresql.org>; Thu, 17 Apr 2003 04:34:32 -0400 (EDT)
Received: from rtlocal.trade-india.com (mail-relay.trade-india.com
	[203.196.129.235])
	by postgresql.org (Postfix) with SMTP id 9A12A475ECE
	for <pgsql-sql@postgresql.org>; Thu, 17 Apr 2003 04:34:16 -0400 (EDT)
Received: (qmail 10669 invoked from network); 17 Apr 2003 08:36:59 -0000
Received: from system67.trade-india-local.com (192.168.0.67)
	by infocom-236-129-del.trade-india.com with SMTP;
	17 Apr 2003 08:36:59 -0000
From: Rajesh Kumar Mallah <mallah@trade-india.com>
Organization: Infocom Network Limited
To: pgsql-sql@postgresql.org
Subject: planner question..
Date: Thu, 17 Apr 2003 14:04:10 +0530
User-Agent: KMail/1.5
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <200304171404.10306.mallah@trade-india.com>
X-Spam-Status: No, hits=-7.2 required=5.0
	tests=BAYES_01,SIGNATURE_SHORT_SPARSE,USER_AGENT version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200304/259
X-Sequence-Number: 12960



Hi,

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'.

Of course index produced better results 22 sec versus 4 secs.
but can someone explain this case.

tradein_clients=# SELECT source,count(*) from  profile_master group by source;
+----------+--------+
|  source  | count  |
+----------+--------+
| BRANDING |  64008 |
| CATALOG  |    711 |
| EYP      |  10380 |
| IID      |    349 |
| IP       |    493 |
| REGIS    | 102090 |
+----------+--------+
(6 rows)

tradein_clients=# SELECT count(*) from  profile_master ;
+--------+
| count  |
+--------+
| 178031 |
+--------+
(1 row)


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) |
|         Filter: (source = 'REGIS'::character varying)                                                                        |
| Total runtime: 22873.03 msec                                                                                                 |
+------------------------------------------------------------------------------------------------------------------------------+
(4 rows)

tradein_clients=# SET enable_indexscan=on;
SET
tradein_clients=# explain analyze SELECT count(*) from  profile_master where source='REGIS';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                          QUERY PLAN                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Aggregate  (cost=18225.48..18225.48 rows=1 width=0) (actual time=5919.24..5919.24 rows=1 loops=1)                                                             |
|   ->  Index Scan using profile_master_index_source on profile_master  (cost=0.00..18213.31 rows=4871 width=0) (actual time=9.43..5786.15 rows=102090 loops=1) |
|         Index Cond: (source = 'REGIS'::character varying)                                                                                                     |
| Total runtime: 5919.31 msec                                                                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
(4 rows)

-- 
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.