[postgis-users] Severe performance problems with prepared statements

[William Becker]

>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!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140107/de87e952/attachment.html>