Query Planner & Advanced SQL

-- View column statistics
SELECT attname, null_frac, n_distinct, most_common_vals
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

-- Manually update statistics
ANALYZE orders;

-- Increase statistics target for skewed columns (default: 100)
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

EXPLAIN ANALYZE
SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending' AND o.created_at > '2024-01-01';

-- Output (simplified):
-- Nested Loop (cost=0.87..156.23 rows=42 width=52) (actual time=0.05..1.2 rows=38 loops=1)
--   -> Index Scan using idx_orders_status_date on orders o
--        (cost=0.43..89.12 rows=42 width=28) (actual rows=38)
--        Filter: status = 'pending' AND created_at > '2024-01-01'
--   -> Index Scan using users_pkey on users u
--        (cost=0.43..1.59 rows=1 width=24) (actual rows=1 loops=38)
--        Index Cond: (id = o.user_id)
-- Planning Time: 0.3 ms
-- Execution Time: 1.5 ms

-- PostgreSQL can combine multiple indexes with BitmapAnd/BitmapOr
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42 AND status = 'pending';

-- If separate indexes exist on user_id and status:
-- BitmapAnd
--   -> Bitmap Index Scan on idx_orders_user_id (rows matching user_id = 42)
--   -> Bitmap Index Scan on idx_orders_status (rows matching status = 'pending')
-- -> Bitmap Heap Scan (fetch rows in both bitmaps)

-- A composite index (user_id, status) would be more efficient here

-- Nested Loop: small orders result joined to users via PK index
EXPLAIN ANALYZE
SELECT o.*, u.email FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.id = 42;
-- Nested Loop: 1 order row → index lookup on users_pkey

-- Hash Join: joining two large tables without useful index
EXPLAIN ANALYZE
SELECT o.*, p.name FROM orders o
JOIN products p ON p.id = o.product_id
WHERE o.created_at > '2024-01-01';
-- Hash Join: build hash on products, probe with filtered orders

-- Merge Join: both sides have sorted index
EXPLAIN ANALYZE
SELECT a.*, b.* FROM table_a a
JOIN table_b b ON a.sort_key = b.sort_key
ORDER BY a.sort_key;
-- Merge Join: both already sorted by sort_key via index

-- Simple CTE (PG 12+ can inline this)
WITH active_users AS (
  SELECT id, email FROM users WHERE status = 'active'
)
SELECT au.email, count(o.id) AS order_count
FROM active_users au
JOIN orders o ON o.user_id = au.id
GROUP BY au.email;

-- Force materialization (optimization fence)
WITH active_users AS MATERIALIZED (
  SELECT id, email FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE email LIKE '%@company.com';

-- Force inlining (let planner push predicates into CTE)
WITH active_users AS NOT MATERIALIZED (
  SELECT id, email FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE email LIKE '%@company.com';

-- Organizational hierarchy: find all reports under a manager
WITH RECURSIVE org_tree AS (
  -- Base case: the manager
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE id = 1  -- CEO

  UNION ALL

  -- Recursive case: find direct reports of current level
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
  WHERE ot.depth < 10  -- prevent infinite recursion
)
SELECT * FROM org_tree ORDER BY depth, name;

-- Category tree with path
WITH RECURSIVE cat_tree AS (
  SELECT id, name, parent_id, name::text AS path
  FROM categories WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.path || ' > ' || c.name
  FROM categories c
  JOIN cat_tree ct ON c.parent_id = ct.id
)
SELECT * FROM cat_tree;

-- ROW_NUMBER: assign sequential number within partition
SELECT
  user_id,
  order_id,
  total,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders;
-- rn = 1 is the most recent order per user

-- Running total
SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS running_total
FROM daily_revenue;

-- LAG/LEAD: compare with previous/next row
SELECT
  date,
  revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS day_over_day_change
FROM daily_revenue;

-- RANK vs DENSE_RANK
SELECT
  name, score,
  RANK() OVER (ORDER BY score DESC) AS rank,        -- 1, 2, 2, 4 (gaps)
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense  -- 1, 2, 2, 3 (no gaps)
FROM leaderboard;

-- Top 3 products per category
SELECT * FROM (
  SELECT
    category_id, product_name, revenue,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC) AS rn
  FROM products
) ranked
WHERE rn <= 3;

-- Top 3 most recent orders per user (efficient with LATERAL)
SELECT u.id, u.email, recent_orders.*
FROM users u
CROSS JOIN LATERAL (
  SELECT o.id AS order_id, o.total, o.created_at
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY o.created_at DESC
  LIMIT 3
) recent_orders
WHERE u.status = 'active';

-- This is much more efficient than window functions for top-N per group
-- because it can use an index on orders(user_id, created_at DESC)

-- ❌ OFFSET pagination — gets slower with depth
SELECT * FROM orders ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 10000;  -- must scan 10,000 rows to discard them

-- ✅ Keyset pagination — constant performance
-- First page:
SELECT * FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page (use last row's values as cursor):
SELECT * FROM orders
WHERE (created_at, id) < ('2024-03-15 10:30:00+00', 98765)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Requires: composite index on (created_at DESC, id DESC)
-- Performance: O(log N) regardless of page depth
-- Trade-off: no "jump to page 50" — only next/previous