EXPLAIN ANALYZE shows Seq Scan on a 10M-row table — how do you diagnose and fix it?

SET max_parallel_workers_per_gather = 0;
CREATE TABLE big_orders (
  id bigint PRIMARY KEY,
  user_id int NOT NULL,
  status text NOT NULL,
  amount numeric NOT NULL,
  created_at date NOT NULL
) WITH (autovacuum_enabled = off);
INSERT INTO big_orders
SELECT g, (g % 20000) + 1,
       CASE WHEN g % 20 = 0 THEN 'cancelled' ELSE 'shipped' END,
       (g % 500) + 1, DATE '2024-01-01' + (g % 366)
FROM generate_series(1, 200000) AS g;
ANALYZE big_orders;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, amount FROM big_orders WHERE user_id = 4242;

SET max_parallel_workers_per_gather = 0;
CREATE TABLE big_orders (
  id bigint PRIMARY KEY,
  user_id int NOT NULL,
  status text NOT NULL,
  amount numeric NOT NULL,
  created_at date NOT NULL
) WITH (autovacuum_enabled = off);
INSERT INTO big_orders
SELECT g, (g % 20000) + 1,
       CASE WHEN g % 20 = 0 THEN 'cancelled' ELSE 'shipped' END,
       (g % 500) + 1, DATE '2024-01-01' + (g % 366)
FROM generate_series(1, 200000) AS g;
CREATE TABLE big_users (
  id int PRIMARY KEY,
  email text NOT NULL
) WITH (autovacuum_enabled = off);
INSERT INTO big_users
SELECT g, CASE WHEN g % 2 = 0 THEN 'User' ELSE 'user' END || g || '@Example.com'
FROM generate_series(1, 20000) AS g;
ANALYZE big_orders;
ANALYZE big_users;
-- plan(q): the plan of q as rows, so several plans fit in one result set
CREATE FUNCTION plan(q text) RETURNS SETOF text LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
  FOR r IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP
    RETURN NEXT r."QUERY PLAN";
  END LOOP;
END $$;
CREATE TABLE steps (n serial, step text, plan_line text);

INSERT INTO steps (step, plan_line)
SELECT '1 no index', plan('SELECT id FROM big_orders WHERE user_id = 4242');

CREATE INDEX big_orders_user_id_idx ON big_orders (user_id);
INSERT INTO steps (step, plan_line)
SELECT '2 index on user_id', plan('SELECT id FROM big_orders WHERE user_id = 4242');

INSERT INTO steps (step, plan_line)
SELECT '3 cast hides column', plan('SELECT id FROM big_orders WHERE user_id::text = ''4242''');

CREATE INDEX big_users_email_idx ON big_users (email);
INSERT INTO steps (step, plan_line)
SELECT '4 lower(email)', plan('SELECT id FROM big_users WHERE lower(email) = ''user4243@example.com''');

CREATE INDEX big_users_lower_email_idx ON big_users (lower(email));
ANALYZE big_users;
INSERT INTO steps (step, plan_line)
SELECT '5 expression index', plan('SELECT id FROM big_users WHERE lower(email) = ''user4243@example.com''');

SELECT step, plan_line FROM steps ORDER BY n;

SET max_parallel_workers_per_gather = 0;
-- A job queue: when statistics were last gathered, most jobs were pending
CREATE TABLE jobs (
  id int PRIMARY KEY,
  status text NOT NULL
) WITH (autovacuum_enabled = off);
INSERT INTO jobs SELECT g, CASE WHEN g % 5 = 0 THEN 'done' ELSE 'pending' END
FROM generate_series(1, 30000) AS g;
CREATE INDEX jobs_status_idx ON jobs (status);
ANALYZE jobs;
-- Workers then finished almost everything: 30 pending jobs remain
UPDATE jobs SET status = 'done' WHERE status = 'pending' AND id > 37;

CREATE FUNCTION est(q text) RETURNS TABLE (plan_node text, estimated_rows numeric, actual_rows numeric)
LANGUAGE plpgsql AS $$
DECLARE j json;
BEGIN
  EXECUTE 'EXPLAIN (ANALYZE, FORMAT JSON) ' || q INTO j;
  plan_node := j->0->'Plan'->>'Node Type';
  estimated_rows := (j->0->'Plan'->>'Plan Rows')::numeric;
  actual_rows := (j->0->'Plan'->>'Actual Rows')::numeric;
  RETURN NEXT;
END $$;

CREATE TABLE seen (n serial, stats text, plan_node text, estimated_rows numeric, actual_rows numeric);
INSERT INTO seen (stats, plan_node, estimated_rows, actual_rows)
SELECT 'stale', * FROM est('SELECT id FROM jobs WHERE status = ''pending''');
ANALYZE jobs;
INSERT INTO seen (stats, plan_node, estimated_rows, actual_rows)
SELECT 'fresh', * FROM est('SELECT id FROM jobs WHERE status = ''pending''');
SELECT stats, plan_node, estimated_rows, actual_rows FROM seen ORDER BY n;