Re: Why does it not use the index?

On Mon, 21 Jul 2003, Philip Greer wrote:

> 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?

It was the analyze that was important for this probably. If you compare
the explain above with the one from before, you'll notice that before it
was estimating that around 46000 rows were going to be returned. If that
were true (and there weren't clustering effects going on) it's possible
that the sequence scan would actually have been faster than scanning the
index. Analyze gave it hopefully more reasonable data for the estimate
and so it's now guessing that 1 row is returned which is certainly better
for the index scan.

> 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?

Well, if you do updates/deletes, vacuum is necessary to reclaim space, so
you should probably do it on some scheduled basis for that purpose. A
good reason to upgrade is that in 7.1 vacuum gets an exclusive lock
whereas in recent versions it doesn't by default and you can analyze
without a vacuum. Analyze is necessary to keep the statistics up to date
and should also be run on a periodic basis (daily isn't bad).