← All cheatsheets

Databases

SQL & PostgreSQL — query, index, and explain

Practical PostgreSQL via psql: connect and inspect, write SELECT/JOIN/GROUP BY queries, CTEs and UPSERT, wrap writes in transactions, add B-tree indexes, and read EXPLAIN ANALYZE to turn a Seq Scan into an Index Scan.

Docs-checkedsqlpostgresqlpsqlindexexplain

Quick reference — reach for these first

Connect with psqlpsql "postgresql://USER:PASSWORD@HOST:5432/DBNAME"Open an interactive session. Use \l to list DBs, \dt to list tables, \d TABLE to describe one, \q to quit.
Explain a query's real planEXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;Actually runs the query and shows node types (Seq Scan vs Index Scan), estimated vs actual rows, and timing.
Create a B-tree indexCREATE INDEX idx_orders_customer_id ON orders (customer_id);Speeds equality/range lookups and sorts on the column. Add CONCURRENTLY in production to avoid locking writes.
Inner + left joinSELECT c.name, o.total FROM customers c JOIN orders o ON o.customer_id = c.id;JOIN (INNER) keeps only matched rows; LEFT JOIN keeps all left rows, NULL-filling unmatched right columns.
Insert or update (UPSERT)INSERT INTO users (email, name) VALUES ('a@x.io','Al') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;Atomic insert-or-update keyed on a unique/PK column. Use DO NOTHING to skip conflicts silently.

How the planner chooses a scan

PostgreSQL scan selectionA query enters the planner, which checks table statistics and index availability, then chooses a Seq Scan when no selective index exists or an Index Scan when one does; EXPLAIN ANALYZE shows the chosen path.SELECTvia psqlPlannerreads pg stats +index catalogIndex Scanselective B-tree index existsreads only matching rowsSeq Scanno usable index / low selectivityreads every rowhas indexno indexEXPLAIN ANALYZE shows which path actually ran

Text equivalent: A query flows from psql into the PostgreSQL planner. The planner checks table statistics and whether a usable index exists on the filtered column. If no selective index exists it chooses a Seq Scan, reading every row. If a selective B-tree index exists it chooses an Index Scan, reading only matching rows. EXPLAIN ANALYZE reveals which path ran.

Common tasks

Query · Write · Tune

QueryWriteTune
Connect to a databasepsql "postgresql://USER:PASSWORD@HOST:5432/DBNAME"Prompt shows DBNAME=#; \conninfo confirms host, port, and user.
List tables and inspect a schema\dt then \d orders\dt lists relations; \d orders prints columns, types, indexes, and constraints.
Filter rows with WHERESELECT id, total FROM orders WHERE status = 'paid' AND total > 100;Only paid orders above 100 appear; row count matches your expectation.
Join two tables (inner)SELECT c.name, o.total FROM orders o JOIN customers c ON c.id = o.customer_id;Each row pairs an order with its customer; unmatched orders are excluded.
Keep unmatched rows with LEFT JOINSELECT c.name, o.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;Customers with no orders still appear, with o.id as NULL.
Aggregate with GROUP BY / HAVINGSELECT customer_id, COUNT(*) n, SUM(total) s FROM orders GROUP BY customer_id HAVING COUNT(*) >= 3;One row per customer with 3+ orders; HAVING filters groups after aggregation.
Sort and cap resultsSELECT id, total FROM orders ORDER BY total DESC LIMIT 10;Exactly the 10 highest-total orders, largest first.
Build a CTE (WITH)WITH big AS (SELECT customer_id FROM orders GROUP BY customer_id HAVING SUM(total) > 1000) SELECT c.name FROM customers c JOIN big ON big.customer_id = c.id;Only customers whose orders sum above 1000 are returned; the CTE reads as a named step.
Use a subquery in WHERESELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE total > 500);Customers linked to at least one order over 500 are listed.
Insert-or-update (UPSERT)INSERT INTO users (email, name) VALUES ('a@x.io','Al') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;A new row appears, or the existing row's name updates; no duplicate email.
Wrap a write in a transaction [DESTRUCTIVE]BEGIN; UPDATE orders SET status = 'void' WHERE id = 99; -- verify, then COMMIT; (or ROLLBACK;)SELECT inside the transaction shows the change; ROLLBACK restores the prior value; COMMIT persists it.
Add an index to speed a lookupCREATE INDEX idx_orders_customer_id ON orders (customer_id);\d orders lists the index; the target query's plan switches to Index Scan.
Read the real planEXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;Top node names Seq Scan or Index Scan; actual time and rows are shown per node.
Create an index in production without blocking [PROD-CHANGING]CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);Build completes without an ACCESS EXCLUSIVE lock; writes continue during the build.

Turn a Seq Scan into an Index Scan

performance

1
A dashboard query over a 2M-row orders table feels slow. Measure the real plan, not a guess.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;
2
Read the output: a sequential scan reads every row, and actual time is high.
3
customer_id is selective (few matching rows) but has no index. Add a B-tree index on it.
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
4
Re-run the exact same EXPLAIN to confirm the planner now uses the index.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;
5
Verify: the node is now Index Scan, estimated cost dropped sharply, and execution time fell from ~243 ms to sub-millisecond.
6
Evaluate the outcome objectively: same result rows, node changed Seq Scan -> Index Scan, cost and time both fell by orders of magnitude. If time had NOT dropped, you'd suspect a low-selectivity filter or stale stats (run ANALYZE orders).

Troubleshooting — symptom → first check → next check

SymptomFirst checkEstablishesNext check
Query is slow on a large tableEXPLAIN (ANALYZE, BUFFERS) SELECT ... — look at the top node and actual time.A Seq Scan over many rows on a filtered column means no usable index (or the planner rejected it).CREATE INDEX on the WHERE/JOIN column, then re-run EXPLAIN to confirm an Index Scan and lower cost.
UPDATE/DELETE changed far more rows than expectedWas there a WHERE clause? Check the row count in the command tag (e.g. UPDATE 50000).A missing WHERE applies the change to the whole table — the classic full-table write.Always BEGIN first, run a SELECT with the same WHERE to preview, then COMMIT; ROLLBACK if the count is wrong.
ERROR: deadlock detectedRead the DETAIL line naming the two processes and the locks each waits on.Two transactions acquired row/table locks in opposite order and each blocks the other.Lock rows in a consistent order across transactions, keep transactions short, and retry the aborted one on 40P01.
FATAL: sorry, too many clients alreadySELECT count(*) FROM pg_stat_activity; compare to SHOW max_connections;Open connections hit the server limit — often leaked/idle connections or no pooler.Close idle sessions, add a pooler (PgBouncer / Supabase pooler), and cap app pool size below max_connections.
ERROR: permission denied for table X\dp X to see table privileges, and SELECT current_user; to confirm who you are.The connected role lacks the needed GRANT on the table (or its schema).As an owner/superuser, GRANT SELECT, INSERT, UPDATE ON X TO ROLE; grant USAGE on the schema if needed.
Index exists but the planner still does a Seq ScanRun EXPLAIN; check whether the table is small or the filter matches most rows.On small tables or low-selectivity filters a Seq Scan is genuinely cheaper, so the planner skips the index.Confirm with a selective value; if stats are stale, run ANALYZE TABLE so estimates reflect current data.

References