SQL Academy · Lesson

Spatial Indexes (GiST)

Make location queries fast.

Spatial Indexes (GiST) is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Location Queries Get Slow

Imagine a table with millions of restaurant locations. If you ask "Find all restaurants within 5 km of me," the database must check every single row to compute the distance. This is called a sequential scan, and it becomes painfully slow as the table grows.

Spatial indexes solve this by organizing geometry data into a tree structure, letting the database skip large portions of the table instantly.

What Is a GiST Index?

GiST stands for Generalized Search Tree. It is a flexible index framework built into PostgreSQL that supports many data types, including geometric shapes and PostGIS geometry.

Unlike a B-tree index (which works on sortable values like integers or strings), GiST can index multi-dimensional data such as points, polygons, and lines. PostGIS uses GiST internally to build its spatial indexes.

Creating a Spatial Index

Creating a GiST index on a geometry column is straightforward. You use CREATE INDEX with the USING gist clause. This single statement can transform a query from taking minutes to taking milliseconds.

CREATE INDEX idx_restaurants_geom
  ON restaurants
  USING gist (geom);

How GiST Works: Bounding Boxes

A GiST spatial index does not store exact geometries. Instead, it stores bounding boxes — the smallest rectangle that encloses each geometry. The tree is built by grouping nearby bounding boxes together at each level.

When a query runs, PostgreSQL descends the tree, pruning branches whose bounding boxes do not overlap the search area. Only the surviving candidate rows are then checked precisely. This two-phase approach (index probe + recheck) is extremely efficient.

Setting Up a Sample Table

Before exploring index behavior, let us create a sample table of city points and populate it with a few rows. The geom column stores each city as a Point in WGS 84 (SRID 4326).

CREATE TABLE cities (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  geom GEOMETRY(Point, 4326)
);

INSERT INTO cities (name, geom) VALUES
  ('Paris',    ST_SetSRID(ST_MakePoint(2.3522,  48.8566), 4326)),
  ('Berlin',   ST_SetSRID(ST_MakePoint(13.4050, 52.5200), 4326)),
  ('Madrid',   ST_SetSRID(ST_MakePoint(-3.7038, 40.4168), 4326)),
  ('Rome',     ST_SetSRID(ST_MakePoint(12.4964, 41.9028), 4326)),
  ('Warsaw',   ST_SetSRID(ST_MakePoint(21.0122, 52.2297), 4326));

Adding the GiST Index

With the table populated, add a GiST index on the geom column. For production tables with millions of rows, this statement may take a few minutes but only needs to run once. After that, every spatial query against this column benefits automatically.

CREATE INDEX idx_cities_geom
  ON cities
  USING gist (geom);

-- Verify the index exists
SELECT indexname, indexdef
FROM   pg_indexes
WHERE  tablename = 'cities';

Bounding-Box Operator &&

PostGIS exposes the && operator to test whether two bounding boxes overlap. This operator is index-aware — the planner uses the GiST index automatically. It is much faster than computing exact geometry intersections and is often used as a quick pre-filter.

-- Find cities whose bounding box overlaps a search rectangle
SELECT name
FROM   cities
WHERE  geom && ST_MakeEnvelope(-5, 40, 15, 50, 4326);

Nearest-Neighbor Search with <->

The <-> operator returns the distance between two geometries and is also GiST-accelerated. Combining it with ORDER BY ... LIMIT gives you an extremely fast k-nearest-neighbor (KNN) query — no full table scan needed.

-- Find the 3 cities closest to a reference point (Brussels)
SELECT name,
       ST_Distance(
         geom::geography,
         ST_SetSRID(ST_MakePoint(4.3517, 50.8503), 4326)::geography
       ) / 1000 AS distance_km
FROM   cities
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(4.3517, 50.8503), 4326)
LIMIT  3;

Verifying Index Usage with EXPLAIN

Always use EXPLAIN or EXPLAIN ANALYZE to confirm that the planner is actually using your index. Look for Bitmap Index Scan or Index Scan using idx_cities_geom in the output. If you see Seq Scan instead, the table may be too small for the planner to prefer the index.

EXPLAIN
SELECT name
FROM   cities
WHERE  geom && ST_MakeEnvelope(-5, 40, 15, 50, 4326);

Concurrent Index Creation

Building a large spatial index with the standard CREATE INDEX command locks the table for writes. In production, use CREATE INDEX CONCURRENTLY to build the index without blocking inserts or updates. The trade-off is that it takes longer and cannot be run inside a transaction block.

-- Safe for production tables (no write lock)
CREATE INDEX CONCURRENTLY idx_restaurants_geom
  ON restaurants
  USING gist (geom);

Maintaining Your Spatial Index

Over time, heavy inserts, updates, and deletes can cause index bloat — the index grows fragmented and less efficient. Use REINDEX to rebuild it cleanly, or schedule periodic VACUUM ANALYZE to update statistics so the query planner makes better decisions.

-- Rebuild the index to remove bloat
REINDEX INDEX idx_cities_geom;

-- Update planner statistics for the table
ANALYZE cities;

Quick Check: GiST Indexes

Test your understanding of spatial indexes with GiST in PostGIS.

Recap: Spatial Indexes with GiST

In this lesson you learned why spatial indexes are essential for performant location queries and how GiST makes them possible in PostgreSQL and PostGIS.

Key takeaways:

GiST (Generalized Search Tree) is a flexible index type that supports multi-dimensional geometry data.
Create a spatial index with CREATE INDEX ... USING gist (geom).
GiST stores bounding boxes and prunes the search tree, avoiding full table scans.
The && operator (bounding-box overlap) and <-> operator (distance/KNN) are both GiST-accelerated.
Use EXPLAIN to verify index usage and CREATE INDEX CONCURRENTLY in production to avoid write locks.
Maintain indexes with REINDEX and ANALYZE to keep queries fast over time.

Frequently asked questions

Is the “Spatial Indexes (GiST)” lesson free?

Yes — the full text of “Spatial Indexes (GiST)” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Spatial Indexes (GiST)”?

Make location queries fast. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Spatial Indexes (GiST)” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy