Skip to content

[EPIC] A collection of Sort + Limit / Top K optimizations #7195

@alamb

Description

@alamb

Is your feature request related to a problem or challenge?

This ticket has links a collection of various ways to make queries with LIMIT or various other variants (like row_number() predicates) both:

  1. Go faster
  2. Use less memory

These are typically called "Top K" style optimizations in databases and optimize the pattern of a sort followed by a limit

LIMIT(fetch = 10)
  SORT(x)
    INPUT...

The observation is that if the INPUT is much larger than the fetch (aka the K) it is much more efficient and less memory intensive to track the top 10 values rather than sort the entire input and discard everything except the top 10

Normally this done with special ExecutionPlan operators. What the operators do and behave depend on the exact query pattern.

Describe the solution you'd like

Describe alternatives you've considered

No response

Additional context

No response

Metadata

Metadata

Assignees

No one assigned

    Labels

    PROPOSAL EPICA proposal being discussed that is not yet fully underwayenhancementNew feature or request

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions