[Solved] You have this query in PostgreSQL 11:WITH cte AS (SELECT * FROM orders WHERE total > 100) SELECT * FROM cte JOIN customers ON cte.customer_id = customers.... What change can improve performance? | PostgreSQL

PostgreSQL - Common Table Expressions

You have this query in PostgreSQL 11:

WITH cte AS (SELECT * FROM orders WHERE total > 100) SELECT * FROM cte JOIN customers ON cte.customer_id = customers.id WHERE customers.region = 'East';

The query is slower than expected. What change can improve performance?

AAdd an index on the orders.total column only.

BRemove the JOIN clause to simplify the query.

CRewrite the query using a subquery instead of a CTE.

DUse a window function inside the CTE.

Step-by-Step Solution

Solution:

Step 1: Recognize CTE materialization impact
CTEs materialize results, preventing join and filter optimizations in PG11.
Step 2: Use subquery to allow planner optimizations
Replacing the CTE with a subquery lets the planner optimize join order and filters better.
Final Answer:
Rewrite the query using a subquery instead of a CTE. -> Option C
Quick Check:
Subqueries enable better join optimization before PG12 [OK]

Quick Trick: Subqueries allow join optimization better than CTEs before PG12 [OK]

Common Mistakes:

Assuming adding indexes fixes CTE slowness
Removing JOIN changes query meaning
Using window functions won't fix materialization

Master "Common Table Expressions" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

You have this query in PostgreSQL 11:

Step 1: Recognize CTE materialization impact

Step 2: Use subquery to allow planner optimizations

Final Answer:

Quick Check:

Want More Practice?