-- always run ANALYZE before investigating slow queries
ANALYZE orders;

-- basic EXPLAIN (shows plan, no execution)
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- EXPLAIN ANALYZE (runs the query, shows actual vs estimated rows)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '7 days';

-- basic single-column index
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- index on a frequently filtered status column
CREATE INDEX idx_orders_status ON orders (status);

-- partial index — only index the rows you actually query
-- much smaller, faster to maintain
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

-- unique index (enforces uniqueness + faster lookups)
CREATE UNIQUE INDEX idx_users_email ON users (lower(email));

-- expression index — index a computed value
CREATE INDEX idx_users_email_lower ON users (lower(email));
-- now this query uses the index:
-- SELECT * FROM users WHERE lower(email) = 'user@example.com';

-- this index supports:
-- WHERE status = 'pending'                          ✓
-- WHERE status = 'pending' AND created_at > ...    ✓
-- WHERE status = 'pending' AND created_at > ... AND customer_id = 42  ✓
-- WHERE created_at > ...                            ✗ (can't skip status)
CREATE INDEX idx_orders_status_created_customer
  ON orders (status, created_at, customer_id);

-- covering index — includes all columns the query needs, zero heap fetches
CREATE INDEX idx_orders_covering ON orders (customer_id, status)
INCLUDE (total, created_at);

-- query that uses the covering index with no heap access:
SELECT total, created_at
FROM orders
WHERE customer_id = 42 AND status = 'shipped';

EXPLAIN (ANALYZE, BUFFERS)
SELECT p.name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.created_at BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY p.id, p.name
ORDER BY revenue DESC
LIMIT 20;

/*
 Sample output sections to read:

 -> Hash Join  (cost=1234.56..5678.90 rows=10000 width=48)
              (actual time=45.123..98.456 rows=8734 loops=1)
   Hash Cond: (oi.product_id = p.id)
   Buffers: shared hit=1023 read=234

   -> Bitmap Heap Scan on order_items oi
         (actual time=12.3..34.5 rows=92345 loops=1)
      Recheck Cond: (created_at BETWEEN ...)
      ->  Bitmap Index Scan on idx_oi_created_at
            (actual time=8.9..8.9 rows=92345 loops=1)

 Planning Time: 2.1 ms
 Execution Time: 102.3 ms
*/

-- BAD: 1 query for orders + N queries for customers
SELECT * FROM orders WHERE status = 'pending';
-- then for each order:
SELECT * FROM customers WHERE id = ?;

-- GOOD: single query with JOIN
SELECT
  o.id,
  o.total,
  o.created_at,
  c.id         AS customer_id,
  c.email      AS customer_email,
  c.first_name,
  c.last_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC;

-- fetching nested aggregates without N+1
-- BAD: separate query per order for item count
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days';
-- then: SELECT COUNT(*) FROM order_items WHERE order_id = ?

-- GOOD: aggregate in JOIN
SELECT
  o.id,
  o.total,
  COUNT(oi.id)              AS item_count,
  SUM(oi.quantity)          AS total_units
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.id
WHERE o.created_at > NOW() - INTERVAL '30 days'
GROUP BY o.id, o.total
ORDER BY o.created_at DESC;

-- subquery in FROM (derived table) — planner can inline and optimize
SELECT p.name, revenue_data.total_revenue
FROM products p
JOIN (
  SELECT product_id, SUM(quantity * unit_price) AS total_revenue
  FROM order_items
  WHERE created_at > NOW() - INTERVAL '90 days'
  GROUP BY product_id
) revenue_data ON revenue_data.product_id = p.id
ORDER BY revenue_data.total_revenue DESC;

-- CTE — cleaner syntax, same performance in Postgres 12+
-- (CTEs are now inlined by default unless MATERIALIZED is specified)
WITH revenue_by_product AS (
  SELECT
    product_id,
    SUM(quantity * unit_price) AS total_revenue,
    COUNT(DISTINCT order_id)   AS order_count
  FROM order_items
  WHERE created_at > NOW() - INTERVAL '90 days'
  GROUP BY product_id
),
top_products AS (
  SELECT product_id, total_revenue, order_count
  FROM revenue_by_product
  WHERE total_revenue > 1000
)
SELECT p.name, tp.total_revenue, tp.order_count
FROM top_products tp
JOIN products p ON p.id = tp.product_id
ORDER BY tp.total_revenue DESC
LIMIT 50;

-- force materialization when the CTE result is expensive + reused
WITH MATERIALIZED expensive_aggregation AS (
  SELECT customer_id, AVG(total) AS avg_order_value
  FROM orders
  WHERE created_at > NOW() - INTERVAL '1 year'
  GROUP BY customer_id
)
SELECT c.email, ea.avg_order_value
FROM expensive_aggregation ea
JOIN customers c ON c.id = ea.customer_id
WHERE ea.avg_order_value > 200;

-- running total
SELECT
  created_at::date      AS date,
  SUM(total)            AS daily_revenue,
  SUM(SUM(total)) OVER (
    ORDER BY created_at::date
    ROWS UNBOUNDED PRECEDING
  )                     AS cumulative_revenue
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY created_at::date
ORDER BY date;

-- rank products by revenue within each category
SELECT
  p.name,
  p.category,
  SUM(oi.quantity * oi.unit_price)          AS revenue,
  RANK() OVER (
    PARTITION BY p.category
    ORDER BY SUM(oi.quantity * oi.unit_price) DESC
  )                                          AS rank_in_category
FROM products p
JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name, p.category
ORDER BY p.category, rank_in_category;

-- compare to previous period (LAG)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month)  AS prev_month_revenue,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month))
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100,
    2
  )                                     AS pct_change
FROM (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total)                      AS revenue
  FROM orders
  GROUP BY 1
) monthly
ORDER BY month;

-- deduplicate: keep latest record per customer
SELECT DISTINCT ON (customer_id)
  customer_id, id AS order_id, created_at, total
FROM orders
ORDER BY customer_id, created_at DESC;

-- find unused indexes (waste space, slow writes)
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- find missing indexes on foreign keys
SELECT
  tc.table_name,
  kcu.column_name,
  ccu.table_name AS references_table
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
  ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND NOT EXISTS (
    SELECT 1 FROM pg_indexes
    WHERE tablename = tc.table_name
      AND indexdef LIKE '%' || kcu.column_name || '%'
  );

-- rebuild bloated indexes concurrently (no table lock)
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;