Re: Fix for Index Advisor related hooks

On 17.02.2011 14:30, Gurjeet Singh wrote:
> On Wed, Feb 16, 2011 at 6:37 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Gurjeet Singh<singh(dot)gurjeet(at)gmail(dot)com> writes:
>>> On Wed, Feb 16, 2011 at 10:25 AM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>> The only reason you'd need that code is if you were trying to construct
>>>> a fake Relation structure, which seems unnecessary and undesirable.
>>
>>> The planner requires IndexOptInfo, and for the planner to choose the
>>> hypothetical index we need to fill in the fwdsortop, revsortop, opfamily
>> and
>>> opcintype, and this is the information that IndexAdvisor populates using
>>> IndexSupportInitialize() (at least until c0b5fac7 changed the function
>>> signature.
>>
>> Yeah, and the set of stuff you need in IndexOptInfo changed again last
>> week; see collations. Direct access to IndexSupportInitialize is even
>> less useful today than it was a week ago. This stuff has changed many
>> times before, and it will change again in the future, and exporting a
>> private function that has an unrelated purpose is not going to insulate
>> you from needing to deal with that.
>>
>
> I guess you are right.
>
>
>>
>>> What would be the best way to build an IndexOptInfo for a plain BTREE
>> index
>>> for different data types?
>>
>> Fetch the values you need and stuff 'em in the struct. Don't expect
>> relcache to do it for you. The only reason relcache is involved in the
>> current workflow is that we try to cache the information across queries
>> in order to save on planner startup time ... but I don't think that that
>> concern is nearly as pressing for something like Index Advisor. You'll
>> have enough to do tracking changes in IndexOptInfo, you don't need to
>> have to deal with refactorings inside relcache as well.
>>
>
> I also wish to make Index Advisor faster by not having to lookup this info
> every time a new query comes in and that's why I was trying to use the guts
> of IndexSupportInitialize() where it does the caching.

I doubt performance matters much here. It's not like you're going to be
explaining hundreds of queries per second with a hypotethical index
installed. I think of this as a manual tool that you run from a GUI when
you wonder if an index on column X would help.

The question is, can the Index Advisor easilt access all the information
it needs to build the IndexOptInfo? If not, what's missing?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com