`WHERE n.prop IN $param` never uses the property's index; the same list written into the query does (100x on 20k vertices)

[tae898]

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?

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:

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:

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):

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).