pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.

view thread (3+ messages)

Message-ID: <3CC64943.1FECC6D8@trade-india.com>
Permalink:  ../3CC64943.1FECC6D8@trade-india.com/
Also on:    postgresql.org/message-id/3CC64943.1FECC6D8@trade-india.com

 · 

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, 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