tae898 opened a new issue, #2582:
URL: https://github.com/apache/age/issues/2582
**Describe the bug**
A property `IN` filter whose list is a Cypher parameter is planned as a
sequential scan through `agtype_in_operator(...)`, so an expression index on
that property is never used. The identical list written into the query text is
planned as `= ANY('{...}'::agtype[])` and uses the index. With 20,000 vertices
that is 25.2 ms against 0.23 ms, and as a sequential scan it grows with the
label table. A single-value parameter (`n.prop = $p`) does use the index, so
parameters in general are fine; it is only the list form.
The cause looks like `transform_AEXPR_IN` in
`src/backend/parser/cypher_expr.c`: only a literal `cypher_list` on the right
is expanded into the indexable `ScalarArrayOpExpr`. Anything else, a parameter
included, becomes a call to `agtype_in_operator`, which the planner cannot
match to an index. The plan shows the parameter already folded to a constant
(`agtype_in_operator('[16, 4242, 9000]'::agtype, ...)`), so the list is known
by then; it is only the shape of the expression that blocks the index. The
literal path came from #1236 (Refactor the IN operator to use '= ANY()'
syntax); parameters and other non-literal lists stayed on `agtype_in_operator`,
so this is not a regression but the case #1236 did not cover.
We found this while making every engine in a benchmark bind its values
instead of pasting them: AGE was the one engine where binding a list made the
query an order of magnitude slower (2.0 ms to 26.4 ms p50 in our workload), so
we keep that list pasted and bind everything else.
**How are you accessing AGE (Command line, driver, etc.)?**
psql, and psycopg 3 in the application (same plans).
**What data setup do we need to do?**
```pgsql
LOAD 'age';
SET search_path = ag_catalog, "$user", public;
SELECT create_graph('shop');
-- 20,000 products, each even pid RELATED to the next odd one
SELECT * FROM cypher('shop', $$ UNWIND range(0, 9999) AS i
CREATE (:Product {pid: 2 * i})-[:RELATED]->(:Product {pid: 2 * i + 1})
$$) AS (v agtype);
CREATE INDEX product_pid ON shop."Product"
USING btree (ag_catalog.agtype_access_operator(properties,
'"pid"'::agtype));
ANALYZE;
```
**What is the necessary configuration info needed?**
None beyond the above. Stock `postgres` image with the PGDG
`postgresql-18-age` package.
**What is the command that caused the error?**
Pasted list, uses the index:
```pgsql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY ON)
SELECT * FROM cypher('shop', $$ MATCH (a:Product)-[:RELATED]->(b)
WHERE a.pid IN [16, 4242, 9000] RETURN b.pid $$) AS (pid agtype);
```
```
Nested Loop (actual rows=3.00 loops=1)
-> Nested Loop (actual rows=3.00 loops=1)
-> Index Scan using product_pid on "Product" a (actual rows=3.00
loops=1)
Index Cond: (agtype_access_operator(VARIADIC
ARRAY[properties, '"pid"'::agtype]) = ANY ('{16,4242,9000}'::agtype[]))
-> Index Scan using "RELATED_start_id_idx" on "RELATED"
_age_default_alias_0 (actual rows=1.00 loops=3)
...
Execution Time: 0.227 ms
```
The same list as a parameter, sequential scan:
```pgsql
PREPARE bound(agtype) AS
SELECT * FROM cypher('shop', $$ MATCH (a:Product)-[:RELATED]->(b)
WHERE a.pid IN $pids RETURN b.pid $$, $1) AS (pid agtype);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY ON) EXECUTE bound('{"pids":
[16, 4242, 9000]}');
```
```
Hash Join (actual rows=3.00 loops=1)
-> Append (actual rows=20000.00 loops=1)
-> Seq Scan on "Product" b_2 (actual rows=20000.00 loops=1)
-> Hash (actual rows=3.00 loops=1)
-> Hash Join (actual rows=3.00 loops=1)
-> Seq Scan on "RELATED" _age_default_alias_0 (actual
rows=10000.00 loops=1)
-> Hash (actual rows=3.00 loops=1)
-> Seq Scan on "Product" a (actual rows=3.00 loops=1)
Filter: agtype_in_operator('[16, 4242,
9000]'::agtype, agtype_access_operator(VARIADIC ARRAY[properties,
'"pid"'::agtype]))
Rows Removed by Filter: 19997
Execution Time: 25.205 ms
```
A single value as a parameter uses the index (0.069 ms):
```pgsql
PREPARE one(agtype) AS
SELECT * FROM cypher('shop', $$ MATCH (a:Product)-[:RELATED]->(b)
WHERE a.pid = $pid RETURN b.pid $$, $1) AS (pid agtype);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY ON) EXECUTE one('{"pid":
16}');
```
```
Index Scan using product_pid on "Product" a (actual rows=1.00 loops=1)
Index Cond: (agtype_access_operator(VARIADIC ARRAY[properties,
'"pid"'::agtype]) = '16'::agtype)
```
Both list forms return the same rows (17, 4243, 9001).
**Expected behavior**
`a.pid IN $pids` uses `product_pid` the way the pasted list does, for
example by planning a parameter list as `= ANY(<agtype[] built from the
parameter>)`, which PostgreSQL can use for an index scan even when the array is
not a literal.
**Environment (please complete the following information):**
- Version: AGE 1.8.0 on PostgreSQL 18.6 (PGDG package `postgresql-18-age
1.8.0~rc0-2.pgdg12+1`, installed in `pgvector/pgvector:0.8.6-pg18`).
`transform_AEXPR_IN` is unchanged on `master` as of today.
**Additional context**
The SQL above is the whole reproduction (a fresh database, then psql).
Related but different: #2548 (`id(a) IN [...]` with a pasted list does not use
the id index).
--
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]
