PostGIS doesn't use spatial index with ST_Intersects
I have found that rearranging the query so that the sub-query is at the same level as the initial select, essentially a Cartesian product, but then using the where clause to restrict the records read, will cause the indexes to be used and avoid a full table scan.
SELECT *
FROM
osm_addr2 AS addr,
(SELECT geometry FROM osm_addr2 WHERE osm_id=-332537) as addr2
WHERE st_intersects(addr.geometry, addr2.geometry);
EDIT: thanks to MikeT for the link to the relevant docs and to Jakub for the term function inlining.
EDIT 2: I now find it more elegant to use CTE queries for this kind of problem, as they are easier to read than subqueries, and have the same effect as far as making the spatial index get utilized for the spatial intersection.
WITH addr2(geometry) AS
(SELECT geometry FROM osm_addr2 WHERE osm_id=-332537)
SELECT addr.*
FROM
osm_addr2 addr, addr2
WHERE ST_Intersects(addr.geometry, addr2.geometry);
PostgreSQL doesn't use indexes for functions, it uses indexes for operators only. What happens is function inlining. ST_INTERSECTS
is defined as:
CREATE OR REPLACE FUNCTION ST_Intersects(geom1 geometry, geom2 geometry)
RETURNS boolean
AS 'SELECT $1 && $2 AND _ST_Intersects($1,$2)'
LANGUAGE 'sql' IMMUTABLE;
And so the query gets rewritten to use the index to check the bounding boxes and narrow down the result and then check again. My best guess is that with a subquery it doesn't happen (probably because at that point the result of subquery is not known).
Note that planners have difficulty with subqueries, and your example can be rewritten without subqueries. A flattened query should look like this:
SELECT A.*
FROM osm_addr2 AS addr, osm_addr2 AS POI
WHERE POI.osm_id=-332537 AND ST_Intersects(addr.geometry, POI.geometry);
There's a relevant example in the manual (last two SQL examples), where a subquery is collapsed into a main query by the planner, which is the same example if you look at @John's answer.