Non-Clustered Index Pointer Reference Lookup Charts

-- Non-clustered indexes are the default CREATE INDEX behavior
CREATE INDEX idx_users_email ON users(email);      -- non-clustered
CREATE INDEX idx_orders_status ON orders(status);  -- non-clustered

-- The index contains: (indexed_value → row_pointer)
-- For users: ('priya@ex.com' → page 42, slot 3)

-- Covering index: include extra columns to avoid table lookup
-- (index covers the entire query without accessing the table)
CREATE INDEX idx_orders_user_total
  ON orders(user_id) INCLUDE (total_amount, status, created_at);
-- Query can be satisfied entirely from the index:
SELECT total_amount, status FROM orders WHERE user_id = 42;

-- Composite non-clustered index
CREATE INDEX idx_products_cat_price ON products(category_id, price);

-- View non-clustered indexes
SELECT indexname, indexdef FROM pg_indexes
WHERE tablename = 'orders' AND indexdef NOT LIKE '%PRIMARY KEY%';

-- API endpoint: GET /users/:id/orders?status=completed&limit=20
-- Query pattern:
SELECT id, order_number, total_amount, created_at, status
FROM orders
WHERE user_id = :user_id AND status = 'completed'
ORDER BY created_at DESC
LIMIT 20;

-- Without index: Sequential scan on orders table → slow for millions of rows
-- EXPLAIN: Seq Scan on orders (cost=0.00..45000 rows=100000)

-- Non-clustered covering index:
CREATE INDEX idx_orders_user_status_cover
  ON orders(user_id, status, created_at DESC)
  INCLUDE (id, order_number, total_amount);
-- Index contains ALL columns needed — no table heap lookup!

-- After index: Index Only Scan → very fast
-- EXPLAIN: Index Only Scan using idx_orders_user_status_cover
--          (cost=0.42..0.55 rows=5)

-- Also helps:
-- WHERE user_id = 42                         (leftmost prefix)
-- WHERE user_id = 42 AND status = 'pending'  (first two columns)
-- ORDER BY created_at DESC                   (third column)