Thread: 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