From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: planner question..
Date: Thu, 17 Apr 2003 20:40:06 +0530
Message-ID: <200304172040.06654.mallah@trade-india.com> (raw)
In-Reply-To: <10432.1050589603@sss.pgh.pa.us>
References: <200304171404.10306.mallah@trade-india.com>
<10432.1050589603@sss.pgh.pa.us>
On Thursday 17 Apr 2003 7:56 pm, you wrote:
> Rajesh Kumar Mallah <mallah@trade-india.com> writes:
> > 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'.
>
> Are there a huge number of dead rows in the table? ("vacuum verbose"
> would give some info)
>
Yes as you predicted after a vacuum verbose the planner did
switch to seq scan and was only marginally slower than index
scan. (do u think we shud live with it?)
so whats the moral ?
ANALYZE itself is not sufficient in updating pg_statistic,
we must do VACUUM ANALYZE always ,
if so why would a seperate ANALYZE command exist ?
here is the info (in the same order as commands were executed)
tradein_clients=# explain analyze SELECT count(*) from general.profile_master where source='REGIS';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Aggregate (cost=18225.48..18225.48 rows=1 width=0) (actual time=6295.33..6295.34 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=14.54..6132.61 rows=102090 loops=1) |
| Index Cond: (source = 'REGIS'::character varying) |
| Total runtime: 6295.41 msec |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
(4 rows)
tradein_clients=# VACUUM VERBOSE general.profile_master;
INFO: --Relation general.profile_master--
INFO: Index profile_master_profile_id_pkey: Pages 426; Tuples 178031: Deleted 11802.
CPU 0.02s/0.12u sec elapsed 0.24 sec.
INFO: Index profile_master_index_userid: Pages 431; Tuples 178031: Deleted 8940.
CPU 0.00s/0.13u sec elapsed 0.14 sec.
INFO: Index profile_master_index_creation_date: Pages 420; Tuples 178031: Deleted 8940.
CPU 0.02s/0.12u sec elapsed 0.20 sec.
INFO: Index profile_master_index_company_id: Pages 419; Tuples 178031: Deleted 8940.
CPU 0.01s/0.10u sec elapsed 0.15 sec.
INFO: Index profile_master_index_eyp_list_id: Pages 419; Tuples 178031: Deleted 8940.
CPU 0.01s/0.11u sec elapsed 0.45 sec.
INFO: Index profile_master_index_iid_list_id: Pages 420; Tuples 178031: Deleted 8940.
CPU 0.01s/0.10u sec elapsed 0.46 sec.
INFO: Index profile_master_index_ip_list_id: Pages 421; Tuples 178031: Deleted 8940.
CPU 0.00s/0.11u sec elapsed 0.12 sec.
INFO: Index profile_master_index_catalog_company_id: Pages 419; Tuples 178031: Deleted 8940.
CPU 0.00s/0.12u sec elapsed 0.46 sec.
INFO: Index profile_master_index_source: Pages 619; Tuples 178031: Deleted 8940.
CPU 0.02s/0.09u sec elapsed 0.29 sec.
INFO: Removed 11802 tuples in 495 pages.
CPU 0.01s/0.03u sec elapsed 0.14 sec.
INFO: Pages 23923: Changed 36, Empty 0; Tup 178031: Vac 11802, Keep 0, UnUsed 325029.
Total CPU 0.96s/1.19u sec elapsed 11.97 sec.
INFO: --Relation pg_toast.pg_toast_122045388--
INFO: Pages 31: Changed 0, Empty 0; Tup 189: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.03 sec.
VACUUM
tradein_clients=# explain analyze SELECT count(*) from general.profile_master where source='REGIS';
+-----------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
+-----------------------------------------------------------------------------------------------------------------------------+
| Aggregate (cost=26398.37..26398.37 rows=1 width=0) (actual time=8355.76..8355.76 rows=1 loops=1) |
| -> Seq Scan on profile_master (cost=0.00..26148.39 rows=99994 width=0) (actual time=16.23..8237.53 rows=102090 loops=1) |
| Filter: (source = 'REGIS'::character varying) |
| Total runtime: 8355.88 msec |
+-----------------------------------------------------------------------------------------------------------------------------+
(4 rows)
> The given result seems suspect; an indexscan couldn't possibly read >50%
> of the rows in less than a quarter of the time for a seqscan. Unless
> (a) the table contains vast amounts of empty space that the seqscan has to
> slog through, or
Sorry i dont understand the vacuum verbose output throughly , does it looks like
though ?
(b) your second measurement is bogus due to caching
> performed by the first measurement.
I am not sure this time but i have the habit of running
EXPLAIN ANALYZE thrice and post the middle one ;-)
>
> Also, might the table be in order by the "source" column? A
> sufficiently high correlation might have persuaded the planner to try an
> indexscan even if point (a) isn't true.
Yes the data is loaded from one source completely before loading
from another source. So they were in order but i did a lot of updates
and deletes.
regds
mallah.
>
> regards, tom lane
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
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.
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, tgl@sss.pgh.pa.us
Subject: Re: planner question..
In-Reply-To: <200304172040.06654.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