Kartoza - Under the Hood: Lightning-Fast Spatial Queries with PostGIS

Preparing for Scale Before It Hurts

While working on a recent client project involving a PostgreSQL and PostGIS database, I was working with just a few hundred spatial records. Everything ran relatively smoothly, but a slight lag during query execution got me thinking: If an unoptimised query takes noticeable time on a dataset this small, what happens when the project scales to tens of thousands or millions of features?

In spatial databases, performance problems rarely scale linearly. An unoptimised query that takes a fraction of a second on a few hundred points can easily take hours or freeze your system entirely at production scale. Imagine you eventually scale up to 5 million building footprints and 50,000 points of interest (POIs) in your PostGIS database. You want to find out which buildings contain a POI, so you write a standard spatial join:

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

Without proper indexing, you hit execute, go make a cup of tea, come back, and the query is still running.

PostGIS transforms PostgreSQL into a world-class geospatial database, but out of the box, spatial operations like ST_Intersects or ST_Contains are computationally heavy. If you force the database to compare the exact vertices of complex polygons line-by-line across every record, your system will grind to a halt as your data grows.

Here is how you can optimise your database to handle scaling smoothly and return results in milliseconds.

1. Why Standard Indexes Fail: The GiST Solution

In standard relational databases, the B-Tree index is the gold standard. It is perfect for numbers, text, and dates. However, spatial data is multi-dimensional. A B-Tree cannot understand that a polygon in London is geographically next to a polygon in Kent.

To solve this, PostGIS uses a GiST (Generalised Search Tree) index. A GiST index does not store the full, complex geometry of your features. Instead, it draws a simplified rectangular bounding box (or Minimum Bounding Rectangle) around every single geometry and stores those simple four-coordinate boxes in a hierarchical, balanced tree.

To build a GiST index on your geometry columns, you must explicitly declare it:

CREATE INDEX idx_buildings_geom ON buildings USING GIST (geom);
CREATE INDEX idx_pois_geom ON pois USING GIST (geom);

(Pro Tip: If you are spinning up a fresh database to test this, using the official kartoza/postgis Docker image comes with many of these spatial optimisations ready to go.)

2. The Two-Step Query Execution Model

Once your GiST index is in place, PostGIS uses a brilliant two-step technique to speed up spatial queries:

1. Phase 1: The Broad Phase (Index Filter). The database completely ignores your complex polygon shapes. Instead, it quickly scans the GiST index using the bounding-box overlap operator (&&) to collect every row whose bounding box overlaps the query area. This is incredibly fast and instantly discards the vast majority of irrelevant rows.
2. Phase 2: The Narrow Phase (Precise Geometry Check). Only for the tiny percentage of records where the bounding boxes overlap, the database performs the heavy mathematical vertex comparison using your chosen function (like ST_Intersects or ST_Contains).

Most modern PostGIS functions (like ST_Intersects and ST_DWithin) automatically wrap this two-step process. However, if you ever write custom dynamic geometries or forget to build your index, you might accidentally bypass Phase 1 and trigger a catastrophic full table scan when your dataset grows.

3. Nearest Neighbour Searches: Avoid ST_Distance

A classic mistake when searching for the closest feature (e.g., "Find the 5 closest hospitals to this crash site") is using ST_Distance in an ORDER BY clause. Calculating the exact distance to every single hospital in the country before sorting them requires a massive full table scan.

Instead, use the KNN (K-Nearest Neighbour) operator: <->. This operator calculates the 2D distance between bounding boxes directly inside the index, allowing the query planner to instantly find the closest geometries.

Slow Query:

SELECT name, geom 
FROM hospitals 
ORDER BY ST_Distance(geom, ST_SetSRID(ST_MakePoint(-0.12, 51.50), 4326)) 
LIMIT 5;

Lightning-Fast Query:

SELECT name, geom 
FROM hospitals 
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(-0.12, 51.50), 4326) 
LIMIT 5;

4. Bulk Nearest Neighbours: Enter the Lateral Join

The <-> KNN operator is incredibly fast, but it has one major drawback: It only uses the spatial index when one side of the operator is a constant, static geometry. This is fine for finding the objects nearest to a single point, but it does not help for a spatial join where you want to find the nearest hospital for every school in your dataset.

To solve this, you must pair the <-> operator with a CROSS JOIN LATERAL. A lateral join acts as an inner loop. It feeds each row from the first table into the subquery one at a time, allowing the index-assisted KNN operator to work its magic for bulk operations.

SELECT s.school_name, h.hospital_name
FROM schools s
CROSS JOIN LATERAL (
    SELECT hospital_name 
    FROM hospitals h 
    ORDER BY h.geom <-> s.geom 
    LIMIT 1
) AS h;

5. Prove It with EXPLAIN ANALYZE

Never guess why a query is slow; ask the database directly. By putting EXPLAIN (ANALYZE, BUFFERS) before your SELECT statement, PostgreSQL will output the exact execution plan. (Note: The SQL command itself retains the American spelling ANALYZE, as it is hardcoded into PostgreSQL).

1. Seq Scan (Sequential Scan): The index was not used, and the database read every single row. This means you either lack an index, your query operator does not support indexes, or your table is so small that the database decided scanning it was faster.
2. Index Scan / Bitmap Index Scan: Your GiST index is working correctly.

Finally, remember that creating an index is not enough. You must run VACUUM ANALYZE table_name; after importing large datasets. This forces PostGIS to gather spatial statistics regarding your geometry distribution. Without these statistics, the query planner relies on stale data and routinely chooses a slow sequential scan even when a perfect GiST index is available.

The Kartoza Challenge

Now it is your turn to test the difference indexing makes on your own machine.

1. Open your PostGIS database (via pgAdmin or psql) and generate 100,000 random points:

CREATE TABLE test_points AS 
SELECT id, ST_SetSRID(ST_MakePoint(random()*100, random()*100), 4326) AS geom 
FROM generate_series(1, 100000) AS id;

1. Run an EXPLAIN (ANALYZE, BUFFERS) to find all points within a specific bounding box before indexing:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM test_points 
WHERE geom && ST_MakeEnvelope(10, 10, 20, 20, 4326);

Take note of the Execution Time.

1. Now, create a GiST index on the geom column and run ANALYZE test_points;.
2. Run the same EXPLAIN (ANALYZE, BUFFERS) query again.

Compare your two execution times. You should see a massive performance gain, proving that preparing for database scale early makes all the difference. Happy querying!