How big are your geometries?<br><br>I get ;-<br> select ST_npoints(the_geom) from territory_geometries order by ST_npoints desc limit 25;<br> st_npoints <br>------------<br> 2065217<br> 1968994<br> 1859352<br> 1625664<br>
1312561<br> 1199055<br> 743194<br> 619309<br> 613689<br> 592206<br> 567210<br> 566678<br> 562565<br> 562527<br> 540225<br> 522073<br> 510018<br> 457435<br> 450007<br>
420056<br> 412069<br> 412069<br> 394161<br> 380606<br> 374984<br>(25 rows)<br><br>As you can see some of the geometries are pretty big. <br><br>Version? <br> postgis_full_version <br>
----------------------------------------------------------------------------------<br> POSTGIS="1.3.3" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec 2007" USE_STATS<br><br>Index?<br><br>CREATE INDEX terr_geom_gist ON territory_geometries USING gist (the_geom);<br>
<br>and the same for the bounding box column<br><br>Just selecting using the bounding box column gives this explanation (I'm using the airports table to save typing in lng/lat all the time, and I know where the airport is and which admin regions it should be in)<br>
<br>explain analyse select <a href="http://t1.id">t1.id</a> from territories t1 <br>join territory_geometries tg1 on (<a href="http://t1.id">t1.id</a> = tg1.territory_id and t1.territory_type > 2) ,airports a2<br>where ST_Within(a2.the_geom,tg1.bbox) <br>
and a2.iata_code = 'YYZ';<br> QUERY PLAN <br>----------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>
Nested Loop (cost=0.00..83.02 rows=57 width=4) (actual time=0.684..6.917 rows=12 loops=1)<br> -> Nested Loop (cost=0.00..16.68 rows=57 width=4) (actual time=0.659..6.496 rows=15 loops=1)<br> Join Filter: _st_within(a2.the_geom, tg1.bbox)<br>
-> Index Scan using airports_iata on airports a2 (cost=0.00..8.27 rows=1 width=100) (actual time=0.039..0.045 rows=1 loops=1)<br> Index Cond: ((iata_code)::text = 'YYZ'::text)<br> -> Index Scan using index_territory_geometries_on_bbox on territory_geometries tg1 (cost=0.00..8.40 rows=1 width=456) (actual time=0.592..6.171 rows=15 loops=1)<br>
Index Cond: (a2.the_geom && tg1.bbox)<br> Filter: (a2.the_geom && tg1.bbox)<br> -> Index Scan using territories_pkey on territories t1 (cost=0.00..1.15 rows=1 width=4) (actual time=0.019..0.021 rows=1 loops=15)<br>
Index Cond: (<a href="http://t1.id">t1.id</a> = tg1.territory_id)<br> Filter: (t1.territory_type > 2)<br> Total runtime: 7.070 ms<br>(12 rows)<br><br>Which is pretty fast, but as you can see I'm getting too many matches because some of the bounding boxes overlap, so I need to take the results of this and do a final match with the<br>
real geom column to filter down to just 3 rows. But when I add the matching clause I get this;-<br><br>explain analyse select <a href="http://t1.id">t1.id</a>,<a href="http://t1.name">t1.name</a>,t1.territory_type from <br>
territories t1 join territory_geometries tg1 on (<a href="http://t1.id">t1.id</a> = tg1.territory_id and t1.territory_type > 2), airports a2<br>where a2.iata_code = 'YYZ'<br>and a2.the_geom && tg1.bbox<br>
and ST_Within(a2.the_geom,tg1.the_geom) ;<br> QUERY PLAN <br>
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br> Nested Loop (cost=0.00..17.85 rows=1 width=18) (actual time=84964.441..202308.802 rows=3 loops=1)<br>
-> Nested Loop (cost=0.00..16.69 rows=1 width=4) (actual time=84964.365..202308.474 rows=4 loops=1)<br> Join Filter: ((a2.the_geom && tg1.the_geom) AND _st_within(a2.the_geom, tg1.the_geom))<br> -> Index Scan using airports_iata on airports a2 (cost=0.00..8.27 rows=1 width=100) (actual time=0.039..0.042 rows=1 loops=1)<br>
Index Cond: ((iata_code)::text = 'YYZ'::text)<br> -> Index Scan using index_territory_geometries_on_bbox on territory_geometries tg1 (cost=0.00..8.40 rows=1 width=44685) (actual time=0.634..3.817 rows=15 loops=1)<br>
Index Cond: (a2.the_geom && tg1.bbox)<br> Filter: (a2.the_geom && tg1.bbox)<br> -> Index Scan using territories_pkey on territories t1 (cost=0.00..1.15 rows=1 width=18) (actual time=0.071..0.073 rows=1 loops=4)<br>
Index Cond: (<a href="http://t1.id">t1.id</a> = tg1.territory_id)<br> Filter: (t1.territory_type > 2)<br> Total runtime: 202308.905 ms<br>(12 rows)<br><br>I don't know why explain analyse reports 12 rows, when I actually run the thing only 3 rows are returned. Anyway, as you can see the query planner decides to do the match on the full geometry column tg1.the_geom, before the match on the reduced geometry tg1.bbox, which is the wrong way round. Crucially, it's not doing an index scan on the full geometry column even though there's an index on it and I've vacuum analysed it umpteen times. I've also added cached bounding boxes to the geometries in that column but it makes no difference.<br>
<br>Simplifying the query to exclude the filter on the bbox column and the other extraneous table I get<br><br> explain analyse select <a href="http://tg.id">tg.id</a> from territory_geometries tg, airports a where ST_Within(a.the_geom,tg.the_geom) and a.iata_code = 'YYZ';<br>
QUERY PLAN <br>--------------------------------------------------------------------------------------------------------------------------------------------------------<br>
Nested Loop (cost=0.00..16.69 rows=57 width=4) (actual time=82110.289..209649.492 rows=4 loops=1)<br> Join Filter: _st_within(a.the_geom, tg.the_geom)<br> -> Index Scan using airports_iata on airports a (cost=0.00..8.27 rows=1 width=100) (actual time=32.846..32.852 rows=1 loops=1)<br>
Index Cond: ((iata_code)::text = 'YYZ'::text)<br> -> Index Scan using terr_geom_gist on territory_geometries tg (cost=0.00..8.41 rows=1 width=44233) (actual time=998.460..4147.349 rows=15 loops=1)<br>
Index Cond: (a.the_geom && tg.the_geom)<br> Filter: (a.the_geom && tg.the_geom)<br> Total runtime: 209657.567 ms<br><br>This time it is using the gist index on the full geometry column, but it's still blindingly slow, over 200 seconds as compared with 7ms using the bbox column.<br>
<br>Anyway, I've got a workaround for the moment. I just code my client to do the lookup in two parts, the first using the bbox to get a reduced list, and the second to step through the reduced list to check against the full geometry. Messy but it works and I can move on. Though I would like to know how to get the query planner to do things the other way around so that it select on the bbox column first and then works on the full geometry.<br>
<br>Ta<br><br>John Small<br><br><br><br><br><br>