| From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Cc: | allen2hsu(at)gmail(dot)com |
| Subject: | BUG #19917: nbtree skip scan leaks one copy of a pass-by-reference skip array element per rescan |
| Date: | 2026-11-19 00:37:24 |
| Message-ID: | 19917-61cc2f6823e22810@postgresql.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 19917
Logged by: Allen Hsu
Email address: allen2hsu(at)gmail(dot)com
PostgreSQL version: 18.6
Operating system: Debian 13 (official Docker image postgres:18), aar
Description:
SUMMARY
When a B-tree index scan uses a skip array on a column whose type is
passed by reference (uuid, text), and the scan is rescanned (the inner
side of a nested loop, for example), each rescan leaves one copy of the
skip array's current element allocated in the caller's memory context.
In the executor that context is the query's ExecutorState, so the memory
is held until the statement ends. It grows with the number of rescans
and is not limited by work_mem. With a uuid column it is 24 bytes per
rescan: 500,000 rescans hold 12 MB.
VERSION
PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on aarch64-unknown-linux-gnu,
compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit. Official Docker image
postgres:18, default configuration. The code in question reads the same
at the REL_18_7 tag, on REL_18_STABLE and on master (where it now lives
in nbtreadpage.c).
STEPS TO REPRODUCE
Run the script at the end of this report with psql in an empty database.
It needs no extension and no changed server settings. It sets a few
planner enable_* settings in its own session, only to get the nested
loop plan on a small table. It builds a 500,000-row table with a unique
index on (lead uuid, id uuid, other uuid) where "lead" holds one value,
and joins to it by "id" alone under a nested loop:
Aggregate
-> Nested Loop
-> Limit
-> Seq Scan on skip_outer
-> Index Only Scan using skip_inner_lead_id_other on skip_inner i
Index Cond: (id = skip_outer.id)
It reads the used bytes of the backend's ExecutorState contexts from
pg_backend_memory_contexts at the end of the same query.
OUTPUT I GOT
Case rescans ExecutorState bytes
A. skip scan over "lead" (uuid) 1 111,240
A. 125,000 3,113,496
A. 250,000 6,113,536
A. 500,000 12,113,576
B. same lookups, "i.lead = o.lead" added 1 111,384
B. (no skip array) 500,000 113,176
C. as A with "lead integer" (pass by value) 1 111,216
C. 500,000 113,216
Case A grows by exactly 24.0 bytes per rescan (a 16-byte uuid and its
chunk header). With a 32-character text leading column I measured 72
bytes per rescan. In a real query of ours a nested loop of about 213,000
such lookups held 5 MB, and the same plan shape over a larger outer side
would hold tens of megabytes.
OUTPUT I EXPECTED
ExecutorState stays flat with the number of rescans, as it does in cases
B and C.
WHAT I THINK HAPPENS (from reading the source; I have not tested a patch)
_bt_skiparray_set_element (nbtutils.c) stores the element with
/* Free memory previously allocated for sk_argument if needed */
if (!array->attbyval && skey->sk_argument)
pfree(DatumGetPointer(skey->sk_argument));
...
skey->sk_argument = datumCopy(tupdatum, array->attbyval, array->attlen);
The copy is made in CurrentMemoryContext, not in so->arrayContext.
_bt_array_increment and _bt_array_decrement allocate the same way.
Within one scan the previous copy is freed as the array advances. On a
rescan, btrescan copies the caller's keys into scan->keyData and zeroes
so->numberOfKeys; _bt_preprocess_array_keys resets only so->arrayContext
and builds a fresh skip key with a zero sk_argument; _bt_preprocess_keys
then copies the new keys over so->keyData[]. The previous scan's last
sk_argument pointer is overwritten without a pfree, and btendscan does
not free it either. A scan that ended with a live copy (here: each
unique lookup) therefore leaves that copy behind.
Freeing a skip array's sk_argument in btrescan before the keys are
rebuilt (and in btendscan), or making the copy in a context that a
rescan resets, would seem to fix it.
WORKAROUND
Adding an equality on the leading column to the join condition (case B)
avoids the skip array. It also took the 500,000 lookups from 874 ms to
439 ms here.
REPRODUCTION SCRIPT (psql, empty database)
-- PostgreSQL 18: a rescanned B-tree skip scan leaves one copy of the
skipped
-- column's value in the query's memory per rescan.
\set ON_ERROR_STOP on
SELECT version();
SET max_parallel_workers_per_gather = 0;
SET enable_hashjoin = off;
SET enable_mergejoin = off;
SET enable_memoize = off;
SET enable_seqscan = off;
SET enable_bitmapscan = off;
CREATE TABLE skip_inner (lead uuid NOT NULL, id uuid NOT NULL, other uuid
NOT NULL);
INSERT INTO skip_inner
SELECT '00000000-0000-0000-0000-000000000001', gen_random_uuid(),
gen_random_uuid()
FROM generate_series(1, 500000);
CREATE UNIQUE INDEX skip_inner_lead_id_other ON skip_inner (lead, id,
other);
CREATE TABLE skip_outer AS SELECT lead, id FROM skip_inner;
VACUUM ANALYZE skip_inner;
VACUUM ANALYZE skip_outer;
-- Bytes used by this backend's ExecutorState contexts, read while the
-- calling query is still running.
CREATE FUNCTION executor_state_bytes() RETURNS bigint LANGUAGE sql VOLATILE
AS
$$ SELECT sum(used_bytes)::bigint FROM pg_backend_memory_contexts WHERE name
= 'ExecutorState' $$;
\echo
\echo A. Index Cond on the second column only (skip scan over "lead")
EXPLAIN (COSTS OFF)
SELECT count(*) FROM (SELECT * FROM skip_outer LIMIT 500000) o
JOIN skip_inner i ON i.id = o.id;
SELECT 1 AS rescans, executor_state_bytes() FROM (SELECT count(*) FROM
(SELECT * FROM skip_outer LIMIT 1) o JOIN skip_inner i ON i.id = o.id OFFSET
0) q;
SELECT 125000 AS rescans, executor_state_bytes() FROM (SELECT count(*) FROM
(SELECT * FROM skip_outer LIMIT 125000) o JOIN skip_inner i ON i.id = o.id
OFFSET 0) q;
SELECT 250000 AS rescans, executor_state_bytes() FROM (SELECT count(*) FROM
(SELECT * FROM skip_outer LIMIT 250000) o JOIN skip_inner i ON i.id = o.id
OFFSET 0) q;
SELECT 500000 AS rescans, executor_state_bytes() FROM (SELECT count(*) FROM
(SELECT * FROM skip_outer LIMIT 500000) o JOIN skip_inner i ON i.id = o.id
OFFSET 0) q;
\echo
\echo B. The same lookups with the leading column also held equal (no skip
array)
EXPLAIN (COSTS OFF)
SELECT count(*) FROM (SELECT * FROM skip_outer LIMIT 500000) o
JOIN skip_inner i ON i.lead = o.lead AND i.id = o.id;
SELECT 1 AS rescans, executor_state_bytes() FROM (SELECT count(*) FROM
(SELECT * FROM skip_outer LIMIT 1) o JOIN skip_inner i ON i.lead = o.lead
AND i.id = o.id OFFSET 0) q;
SELECT 500000 AS rescans, executor_state_bytes() FROM (SELECT count(*) FROM
(SELECT * FROM skip_outer LIMIT 500000) o JOIN skip_inner i ON i.lead =
o.lead AND i.id = o.id OFFSET 0) q;
\echo
\echo C. As A with an integer (pass-by-value) leading column
CREATE TABLE skip_inner_int (lead integer NOT NULL, id uuid NOT NULL, other
uuid NOT NULL);
INSERT INTO skip_inner_int SELECT 1, id, other FROM skip_inner;
CREATE UNIQUE INDEX skip_inner_int_lead_id_other ON skip_inner_int (lead,
id, other);
VACUUM ANALYZE skip_inner_int;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM (SELECT * FROM skip_outer LIMIT 500000) o
JOIN skip_inner_int i ON i.id = o.id;
SELECT 1 AS rescans, executor_state_bytes() FROM (SELECT count(*) FROM
(SELECT * FROM skip_outer LIMIT 1) o JOIN skip_inner_int i ON i.id = o.id
OFFSET 0) q;
SELECT 500000 AS rescans, executor_state_bytes() FROM (SELECT count(*) FROM
(SELECT * FROM skip_outer LIMIT 500000) o JOIN skip_inner_int i ON i.id =
o.id OFFSET 0) q;
\echo
\echo Timing of A and B at 500000 lookups
\timing on
SELECT count(*) FROM (SELECT * FROM skip_outer LIMIT 500000) o JOIN
skip_inner i ON i.id = o.id;
SELECT count(*) FROM (SELECT * FROM skip_outer LIMIT 500000) o JOIN
skip_inner i ON i.lead = o.lead AND i.id = o.id;
| From | Date | Subject | |
|---|---|---|---|
| Next Message | PG Bug reporting form | 2026-11-19 01:21:24 | BUG #19918: SIGSEGV in auto_explain + EXPLAIN (BUFFERS) |
| Previous Message | PG Bug reporting form | 2026-11-18 22:24:14 | BUG #19916: UNION over hash-only and sort-only columns: could not devise a query plan |