Re: More Performance

"Matthias Urlichs" <smurf(at)noris(dot)net> writes:
> I've found another one of these performance problems in the benchmark,
> related to another ignored index.
> The whole thing works perfectly after a VACUUM ANALYZE on the
> table.
> IMHO this is somewhat non-optimal. In the absence of information
> to the contrary, PostgreSQL should default to using an index if
> it might be appropriate, not ignore it.

Just FYI: Postgres absolutely does not "ignore" an index in the absence
of VACUUM ANALYZE stats. However, the default assumptions about
selectivity stats create cost estimates that are not too far different
for index and sequential scans. On a never-vacuumed table you will
get an indexscan for "WHERE col = foo". If the table has been vacuumed
but never vacuum analyzed, it turns out that you get varying results
depending on the size of the table and the average tuple size (since the
planner now has non-default info about the table size, but still nothing
about the actual selectivity of the WHERE condition).

The cost estimation code is under active development, and if you check
the pgsql list archives you will find lively discussions about its
deficiencies ;-). But I'm having a hard time mustering much concern
about whether it behaves optimally in the vacuum-but-no-vacuum-analyze
case.

regards, tom lane