Measure, then fix
Slow queries are usually a missing or wrong index. EXPLAIN ANALYZE shows exactly what PostgreSQL did and how long each step took. Specialised indexes then target the real problem.
Smarter indexes
CREATE TABLE tickets (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL, status text NOT NULL, created_at timestamptz NOT NULL); INSERT INTO tickets (email, status, created_at) SELECT 'User' || (i % 3000) || '@Cafe.np', CASE WHEN i % 50 = 0 THEN 'open' ELSE 'closed' END, now() - (i || ' minutes')::interval FROM generate_series(1, 40000) AS i; ANALYZE tickets; CREATE INDEX tickets_open_idx ON tickets (created_at) WHERE status = 'open'; CREATE INDEX tickets_email_lower_idx ON tickets (lower(email)); EXPLAIN ANALYZE SELECT * FROM tickets WHERE status = 'open' ORDER BY created_at DESC LIMIT 10; EXPLAIN ANALYZE SELECT * FROM tickets WHERE lower(email) = '[email protected]';
- Covering indexes: CREATE INDEX … (customer_id) INCLUDE (total) can answer a query from the index alone.
- pg_stat_statements finds your slowest queries in production.
- Autovacuum cleans up dead rows; very busy tables may need tuning.
- N+1 queries from an ORM (one query per row) are a classic app-side cause — batch them.