Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 37 ยท Lazy Evaluation

Query Plans and the Optimizer

Read Polars query plans with explain, compare optimized and unoptimized plans, understand predicate, projection and slice pushdown, and switch optimizations off for debugging.

When you call collect() on a LazyFrame, Polars does not run your steps in the order you wrote them. It first builds a logical plan, a tree describing the query, then rewrites that tree using a set of optimizations, and only then executes. You can look at both plans with explain(), which is the best tool for understanding why a query is fast or slow.

In this lesson we use a CSV scan, because that is where the plans are most interesting. The browser build can create and print the plan for a file scan, even though it cannot execute it, so every example here really runs.

Python Create a CSV in the virtual file system

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Reading a plan

Plans print as an indented tree. Read from the bottom up: the bottom is the data source, and each line above transforms what the line below produces. In the output below, FROM links an operation to its input.

Python Optimized versus unoptimized plan

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Compare the two. As written, the scan reads all four columns (PROJECT */4 COLUMNS) and a separate FILTER runs afterwards. In the optimized plan:

  • PROJECT 2/4 COLUMNS: only region and amount are read. This is projection pushdown.
  • SELECTION: [(col("amount")) > (50.0)] is attached to the scan itself. This is predicate pushdown: rows are discarded as the file is read, so they never travel through the rest of the plan.

The main optimizations

Optimization What it does
Projection pushdown Reads only the columns the query actually uses
Predicate pushdown Moves filters as close to the data source as possible
Slice pushdown Pushes head, limit and slice into the scan so less is read
Common subplan elimination Computes a repeated sub-query once and caches it
Common subexpression elimination Computes a repeated expression once per context
Simplify expressions Folds constants (2 + 3 becomes 5) and applies simple algebra
Type coercion Inserts the casts needed so types line up
Predicate/projection through joins Pushes filters and column pruning into both join inputs

Projection pushdown in action

Python Unused columns disappear

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The optimizer notices that with_tax is never used and removes the computation entirely; the scan reads one column, id. You did not have to remember to delete the unused step.

Constant folding

Python Simplifying expressions

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Python itself evaluates 2 + 3 to 5 before Polars sees it, but the optimizer also folds work that is spelled out in Polars expressions, for example literals combined with pl.lit. The principle is the same: do as much as possible once, before touching the data.

Switching optimizations off

For debugging, or to check that an optimization is not the cause of a surprise, pass QueryOptFlags to explain or collect.

Python Turning optimizations on and off

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

With predicate_pushdown=False the FILTER stays as a separate step above the scan while projection still prunes columns. QueryOptFlags.none() turns everything off, which gives the slowest, most literal plan. Results must be identical either way; if they are not, that is a bug worth reporting. (In older Polars versions the same switches were keyword arguments such as predicate_pushdown=False directly on collect.)

Profiling a query

profile() runs the query and returns the result plus a timing table for each node, so you can see where the time goes. It works on in-memory queries here:

Python profile()

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The timing table has node, start and end columns (microseconds). Use it to find the slowest step, then try to rewrite it.

Some practical rules of thumb

  • Write filters and column selections wherever they read naturally; the optimizer will move them. You rarely need to hand-optimize order.
  • Optimization barriers: map_elements and other Python-function steps are opaque; Polars cannot reorder around them or know which columns they use. Prefer native expressions.
  • Use explain() after you write a slow query. Look for scans reading */N COLUMNS, filters that did not reach the scan, and repeated big sub-plans.
  • show_graph() draws the plan as a diagram (it needs Graphviz installed on your machine).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In a printed Polars plan, which direction do you read?

    The bottom of the tree is the source (the scan); each higher line transforms the output of the one below.
  2. 2. What does 'PROJECT 2/4 COLUMNS' on a CSV scan mean?

    Projection pushdown: the scan reads just the columns the rest of the query needs.
  3. 3. What is predicate pushdown?

    A predicate is a filter condition. Pushing it down (toward the scan) means fewer rows flow through later steps.
  4. 4. Why does a Python function passed to map_elements hurt optimization?

    Polars cannot see inside arbitrary Python code, so it cannot know which columns it uses or move steps across it, and it runs row by row.

Summary

  • explain() prints the plan, read bottom to top; explain(optimized=False) shows the plan as written.
  • Projection, predicate and slice pushdown, subplan/subexpression elimination and constant folding make queries cheaper without changing results.
  • QueryOptFlags turns optimizations on or off for debugging; profile() shows per-node timing.
  • Native expressions optimize well; Python functions are barriers.

Next: Lazy Pipelines and Streaming