[postgis-users] Odd explain analyze estimates?

[strk at refractions.net]

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