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.