Re: Why does it not use the index?

Andrew Ayers <aayers(at)eldocomp(dot)com> writes:

> Doug McNaught wrote:
> > It's not the VACUUM that's necessary; it's the ANALYZE. The query
> > planner uses table statistics to make its decisions, and ANALYZE is
> > what collects those statistics. Without an ANALYZE the planner will
> > make default assumptions that are rarely correct. :)
>
> I am not the original poster, but I am a PG newbie, so:
>
> So - are you saying that if you have a table, and you create an index on
> that table, you need to perform an ANALYZE in order for PG to use the
> index. Otherwise, the index goes unused (or used improperly)?

Actually, the time to ANALYZE is after you make significant changes in
the table (bulk load a bunch of data, delete a bunch of rows etc). If
you create an index on a table with correct statistics, the planner
should start using it without necessarily needing ANALYZE to be run.

So run it by hand after making major data changes, and run it
periodically depending on how fast your data changes in regular use.

Clear?

-Doug