A btree index scan on agtype that starts at a >= bound skips the keys equal to it (n.id >= 995 returns 996 to 1000)

Describe the bug

A btree index scan on an agtype key that starts at a >= bound skips the keys equal to the bound. MATCH (n:P) WHERE n.id >= 995 RETURN n.id through the documented property expression index returns 996 to 1000; without the index it returns 995 to 1000. A closed range n.id >= 500 AND n.id <= 510 returns 10 rows instead of 11, and a >= 5 AND a <= 5 returns none while a = 5 returns one.

It is not specific to Cypher or to expression indexes: a plain agtype column with a default btree index does the same, for integer, float and string values, and on a 10-row index that fits on one leaf page. Through the same index, >, <, <= and = return the right rows, and so does >= when the scan does not start at it (a DESC index, or ORDER BY a DESC). A >= bound with no equal key (a >= 4.5) is also right. A float8 column with the same data and queries on the same server is right.

What I checked: agtype_ops_btree maps strategies 1 to 5 to <, <=, =, >, >= on (agtype, agtype); agtype_btree_cmp returns 0 for a stored value against the equal constant, in both argument orders; and agtype_ge returns true for that pair. So the functions agree that the values are equal, and the row is lost when a forward scan is positioned at the >= bound. I have not found the line responsible.

We found this in a benchmark: after the write phase, a read-back MATCH (q:Person) WHERE q.id >= $f RETURN q.id, ... on AGE returned one person fewer than eight other graph engines. The planner chose the index scan itself there (2,000 persons), so no planner settings are involved in the application.

How are you accessing AGE (Command line, driver, etc.)?

psql, and psycopg 3 in the application (same results).

What data setup do we need to do?

CREATE EXTENSION IF NOT EXISTS age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;
SELECT create_graph('rng');
SELECT * FROM cypher('rng', $$ UNWIND range(1, 1000) AS i CREATE (:P {id: i}) $$) AS (v agtype);
CREATE INDEX p_id ON rng."P" USING btree (agtype_access_operator(properties, '"id"'::agtype));
ANALYZE rng."P";

What is the necessary configuration info needed?

None. On a table this small the planner may prefer a sequential scan, so the repro sets enable_seqscan = off and enable_bitmapscan = off to make it take the index.

What is the command that caused the error?

SET enable_seqscan = off;
SET enable_bitmapscan = off;
SELECT string_agg(v::text, ',') FROM cypher('rng', $$ MATCH (n:P) WHERE n.id >= 995 RETURN n.id $$) AS (v agtype);
-- 996,997,998,999,1000
SELECT count(*) FROM cypher('rng', $$ MATCH (n:P) WHERE n.id >= 500 AND n.id <= 510 RETURN n $$) AS (v agtype);
-- 10

The same without a graph:

CREATE TABLE t (a agtype);
INSERT INTO t SELECT i::text::agtype FROM generate_series(1, 10) i;
CREATE INDEX ON t (a);
SET enable_seqscan = off;
SET enable_bitmapscan = off;
SELECT string_agg(a::text, ',') FROM t WHERE a >= '5'::agtype;        -- 6,7,8,9,10
SELECT count(*) FROM t WHERE a >= '5'::agtype AND a <= '5'::agtype;   -- 0
SELECT count(*) FROM t WHERE a = '5'::agtype;                         -- 1
SELECT string_agg(a::text, ',') FROM t WHERE a > '4'::agtype;         -- 5,6,7,8,9,10

Expected behavior

The index returns the rows the predicate selects without it: 995 to 1000; 11 rows; 5 to 10; 1; 1; 5 to 10.

Environment (please complete the following information):

The same results on every image tried:

image	PostgreSQL	AGE
`apache/age:release_PG18_1.8.0`	18.6	1.8.0
`apache/age:dev_snapshot_master`	18.6	master
`apache/age:dev_snapshot_PG18`	18.4	PG18 branch
`apache/age:release_PG17_1.7.0`	17.11	1.7.0
`apache/age:release_PG16_1.6.0`	16.10	1.6.0
`pgvector/pgvector` pg18 + PGDG `postgresql-18-age`	18.6	1.8.0

Additional context

Results through the index on the 1,000-vertex graph above, and on a 1,000-row plain agtype column:

predicate	through the index	without the index
`>= 995`	996 to 1000	995 to 1000
`> 994`	995 to 1000	995 to 1000
`<= 5`	1 to 5	1 to 5
`>= 500 AND <= 510`	10 rows	11 rows
`= 995`	1 row	1 row
`>= 995`, `DESC` index	995 to 1000	995 to 1000

benchmarks: the instrument changes for the 26.10.1 re-pin campaign, w…

The cause is the strategy numbers in agtype_ops_btree. It registers OPERATOR 4 > and OPERATOR 5 >=, but btree reads strategy 4 as >= and 5 as > (stratnum.h). _bt_first positions a forward scan from the strategy number, so a >= bound (and a commuted v <= prop) starts after the keys equal to it, as if it were >. _bt_checkkeys then filters with the operator's own function. That is why > is right (it starts at the equal keys, then drops them), why backward scans are right, and why a >= 5 AND a <= 5 returns nothing.

It is fixed on master by #2577 (merged 2026-10-11, d1b7cc4), after this issue was opened, which would explain dev_snapshot_master still showing it. Every release branch, PG11 to PG18, still has the old mapping, so the release_PG18_1.8.0, release_PG17_1.7.0 and release_PG16_1.6.0 images in the table are affected until #2577 is backported:

  OPERATOR 4 >,
  OPERATOR 5 >=,

An existing install can be corrected in place, with no reindex: the index order comes from support function 1, agtype_btree_cmp, which does not change. We ran this as superuser on a 1.8.0 / PostgreSQL 18.6 database with about 3,300 agtype btree indexes. Afterwards the indexed >= forms (commuted, string values, a Cypher parameter) matched the sequential scan, and the change replicated to the standby. In psql:

BEGIN;
SELECT oid AS fam FROM pg_opfamily
 WHERE opfname = 'agtype_ops_btree'
   AND opfmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') \gset
UPDATE pg_amop SET amopstrategy = 6 WHERE amopfamily = :fam AND amopstrategy = 4;
UPDATE pg_amop SET amopstrategy = 4 WHERE amopfamily = :fam AND amopstrategy = 5;
UPDATE pg_amop SET amopstrategy = 5 WHERE amopfamily = :fam AND amopstrategy = 6;
-- expect 1 <, 2 <=, 3 =, 4 >=, 5 >
SELECT amopstrategy, amopopr::regoperator FROM pg_amop WHERE amopfamily = :fam ORDER BY 1;
COMMIT;

Run it only while strategy 4 is still >; a second run swaps them back. One caveat, which #2577's upgrade script also handles: a session that was open before the swap keeps the old "greater than" operator in its type cache, and an ORDER BY ... DESC on agtype in that session then fails with operator NNNNN is not a valid ordering operator. DISCARD ALL does not clear it; reconnecting does. #2577 touches the pg_opclass row so the cache is invalidated instead.

Confirmed, thank you: master is fixed, the 1.8.0 release is not, and your in-place swap fixes 1.8.0.

The repro from the issue (1,000 vertices, a btree index on id, sequential and bitmap scans off), each on a fresh container (PostgreSQL 18.6, AGE 1.8.0):

image	SQL `id >= 995`	SQL `id >= 500 AND id <= 510`	Cypher `n.id >= 995`
`dev_snapshot_master`	995, 996, 997, 998, 999, 1000	11	995, 996, 997, 998, 999, 1000
`release_PG18_1.8.0`	996, 997, 998, 999, 1000	10	996, 997, 998, 999, 1000
`release_PG18_1.8.0` after the swap in your comment	995, 996, 997, 998, 999, 1000	11	995, 996, 997, 998, 999, 1000

The answers without the index are 995, ..., 1000 and 11 everywhere. dev_snapshot_master is the image built on 2026-10-13, after #2577 merged. After the swap, pg_amop reads 1 <, 2 <=, 3 =, 4 >=, 5 > as you expected.

I'll leave the issue open until #2577 reaches the release branches, unless you would rather track the backport there; either is fine with me.

Navigation Menu

Repository navigation

A btree index scan on agtype that starts at a `>=` bound skips the keys equal to it (`n.id >= 995` returns 996 to 1000) #2587

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

A btree index scan on agtype that starts at a >= bound skips the keys equal to it (n.id >= 995 returns 996 to 1000) #2587

Description

Activity

jok-carbonarc commented on Oct 17, 2026

tae898 commented on Oct 19, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

A btree index scan on agtype that starts at a `>=` bound skips the keys equal to it (`n.id >= 995` returns 996 to 1000) #2587