How to create a GIST index

Products

• VMware Tanzu Greenplum

Issue/Introduction

From postgresql document, a gist index is defined as below: 

GiST indexes are not a single kind of index, but rather an infrastructure within which many different indexing strategies can be implemented. 

Environment

Resolution

Below are steps to create a sample gist index:

​1. Download the PostGIS extension from pivotal network.
https://network.pivotal.io/products/pivotal-gpdb#/releases/158026/file_groups/1084
​
2. Install this extension.
gppkg -i postgis-2.1.5+pivotal.1-gp5-rhel6-x86_64.gppkg
​
3. Enable the PostGIS extension.
cd $GPHOME/share/postgresql/contrib/postgis-2.1/
./postgis_manager.sh "mydatabase" install (need to add the correct dbname)
​
4. psql "dbname" 
   create table gis_idx_test(id int, info text, pos geometry)
​
5. create index idx_gis_idx_test on gis_idx_test using gist (pos);

If you want to use a GIST index on more data types, please check the documents in the Additional Information section.

Additional Information

http://gpdb.docs.pivotal.io/590/ref_guide/sql_commands/CREATE_OPERATOR_CLASS.html
http://gpdb.docs.pivotal.io/590/ref_guide/extensions/postGIS.html
https://www.postgresql.org/docs/8.3/static/indexes-types.html