Skip to content

remove large limit optimization whenever there is a filter#19911

Merged
Mytherin merged 1 commit intoduckdb:v1.4-andiumfrom
guillesd:large_limit_opt_with_filters
Nov 25, 2025
Merged

remove large limit optimization whenever there is a filter#19911
Mytherin merged 1 commit intoduckdb:v1.4-andiumfrom
guillesd:large_limit_opt_with_filters

Conversation

@guillesd
Copy link
Contributor

@guillesd guillesd commented Nov 24, 2025 •
edited
Loading

We found this issue when using the python client (because of the `.show() method propagating a LIMIT), that large limit optimizations were getting in the way of filter pushdowns. The idea is to push the filter before applying the limit whenever there is a filter. The idea is from @Mytherin, I just added a test.

Original Optimized logical plan:

┌─────────────────────────────┐
│┌───────────────────────────┐│
││  Optimized Logical Plan   ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│       Expressions: a      │
│                           │
│          ~0 rows          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│       Expressions: a      │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│        Expressions:       │
│__internal_decompress_integ│
│     ral_bigint(#0, 0)     │
│             #1            │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│          ORDER_BY         │
│    ────────────────────   │
│           rowid           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│        Expressions:       │
│__internal_compress_integra│
│     l_uinteger(#0, 0)     │
│             #1            │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      COMPARISON_JOIN      │
│    ────────────────────   │
│      Join Type: SEMI      │
│                           ├──────────────┐
│        Conditions:        │              │
│      (rowid = rowid)      │              │
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│          SEQ_SCAN         ││           LIMIT           │
│    ────────────────────   ││    ────────────────────   │
│          Table: t         ││                           │
│   Type: Sequential Scan   ││                           │
└───────────────────────────┘└─────────────┬─────────────┘
                             ┌─────────────┴─────────────┐
                             │          SEQ_SCAN         │
                             │    ────────────────────   │
                             │       Filters: a<50       │
                             │          Table: t         │
                             │   Type: Sequential Scan   │
                             │                           │
                             │       ~400,000 rows       │
                             └───────────────────────────┘

Logical plan after this PR:

┌─────────────────────────────┐
│┌───────────────────────────┐│
││  Optimized Logical Plan   ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│       Expressions: a      │
│                           │
│          ~0 rows          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           LIMIT           │
│    ────────────────────   │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│          SEQ_SCAN         │
│    ────────────────────   │
│       Filters: a<50       │
│          Table: t         │
│   Type: Sequential Scan   │
│                           │
│       ~400,000 rows       │
└───────────────────────────┘

@Mytherin Mytherin merged commit 9b6c56b into duckdb:v1.4-andium Nov 25, 2025
57 of 58 checks passed
@Mytherin
Copy link
Collaborator

Mytherin commented Nov 25, 2025

Thanks!

github-actions bot pushed a commit to duckdb/duckdb-r that referenced this pull request Nov 27, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@9b6c56b
db341e9 
remove large limit optimization whenever there is a filter (duckdb/duckdb#19911)
Bump: ducklake, mysql_scanner (duckdb/duckdb#19910)
clean up tmp files while building extensions (duckdb/duckdb#19908)
Add v1.4.3 to Storage Version (duckdb/duckdb#19907)
@github-actions github-actions bot mentioned this pull request Nov 27, 2025
Merged
github-actions bot added a commit to duckdb/duckdb-r that referenced this pull request Nov 27, 2025
@github-actions @krlmlr
vendor: Update vendored sources to duckdb/duckdb@9b6c56b (#1748)
65da4a2 
remove large limit optimization whenever there is a filter (duckdb/duckdb#19911)
Bump: ducklake, mysql_scanner (duckdb/duckdb#19910)
clean up tmp files while building extensions (duckdb/duckdb#19908)
Add v1.4.3 to Storage Version (duckdb/duckdb#19907)

Co-authored-by: krlmlr <krlmlr@users.noreply.github.com>
@guillesd guillesd mentioned this pull request Dec 10, 2025
Open
2 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

Ready To Merge

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

2 participants

@guillesd @Mytherin