[postgis-users] Why does this take almost 3 hours?

[strk at refractions.net]

On Thu, Sep 08, 2005 at 12:16:00PM -0600, Ethan Alpert wrote:
> 
> 
> No explicit JOIN's in my query

I tried running a query similar to yours, and my planner (8.0.0)
chooses this plan:

# explain select * from input.geobit_5 a, input.geobit_7 b where a.the_geom && b.the_geom and intersects(a.the_geom, b.the_geom);

 Nested Loop  (cost=249.01..647983.68 rows=7508 width=2541)
   Join Filter: (("outer".the_geom && "inner".the_geom) AND intersects("outer".the_geom, "inner".the_geom))
   ->  Seq Scan on geobit_5 a  (cost=0.00..3018.02 rows=8102 width=2271)
   ->  Materialize  (cost=249.01..280.84 rows=3183 width=270)
         ->  Seq Scan on geobit_7 b  (cost=0.00..245.83 rows=3183 width=270)

Compare with your plan:

> Nested Loop  (cost=0.00..67.41 rows=4966 width=273) (actual
> time=11.040..2397944.885 rows=13416 loops=1)
>    Join Filter: intersects("inner".the_geom, "outer".the_geom)
>    ->  Seq Scan on nga_reg b  (cost=0.00..1.11 rows=11 width=37) (actual
> time=0.004..147.586 rows=11 loops=1)
>    ->  Index Scan using new_snaps_spatial on new_snaps a
> (cost=0.00..6.01 rows=1 width=268) (actual time=68.748..9599.432
> rows=3892 loops=11)
>          Index Cond: (a.the_geom && "outer".the_geom)
>          Filter: (a.the_geom && "outer".the_geom)

Would you try *forcing* use of the && check *before* intersects() ?

Something like:

	select intersects(a, b)
	FROM (select t1.geom as a, t2.geom as b where t1.geom && t2.geom)
	as foo;

--strk;