apache / apache/age

Inefficient MATCH (a)-[:RELATED]->(b) WHERE id(a) IN ...

Open
#2,548 0 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
C
Stars
4.8k
Forks
523
Avg merge
1d 2h
Merged PRs (30d)
9

Description

**Describe the bug**
`MATCH (a) WHERE id(a) IN [...]` never uses the vertex/edge table's indexes. AGE fully scans the label table and builds an `agtype` object for every row before applying the id filter, so cost is `O(table size)` no matter how selective the filter is. The same filter run as plain SQL against the underlying table hits the index and is ~250x faster even on a tiny 5k-row table.

**How are you accessing AGE (Command line, driver, etc.)?**
- psql / psycopg2, plain SQL (`SELECT * FROM cypher(...)`)

**What data setup do we need to do?**
```pgsql
SELECT create_graph('bugtest');

SELECT * FROM cypher('bugtest', $$
UNWIND range(1, 5000) AS i
CREATE (:Part {part_num: i})
$$) AS (a agtype);

SELECT * FROM cypher('bugtest', $$
UNWIND range(1, 5000) AS i
MATCH (a:Part {part_num: i}), (b:Part {part_num: (i % 5000) + 1})
CREATE (a)-[:used_by {quantity: 1}]->(b)
$$) AS (a agtype);
```

**What is the necessary configuration info needed?**
- Default install. An existing sufficiently large graph.

**What is the command that caused the error?**

Not a crash — a bad query plan.

```pgsql
EXPLAIN (ANALYZE) SELECT * FROM cypher('bugtest', $$
MATCH (a)-[:used_by]->(b)
WHERE id(a) IN [844424930131969, 844424930131970, 844424930131971]
RETURN id(a), id(b)
$$) AS (a agtype, b agtype);
```
```
Seq Scan on "Part" a_2 (actual rows=3 loops=1)
Filter: (age_id(_agtype_build_vertex(a_2.id, ...)) = ANY ('{...}'::agtype[]))
Rows Removed by Filter: 4997
Seq Scan on used_by (actual rows=5000 loops=1)
Execution Time: 2.947 ms
```

Same filter, plain SQL against the same table:

```pgsql
EXPLAIN (ANALYZE) SELECT start_id, end_id FROM bugtest.used_by
WHERE start_id = ANY('{844424930131969,844424930131970,844424930131971}'::graphid[]);
```
```
Bitmap Heap Scan on used_by
-> Bitmap Index Scan on used_by_start_id_idx
Execution Time: 0.012 ms
```

Note the reverse case (`id(b) = end_id` as a join key from the pattern itself, not a `WHERE ... IN` list) *does* get an `Index Scan` — so the planner can use these indexes, it just doesn't for an explicit `id() IN` filter.

**Expected behavior**
`id(a) IN [...]` should hit the existing primary-key/`start_id`/`end_id` btree index, like the equivalent plain SQL does, instead of a full sequential scan regardless of selectivity.

**Environment (please complete the following information):**
- AGE 1.7.0, PostgreSQL 18.6, `apache/age:latest` Docker image

**Additional context**
Found while debugging why an anonymous-mode subgraph load (via a graph-analytics extension built on AGE) cost ~7s/call on a 1M-node graph regardless of filter selectivity. Workaround required bypassing `MATCH` entirely and going through a raw-SQL escape hatch instead.

Contributor guide

Open the contributing guide

Research direction

Reproduce the provided Cypher setup and both EXPLAIN (ANALYZE) queries on AGE 1.7.0/PostgreSQL 18.6, then trace MATCH handling for an explicit id(a) IN filter and compare it with the working reverse join-key index scan. Done means the Cypher plan uses the existing vertex/edge btree index instead of building agtype values for a sequential scan, while preserving the query result.

Written by the indexing model from the issue text.

Assessment

Tech stack
c, postgresql
Domain
databases, performance
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.