pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: pgsql-sql@postgresql.org
Subject: planner question..
Date: Thu, 17 Apr 2003 14:04:10 +0530
Message-ID: <200304171404.10306.mallah@trade-india.com> (raw )
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.
view thread (8+ messages) latest in thread
Message-ID: <200304171404.10306.mallah@trade-india.com>
Permalink: ../200304171404.10306.mallah@trade-india.com/
Also on: postgresql.org/message-id/200304171404.10306.mallah@trade-india.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mallah@trade-india.com
Subject: Re: planner question..
In-Reply-To: <200304171404.10306.mallah@trade-india.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox