-- Most queries filter WHERE deleted_at IS NULL
-- Create partial index excluding soft-deleted rows

CREATE INDEX idx_active_products ON products(name, price)
WHERE is_active = TRUE; -- same as NOT deleted

CREATE INDEX idx_active_orders ON orders(cust_id, order_date)
WHERE status NOT IN ('cancelled', 'deleted');

-- These queries will use partial index:
SELECT * FROM products WHERE name = 'iPhone' AND is_active = TRUE;
SELECT * FROM orders WHERE cust_id = 42 AND status NOT IN ('cancelled', 'deleted');

-- Check index size savings
SELECT
  pg_size_pretty(pg_relation_size('idx_active_products')) AS partial_idx_size;