Date: Fri, 19 Jun 2020 19:33:31 +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: <20200619173332.8A83D5FB26@mx.zeyos.com>
Precedence: bulk

> No.  The subplan estimates are for the number of rows produced by one
> execution of the subplan, ie the numbers of "accounts" or "contracts"
> rows that match those inner WHERE conditions.  This has very little
> a-priori relationship to the number of "transactions" rows that will
> satisfy the outer WHERE condition.  If we knew that transactions.account
> and transactions.contract were unique columns, then we might be able
> to say that there shouldn't be more than one outer match per subplan
> result row ... but you didn't say that, and it seems unlikely.=0A=0AThank=
s Tom, I understand your point.=0A=0AI don't want to waste your time but ma=
ybe there is room for improvement as both "account" and "contract" are high=
ly distinct and the individual subplan estimates are quite accurate:=0A=0AS=
eq Scan on transactions  (cost=3D67.81..171458.63 rows=3D1301316 width=3D12=
06) (actual time=3D69.418..917.594 rows=3D112 loops=3D1)=0A  Filter: ((hash=
ed SubPlan 1) OR (hashed SubPlan 2))=0A  Rows Removed by Filter: 1792937=0A=
  SubPlan 1=0A    ->  Bitmap Heap Scan on accounts  (cost=3D33.96..61.76 ro=
ws=3D46 width=3D4) (actual time=3D3.053..3.292 rows=3D111 loops=3D1)=0A    =
      Recheck Cond: ((name)::text ~~* '%test%'::text)=0A          Rows Remo=
ved by Index Recheck: 4=0A          Heap Blocks: exact=3D104=0A          ->=
  Bitmap Index Scan on s_accounts  (cost=3D0.00..33.95 rows=3D46 width=3D0)=
 (actual time=3D0.505..0.505 rows=3D118 loops=3D1)=0A                Index =
Cond: ((name)::text ~~* '%test%'::text)=0A  SubPlan 2=0A    ->  Seq Scan on=
 contracts  (cost=3D0.00..5.93 rows=3D5 width=3D4) (actual time=3D2.531..2.=
836 rows=3D4 loops=3D1)=0A          Filter: ((name)::text ~~* '%test%'::tex=
t)=0A          Rows Removed by Filter: 272=0A=0AFor comparison here are the=
 plans for the queries with the individual where clauses:=0A=0ASELECT * FRO=
M "transactions" WHERE "account" IN (SELECT "ID" FROM "accounts" WHERE "nam=
e" ~~* '%test%')=0A=0ANested Loop  (cost=3D34.38..488.93 rows=3D155 width=
=3D1206) (actual time=3D0.599..1.393 rows=3D112 loops=3D1)=0A  ->  Bitmap H=
eap Scan on accounts  (cost=3D33.96..61.76 rows=3D46 width=3D4) (actual tim=
e=3D0.541..0.796 rows=3D111 loops=3D1)=0A        Recheck Cond: ((name)::tex=
t ~~* '%test%'::text)=0A        Rows Removed by Index Recheck: 4=0A        =
Heap Blocks: exact=3D104=0A        ->  Bitmap Index Scan on s_accounts  (co=
st=3D0.00..33.95 rows=3D46 width=3D0) (actual time=3D0.521..0.521 rows=3D11=
8 loops=3D1)=0A              Index Cond: ((name)::text ~~* '%test%'::text)=
=0A  ->  Index Scan using fk_transactions_account on transactions  (cost=3D=
0.43..9.08 rows=3D21 width=3D1206) (actual time=3D0.004..0.005 rows=3D1 loo=
ps=3D111)=0A        Index Cond: (account =3D accounts."ID")=0A=0ASELECT * F=
ROM "transactions" WHERE "contract" IN (SELECT "ID" FROM "contracts" WHERE =
"name" ~~* '%test%')=0A=0ANested Loop  (cost=3D3.76..10.10 rows=3D31662 wid=
th=3D1206) (actual time=3D0.082..0.082 rows=3D0 loops=3D1)=0A  ->  Bitmap H=
eap Scan on contracts  (cost=3D3.64..5.74 rows=3D5 width=3D4) (actual time=
=3D0.069..0.075 rows=3D4 loops=3D1)=0A        Recheck Cond: ((name)::text ~=
~* '%test%'::text)=0A        Heap Blocks: exact=3D2=0A        ->  Bitmap In=
dex Scan on s_contracts  (cost=3D0.00..3.64 rows=3D5 width=3D0) (actual tim=
e=3D0.060..0.060 rows=3D4 loops=3D1)=0A              Index Cond: ((name)::t=
ext ~~* '%test%'::text)=0A  ->  Index Scan using fk_transactions_contract o=
n transactions  (cost=3D0.12..0.86 rows=3D1 width=3D1206) (actual time=3D0.=
001..0.001 rows=3D0 loops=3D4)=0A        Index Cond: (contract =3D contract=
s."ID")=0A=0AThe statistics for the columns are:=0A=0ASELECT attname, null_=
frac, n_distinct from pg_stats WHERE tablename =3D 'transactions' AND attna=
me IN ('account', 'contract')=0A=0Atransactions.account:   null_frac=3D0.02=
5 n_distinct=3D80277=0Atransactions.contract:  null_frac=3D1     n_distinct=
=3D0     (there are basically no non-null values for field "contract" in tr=
ansactions)=0A=0AAccording to pg_class.reltuples the table "transactions" h=
as 1735088 rows.=0A=0AI'd naively expect the selectivity for an OR of those=
 two hashed subplans given uniform distribution to be:=0A=0Arows_total =3D =
=0A=09((rows_transactions * (1 - null_frac_account)) / n_distinct_account) =
* expected_rows_from_subplan1 +=0A=09((rows_transactions * (1 - null_frac_c=
ontract)) / n_distinct_contract) * expected_rows_from_subplan2=0A=0A=3D> ro=
ws_total =3D =0A=09((1735088 * (1 - 0.025)) / 80277) * 46 +=0A=09((1735088 =
* (1 - 1)) / 0) * 5=0A=0A=3D> rows_total =3D 969 + 0 /* no non-null values =
for contract field */=0A=0APlease forgive the sloppy math but something alo=
ng this line could be promising.=0A=0ABtw, I don't quite understand why the=
 nested loop on contract only is expected to yield 31662 rows, when the nul=
l_frac of field transactions.contract is 1. Shouldn't that indicate zero ro=
ws or some kind of default minimum estimate for that query?=0A=0AThanks aga=
in!=0A=0ABenjamin Coutu