17-advanced-indexing.sql

-- ============================================================
-- SQL Masterclass
-- Chapter 17: Advanced Indexing
-- ============================================================
-- Level: Expert (Database Admin)
-- Dependencies: PostgreSQL (Requires ecommerce DB from setup)
-- 
-- Concepts Covered:
-- 1. B-Tree Indexes (Review)
-- 2. Composite Indexes (Multi-column)
-- 3. Partial Indexes (WHERE clause matching)
-- 4. Expression Indexes (Formatting based)
-- 5. Using EXPLAIN ANALYZE to prove index usage
-- ============================================================

-- ============================================================
-- 1. Setup: Creating a massive table for testing
-- ============================================================
-- Indexes only matter on large tables. Let's create a bloated 
-- copy of our orders to see the impact.
DROP TABLE IF EXISTS big_orders;
CREATE TABLE big_orders AS
SELECT * FROM orders;

-- Duplicate data to make it larger (~500k rows)
INSERT INTO big_orders SELECT * FROM orders;
INSERT INTO big_orders SELECT * FROM orders;
INSERT INTO big_orders SELECT * FROM orders;
INSERT INTO big_orders SELECT * FROM orders;

-- ============================================================
-- 2. The Sequential Scan (No Index)
-- ============================================================
-- Querying a specific customer_id. 
-- Notice the "Seq Scan" in the execution plan. It had to read
-- through almost 500k rows to find these matches.
EXPLAIN ANALYZE
SELECT * FROM big_orders 
WHERE customer_id = '9ef432eb6251297304e7';

-- Let's add a standard B-Tree index:
CREATE INDEX idx_big_orders_customer ON big_orders(customer_id);

-- Run it again. You should now see "Index Scan" or "Bitmap Heap Scan",
-- and the Execution Time should drop from ~100ms to <1ms!
EXPLAIN ANALYZE
SELECT * FROM big_orders 
WHERE customer_id = '9ef432eb6251297304e7';


-- ============================================================
-- 3. Composite Indexes (Multi-Column)
-- ============================================================
-- If you frequently query by combination, a composite index helps.
-- Order matters: an index on (A, B) helps queries filtering by A, 
-- or by (A AND B), but NOT queries filtering only by B.

EXPLAIN ANALYZE
SELECT * FROM big_orders
WHERE order_status = 'shipped' 
  AND CAST(order_purchase_timestamp AS DATE) = '2018-01-10';

-- Create the composite index. We put `order_purchase_timestamp` first
-- because it is highly distinct (cardinality), filtering out more rows faster.
CREATE INDEX idx_big_orders_date_status 
ON big_orders(order_purchase_timestamp, order_status);

-- Run the EXPLAIN again. It should use `idx_big_orders_date_status`.
EXPLAIN ANALYZE
SELECT * FROM big_orders
WHERE order_status = 'shipped' 
  AND order_purchase_timestamp >= '2018-01-10 00:00:00'
  AND order_purchase_timestamp < '2018-01-11 00:00:00';


-- ============================================================
-- 4. Partial Indexes
-- ============================================================
-- If you only ever query "canceled" orders, creating an index on
-- the entire table wastes disk space. You can index just the subset!

EXPLAIN ANALYZE
SELECT order_id FROM big_orders
WHERE order_status = 'canceled';

-- Create a Partial Index:
CREATE INDEX idx_big_orders_canceled 
ON big_orders(order_status) 
WHERE order_status = 'canceled';

-- The engine now has a tiny, instant lookup table just for cancellations.
EXPLAIN ANALYZE
SELECT order_id FROM big_orders
WHERE order_status = 'canceled';


-- ============================================================
-- 5. Expression Indexes (Function-Based)
-- ============================================================
-- If users frequently search for cities in lowercase or uppercase, 
-- `WHERE LOWER(customer_city) = 'sao paulo'` will IGNORE a standard index
-- because the index stores the raw casing, not the LOWER() casing.

-- Test table:
DROP TABLE IF EXISTS big_customers;
CREATE TABLE big_customers AS SELECT * FROM customers;
INSERT INTO big_customers SELECT * FROM customers;
INSERT INTO big_customers SELECT * FROM customers;

-- Add standard index
CREATE INDEX idx_bc_city ON big_customers(customer_city);

-- Sequential Scan! The standard index is bypassed because of the LOWER() function.
EXPLAIN ANALYZE
SELECT customer_id FROM big_customers 
WHERE LOWER(customer_city) = 'sao paulo';

-- The Fix: Index the result of the expression!
CREATE INDEX idx_bc_city_lower ON big_customers(LOWER(customer_city));

-- Index Scan restored!
EXPLAIN ANALYZE
SELECT customer_id FROM big_customers 
WHERE LOWER(customer_city) = 'sao paulo';


-- ============================================================
-- 6. Exercises
-- ============================================================
-- Exercise 1: You are building an internal dashboard showing delayed shipments.
-- The query always filters for orders where `order_delivered_customer_date` 
-- is GREATER THAN the `order_estimated_delivery_date`.
-- Create the most efficient Partial Index for this specific dashboard.

-- Write your CREATE INDEX statement below:
-- CREATE INDEX ...


-- ============================================================
-- Solutions
-- ============================================================
/*
-- Solution 1:
CREATE INDEX idx_delayed_shipments 
ON big_orders(order_id)
WHERE order_delivered_customer_date > order_estimated_delivery_date;
*/