Spatio-temporal range queries and point-in-polygon (PIP) queries on large datasets can produce oversized intermediate result sets during index-based coarse filtering, forcing expensive exact-geometry calculations on thousands of irrelevant records. GanosBase two-phase query optimization refines the intermediate result set before the precise filtering phase runs, reducing unnecessary data I/O and compute overhead.
Enable the optimization by setting a session-level GUC parameter:
SET rds_enable_gist_refine = true;How it works
A spatio-temporal query runs in two passes:
Coarse filtering: The database uses multi-dimensional spatio-temporal indexes to identify candidate records. This pass is fast but can return a superset of matching records, including records that do not match the actual query geometry.
Precise filtering: The database applies an exact spatial function (such as
st_intersects) to every record in the intermediate result set. This pass is accurate but computationally expensive, and its cost scales with the size of the intermediate result set.
When the intermediate result set is large, the irrelevant records from coarse filtering drive up I/O and compute costs in the precise filtering phase. Two-phase query optimization reduces those irrelevant records before the precise filtering phase runs.
Query example
The following query uses st_intersects to find all rows in the test table whose geometry intersects a given polygon:
SELECT id
FROM test
WHERE st_intersects(
ST_GeomFromText('POLYGON((250000 -268000, 250000 270000, 280000 270000, 280000 -268000, 250000 -268000))', 3857),
geom
) = true;Without optimization, the multi-dimensional spatio-temporal index produces an intermediate result set that may contain irrelevant records. st_intersects then evaluates every record in that set against the exact polygon geometry.
Enable or disable the optimization
Two-phase query optimization is controlled by the GUC parameter rds_enable_gist_refine. The parameter is session-scoped and disabled by default.
Run the following statements at the start of each session where you need the optimization:
-- Enable two-phase query optimization
SET rds_enable_gist_refine = true;
-- Disable two-phase query optimization
SET rds_enable_gist_refine = false;rds_enable_gist_refine is not visible in the GUC parameter list. Set it explicitly at the start of each session.
The optimization applies to spatio-temporal range queries and PIP queries, including queries that use st_intersects.