Re: Automatic Indexes from Query Optimization?

On Wed, Sep 18, 2002 at 09:31:00AM -0400, Daryl Beattie wrote:
>     and I run, say, 10,000 ILIKE queries on Name, is the query optimizer
> smart enough to create an index on lower(Name) and change subsequent queries
> to use a lower() comparison instead of an ILIKE comparison? [This is
> probably a bad example because it is likely that a ILIKE b is not equivalent
> to lower(a) = lower(b) in every case. However, it does illustrate where
> automatic index creation coupled with query optimization would improve
> performance.]

No, postgresql never automatically create indexes in such cases. The only
indexes automatically created are for serial and primary key fields.

>     A further question I have is; if indexes are created by the DBA,
> will the query optimizer know to use them? For example, it would be a bad
> situation if there was a lower(Name) index, but the query optimizer
> optimized the lower() out of a query because it believed speed would be
> increased.

Yep, the optimiser is pretty good in determining when to use an index. As
for optimising lower() out of a query that'd be a bug, since that should
never happen.

>     I hope I am asking these questions in the right forum. I am having a
> bit of trouble figuring out where the line is drawn in terms of what
> optimization I need to do by hand, and what the database can do for itself.

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.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.