[postgis-users] Odd explain analyze estimates?

[strk at refractions.net]

On Fri, Mar 04, 2005 at 03:58:02PM +0100, strk at refractions.net wrote:
> I think this is still due to JOIN selectivity.
> In the first query it cannot attempt a real estimate so a default
> is returned.
> In the second query it tries, and if I get it right it fails ;).

Sorry, reading closely to the estimates it seems that Mark's JOIN
selectivity estimator does a good job... it is the RESTRICT selectivity
which is returning a number too low (due to unavailability of a constant
probably). You can enable debugging info:

#define DEBUG_GEOMETRY_STATS 1
in lwgeom/lwgeom_estimate.c around line 28

--strk;

> 
> #define REALLY_DO_JOINSEL 0
> in lwgeom_estimate.c around line 100, re-install and see how things
> change.
> 
> --strk;
> 
> On Fri, Mar 04, 2005 at 03:46:49AM -0800, Ron Mayer wrote:
> > 
> > I have a query that runs quite quickly using a hash join when run
> > standalone.  When I use this query as a subquery the planner always 
> > seems to pick a differnt plan with an order of magnitude worse 
> > performance.
> > 
> > This bad plan is chosen even when the outer sql statement is
> > a trivial expression like this:
> >    select * from (query) as a;
> > which I believe should be a no-op.
> > 
> > 
> > 
> > If I'm reading the "explain analyze" output correctly; it seems that 
> > depending on the context; in one case the optimizer thinks the 
> > comparison on GEOMETRIES will be extremely selective (returning 1 row)
> > while in the other case it think it will be quite unselective (returning 
> > 15000+ rows).   I'm pretty sure that the two expressions are identical,
> > though, and can't guess why the estimates vary so drastically.  Am I
> > reading that explain analyze output correctly?  Or am I doing something
> > else wrong?   Thanks.
> > 
> > 
> >   Explain analyze output follows.
> >   Thanks,
> >   Ron
> > 
> > 
> > 
> > ============================================================================
> > 
> > fli=# explain analyze SELECT * from (select * from userfeatures.points join icons using (iconid) where the_geom && setSRID('BOX3D(-123.40 25.66,-97.87 43.17)'::BOX3D, -1 )) as upf ;
> >                                                                                                               QUERY PLAN                                                                                                              
> > --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> >  Nested Loop  (cost=0.00..446.42 rows=1 width=120) (actual time=-0.096..7928.546 rows=15743 loops=1)
> >    Join Filter: ("outer".iconid = "inner".iconid)
> >    ->  Seq Scan on points  (cost=0.00..444.43 rows=1 width=82) (actual time=0.096..132.255 rows=15743 loops=1)
> >          Filter: (the_geom && '010300000001000000050000009A99999999D95EC0295C8FC2F5A839409A99999999D95EC0F6285C8FC295454048E17A14AE7758C0F6285C8FC295454048E17A14AE7758C0295C8FC2F5A839409A99999999D95EC0295C8FC2F5A83940'::geometry)
> >    ->  Seq Scan on icons  (cost=0.00..1.44 rows=44 width=42) (actual time=0.006..0.242 rows=44 loops=15743)
> >  Total runtime: 8005.766 ms
> > (6 rows)
> > 
> > fli=# explain analyze               select * from userfeatures.points join icons using (iconid) where the_geom && setSRID('BOX3D(-123.40 25.66,-97.87 43.17)'::BOX3D, -1 );
> >                                                                                                               QUERY PLAN                                                                                                              
> > --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> >  Hash Join  (cost=1.55..682.84 rows=15789 width=120) (actual time=0.641..320.002 rows=15743 loops=1)
> >    Hash Cond: ("outer".iconid = "inner".iconid)
> >    ->  Seq Scan on points  (cost=0.00..444.43 rows=15794 width=82) (actual time=0.067..94.307 rows=15743 loops=1)
> >          Filter: (the_geom && '010300000001000000050000009A99999999D95EC0295C8FC2F5A839409A99999999D95EC0F6285C8FC295454048E17A14AE7758C0F6285C8FC295454048E17A14AE7758C0295C8FC2F5A839409A99999999D95EC0295C8FC2F5A83940'::geometry)
> >    ->  Hash  (cost=1.44..1.44 rows=44 width=42) (actual time=0.530..0.530 rows=0 loops=1)
> >          ->  Seq Scan on icons  (cost=0.00..1.44 rows=44 width=42) (actual time=0.026..0.287 rows=44 loops=1)
> >  Total runtime: 397.003 ms
> > (7 rows)
> > 
> > 
> > 
> > 
> > 
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users