Contents
Permissioned Search, Low Selectivity (Score Sort)
- Join: Single Feature (BM25 Score)
- Description: A Full Text Search (BM25) drives the ranking, restricted by a low-selectivity (broad) foreign filter on
users(u.reputation > 100). Because the foreign filter passesRankJoinRuleGate 3 (>= 2.5%unscored selectivity and>= 10,000rows),rank_joinplansRankJoinExec(NRJN) and probesusersvia Random Access (RA).
Query Info (statistics from 20M dataset; 100k dataset has ~82% on reputation > 100 due to active-user sampling skew):
stackoverflow_posts.title ||| 'how using get create': ~2.03M matches (~10.1%)users.reputation > 100: 791,228 / 2,261,291 users (~35.0%), yielding 1,463,595 joined post matches- Under database convention, this broad filter represents low selectivity (large candidate pool).
Native PostgreSQL Baseline Omission
postgres.sql is omitted across the join_permissioned_search_* tiers:
- Equivalent disjunctive
to_tsvector('english', p.title) @@ to_tsquery('english', 'how | using | get | create')matches 1,286,163 posts and requires aParallel Bitmap Heap Scan+ts_rank()evaluation across all 1.29M heap tuples before sorting (~26.7 s per iteration even under selectiveusersfilters, because PostgreSQL drives from the GIN scan onstackoverflow_posts). - Note: an earlier
postgres.sqlusedplainto_tsquery('english', 'how using get create'), which PostgreSQL parses as the conjunction'use' & 'get' & 'creat'(496 posts instead of 1.29M), benchmarking a different query.