Return to Answer

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.

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);

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);

Unfortunately, I don't know the Postgres terminology/reason for this in terms of the planner's behavior.

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.

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 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 ); 

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);

Unfortunately, I don't know the Postgres terminology/reason for this in terms of the planner's behavior.