Filtered context expansion reads the seed's whole side of its partition on PostgreSQL #1712

Problem

On PostgreSQL, get_segment_contexts with a property_filter can read every row on the seed's side of its partition, sort them, and keep the first few matches. The window query per seed and direction is ORDER BY the store's walk order with LIMIT the context size, which an ordered index scan answers by stopping at the first matches. The planner has no statistics for property predicates, so it estimates about one matching row per seed, costs the ordered walk as reading the whole side, and picks a bitmap scan over that side followed by a sort. The read then grows with the partition instead of with the context requested.

Measured on the #1661 store (shared tables), PostgreSQL 16 with 128 MB shared buffers, partitions' rows interleaved on disk, 20 seeds per request with 2 segments back and 4 forward:

main's store sends the same query and gets the same plan: a bitmap over the seed's side of the partition and a sort, filtering out 723 to 15,277 rows per statement on a 20,000-segment partition with a property filter matching 20%. The two stores have not been compared on identical data.

A session column and a session-led index (#1684) bound the read by the seed's session instead of its partition, but the estimate problem remains. In a 2.8M-row table, with 4 segments back and 12 forward, property filters took 5.2-5.6 s per request on an 80,000-segment session and 0.54-0.66 s on a 20,000-segment one, and a typed source_id filter matching 2.2% of rows planned the same way in its first five executions (190 ms per statement), when its estimate fell below the context size.

The SQLite path has not been measured.

Occurrences

• main at d19c20667: the window query, sqlalchemy_segment_store.py:418-439
• Overhaul segment store: shared tables with incarnation-scoped tenant keys (port of #1548) #1661 at 7b014ac34: the same query on the shared tables, sqlalchemy_segment_store.py:532-553

Proposal

Bound the walk in SQL. The lateral's inner query reads the seed's next W segments in walk order with no filter (ORDER BY ... LIMIT W), and the filter and LIMIT the context size apply over those rows. The inner query has no filter, so its estimate is exact and its plan is an ordered index scan whatever the filter; rows are pulled on demand, so a seed reads until it has its context or W rows, whichever comes first. On #1684's schema in the same 2.8M-row table (client-side timings, 20 seeds, 4 back and 12 forward), a request took 4-18 ms at W = 64 or 128 with any of the filters tried, against 435-5,510 ms for the current query with property filters and 9-46 ms with source_id filters; every plan was an index scan.

This changes what a filtered read returns: matching segments further than W from the seed are no longer context. To decide:

• W's value, and whether it is fixed rather than chosen by the caller. On a real store of coding-agent sessions (915k segments), a user-and-assistant-message filter keeps 89-94% of what an unbounded walk returns at W = 128 per direction, and the default kinds keep 99-100% by W = 48.
• Whether unfiltered reads take the same bound; they already stop at the context size.
• Whether it lands on the partition walk or on [event memory 1/7] Add time bounds, sources and expansion to EventMemory (port of #1597, without session) #1684's session walk, whichever ships first.