public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexander Korotkov <[email protected]>
To: [email protected]
Subject: pgsql: Convert 'x IN (VALUES ...)' to 'x = ANY ...' then appropriate
Date: Fri, 04 Apr 2025 13:17:00 +0000
Message-ID: <[email protected]> (raw)
Convert 'x IN (VALUES ...)' to 'x = ANY ...' then appropriate
This commit implements the automatic conversion of 'x IN (VALUES ...)' into
ScalarArrayOpExpr. That simplifies the query tree, eliminating the appearance
of an unnecessary join.
Since VALUES describes a relational table, and the value of such a list is
a table row, the optimizer will likely face an underestimation problem due to
the inability to estimate cardinality through MCV statistics. The cardinality
evaluation mechanism can work with the array inclusion check operation.
If the array is small enough (< 100 elements), it will perform a statistical
evaluation element by element.
We perform the transformation in the convert_ANY_sublink_to_join() if VALUES
RTE is proper and the transformation is convertible. The conversion is only
possible for operations on scalar values, not rows. Also, we currently
support the transformation only when it ends up with a constant array.
Otherwise, the evaluation of non-hashed SAOP might be slower than the
corresponding Hash Join with VALUES.
Discussion: https://postgr.es/m/0184212d-1248-4f1f-a42d-f5cb1c1976d2%40tantorlabs.com
Author: Alena Rybakina <[email protected]>
Author: Andrei Lepikhov <[email protected]>
Reviewed-by: Ivan Kush <[email protected]>
Reviewed-by: Alexander Korotkov <[email protected]>
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/c0962a113d1f2f94cb7222a7ca025a67e9ce3860
Modified Files
--------------
src/backend/optimizer/plan/subselect.c | 80 ++++++++
src/backend/optimizer/prep/prepjointree.c | 12 ++
src/backend/optimizer/util/clauses.c | 14 +-
src/include/optimizer/subselect.h | 3 +
src/test/regress/expected/subselect.out | 308 ++++++++++++++++++++++++++++++
src/test/regress/sql/subselect.sql | 100 ++++++++++
6 files changed, 512 insertions(+), 5 deletions(-)
view thread (7+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: pgsql: Convert 'x IN (VALUES ...)' to 'x = ANY ...' then appropriate
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox