15-views-indexes-performance.sql

-- ============================================================
-- SQL Masterclass — Chapter 15: Views, Indexes & Performance
-- ============================================================
-- 🟣 POSTGRESQL SPECIFIC — Production Mastery
--
-- In this chapter you will learn:
--   • Views — virtual tables from queries
--   • Materialized views — cached query results
--   • Indexes — B-tree, GIN, and when to use them
--   • EXPLAIN and EXPLAIN ANALYZE — reading query plans
--   • Query optimization techniques
--   • Partitioning basics
--   • Common anti-patterns to avoid
-- ============================================================
-- ⚠️ This chapter requires PostgreSQL. It will NOT work in SQLite.
-- ============================================================


-- ============================================================
-- 15.1  VIEWS — Virtual tables
-- ============================================================
-- A view is a saved SQL query that acts like a table.
-- It runs the query every time you access it.

-- Create a view for enriched order details
CREATE OR REPLACE VIEW v_order_details AS
SELECT
    o.order_id,
    o.order_status,
    o.order_purchase_timestamp::DATE AS order_date,
    c.customer_state,
    c.customer_city,
    oi.product_id,
    oi.price,
    oi.freight_value,
    oi.price + oi.freight_value AS total_item_cost,
    COALESCE(t.product_category_name_english, p.product_category_name) AS category,
    s.seller_state,
    s.seller_city,
    r.review_score
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
LEFT JOIN product_category_name_translation t
    ON p.product_category_name = t.product_category_name
JOIN sellers s ON oi.seller_id = s.seller_id
LEFT JOIN order_reviews r ON o.order_id = r.order_id;


-- Now use the view like a regular table!
SELECT
    category,
    COUNT(*) AS items_sold,
    ROUND(AVG(price)::NUMERIC, 2) AS avg_price,
    ROUND(AVG(review_score)::NUMERIC, 2) AS avg_review
FROM v_order_details
WHERE category IS NOT NULL
GROUP BY category
ORDER BY items_sold DESC
LIMIT 10;

-- View for seller scorecard
CREATE OR REPLACE VIEW v_seller_scorecard AS
WITH seller_metrics AS (
    SELECT
        s.seller_id,
        s.seller_state,
        s.seller_city,
        COUNT(DISTINCT oi.order_id) AS total_orders,
        SUM(oi.price) AS total_revenue,
        AVG(r.review_score) AS avg_review
    FROM sellers s
    JOIN order_items oi ON s.seller_id = oi.seller_id
    JOIN orders o ON oi.order_id = o.order_id
    LEFT JOIN order_reviews r ON o.order_id = r.order_id
    GROUP BY s.seller_id, s.seller_state, s.seller_city
)
SELECT
    *,
    ROUND(total_revenue::NUMERIC / NULLIF(total_orders, 0), 2) AS avg_order_value,
    NTILE(10) OVER (ORDER BY total_revenue) AS revenue_decile
FROM seller_metrics;

-- Use the seller scorecard
SELECT * FROM v_seller_scorecard
WHERE revenue_decile = 10  -- top 10% sellers
ORDER BY total_revenue DESC
LIMIT 10;


-- ============================================================
-- 15.2  MATERIALIZED VIEWS — Cached results
-- ============================================================
-- A materialized view stores the result physically.
-- Much faster to query but needs to be refreshed manually.

CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT
    DATE_TRUNC('month', o.order_purchase_timestamp::TIMESTAMP)::DATE AS month,
    c.customer_state,
    COUNT(DISTINCT o.order_id) AS num_orders,
    SUM(oi.price) AS revenue,
    SUM(oi.freight_value) AS freight_cost,
    AVG(r.review_score) AS avg_review
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN order_reviews r ON o.order_id = r.order_id
GROUP BY
    DATE_TRUNC('month', o.order_purchase_timestamp::TIMESTAMP)::DATE,
    c.customer_state;

-- Query the materialized view (instant!)
SELECT
    month,
    SUM(revenue) AS total_revenue,
    SUM(num_orders) AS total_orders
FROM mv_monthly_revenue
GROUP BY month
ORDER BY month;

-- Refresh when underlying data changes
REFRESH MATERIALIZED VIEW mv_monthly_revenue;

-- Refresh concurrently (no lock, requires unique index)
CREATE UNIQUE INDEX ON mv_monthly_revenue (month, customer_state);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;


-- ============================================================
-- 15.3  INDEXES — Speed up queries
-- ============================================================

-- B-tree index (default, most common)
-- Best for: exact matches, ranges, sorting
CREATE INDEX idx_orders_status ON orders(order_status);
CREATE INDEX idx_orders_purchase_date
    ON orders(order_purchase_timestamp);
CREATE INDEX idx_order_items_seller ON order_items(seller_id);
CREATE INDEX idx_customers_state ON customers(customer_state);

-- Composite index for frequent multi-column lookups
CREATE INDEX idx_orders_status_date
    ON orders(order_status, order_purchase_timestamp);

-- Partial index — only index rows matching a condition
-- Saves space when you frequently query a subset
CREATE INDEX idx_orders_delivered
    ON orders(order_delivered_customer_date)
    WHERE order_status = 'delivered';

-- Expression index — index on a computed value
CREATE INDEX idx_orders_year_month
    ON orders(DATE_TRUNC('month', order_purchase_timestamp::TIMESTAMP));

-- GIN index for array/JSONB columns (if you have them)
-- CREATE INDEX idx_jsonb_data ON some_table USING GIN (jsonb_column);


-- ============================================================
-- 15.4  EXPLAIN — Understanding query plans
-- ============================================================

-- Basic EXPLAIN shows the plan without executing
EXPLAIN
SELECT *
FROM orders
WHERE order_status = 'delivered';

-- EXPLAIN ANALYZE actually runs the query and shows timing
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE order_status = 'delivered';

-- Compare with and without index
-- This query will use our idx_orders_status index:
EXPLAIN ANALYZE
SELECT COUNT(*)
FROM orders
WHERE order_status = 'canceled';

-- More complex query plan
EXPLAIN ANALYZE
SELECT
    c.customer_state,
    COUNT(DISTINCT o.order_id) AS num_orders,
    SUM(oi.price) AS revenue
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_status = 'delivered'
  AND o.order_purchase_timestamp >= '2018-01-01'
GROUP BY c.customer_state
ORDER BY revenue DESC;


-- ============================================================
-- 15.5  QUERY OPTIMIZATION TECHNIQUES
-- ============================================================

-- ❌ BAD: Function on indexed column prevents index use
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_purchase_timestamp::TIMESTAMP) = 2018;

-- ✅ GOOD: Range comparison uses index
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_purchase_timestamp >= '2018-01-01'
  AND order_purchase_timestamp < '2019-01-01';


-- ❌ BAD: SELECT * pulls all columns
SELECT * FROM order_items WHERE seller_id = 'some_seller_id';

-- ✅ GOOD: Select only needed columns
SELECT order_id, price, freight_value
FROM order_items WHERE seller_id = 'some_seller_id';


-- ❌ BAD: NOT IN with NULLs can produce wrong results
SELECT * FROM products
WHERE product_category_name NOT IN (
    SELECT product_category_name
    FROM product_category_name_translation
);
-- If the subquery returns any NULL, ALL rows are excluded!

-- ✅ GOOD: Use NOT EXISTS instead
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM product_category_name_translation t
    WHERE t.product_category_name = p.product_category_name
);


-- ❌ BAD: DISTINCT on large result sets is expensive
SELECT DISTINCT customer_city, customer_state FROM customers;

-- ✅ GOOD: GROUP BY is often more predictable for the planner
SELECT customer_city, customer_state
FROM customers
GROUP BY customer_city, customer_state;


-- ============================================================
-- 15.6  TABLE STATISTICS
-- ============================================================

-- Check table sizes
SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS data_size,
    pg_size_pretty(pg_total_relation_size(relid) -
                   pg_relation_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;


-- Check index usage
SELECT
    schemaname,
    relname AS tablename,
    indexrelname AS indexname,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;


-- Check for missing indexes (sequential scans on large tables)
SELECT
    relname AS table_name,
    seq_scan,
    idx_scan,
    n_live_tup AS row_count,
    CASE WHEN seq_scan > 0
        THEN ROUND(100.0 * idx_scan / (seq_scan + idx_scan), 1)
        ELSE 100
    END AS index_usage_pct
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;


-- ============================================================
-- 15.7  COMMON ANTI-PATTERNS
-- ============================================================

-- Anti-Pattern 1: N+1 Queries
-- ❌ Running a query per row in application code
-- ✅ Use a JOIN to get all data in one query

-- Anti-Pattern 2: Missing WHERE on large JOIN
-- ❌ SELECT * FROM orders o JOIN order_items oi ON ...
-- ✅ Always filter early: WHERE o.order_purchase_timestamp > '2018-01-01'

-- Anti-Pattern 3: LIKE '%prefix%' — cannot use index
-- ❌ WHERE customer_city LIKE '%paulo%'
-- ✅ Use GIN/trigram index or full-text search:
-- CREATE EXTENSION pg_trgm;
-- CREATE INDEX idx_trgm_city ON customers USING GIN (customer_city gin_trgm_ops);

-- Anti-Pattern 4: Counting with subquery instead of window
-- ❌ SELECT *, (SELECT COUNT(*) FROM orders WHERE ...) FROM ...
-- ✅ SELECT *, COUNT(*) OVER (PARTITION BY ...) FROM ...


-- ============================================================
-- 15.8  CLEANUP
-- ============================================================
-- Remove objects created in this chapter (optional)

-- DROP VIEW IF EXISTS v_order_details;
-- DROP VIEW IF EXISTS v_seller_scorecard;
-- DROP MATERIALIZED VIEW IF EXISTS mv_monthly_revenue;
-- DROP INDEX IF EXISTS idx_orders_status;
-- DROP INDEX IF EXISTS idx_orders_purchase_date;
-- DROP INDEX IF EXISTS idx_order_items_seller;
-- DROP INDEX IF EXISTS idx_customers_state;
-- DROP INDEX IF EXISTS idx_orders_status_date;
-- DROP INDEX IF EXISTS idx_orders_delivered;
-- DROP INDEX IF EXISTS idx_orders_year_month;


-- ============================================================
-- EXERCISES
-- ============================================================

-- Exercise 1: Create a view called v_product_performance that
--             shows each product category with its total items
--             sold, total revenue, and average review score.

-- Exercise 2: Create a materialized view of daily order counts.
--             Then query it for the busiest day of the week.

-- Exercise 3: Run EXPLAIN ANALYZE on a query that joins 4 tables
--             and note what type of joins PostgreSQL chooses.

-- Exercise 4: Create an appropriate index to speed up queries
--             that filter on payment_type in order_payments.


-- ============================================================
-- SOLUTIONS
-- ============================================================

-- Exercise 1
CREATE OR REPLACE VIEW v_product_performance AS
SELECT
    COALESCE(t.product_category_name_english, p.product_category_name) AS category,
    COUNT(*) AS items_sold,
    ROUND(SUM(oi.price)::NUMERIC, 2) AS total_revenue,
    ROUND(AVG(oi.price)::NUMERIC, 2) AS avg_price,
    ROUND(AVG(r.review_score)::NUMERIC, 2) AS avg_review
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
LEFT JOIN product_category_name_translation t
    ON p.product_category_name = t.product_category_name
JOIN orders o ON oi.order_id = o.order_id
LEFT JOIN order_reviews r ON o.order_id = r.order_id
WHERE p.product_category_name IS NOT NULL
GROUP BY COALESCE(t.product_category_name_english, p.product_category_name);

SELECT * FROM v_product_performance ORDER BY total_revenue DESC LIMIT 10;

-- Exercise 2
CREATE MATERIALIZED VIEW mv_daily_orders AS
SELECT
    order_purchase_timestamp::DATE AS order_date,
    EXTRACT(DOW FROM order_purchase_timestamp::TIMESTAMP) AS day_of_week,
    COUNT(*) AS order_count
FROM orders
GROUP BY order_purchase_timestamp::DATE,
    EXTRACT(DOW FROM order_purchase_timestamp::TIMESTAMP);

SELECT
    CASE day_of_week
        WHEN 0 THEN 'Sunday'
        WHEN 1 THEN 'Monday'
        WHEN 2 THEN 'Tuesday'
        WHEN 3 THEN 'Wednesday'
        WHEN 4 THEN 'Thursday'
        WHEN 5 THEN 'Friday'
        WHEN 6 THEN 'Saturday'
    END AS day_name,
    AVG(order_count) AS avg_daily_orders,
    MAX(order_count) AS peak_orders
FROM mv_daily_orders
GROUP BY day_of_week
ORDER BY avg_daily_orders DESC;

-- Exercise 3
EXPLAIN ANALYZE
SELECT
    c.customer_state,
    COALESCE(t.product_category_name_english, p.product_category_name) AS category,
    SUM(oi.price) AS revenue
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
LEFT JOIN product_category_name_translation t
    ON p.product_category_name = t.product_category_name
WHERE o.order_status = 'delivered'
GROUP BY c.customer_state,
    COALESCE(t.product_category_name_english, p.product_category_name)
ORDER BY revenue DESC
LIMIT 20;

-- Exercise 4
CREATE INDEX idx_payments_type ON order_payments(payment_type);

-- Verify it's used:
EXPLAIN ANALYZE
SELECT SUM(payment_value), COUNT(*)
FROM order_payments
WHERE payment_type = 'boleto';