postgresql

PostgreSQL CTE (With)

Visualizing Common Table Expressions (WITH clauses) in query plans.

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
)
SELECT region, total_sales
FROM regional_sales
WHERE total_sales > (SELECT AVG(total_sales) FROM regional_sales);

CTEs (Common Table Expressions) often appear as their own nodes or sub-plans. In this example, "regional_sales" is calculated once and then scanned multiple times (CTE Scan). Modern PostgreSQL can sometimes optimize CTEs by inlining them, but materialization is still common for complex aggregations.

Interactive Execution Plan

EXPLAIN Output

Run: EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT TEXT) your_query;

Enter SQL EXPLAIN output to visualizeSupports PostgreSQL EXPLAIN ANALYZE, MySQL EXPLAIN, and SQLite EXPLAIN QUERY PLAN formats