[postgis-users] Odd explain analyze estimates?

[Ron Mayer]

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=#