[postgis-devel] Re: [postgis-users] GIST index speed

[Mark Cave-Ayland]

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