A multi-column IN predicate is a convenient way to perform a batch of point lookups:
SELECT item_id,
description,
category_id,
state_code,
lookup_hash,
version,
payload
FROM lookup_item
WHERE (lookup_hash, category_id) IN (
($1, $2),
($3, $4),
($5, $6),
...
($99, $100)
);
There are two related performance considerations with this pattern.
The first is statement reuse.
If the application generates a different number of placeholders depending on the batch size, then the SQL statement itself changes:
- ● 5 pairs → 10 parameters
- ● 25 pairs → 50 parameters
- ● 50 pairs → 100 parameters
A statement with 10 parameters is not the same prepared statement as one with 100 parameters. Applications or drivers that cache prepared statements by SQL shape may therefore accumulate multiple versions of what is logically the same lookup.
The second consideration is planning cost.
When the application submits a statement shape that does not have a reusable prepared plan, YSQL must perform planning work for that statement.
In the eight-pair example used in this Tip, YugabyteDB produced eight separate Bitmap Index Scan branches underneath a BitmapOr.
That eight-pair example is useful for visualizing how the expression grows, but it is not intended to prove a meaningful planning-time difference by itself.
In one larger observed workload using 50 tuple pairs and 100 bind parameters, mean planning time was approximately 25 ms, with individual planning times exceeding 200 ms.
The goal of this Tip is therefore not to claim that every large IN list is slow. It is to show how an array-based UNNEST pattern can keep the SQL statement fixed at two parameters, making prepared-statement reuse much easier and avoiding continually generating larger statement shapes.
UNNEST does more than shorten the SQL. It lets the application use the
same two-parameter statement regardless of whether the batch contains 5, 50, or 500
lookup pairs. That makes prepared-statement reuse much easier and avoids continually
introducing new SQL shapes that may need to be planned separately.
Why Large Tuple Lists Add Planner Work
The eight-pair example is intentionally small, but it makes the optimizer work easy to visualize. Each tuple can contribute another index condition and, in this plan, another Bitmap Index Scan branch under the BitmapOr.
Now imagine the same query shape with 50 tuple pairs:
The exact plan varies based on the schema, statistics, indexes, YugabyteDB version, and batch size. The important characteristic is that the amount of SQL and optimizer work can grow with the number of tuple pairs.
A better approach is to keep the SQL shape fixed and pass the changing lookup values as data instead of continually making the SQL expression larger.
Example
The following anonymized table reproduces the query pattern without using an application-specific schema:
DROP TABLE IF EXISTS lookup_item;
CREATE TABLE lookup_item (
item_id bigint NOT NULL,
category_id smallint NOT NULL,
version smallint NOT NULL,
lookup_hash varchar(40) NOT NULL,
description varchar(150),
payload text NOT NULL,
state_code char(1) NOT NULL,
PRIMARY KEY ((lookup_hash) HASH, category_id ASC, state_code ASC)
);
Populate it with 10,000 rows:
INSERT INTO lookup_item
(item_id, category_id, version, lookup_hash, description, payload, state_code)
SELECT
g,
(g % 100)::smallint,
1,
lpad(g::text, 40, '0'),
'Sample Item ' || g,
'{"value":"sample"}',
'A'
FROM generate_series(1, 10000) AS g;
ANALYZE lookup_item;
The test table contains 10,000 rows and uses a hash-partitioned first primary-key column followed by clustering columns.
Baseline: Multi-Column IN
To make the behavior easy to see, start with eight lookup pairs:
EXPLAIN (ANALYZE, DIST)
SELECT item_id,
description,
category_id,
state_code,
lookup_hash,
version,
payload
FROM lookup_item
WHERE (lookup_hash, category_id) IN (
('0000000000000000000000000000000000001001', 1),
('0000000000000000000000000000000000001002', 2),
('0000000000000000000000000000000000001003', 3),
('0000000000000000000000000000000000001004', 4),
('0000000000000000000000000000000000001005', 5),
('0000000000000000000000000000000000001006', 6),
('0000000000000000000000000000000000001007', 7),
('0000000000000000000000000000000000001008', 8)
);
The observed plan started with:
-> BitmapOr
-> Bitmap Index Scan on lookup_item_pkey
-> Bitmap Index Scan on lookup_item_pkey
-> Bitmap Index Scan on lookup_item_pkey
-> Bitmap Index Scan on lookup_item_pkey
-> Bitmap Index Scan on lookup_item_pkey
-> Bitmap Index Scan on lookup_item_pkey
-> Bitmap Index Scan on lookup_item_pkey
-> Bitmap Index Scan on lookup_item_pkey
Each tuple contributed a separate Bitmap Index Scan branch underneath the BitmapOr.
The purpose of this small example is not to benchmark planning time. It is to make the shape of the plan easy to visualize. As more tuple pairs are added, YSQL has more predicates and potential index conditions to process when constructing the plan.
IN list can expand
into multiple planner branches. It is intentionally small and should not be interpreted
as a planning-time benchmark. The more important question is what happens as the application
generates much larger and continually changing statement shapes.
IN predicates can be very fast. The concern is large,
application-generated lists where the SQL expression and number of bind parameters continue
growing with the batch size.
Why the Changing SQL Shape Matters
Suppose an application dynamically builds the predicate based on the requested batch size.
The application isn’t only passing more data. It is generating a different SQL statement shape for each batch size.
That distinction matters for prepared statements. A statement containing 10 bind parameters is not the same statement shape as one containing 50 or 100 bind parameters. An application or driver that caches prepared statements may therefore need to maintain multiple prepared versions of what is logically the same lookup.
A Better Pattern: Keep the SQL Stable with UNNEST
Instead of constructing a different list of placeholders for every batch size, pass the lookup values as two arrays and expand them with UNNEST:
SELECT l.item_id,
l.description,
l.category_id,
l.state_code,
l.lookup_hash,
l.version,
l.payload
FROM lookup_item l
JOIN UNNEST(
$1::varchar[],
$2::smallint[]
) AS batch(hash_val, category_val)
ON l.lookup_hash = batch.hash_val
AND l.category_id = batch.category_val;
The statement now always has exactly two parameters:
- ●
$1= array oflookup_hashvalues - ●
$2= array ofcategory_idvalues
Whether the application looks up 5 rows, 50 rows, or 500 rows, the SQL text and parameter count remain unchanged. Only the contents of the two arrays change.
That gives the application one stable statement shape that can be prepared and reused across different batch sizes.
UNNEST, the number of lookup values can change without changing the SQL
text or the number of bind parameters. That makes the same prepared statement reusable
across different batch sizes instead of continually introducing new statement shapes that
may need to be prepared and planned separately.
How the Two Arrays Turn Back Into Rows
You can think of UNNEST here as zipping two arrays together.
The first element from each array becomes the first lookup row, the second element from each array becomes the second lookup row, and so on.
UNNEST walks the arrays in parallel:
element 1 from each array becomes row 1,
element 2 becomes row 2, and so on.
The lookup pairs are preserved… only the way they are passed into the query changes.
The Fixed-Shape UNNEST Version
Using the same eight lookup keys:
EXPLAIN (ANALYZE, DIST)
SELECT l.item_id,
l.description,
l.category_id,
l.state_code,
l.lookup_hash,
l.version,
l.payload
FROM lookup_item l
JOIN UNNEST(
ARRAY[
'0000000000000000000000000000000000001001',
'0000000000000000000000000000000000001002',
'0000000000000000000000000000000000001003',
'0000000000000000000000000000000000001004',
'0000000000000000000000000000000000001005',
'0000000000000000000000000000000000001006',
'0000000000000000000000000000000000001007',
'0000000000000000000000000000000000001008'
]::varchar[],
ARRAY[
1,
2,
3,
4,
5,
6,
7,
8
]::smallint[]
) AS batch(hash_val, category_val)
ON l.lookup_hash = batch.hash_val
AND l.category_id = batch.category_val;
The important difference is not the planning time of this small eight-row test. It is the shape of the statement.
The IN version grows as more tuple pairs are added. The UNNEST version does not.
In an application, the same SQL can remain:
JOIN UNNEST(
$1::varchar[],
$2::smallint[]
) AS batch(hash_val, category_val)
whether the arrays contain 5 elements, 50 elements, or 500 elements.
Only the parameter values change… the SQL text and parameter count remain constant.
UNNEST rewrite in this Tip is not to prove that one
small query plans a fraction of a millisecond faster than another. The important
change is that the same two-parameter SQL statement can be reused across many
different batch sizes.
Why UNNEST Keeps the Statement Stable
The advantage of UNNEST is not that it magically makes every query plan faster.
It changes where the variable-sized data lives.
With a tuple IN list, the changing batch size is represented directly in the SQL expression itself.
As N increases, the statement contains more tuple expressions and more placeholders.
With UNNEST, the variable-sized data moves into the parameter values instead:
$1::varchar[]
$2::smallint[]
The SQL statement itself stays unchanged. That makes it much easier for the application or driver to prepare and reuse one statement instead of maintaining many versions for different batch sizes.
Why Prepared Statement Reuse Matters
Prepared statements can amortize planning work when the same statement is executed repeatedly.
But the statement has to remain reusable.
Consider an application dynamically generating:
- ● 25 pairs → 50 parameters
- ● 50 pairs → 100 parameters
- ● 75 pairs → 150 parameters
Those are different statement shapes.
A statement prepared for 50 placeholders cannot simply be reused as the 100-placeholder version. Depending on the application, driver, and statement-cache behavior, multiple prepared versions may need to be created and maintained.
With UNNEST:
JOIN UNNEST(
$1::varchar[],
$2::smallint[]
)
the statement always accepts the same two parameters.
Only the number of elements inside the arrays changes.
UNNEST makes it much
easier to reuse one prepared statement across those batch sizes.
Why Not Just Use VALUES?
A VALUES relation is another useful way to express the lookup as rows:
SELECT l.item_id,
l.description,
l.category_id,
l.state_code,
l.lookup_hash,
l.version,
l.payload
FROM lookup_item l
JOIN (
VALUES
($1, $2),
($3, $4),
($5, $6),
...
) AS batch(hash_val, category_val)
ON l.lookup_hash = batch.hash_val
AND l.category_id = batch.category_val;
However, from the perspective of statement reuse, VALUES still has the same basic limitation as the original tuple IN list: the SQL grows as the batch grows.
When the goal is to keep one reusable SQL shape across variable batch sizes, UNNEST is the better fit.
Will Catalog Precaching Fix This?
Catalog caching and catalog precaching solve a different problem.
They reduce distributed reads needed to populate PostgreSQL system-catalog metadata for a YSQL backend.
They do not make a large tuple expression smaller.
In a warmed session, a query may show:
Catalog Read Requests: 0
Catalog Read Ops: 0
…while YSQL still has to parse, transform, cost, and construct the execution plan.
The two issues are different:
IN expression. If catalog reads are already zero, additional catalog caching will not reduce the size or complexity of the application-generated SQL expression.How to Test Your Application
Use:
EXPLAIN (ANALYZE, DIST)
and compare realistic batch sizes such as:
- ● 5 pairs
- ● 25 pairs
- ● 50 pairs
- ● 100 pairs
Run each query several times in the same YSQL session so initial catalog activity does not dominate the results.
Focus on:
Planning Time
Catalog Read Requests
Statement / parameter shape
The main question for this Tip is:
- ● Does the application repeatedly incur planning work because it keeps generating new SQL shapes?
- ● At realistic batch sizes, does that planning cost become material to the workload?
Test representative sizes such as 5, 25, 50, and 100 pairs. Run repeated executions in a warmed session so initial catalog activity does not dominate the results.
Then compare that behavior with the fixed-shape UNNEST($1, $2) statement.
The Same Rewrite Can Also Help Execution
In the companion Tip, Reduce YSQL Execution Latency by Batching Multi-Row Point Lookups, we’ll look at another benefit of the same rewrite:
UNNEST and VALUES can give YugabyteDB a join shape that enables Batched Nested Loop execution and can reduce distributed storage requests.Final Takeaway
If an application dynamically generates a large multi-column IN predicate:
WHERE (lookup_hash, category_id) IN (
($1,$2),
($3,$4),
...
($99,$100)
)
… the statement becomes increasingly complex as the batch grows.
UNNEST($1, $2)IN lists, consider passing the lookup keys as arrays and expanding
them with UNNEST. The statement remains fixed at two parameters
regardless of batch size, making prepared-statement reuse much easier and avoiding
a growing collection of SQL shapes that may each require separate planning.
Have Fun!
