Subject: Re: Potential performance issues
To: pgsql-performance@lists.postgresql.org
References: 
 <BN6PR07MB313763C4040616159DE1B6FBEE9B9@BN6PR07MB3137.namprd07.prod.outlook.com>
From: Thomas Kellerer <shammat@gmx.net>
Message-ID: <5fcce4e3-3ee7-e516-91a6-1883def57de8@gmx.net>
Date: Mon, 1 Mar 2021 15:44:02 +0100
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23)
 Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
MIME-Version: 1.0
In-Reply-To: 
 <BN6PR07MB313763C4040616159DE1B6FBEE9B9@BN6PR07MB3137.namprd07.prod.outlook.com>
Content-Type: text/plain; charset=utf-8
Content-Language: de-DE
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

Jung, Jinho schrieb am 28.02.2021 um 16:04:
> # Performance issues discovered from differential test
>
> For example, the below query runs x1000 slower than other DBMSs from Pos=
tgreSQL.
>
> =C2=A0 =C2=A0 select ref_0.ol_amount as c0
> =C2=A0 =C2=A0 from order_line as ref_0
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 left join stock as ref_1
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 on (ref_0.ol_o_id =3D ref_1.s_w_id )
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 inner join warehouse as ref_2
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 on (ref_1.s_dist_09 is NULL)
> =C2=A0 =C2=A0 where ref_2.w_tax is NULL;

I find this query extremely weird to be honest.

There is no join condition between warehouse and the other two tables whic=
h results in a cross join.
Which is "reduced" somehow by applying the IS NULL conditions - but still,=
 to me this makes no sense.

Maybe the Postgres optimizer doesn't handle this ugly "join condition" the=
 same way the others do.

I would rather expect a NOT EXISTS against the warehouse table.

Thomas