[postgis-devel] Re: [postgis-users] Odd explain analyze estimates?

[strk at refractions.net]

On Tue, Mar 08, 2005 at 01:44:05AM -0800, Ron Mayer wrote:
> Seems I didn't get the whole patch..
> somewhere around line 1655 there must have been another change.
> (or I made a typeo when trying to apply your patch).

Sorry, wrong snippet, the important one is:

@@ -1653,5 +1653,6 @@
         */

-       relid = getrelid(varRelid, root->rtable);
+//     relid = getrelid(varRelid, root->rtable);
+       relid = getrelid(self->varno, root->rtable);


Change that line manually in lwgeom_estimate.c.
If you can't find it at line 1653 look
for "getrelid", there are about 4 and only ONE using verRelid.

In any case the change is in current CVS.

--strk;


> 
>   Ron
> 
> 
> 
> strk at refractions.net wrote:
> > This seems to fix the problem:
> >
> > diff -U2 -r1.25 -r1.26
> > --- lwgeom_estimate.c   8 Mar 2005 09:23:34 -0000       1.25
> > +++ lwgeom_estimate.c   8 Mar 2005 09:27:23 -0000       1.26
> > @@ -1571,5 +1571,5 @@
> >         //Oid operator = PG_GETARG_OID(1);
> >         List *args = (List *) PG_GETARG_POINTER(2);
> > -       int varRelid = PG_GETARG_INT32(3);
> > +       //int varRelid = PG_GETARG_INT32(3);
> >         Oid relid;
> >         HeapTuple stats_tuple;
> >
> > Can you check ?
> > --strk;
> >
> > On Mon, Mar 07, 2005 at 04:42:18PM -0800, Ron Mayer wrote:
> >
> >>Ron Mayer wrote:
> >>
> >>>strk at refractions.net wrote:
> >>>
> >>>>>>... You can enable debugging info:
> >>>>>>#define DEBUG_GEOMETRY_STATS 1
> >>>>>>in lwgeom/lwgeom_estimate.c around line 28
> >>>
> >>>I have a version that does this now; but it sounds like Mark
> >>>already understands the problem.
> >>
> >>And indeed, the extra "select * from ([myquery])" lead it to use the
> >>default estimate.
> >>
> >>   Ron
> >>
> >>
> >>fli=# explain analyze select * from (select * from t1 natural join t2 
> where
> >>g&&'BOX3D(0 0,100 100)'::box3d) as a;
> >>NOTICE:  LWGEOM_gist_sel called
> >>NOTICE:   No statistics, returning default estimate
> >>                                                                          
> QUERY PLAN                                  \
> >>------------------------------------------------------------------------------------------------------------------------------------------------------\
> >>-------------------------------------------------------------------------
> >> Nested Loop  (cost=0.00..230.22 rows=1 width=46) (actual
> >> time=0.196..6028.713 rows=10000 loops=1)
> >>   Join Filter: ("outer".id = "inner".id)
> >>   ->  Seq Scan on t1  (cost=0.00..229.00 rows=1 width=46) (actual
> >>   time=0.093..253.877 rows=10000 loops=1)
> >>         Filter: (g && '[...long geometry...]'::geometry)
> >>   ->  Seq Scan on t2  (cost=0.00..1.10 rows=10 width=4) (actual
> >>   time=0.025..0.266 rows=10 loops=10000)
> >> Total runtime: 6261.536 ms
> >>(6 rows)
> >>
> >>fli=# explain analyze select * from t1 natural join t2 where g&&'BOX3D(0
> >>0,100 100)'::box3d;
> >>NOTICE:  LWGEOM_gist_sel called
> >>NOTICE:   search_box contains histogram, returning 1
> >>NOTICE:   returning computed value: 1.000000
> >>                                                                          
> QUERY PLAN                                  \
> >>
> >>------------------------------------------------------------------------------------------------------------------------------------------------------\
> >>-------------------------------------------------------------------------
> >> Hash Join  (cost=1.12..380.12 rows=10000 width=46) (actual
> >> time=0.742..730.325 rows=10000 loops=1)
> >>   Hash Cond: ("outer".id = "inner".id)
> >>   ->  Seq Scan on t1  (cost=0.00..229.00 rows=10000 width=46) (actual
> >>   time=0.035..251.434 rows=10000 loops=1)
> >>         Filter: (g && '[...long geometry...]'::geometry)
> >>   ->  Hash  (cost=1.10..1.10 rows=10 width=4) (actual time=0.597..0.597
> >>   rows=0 loops=1)
> >>         ->  Seq Scan on t2  (cost=0.00..1.10 rows=10 width=4) (actual
> >>         time=0.043..0.250 rows=10 loops=1)
> >> Total runtime: 984.099 ms
> >>(7 rows)
> >>
> >>fli=#
> >>_______________________________________________
> >>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