Thread: 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
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.
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
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
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. 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. 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. 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
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@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
Oleg Bartunov <oleg@sai.msu.su> writes:
> AFAIK, posgresql doesnt' supports several indices for the same type.
I think what's really being asked for is several operator classes for
the same type. This most certainly *is* possible.
regards, tom lane
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 > >