Re: sp-gist porting to postgreSQL - Mailing list pgsql-hackers

On Wed, 10 Nov 2004, Oleg Bartunov wrote:

> On Wed, 10 Nov 2004, Ramy M. Hassan wrote:
>
>> I believe that it is still possible to have several index access methods 
>> for
>> the same type and the same operations. But this requires that each index
>> access method has its own tuple in the pg_am relation and therefore
>> postgresql recognizes it by itself. But this is not the case with GiST 
>> based
>> indices. They are all recognized by postgresql as same index access method,
>> and from here comes the limitation.
>
> It's possible, see contrib/intarray, for example. You can specify
> opclass in CREATE INDEX command:
>
> CREATE INDEX text_idx  on test__int using gist ( a gist__int_ops );
> CREATE INDEX text_idx2 on test__int using gist ( a gist__intbig_ops );
>
> Here  gist__int_ops and gist__intbig_ops  are different opclasses for the
> same type and intended to use with different cardinality. The problem is how 
> to use them (indices) automatically, how planner/optimizer could
> select which indices to use.

This is great. I didn't know that. Thanks.

>
>> 
>> Also, I think GiST and SP-GiST are better viewed as index classes not as
>> indices by themselves. So may be it is more logical to say:
>> CREATE INDEX index_name ON table_name USING spgist_trie(field)
>> Where spgist_trie is an spgist based index instance.
>> 
>> Than to say:
>> CREATE INDEX index_name ON table_name USING spgist(field)
>> And depend on the operator classes to define the required support methods
>> for the trie function.
>> 
>> 
>
> why not use existed syntax  ?
> CREATE INDEX index_name ON table_name USING spgist (fiels trie_ops)
>

That's ok now.
The only concern now is the portability of the extensions. Currently Are there any 
plans to introduce GiST to some other DBMS ? If yes, then I think all GiST 
based indexes will have to be rewritten or atleast modified to a great 
extent, as they depend on postgresql API and how index access methods work in postgresql.
Do you see any value in defining an SP-GiST API for the extensions 
to completely isolate the extensions code from postgresql ?
Such isolation will require that SP-GiST code loads the extensions instead 
of relying on postgresql to do that so it will no longer be a matter of 
operator classes that defines extension.


>
>> I am not sure I have a complete vision, but this is what I see. I would
>> appreciate your opinions regarding to this design issue.
>> 
>
> Teodor is rather busy right now, but he certainly knows better GiST 
> internals,
> so we'll wait his comments.
>
>> Thanks
>> Ramy
>> 
>> 
>> 
>> 
>> -----Original Message-----
>> From: Oleg Bartunov [mailto:oleg@sai.msu.su]
>> Sent: Wednesday, November 10, 2004 5:21 AM
>> To: Ramy M. Hassan
>> Cc: 'Pgsql Hackers'; 'Teodor Sigaev'; 'Walid G. Aref'
>> Subject: RE: sp-gist porting to postgreSQL
>> 
>> On Wed, 10 Nov 2004, Ramy M. Hassan wrote:
>> 
>>> Oleg,
>>> 
>>> Thanks for your prompt reply.
>>> Actually, I am able to create a new access method for testing and add an
>>> operator class for the type "integer" using the new access method. Then
>>> created a table with two integer fields, one indexed using the new access
>>> method and the other using a btree index, and everything is ok so far.
>> Even
>>> using EXPLAIN statement for queries show that the indexes are used
>> correctly
>>> as they should.
>>> I am using postgresql version 8.0.0beta3 from CVS.
>> 
>> I was wrong, Ramy. You could have several indices for the same type as soon
>> as they support different operations. I don't know if it's possible
>> to have them for the same operation but for different conditions.
>> 
>>> 
>>> Thanks
>>> Ramy
>>> 
>>> 
>>> 
>>> -----Original Message-----
>>> From: Oleg Bartunov [mailto:oleg@sai.msu.su]
>>> Sent: Wednesday, November 10, 2004 12:35 AM
>>> To: Ramy M. Hassan; Pgsql Hackers
>>> Cc: Teodor Sigaev; Walid G. Aref
>>> Subject: Re: sp-gist porting to postgreSQL
>>> 
>>> Ramy,
>>> 
>>> glad to hear from you !
>>> AFAIK, posgresql doesnt' supports several indices for the same type.
>>> I think this is a problem of optimizer. Probably other hackers know
>>> better. I forward your message to -hackers mailing list which is a
>>> relevant place for GiST discussion.
>>> 
>>> regards,
>>> Oleg
>>> 
>>> 
>>> On Tue, 9 Nov 2004, Ramy M. Hassan wrote:
>>> 
>>>> Dear Oleg and Teodor,
>>>> Thanks for offering help.
>>>> I have a design question for now.
>>>> Currently in the postgresql GiST implementation, I noticed that the way
>> to
>>> 
>>>> have a GiST based index is to define an operator class for a certain type
>>>> using GiST index. There is no new index type defined from the point of
>>> view
>>>> of postgresql ( nothing is added to pg_am ). This means that for a
>> certain
>>> 
>>>> type there could only be one GiST based index. I mean that there is no
>> way
>>> in
>>>> the same server to use gist to implement an xtree index and a ytree  for
>>> the
>>>> same  type even if they index different fields in different relations. is
>>>> that correct ?
>>>> What about doing it the other way ( I am talking about SP-GiST now ) , by
>>>> providing the extension writer with an API to use it to instantiate a
>>>> standalone SP-GiST based index ( for example trie index ) that has a
>>> record
>>>> in the pg_am relation. In my point of view this would give more
>>> flexibility,
>>>> and also would not require the extension writer to learn the postgresql
>>> API (
>>>> maybe oneday SP-GiST will be ported to another database engine )  he will
>>>> just need to learn the SP-GiST API which will propably be less amount of
>>>> study  (and this is what GiST and SP-GiST is all about if I correctly
>>>> understand ).
>>>> Please let me know your opinions regarding to this.
>>>> 
>>>> Thanks
>>>> 
>>>> Ramy
>>>> 
>>> 
>>> Regards,
>>>         Oleg
>>> _____________________________________________________________
>>> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
>>> Sternberg Astronomical Institute, Moscow University (Russia)
>>> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
>>> phone: +007(095)939-16-83, +007(095)939-23-83
>>> 
>> 
>> Regards,
>>         Oleg
>> _____________________________________________________________
>> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
>> Sternberg Astronomical Institute, Moscow University (Russia)
>> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
>> phone: +007(095)939-16-83, +007(095)939-23-83
>> 
>> 
>> ---------------------------(end of broadcast)---------------------------
>> TIP 8: explain analyze is your friend
>> 
>
> Regards,
>         Oleg
> _____________________________________________________________
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
>
>