redhog opened a new issue, #2548:
URL: https://github.com/apache/age/issues/2548
**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.
--
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]
