CTE materialization behavior in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does CTE stand for in SQL?

CTE stands for Common Table Expression. It is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

intermediate

What is materialization in the context of CTEs in PostgreSQL?

Materialization means the CTE is executed once and its result is stored temporarily. Later, the stored result is reused wherever the CTE is referenced in the query.

intermediate

How does PostgreSQL treat CTEs by default before version 12?

Before PostgreSQL 12, CTEs are always materialized by default. This means the CTE query runs once and the result is saved for reuse.

advanced

What change did PostgreSQL 12 introduce regarding CTE materialization?

PostgreSQL 12 introduced the ability to inline CTEs (not materialize) by default when possible, improving performance by allowing the planner to optimize the query better.

advanced

How can you force materialization or inlining of a CTE in PostgreSQL 12 and later?

You can use the keywords MATERIALIZED or NOT MATERIALIZED after the CTE name to force materialization or inlining respectively. Example: WITH cte_name AS MATERIALIZED (...) or WITH cte_name AS NOT MATERIALIZED (...).

What happens when a CTE is materialized in PostgreSQL?

A The CTE query runs multiple times for each reference.

B The CTE query runs once and the result is stored temporarily.

C The CTE is ignored by the query planner.

D The CTE is converted into a permanent table.

Materialization means the CTE query runs once and its result is stored temporarily for reuse.

Before PostgreSQL 12, how were CTEs treated by default?

A Converted to temporary tables.

B Always inlined into the main query.

C Ignored if not used.

D Always materialized.

Before version 12, PostgreSQL always materialized CTEs by default.

Which keyword forces a CTE to be inlined in PostgreSQL 12 and later?

A NOT MATERIALIZED

B MATERIALIZED

C INLINE

D NO_MATERIALIZE

The NOT MATERIALIZED keyword forces the CTE to be inlined instead of materialized.

Why might inlining a CTE improve query performance?

A Because it runs the CTE multiple times.

B Because it stores the result permanently.

C Because the planner can optimize the whole query better.

D Because it disables query optimization.

Inlining allows the query planner to optimize the entire query together, often improving performance.

Which PostgreSQL version introduced the option to inline CTEs by default?

A PostgreSQL 12

B PostgreSQL 11

C PostgreSQL 13

D PostgreSQL 10

PostgreSQL 12 introduced the ability to inline CTEs by default when possible.

Explain what CTE materialization means and how it affects query execution in PostgreSQL.

Think about how many times the CTE query runs and what happens to its result.

Describe the difference between MATERIALIZED and NOT MATERIALIZED keywords in PostgreSQL CTEs.

Consider how these keywords influence whether the CTE is stored or merged into the main query.