icon Join with us for Oracle DBA Training Live Session ENROLL NOW

PostGIS Performance: Indexing and EXPLAIN

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • 24 Oct, 2026
  • 0 Comments
  • 6 Mins Read

PostGIS Performance: Indexing and EXPLAIN

PostGIS Performance: Indexing and EXPLAIN

Spatial data has a way of humbling database administrators. A query that feels instant on a test table with a few hundred rows can grind to a halt when it hits production with millions of geometries. The difference between a spatial query that takes milliseconds and one that takes hours almost always comes down to two things: whether the right index exists, and whether the query planner is actually using it.

This guide breaks down the essentials of PostGIS performance. We will look at why standard indexes fail for geometry, how the GiST index and bounding boxes work together, and how to read EXPLAIN output to diagnose exactly why your spatial join is scanning the entire table.

Why Standard Indexes Fail for Spatial Data

If you come from a traditional relational background, you are used to B-Tree indexes. They are excellent for looking up a specific user ID, a date range, or an email address. However, B-Trees assume data is one-dimensional and sortable along a single axis.

Geometry is multidimensional. A polygon representing a building has an X and Y extent, and potentially a Z and M value. A B-Tree cannot efficiently answer the question: “Which buildings overlap this specific rectangular area?” It has no concept of spatial proximity or overlap in two-dimensional space.

This is where the GiST (Generalized Search Tree) index comes in. PostGIS uses GiST to build a hierarchical tree of bounding boxes (also called Minimum Bounding Rectangles, or MBRs). Instead of storing the complex geometry itself, the index stores the smallest rectangle that completely encloses the shape . When you query for intersecting geometries, PostgreSQL first scans this tree of simple rectangles, which is incredibly fast.

The Two-Step Execution Model

Understanding how PostGIS actually executes a spatial query is the key to writing fast SQL. It happens in two distinct phases:

Phase 1: The Broad Phase (Index Filter)

The database uses the index to find every bounding box that overlaps with your query area. It uses the && operator (bounding box overlap) for this. This phase is fast because comparing four coordinates (a box) is trivial compared to comparing hundreds of vertices.

Phase 2: The Narrow Phase (Precise Check)

Only for the rows that survived Phase 1 does PostGIS perform the expensive math. It calculates the exact intersection using functions like ST_Intersects or ST_Contains. Because the index already eliminated the vast majority of irrelevant rows, this step only runs on a tiny subset of the table.

The Practical Takeaway: Most modern PostGIS functions automatically invoke the index for Phase 1. However, if you write a query in a way that prevents the && operator from being used, you force a full table scan and lose the benefit of the index entirely.

Reading EXPLAIN: Finding the Bottleneck

You cannot fix what you cannot see. When a query is slow, the first step is always EXPLAIN ANALYZE. This command runs the query and shows you the execution plan, including how long each step took and how many rows were processed.

What to Look For

  • Seq Scan: This is the red flag. It means PostgreSQL is scanning every single row in the table. For a spatial table with millions of rows, this is almost always the cause of the slowdown .

  • Bitmap Index Scan or Index Scan: This is what you want to see. It indicates the GiST index is being used to filter rows.

  • Rows Removed by Filter: This tells you how many rows were discarded during the precise check. A high number here relative to the rows returned suggests the index filter (Phase 1) is doing its job, but the data is dense.

A Real-World Diagnosis

Imagine you are joining a table of buildings (5 million rows) to a table of pois (Points of Interest, 50,000 rows).

sql
EXPLAIN ANALYZE
SELECT b.building_id, p.poi_name
FROM buildings b
JOIN pois p ON ST_Intersects(b.geom, p.geom);

If the output shows Seq Scan on buildings, the database is comparing every building to every POI. This will likely take hours. The fix is ensuring a GiST index exists on buildings.geom and that the query planner chooses to use it.

Common Mistakes and Best Practices

Mistake 1: Missing the GiST Index

This is the most frequent culprit. Without an index, PostGIS is forced to calculate the exact geometry of every row. Creating one is straightforward:

sql
CREATE INDEX idx_buildings_geom ON buildings USING GIST (geom);

Always verify that the index exists on the geometry column you are filtering or joining on.

Mistake 2: Using Functions That Bypass the Index

Certain function calls in a WHERE clause can prevent the index from being used. The classic example is ST_Buffer:

sql
-- SLOW: Buffer is calculated for every row
SELECT * FROM zones
WHERE ST_Intersects(geom, ST_Buffer(ST_MakePoint(-0.12, 51.50), 100));

The planner may struggle to use the index because the right-hand side of the expression is not a simple geometry. A better approach is to use ST_DWithin, which is index-aware:

sql
-- FAST: Uses the index
SELECT * FROM zones
WHERE ST_DWithin(geom, ST_MakePoint(-0.12, 51.50), 100);

Mistake 3: Forgetting the Bounding Box Filter in Complex Joins

In a spatial join, explicitly adding the && operator can sometimes help the planner choose the index, especially in complex queries or when using ST_Contains / ST_Within .

sql
-- Explicitly using the index-friendly bounding box check
SELECT r.*
FROM routes r
JOIN communes c ON r.geom && c.geom AND ST_Intersects(r.geom, c.geom)
WHERE c.nom = 'Paris';

Mistake 4: Ignoring Geometry Complexity

A GiST index stores a bounding box. If your geometries are extremely complex (e.g., a coastline with 50,000 vertices), the precise Phase 2 check becomes expensive even after the index narrows down the candidates. In analytical queries where sub-meter precision is not required, consider using ST_Simplify on a copy of the data or using ST_Subdivide to break large polygons into smaller, index-friendly chunks.

When to Update Statistics

PostgreSQL relies on statistics to estimate how many rows will be returned. If your statistics are stale, the planner might incorrectly estimate that a sequential scan is cheaper than an index scan. After bulk loading or significantly modifying spatial data, always run:

sql
VACUUM ANALYZE your_table;

For spatial tables, you may also want to increase the statistics target for geometry columns, as the default sample size may not capture the complexity of spatial distributions:

sql
ALTER TABLE buildings ALTER COLUMN geom SET STATISTICS 1000;

Key Takeaways

  • Index First: Always create a GiST index on geometry columns used in WHERE or JOIN clauses.

  • Trust the Two-Step: PostGIS uses the index for a fast bounding-box filter, then does precise math on the survivors. Write queries that let the planner do this.

  • Read the Plan: EXPLAIN ANALYZE is non-negotiable. Look for Seq Scan as your primary warning sign.

  • Use Index-Friendly Functions: Prefer ST_DWithin over buffering, and consider adding explicit && operators in joins.

  • Maintain Statistics: Run VACUUM ANALYZE after large data changes to keep the planner informed.

Spatial performance is not magic. It is a predictable outcome of giving the database the right tools (indexes) and asking the right questions (queries that respect how those indexes work). Master these two levers, and your PostGIS queries will scale from thousands to millions of rows without breaking a sweat.

lets talk - learnomate helpdesk

Book a Free Demo

lets talk - learnomate helpdesk

Book a Free Demo