Repository navigation
Commit d48480e
feat: report statistics of materialized CTE scans (apache#26271)
## Which issue does this PR close?
- Closes apache#26230.
## Rationale for this change
A reference to a `MATERIALIZED` CTE reported unknown statistics. A query
could then get a worse plan than the same query with the CTE inlined.
```sql
SET datafusion.sql_parser.dialect = 'PostgreSQL';
CREATE TABLE small AS SELECT value AS k FROM generate_series(1, 5);
CREATE TABLE huge AS SELECT value AS k FROM generate_series(1, 100000);
EXPLAIN WITH c AS MATERIALIZED (SELECT k FROM small)
SELECT count(*) FROM huge JOIN c ON huge.k = c.k;
```
Before, the hash table is built on the 100000 rows of `huge`:
```
HashJoinExec: mode=CollectLeft, join_type=Inner, on=[(k@0, k@0)], projection=[]
DataSourceExec: partitions=1, partition_sizes=[13]
MaterializedCteScanExec: name=c
```
After, it is built on the 5 rows of the CTE:
```
HashJoinExec: mode=CollectLeft, join_type=Inner, on=[(k@0, k@0)], projection=[]
MaterializedCteScanExec: name=c
DataSourceExec: partitions=1, partition_sizes=[13]
```
## What changes are included in this PR?
The scan is a leaf and the body is a child of `MaterializedCteExec`, so
the planner copies the statistics to the scan in
`DefaultPhysicalPlanner::optimize_physical_plan`, before the optimizer
runs. The optimizer runs one time, as before. The EXPLAIN paths use the
same function.
Each scan gets the statistics of the body, computed with
`StatisticsContext`. A body that reads another CTE gets the statistics
of that CTE first. A rewrite of the body does not change its rows, so
the statistics stay correct. The statistics of one scan partition are
known only when the scan has one partition. The statistics API is not
changed.
Other plan changes that follow from the statistics: an exact row count
of the body can answer `count(*)`, and the body then does not run. One
pinned plan in `cte_materialized.slt` has a new join order.
The ordering and the hash partitioning of the scans are a follow-up:
apache#26424.
The scan gets the complete statistics of the body, not only the row
count: column min, max, null count, distinct count and byte size. Thus
the outer query uses the column statistics of the CTE. For example, a
filter above the scan derives its bounds from the column bounds of the
body, the same as in the inlined plan. The statistics of one partition
are given only when the scan has one partition. Otherwise they are
unknown, because scan partitions do not map to body partitions.
**Comparison with PostgreSQL and DuckDB.** This is what PostgreSQL and
DuckDB do. PostgreSQL gives a CTE scan the row estimate of the CTE plan
([allpaths.c](https://github.com/postgres/postgres/blob/af090508018be5ace443ba6e2814500c10842a4d/src/backend/optimizer/path/allpaths.c#L3105)).
DuckDB copies the statistics of the body to each reference
([propagate_cte.cpp](https://github.com/duckdb/duckdb/blob/1b4c3e54ba3625bac56ad184f0ce642a8d2bfbd5/src/optimizer/statistics/operator/propagate_cte.cpp#L34)).
## Are these changes tested?
Yes. `cte_materialized.slt`: the join-order plan, the statistics in
EXPLAIN, `count(*)` from exact statistics, a chain of two CTEs, a scalar
subquery and a view. A unit test for the statistics of a scan.
## Are there any user-facing changes?
Plans of queries with `MATERIALIZED` CTEs can change. New public
functions: `MaterializedCteScanExec::with_body_statistics` and
`MaterializedCteExec::body`.
---------
Co-authored-by: Claude Opus 5.5 <[email protected]>1 parent c6ab9b2 commit d48480e
3 files changed
Lines changed: 392 additions & 70 deletions
File tree
- datafusion
- core/src
- physical-plan/src
- sqllogictest/test_files
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
72 | 72 | | |
73 | 73 | | |
74 | 74 | | |
75 | | - | |
76 | | - | |
| 75 | + | |
| 76 | + | |
77 | 77 | | |
78 | 78 | | |
79 | 79 | | |
| |||
112 | 112 | | |
113 | 113 | | |
114 | 114 | | |
115 | | - | |
| 115 | + | |
116 | 116 | | |
117 | 117 | | |
118 | 118 | | |
| |||
239 | 239 | | |
240 | 240 | | |
241 | 241 | | |
242 | | - | |
243 | | - | |
244 | 242 | | |
245 | 243 | | |
246 | 244 | | |
| |||
318 | 316 | | |
319 | 317 | | |
320 | 318 | | |
321 | | - | |
322 | | - | |
| 319 | + | |
| 320 | + | |
| 321 | + | |
323 | 322 | | |
324 | 323 | | |
325 | 324 | | |
326 | 325 | | |
327 | 326 | | |
328 | | - | |
329 | | - | |
| 327 | + | |
| 328 | + | |
330 | 329 | | |
331 | 330 | | |
332 | 331 | | |
333 | | - | |
| 332 | + | |
334 | 333 | | |
335 | 334 | | |
| 335 | + | |
| 336 | + | |
| 337 | + | |
| 338 | + | |
| 339 | + | |
| 340 | + | |
| 341 | + | |
| 342 | + | |
| 343 | + | |
| 344 | + | |
| 345 | + | |
| 346 | + | |
| 347 | + | |
| 348 | + | |
| 349 | + | |
| 350 | + | |
| 351 | + | |
| 352 | + | |
| 353 | + | |
| 354 | + | |
| 355 | + | |
| 356 | + | |
| 357 | + | |
| 358 | + | |
| 359 | + | |
| 360 | + | |
| 361 | + | |
| 362 | + | |
| 363 | + | |
| 364 | + | |
| 365 | + | |
| 366 | + | |
| 367 | + | |
| 368 | + | |
| 369 | + | |
| 370 | + | |
| 371 | + | |
| 372 | + | |
| 373 | + | |
| 374 | + | |
| 375 | + | |
| 376 | + | |
| 377 | + | |
| 378 | + | |
| 379 | + | |
| 380 | + | |
| 381 | + | |
| 382 | + | |
| 383 | + | |
| 384 | + | |
| 385 | + | |
| 386 | + | |
| 387 | + | |
| 388 | + | |
| 389 | + | |
| 390 | + | |
| 391 | + | |
| 392 | + | |
| 393 | + | |
| 394 | + | |
| 395 | + | |
| 396 | + | |
| 397 | + | |
| 398 | + | |
| 399 | + | |
| 400 | + | |
| 401 | + | |
| 402 | + | |
| 403 | + | |
| 404 | + | |
| 405 | + | |
| 406 | + | |
| 407 | + | |
| 408 | + | |
| 409 | + | |
| 410 | + | |
| 411 | + | |
| 412 | + | |
| 413 | + | |
| 414 | + | |
| 415 | + | |
| 416 | + | |
| 417 | + | |
| 418 | + | |
| 419 | + | |
| 420 | + | |
| 421 | + | |
| 422 | + | |
| 423 | + | |
| 424 | + | |
| 425 | + | |
| 426 | + | |
| 427 | + | |
| 428 | + | |
| 429 | + | |
336 | 430 | | |
337 | 431 | | |
| 432 | + | |
338 | 433 | | |
339 | | - | |
340 | | - | |
341 | | - | |
342 | | - | |
343 | | - | |
344 | | - | |
345 | | - | |
346 | | - | |
347 | | - | |
348 | | - | |
349 | | - | |
350 | | - | |
351 | | - | |
352 | | - | |
353 | | - | |
| 434 | + | |
| 435 | + | |
354 | 436 | | |
355 | 437 | | |
356 | | - | |
357 | | - | |
358 | | - | |
359 | | - | |
360 | | - | |
361 | | - | |
362 | | - | |
363 | | - | |
364 | | - | |
365 | | - | |
366 | | - | |
367 | | - | |
368 | | - | |
369 | | - | |
370 | | - | |
| 438 | + | |
371 | 439 | | |
372 | 440 | | |
373 | 441 | | |
| |||
3301 | 3369 | | |
3302 | 3370 | | |
3303 | 3371 | | |
3304 | | - | |
| 3372 | + | |
| 3373 | + | |
| 3374 | + | |
| 3375 | + | |
| 3376 | + | |
| 3377 | + | |
| 3378 | + | |
| 3379 | + | |
| 3380 | + | |
3305 | 3381 | | |
3306 | 3382 | | |
3307 | 3383 | | |
| |||
3311 | 3387 | | |
3312 | 3388 | | |
3313 | 3389 | | |
| 3390 | + | |
3314 | 3391 | | |
3315 | 3392 | | |
3316 | 3393 | | |
| |||
0 commit comments