pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Cannot 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>
  2002-04-23 15:01 ` Re: Cannot get to use index scan on a big table! Stephan Szabo <sszabo@megazone23.bigpanda.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 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   ` Re: Cannot get to use index scan on a big table! 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-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 ` Re: Cannot get to use index scan on a big table! Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-04-24 05:57   ` Rajesh Kumar Mallah <mallah@trade-india.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