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

On Tue, 23 Apr 2002, Rajesh Kumar Mallah wrote:

> i have a simple SQL query and it does not seems to use index
> despite its existance and VCUUMING of table.
>
> tradein_clients=> explain  select email_id from email_source  where
> source_id=186 ;
> NOTICE:  QUERY PLAN:
>
> Seq Scan on email_source  (cost=0.00..19191.50 rows=41602 width=4)
>
> EXPLAIN
> tradein_clients=>
>
> (can anyone please explain why the figure 41602??)

That's the estimated number of matching rows.  I'd guess that this
number is an over estimate (how many rows actually are returned?)
Do you have any particularly frequent values of source_id that are much
more common than others?  7.1 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.

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?