Optimization: COPY's fixed cost follows max_num_threads and NODE_GROUP_SIZE, not the input

[alvorithm]

This work was produced with the help of language models.

This is for the discussion about items 3 and 4 of #781, which you encouraged us to have separately: "Avoiding memory allocations or deferring them is a much needed optimization."

The deferrals are nearly done. #781 moved the node path's column chunks to the first append, and two follow-ups defer the primary-key index buffers and the rel path's CSR header. Two assumptions underneath them are what remain: a COPY runs max_num_threads workers whatever the input holds, and a worker that receives one row reserves a node group sized for 131 072 rows.

Numbers below come from two places (see the tables). Engine measurements are on main at 213891756, relwithdebinfo, gcc 14.2.0, on an otherwise idle 16 core (8 physical) machine, with both follow-up deferrals applied. Application measurements come from one consumer's graph ingest on the 0.19.1 release.

A real ingest gets slower with every worker beyond two

The consumer projects a document into a graph of nodes and edges and writes it through the Arrow path, as one COPY per non-empty node table and one per non-empty edge group. Ingest wall time for three documents, varying SystemConfig.maxNumThreads alone, three repetitions per cell:

document	nodes	edges	statements	1	2	4	8	16
A	22	21	18	504, 504, 478	532, 515, 478	529, 539, 572	639, 611, 623	826, 800, 770
B	1482	1481	30	1073, 1049, 1060	1075, 1157, 1066	1197, 1122, 1119	1237, 1242, 1237	1516, 1512, 1453
C	2146	2145	29	1240, 1182, 1308	1235, 1246, 1242	1256, 1255, 1264	1451, 1401, 1357	1654, 1620, 1623

Sixteen workers cost 60%, 42% and 31% more wall time than one worker, and the curve is monotone from two workers up in all three documents. In the fifteen documents measured, the edge group these statements carry holds one edge fewer than the document has nodes, and the section below characterises the statements themselves.

Where does the time go?

One COPY of 100 rows into an 89-column node table, one statement per process, measured on patched main:

max_num_threads	wall	CPU	minor faults
1	67.4 ms	66.7 ms	26.4k
2	67.2 ms	70.1 ms	27.6k
4	70.5 ms	83.0 ms	29.5k
8	78.7 ms	93.7 ms	31.3k
16	72.2 ms	105.9 ms	34.5k

Wall time stays flat between one worker and sixteen while CPU rises by 59%. A zero-row COPY into the same table costs 10.0 ms of CPU at one thread and 16.8 ms at sixteen. On the rel side, before its deferral, one COPY of 100 edges cost 18.9 ms of CPU at one thread and 48.6 ms at sixteen; with the deferral it is flat at 19 to 21 ms across the sweep.

perf record -g --call-graph dwarf over fifty zero-row statements at 16 threads puts 21.6% of the patched profile under TaskScheduler::runWorkerThread, down from 67.7% before the two deferrals. The largest self-time symbol left is BufferManager::removeEvictedCandidates at 44.2%, which is eviction bookkeeping that scales with the pool, not with the input.

The caller usually knows the row count, including through a subquery

#781 named the obstacle: "A CSV file can be stated, an Arrow source knows its row count, and a COPY FROM (MATCH ...) subquery knows nothing." The consumer these reports come from uses the third shape only, and here is what its statements are, read out of its schema registry and its loader, and measured over fifteen documents spanning 22 to 21935 nodes:

• Twelve node tables, 9 to 89 columns, 660 projected columns in total. The widest is 89 columns, which is the width the fix(copy): defer COPY's per-worker chunk buffers to the first append #781 reproducers use.
• Four rel groups holding 210 (from, to) pairs, of which the loader writes one group's 47.
• One load issues one COPY per non-empty node table and one per non-empty edge pair: 14 to 32 statements per document across the fifteen measured, 366 statements in total.
• Every statement is COPY T FROM (MATCH (n:stg) RETURN …), where stg is an Arrow table the caller staged through createArrowTable one call earlier, holding exactly the rows that statement will read.

This is the distribution:

statement size	count	share
under 10 rows	127	35%
under 100 rows	248	68%
under 1000 rows	334	91%
under 10000 rows	364	99%

The median statement carries 30 rows and the largest carries 11297. One node table holds exactly one row in every document by construction, and two others hold tens.

So the subquery in this shape is a full scan of a table the engine is holding in memory, with a known row count and no filter. The planner has the cardinality, and the caller certainly has it. The edge statements join the staged table against two node tables, so their cardinality is bounded above by the staged row count, but not known exactly.

Options

No preference here among:

1. Size the worker pool from the source's estimated cardinality, capped by max_num_threads. Cardinality sizing uses what the planner already holds, and it covers COPY FROM 'file.csv' and Arrow sources too. The route also has the widest blast radius of the three, which is why this is an issue rather than a patch.
2. Let the caller say so. A statement-level option, COPY t FROM (…) (parallelism = 1) or similar, needs no estimate and no scheduler change, and it puts the decision with the party that staged the data. The option helps a caller that knows its row count, and this one does.
3. Leave the worker count alone and scale the first node group instead. An active worker reserves a full NODE_GROUP_SIZE for its first row, and the machinery to grow on demand exists: ColumnChunkData::resize and InMemChunkedNodeGroup::resizeChunks.

Option 3 is the group capacity item from #781, folded in here because it is the same argument: an input too small for the shape the engine assumes should not pay for that shape. It has its own numbers. With one worker and 100 rows, the smallest buffer pool that loads the table tracks the column count, bisected to 8 MB:

columns	100 rows, one worker
2	16 MB
11	16 MB
21	32 MB
41	64 MB
89	128 MB
161	256 MB
321	512 MB

The data in that load is a few hundred kilobytes, and the rest is capacity reserved for rows that do not arrive, in the one worker that is active. A profile of a 100-row COPY at 16 threads on the patched build puts 36.7% under InMemChunkedNodeGroup's constructor and 36.0% under ColumnChunkData::initializeBuffer, which is that reservation.

The worker count and the group capacity also interact, and the first follow-up patch shows how. Deferring the index buffers removes startup work from each worker, and an instrumented build shows the effect: on a 10000-row input at 16 threads the peak pool demand is 1544.5 MB on both builds, reached by 9 runs of 9 with the deferral and by 5 runs of 9 without it. Making workers cheaper to start makes more of them reach a full-capacity group at once, so the group capacity is the term that sets the ceiling.

What would help

Say which of the three you would accept a patch for, and whether sizing from a cardinality estimate is acceptable in the planner at all. If the answer is the caller-side option, it is small and we can offer it. If the group capacity is the one you would rather move, your input is needed for the growth policy. Doubling from a small initial capacity and resizing once to the known input size reach different assumptions about write-and-reset frequency.

We are not asking for a scheduler redesign, and we are not blocked. The consumer sets max_num_threads explicitly today, which is a workaround with the same shape as the problem.

How the numbers were taken

Engine measurements use one_copy.c, many_copies.c and bp_bisect.c from #760 and #781, and their variants. Timings come from clock_gettime and getrusage around the COPY loop, profiles from perf record -g --call-graph dwarf -F 499 read with perf report --children, floors from bisection to 8 MB, and peak pool demand from a temporary watermark in BufferManager::reserve. Application measurements come from a probe that runs the consumer's schema DDL and its loader against a fresh on-disk database, with SystemConfig.maxNumThreads set per run, over fifteen documents of 22 to 21935 nodes. Machine: 16 core (8 physical) AMD Ryzen 7 7840U, 64 GB RAM, Linux 7.1.3, carrying light background load rather than idle, so each comparison interleaves the builds run by run and the tables give each run.