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, MinLen 103, 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 continual thing? 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(at)tildesoftware(dot)com
Law of probable dispersal: Whatever it is that hits the fan will not be
evenly distributed.