Date: Fri, 19 Jun 2020 22:33:15 +0200
Subject: Re: Unclamped row estimates whith OR-ed subplans
From: "Benjamin Coutu" <ben.coutu@zeyos.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <20200619203316.68FB05FB27@mx.zeyos.com>
Precedence: bulk

> While you're waiting, you might think about recasting the query to
> avoid the OR.  Perhaps you could do a UNION of two scans of the
> transactions table?=0A=0AThanks for the hint, I am well aware of the work=
around for OR via UNION. I am not trying to improve this query per se as it=
 is the small root of a very complex generated query and it's unfeasible to=
 rewrite it to a UNION in this case.=0A=0AThe point of my message to the li=
st was to highlight the misestimation, which I couldn't wrap my head around=
. Maybe this discussion can give some food for thought to someone who might=
 tackle this in the future. It would surely be great to have selectivity es=
timate smarts for the generic case of OR-ed SubPlans someday.=0A
>=20
> > Btw, I don't quite understand why the nested loop on contract only is e=
xpected to yield 31662 rows, when the null_frac of field transactions.contr=
act is 1. Shouldn't that indicate zero rows or some kind of default minimum=
 estimate for that query?
>=20
> That I don't understand.  I get a minimal rowcount estimate for an
> all-nulls outer table, as long as I'm using just one IN rather than=0A=0A=
Yeah, that might be worth digging into. Is there any other info apart from =
those stats that I could provide?