From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Rajesh Kumar Mallah <mallah@trade-india.com>
Cc: PostgResql SQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: Cannot get to use index scan on a big table!
Date: Tue, 23 Apr 2002 08:01:02 -0700 (PDT)
Message-ID: <20020423075534.S13153-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <3CC511E1.F19105F6@trade-india.com>
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?
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: sszabo@megazone23.bigpanda.com, mallah@trade-india.com
Subject: Re: Cannot get to use index scan on a big table!
In-Reply-To: <20020423075534.S13153-100000@megazone23.bigpanda.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