NotHimmel opened a new issue, #2560:
URL: https://github.com/apache/age/issues/2560
**Describe the bug**
A 2-hop MATCH query changes its plan from per-hop index probes (Nested Loop,
sub-second) to a full scan over all 9,000,000 edges followed by a Merge Join
(17.1 s) as soon as an attribute index (GIN) exists on the start vertex's
filter property. Dropping that index restores the fast index-probe plan. The
query results are identical in both cases (count = 300), only the chosen plan
differs.
**How are you accessing AGE (Command line, driver, etc.)?**
- psql command line
**What data setup do we need to do?**
```sql
CREATE EXTENSION age;
LOAD 'age';
SET search_path = ag_catalog, public;
SELECT create_graph('repro_graph');
SELECT create_vlabel('repro_graph','VTABLE');
SELECT create_elabel('repro_graph','E1TABLE'); -- E2TABLE / E3TABLE same
-- 3,000,000 vertices: 10,000 distinct person_id values, 300 vertices each
INSERT INTO "VTABLE" (id, properties)
SELECT ag_catalog._graphid(3, g),
ag_catalog.agtype_build_map('person_id', 'B' || lpad((g %
10000)::text, 8, '0'))
FROM generate_series(1, 3000000) g;
-- 3,000,000 edges per table: endpoints concentrated on ~1,000 hub nodes
-- (skewed relation-graph shape, e.g. person→merchant→person;
-- a uniform distribution does NOT reproduce the problem)
INSERT INTO "E1TABLE" (id, start_id, end_id, properties)
SELECT ag_catalog._graphid(4, g),
ag_catalog._graphid(3, ((g-1) % 999) + 2),
ag_catalog._graphid(3, ((g::bigint * 7919) % 1000) + 1),
ag_catalog.agtype_build_map('etype', 'E1')
FROM generate_series(1, 3000000) g;
-- E2TABLE / E3TABLE analogous; full script attached (01_load_data.sql)
CREATE INDEX v_prop_gin ON repro_graph."VTABLE" USING gin (properties);
ANALYZE;
```
(The queried start value matches 300 vertices; its actual traversal touches
only ~600 edges, so replays are safe and results are stable.)
**What is the necessary configuration info needed?**
- `shared_preload_libraries = 'age'` (or `LOAD 'age';` per session)
- Everything else default; no special GUCs
**What is the command that caused the error?**
```sql
EXPLAIN (ANALYZE)
SELECT * FROM cypher('repro_graph', $$
MATCH (a:VTABLE {person_id: 'B00000001'})-->()-->(c)
RETURN count(DISTINCT c) $$) AS (r agtype);
```
Output (excerpt):
```
Merge Join (actual rows=300)
-> Nested Loop (actual rows=9,000,300)
-> Merge Join
-> Index Scan using "E1TABLE_start_id_idx" (actual
rows=3,000,000)
-> Index Scan using "E2TABLE_start_id_idx" (actual
rows=3,000,000)
-> Index Scan using "E3TABLE_start_id_idx" (actual
rows=3,000,000)
-> Sort (rows=300)
-> Bitmap Index Scan on v_prop_gin
Execution Time: 17113.490 ms
```
Dropping `v_prop_gin` and re-running the same command restores per-hop
`start_id` / `end_id` index probes and completes in under a second.
For diagnosis: with the index in place, `SET enable_mergejoin = off;` brings
the same query down to 0.54 s — confirming the data and results are correct
and only the plan selection is affected.
**Expected behavior**
Whether or not the start-point filter uses an attribute index should not
change how subsequent hops are accessed: the plan should keep probing the
per-hop `start_id` / `end_id` indexes regardless.
**Environment (please complete the following information):**
- Version: AGE 1.7.0 (release), PostgreSQL 18, Linux x86_64
--
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]
