sergiogarciasilva commented on issue #2560:
URL: https://github.com/apache/age/issues/2560#issuecomment-5742733820
We can confirm this on 1.7.0 / PostgreSQL 17 with a real (not synthetic)
graph — ~45,700
`:Function` vertices, ~139,000 `:CALLS` edges, btree expression index on
`agtype_access_operator(VARIADIC ARRAY[properties, '<prop>'::agtype])`
(index name below is
anonymized, structure is real).
Inline map form (`{key: value}`), which compiles to `@>`:
```
MATCH (f:Function {name: 'x'}) RETURN f.uid
```
```
Seq Scan on "Function" f
Filter: (properties @> '{"name": "x"}'::agtype)
Rows Removed by Filter: 45718
Buffers: shared hit=9375
Execution Time: 66.309 ms
```
Same predicate, rewritten as `WHERE f.name = 'x'`, forces the containment
path off first
(`SET age.enable_containment = off`), which routes it through
`agtype_access_operator(...) = value` and `eqsel` against the
expression-index stats instead:
```
Index Scan using idx_<graph>_function_name on "Function" f
Index Cond: (agtype_access_operator(VARIADIC ARRAY[properties,
'"name"'::agtype]) = '"x"'::agtype)
Buffers: shared hit=8
Execution Time: 0.115 ms
```
576x on execution time, 1172x on buffer reads, over 45,720 vertices — one
selective key,
default statistics target, nothing exotic. This lines up with what's
reported here: the
per-key statistics on the expression index are sitting right there and get
used the moment
the predicate takes the `=` / `eqsel` shape; `@>`'s `RESTRICT` estimator
never looks at them.
Two things worth folding into this issue if a fix is scoped:
1. `age.enable_containment` already exists and already does exactly the
workaround this issue
is asking for at the planner level (forces the `=`/`eqsel` path instead
of `@>`). But it's
effectively undocumented — a repo-wide search only turns it up in
`ag_guc.c`/`ag_guc.h`,
the regression tests, and the RELEASE notes, not in any user-facing
manual page. Anyone
hitting this issue today has no way to find the existing lever without
reading the C
source.
2. The GUC does not help *parameterized* inline maps, but the `WHERE` form
does — and that
distinction matters for how a fix here gets scoped. #2339 fixed a crash
in `PREPARE` with a
property-map parameter while the GUC is off, and it fixed it by falling
back to `@>`
whenever the value is a parameter (the map can't be decomposed at parse
time because the
keys aren't known until execution). So `MATCH (f:Function {name: $p})` is
pinned to the
fixed-selectivity estimator no matter what the GUC says.
The `WHERE` form is not. We measured this on 1.7.0 using the third
`agtype` argument of
`cypher()`, with the GUC left at its default: from the sixth execution
on, PostgreSQL
switches to the generic plan and the parameter survives *inside* the
index condition rather
than being folded to a constant —
```
Index Cond: (agtype_access_operator(VARIADIC ARRAY[properties,
'"name"'::agtype])
= agtype_access_operator($1, '"fn"'::agtype))
```
Planning drops from 0.872 ms to 0.049 ms across executions. So
prepared-statement users can
already reach the `eqsel` path today, but only by writing `WHERE a.key =
$p` instead of an
inline map — which is the same rewrite this issue is about, and one more
reason the inline
map is the shape worth fixing.
Unrelated but visible in that same generic plan, in case it is useful to
#2489: the parameter
reaches `agtype_access_operator` as two plain arguments (`$1, '"fn"'`)
rather than packed
into a `VARIADIC ARRAY[...]`, the way the literal form is.
Happy to share the full anonymized `EXPLAIN (ANALYZE, BUFFERS)` output and
the DDL for the
expression index if useful for a regression test.
--
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]
