Inefficient MATCH (a)-[:RELATED]->(b) WHERE id(a) IN ...
- 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
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