Examples

Lazy queries over a large file

Scan a Parquet or CSV file with up to 5 million rows, let Polars push the filter and column selection into the scan, and compare the time with reading the whole file first.

Uses phpolars

Inputs

Rows in the file

File format

Parquet CSV

Keep orders with an amount above Running

A lazy query describes the work before any data is read. scan_parquet() returns a LazyFrame, the verbs add steps to its plan, and collect() hands the whole plan to Polars, which optimizes it and runs it in parallel. Here the file holds 1,000,000 orders with 8 columns, and the query uses five of them.

use function Phpolars\{col, scan_parquet};

$top = scan_parquet('orders.parquet')
    ->filter(col('amount')->gt(500)->and(col('returned')->not()))
    ->groupBy('region')
    ->agg(
        col('order_id')->count()->alias('orders'),
        col('amount')->sum()->round(2)->alias('revenue'),
        col('customer_id')->nUnique()->alias('customers'),
    )
    ->sort('revenue', descending: true)
    ->collect();

Lazy scan and query: 34 ms
Read the whole file, then query: 81 ms
Regions: 20

Revenue by region

region	orders	revenue	customers
region-19	27,095	25,198,337.72	4,999
region-18	26,623	24,224,207.81	4,999
region-17	26,116	23,240,850.62	4,999
region-16	25,579	22,251,443.89	4,999
region-15	24,994	21,245,438.28	4,999
region-14	24,387	20,239,821.33	4,999
region-13	23,726	19,217,533.83	4,999
region-12	23,014	18,181,084.39	4,999
region-11	22,250	17,133,166.42	4,999
region-10	21,431	16,073,126.90	4,999
region-09	20,535	14,990,609.26	4,999
region-08	19,570	13,894,456.87	4,999
region-07	18,506	12,769,938.54	4,999
region-06	17,350	11,624,558.95	4,999
region-05	16,072	10,447,545.12	4,999
region-04	14,667	9,240,432.52	4,999
region-03	13,097	7,989,536.44	4,999
region-02	11,350	6,696,781.41	4,942
region-01	9,378	5,345,864.02	4,805
region-00	7,140	3,926,658.72	4,544

explain() prints the optimized plan. Polars pushes the filter into the scan, so rows are dropped while the file is read, and it reads only the columns the query uses.

SORT BY [descending: [true]] [col("revenue")]
  AGGREGATE[maintain_order: false]
    [col("order_id").count().alias("orders"), col("amount").sum().round().alias("revenue"), col("customer_id").n_unique().alias("customers")] BY [col("region")]
    FROM
    simple π 4/4 ["region", "order_id", "amount", ... 1 other column]
      Parquet SCAN [orders.parquet]
      PROJECT 5/8 COLUMNS
      SELECTION: (col("amount") > 500.0) & col("returned").not()
      ESTIMATED ROWS: 1000000

The eager version reads every column of every row into memory before filtering. The lazy version skips that work, and the gap grows with the file. Parquet stores columns separately, so leaving columns out saves more than it does with CSV.

Script: site/vignettes/lazy-queries.php in phpstats/phpolars@8487062.