Re: Fix for Index Advisor related hooks

On 18.02.2011 17:02, Gurjeet Singh wrote:
> 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.

Nah, I don't buy that, sounds like a premature optimization. Just
planning a bunch of SQL statements, even if there's hundreds of them in
the file, shouldn't take that long. And even if it does, I don't believe
without evidence that the catalog lookups for the hypothetical indexes
is where the time is spent.

> 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.

How exactly does that work? I would imagine that you log all the
different SQL statements and how often they're run during that period.
Similar to pgFouine, for example. And only then you run the index
advisor on the collected SQL statements.

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