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 psql
psql "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 plan
EXPLAIN (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 index
CREATE 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 join
SELECT 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
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
| Query | Write | Tune |
|---|---|---|
| Connect to a database | psql "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 WHERE | SELECT 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 JOIN | SELECT 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 / HAVING | SELECT 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 results | SELECT 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 WHERE | SELECT 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 lookup | CREATE 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 plan | EXPLAIN (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
| Symptom | First check | Establishes | Next check |
|---|---|---|---|
| Query is slow on a large table | EXPLAIN (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 expected | Was 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 detected | Read 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 already | SELECT 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 Scan | Run 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
- PostgreSQL: Using EXPLAINpostgresql.org
- PostgreSQL: CREATE INDEXpostgresql.org
- PostgreSQL: INSERT (ON CONFLICT)postgresql.org
- PostgreSQL: SELECTpostgresql.org
- psql referencepostgresql.org