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

[redhog]

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?

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.

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:

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.