From: Tom Lane <tgl@sss.pgh.pa.us>
To: kaarel.moppel@gmail.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17922: ANY vs IN execution plan difference for a single
 explicit input value
In-reply-to: <17922-1e2e0aeedd294424@postgresql.org>
References: <17922-1e2e0aeedd294424@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Sat, 06 May 2023 20:56:21 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4035308.1683424028.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sat, 06 May 2023 21:47:08 -0400
Message-ID: <4035309.1683424028@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/4035309.1683424028%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> I guess there can be differences how IN vs ANY input is handled for prep=
ared
> statements but for plain SQL input I would expect them to produce identi=
cal
> plans.

If you can find any such promise in the documentation, I will be glad
to remove it ;-)

> EXPLAIN
> SELECT
>     a.*
> FROM
>     a
>     JOIN b USING (node_id)
> WHERE
>     a.project_id =3D ANY (ARRAY[1]::int8[])
> ORDER BY
>     a.project_id,
>     a.node_id
> LIMIT 10;

> EXPLAIN
> SELECT
>     a.*
> FROM
>     a
>     JOIN b USING (node_id)
> WHERE
>     a.project_id IN (1) -- Also OK with a plain "=3D1" witout IN
> ORDER BY
>     a.project_id,
>     a.node_id
> LIMIT 10;

The thing about "project_id IN (1)", which is reduced to "project_id =3D 1=
"
by the parser, is that the planner can see that that renders the "ORDER
BY a.project_id" clause a no-op.  So it only has to order by node_id,
which is what makes this plan valid:

>  Limit  (cost=3D0.72..38.07 rows=3D10 width=3D16)
>    ->  Nested Loop  (cost=3D0.72..55440.79 rows=3D14842 width=3D16)
>          ->  Index Only Scan using b_node_id_key on b  (cost=3D0.29..397=
.29 rows=3D15000 width=3D8)
>          ->  Index Only Scan using a_uq on a  (cost=3D0.43..3.67 rows=3D=
1 width=3D16)
>                Index Cond: ((project_id =3D 1) AND (node_id =3D b.node_i=
d))

If you don't recognize that then you're forced into sorting the indexscan
output, which is going to look pretty bad for a small-LIMIT situation.

We don't make any attempt to make a similar deduction from =3DANY clauses,
mainly because it's usually not possible to be sure that the array has
only one member.  I'm not excited about the cost/benefit ratio of adding
code to check for that.

			regards, tom lane