Views and EXPLAIN

What you will be able to do

• CREATE VIEW for a repeated metric query
• SELECT from the view like a table
• Run EXPLAIN and spot Seq Scan vs key ideas

Why this matters for data work

Views package logic teammates can reuse. EXPLAIN shows how Postgres plans to run a query — you are not tuning production yet; you are learning to look.

Concepts

• CREATE OR REPLACE VIEW name AS SELECT …
• Views store the query, not a snapshot (unless materialized — out of scope).
• EXPLAIN / EXPLAIN ANALYZE — analyze actually runs the query.
• Seq Scan on small lab tables is normal.

Practice on your machine

export PGPASSWORD=dcubes PGHOST=127.0.0.1 PGPORT=15432 PGUSER=dcubes PGDATABASE=retail
psql << 'SQL'
CREATE OR REPLACE VIEW v_completed_by_store AS
SELECT s.store_id, s.store_name, s.region,
       count(*) AS orders,
       round(sum(o.line_total), 2) AS revenue
FROM orders o
JOIN stores s ON o.store_id = s.store_id
WHERE o.status = 'completed'
GROUP BY s.store_id, s.store_name, s.region;

SELECT * FROM v_completed_by_store ORDER BY revenue DESC;

EXPLAIN SELECT * FROM orders WHERE status = 'completed';
SQL

What you should see

• Five store rows of completed metrics (or fewer if a store has none).
• An EXPLAIN tree mentioning Seq Scan on this tiny table.

Common mistakes

• Treating a view as a cached table — base data changes, view output changes.
• EXPLAIN ANALYZE on heavy prod queries without care.
• Over-indexing labs — not the point yet.

Next

Query habits for DE