liveBook · Manning

Index-aware function

As explained in Just Use Postgres!

An index-aware function is a spatial function whose query conditions can be transformed into index-supported bounding-box tests before exact evaluation.

An index-aware function lets PostgreSQL use a GiST index for an initial bounding-box filter before evaluating the exact spatial condition. This speeds searches while preserving accurate results. section 10.6.2

ST_DWithin is index-aware: it combines bounding-box operators such as && with ST_Expand, then compares actual distances. ST_Distance is not index-aware and can force a parallel full-table scan. section 10.6.2

Find nearby shops

Join points with ST_DWithin(b.way, p.way, 500), filter shops, calculate exact ST_Distance for display, sort by distance, and return the 10 closest results. The plan uses a GiST-assisted bitmap heap scan; replacing ST_DWithin with ST_Distance(...) <= 500 removes that index path.

How Just Use Postgres! covers it

How deep it goes
Explains bounding-box prefiltering, exact distance checks, and execution-plan differences, but does not detail operator implementation internals.
Examples
Demonstrates nearby-shop searches, GiST-assisted plans, and a contrasting ST_Distance plan that scans the table.
What it assumes
Assumes SQL, spatial geometries, GiST indexes, and PostGIS functions such as ST_DWithin and ST_Distance.
What you can do after
Diagnose spatial query performance by comparing index-aware predicates and EXPLAIN execution plans.
How it connects
Connects spatial indexes with GiST, bounding-box operators, ST_DWithin, ST_Distance, ST_Contains, ST_Intersects, and ST_Overlaps.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.