[postgis-users] Odd explain analyze estimates?

[Ron Mayer]

Thanks for looking into this, guys.

Mark Cave-Ayland wrote:
> OK looking closer, it appears as if the code returns the default selectivity
> if called with two variable arguments in the RESTRICT clause. 

Ah... to the default selectivity was simply so optimistic in my case
that it thought only 1 row would be returned.  Note that in both my
real-world example, and the standalone example, the estimate that only
1 row would return lead to a really really bad plan (nested loops of
sequential scans).   Even if the estimate was 2 rows it would have
picked something better (probably the same hash join that the correct
estimate of 10000 found).


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.

strk at refractions.net wrote:
 > It makes our JOINSEL return a default value:
 > #define DEFAULT_GEOMETRY_JOINSEL 0.000005
 >
 > If I recall correctly the pgsql default was much higher (0.1 or something).

Any reason the default is so optimistic?  At least for the
applications I'm dealing with (county-wide information systems)
it's pretty common for the map to cover the entire data set unless
you zoom in pretty narrowly.


Does it sound like the easiest workaround for me is to merely increase
this default value to something like 0.001?  (trying that now)


    Ron