Hacktoberfest 2026: le issue che i maintainer hanno segnato per ottobre, aperte e adatte ai principianti. Sfoglia le issue Hacktoberfest

Performance regression in 1.8.0: every Cypher DELETE costs ~1 ms per edge label in the graph

Aperta
  • Qualcosa non va?
#2,586 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 2 giorni

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
72/100
Tipo di issue
Bug
Chiarezza
Specificata chiaramente
Stato di attività
Attiva
Stack tecnologico
c, postgresql
Ambito
databases, performance

Direzione di ricerca

Start with src/backend/executor/cypher_delete.c, especially process_edges_by_index() and the two check_for_connected_edges() calls, then inspect DELETE_VERTEX_HTAB_SIZE in src/include/executor/cypher_utils.h. Run the SQL reproduction with varying numbers of edge labels and compare the indexed and heap-scan paths. Done means DELETE statements that match nothing or one vertex no longer incur latency proportional to edge-label count while the stated edge-deletion correctness remains intact.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

bug

Describe the bug
In 1.8.0, every Cypher DELETE / DETACH DELETE statement costs about 1 ms per edge label in the graph, even when the statement matches nothing. The number of rows or edges doesn't change it. With 150 edge labels, a DETACH DELETE that matches no vertex takes ~165 ms on 1.8.0 and ~1 ms on 1.7.0. Reads, CREATE and SET are not affected.

The cost is in process_edges_by_index(), which was added in #2351:

  • check_for_connected_edges() calls it twice for every edge label, once for the start_id pass and once for the end_id pass.
  • Each call walks vertex_id_htab with hash_seq_init() / hash_seq_search().
  • That table is created with DELETE_VERTEX_HTAB_SIZE = 1,000,000, so every walk visits about 1M buckets, even when the table holds zero vertices or one.

Rebuilding 1.8.0 with DELETE_VERTEX_HTAB_SIZE set to 1024 brings the 150-label case from ~164 ms to ~0.55 ms (table below).

How are you accessing AGE (Command line, driver, etc.)?

  • psql for the repro below. Our application uses Python (asyncpg).

What data setup do we need to do?

LOAD 'age';
SET search_path = ag_catalog, "$user", public;

SELECT create_graph('delete_repro');

-- 150 edge labels; they can all be empty
DO $$
BEGIN
  FOR i IN 1..150 LOOP
    PERFORM create_elabel('delete_repro', ('E' || i)::cstring);
  END LOOP;
END $$;

SELECT * FROM cypher('delete_repro', $$
  CREATE (:Person {name: 'a'})-[:E1]->(:Person {name: 'b'})
$$) AS (a agtype);

What is the necessary configuration info needed?

  • Nothing special. Reproduced with the stock apache/age:release_PG18_1.8.0 Docker image (PostgreSQL 18.6) on default settings, and with AGE built from the PG18/v1.8.0-rc0 tag.

What is the command that caused the error?

\timing on
-- matches no vertex
SELECT * FROM cypher('delete_repro', $$
  MATCH (n:Person {name: 'nobody'}) DETACH DELETE n
$$) AS (a agtype);
-- deletes one vertex and its one edge
SELECT * FROM cypher('delete_repro', $$
  MATCH (n:Person {name: 'a'}) DETACH DELETE n
$$) AS (a agtype);

-- 1.8.0 (apache/age:release_PG18_1.8.0)
Time: 166.609 ms
Time: 165.141 ms

-- 1.7.0 (apache/age:release_PG18_1.7.0), same script
Time: 4.658 ms   (first Cypher statement in the session)
Time: 1.325 ms

Expected behavior
A DELETE that matches nothing, or deletes one vertex, shouldn't get slower as the number of edge labels grows. 1.7.0 does the same statements in about 1 ms with 150 edge labels.

Environment (please complete the following information):

  • Version: 1.8.0 (PG18/v1.8.0-rc0) on PostgreSQL 18.6. Regression from 1.7.0 (PG18/v1.7.0-rc0).
  • Docker on Linux arm64, 4 vCPUs.
  • process_edges_by_index() and DELETE_VERTEX_HTAB_SIZE are unchanged on master as of 2026-10-01.

Additional context

Average latency of a DETACH DELETE matching no vertex (pgbench, 50 runs each). All three builds are from source on the same PostgreSQL 18.6:

Edge labels 1.7.0 1.8.0 1.8.0 with DELETE_VERTEX_HTAB_SIZE 1024
10 0.19 ms 11.7 ms 0.10 ms
50 0.25 ms 54.5 ms 0.21 ms
150 1.19 ms 163.7 ms 0.55 ms

On 1.8.0, deleting one vertex with one edge, and a plain MATCH ()-[e:E1 {w: 'nope'}]->() DELETE e matching nothing, show the same per-label cost (~165 ms at 150 labels).

What we ruled out:

  • PostgreSQL version: 1.7.0 built on 18.6 performs the same as the stock 1.7.0 image on 18.1.
  • The _age_cache_invalidate trigger: disabling it on every label table doesn't change the timing.
  • The rest of the delete path: dropping the end_id indexes on the edge label tables, which makes check_for_connected_edges() fall back to the heap-scan path, brings the same statement down to ~2 ms.

Correctness with the smaller size: a DETACH DELETE of a vertex with three edges in different labels, including a self-loop, removes exactly those edges, and unrelated edges stay. The local dynahash grows on demand, so the constant is only an initial size.

Possible fixes, any of which would help:

  • Create vertex_id_htab with a small initial size and let it grow.
  • Skip check_for_connected_edges() when hash_get_num_entries(css->vertex_id_htab) == 0. This covers edge-only DELETE and deletes that match nothing.
  • Collect the vertex ids into an array once and loop over that array in both index passes.

Impact for us: our graph has ~120 edge labels, so every delete got ~130 ms slower after upgrading. Our 672-test suite went from 16 s to 5 min 44 s. We're staying on 1.7.0 in production until this is fixed.

Lingua principale
C
Stelle
4.9k
Fork
532
Merge medio
9g 18h
PR unite (30g)
10

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di apache/age

Tutte le issue di apache/age

Issue simili

Altre issue su C

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.