[postgis-devel] Re: [postgis-users] GIST index speed
Mark Cave-Ayland
mark.cave-ayland at siriusit.co.uk
Wed Jun 11 08:55:46 PDT 2008
Paul Ramsey wrote:
> As performance problems go, it's not the worst case scenario.
> Re-writing the query as a join makes it very very fast. And wrapping
> it in a non-destructive function makes it acceptably fast. So there
> are ways to make it go fast.
Just for completeness, here is the EXPLAIN I get from your re-written
join query:
postgis=# explain analyze select count(*) from geography a join
geography b on (b.the_geom &&
b.centroid) where a.id = 69495;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=14298.01..14298.02 rows=1 width=0) (actual
time=294.601..294.602 rows=1 loops=1)
-> Nested Loop (cost=0.00..14298.01 rows=1 width=0) (actual
time=56.222..257.332 rows=32880 loops=1)
-> Seq Scan on geography a (cost=0.00..7149.00 rows=1
width=0) (actual time=56.186..56.363 rows=1 loops=1)
Filter: (id = 69495::numeric)
-> Seq Scan on geography b (cost=0.00..7149.00 rows=1
width=0) (actual time=0.020..128.780 rows=32880 loops=1)
Filter: (b.the_geom && b.centroid)
Total runtime: 294.693 ms
(7 rows)
I'm afraid I don't have enough planner-fu to know why this takes the low
cost route...
> My bright idea is: don't worry, be happy :)
I think that may just have to be the case ;)
ATB,
Mark.
--
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063
More information about the postgis-devel
mailing list