Re: Why does it not use the index?

Thanks for the response:

I took a look at the table with 'vacuum verbose analyze', here's the results:

dumps=# vacuum verbose analyze fal_profdel;
NOTICE:  --Relation fal_profdel--
NOTICE:  Pages 62232: Changed 0, reaped 205, Empty 0, New 0; Tup 4664867: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 447,
MinLen103, MaxLen 106; Re-using: Free/Avail. Space 14896/0; EndEmpty/Avail. Pages 0/0. CPU 2.53s/0.58u sec. 
NOTICE:  Index fal_prfdel_date: Pages 25318; Tuples 4664867: Deleted 0. CPU 0.90s/4.39u sec.
NOTICE:  Index fal_prfdel_cn: Pages 23128; Tuples 4664867: Deleted 0. CPU 0.95s/4.63u sec.
NOTICE:  Index fal_prfdel_pk: Pages 28323; Tuples 4664867: Deleted 0. CPU 1.21s/4.40u sec.
NOTICE:  Analyzing...
VACUUM


Then - afterwards, I ran the explain again:

dumps=# explain select card_num from fal_profdel where card_num = '4828820006970';
NOTICE:  QUERY PLAN:

Index Scan using fal_prfdel_cn on fal_profdel  (cost=0.00..4.95 rows=1 width=12)

EXPLAIN


WTF? Why would a vacuum be necessary in order for it to start using the index?


By the way, the actual query takes subseconds to return now. MUCH better.


So - let me know why one would have to use vacuum in order for the scans to cease and index use begin. Is it a
continualthing? Or does vacuum need to be done after a 'create index' in order for it to begin using the index? 

Thanks for your assistance, much apprecaited!


On Mon, Jul 21, 2003 at 11:00:56AM -0700, Stephan Szabo filled up my inbox with the following:
> On Mon, 21 Jul 2003, Philip Greer wrote:
>
> > dumps=# \d fal_profdel
> >                Table "fal_profdel"
> >  Attribute |           Type           | Modifier
> > -----------+--------------------------+----------
> >  sid       | character(4)             | not null
> >  card_num  | character(19)            | not null
> >  date_del  | timestamp with time zone |
> >  filename  | character varying(30)    |
> > Indices: fal_prfdel_cn,
> >          fal_prfdel_date,
> >          fal_prfdel_pk
> >
> > dumps=# \d fal_prfdel_cn
> >    Index "fal_prfdel_cn"
> >  Attribute |     Type
> > -----------+---------------
> >  card_num  | character(19)
> > unique btree
> >
> > dumps=# explain select card_num from fal_profdel where card_num = 'removed_for_privacy';
> > NOTICE:  QUERY PLAN:
> >
> > Seq Scan on fal_profdel  (cost=0.00..120546.39 rows=46649 width=12)
> >
> > EXPLAIN
> > ================================================================================
> >
> > Now, why the heck is the select query not using the index? I've tried
> > it by having an exact 19 character card_num as well - still explains
> > as a 'Seq Scan' (tablespace scan) - and each query takes up to 37
> > seconds (thus confirming that it is indeed doing scans and not using
> > the index).
>
> Have you vacuum analyzed the table recently? What does explain show if you
> do set enable_seqscan=off; before the explain and then how long does the
> query actually take to run with seqscan disabled.
>

--
-----------------------------------------------------------------------------
PG..                                                 philip@tildesoftware.com
Law of probable dispersal: Whatever it is that hits the fan will not be
evenly distributed.