[postgis-users] Over-complicated shapes slowing down my queries?

[Dan Bernier]

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