Re: sp-gist porting to postgreSQL

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.

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

> 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(at)sai(dot)msu(dot)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(at)sai(dot)msu(dot)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(at)sai(dot)msu(dot)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(at)sai(dot)msu(dot)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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83