Re: Replace IN VALUES with ANY in WHERE clauses during optimization

Hi, Alexander!

On 30.03.2025 00:59, Alexander Korotkov wrote:
> Hi, Alena!
>
> On Sat, Mar 29, 2025 at 9:03 PM Alena Rybakina
> <a(dot)rybakina(at)postgrespro(dot)ru> wrote:
>> On 29.03.2025 14:03, Alexander Korotkov wrote:
>>> One thing I have to fix: we must do
>>> IncrementVarSublevelsUp() unconditionally for all expressions as Vars
>>> could be deeper inside.
>> Yes, I'm looking at it too, I've just understood that it was needed for subqueries - they can contain var elements which needs decrease the sublevel parameter.
>>
>> for example for the query:
>>
>> EXPLAIN (COSTS OFF)
>> SELECT ten FROM onek t
>> WHERE unique1 IN (VALUES (0), ((2 IN (SELECT unique2 FROM onek c
>> WHERE c.unique2 = t.unique1))::integer));
>>
>> We are interested in this element: ((2 IN (SELECT unique2 FROM onek c WHERE c.unique2 = t.unique1))
>>
>> It is funcexpr object with RabgeTblEntry variable. I highlighted
>>
>> WARNING: 1{FUNCEXPR :funcid 2558 :funcresulttype 23 :funcretset false :funcvariadic false :funcformat 1 :funccollid 0 :inputcollid 0 :args ({SUBLINK :subLinkType 2 :subLinkId 0 :testexpr {OPEXPR :opno 96 :opfuncid 65 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location -1 :constvalue 4 [ 2 0 0 0 0 0 0 0 ]} {PARAM :paramkind 2 :paramid 1 :paramtype 23 :paramtypmod -1 :paramcollid 0 :location -1}) :location -1} :operName ("=") :subselect {QUERY :commandType 1 :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :hasAggs false :hasWindowFuncs false :hasTargetSRFs false :hasSubLinks false :hasDistinctOn false :hasRecursive false :hasModifyingCTE false :hasForUpdate false :hasRowSecurity false :hasGroupRTE false :isReturn false :cteList <> :rtable ({RANGETBLENTRY :alias {ALIAS :aliasname c :colnames <>} :eref {ALIAS :aliasname c :colnames ("unique1" "unique2" "two" "four" "ten" "twenty" "hundred" "thousand" "twothousand" "fivethous" "tenthous" "odd" "even" "stringu1" "stringu2" "string4")} :rtekind 0 :relid 32795 :inh true :relkind r :rellockmode 1 :perminfoindex 1 :tablesample <> :lateral false :inFromCl true :securityQuals <>}) :rteperminfos ({RTEPERMISSIONINFO :relid 32795 :inh true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 9) :insertedCols (b) :updatedCols (b)}) :jointree {FROMEXPR :fromlist ({RANGETBLREF :rtindex 1}) :quals {OPEXPR :opno 96 :opfuncid 65 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 2 :vartype 23 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varreturningtype 0 :varnosyn 1 :varattnosyn 2 :location -1} {VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 2 :varreturningtype 0 :varnosyn 1 :varattnosyn 1 :location -1}) :location -1}} :mergeActionList <> :mergeTargetRelation 0 :mergeJoinCondition <> :targetList ({TARGETENTRY :expr {VAR :varno 1 :varattno 2 :vartype 23 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varreturningtype 0 :varnosyn 1 :varattnosyn 2 :location -1} :resno 1 :resname unique2 :ressortgroupref 0 :resorigtbl 32795 :resorigcol 2 :resjunk false}) :override 0 :onConflict <> :returningOldAlias <> :returningNewAlias <> :returningList <> :groupClause <> :groupDistinct false :groupingSets <> :havingQual <> :windowClause <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :limitOption 0 :rowMarks <> :setOperations <> :constraintDeps <> :withCheckOptions <> :stmt_location -1 :stmt_len -1} :location -1}) :location -1}
>>
>>
>> I highlighted in bold the var we need - since it is in a subquery in the in expression will be flattened, all elements contained in it should decrease the level number by one, since they will belong to the subtree located above it. Because of that condition, this did not happen.
>>
>> I generally agree with you that it is better to remove that condition. The function IncrementVarSublevelsUp essentially goes through the structures below and will decrease the level of only the vars for which this needs to be done, and the condition with 1 will protect us from touching those vars that should not. So the varlevelsup for this var should be 1.
>>
>> I am currently investigating whether this transformation will be fair for all cases; I have not found any problems yet.
> Thank you for your feedback. I appreciate you're also looking for the
> potential problems. On thing to highlight: doing
> IncrementVarSublevelsUp() unconditionally is required not just for
> subqueries. Consider the following example.
>
> SELECT * FROM t WHERE val1 IN (VALUES (val2), (val2 +1));
>
> The second value contain Var, which needs IncrementVarSublevelsUp(),
> but the top node is OpExpr.
Yes, I agree with that - this is precisely why we need to call
IncrementVarSublevelsUp() unconditionally for all types.

As you mentioned earlier, Var nodes can be nested more deeply, and
skipping this step could lead to incorrect behavior in those cases. So,
now it works fine)

Thank you for an example.

I analyzed this transformation with various types of values that might
be used in conditions.

First, I verified whether the change would affect semantics, especially
in the presence of NULL elements. The only notable behavior I observed was
the coercion of NULL to an integer type. However, this behavior remains
the same even without our transformation, so everything is fine.

To test this, I created a onek table containing NULL values:

CREATE TABLE onek ( unique1 INT, unique2 INT, ten TEXT );

INSERT INTO onek VALUES (0, 10, 'zero'), (1, NULL, 'one'), (2, 2,
'two'), (3, NULL, 'three'), (4, NULL, 'only null match'), (5, NULL, 'two
+ null match'), (6, NULL, 'no match');

1.1) explain analyze SELECT ten FROM onek t WHERE unique1 IN (VALUES
(0), (NULL));

QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on onek t (cost=0.00..25.00 rows=6 width=32) (actual
time=0.274..0.282 rows=1.00 loops=1) Filter: (unique1 = ANY
('{0,NULL}'::integer[])) Rows Removed by Filter: 6 Buffers: shared
read=1 Planning: Buffers: shared hit=52 read=23 Planning Time: 2.124 ms
Execution Time: 0.374 ms (8 rows)

The query plan without our patch:

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=0.05..25.33 rows=12 width=32) (actual
time=0.064..0.081 rows=1.00 loops=1) Hash Cond: (t.unique1 =
"*VALUES*".column1) Buffers: shared hit=1 -> Seq Scan on onek t
(cost=0.00..22.00 rows=1200 width=36) (actual time=0.028..0.034
rows=7.00 loops=1) Buffers: shared hit=1 -> Hash (cost=0.03..0.03 rows=2
width=4) (actual time=0.018..0.020 rows=1.00 loops=1) Buckets: 1024
Batches: 1 Memory Usage: 9kB -> Values Scan on "*VALUES*"
(cost=0.00..0.03 rows=2 width=4) (actual time=0.004..0.008 rows=2.00
loops=1) Planning: Buffers: shared hit=8 Planning Time: 0.513 ms
Execution Time: 0.182 ms (12 rows)

I added another tuple with a NULL value in the unique1 column to verify
that the semantics remain correct when comparing NULL with NULL.

I didn't observe any issues, as the behavior was identical to how it
worked before applying the patch.

insert into onek values (NULL, 1, 'match');

1.2) explain analyze SELECT ten FROM onek t WHERE unique1 IN (VALUES
(0), (NULL));

QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on onek t (cost=0.00..25.00 rows=6 width=32) (actual
time=0.053..0.063 rows=1.00 loops=1) Filter: (unique1 = ANY
('{0,NULL}'::integer[])) Rows Removed by Filter: 7 Buffers: shared hit=1
Planning Time: 0.178 ms Execution Time: 0.109 ms (6 rows)

The query plan without our patch:

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=0.05..25.33 rows=12 width=32) (actual
time=0.076..0.090 rows=1.00 loops=1) Hash Cond: (t.unique1 =
"*VALUES*".column1) Buffers: shared hit=1 -> Seq Scan on onek t
(cost=0.00..22.00 rows=1200 width=36) (actual time=0.043..0.048
rows=8.00 loops=1) Buffers: shared hit=1 -> Hash (cost=0.03..0.03 rows=2
width=4) (actual time=0.017..0.018 rows=1.00 loops=1) Buckets: 1024
Batches: 1 Memory Usage: 9kB -> Values Scan on "*VALUES*"
(cost=0.00..0.03 rows=2 width=4) (actual time=0.005..0.008 rows=2.00
loops=1) Planning Time: 0.312 ms Execution Time: 0.174 ms (10 rows)

Since the subquery became correlated with our transformation when it
included var objects, I also checked whether our transformation had any
negative impact on query performance.

To do this, I added unique values to the table, forcing the subquery to
be re-executed for each outer tuple. I observed a little performance
degradation (see the number of shared hit in a query 2.1) and
the worst scenario involving nested VALUES clauses, where the
performance impact is substantial (a query 2.2).

As for the 2.2 query, without our transformation, the query requires
scanning significantly fewer blocks - roughly half as many (see the
number of shared hit).

insert into onek select id, id, 'match' from generate_series(1,10000) id;

2.1) explain analyze SELECT ten FROM onek t WHERE unique1 IN ( VALUES
(0), ((2 IN ( SELECT unique2 FROM onek c WHERE c.unique2 = t.unique1
))::integer) );

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Seq Scan on onek t (cost=0.00..901463.05 rows=51 width=6) (actual
time=5.703..15131.084 rows=1.00 loops=1) Filter: (unique1 = ANY
(ARRAY[0, ((ANY (2 = (SubPlan 1).col1)))::integer])) Rows Removed by
Filter: 10007 *Buffers: shared hit=550389* SubPlan 1 **-> Seq Scan on
onek c (cost=0.00..180.10 rows=1 width=4) (actual time=0.818..1.511
rows=1.00 loops=10008) Filter: (unique2 = t.unique1) Rows Removed by
Filter: 10005 *Buffers: shared hit=550334* Planning Time: 0.279 ms
Execution Time: 15131.148 ms (11 rows)

The query plan without our patch:

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Nested Loop Semi Join (cost=0.00..385.00 rows=66 width=32) (actual
time=0.099..19935.638 rows=1.00 loops=1) Buffers: shared hit=550334 ->
Seq Scan on onek t (cost=0.00..121.00 rows=6600 width=36) (actual
time=0.082..1.723 rows=10008.00 loops=1) Buffers: shared hit=55 ->
Values Scan on "*VALUES*" (cost=0.00..0.03 rows=1 width=4) (actual
time=1.991..1.991 rows=0.00 loops=10008) Filter: (t.unique1 = column1)
Rows Removed by Filter: 2 *Buffers: shared hit=550279* SubPlan 1 -> Seq
Scan on onek c (cost=0.00..137.50 rows=33 width=4) (actual
time=1.106..1.989 rows=1.00 loops=10007) Filter: (unique2 = t.unique1)
Rows Removed by Filter: 10005 *Buffers: shared hit=550279* Planning
Time: 0.393 ms Execution Time: 19935.710 ms (15 rows)

2.2) explain analyze SELECT ten FROM onek t WHERE unique1 IN ( VALUES
(0), ((2 IN ( SELECT unique2 FROM onek c WHERE c.unique2 in
((values(0),((2 in (select unique2 from onek c1 where c1.unique2 =
t.unique1))::integer))) ))::integer) );

--------------------------------------------------------------------------------------------------------------------------
Seq Scan on onek t (cost=0.00..2954341.54 rows=51 width=6) (actual
time=8.095..93017.686 rows=1.00 loops=1) Filter: (unique1 = ANY
(ARRAY[0, ((ANY (2 = (SubPlan 2).col1)))::integer])) Rows Removed by
Filter: 10007 Buffers: shared hit=1100935 SubPlan 2 -> Seq Scan on onek
c (cost=180.10..410.24 rows=2 width=4) (actual time=9.290..9.292
rows=0.00 loops=10008) Filter: (unique2 = ANY (ARRAY[0, ((ANY (2 =
(hashed SubPlan 1).col1)))::integer])) Rows Removed by Filter: 10008
*Buffers: shared hit=1100880* SubPlan 1 -> Seq Scan on onek c1
(cost=0.00..180.10 rows=1 width=4) (actual time=1.183..2.291 rows=1.00
loops=10008) Filter: (unique2 = t.unique1) Rows Removed by Filter: 10007
Buffers: shared hit=550440 Planning: Buffers: shared hit=19 Planning
Time: 0.733 ms *Execution Time: 93017.795 ms* (18 rows)

The query plan without our patch:

--------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Semi Join (cost=0.00..555.40 rows=2 width=6) (actual
time=0.046..50110.402 rows=1.00 loops=1) Buffers: shared hit=1100825 ->
Seq Scan on onek t (cost=0.00..155.08 rows=10008 width=10) (actual
time=0.028..1.374 rows=10008.00 loops=1) Buffers: shared hit=55 ->
Values Scan on "*VALUES*" (cost=0.00..0.03 rows=1 width=4) (actual
time=5.006..5.006 rows=0.00 loops=10008) Filter: (t.unique1 = column1)
Rows Removed by Filter: 2 Buffers: shared hit=1100770 SubPlan 2 -> Hash
Semi Join (cost=0.05..181.42 rows=2 width=4) (actual time=5.003..5.003
rows=0.00 loops=10007) Hash Cond: (c.unique2 = "*VALUES*_1".column1)
Buffers: shared hit=1100770 -> Seq Scan on onek c (cost=0.00..155.08
rows=10008 width=4) (actual time=0.004..1.165 rows=10008.00 loops=10007)
*Buffers: shared hit=550385* -> Hash (cost=0.03..0.03 rows=2 width=4)
(actual time=1.921..1.921 rows=2.00 loops=10007) Buckets: 1024 Batches:
1 Memory Usage: 9kB Buffers: shared hit=550385 -> Values Scan on
"*VALUES*_1" (cost=0.00..0.03 rows=2 width=4) (actual time=0.000..1.920
rows=2.00 loops=10007) Buffers: shared hit=550385 SubPlan 1 -> Seq Scan
on onek c1 (cost=0.00..180.10 rows=1 width=4) (actual time=1.010..1.917
rows=1.00 loops=10007) Filter: (unique2 = t.unique1) Rows Removed by
Filter: 10007 Buffers: shared hit=550385 Planning: Buffers: shared hit=6
Planning Time: 0.874 ms *Execution Time: 50110.531 ms* (28 rows)

If we build an index, the number of scanned blocks remains the same or
even decreases, so I don't observe any performance degradation in that case.

Does this mean that we should consider applying this transformation
later, perhaps where the OR->ANY transformation is performed, at least
for cases where VALUES clauses contain subqueries or Var nodes?

create index on onek (unique2);

3.1)explain analyze SELECT ten FROM onek t WHERE unique1 IN ( VALUES
(0), ((2 IN ( SELECT unique2 FROM onek c WHERE c.unique2 = t.unique1
))::integer) );

---------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on onek t (cost=0.00..23198.50 rows=51 width=6) (actual
time=0.142..60.369 rows=1.00 loops=1) Filter: (unique1 = ANY (ARRAY[0,
((ANY (2 = (SubPlan 1).col1)))::integer])) Rows Removed by Filter: 10007
*Buffers: shared hit=20070* SubPlan 1 -> Index Only Scan using
onek_unique2_idx on onek c (cost=0.29..4.30 rows=1 width=4) (actual
time=0.004..0.005 rows=1.00 loops=10008) Index Cond: (unique2 =
t.unique1) Heap Fetches: 0 *Index Searches: 10007* *Buffers: shared
hit=20015* Planning: Buffers: shared hit=121 Planning Time: 2.426 ms
Execution Time: 60.512 ms (14 rows)

The query plan without our patch:

---------------------- Nested Loop Semi Join (cost=0.00..555.40 rows=2
width=6) (actual time=0.118..59.554 rows=1.00 loops=1) *Buffers: shared
hit=19983 read=85* -> Seq Scan on onek t (cost=0.00..155.08 rows=10008
width=10) (actual time=0.090..1.834 rows=10008.00 loops=1) *Buffers:
shared read=55* -> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=1
width=4) (actual time=0.005..0.005 rows=0.00 loops=10008) Filter:
(t.unique1 = column1) Rows Removed by Filter: 2 *Buffers: shared
hit=19983 read=30* SubPlan 1 -> Index Only Scan using onek_unique2_idx
on onek c (cost=0.29..4.30 rows=1 width=4) (actual time=0.004..0.004
rows=1.00 loops=10007) Index Cond: (unique2 = t.unique1) Heap Fetches: 0
*Index Searches: 10006* *Buffers: shared hit=19983 read=30* Planning:
Buffers: shared hit=120 read=24 Planning Time: 3.731 ms Execution Time:
59.644 ms (18 rows)

3.2) explain analyze SELECT ten FROM onek t WHERE unique1 IN ( VALUES
(0), ((2 IN ( SELECT unique2 FROM onek c WHERE c.unique2 in
((values(0),((2 in (select unique2 from onek c1 where c1.unique2 =
t.unique1))::integer))) ))::integer) );

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Semi Join (cost=0.00..555.40 rows=2 width=6) (actual
time=0.055..131.421 rows=1.00 loops=1) *Buffers: shared hit=40090* ->
Seq Scan on onek t (cost=0.00..155.08 rows=10008 width=10) (actual
time=0.038..1.775 rows=10008.00 loops=1) *Buffers: shared hit=55* ->
Values Scan on "*VALUES*" (cost=0.00..0.03 rows=1 width=4) (actual
time=0.013..0.013 rows=0.00 loops=10008) Filter: (t.unique1 = column1)
Rows Removed by Filter: 2 *Buffers: shared hit=40035* SubPlan 2 ->
Nested Loop (cost=0.32..8.67 rows=2 width=4) (actual time=0.012..0.012
rows=0.00 loops=10007) *Buffers: shared hit=40035* -> Unique
(cost=0.04..0.04 rows=2 width=4) (actual time=0.008..0.008 rows=1.00
loops=10007) *Buffers: shared hit=20016* -> Sort (cost=0.04..0.04 rows=2
width=4) (actual time=0.007..0.007 rows=2.00 loops=10007) Sort Key:
"*VALUES*_1".column1 Sort Method: quicksort Memory: 25kB *Buffers:
shared hit=20016* -> Values Scan on "*VALUES*_1" (cost=0.00..0.03 rows=2
width=4) (actual time=0.000..0.005 rows=2.00 loops=10007) *Buffers:
shared hit=20013* SubPlan 1 -> Index Only Scan using onek_unique2_idx on
onek c1 (cost=0.29..4.30 rows=1 width=4) (actual time=0.004..0.004
rows=1.00 loops=10007) Index Cond: (unique2 = t.unique1) Heap Fetches: 0
*Index Searches: 10006* *Buffers: shared hit=20013* -> Index Only Scan
using onek_unique2_idx on onek c (cost=0.29..4.30 rows=1 width=4)
(actual time=0.003..0.003 rows=0.00 loops=10009) Index Cond: (unique2 =
"*VALUES*_1".column1) Heap Fetches: 0 *Index Searches: 10009* *Buffers:
shared hit=20019* Planning: Buffers: shared hit=10 Planning Time: 1.183
ms Execution Time: 131.616 ms (34 rows)

The query plan without our patch:

------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on onek t (cost=0.00..87875.20 rows=51 width=6) (actual
time=0.217..174.053 rows=1.00 loops=1) Filter: (unique1 = ANY (ARRAY[0,
((ANY (2 = (SubPlan 2).col1)))::integer])) Rows Removed by Filter: 10007
*Buffers: shared hit=40002 read=85* SubPlan 2 -> Index Only Scan using
onek_unique2_idx on onek c (cost=4.60..12.92 rows=2 width=4) (actual
time=0.007..0.007 rows=0.00 loops=10008) Index Cond: (unique2 = ANY
(ARRAY[0, ((ANY (2 = (hashed SubPlan 1).col1)))::integer])) Heap
Fetches: 0 *Index Searches: 10008* *Buffers: shared hit=20017* SubPlan 1
-> Index Only Scan using onek_unique2_idx on onek c1 (cost=0.29..4.30
rows=1 width=4) (actual time=0.006..0.006 rows=1.00 loops=10008) Index
Cond: (unique2 = t.unique1) Heap Fetches: 0 *Index Searches: 10007*
*Buffers: shared hit=19985 read=30* Planning: Buffers: shared hit=91
read=25 Planning Time: 2.858 ms Execution Time: 174.233 ms (20 rows)

I don't observe any performance degradation if VALUES contains constants.

4.1) explain analyze SELECT ten FROM onek t WHERE unique1 IN ( VALUES
(0), ((2 IN ( SELECT unique2 FROM onek c WHERE c.unique2 in
((values(0),(2))))::integer)) );

QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on onek t (cost=180.11..410.25 rows=2 width=6) (actual
time=5.014..13.256 rows=3.00 loops=1) Filter: (unique1 = ANY (ARRAY[0,
((ANY (2 = (hashed SubPlan 1).col1)))::integer])) Rows Removed by
Filter: 10005 *Buffers: shared hit=110* SubPlan 1 -> Seq Scan on onek c
(cost=0.00..180.10 rows=3 width=4) (actual time=0.022..4.951 rows=2.00
loops=1) Filter: (unique2 = ANY ('{0,2}'::integer[])) Rows Removed by
Filter: 10006 *Buffers: shared hit=55* Planning: Buffers: shared hit=6
dirtied=1 Planning Time: 0.502 ms Execution Time: 13.348 ms (13 rows)

The query plan without our patch:

--------------------------------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=0.05..181.42 rows=2 width=6) (actual
time=5.072..9.076 rows=3.00 loops=1) Hash Cond: (t.unique1 =
"*VALUES*".column1) *Buffers: shared hit=55 read=55* -> Seq Scan on onek
t (cost=0.00..155.08 rows=10008 width=10) (actual time=0.145..1.802
rows=10008.00 loops=1) *Buffers: shared hit=52 read=3* -> Hash
(cost=0.03..0.03 rows=2 width=4) (actual time=4.908..4.912 rows=2.00
loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB *Buffers: shared
hit=3 read=52* -> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2
width=4) (actual time=0.003..4.901 rows=2.00 loops=1) *Buffers: shared
hit=3 read=52* SubPlan 1 -> Hash Semi Join (cost=0.05..181.42 rows=2
width=4) (actual time=0.036..4.861 rows=2.00 loops=1) Hash Cond:
(c.unique2 = "*VALUES*_1".column1) *Buffers: shared hit=3 read=52* ->
Seq Scan on onek c (cost=0.00..155.08 rows=10008 width=4) (actual
time=0.009..2.120 rows=10008.00 loops=1) *Buffers: shared hit=3 read=52*
-> Hash (cost=0.03..0.03 rows=2 width=4) (actual time=0.006..0.008
rows=2.00 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Values
Scan on "*VALUES*_1" (cost=0.00..0.03 rows=2 width=4) (actual
time=0.001..0.002 rows=2.00 loops=1) Planning: Buffers: shared hit=102
read=22 Planning Time: 1.853 ms Execution Time: 9.281 ms (23 rows)

4.2) explain analyze SELECT ten FROM onek t WHERE unique1 IN ( VALUES
(0),(2) );

QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on onek t (cost=0.00..180.10 rows=3 width=6) (actual
time=0.200..3.777 rows=3.00 loops=1) Filter: (unique1 = ANY
('{0,2}'::integer[])) Rows Removed by Filter: 10005 *Buffers: shared
read=55* Planning: Buffers: shared hit=65 read=26 Planning Time: 1.345
ms Execution Time: 3.826 ms (8 rows)

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=0.05..181.42 rows=2 width=6) (actual
time=0.094..4.935 rows=3.00 loops=1) Hash Cond: (t.unique1 =
"*VALUES*".column1) *Buffers: shared hit=55* -> Seq Scan on onek t
(cost=0.00..155.08 rows=10008 width=10) (actual time=0.056..2.052
rows=10008.00 loops=1) *Buffers: shared hit=55* -> Hash (cost=0.03..0.03
rows=2 width=4) (actual time=0.019..0.019 rows=2.00 loops=1) Buckets:
1024 Batches: 1 Memory Usage: 9kB -> Values Scan on "*VALUES*"
(cost=0.00..0.03 rows=2 width=4) (actual time=0.005..0.009 rows=2.00
loops=1) Planning Time: 0.332 ms Execution Time: 4.998 ms (10 rows)

--
Regards,
Alena Rybakina
Postgres Professional