Re: Fix for Index Advisor related hooks

On Fri, Feb 18, 2011 at 2:27 AM, Heikki Linnakangas <
heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:

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

There's a command line tool in the Index Adviser contrib that takes a file
full of SQL and run them against the Index Adviser. People would want that
tool to be as fast as it can be.

Another use case of the Index Advisor is to be switched on for a few hours
while the application runs, and gather the recommendations for the whole
run. We'll need good performance that case too.

I'll try to figure out what all info we need for IndexOptInfo, it'll take
some time though.

Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh(dot)gurjeet(at){ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device