Non-BMW index scan returns results in wrong order when data spans memtable and segments

[tjgreen42]

Summary

When pg_textsearch.enable_bmw = off, the index scan returns results in storage/insertion order rather than score order. This causes the least-relevant documents to appear first instead of the most-relevant ones.

The bug manifests when data exists in both a spilled segment and the active memtable.

Reproduction

Tested on PG18 with pg_textsearch built from main (also observed on v0.5.0 against a 2.2M row production dataset):

CREATE EXTENSION pg_textsearch;

-- Create table and index
CREATE TABLE test_docs (id serial PRIMARY KEY, content text);
CREATE INDEX test_docs_bm25_idx ON test_docs USING bm25 (content)
  WITH (text_config = 'english');

-- Insert 50,000 highly-relevant documents
INSERT INTO test_docs (content)
SELECT 'database performance optimization query tuning indexing '
       || 'storage engine transaction isolation level concurrency '
       || 'control locking mechanism buffer pool management '
       || md5(i::text)
FROM generate_series(1, 50000) i;

-- Spill memtable to segment
SELECT bm25_spill_index('test_docs_bm25_idx');

-- Insert 5,000 low-relevance documents into memtable
INSERT INTO test_docs (content)
SELECT 'the quick brown fox jumps over the lazy dog and the cat '
       || 'sat on the mat while the database hummed quietly in the '
       || 'background serving requests to many users around the world '
       || md5(i::text)
FROM generate_series(50001, 55000) i;

-- BMW ON: returns correct top-k (most relevant first)
SET pg_textsearch.enable_bmw = on;
SELECT id, content <@> to_bm25query('test_docs_bm25_idx', 'database performance optimization') AS score
FROM test_docs
ORDER BY content <@> to_bm25query('test_docs_bm25_idx', 'database performance optimization')
LIMIT 10;

-- BMW OFF: returns WRONG order (least relevant first)
SET pg_textsearch.enable_bmw = off;
SELECT id, content <@> to_bm25query('test_docs_bm25_idx', 'database performance optimization') AS score
FROM test_docs
ORDER BY content <@> to_bm25query('test_docs_bm25_idx', 'database performance optimization')
LIMIT 10;

Note: The to_bm25query argument order above is for current main. On v0.5.0, the order is reversed: to_bm25query('query text', 'index_name').

Observed behavior

BMW ON returns the most relevant documents (score ~ -0.093):

 id |        score
----+---------------------
  1 | -0.0933843502371019
  2 | -0.0933843502371019
  ...

BMW OFF returns the least relevant documents first (score ~ -2.77e-05):

  id   |          score
-------+-------------------------
 50002 | -2.7701315048034303e-05
 50003 | -2.7701315048034303e-05
  ...

Both use Index Scan using test_docs_bm25_idx (confirmed via EXPLAIN). The per-document scores are correct in both modes -- the issue is purely in the ordering of results returned by the non-BMW scan path.

Expected behavior

Both BMW ON and BMW OFF should return results in the same order (most relevant first, i.e. most negative scores first).

Key factors

• Requires data in both a spilled segment and the active memtable
• The non-BMW path appears to merge segment and memtable results in storage order rather than score order
• Single-source queries (all in memtable or all in segments) may not exhibit this bug

[tjgreen42]

GUC is gone