crdv7 opened a new pull request, #2486:
URL: https://github.com/apache/age/pull/2486
This change avoids redundant graphid lookups when `SET`, `REMOVE`, or
`DELETE` writes an entity already read by `MATCH`.
For writable statements, AGE now carries the matched tuple's `ctid` as a
hidden internal lookup hint. The executor validates the tuple's snapshot
visibility and graphid before using it, and falls back to the existing
graphid lookup when the hint is missing or stale. Graphid remains the logical
entity identity.
The same executor path also contained a correctness issue: when two aliases
in one result row represented the same entity, a later `SET` or `REMOVE`
could rebuild the entity from stale alias-local properties and overwrite an
earlier change. This change refreshes the current properties and keeps
same-graphid aliases and paths synchronized.
The resulting lookup order is:
```text
validated CTID fetch
-> usable non-partial B-tree index whose first key is entity id
-> sequential scan when no suitable id index exists
```
No Cypher syntax or user-visible result columns are added.
## Implementation
### Hidden CTID propagation
- Add a hidden CTID target entry for named vertices and edges read by
`MATCH`, but only when the statement contains `SET`, `REMOVE`, or `DELETE`.
- Carry the hint through simple `WITH n` and `WITH n AS alias` projections.
- Stop propagation at aggregation, `DISTINCT`, computed expressions, and
parser-generated variables, where a hidden value could change row-shaping
semantics or become associated with the wrong entity.
- Store the hidden column position in update/delete plan metadata and include
it in ExtensibleNode copy/out/read support.
PostgreSQL's native `tid` is packed into a scalar agtype value for transport
through AGE target lists:
```text
BlockNumber (32 bits) << 16 | OffsetNumber (16 bits)
```
Read-only `MATCH`/`RETURN` statements do not receive hidden target entries.
### Shared entity lookup
`find_entity_tuple()` centralizes tuple lookup for the writable executors.
1. If a hidden CTID is available, fetch that row version directly.
2. Accept it only when it is visible to the current snapshot and contains
the expected graphid.
3. Otherwise, use a valid non-partial B-tree index whose first key is the
entity `id`.
4. Fall back to a sequential scan only when no suitable `id` index exists.
This remains correct if a user drops a default vertex `id` index. It also
allows a user-created edge `id` index to serve as the fallback. The default
edge `start_id` and `end_id` indexes cannot resolve an edge by graphid.
A statement-local cache retains the tuple fetch slot, opened `id` index
relation, and negative index-search result. `SET` and `DELETE` own that cache
for their custom-scan lifetime.
CTID is always a hint rather than identity. A previous writable clause may
move the tuple and leave another alias with a stale CTID; the validation and
graphid fallback prevent an update or delete from targeting the wrong row.
After an update, the new CTID is propagated for later writes in the same
result row.
### Same-row alias consistency
The original `apply_update_list()` built replacement properties from the
agtype value stored at each update item's tuple position before locating the
current heap tuple. Different aliases were treated as independent positions
even when they contained the same graphid, and the existing synchronization
pass updated entities inside paths but not top-level aliases.
For example:
```cypher
MATCH (n:T), (m:T)
WHERE id(n) = id(m)
SET n.a = 1
SET m.b = n.a + 1
RETURN n.a, m.a, m.b
```
The query could return `1, NULL, 2` while the stored entity contained
`a = NULL, b = 2`.
The updated executor locates the current heap tuple before building the first
update for an entity position, refreshes stale base properties from that
tuple, synchronizes top-level aliases and paths with the same graphid, and
reuses the tuple for the physical update. `REMOVE` uses the same path and
receives the same consistency fix.
`DELETE` does not reconstruct properties, but it uses the validated lookup
helper so a stale alias CTID after an earlier `SET` cannot cause the wrong
tuple to be deleted.
### MERGE fallback cache
`MERGE` does not receive a hidden CTID fast path in this change. When a plan
contains `ON MATCH SET` or `ON CREATE SET`, it creates the shared lookup
cache
for the statement lifetime and releases it when the custom scan ends.
## Suggested review order
1. `src/backend/executor/cypher_utils.c`: shared CTID/index/SeqScan lookup.
2. `src/backend/parser/cypher_clause.c`: hidden CTID generation and `WITH`
propagation.
3. `src/backend/executor/cypher_set.c`: current-property refresh and
alias/path synchronization.
4. `src/backend/executor/cypher_delete.c` and
`src/backend/executor/cypher_merge.c`: lookup/cache integration.
5. ExtensibleNode definitions and copy/out/read serialization.
6. Regression SQL and expected output.
## Performance
### Environment and method
- Baseline AGE:
`801417404978823bd8732452c3f7959017584785`
- Patched AGE:
`406df98918c1e49be4fce170c078c731097faf42`
- PostgreSQL 18.4, built with `--disable-debug --disable-cassert` and
`CFLAGS=-O2 -g`
- Intel Xeon 6982P-C, 8 cores / 16 threads, 29 GiB RAM
- Local Unix-domain socket
- `shared_buffers=1GB`, `fsync=off`, `synchronous_commit=off`,
`full_page_writes=off`, `autovacuum=off`, `jit=off`
Only `age.so` changed between variants. Data reset, `VACUUM ANALYZE`, server
restart, and shared-library replacement were outside the timed interval.
The control workloads use 15 measured AB/BA-interleaved runs after an
unreported warm-up. Writable and edge workloads use five measured
AB/BA-interleaved runs after warming both variants. CV is the sample
coefficient of variation. Negative elapsed-time change means the patched
version is faster.
| Workload | Rows | Baseline median (CV) | Patched median (CV) | Elapsed
change |
|---|---:|---:|---:|---:|
| Read-only `MATCH`/count | 100,000 | 22.832 ms (2.72%) | 23.502 ms (3.46%)
| +2.93% |
| SQL insert control | 100,000 | 163.323 ms (0.89%) | 163.142 ms (0.68%) |
-0.11% |
| Cypher `CREATE` control | 100,000 | 191.679 ms (0.91%) | 193.304 ms
(2.87%) | +0.85% |
| `SET` | 100,000 vertices | 12.352 s (2.50%) | 12.155 s (0.43%) | -1.59% |
| `REMOVE` | 100,000 vertices | 12.533 s (1.30%) | 12.109 s (0.58%) | -3.39%
|
| `DELETE` | 100,000 vertices | 526.539 ms (1.33%) | 442.668 ms (0.63%) |
-15.93% |
| `DELETE` | 500,000 vertices | 2.754 s (0.42%) | 2.243 s (0.82%) | -18.55% |
| `MERGE ON MATCH SET` | 100,000 operations | 11.870 s (1.31%) | 11.887 s
(0.83%) | +0.14% |
| Edge `SET`, no edge `id` index | 10,000 | 887.287 ms (0.91%) | 210.586 ms
(0.30%) | -76.27% |
| Edge `SET`, no edge `id` index | 20,000 | 3.362 s (0.21%) | 579.715 ms
(0.53%) | -82.76% |
| Edge `SET`, no edge `id` index | 100,000 | 86.218 s (0.95%) | 12.442 s
(1.14%) | -85.57% |
| `DISTINCT` edge `SET`, edge `id` index | 20,000 | 624.720 ms (2.13%) |
629.566 ms (1.34%) | +0.78% |
`DELETE` isolates entity lookup most clearly and improves by 16–19%.
`SET` and `REMOVE` spend most of their time rebuilding properties and
updating PostgreSQL tuples, so their statement-level gains are smaller.
`MERGE`, which uses only the fallback cache in this change, remains neutral.
The read-only control is 0.670 ms slower at 100,000 rows; no hidden CTID
target
entry is generated for that statement. SQL insert is neutral, and Cypher
`CREATE` remains within 1%.
The edge-label case shows the largest benefit. Without a user-created `id`
index, the baseline performs a sequential graphid lookup for each edge. A
10x increase from 10,000 to 100,000 edges increases baseline time by 97.17x,
from 0.887 s to 86.218 s. The final patch takes 12.442 s at 100,000 edges, an
85.57% reduction.
### Benchmark reproduction
A self-contained reviewer script builds the baseline and patched commits,
creates an isolated PostgreSQL 18 cluster, runs the workloads in AB/BA order,
and writes raw and summarized CSV files. It neither installs AGE into the
PostgreSQL prefix nor modifies an existing cluster.
[repro_pr_benchmark.sh](https://github.com/user-attachments/files/30534979/repro_pr_benchmark.sh)
From an AGE checkout on the pull request branch:
```bash
chmod +x repro_pr_benchmark.sh
./repro_pr_benchmark.sh \
--age-repo "$PWD" \
--pg-config /path/to/postgresql-18/bin/pg_config \
--full
```
The default full run reproduces the timing matrix above, including 15 control
runs, five writable/edge runs, and the 500,000-vertex `DELETE` case. Because
the no-index 100,000-edge baseline is intentionally slow, reviewers can first
validate their environment with:
```bash
./repro_pr_benchmark.sh \
--age-repo "$PWD" \
--pg-config /path/to/postgresql-18/bin/pg_config \
--quick
```
The main output is `all-summary.csv`; per-group raw CSV, build logs,
PostgreSQL logs, and an environment manifest are retained alongside it.
### Standalone edge-label benchmark
The following self-contained script creates an edge label with only the
default `start_id` and `end_id` indexes, loads the requested number of edges,
prints the actual indexes, runs `EXPLAIN ANALYZE`, and verifies the updated
row count:
[repro_edge_set_no_id_index.sql](https://github.com/user-attachments/files/30529509/repro_edge_set_no_id_index.sql)
```bash
# Quick check: 10,000 edges
psql -X -d <database> -f repro_edge_set_no_id_index.sql
# Same scale as the largest result above
psql -X -d <database> -v edge_count=100000 \
-f repro_edge_set_no_id_index.sql
```
### CPU profile
The 2,000,000-vertex `DELETE` workload was sampled at 199 Hz with
`cycles:u` and DWARF call graphs:
| Profile | Samples | Sample duration | Sampled cycles |
|---|---:|---:|---:|
| Baseline | 2,217 | 11.158 s | 37.43 billion |
| Patched | 1,822 | 9.144 s | 29.70 billion |
In the baseline, stacks below `process_delete_list()` containing
`index_getnext_slot` account for 16.56% of sampled cycles:
```text
process_delete_list
index_getnext_slot
index_getnext_tid
btgettuple
_bt_first
_bt_search
```
That stack disappears from the patched profile. Its replacement accounts for
3.09% of patched sampled cycles:
```text
process_delete_list
find_entity_tuple
try_ctid_fetch_tuple
table_tuple_fetch_row_version
heapam_fetch_row_version
heap_fetch
```
Baseline flame graph:
<img width="1200" height="750" alt="baseline-flamegraph-static"
src="https://github.com/user-attachments/assets/990505db-a657-49ca-9200-62e28ceb3b9e";
/>
Patched flame graph:
<img width="1200" height="750" alt="patched-flamegraph-static"
src="https://github.com/user-attachments/assets/08a0b2c2-28c0-4a27-92fd-1df516e4b291";
/>
Five-run `perf stat` medians for the 500,000-vertex `DELETE` workload:
| Event | Baseline | Patched | Change |
|---|---:|---:|---:|
| CPU cycles | 8,784,745,592 | 7,027,241,137 | -20.01% |
| Instructions | 28,468,929,329 | 22,572,493,361 | -20.71% |
| Branches | 5,228,809,347 | 4,153,536,763 | -20.56% |
| Branch misses | 5,534,090 | 3,547,249 | -35.90% |
| Cache references | 14,515,881 | 13,605,987 | -6.27% |
| Cache misses | 3,389,227 | 2,924,470 | -13.71% |
The elapsed-time improvement is accompanied by comparable reductions in
instructions and branches, supporting a CPU-work reduction rather than an
I/O-only effect.
## Added Tests
Built and tested against PostgreSQL 18.4:
- clean release build completed without warnings;
- targeted writable/VLE regression suites passed under debug/cassert:
`cypher_set`, `cypher_remove`, `cypher_delete`, `cypher_with`,
`cypher_vle`, and `cypher_merge`;
- the final combined commit passed the full debug/cassert AGE regression
suite: **43/43**.
New regression coverage includes:
- valid and stale CTID lookup, including fallback after an earlier `SET`;
- vertex and edge aliases that share a graphid across chained writes;
- dependent right-hand-side expressions and persisted-state verification;
- alias/path synchronization for `SET` and `REMOVE`;
- simple `WITH` references and renames;
- aggregation, `DISTINCT`, and computed-expression propagation boundaries;
- stale CTID fallback for `REMOVE`, `DELETE`, and `DETACH DELETE`;
- fallback through a user-created edge `id` B-tree index.
## Related issues
Closes #2484
Closes #2485
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
