From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: PostgResql SQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: Cannot get to use index scan on a big table!
Date: Wed, 24 Apr 2002 11:27:23 +0530
Message-ID: <3CC64943.1FECC6D8@trade-india.com> (raw)
References: <20020423075534.S13153-100000@megazone23.bigpanda.com>
Stephan Szabo wrote:
The actual now nos rows is 10 for source_id=186
>
> Do you have any particularly frequent values of source_id that are much
> more common than others? 7.1
yes you are right freq. of source_id are *quite* varied. from 700,000
to 10 :-( but except for the most frequent avg. freq. may be 5000
> and earlier had problems with over
> estimating the number of matching rows when the distribution had a
> very uneven distribution of values, select * from pg_statistic where
> starelid=(select oid from pg_class where relname='email_source')
> should give the stored statistics from the analyze.
select * from pg_statistic where starelid=(select oid from pg_class where
relname='email_source');
starelid | staattnum | staop | stanullfrac | stacommonfrac | stacommonval
| staloval | stahival
----------+-----------+-------+-------------+---------------+--------------+----------+----------
31548 | 1 | 97 | 0 | 5.59811e-06 | 53872
| 2 | 1626629
31548 | 2 | 97 | 0 | 0.611849 | 156
| 1 | 186
(2 rows)
AFTER VACUUM ANALYINZING JUST NOW!
starelid | staattnum | staop | stanullfrac | stacommonfrac | stacommonval
| staloval | stahival
----------+-----------+-------+-------------+---------------+--------------+----------+----------
31548 | 1 | 97 | 0 | 6.4078e-06 | 53872
| 2 | 1629500
31548 | 2 | 97 | 0 | 0.600296 | 156
| 1 | 190
(2 rows)
>
>
> As a comparison, if you do "set enable_seqscan=off;" and then do
> the query and explain, what does it give for the costs there, and
> does it take less time?
Still its overestimated , but its much faster
explain select count(email_id) from email_source where source_id=186;
NOTICE: QUERY PLAN:
Aggregate (cost=46798.14..46798.14 rows=1 width=4)
-> Index Scan using email_source_source_id on email_source
(cost=0.00..46688.98 rows=43664 width=4)
EXPLAIN
Stephan thanks for the reply,
i think my question has been adequetely answered and i conclude
that i shud. upgrade my PG setup without wasting my/others' time.
regds
mallah.
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, sszabo@megazone23.bigpanda.com
Subject: Re: Cannot get to use index scan on a big table!
In-Reply-To: <3CC64943.1FECC6D8@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