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
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
