Ralf Mattes <rm@mh-freiburg.de> writes:
> Still, the remarkl about running 'vacuum' after the creation
> of an index seems valid. I was bitten by this just last week--
> somehow it seems counterintuitive to have to vacuum a table only
> to tell the system that an index exists. This should be the job
> of 'create index' or am i wrong?
The system knows perfectly well that the index exists. The issue
is whether the planner will conclude that the index is worth using
for a particular query, in the absence of complete statistical
information. If you've never done a 'vacuum analyze' on the table
then the planner is flying blind about what to do (and no, it does
not matter whether the index exists at the time the vacuum is done).
There are some subtle interactions between the default estimates that
are made for various parameters. The current behavior clearly needs
work, but I'm hesitant to "fix" it by just lowering the default
selectivity estimate (or some such) without careful study.
I'm hoping to have some time to spend on that issue for 7.2 ...
regards, tom lane
