Re: Why does it not use the index?

On Monday 21 July 2003 19:51, 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;
[snip]
> 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)
[snip]
> WTF? Why would a vacuum be necessary in order for it to start using the
> index?

It's not the vacuum - it's the analyse. That builds up statistics on the table
in question so the planner knows how many rows there are, what the most
common values are etc. That way it can make a "best guess" as to whether
scanning the whole table or using the index will be faster.

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

You should vacuum to reclaim "deleted" space. You should analyse to update
statistics on the table. They both tend to depend on the amount of activity
you have.

--
Richard Huxton
Archonet Ltd