Hello, and apologies in advance if this is a naive question - I'm
still very new to GIS.
Does PostGIS provide a way to measure the "complexity" of a shape?
I have some shapes that I suspect are slowing down queries - normally,
joining against them with ST_within takes < 1 second, but for some
rows, it takes from 3 to 25 seconds. The explain analyze for the
different queries looks the same. The complexity of the shape is the
only thing I can think of that might cause this, but I don't know of
any way to measure this, to guide my reasoning. I tried googling, but
don't quite know the right terms to search for.
Beyond general shape-complexity, are there any other properties of a
shape that might cause this kind of slow-down?
In case it helps, here are 3 explain analyze queries, taking ~0.5
second, ~3 seconds, and ~28 seconds:
prod=> explain analyze SELECT count(*) FROM "issues" as issues inner
join enhanced_watch_areas on ST_within(issues.point,
enhanced_watch_areas.poly) WHERE (enhanced_watch_areas.id = 53);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=17.96..17.97 rows=1 width=0) (actual
time=2954.615..2954.616 rows=1 loops=1)
-> Nested Loop (cost=0.00..16.61 rows=539 width=0) (actual
time=2.599..2907.044 rows=35460 loops=1)
Join Filter: _st_within(issues.point, enhanced_watch_areas.poly)
-> Index Scan using enhanced_watch_areas_pkey on
enhanced_watch_areas (cost=0.00..8.27 rows=1 width=53014) (actual
time=0.018..0.021 rows=1 loops=1)
Index Cond: (id = 53)
-> Index Scan using index_issues_on_point on issues
(cost=0.00..8.33 rows=1 width=100) (actual time=0.437..770.227
rows=35884 loops=1)
Index Cond: (issues.point && enhanced_watch_areas.poly)
Filter: (issues.point && enhanced_watch_areas.poly)
Total runtime: 2954.697 ms
(9 rows)
prod=> explain analyze SELECT count(*) FROM "issues" as issues inner
join enhanced_watch_areas on ST_within(issues.point,
enhanced_watch_areas.poly) WHERE (enhanced_watch_areas.id = 87);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=17.96..17.97 rows=1 width=0) (actual
time=25771.930..25771.931 rows=1 loops=1)
-> Nested Loop (cost=0.00..16.61 rows=539 width=0) (actual
time=278.313..25762.925 rows=2765 loops=1)
Join Filter: _st_within(issues.point, enhanced_watch_areas.poly)
-> Index Scan using enhanced_watch_areas_pkey on
enhanced_watch_areas (cost=0.00..8.27 rows=1 width=53014) (actual
time=0.016..0.021 rows=1 loops=1)
Index Cond: (id = 87)
-> Index Scan using index_issues_on_point on issues
(cost=0.00..8.33 rows=1 width=100) (actual time=276.910..7118.288
rows=3103 loops=1)
Index Cond: (issues.point && enhanced_watch_areas.poly)
Filter: (issues.point && enhanced_watch_areas.poly)
Total runtime: 25772.008 ms
(9 rows)
prod=> explain analyze SELECT count(*) FROM "issues" as issues inner
join enhanced_watch_areas on ST_within(issues.point,
enhanced_watch_areas.poly) WHERE (enhanced_watch_areas.id = 13);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=17.96..17.97 rows=1 width=0) (actual
time=542.366..542.367 rows=1 loops=1)
-> Nested Loop (cost=0.00..16.61 rows=539 width=0) (actual
time=2.577..531.721 rows=8155 loops=1)
Join Filter: _st_within(issues.point, enhanced_watch_areas.poly)
-> Index Scan using enhanced_watch_areas_pkey on
enhanced_watch_areas (cost=0.00..8.27 rows=1 width=53014) (actual
time=0.014..0.017 rows=1 loops=1)
Index Cond: (id = 13)
-> Index Scan using index_issues_on_point on issues
(cost=0.00..8.33 rows=1 width=100) (actual time=2.249..193.008
rows=8903 loops=1)
Index Cond: (issues.point && enhanced_watch_areas.poly)
Filter: (issues.point && enhanced_watch_areas.poly)
Total runtime: 542.439 ms
(9 rows)
Thanks very much,
Dan Bernier
--
twitter @danbernier
http://wordcram.org
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
