Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 37 ยท Performance

Query Plans and Optimizations

Read Polars query plans with explain(), see predicate and projection pushdown at work, learn what the optimizer rewrites, and profile a lazy query step by step.

When you write a lazy query, Polars does not run it line by line. It first builds a logical plan, a tree describing what you asked for, then rewrites that plan into a cheaper equivalent, and only then executes it. This is the same idea a database uses, and it is the reason a lazy Polars pipeline is often faster than the same steps written eagerly.

In this lesson you will learn to look at plans with explain(), recognize the main optimizations (predicate pushdown, projection pushdown, simplification, common subplan elimination), and measure where the time goes with profile().

Seeing a plan

Any LazyFrame has explain(). It returns the optimized plan as text; explain(optimized=False) shows the plan exactly as you wrote it. Plans read bottom to top: the data source is at the bottom, the final result at the top.

Python Your query versus the optimized query

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 outputs. In the plan you wrote, the filter is applied after the new column is computed, and the source reads every column. In the optimized plan:

  • The filter moved down, closer to the data, so double is computed only for the rows that survive.
  • The source line mentions that only some columns are needed (look for PROJECT 2/3 COLUMNS), so the unused column is never touched.

The exact wording of plan text changes between Polars versions, so learn the shape, not the text.

The main optimizations

flowchart BT
    S["data source"] --> F["filter early<br/>(predicate pushdown)"]
    F --> P["drop unused columns<br/>(projection pushdown)"]
    P --> C["compute once<br/>(common subplan elimination)"]
    C --> R["result"]

Reading from the bottom: the optimizer moves work towards the source and removes work nobody needs.

Optimization What it does Why it matters
Predicate pushdown Moves filters as close to the source as possible Fewer rows flow through later steps; with file scans, row groups can be skipped entirely
Projection pushdown Reads only the columns the query uses Less I/O and memory, especially with Parquet and wide tables
Slice pushdown Pushes head(n) / limit toward the source A scan_csv(...).head(5) reads only a few lines
Simplification Folds constants and removes no-ops (1 + 1 becomes 2) Less work per row
Common subplan / subexpression elimination Computes a repeated sub-query or expression once and caches it Self-joins and repeated expressions do not recompute
Join and sort reordering Chooses cheaper orders where results are equal Smaller intermediate tables

Pushdown into a file reader is the most dramatic. This snippet is read-only because the browser build cannot scan files, but on your computer explain() shows the filter and the column list inside the scan node:

Python
q = (
    pl.scan_parquet("trips/*.parquet")
    .filter(pl.col("year") == 2025)
    .select("city", "fare")
    .group_by("city")
    .agg(pl.col("fare").mean())
)
print(q.explain())     # the scan shows: PROJECT 2/12 COLUMNS, SELECTION: year == 2025

An optimization you can see

Projection pushdown is easiest to confirm from the plan text. Here a wide frame is filtered and one column is selected, and we count how many columns the source node reports:

Python Projection pushdown in the 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.

The query never needed the other eight columns, so Polars never carries them along. The same effect for a CSV or Parquet source means those columns are not even parsed.

Switching optimizations off

For debugging (or to find out whether an optimizer bug is to blame) you can disable them. In recent versions you pass a QueryOptFlags object to collect:

Python Compare results with and without the optimizer

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.

Both give the same answer: the optimizer may change how the result is computed, never what the result is. Individual flags exist too, for example predicate_pushdown=False.

profile(): where does the time go?

LazyFrame.profile() runs the query and returns the result plus a small table with a start and end time (in microseconds) for each plan node. It is the quickest way to find the slow step.

Python Profile a query

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 numbers differ from run to run and machine to machine, so compare steps within one profile rather than across runs.

Schema and errors before running

Because a lazy query is a plan, Polars can tell you the output schema and catch many mistakes (a misspelled column, a type mismatch) before it processes any data: lf.collect_schema() returns the schema without running the query.

Python Check the schema without running

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.

Helping the optimizer

  • Stay lazy as long as possible. Convert to eager only at the end with collect(). Calling collect() in the middle ends the optimization window.
  • Use expressions. The optimizer understands pl.col(...) expressions. It treats map_elements and map_batches with Python lambdas as black boxes: it cannot push filters through them or run them in parallel.
  • Filter and select early in your own code too. The optimizer will usually fix it, but a clear query is easier to read.
  • Avoid unnecessary materialization, such as turning columns into Python lists mid-pipeline.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How is a Polars query plan printed by explain() read?

    The source scan sits at the bottom and each step above it consumes the output of the step below.
  2. 2. What does predicate pushdown do?

    Filtering early means later steps, and the file reader, handle fewer rows.
  3. 3. What does projection pushdown do?

    Columns that are never used are not read or carried through the plan.
  4. 4. Why can a Python lambda inside map_elements hurt optimization?

    Python callables are opaque to the optimizer and run row by row under the GIL.

Summary

  • A lazy query becomes a plan, which Polars optimizes and then executes.
  • explain() shows the optimized plan (read bottom to top); explain(optimized=False) shows your original.
  • Predicate and projection pushdown, slice pushdown, simplification and subplan elimination are the big wins.
  • profile() times each node; collect_schema() checks types without running.
  • Stay lazy, stay with expressions, and collect once at the end.

Next: Fast Patterns and Common Slow Spots