Re: Index Tuning Features

> On 10/10/06, Mark Woodward <pgsql(at)mohawksoft(dot)com> wrote:
>> I think the idea of "virtual indexes" is pretty interesting, but
>> ultimately a lesser solution to a more fundimental issue, and that would
>> be "hands on" control over the planner. Estimating the effect of an
>> index
>> on a query "prior" to creating the index is a great idea, how that is
>> done
>> is something different than building concensus that it should be done.
>>
>> Another thing that this brings up is "hints" to a query. Over the years,
>> I
>> have run into situation where the planner wasn't great. It would be
>> nice
>> to try forcing different strategies on the planner and see if
>> performance
>> caan be improved.
>>
>
> you can do this by setting enable_"access_method" type parameters.

Here's your hammer, all your problems are now nails.

The enable_xxx setting are OK for simple queries gone wrong, but if you
have a more complex query, any one of those settins may help or hinder
different parts of a query, then you would be left with choosing which of
them helps more than hurts the over-all query.

being able to alter the query plan would help in areas where there are
data patterns in a database that the ANALYZE command can't pick up because
it is not designed too.

Imagine you have a street map database ordered by zip, street, number. The
primary order is zipcode, the secondary order is street. There is a
relationship of number to street, and zip to street. The analyzer, at
least the last time I checked, does not recognize these relationships. So,
a search by street and number would probably use a sequential scan rather
than the street index.