Thread: GiST index implementation
Hi list,
what is the default implementation for GiST index? B-Tree or R-Tree?
That is, if i execute the following SQL command:
CREATE index ON table USING Gist (column)
what is the type of the index that is actually built?
If I specify R-Tree instead, with:
CREATE index ON table USING Rtree (column)
I got this message:
NOTICE: substituting access method "gist" for obsolete method "rtree"
and when I look at the SQL code that created the index in pgAdmin, I found again
CREATE index ON table USING Gist (column).
How can I specify in SQL one of the two implementations provided (e.g. R-Tree)?
Thank you for helping.
Regards,
-Elena
what is the default implementation for GiST index? B-Tree or R-Tree?
That is, if i execute the following SQL command:
CREATE index ON table USING Gist (column)
what is the type of the index that is actually built?
If I specify R-Tree instead, with:
CREATE index ON table USING Rtree (column)
I got this message:
NOTICE: substituting access method "gist" for obsolete method "rtree"
and when I look at the SQL code that created the index in pgAdmin, I found again
CREATE index ON table USING Gist (column).
How can I specify in SQL one of the two implementations provided (e.g. R-Tree)?
Thank you for helping.
Regards,
-Elena
"Elena Camossi" <elena.camossi@gmail.com> writes: > Hi list, > > what is the default implementation for GiST index? B-Tree or R-Tree? > That is, if i execute the following SQL command: > > CREATE index ON table USING Gist (column) > > what is the type of the index that is actually built? uhm, GIST. GIST is a particular type of index just like btree. What are you actually trying to do? Do you have a particular problem you're trying to solve? > How can I specify in SQL one of the two implementations provided (e.g. > R-Tree)? R-Tree indexes don't exist in Postgres any more. GIST indexes are very similar only more general. They handle 2D geometric data types like RTree did previously as well as n-dimensional data types and other more exotic things like intarrays and full text search. There are different "kinds" of GIST indexes which you can specify by specifying an operator class with you define the index. But most data types only have a single operator class available to them so that's probably not what you need unless you're trying to do something unusual. An "operator class" defines a set of operators which can be optimized by an index organized the same way and usually correspond to a particular interpretation of the data type. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Hi Gregory,
thank you very much for you answer!
according to the documentation (ch 11.2) "GiST indexes are not a single kind of index, but rather an infrastructure within wich many different indexing strategies can be implemented", and (ch 50.1) "B-Tree, R-Tree and many other indexing schemes can be implemented in GiST".
Moreover, according to what i found on the web (see http://www.sai.msu.su/~megera/postgres/gist/ ) some of such implementations are now included in the core of Postgres.
I supposed there was a way to specify one among the implementation provided.
I wanted to test the suitability and the efficiency of R-Tree/GiST for query involving standard PostgreSQL temporal column data.
Are these functionalities all included by default in the standard GiST indexing?
Is the specification of the operator class done when I create the index?
Or is the suitable operator class automatically loaded according to the data type of the column on which I build the index?
And if I would specify a different operator class, how can i do it?
May I provide a different operator class beyond the ones provided?
Thank you very much.
Regards,
-Elena
thank you very much for you answer!
>
> what is the default implementation for GiST index? B-Tree or R-Tree?
> That is, if i execute the following SQL command:
>
> CREATE index ON table USING Gist (column)
>
> what is the type of the index that is actually built?
uhm, GIST. GIST is a particular type of index just like btree.
according to the documentation (ch 11.2) "GiST indexes are not a single kind of index, but rather an infrastructure within wich many different indexing strategies can be implemented", and (ch 50.1) "B-Tree, R-Tree and many other indexing schemes can be implemented in GiST".
Moreover, according to what i found on the web (see http://www.sai.msu.su/~megera/postgres/gist/ ) some of such implementations are now included in the core of Postgres.
I supposed there was a way to specify one among the implementation provided.
What are you actually trying to do? Do you have a particular problem you're
trying to solve?
I wanted to test the suitability and the efficiency of R-Tree/GiST for query involving standard PostgreSQL temporal column data.
> How can I specify in SQL one of the two implementations provided (e.g.
> R-Tree)?
R-Tree indexes don't exist in Postgres any more. GIST indexes are very similar
only more general. They handle 2D geometric data types like RTree did
previously as well as n-dimensional data types and other more exotic things
like intarrays and full text search.
Are these functionalities all included by default in the standard GiST indexing?
There are different "kinds" of GIST indexes which you can specify by
specifying an operator class with you define the index.
But most data types
only have a single operator class available to them so that's probably not
what you need unless you're trying to do something unusual.
An "operator class" defines a set of operators which can be optimized by an
index organized the same way and usually correspond to a particular
interpretation of the data type.
Is the specification of the operator class done when I create the index?
Or is the suitable operator class automatically loaded according to the data type of the column on which I build the index?
And if I would specify a different operator class, how can i do it?
May I provide a different operator class beyond the ones provided?
Thank you very much.
Regards,
-Elena
"Elena Camossi" <elena.camossi@gmail.com> writes: > Hi Gregory, > > thank you very much for you answer! > >> >> > what is the default implementation for GiST index? B-Tree or R-Tree? >> > That is, if i execute the following SQL command: >> > >> > CREATE index ON table USING Gist (column) >> > >> > what is the type of the index that is actually built? >> >> uhm, GIST. GIST is a particular type of index just like btree. > > > according to the documentation (ch 11.2) "GiST indexes are not a single kind > of index, but rather an infrastructure within wich many different indexing > strategies can be implemented", and (ch 50.1) "B-Tree, R-Tree and many other > indexing schemes can be implemented in GiST". Hm, well that's kind of true from an abstract point of view. But from an practical point of view it's not really relevant. What you get when you use GIST indexing Postgres calls a GIST index regardless of what operator class you use. Most datatypes only have one GIST operator class. The geometric data types have a class that is most similar to the old RTree implementation. There is a GIST operator class for integers which implements an ordered btree style index -- but you probably would just use a Postgres BTree index if you wanted such an index. > I wanted to test the suitability and the efficiency of R-Tree/GiST for > query involving standard PostgreSQL temporal column data. ... > Are these functionalities all included by default in the standard GiST > indexing? The only GIST operator classes which come with the Postgres core are box_ops, poly_ops, and circle_ops which are the equivalents to the old RTree operator classes. There are a bunch more in the contrib modules. But I don't see any related to temporal data. You might want to look at the seg module and see if it can be altered to work with temporal data instead. You might also look on Pgfoundry, there might be something there. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com