Re: Hypothetical Indexes

Tom,
> 
> This would be of some value if the optimizer's cost estimates were
> highly reliable, but unfortunately they are far from being so :-(
> Without the ability to measure *actual* as opposed to estimated
> costs, I'm not sure you can really do much.
> 
 In fact, any index selection tool that uses hypothetical indexes
will not recommend indexes that the optimizer does not recognize as
good enough for the query. The bright side of this is that the better
the optimizer gets, the better are the recommendations made by the
index selection tool. And people tend to invest resources in
constructing good query optimizers. Other index selection tools, such
as Microsoft's and IBM's, have the same limitation. Even though, the
tools are useful for people that have to deal with databases with a
big quantity of tables and queries. Finding useful indexes in this
kind of setting is a difficult problem for DBAs.
 So, our point is that hypothetical indexes just have to be as well
estimated by the optimizer as conventional, real indexes. An index not
suggested by the optimizer might still be usable, but that would
require rewriting the query or using hints, things that need the
intervention of a more skilled DBA anyway.
 Best regards,
   Marcos.


>                        regards, tom lane
>