[postgis-users] Severe performance problems with prepared statements

[Andy Colson]

On 1/7/2014 3:04 PM, William Becker wrote:
>
>>It looks like a bunch of row estimates are off.  Have you run analyze on the db?
>
> Yeah - I did do that, or at least Vacuum analyzes.
>
> Running an analyze now:
>
> INFO:  analyzing "public.place"
> INFO:  "place": scanned 4134 of 4134 pages, containing 442283 live rows
> and 0 dead rows; 30000 rows in sample, 442283 estimated total rows
> Total query runtime: 174 ms.
>
> Here is the before/after explain analyzes on the query you wrote below:
> you can see they still both do a seq scan.
>
> "Aggregate  (cost=119496.15..119496.16 rows=1 width=0) (actual
> time=1617.616..1617.616 rows=1 loops=1)"
> "  ->  Seq Scan on place  (cost=0.00..119127.58 rows=147428 width=0)
> (actual time=746.779..1617.611 rows=9 loops=1)"
> "        Filter:
> st_covers('0103000020E610000001000000050000000000D33CB60D9C3FB0941EB661BD49400000D33CB60D9C3FFF56F03723BA494000002DC349F290BFFF56F03723BA494000002DC349F290BFB0941EB661BD49400000D33CB60D9C3FB0941EB661BD4940'::geography,
> location)"
> "Total runtime: 1617.639 ms"
>
>
> "Aggregate  (cost=119496.15..119496.16 rows=1 width=0) (actual
> time=1618.655..1618.655 rows=1 loops=1)"
> "  ->  Seq Scan on place  (cost=0.00..119127.58 rows=147428 width=0)
> (actual time=747.573..1618.649 rows=9 loops=1)"
> "        Filter:
> st_covers('0103000020E610000001000000050000000000D33CB60D9C3FB0941EB661BD49400000D33CB60D9C3FFF56F03723BA494000002DC349F290BFFF56F03723BA494000002DC349F290BFB0941EB661BD49400000D33CB60D9C3FB0941EB661BD4940'::geography,
> location)"
> "Total runtime: 1618.678 ms"
>
> 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.
>
> 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.
>
> Thanks for the help Andy!

Weird that it doesn't use an index.  I see you are using Geography type, 
which was pretty new... maybe v2.0 st_covers() didnt use an index right.

If you still cannot get it to use and index, you might use a less 
restrictive (and probably quicker) && check:


select count(*)
from place,
st_GeogFromText('0103000020E610000001000000050000000000D33CB60D9C3FB0941EB661BD49400000D33CB60D9C3FFF56F03723BA494000002DC349F290BFFF56F03723BA494000002DC349F290BFB0941EB661BD49400000D33CB60D9C3FB0941EB661BD4940') 
as g
where g && location and st_covers(g, location)

The && might return too many records, but the st_covers() will drop 
them.  && is sure to be indexed, and is probably calculated faster 
because it's just bounding box.

-Andy