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

[Ethan Alpert]

No explicit JOIN's in my query

-e

-----Original Message-----
From: strk at refractions.net [mailto:strk at refractions.net] 
Sent: Thursday, September 08, 2005 11:45 AM
To: PostGIS Users Discussion; Ethan Alpert
Cc: Mark Cave-Ayland
Subject: Re: [postgis-users] Why does this take almost 3 hours?


On Thu, Sep 08, 2005 at 07:37:23PM +0200, strk at refractions.net wrote:
> On Wed, Aug 31, 2005 at 01:21:21PM -0600, Ethan Alpert wrote:
> > 
> > 
> > I'll look at the explain again. In my first email I mentioned I have

> > created spatial indexes for both my tables.
> 
> Ethan, would you run VACUUM ANALYZE and send explain output again ?

A few more words about this.
The explain output you reported in an older mail follows:

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)
 Total runtime: 2397998.833 ms

What I don't know is why the intersect() function is used as Join Filter
rather then the && operator. Did you force it with an explicit JOIN 
.. ON query or was it choosen by the planner ?

In the latter case this might be due to the join selectivity estimator
returning a too high value, in which case you might try recompiling
postgis undefining REALLY_DO_JOINSEL in lwgeom_estimate.c (around line
100).

--strk;