<div dir="ltr"><br style="font-family:arial,sans-serif;font-size:13px"><span style="font-family:arial,sans-serif;font-size:13px">>It looks like a bunch of row estimates are off. Have you run analyze on the db?</span><br style="font-family:arial,sans-serif;font-size:13px">
<br>Yeah - I did do that, or at least Vacuum analyzes.<div><br></div><div>Running an analyze now: </div><div><br></div><div><div>INFO: analyzing "public.place"</div><div>INFO: "place": scanned 4134 of 4134 pages, containing 442283 live rows and 0 dead rows; 30000 rows in sample, 442283 estimated total rows</div>
<div>Total query runtime: 174 ms.</div><div><br></div><div>Here is the before/after explain analyzes on the query you wrote below: you can see they still both do a seq scan. </div><div><br></div><div><div>"Aggregate (cost=119496.15..119496.16 rows=1 width=0) (actual time=1617.616..1617.616 rows=1 loops=1)"</div>
<div>" -> Seq Scan on place (cost=0.00..119127.58 rows=147428 width=0) (actual time=746.779..1617.611 rows=9 loops=1)"</div><div>" Filter: st_covers('0103000020E610000001000000050000000000D33CB60D9C3FB0941EB661BD49400000D33CB60D9C3FFF56F03723BA494000002DC349F290BFFF56F03723BA494000002DC349F290BFB0941EB661BD49400000D33CB60D9C3FB0941EB661BD4940'::geography, location)"</div>
<div>"Total runtime: 1617.639 ms"</div><div><br></div><div><br></div><div>"Aggregate (cost=119496.15..119496.16 rows=1 width=0) (actual time=1618.655..1618.655 rows=1 loops=1)"</div><div>" -> Seq Scan on place (cost=0.00..119127.58 rows=147428 width=0) (actual time=747.573..1618.649 rows=9 loops=1)"</div>
<div>" Filter: st_covers('0103000020E610000001000000050000000000D33CB60D9C3FB0941EB661BD49400000D33CB60D9C3FFF56F03723BA494000002DC349F290BFFF56F03723BA494000002DC349F290BFB0941EB661BD49400000D33CB60D9C3FB0941EB661BD4940'::geography, location)"</div>
<div>"Total runtime: 1618.678 ms"</div></div><div><br></div>Interestingly, it seems to be using the indexes on my dev database, so try upgrading from 2.0->2.1 and 9.2->9.3 and redeploying the db and see if that has an effect.</div>
<div><br></div><div>I'll try to do the upgrade over the next few days and then if that works, the stored procedure again and let you know how it goes.</div><div><br></div><div>Thanks for the help Andy!</div></div>