SQL Partial Index for a Hot Query

-- Schema assumptions (PostgreSQL 15+):
--   orders(id bigint PRIMARY KEY, customer_id bigint NOT NULL,
--          status text NOT NULL CHECK (status IN ('pending','paid','cancelled')),
--          created_at timestamptz NOT NULL DEFAULT now(), total_cents integer NOT NULL)
--   subscriptions(id bigint PRIMARY KEY, user_id bigint NOT NULL, plan text NOT NULL,
--                 canceled_at timestamptz)
--   users(id bigint PRIMARY KEY, email text NOT NULL)
-- TEMP copies of those tables keep this example self-contained.

CREATE TEMP TABLE users (
    id    bigint PRIMARY KEY,
    email text NOT NULL
);

CREATE TEMP TABLE orders (
    id          bigint PRIMARY KEY,
    customer_id bigint NOT NULL,
    status      text NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
    created_at  timestamptz NOT NULL DEFAULT now(),
    total_cents integer NOT NULL
);

CREATE TEMP TABLE subscriptions (
    id          bigint PRIMARY KEY,
    user_id     bigint NOT NULL,
    plan        text NOT NULL,
    canceled_at timestamptz
);

INSERT INTO users (id, email) VALUES
    (1, '[email protected]'),
    (2, '[email protected]');

INSERT INTO orders (id, customer_id, status, created_at, total_cents) VALUES
    (1, 1, 'pending', now() - interval '2 hours', 4900),
    (2, 1, 'paid', now() - interval '3 days', 2500),
    (3, 2, 'pending', now() - interval '30 days', 8900);

INSERT INTO subscriptions (id, user_id, plan, canceled_at) VALUES
    (1, 1, 'pro', NULL),
    (2, 1, 'pro', now() - interval '90 days'),
    (3, 2, 'team', NULL);

-- Partial index: only pending rows are stored, so it stays small even when
-- the orders table is dominated by paid and cancelled history.
CREATE INDEX idx_orders_pending_created_at
    ON orders (created_at DESC)
    WHERE status = 'pending';

ANALYZE orders;

-- The predicate matches the index condition exactly, so this is indexable.
EXPLAIN (COSTS OFF)
SELECT id, customer_id, total_cents
FROM orders
WHERE status = 'pending'
  AND created_at > now() - interval '1 day'
ORDER BY created_at DESC
LIMIT 50;

-- Omitting `status = 'pending'` makes the predicate non-matching; on a
-- production-sized table this query should not use the partial index.
EXPLAIN (COSTS OFF)
SELECT id, total_cents
FROM orders
WHERE created_at > now() - interval '1 day'
ORDER BY created_at DESC;

-- At most one active subscription per user; cancelled history is exempt.
CREATE UNIQUE INDEX uq_subscriptions_active_per_user
    ON subscriptions (user_id)
    WHERE canceled_at IS NULL;

-- Case-insensitive uniqueness without changing stored email casing.
CREATE UNIQUE INDEX uq_users_email_lower
    ON users (lower(email));