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

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Rajesh Kumar Mallah <mallah(at)trade-india(dot)com>
Cc: PostgResql SQL Mailing List <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Cannot get to use index scan on a big table!
Date: 2002-04-23 15:01:02
Message-ID: 20020423075534.S13153-100000@megazone23.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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?

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Bruce Momjian 2002-04-23 16:15:33 Re: call the same pl/pgsql procedure twice in the same connection
Previous Message Ian Cass 2002-04-23 14:07:49 Re: Date indexing