Date: Fri, 19 Jun 2020 17:12:31 +0200
Subject: Unclamped row estimates whith OR-ed subplans
From: "Benjamin Coutu" <ben.coutu@zeyos.com>
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <20200619151232.7F3285FB26@mx.zeyos.com>
Precedence: bulk

Hello,=0A=0Aplease consider the following SQL query:=0A=0ASELECT * FROM "tr=
ansactions" WHERE =0A=09"account" IN (SELECT "ID" FROM "accounts" WHERE "na=
me" ~~* '%test%') OR=0A=09"contract" IN (SELECT "ID" FROM "contracts" WHERE=
 "name" ~~* '%test%')=0A=0AThis yields the following plan on Postgres 11:=
=0A=0ASeq Scan on transactions  (cost=3D67.21..171458.03 rows=3D1301316 wid=
th=3D1206)=0A  Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2))=0A  SubPl=
an 1=0A    ->  Bitmap Heap Scan on accounts  (cost=3D33.36..61.16 rows=3D46=
 width=3D4)=0A          Recheck Cond: ((name)::text ~~* '%test%'::text)=0A =
         ->  Bitmap Index Scan on s_accounts  (cost=3D0.00..33.35 rows=3D46=
 width=3D0)=0A                Index Cond: ((name)::text ~~* '%test%'::text)=
=0A  SubPlan 2=0A    ->  Seq Scan on contracts  (cost=3D0.00..5.93 rows=3D5=
 width=3D4)=0A          Filter: ((name)::text ~~* '%test%'::text)=0A=0ASo t=
he where clause of this query has just two subplans OR-ed together, one is =
estimated to yield 46 rows and one is estimated to yield 5 rows.=0AI'd expe=
ct the total rows for the seqscan to be estimated at 46 then, following the=
 logic that rows_seqscan =3D max(rows_subplan1, rows_subplan2). As you can =
see, the optimizer estimates a whopping 1301316 rows instead.=0A=0AI am abs=
olutely aware that those are hashed sub plans below a seqscan and that Post=
gres therefore has to scan all tuples of the table. But the problem is that=
 upper nodes (which are excluded from this example for simplicity) think th=
ey will receive 1301316 rows from the seqscan, when in fact they will proba=
bly only see a hand full, which the planner could have (easily?) deduced by=
 taking the greater of the two subplan row estimates.=0A=0AWhat am I missin=
g, or is this perhaps a shortfall of the planner?=0A=0AThanks,=0A=0ABen=0A=
=0A-- =0A=0ABejamin Coutu=0Aben.coutu@zeyos.com=0A=0AZeyOS GmbH & Co. KG=0A=
http://www.zeyos.com