Multi-field relevance scores depend on predicate order

[erickreutz]

When a search matches multiple TIN-indexed columns, tin.full_score(ctid) only includes one column's relevance. Swapping the order of equivalent OR predicates changes which matching rows receive a zero score.

Environment: Lead 1.0.3 at bd95c7e51b6afce81396790852ee2f2c169570ad, PostgreSQL 18.6, Linux arm64.

Reproduction

CREATE EXTENSION IF NOT EXISTS tin;

BEGIN;
CREATE TEMP TABLE scoring_probe (
  id integer PRIMARY KEY,
  title text,
  body text
);
CREATE INDEX ON scoring_probe USING tin (title);
CREATE INDEX ON scoring_probe USING tin (body);

INSERT INTO scoring_probe VALUES
  (1, 'quiet', 'ruby ruby'),
  (2, 'ruby', 'quiet');

-- Title predicate first.
SELECT id, tin.full_score(ctid) AS score
FROM scoring_probe
WHERE title ==> 'ruby' OR body ==> 'ruby'
ORDER BY id;

-- Equivalent matching condition, with body first.
SELECT id, tin.full_score(ctid) AS score
FROM scoring_probe
WHERE body ==> 'ruby' OR title ==> 'ruby'
ORDER BY id;

ROLLBACK;

Observed:

Document	Title predicate first	Body predicate first
1: term only in body	0	0.87138504
2: term only in title	0.6931472	0

Both queries find both documents, but the score depends on predicate order. With ORDER BY score DESC LIMIT ..., the returned ranking can change even though the search condition is equivalent.

Expected: combine relevance from the matching indexed fields, independent of their order in the condition. For this fixture, scoring each field independently and summing gives 0.87138504 for document 1 and 0.6931472 for document 2. This follows the documented multi-column scoring behavior.

Suspected cause

In score_support, find_map selects the first matching indexed expression. The subsequent same_expression filter combines predicates only for that expression, leaving other columns out of scoring.

Local workaround

Scoring each field at its own query level and summing produces the expected results:

SELECT id,
  COALESCE((
    SELECT tin.full_score(s.ctid)
    FROM scoring_probe s
    WHERE s.id = scoring_probe.id AND s.title ==> 'ruby'
  ), 0)
  + COALESCE((
    SELECT tin.full_score(s.ctid)
    FROM scoring_probe s
    WHERE s.id = scoring_probe.id AND s.body ==> 'ruby'
  ), 0) AS score
FROM scoring_probe
WHERE title ==> 'ruby' OR body ==> 'ruby'
ORDER BY id;

Run that query before the reproduction's ROLLBACK. It returns the expected per-field sums. An adapter using this workaround also passes tests for field-order independence, body-only hits with NULL titles, and arbitrary selected field subsets. It adds query work, so it would be useful for Lead to support the original query shape directly.

This may be related to #11's score-binding findings, but it reproduces with literal queries on different indexed columns, without parameters or aggregates. I have not tested production TIN for this behavior.

[erickreutz]

I’ve now run the original two-row reproduction against actual PlanetScale TIN 1.0.3 on PostgreSQL 18.6, Linux arm64. TIN combines both fields correctly, independent of predicate order:

Document	Title predicate first	Body predicate first	Per-field subquery sum
1: term only in body	0.87138504	0.87138504	0.87138504
2: term only in title	0.6931472	0.6931472	0.6931472

This confirms a Lead/TIN compatibility difference for the SQL in this report. Production TIN handles the original query directly; the per-field workaround is needed for Lead’s score binding.

Additional fresh-fixture comparisons also agree between native TIN scoring and the workaround: reversed field order, a selected single-field subset, AND terms distributed across fields, and a phrase plus a term. These include NULL titles, body-only hits, and documents matching both fields.

The query plans show why native support matters: TIN uses a Tin Multi-Index Scan, while the workaround runs correlated scoring subqueries for each matching row. This was a correctness and query-plan check on small fixtures, not a performance benchmark.