Finding and fixing slow queries

CREATE EXTENSION pg_stat_statements;

-- Also track statements run inside functions: the app's queries run in timed().
SET pg_stat_statements.track = 'all';

-- Every request is logged with its real query and duration,
-- like an APM tool or log_min_duration_statement would.
CREATE TABLE request_log (
  id SERIAL PRIMARY KEY,
  endpoint TEXT NOT NULL,
  query TEXT NOT NULL,
  duration_ms NUMERIC NOT NULL
);

-- Runs a query, returns how long it took in milliseconds.
CREATE FUNCTION timed(query TEXT) RETURNS NUMERIC AS $$
DECLARE
  started TIMESTAMPTZ := clock_timestamp();
BEGIN
  EXECUTE query;
  RETURN round(extract(epoch FROM clock_timestamp() - started)::numeric * 1000, 2);
END;
$$ LANGUAGE plpgsql;

SELECT pg_stat_statements_reset();

-- POST /products, 200 times
INSERT INTO request_log (endpoint, query, duration_ms)
SELECT 'create', q, timed(q)
FROM generate_series(1, 200) AS n,
  format(
    'INSERT INTO products (name, category, price_cents) VALUES (%L, %L, %s)',
    'new lamp ' || n, 'garden', 1000 + n) AS q;

-- GET /products?category=kitchen&page=..., 200 times, pages 0 to 99
INSERT INTO request_log (endpoint, query, duration_ms)
SELECT 'list', q, timed(q)
FROM generate_series(1, 200) AS n,
  LATERAL (SELECT n % 100 AS page) AS p,
  format(
    'SELECT id, name, price_cents FROM products WHERE category = %L ORDER BY id LIMIT 20 OFFSET %s',
    'kitchen', page * 20) AS q;

-- DELETE /products/:id, 100 times
INSERT INTO request_log (endpoint, query, duration_ms)
SELECT 'delete', q, timed(q)
FROM generate_series(1, 100) AS n,
  format('DELETE FROM products WHERE id = %s', n * 97) AS q;

-- GET /products/search?q=..., 10 search terms, twice each
INSERT INTO request_log (endpoint, query, duration_ms)
SELECT 'search', q, timed(q)
FROM unnest(ARRAY['lamp', 'oak', 'blue sofa', 'mirror 42', 'candle',
                  'rug', 'velvet', 'desk 7', 'wooden bench', 'clock']) AS term,
  generate_series(1, 2),
  format(
    'SELECT id, name FROM products WHERE name ILIKE %L ORDER BY name LIMIT 20',
    '%' || term || '%') AS q;

-- pg_trgm splits text into 3-letter chunks: 'lamp' -> ' la', 'lam', 'amp'...
-- A GIN index on them can answer ILIKE '%...%', which a B-tree can't.
CREATE EXTENSION pg_trgm;
CREATE INDEX products_name_trgm_idx ON products USING gin (name gin_trgm_ops);

-- Before: skip 1,980 rows to show page 99.
SELECT id, name, price_cents FROM products
WHERE category = 'kitchen' ORDER BY id LIMIT 20 OFFSET 1980;

-- After: the app remembers the last id it showed and continues from there.
SELECT id, name, price_cents FROM products
WHERE category = 'kitchen' AND id > 19800 ORDER BY id LIMIT 20;