pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCannot get to use index scan on a big table!
3+ messages / 2 participants
[nested] [flat]
* Cannot get to use index scan on a big table!
@ 2002-04-23 07:48 Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 1 reply; 3+ messages in thread
From: Rajesh Kumar Mallah @ 2002-04-23 07:48 UTC (permalink / raw)
To: pgsql-sql
Hi Folks,
i guess by now it is the most freq. asked question on list ;-)
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??)
i am using postgresql 7.1.2 (ples. do not curse me for not upg.)
i have done "VACUUM ANALYZE" on the table in question
the table has ~ 1.1 million records and seq scan is
killing my apps
regds
mallah.
Some more info below:
tradein_clients=> VACUUM VERBOSE ANALYZE email_source ;
NOTICE: --Relation email_source--
NOTICE: Pages 5794: Changed 1, reaped 30, Empty 0, New 0; Tup 1071800:
Vac 81, Keep/VTL 9/0, Crash 0, UnUsed 9, MinLen 40, MaxLen 40; Re-using:
Free/Avail. Space 4560/2208; EndEmpty/Avail. Pages 0/29. CPU 0.30s/0.12u
sec.
NOTICE: Index email_source_email_id: Pages 2350; Tuples 1071800:
Deleted 0. CPU 0.16s/1.08u sec.
NOTICE: Index email_source_source_id: Pages 2350; Tuples 1071800:
Deleted 0. CPU 0.14s/1.02u sec.
NOTICE: Rel email_source: Pages: 5794 --> 5794; Tuple(s) moved: 32. CPU
0.01s/0.01u sec.
NOTICE: Index email_source_email_id: Pages 2350; Tuples 1071800:
Deleted 32. CPU 0.15s/0.84u sec.
NOTICE: Index email_source_source_id: Pages 2350; Tuples 1071800:
Deleted 32. CPU 0.11s/0.79u sec.
NOTICE: Analyzing...
VACUUM
table structures:
tradein_clients=> \d email_source
Table "email_source"
Column | Type | Modifiers
-----------+---------+-----------
email_id | integer |
source_id | integer |
Indexes: email_source_email_id,
email_source_source_id
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Cannot get to use index scan on a big table!
@ 2002-04-23 15:01 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 1 reply; 3+ messages in thread
From: Stephan Szabo @ 2002-04-23 15:01 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: 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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Cannot get to use index scan on a big table!
@ 2002-04-24 05:57 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 3+ messages in thread
From: Rajesh Kumar Mallah @ 2002-04-24 05:57 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-04-24 05:57 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-23 07:48 Cannot get to use index scan on a big table! Rajesh Kumar Mallah <mallah@trade-india.com>
2002-04-23 15:01 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-04-24 05:57 ` Rajesh Kumar Mallah <mallah@trade-india.com>
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