crprashant opened a new pull request, #2417:
URL: https://github.com/apache/age/pull/2417
# Restore contsel/contjoinsel for containment & key-existence operators
Closes #2356.
## Why
The containment (`@>`, `<@`, `@>>`, `<<@`) and key-existence (`?`, `?|`,
`?&`) operators on `agtype` are bound to `matchingsel` / `matchingjoinsel`.
`matchingsel` is intended for pattern operators (LIKE/regex) — during planning
it invokes the operator's underlying function (`agtype_contains`) once **per
`pg_statistic` MCV entry**. With realistic statistics targets that produces a
planner-time regression that dominates simple OLTP-style point queries.
PostgreSQL core binds jsonb's analogous operators (`@>`, `<@`, `?` on
`jsonb`) to `contsel` / `contjoinsel` for exactly this reason. This PR restores
that precedent for `agtype`.
## What
- `sql/agtype_operators.sql`, `sql/agtype_exists.sql`: 10 operators flipped
to `contsel` / `contjoinsel`.
- `age--1.7.0--y.y.y.sql`: appended `ALTER OPERATOR ... SET (RESTRICT,
JOIN)` for all 10 operators so existing installs flip on `ALTER EXTENSION age
UPDATE`.
- `regress/sql/containment_selectivity.sql`: new test that pins the bindings
via `pg_operator`, plus a *no-leaked-matchingsel* aggregate guard and
functional smoke for all 10 operators.
- `regress/expected/cypher_match.out`, `regress/expected/cypher_vle.out`:
refresh expected — `test_enable_containment` now picks Nested Loop + Index Only
Scans over Seq Scan/Hash Join (a direct, **better** plan), and two `MATCH
p=...` and `show_list_use_vle` queries flip row order (they have no `ORDER BY`;
result set unchanged).
- `Makefile`: register the new test in `REGRESS`.
## Validation
### Regression suite
36/37 pass with `EXTRA_TESTS="pgvector fuzzystrmatch pg_trgm"`. Only
`age_upgrade` fails — pre-existing on master at `774e781b` (verified by `git
stash && installcheck` baseline of 32/33 with the same `age_upgrade` failure).
### Reporter's exact methodology — reproduced
Reporter's three scripts (`generate_graph.sql`,
`setup_func_for_workload.sql`, `workload_select.sql`) used unchanged. Run on
PG18 with `default_statistics_target = 1000` to populate MCV lists, matching
the reporter's analyzed-graph conditions:
| Metric | matchingsel | contsel | Delta |
|------------------------------|------------:|--------:|------:|
| EXPLAIN planning time | 1.42 ms | 0.97 ms | −32% |
| EXPLAIN execution time | 0.34 ms | 0.31 ms | ~0% |
| pgbench TPS (8 clients × 30s) | 5247 | 7378 | +40.6% |
The −32% planning-time delta lines up with the reporter's "~30% of execution
time spent in agtype_contains during planning" observation; the +40.6% TPS gain
matches the "severe TPS drop in OLTP-style workloads" they reported.
### Upgrade path
Validated end-to-end during the benchmark: operator bindings were flipped
from `matchingsel` → `contsel` via the same `ALTER OPERATOR` statements the
upgrade SQL ships, while operators remained functional throughout.
### `pg_operator` snapshots
```
-- Before (matchingsel binding shipped on master)
oprname | lhs | rhs | restrict_fn | join_fn
---------+--------+--------+--------------+------------------
<<@ | agtype | agtype | matchingsel | matchingjoinsel
<@ | agtype | agtype | matchingsel | matchingjoinsel
@> | agtype | agtype | matchingsel | matchingjoinsel
@>> | agtype | agtype | matchingsel | matchingjoinsel
? | agtype | agtype | matchingsel | matchingjoinsel
? | agtype | text | matchingsel | matchingjoinsel
?& | agtype | agtype | matchingsel | matchingjoinsel
?& | agtype | text[] | matchingsel | matchingjoinsel
?| | agtype | agtype | matchingsel | matchingjoinsel
?| | agtype | text[] | matchingsel | matchingjoinsel
-- After (this PR)
oprname | lhs | rhs | restrict_fn | join_fn
---------+--------+--------+-------------+-------------
<<@ | agtype | agtype | contsel | contjoinsel
<@ | agtype | agtype | contsel | contjoinsel
@> | agtype | agtype | contsel | contjoinsel
@>> | agtype | agtype | contsel | contjoinsel
? | agtype | agtype | contsel | contjoinsel
? | agtype | text | contsel | contjoinsel
?& | agtype | agtype | contsel | contjoinsel
?& | agtype | text[] | contsel | contjoinsel
?| | agtype | agtype | contsel | contjoinsel
?| | agtype | text[] | contsel | contjoinsel
```
### Driver workflows
Intentionally not run: this PR only adjusts `pg_operator` selectivity
metadata. There is no C code, type, or wire-protocol change that python / go /
node / JDBC drivers could observe.
## Notes for reviewers
- `matchingsel` does provide better estimates when good statistics exist on
heavily-analyzed `agtype` columns. PostgreSQL core accepts the same trade-off
for jsonb. A future improvement (out of scope here) would be a custom
`agtype_contains_selectivity` mirroring `jsonb_sel`; happy to file as a
follow-up if there's interest.
- The `containment_selectivity` regression test is intentionally minimal so
the diff is loud and precise if anyone re-introduces `matchingsel` here. The
aggregate guard catches future operator additions that forget the right helper.
--
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]
