011. Reduce dynamic PostgreSQL JSON sort lowering work

• Status: Measured
• Date: 2026-10-13

Problem and mechanism

Dynamic PostgreSQL sorting reconstructed a comparison document even when a native JSON Any list already retained the element representations needed by the sort. Float sort keys also repeated exact numeric decomposition for zero values. The candidate array-gates and consumes the original native JSON list for Any elements. For recoverable dynamic values, it projects each decoded Float locally and uses numeric zero directly as the exact sort key, avoiding unnecessary decomposition for zero; nonzero values still use the shared floatNumeric lowering. JSONB continues to normalize through the comparison-document path when needed to restore numeric subtypes. The schema-resolved homogeneous fast paths and global floatNumeric implementation are unchanged.

These changes were measured together; the results do not isolate the effect of either mechanism. Sorting still rejects mixed runtime families and null or non-orderable elements as ordinary null. Stable ordering and the original representations of equal values are preserved when those representations are recoverable. JSONB type metadata remains necessary when normalization erased an integer/float distinction; a schema-known Float type alone cannot restore a negative-zero sign lost by JSONB.

Applicability

Reproduction

The baseline is 290e7f6. The candidate source snapshot for backends/sql/json_sort_dynamic.go has SHA-256 cb3e9bc4b514426996e6474db8614115042c985ec8fac299135146c127c3911f. The same benchmark harness was applied to both revisions:

• Build benchmark, SHA-256 0096237bedf8efce2b67ccb14d30d1e3b0c793afcb688634ea56b986c97888b6
• PostgreSQL execution benchmark, SHA-256 aa03b8b6e48e4d7f905c8a2533f5ea7519d67744743f43d7ad58609f9c64da0f

The Build matrix crosses native JSON and JSONB with TypedIntControl, TypedFloatControl, AnyNumbers, AnyStrings, AnyInvalidNested, AnyInvalidMixed, and RepeatedSort. Execution uses those workloads with 25 or 100 rows and 16 or 64 elements per list. Dynamic fixtures include mixed int64/float64 values, strings, signed zero, nested non-orderable values, invalid mixed families, and null rows. Each database sample first checks matching IDs against local HFF before measuring the EXPLAIN ANALYZE query; fixture creation, transport, and validation are not included in the reported execution time.

The host was Darwin 25.2.0, arm64, Apple M3 Max with 64 GiB RAM. Go was 1.27.1 with default GOMAXPROCS=16. PostgreSQL was 18.6 in a Debian aarch64 container using image digest sha256:5a5a84b19854a9ffaa54082c166ff4ec27473a361e496e5ea167f298f2da9722. The container had no explicit CPU, memory, or cpuset limits, and its data directory used tmpfs. The runs used no Docker network, disabled JIT and parallel workers, forced custom plans, and set a 60-second statement timeout.

The final aggregate combines two serialized three-sample rounds in the order baseline, candidate, baseline repeat, candidate repeat (six samples per side). Raw logs, plans, and per-sample results remain outside Git in /private/tmp/hff-sort-evidence/ on the measurement host.

Build command:

GOTOOLCHAIN=go1.27.1 go test ./backends/sql -run '^$' \
  -bench '^BenchmarkPostgreSQLSortBuild$/(json|jsonb)/(TypedIntControl|TypedFloatControl|AnyNumbers|AnyStrings|AnyInvalidNested|AnyInvalidMixed|RepeatedSort)$' \
  -benchmem -benchtime=200ms -count=3

PostgreSQL execution command (use separate plan directories for each revision and repeat):

HFF_POSTGRES_CONTAINER=<container-name-or-id> \
HFF_POSTGRES_PLAN_DIR=/private/tmp/hff-sort-plans-baseline \
GOTOOLCHAIN=go1.27.1 go test -tags=integration ./backends/sql -run '^$' \
  -bench '^BenchmarkPostgreSQLSortExecution$/(json|jsonb)/(25Rows|100Rows)/(16Elements|64Elements)/(TypedIntControl|TypedFloatControl|AnyNumbers|AnyStrings|AnyInvalidNested|AnyInvalidMixed|RepeatedSort)$' \
  -benchtime=1x -count=3

Results

Build time is median ns/op with min–max ranges across six samples. B/op is cumulative allocation, not retained or peak memory. SQL bytes and parameter counts describe the generated predicate.

Database planning and execution are median milliseconds with min–max ranges across six single-execution samples. Plan-node counts, SQL bytes, and expected matched rows are baseline → candidate. The 16 control SQL scripts were byte-identical across the four runs. Across all 16 controls, execution medians ranged from 3.9% lower to 10.5% higher; at 100 rows and 64 elements, control drift was about 1–4%. These six-sample measurements come from one environment without independent replication, so small differences should not be read as stable changes.

At 100 rows and 64 elements, the measured JSON AnyNumbers, AnyStrings, and RepeatedSort execution medians changed from 22.62 to 19.47 ms, 6.225 to 5.466 ms, and 43.66 to 38.065 ms. The corresponding JSONB metadata-backed medians were 33.81 to 31.955 ms for AnyNumbers and 55.85 to 49.97 ms for RepeatedSort; the JSONB AnyInvalidNested workload was effectively unchanged at 32.125 versus 32.08 ms. These are workload-specific observations, not a general execution guarantee. Build timing was not uniformly lower, some candidate workloads allocated more bytes despite fewer allocations, and control timings drifted despite identical SQL. The benchmark validates predicate results and plan cardinality; it does not claim to compare every sorted output value. Focused PostgreSQL/local parity tests cover selected sort values, subtypes, stable ties, JSON whitespace and escapes, underflow-to-zero, repeated sorts, and invalid shapes.

Correctness and tradeoffs

The PostgreSQL/local dynamic-sort parity matrix passed 2,312 comparisons. The project check and integration-tagged SQL vet passed. The correctness tests include JSONB metadata restoration and confirm that ordinary JSONB Float schema information cannot recover a negative-zero sign that storage already erased. The combined candidate reduces SQL bytes and plan-node counts for measured dynamic workloads while retaining normalization where JSONB representation recovery needs it. It does not alter HFF collection semantics or the schema- resolved homogeneous path. No retained-memory, peak-memory, production, or cross-version measurement was made.

Decision and limits

Retain the bounded lowering for the measured dynamic JSON sort shapes. The measured improvements vary by JSON format and workload; the JSONB invalid-nested case and typed controls do not show a consistent execution benefit. Do not extrapolate these local measurements to other schemas, PostgreSQL versions, list sizes, data distributions, or service latency. Further optimization of dynamic sort construction requires its own paired correctness and measurement evidence. See the PostgreSQL optimization catalog for the translation scope.