Contents
Disjunctive Search across Normalized Hierarchy, High Selectivity (Score Sort)
- Join: users → stackoverflow_posts → comments
- Description: Disjunctive (OR) full-text search across normalized relational tables with relevance score ranking. This pattern represents the normalized relational equivalent of searching across a single flattened/denormalized document (e.g., Elasticsearch index). In ParadeDB, users can execute broad full-text searches across normalized table boundaries without paying the storage, memory, or ingestion cost of denormalizing parent/child entities.
- Note: We benchmark the “score sort” variant separately from the “local sort” variant to track the impact of scoring and top-K sorting on disjunctive join scans.
Query Info (statistics from 20m dataset):
- ‘ocaml’ selectivity:
- comments.text ||| ‘ocaml’: ~2.2K matches (~0.008%, ~2.2K joined rows)
- stackoverflow_posts.title ||| ‘ocaml’: ~1.6K matches (~0.008%, fanning out to ~1.8K joined rows)
- users.about_me ||| ‘ocaml’: ~137 matches (~0.006%, fanning out to ~14.0K joined rows)
- Combined disjunctive (
OR) joined selectivity: ~0.06% (~17.0K / 28.45M joined rows). Under database convention, this narrow filter represents high selectivity (filters out ~99.94% of joined rows).
Native PostgreSQL Variants
See ../join_disjunctive_score_sort_low_selectivity/README.md for a detailed explanation of both PostgreSQL GIN + B-Tree rewrite strategies and their recall tradeoffs. High-selectivity ('ocaml') behavior on the 20M dataset:
postgres_union_agg.sql(~372 ms, 100% exact recall):- Materializes and hash-aggregates all qualifying joined triples (~17K rows at high selectivity) before sorting.
postgres_early_topk.sql(~73 ms, 20/20 recall on this dataset):- Branch 1 (
usersonly) halts after inspecting 2 users; Branch 2 (posts LEFT JOIN users) halts after inspecting ~15 posts; Branch 3 (comments) uses an in-memoryParallel Hash Left Joinagainst the GIN-filteredposts LEFT JOIN usersset and only probesstackoverflow_posts_pkey/users_pkeyfor the final 20 rows. - All 20 top results are 3-way
(1,1,1)matches, yielding 20/20 exact recall.
- Branch 1 (