Indexes and Explain

CREATE INDEX idx_users_email ON users (email);
-- speeds WHERE email = ...

CREATE INDEX idx_orders_open ON orders (created_at)
WHERE status = 'open';
-- smaller index for open orders

CREATE INDEX idx_users_lower_email ON users (lower(email));
-- WHERE lower(email) = 'a@b.co'

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 1;
-- plan + actual rows/time

CREATE INDEX idx_orders_user ON orders (user_id) INCLUDE (total);
-- SELECT total WHERE user_id = ? may avoid heap

CREATE UNIQUE INDEX uq_users_email ON users (email);

CREATE INDEX CONCURRENTLY idx_events_ts ON events (ts);
-- cannot run in a transaction block

CREATE INDEX idx_doc ON docs USING gin (body jsonb_path_ops);
-- WHERE body @> '{"type":"x"}'

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';
-- index catalog rows

DROP INDEX CONCURRENTLY IF EXISTS idx_old;

EXPLAIN SELECT * FROM t WHERE a = 1 AND b = 2;
-- may show BitmapAnd

EXPLAIN SELECT user_id FROM orders WHERE user_id = 1;
-- Index Only Scan if possible

SET enable_seqscan = off;
EXPLAIN SELECT * FROM t WHERE id = 1;
-- forces index if available

REINDEX INDEX CONCURRENTLY idx_users_email;

EXPLAIN SELECT count(*) FROM big;
-- estimated costs only