Обсуждение: Automatic Indexes from Query Optimization?

Dear PostgreSQL people,

    I did some browsing through the docs, and I still haven't found a
simple yes/no answer to these questions:
    Are indexes created automatically by the query optimizer? Or does
the query optimizer only optimize lone queries? I assume it only optimizes
queries, and does not optimize the database depending on how it is used.
Perhaps what I am thinking of is not a query optimizer but more like a
relation-optimizer.
    Does the query optimizer remember optimizations it does for queries
that are run over and over with different parameters?
    A concrete example would be if I created a table like this:

CREATE TABLE Food (
    Id    SERIAL PRIMARY KEY,
    Name    VARCHAR(10)
);

    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.]
    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.
    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.

Sincerely,

    Daryl.

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.

Martijn van Oosterhout <kleptog@svana.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@samurai.com> || PGP Key ID: DB3C29FC