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

[Ron Mayer]

Thanks!

Yup, it does indeed fix the problem I had, and now the test case
(where "select * from ([testquery]) as foo" gave a different plan
than the standalone query) gives the expected results (the same
plan) as well.

Hope this didn't mess up anyone else's query plans.

strk at refractions.net wrote:
> 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