Tuple comparison and Multi-column IN

Introduction

In PostgreSQL, Tuple comparison (row/tuple comparison) and Multi-column IN (IN condition on multiple columns) are two extremely powerful features that help write concise, clearer SQL queries and significantly optimize performance compared to manually chaining multiple AND/OR conditions.

Tuple comparison

Tuple comparison allows grouping multiple columns or values into a tuple (using parentheses (...)) and comparing these two tuples directly with each other using operators like =, <>, <, >, <=, >=
Used for Keyset Pagination with more than 1 column and sorting applied

Lexicographical order

PostgreSQL compares elements from left to right, similar to dictionary sorting, starting by comparing the first pair of elements.

If they differ, the result of the entire comparison is decided immediately without evaluating subsequent columns
If they are equal, it proceeds to compare the next pair of elements, continuing this process until the end.

General Example

(a, b) < (x, y) is equivalent to (a < x) or (a = x and b < y)
If a <> x, the result is decided immediately as follows
- a > x yields FALSE
- a < x yields TRUE
If a = x, PostgreSQL proceeds to compare the second column (b and y).
- b >= x yields FALSE
- b < x yields TRUE

Specific Example

(1, 100) < (2, 1) = TRUE (because 1 < 2, ignoring 100 and 1)
(1, 5) < (1, 10) = TRUE (because 1 = 1, proceeding to compare 5 < 10)
('2026-03-01', 105) > ('2026-03-01', 100) = TRUE (dates are equal, comparing ID 105 > 100).

Usage with Index

If you use Tuple comparison for 2 columns but only create an Index on 1 column, Postgres can use the Index on that column to narrow down the range first
Afterwards, it must Filter using Seq Scan for the remaining column
A more efficient approach is to use a Composite Index for the required columns

Multi-column IN

Commonly used when you want to check whether a tuple/pair of values from the current row exists within a list of given tuples.
Used when identifying data requires 2 or more columns, where applying IN individually to each column would lead to incorrect results

For example, instead of writing verbose code that can be error-prone and hard to read

WHERE (student_id = 101 AND course_id = 5)
   OR (student_id = 102 AND course_id = 8)
   OR (student_id = 105 AND course_id = 3)

You can write it concisely using Literal Values like this

WHERE (student_id, course_id) IN (
    (101, 5),
    (102, 8),
    (105, 3)
);

Or using a Subquery like this

WHERE (customer_id, created_at) IN (
    SELECT customer_id, MAX(created_at)
    FROM orders
    GROUP BY customer_id
);

Usage with Index

Using separate indexes for each column is not as efficient as a Composite Index
It also utilizes common algorithms such as
- Hash Semi-Join: Most common for medium to large datasets
- Nested Loop Semi-Join: Used when the dataset is small or a Composite Index exists
- Merge Semi-Join: Used when both datasets are already sorted

Limitations with NULL

This is a limitation when used with IN or NOT IN because comparing a NULL value always results in Unknown
For example, checking (col1, col2) = (100, NULL) results in Unknown and returns no data
If your data contains NULL and you want NULL = NULL to evaluate as equal (or eliminate the risks of IN), apply the following methods:
- Use IS NOT NULL to filter out NULL in the Subquery beforehand
- Use IS NOT DISTINCT FROM, an operator in Postgres that treats comparing NULL with NULL as TRUE
- Switch to EXISTS instead of IN

Detail

First, create the Table and Indexes as follows

CREATE TABLE IF NOT EXISTS orders (
    order_id BIGSERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(30) NOT NULL,
    payment_method VARCHAR(30) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    total_amount NUMERIC(12, 2) NOT NULL
);

CREATE INDEX idx_orders_created_id ON orders (created_at DESC, order_id DESC);

CREATE INDEX idx_orders_status_pay_partial ON orders (status, payment_method) WHERE total_amount > 600;

Tuple comparison

-- Query 1
SELECT order_id, customer_id, status, created_at, total_amount
FROM orders
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

-- Query 2
SELECT order_id, customer_id, status, created_at, total_amount
FROM orders
WHERE (created_at, order_id) < ('2026-07-28 02:27:53.701779+00', 75182)
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

-- Query 3
SELECT order_id, customer_id, status, created_at, total_amount
FROM orders
WHERE created_at < '2026-07-28 02:27:53.701779+00'
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

-- Query 4
SELECT order_id, customer_id, status, created_at, total_amount
FROM orders
WHERE order_id < 75182
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

-- Query 5
SELECT order_id, customer_id, status, created_at, total_amount
FROM orders
WHERE created_at < '2026-07-28 02:27:53.701779+00'
   OR (created_at = '2026-07-28 02:27:53.701779+00' AND order_id < 75182)
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

This is the most common use case in Keyset Pagination (also known as Cursor Pagination)
Usually, a single column key is sufficient, but when applying ORDER BY across multiple columns, comparing only 1 column will lead to fetching incorrect data

Query 1: This is the initial query used to retrieve 20 records sorted in descending order by created_at and order_id

Query 2: This query uses Tuple comparison to retrieve the next 20 records by passing created_at and order_id obtained from the last record of Query 1, efficiently utilizing the Composite Index

Query 3: This query yields incorrect results because it only compares created_at, which is not a Primary Key, causing rows with identical timestamps to be missed

Query 4: This query also yields incorrect results because it only compares order_id. Although it is the Primary Key, it is used alongside ORDER BY order_id DESC, meaning filtering by order_id < 91084 can produce duplicate results already fetched on Page 1

Query 5: This query is rewritten without Tuple Comparison, using standard comparison with AND/OR instead. It still yields correct results and utilizes the Composite Index, but its syntax is more verbose and less clear compared to using Tuple Comparison

Multi-column IN

-- Query 1
SELECT order_id, customer_id, status, payment_method
FROM orders
WHERE (status = 'PENDING' AND payment_method = 'COD')
   OR (status = 'PROCESSING' AND payment_method = 'CREDIT_CARD');

-- Query 2
SELECT order_id, customer_id, status, payment_method
FROM orders
WHERE (status, payment_method) IN (
    ('PENDING', 'COD'),
    ('PROCESSING', 'CREDIT_CARD')
);

-- Query 3
SELECT order_id, customer_id, status, payment_method
FROM orders
WHERE (status, payment_method) NOT IN (
    SELECT status, payment_method
    FROM orders
    WHERE total_amount > 600
);

-- Query 4
SELECT order_id, customer_id, status, payment_method
FROM orders
WHERE (status, payment_method) NOT IN (
    SELECT status, payment_method
    FROM orders
    WHERE total_amount > 600
      AND status IS NOT NULL 
      AND payment_method IS NOT NULL
);

-- Query 5
SELECT o.order_id, o.customer_id, o.status, o.payment_method
FROM orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM orders sub
    WHERE sub.total_amount > 600
      AND (sub.status, sub.payment_method) IS NOT DISTINCT FROM (o.status, o.payment_method)
);

Query 1: Standard query using only AND/OR to compare multiple columns simultaneously

Query 2: Uses Multi-column IN with simpler syntax, utilizes the Index and yields the same result as Query 1

Query 3: Uses NOT IN without IS NOT NULL constraints. If a record has NULL for both status and payment_method, the equivalence transformation of IN to AND for each row causes the result set to be empty

Query 4: Correct usage with IS NOT NULL to guarantee no rows in the Subquery contain NULL

Query 5: Equivalent to Query 4 but uses NOT EXISTS and IS NOT DISTINCT FROM

NOT EXISTS prevents empty results when NULL values exist in the Subquery
IS NOT DISTINCT FROM compares NULL with NULL as TRUE rather than Unknown as in standard comparisons (NULL=NULL)
This query utilizes Nested Loop Anti Join alongside an Index instead of Seq Scan seen in Query 4