Re: Replace IN VALUES with ANY in WHERE clauses during optimization - Mailing list pgsql-hackers

Hi, Alexander!

Hi, Alena!

On Sat, Mar 29, 2025 at 9:03 PM Alena Rybakina
<a.rybakina@postgrespro.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