Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- my query #1:
- EXPLAIN ANALYSE SELECT * FROM import.osm_addresses AS addr
- WHERE st_intersects(addr.geometry, (SELECT geometry FROM import.osm_admin_8 WHERE osm_id=-332537));
- --RESULT:
- "Seq Scan on osm_addresses addr (cost=11.69..111161.75 rows=134094 width=656) (actual time=67.110..7203.351 rows=140 loops=1)"
- " Filter: st_intersects(geometry, $0)"
- " Rows Removed by Filter: 402141"
- " InitPlan 1 (returns $0)"
- " -> Bitmap Heap Scan on osm_admin_8 (cost=4.29..11.69 rows=2 width=32) (actual time=0.031..0.032 rows=1 loops=1)"
- " Recheck Cond: (osm_id = (-332537))"
- " -> Bitmap Index Scan on osm_admin_8_osm_id_idx (cost=0.00..4.29 rows=2 width=0) (actual time=0.022..0.022 rows=1 loops=1)"
- " Index Cond: (osm_id = (-332537))"
- "Total runtime: 7203.440 ms"
- -- my query #2:
- EXPLAIN ANALYSE SELECT * FROM import.osm_addresses AS addr
- WHERE addr.geometry && (SELECT geometry FROM osm_addr2 WHERE osm_id=-332537) AND
- st_intersects(addr.geometry, (SELECT geometry FROM import.osm_admin_8 WHERE osm_id=-332537));
- -- RESULT
- "Bitmap Heap Scan on osm_addresses addr (cost=40.77..201.90 rows=13 width=656) (actual time=0.009..0.009 rows=0 loops=1)"
- " Recheck Cond: (geometry && $0)"
- " Filter: st_intersects(geometry, $1)"
- " InitPlan 1 (returns $0)"
- " -> Seq Scan on osm_addr2 (cost=0.00..24.50 rows=6 width=32) (actual time=0.001..0.001 rows=0 loops=1)"
- " Filter: (osm_id = (-332537))"
- " InitPlan 2 (returns $1)"
- " -> Bitmap Heap Scan on osm_admin_8 (cost=4.29..11.69 rows=2 width=32) (never executed)"
- " Recheck Cond: (osm_id = (-332537))"
- " -> Bitmap Index Scan on osm_admin_8_osm_id_idx (cost=0.00..4.29 rows=2 width=0) (never executed)"
- " Index Cond: (osm_id = (-332537))"
- " -> Bitmap Index Scan on osm_addresses_geom (cost=0.00..4.58 rows=40 width=0) (actual time=0.006..0.006 rows=0 loops=1)"
- " Index Cond: (geometry && $0)"
- "Total runtime: 0.080 ms"
- -- Solution of John Barce:
- EXPLAIN ANALYSE SELECT * FROM import.osm_addresses AS addr, (SELECT geometry FROM import.osm_admin_8 WHERE osm_id=-332537) AS addr2
- WHERE st_intersects(addr.geometry, addr2.geometry);
- -- RESULT:
- "Nested Loop (cost=8.87..343.38 rows=268 width=483) (actual time=1.183..8.358 rows=140 loops=1)"
- " -> Bitmap Heap Scan on osm_admin_8 (cost=4.29..11.69 rows=2 width=32) (actual time=0.023..0.024 rows=1 loops=1)"
- " Recheck Cond: (osm_id = (-332537))"
- " -> Bitmap Index Scan on osm_admin_8_osm_id_idx (cost=0.00..4.29 rows=2 width=0) (actual time=0.018..0.018 rows=1 loops=1)"
- " Index Cond: (osm_id = (-332537))"
- " -> Bitmap Heap Scan on osm_addresses addr (cost=4.59..165.71 rows=13 width=451) (actual time=1.151..8.216 rows=140 loops=1)"
- " Recheck Cond: (geometry && osm_admin_8.geometry)"
- " Filter: _st_intersects(geometry, osm_admin_8.geometry)"
- " Rows Removed by Filter: 42"
- " -> Bitmap Index Scan on osm_addresses_geom (cost=0.00..4.58 rows=40 width=0) (actual time=0.106..0.106 rows=182 loops=1)"
- " Index Cond: (geometry && osm_admin_8.geometry)"
- "Total runtime: 8.466 ms"
- -- Solution of Mike T
- EXPLAIN ANALYSE SELECT * FROM import.osm_addresses AS addr, import.osm_admin_8 AS addr2
- WHERE addr2.osm_id=-332537 AND st_intersects(addr.geometry, addr2.geometry);
- -- RESULT
- "Nested Loop (cost=8.87..343.38 rows=268 width=740) (actual time=1.434..7.810 rows=140 loops=1)"
- " -> Bitmap Heap Scan on osm_admin_8 addr2 (cost=4.29..11.69 rows=2 width=84) (actual time=0.028..0.028 rows=1 loops=1)"
- " Recheck Cond: (osm_id = (-332537))"
- " -> Bitmap Index Scan on osm_admin_8_osm_id_idx (cost=0.00..4.29 rows=2 width=0) (actual time=0.021..0.021 rows=1 loops=1)"
- " Index Cond: (osm_id = (-332537))"
- " -> Bitmap Heap Scan on osm_addresses addr (cost=4.59..165.71 rows=13 width=656) (actual time=1.398..7.665 rows=140 loops=1)"
- " Recheck Cond: (geometry && addr2.geometry)"
- " Filter: _st_intersects(geometry, addr2.geometry)"
- " Rows Removed by Filter: 42"
- " -> Bitmap Index Scan on osm_addresses_geom (cost=0.00..4.58 rows=40 width=0) (actual time=0.190..0.190 rows=182 loops=1)"
- " Index Cond: (geometry && addr2.geometry)"
- "Total runtime: 7.928 ms"
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement