[postgis-users] Not able to excute spatial query on postgresql database with postgis support

[Michael Fuhr]

On Mon, Oct 30, 2006 at 09:33:34AM -0600, vamsee movva wrote:
> explain ANALYZE (select count(*) from la_blocks as c join la_damage as d on
> c.stfid='220750501002999' and d.the_geom&&c.the_geom and intersects(
> d.the_geom,c.the_geom));
> 
>                                                                      QUERY
> PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------
> Aggregate  (cost=9.06..9.07 rows=1 width=0) (actual time=
> 973892.916..973892.917 rows=1 loops=1)
>   ->  Nested Loop  (cost=0.00..9.05 rows=1 width=0) (actual time=
> 973892.901..973892.901 rows=0 loops=1)
>         Join Filter: intersects("inner".the_geom, "outer".the_geom)
>         ->  Index Scan using stfidx_la_blocks on la_blocks c  (cost=
> 0.00..3.03 rows=1 width=585) (actual time=0.035..0.046 rows=1 loops=1)
>               Index Cond: ((stfid)::text = '220750501002999'::text)
>         ->  Index Scan using geomidx_la_damage on la_damage d  (cost=
> 0.00..6.01 rows=1 width=21) (actual time=2018.848..44473.260 rows=1139
> loops=1)
>               Index Cond: (d.the_geom && "outer".the_geom)
>               Filter: (d.the_geom && "outer".the_geom)
> Total runtime: 973918.391 ms

The index scan on geomidx_la_damage is taking a long time to find
1139 rows and then intersects() is taking forever to eliminate them
all.  How big is that index?  How many rows are in la_damage?  What
can you tell us about the geometries in la_damage and the geometry
in la_blocks for stfid = 220750501002999?  What's the output of the
following query?

SELECT summary(the_geom)
FROM la_blocks
WHERE stfid = '220750501002999';

Is the query significantly faster with distance() = 0 instead of
intersects()?  What version of PostgreSQL are you running?  What
OS and hardware?  What non-default settings do you have in
postgresql.conf?

-- 
Michael Fuhr