Re: Automatic Indexes from Query Optimization?

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> No, postgresql never automatically create indexes in such cases. The
> only indexes automatically created are for serial and primary key
> fields.

Indexes are also created automatically for unique constraints; in 7.3,
serial columns are not unique by default, so they don't have an index
created automatically on them.

> Yep, the optimiser is pretty good in determining when to use an
> index.

Yeah, you just need to ensure that you run ANALYZE on some kind of
periodic basis, and steer clear of some well-known optimizer bugs
(e.g. integer literals and int2/int8 columns, IN vs. EXISTS, etc.).

> EXPLAIN [ANALYZE] is an excellent tool to determining where to
> optimise. I occasionally go through the server logs and look at any
> query that is either common or long and work out whether the query
> needs to be rewritten or I need to tweak the indexes.

show_statement_stats in 7.3 is also useful for this.

Some other databases (e.g. MS SQL, so I've heard) have tools to
assist DBAs in deciding when an index is appropriate. I personally
don't see a lot of value in a tool like that, but if someone would
find it useful, it might be an interesting tool to develop...

Cheers,

Neil

--
Neil Conway <neilc(at)samurai(dot)com> || PGP Key ID: DB3C29FC