14 - Query Planning & Optimization (CMU Intro to Database Systems / Fall 2022)

15-445/645 Intro to Database Systems (Fall 2022)
Carnegie Mellon University

[Stolb]

Thank you for this course! Let's provide small addition about Postgres functions.
Postgres doesn't know about possibility to return null for both now and random functions. As far I remember, there is no such metadata for functions in the catalog. But these functions have different types. now is a stable function that returns the same value during statement processing. random is a volatile function that really may be affected by side effects. That is why the optimizer able to understand that now should be called before scan as one time filter and scan may be run only in case of filter is true. random is applied for each fetched line without optimization.

[w-mcode]

34:48 Quick comment about expression rewriting: While most people would not write 'WHERE 1 = 0' and similar 'stupid' constructs directly, they still regularly appear after other optimization passes have been applied. For instance, after doing 'decomposing queries' and going back to the optimizer, or when you run a parameterized query for particular parameter values.

[王鑫涛-kt]

first time seeing andy pickup a chalk🤣

[zhjwpku]

hey andy, not sure what postgres version were you using, but I tried this on PostgreSQL 11, select * from test_table where random() = NULL output the `One-Time Filter: false`.