feat: Add TopN Scan Validation by vrn21 · Pull Request #3816 · paradedb/paradedb

vrn21 · 2025-12-27T11:21:30Z

Ticket(s) Closed

Closes Make it harder to accidentally miss a TopN scan #3687

What

Added paradedb.check_topn_scan GUC that emits a warning when a query with LIMIT should use TopN scan but falls back to a slower execution method.

Files changed:

pg_search/src/gucs.rs (+17 lines) — GUC definition
pg_search/src/postgres/customscan/pdbscan/mod.rs (+96 lines) — Validation logic
pg_search/tests/pg_regress/sql/topn_validation.sql (+116 lines) — Tests

Why

ParadeDB silently falls back to slower execution methods when TopN cannot be used (missing fast: true, normalizer mismatch, too many ORDER BY columns). Queries work fine on small dev datasets but become 100-1000x slower in production with no warning.

How

Added validate_topn_expectation() function in choose_exec_method() that checks if TopN was expected (has LIMIT + search operator + no GROUP BY) but not chosen. If validation is enabled and TopN was missed, emits a warning with the reason.

Default: false (opt-in)

Usage:

SET paradedb.check_topn_scan = true;

SELECT * FROM products WHERE cat @@@ 'electronics' ORDER BY cat LIMIT 10;
-- WARNING: Query has LIMIT 10 but is not using TopN scan (using FastFieldMixed instead).
-- Reason: ORDER BY columns cannot be pushed down to the index.

Tests

pg_search/tests/pg_regress/sql/topn_validation.sql covers 8 scenarios:

Test	Scenario	Expected
1	Validation OFF, non-fast ORDER BY	No warning
2	Validation ON, non-fast ORDER BY	WARNING
3	Validation ON, valid TopN query	No warning
4	Too many ORDER BY columns	WARNING
5a	ORDER BY lower() matching index	No warning
5b	ORDER BY not matching lower() index	WARNING
6	Query without LIMIT	No validation
7	EXPLAIN with validation	WARNING
8	Disable validation mid-session	No warning

Note

Introduces an opt-in validator to catch missed TopN optimizations on LIMIT queries using ParadeDB search.

New GUC paradedb.check_topn_scan (default false) in pg_search/src/gucs.rs
Adds validate_topn_expectation() in pdbscan/mod.rs; invoked from choose_exec_method() to log a warning when TopN is expected (LIMIT + search + no GROUP BY) but ExecMethodType is not TopN, with reasons derived from PathKeyInfo (e.g., unusable pathkeys, prefix-only, >MAX_TOPN_FEATURES, normalizer mismatch)
Minor refactor to choose method then validate before returning
Regression tests tests/pg_regress/sql/topn_validation.sql (+ expected output) cover non-fast ORDER BY, valid TopN, too many ORDER BY cols, lower() normalizer mismatch, no LIMIT, EXPLAIN logging, and toggling the GUC

^{Written by Cursor Bugbot for commit 9b41430. This will update automatically on new commits. Configure here.}

CLAassistant · 2025-12-27T11:21:36Z

All committers have signed the CLA.

philippemnoel · 2025-12-31T15:18:00Z

This failing test runs need to pass before we can review: https://github.com/paradedb/paradedb/actions/runs/20538587650/job/58999930420?pr=3816. You can ignore the SchemaBot CI failure

vrn21 · 2025-12-31T15:36:16Z

yo sorry, thought that failed test was irrelevant for my changes, anyways looking into it rn

vrn21 · 2025-12-31T16:47:48Z

@philippemnoel lgtm?

philippemnoel · 2026-01-01T12:23:25Z

@philippemnoel lgtm?

Please rebase, your code shows 220 files changed. Then we'll need either Ming, Stu or Moe reviewing

vrn21 · 2026-01-01T17:39:30Z

Sorry for the mishap, have updated it @philippemnoel

philippemnoel · 2026-01-02T19:17:51Z

Sorry for the mishap, have updated it @philippemnoel

Thanks. I'll update your branch, as we have now fixed SchemaBot to work for external contributors

cursor

This PR is being reviewed by Cursor Bugbot

Details

You are on the Bugbot Free tier. On this plan, Bugbot will review limited PRs each billing cycle.

To receive Bugbot reviews on all of your PRs, visit the Cursor dashboard to activate Pro and start your 14-day free trial.

stuhood

Thanks a lot! Just for comments, and then I think that we can merge this.

stuhood

These look great: thanks for the help!

I'll handle the rest of getting this landed: it looks like there is one regress test that gets a complete false positive, but the rest are accurate.

vrn21 · 2026-01-07T17:49:01Z

fixed issues with some .out files

stuhood · 2026-01-07T17:56:22Z

fixed issues with some .out files

Not to worry: we'll take it from here. Thanks a lot!

…cing an `UnusableReason` enum.

Add is_minmax_implicit_limit() to detect when LIMIT 1 is injected by PostgreSQL's MIN/MAX rewrite, preventing spurious warnings for users.

stuhood

Thanks!

Add COSTS OFF to EXPLAIN statements and ANALYZE to setup. Add ORDER BY to INTERSECT query for consistent row ordering.

Ensures PostgreSQL has accurate statistics for all test tables, preventing plan variations across different environments.

# Ticket(s) Closed - Closes #3687 ## What Added `paradedb.check_topn_scan` GUC that emits a warning when a query with LIMIT should use TopN scan but falls back to a slower execution method. **Files changed:** - [pg_search/src/gucs.rs](file:///Users/vrn21/Developer/rust/paradedb/pg_search/src/gucs.rs) (+17 lines) — GUC definition - [pg_search/src/postgres/customscan/pdbscan/mod.rs](file:///Users/vrn21/Developer/rust/paradedb/pg_search/src/postgres/customscan/pdbscan/mod.rs) (+96 lines) — Validation logic - [pg_search/tests/pg_regress/sql/topn_validation.sql](file:///Users/vrn21/Developer/rust/paradedb/pg_search/tests/pg_regress/sql/topn_validation.sql) (+116 lines) — Tests ## Why ParadeDB silently falls back to slower execution methods when TopN cannot be used (missing `fast: true`, normalizer mismatch, too many ORDER BY columns). Queries work fine on small dev datasets but become 100-1000x slower in production with no warning. ## How Added [validate_topn_expectation()](file:///Users/vrn21/Developer/rust/paradedb/pg_search/src/postgres/customscan/pdbscan/mod.rs#1344-1445) function in [choose_exec_method()](file:///Users/vrn21/Developer/rust/paradedb/pg_search/src/postgres/customscan/pdbscan/mod.rs#1446-1488) that checks if TopN was expected (has LIMIT + search operator + no GROUP BY) but not chosen. If validation is enabled and TopN was missed, emits a warning with the reason. **Default**: `false` (opt-in) **Usage:** ```sql SET paradedb.check_topn_scan = true; SELECT * FROM products WHERE cat @@@ 'electronics' ORDER BY cat LIMIT 10; -- WARNING: Query has LIMIT 10 but is not using TopN scan (using FastFieldMixed instead). -- Reason: ORDER BY columns cannot be pushed down to the index. ``` ## Tests [pg_search/tests/pg_regress/sql/topn_validation.sql](file:///Users/vrn21/Developer/rust/paradedb/pg_search/tests/pg_regress/sql/topn_validation.sql) covers 8 scenarios: | Test | Scenario | Expected | |------|----------|----------| | 1 | Validation OFF, non-fast ORDER BY | No warning | | 2 | Validation ON, non-fast ORDER BY | WARNING | | 3 | Validation ON, valid TopN query | No warning | | 4 | Too many ORDER BY columns | WARNING | | 5a | ORDER BY lower() matching index | No warning | | 5b | ORDER BY not matching lower() index | WARNING | | 6 | Query without LIMIT | No validation | | 7 | EXPLAIN with validation | WARNING | | 8 | Disable validation mid-session | No warning |  --- > [!NOTE] > Introduces an opt-in validator to catch missed TopN optimizations on LIMIT queries using ParadeDB search. > > - New GUC `paradedb.check_topn_scan` (default `false`) in `pg_search/src/gucs.rs` > - Adds `validate_topn_expectation()` in `pdbscan/mod.rs`; invoked from `choose_exec_method()` to log a warning when TopN is expected (LIMIT + search + no GROUP BY) but `ExecMethodType` is not `TopN`, with reasons derived from `PathKeyInfo` (e.g., unusable pathkeys, prefix-only, >`MAX_TOPN_FEATURES`, normalizer mismatch) > - Minor refactor to choose method then validate before returning > - Regression tests `tests/pg_regress/sql/topn_validation.sql` (+ expected output) cover non-fast ORDER BY, valid TopN, too many ORDER BY cols, `lower()` normalizer mismatch, no LIMIT, EXPLAIN logging, and toggling the GUC > > <sup>Written by [Cursor Bugbot](https://cursor.com/dashboard?tab=bugbot) for commit 9b41430. This will update automatically on new commits. Configure [here](https://cursor.com/dashboard?tab=bugbot).</sup>  --------- Co-authored-by: Stu Hood <[email protected]> Co-authored-by: Mithun Chicklore Yogendra <[email protected]>

vrn21 requested review from mdashti, rebasedming and stuhood as code owners December 27, 2025 11:21

philippemnoel added the Do Not Cherry Pick PR should not be cherry-picked to other branches label Dec 31, 2025

vrn21 requested a review from philippemnoel as a code owner December 31, 2025 15:59

vrn21 force-pushed the main branch from d21cb72 to 05f7f89 Compare January 1, 2026 17:38

philippemnoel force-pushed the main branch from 05f7f89 to 9b41430 Compare January 2, 2026 19:17

cursor bot reviewed Jan 2, 2026

View reviewed changes

Comment thread pg_search/src/postgres/customscan/pdbscan/mod.rs Outdated

stuhood reviewed Jan 5, 2026

View reviewed changes

Comment thread pg_search/src/postgres/customscan/pdbscan/mod.rs Outdated

stuhood approved these changes Jan 7, 2026

View reviewed changes

vrn21 force-pushed the main branch from b5d0329 to d59770f Compare January 7, 2026 17:53

stuhood assigned mdashti and mithuncy and unassigned mdashti Jan 7, 2026

vrn21 added 6 commits January 16, 2026 00:04

feat: Add TopN Scan Validation

c984d64

cargo fmt happy

cba07a8

fix for ci

fb68f57

fix for ci: .out file had formatting issues

3bb078b

feat: provide more detailed reasons for TopN scan warnings by introdu…

c1d54a5

…cing an `UnusableReason` enum.

changes from comments

8da216b

vrn21 and others added 3 commits January 16, 2026 00:06

some more .out files

2af3a61

Add comments.

8a8679b

fix: suppress TopN warnings for PostgreSQL MIN/MAX optimization

c5ee761

Add is_minmax_implicit_limit() to detect when LIMIT 1 is injected by PostgreSQL's MIN/MAX rewrite, preventing spurious warnings for users.

mithuncy force-pushed the main branch from 1e6e957 to c5ee761 Compare January 15, 2026 18:46

mithuncy mentioned this pull request Jan 15, 2026

feat: Add TopN Scan Validation #3931

Closed

stuhood approved these changes Jan 15, 2026

View reviewed changes

mithuncy added 2 commits January 16, 2026 01:31

fix: make regression tests deterministic

4467c29

Add COSTS OFF to EXPLAIN statements and ANALYZE to setup. Add ORDER BY to INTERSECT query for consistent row ordering.

fix: add ANALYZE to mixedff_advanced setup for consistent plans

eb5e6ac

Ensures PostgreSQL has accurate statistics for all test tables, preventing plan variations across different environments.

stuhood merged commit 462740b into paradedb:main Jan 16, 2026
19 checks passed

Uh oh!

Conversation

vrn21 commented Dec 27, 2025 • edited by cursor bot Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Ticket(s) Closed

What

Why

How

Tests

Uh oh!

CLAassistant commented Dec 27, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

philippemnoel commented Dec 31, 2025

Uh oh!

vrn21 commented Dec 31, 2025

Uh oh!

vrn21 commented Dec 31, 2025

Uh oh!

philippemnoel commented Jan 1, 2026

Uh oh!

vrn21 commented Jan 1, 2026

Uh oh!

philippemnoel commented Jan 2, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

cursor bot left a comment

Choose a reason for hiding this comment

This PR is being reviewed by Cursor Bugbot

Uh oh!

Uh oh!

stuhood left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

stuhood left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

vrn21 commented Jan 7, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

stuhood commented Jan 7, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

stuhood left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

6 participants

vrn21 commented Dec 27, 2025 •

edited by cursor bot

Loading

CLAassistant commented Dec 27, 2025 •

edited

Loading

philippemnoel commented Jan 2, 2026 •

edited

Loading

vrn21 commented Jan 7, 2026 •

edited

Loading

stuhood commented Jan 7, 2026 •

edited

Loading