[postgis-devel] RE: estimates problems and 1.0.0 delay
strk at refractions.net
strk at refractions.net
Mon Apr 18 05:08:19 PDT 2005
Mark, BTW, here is another warm.
Single point in table, invalid join selectivity:
strk=# explain analyze select * from t1 a, t1 b where a.g && b.g; NOTICE: LWGEOM_gist_joinsel called with jointype 0
NOTICE: Working with relations oids: 4150592 4150592
NOTICE: -- geomstats1 box: 1 1, 1 1
NOTICE: -- geomstats2 box: 1 1, 1 1
NOTICE: -- calculated intersection box is : 1 1, 1 1
NOTICE: search_box contains histogram, returning 1
NOTICE: search_box contains histogram, returning 1
NOTICE: selectivity1: 1 selectivity2: 1
NOTICE: Rows from rel1: 1.000000
NOTICE: Rows from rel2: 1.000000
NOTICE: Estimated rows returned: 4.000000
ERROR: invalid join selectivity: 4.000000
What about using selectivity1 * selectivity2 ?
It would allow use to avoid total_tuples lookup.
Would it be correct ?
--strk;
On Mon, Apr 18, 2005 at 12:46:11PM +0100, Mark Cave-Ayland wrote:
> Hi strk/Ron,
>
> > -----Original Message-----
> > From: strk at refractions.net [mailto:strk at refractions.net]
> > Sent: 18 April 2005 12:08
> > To: rm_postgis at cheapcomplexdevices.com; Mark Cave-Ayland
> > (External); postgis-devel at postgis.refractions.net
> > Subject: estimates problems and 1.0.0 delay
> >
> >
> > I've committed Ron's patch, but making some tests I
> > discovered some corner cases still unandled.
> >
> > It is collapsed histogram extent, mostly due to standard
> > deviation based hard deviants cut-off. Problem is that after
> > cut-off of hard deviants we end up with a 0-size dimension on Y or X.
> >
> > This is surely NOT something that happens on a normal
> > database usage but I think we should farther inspect it and
> > find a workaround. Sounds as a can o worms so I wouldn't open
> > it before 1.0.0 which was planned for today, but I'd delay
> > release until tomorrow evening
> > (CET) to allow a few more tests to be performed by Ron and
> > eventually Mark (and myself, of course).
>
> I agree, we could do with having a workaround on this. For example, what
> happens if you load a single point into a geometry table and then ANALYZE?
> Will that also produce a 0 sized dimension in Y and X?
>
> My current thinking would be to enforce a minimum (non-zero) histogram size,
> much along the lines that new tables default to returning 1000 rows until
> statistics information becomes available. Then if we find X or Y collapses
> to a zero dimension then we add some default (small) offsets and use this as
> the size of the X or Y histogram instead. This should only be an issue on
> small or artificial datasets, so if the estimates come out slightly less
> accurate for just these cases then we shouldn't have too much of a problem.
>
>
> Kind regards,
>
> Mark.
>
> ------------------------
> WebBased Ltd
> South West Technology Centre
> Tamar Science Park
> Plymouth
> PL6 8BT
>
> T: +44 (0)1752 791021
> F: +44 (0)1752 791023
> W: http://www.webbased.co.uk
>
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
More information about the postgis-devel
mailing list