RE: Improve selectivity estimate for range queries

From: "Yuzuko Hosoya" <hosoya(dot)yuzuko(at)lab(dot)ntt(dot)co(dot)jp>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "'Robert Haas'" <robertmhaas(at)gmail(dot)com>
Cc: "'Kyotaro HORIGUCHI'" <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>, "'PostgreSQL Hackers'" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: RE: Improve selectivity estimate for range queries
Date: 2019-01-11 02:36:47
Message-ID: 000001d4a956$806a2ab0$813e8010$
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


Thanks for the comments, and I'm sorry for the late reply.

> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Friday, January 11, 2019 7:04 AM
> > Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Fri, Dec 21, 2018 at 11:50 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> A smaller-footprint way to fix the immediate problem might be to
> >> change the values of DEFAULT_INEQ_SEL and friends so that they're
> >> even less likely to be matched by accident. That is, instead of
> >> 0.3333333333333333, use 0.333333333333342 or some such.
> > That's not a dumb idea, but it seems pretty unprincipled to me, and to
> > be honest I'm kind of surprised that you're not proposing something
> > cleaner.
> The problem is the invasiveness of such a change (large) vs the benefit (not so large). The
> patch attempted to add a separate signaling path, but it was very incomplete --- and yet both I
> Horiguchi-san thought it was already too messy.
> Maybe at some point we'll go over to something reasonably principled, like adding confidence
> to all selectivity estimates. That would be *really* invasive but perhaps would bring enough
> to justify itself. But the current patch is just attempting to fix one extremely narrow pain
> and if that is all it's doing it should have a commensurately small footprint. So I don't think
> submitted patch looks good from a cost/benefit standpoint.
Yes, I agree with you. Indeed the patch I attached is insufficient in cost-effectiveness.
However, I want to solve problems of that real estimates happened to equal to the default
values such as this case, even though it's a narrow pain point. So I tried distinguishing
explicitly between real estimates and otherwise as Robert said.

The idea Tom proposed and Horiguchi-san tried seems reasonable, but I'm concerned whether
any range queries really cannot match 0.333333333333342 (or some such) by accident in any
environments. Is the way which Horiguchi-san did enough to prove that?

Best regards,
Yuzuko Hosoya
NTT Open Source Software Center

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Gierth 2019-01-11 02:40:25 Re: Ryu floating point output patch
Previous Message Etsuro Fujita 2019-01-11 02:21:01 Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0