GiST index for geometric and text in PostgreSQL

Introduction

A GiST index helps speed up searches on complex data like shapes or text by organizing data smartly.

When you want to quickly find points or shapes inside a map area.

When searching for similar words or text patterns fast.

When working with geometric data like circles, polygons, or lines.

When you need fast full-text search in a large text column.

When normal indexes are too slow for your special data types.

Syntax

PostgreSQL

CREATE INDEX index_name ON table_name USING gist (column_name);

GiST stands for Generalized Search Tree, a flexible index type.

You can use GiST for geometric types like points, polygons, and for text search types.

Examples

This creates a GiST index on the 'location' column which stores geometric points.

PostgreSQL

CREATE INDEX location_gist_idx ON places USING gist (location);

This creates a GiST index for fast full-text search on the 'content' column.

PostgreSQL

CREATE INDEX textsearch_gist_idx ON documents USING gist (to_tsvector('english', content));

Sample Program

This example creates a table with polygon areas, adds a GiST index on the area column, and queries parks overlapping a specific polygon.

PostgreSQL

CREATE TABLE parks (id SERIAL PRIMARY KEY, name TEXT, area POLYGON);

INSERT INTO parks (name, area) VALUES
('Central Park', '((0,0),(0,10),(10,10),(10,0),(0,0))'),
('Riverside Park', '((5,5),(5,15),(15,15),(15,5),(5,5))');

CREATE INDEX parks_area_gist_idx ON parks USING gist (area);

-- Find parks overlapping a given area
SELECT name FROM parks WHERE area && '((7,7),(7,12),(12,12),(12,7),(7,7))'::polygon;

Important Notes

GiST indexes are great for range and similarity searches but may be slower to update than regular indexes.

Use GiST indexes when your queries involve geometric operators like overlaps (&&) or full-text search functions.

Summary

GiST indexes speed up searches on complex data like shapes and text.

They work well with geometric types and full-text search.

Create them using CREATE INDEX ... USING gist (column).