Re: [Fwd: Index Advisor]

Hi Gurjeet,
I include pgsql-hackers in this discussion ...

Am 01.11.2006 um 17:38 schrieb Gurjeet Singh:

> Hi Kai,
>
>     I am working with Simon at EnterpriseDB, and am currently  
> working on porting
> your patch to 8.2 sources. I have done a quick hack to make it work  
> on 8.2;
> please find the modified patch attached (remember it's a quick-n- 
> dirty hack; it
> still needs a cleanup).
>
>     The only changes (as yet) that I have done are:
>       (1) Changed the code according to the change in the linked- 
> list (List*)
> handling across 7.4 and 8.2.
>       (2) Added support for BitmapAnd, BitmapOr, BitmapHeapScan and
> BitmapIndexScan plan-nodes in scan_plan().
>
>     The outstanding issues, as of now, as I see them, are:
>         (1) There are left-over dependencies in pg_depends, that  
> stops the
> table-being-analyzed from getting dropped (probably it'll affect many
> other DDLs, I haven't tested though). I am investigating this.
>
>         (2) The intermediate indexes that are created are 'real'  
> indexes, in the
> sense that they are actiually created on the disk before
> planning/analyzing and are then dropped. I assume this since, you are
> calling index_create() which in turn calls index_build(), which in  
> turn
> calls the index's Access-method's build method, which, I assume, will
> create the index on disk. Please point out if this is not the case.
You are right - at least an empty index is created. I'm not sure if  
the index appears on disk, but this was the easiest way in 7.4 to do  
this. However, I agree - it has performance drawbacks and raises  
concurreny issues.
>
>         This, real-index build, can be counter productive where the  
> underlying
> table is huge. We were thinking of creating actual 'virtual'  
> indexes, by
> utilizing the 'skip_build' parameter of index_create() (introduced in
> index.c:1.229). But that would entail calculating the number-of- 
> pages, average
> record-size, and probably some more stats, on our own. And then  
> putting these
> values in the catalog.
Actually, we did exactly this - if you have only an empty index then  
you have to estimate this values.

>
>     I see that you have declred following two functions in
> src/include/catalog/index.h:
>
> extern int2 get_attrType(Oid attrTypId, int2 attrSize, int4  
> attrTypMod);
>
> extern int4 estimate_indexPages(Relation heapRelation,
>                 IndexInfo *indexInfo);
>
>     Looking at these, I suppose that you also worked on some such  
> calculations.
> If still with you, can you share this code with us?
It should be part of the patch - but let me check this.

>
>     (3) (If you've lost track, this is the third in the list of  
> outstanding
> issues :). I am concerned about the visibility of the virtual  
> indexes. If
> these indexes are immediately visible to other sessions, then there  
> is a
> strong possibilty that other backends that are in the planning  
> stage of a
> non-explain query, will pickup these indexes and develop their plan  
> and send
> for execution. And you know what hell will break loose if that  
> happens;
> there won't be any data in these indexes!!
Right - that's what I meant above by concurrency issues. Honestly, we  
had not enough knowledge about pgsql at this time to do this. I  
suppose the right way would be to add a session or transaction id to  
the virtual index and let the planner use only virtual indexes from  
the same session as the query.

>
>     One more thing, we are looking at ways to make it easier for  
> others too, to
> develop their own advisors. So we are looking at the possibility of  
> making
> it plugin based arch, similar to how edb-debugger for pl/pgsql is  
> being
> developed. This will allow others to develop and use their own  
> advisors for
> various Select/DML statements, in the least invasive way.
That's a great idea - it could be helpful for other kind of virtual  
objects too, e.g. materialized views, partitions etc. There are  
several exits for plugins: the set off indexes which should be  
created virtually, the profit assignment to the individual indexes as  
well as the way the recommendation is used. For example, we have the  
prototype of an online advisor which collects the recommendations  
continuously and tries to adapt the current set of real indexes (at  
least as an alerter for the DBA).

>
>     Lastly, and most importantly, can we move this discussion to  
> pgsql-hackers?
Done.

So, let me know if there is anything that I can do.

>
> Best regards,
> -- gurjeet@EnterpriseDB.com singh.gurjeet@{ gmail | hotmail |  
> yahoo }.com
>
Best,   Kai