From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Benjamin Coutu" <ben.coutu@zeyos.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Unclamped row estimates whith OR-ed subplans
In-reply-to: <20200619173332.8A83D5FB26@mx.zeyos.com>
References: <20200619173332.8A83D5FB26@mx.zeyos.com>
Comments: In-reply-to "Benjamin Coutu" <ben.coutu@zeyos.com>
	message dated "Fri, 19 Jun 2020 19:33:31 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1729590.1592590609.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 19 Jun 2020 14:16:49 -0400
Message-ID: <1729591.1592590609@sss.pgh.pa.us>
Precedence: bulk

"Benjamin Coutu" <ben.coutu@zeyos.com> writes:
> I don't want to waste your time but maybe there is room for improvement =
as both "account" and "contract" are highly distinct and the individual su=
bplan estimates are quite accurate:

Yeah, as I said, the estimates you're getting for the OR'd subplans are
pretty stupid.  Once you throw the OR in there, it's not possible to
convert the IN clauses to semi-joins, so they just stay as generic
subplans.  It looks like we have exactly zero intelligence about the
generic case --- unless I'm missing something in clause_selectivity,
you just end up with a default 0.5 selectivity estimate.  So yeah,
there's a lot of room for improvement, whenever anyone finds some
round tuits to work on that.

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?

> Btw, I don't quite understand why the nested loop on contract only is ex=
pected 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 minimu=
m estimate for that query?

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
an OR:

regression=3D# create table contracts (id int);
CREATE TABLE
regression=3D# insert into contracts values(1),(2),(3),(4);
INSERT 0 4
regression=3D# analyze contracts ;
ANALYZE
regression=3D# create table transactions (contract int);
CREATE TABLE
regression=3D# insert into transactions select null from generate_series(1=
,100000);
INSERT 0 100000
regression=3D# analyze transactions;
ANALYZE
regression=3D# explain select * from transactions where contract in (selec=
t id from contracts);
                                QUERY PLAN                                =

--------------------------------------------------------------------------
 Hash Semi Join  (cost=3D1.09..1607.59 rows=3D1 width=3D4)
   Hash Cond: (transactions.contract =3D contracts.id)
   ->  Seq Scan on transactions  (cost=3D0.00..1344.00 rows=3D100000 width=
=3D4)
   ->  Hash  (cost=3D1.04..1.04 rows=3D4 width=3D4)
         ->  Seq Scan on contracts  (cost=3D0.00..1.04 rows=3D4 width=3D4)
(5 rows)

			regards, tom lane