Re: Cannot get to use index scan on a big table!

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.