I'm working with OpenStreetMap osm2pgsql database. One of its table (planet_osm_line) have two indexed fields: osm_id (int, primary key) and way (postgis geometry).
I'd like to find which streets intersect with a specific street, which I know by it's osm_id. So I do:
SELECT name, * FROM planet_osm_line WHERE highway IS NOT NULL AND osm_id != 126021312 AND ST_Intersects(way, (SELECT way FROM planet_osm_line WHERE osm_id = 126021312 LIMIT 1))
And it takes about 10 seconds to run.
If instead, I take that subquery out and run it separately, it looks about like this:
SELECT name, * FROM planet_osm_line WHERE highway IS NOT NULL AND osm_id != 126021312 AND ST_Intersects(way, '010200002031BF0D000D000000E17...')
And it takes about 0.47 seconds to run.
Running EXPLAIN on the first and the second query gives me a hint about the difference.
Seq Scan on planet_osm_line (cost=2.09..614596.67 rows=628706 width=1079) Filter: ((highway IS NOT NULL) AND (osm_id <> 126021312) AND st_intersects(way, $0)) InitPlan 1 (returns $0) -> Limit (cost=0.43..2.09 rows=1 width=249) -> Index Scan using planet_osm_line_pkey on planet_osm_line planet_osm_line_1 (cost=0.43..3.76 rows=2 width=249) Index Cond: (osm_id = 126021312)
Index Scan using planet_osm_line_index on planet_osm_line (cost=0.41..4.25 rows=1 width=1079) Index Cond: (way && '010200002031BF0D000D000000E17...'::geometry) Filter: ((highway IS NOT NULL) AND (osm_id <> 126021312) AND _st_intersects(way, '010200002031BF0D000D000000E17...'::geometry))
Why is it that PostgreSQL is doing a seq scan on the first and a index scan on the second? Is there a way to solve this problem without issuing two queries?