Lesson 17 of 30
Fixing Slow Queries
Most slow queries have one of a few causes: the database cannot use an index, reads more rows or columns than needed, or the application sends too many queries. Each example below shows the slow form, the plan, the fix, and the plan after the fix. Plan notation is explained in Query Plans, and index types in How Indexes Work.
The demo tables have 20,000 customers, 200,000 orders, and 400,000 order lines. Parallel plans are switched off so the output is stable.
SET max_parallel_workers_per_gather = 0;
CREATE TABLE big_customers AS
SELECT i AS id, 'customer' || i AS name,
(ARRAY['Bengaluru','Mumbai','Delhi','Pune','Chennai'])[i % 5 + 1] AS city
FROM generate_series(1, 20000) AS i;
CREATE TABLE big_orders AS
SELECT i AS id,
(i * 7) % 20000 + 1 AS customer_id,
DATE '2024-01-01' + i / 600 AS order_date,
(ARRAY['placed','shipped','delivered','cancelled'])[i % 4 + 1] AS status,
(i * 13) % 1000 AS amount
FROM generate_series(1, 200000) AS i;
CREATE TABLE big_items AS
SELECT i AS id, (i % 200000) + 1 AS order_id, (i * 3) % 500 + 1 AS product_id, i % 5 + 1 AS quantity
FROM generate_series(1, 400000) AS i;
ALTER TABLE big_customers ADD PRIMARY KEY (id);
ALTER TABLE big_orders ADD PRIMARY KEY (id);
ALTER TABLE big_items ADD PRIMARY KEY (id);
ALTER TABLE big_orders ADD FOREIGN KEY (customer_id) REFERENCES big_customers (id);
ALTER TABLE big_items ADD FOREIGN KEY (order_id) REFERENCES big_orders (id);
CREATE INDEX idx_big_orders_date ON big_orders (order_date);
VACUUM ANALYZE big_customers;
VACUUM ANALYZE big_orders;
VACUUM ANALYZE big_items;
SELECT (SELECT count(*) FROM big_customers) AS customers,
(SELECT count(*) FROM big_orders) AS orders,
(SELECT count(*) FROM big_items) AS items; customers | orders | items
-----------+--------+--------
20000 | 200000 | 400000
(1 row)Example 1: function on an indexed column
A predicate is sargable (search-argument-able) when the database can use an index for it. Wrapping the column in a function hides it from the index.
-- before: the function is applied to every row
EXPLAIN (COSTS OFF)
SELECT count(*) FROM big_orders
WHERE extract(year FROM order_date) = 2024 AND extract(month FROM order_date) = 3;
-- after: a range on the bare column
EXPLAIN (COSTS OFF)
SELECT count(*) FROM big_orders
WHERE order_date >= DATE '2024-03-01' AND order_date < DATE '2024-04-01'; QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate
-> Seq Scan on big_orders
Filter: ((EXTRACT(year FROM order_date) = '2024'::numeric) AND (EXTRACT(month FROM order_date) = '3'::numeric))
(3 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------
Aggregate
-> Index Only Scan using idx_big_orders_date on big_orders
Index Cond: ((order_date >= '2024-03-01'::date) AND (order_date < '2024-04-01'::date))
(3 rows)Move the calculation to the constant side. If the function cannot be removed, create an expression index on it (see How Indexes Work). The same rule applies in MySQL, SQL Server, and Oracle.
Example 2: leading wildcard LIKE
A B-tree index sorts values from the first character, so it can serve LIKE 'abc%' but not LIKE '%abc'.
CREATE INDEX idx_big_customers_name ON big_customers (name text_pattern_ops);
-- prefix search: index range
EXPLAIN (COSTS OFF) SELECT * FROM big_customers WHERE name LIKE 'customer1234%';
-- leading wildcard: full scan
EXPLAIN (COSTS OFF) SELECT * FROM big_customers WHERE name LIKE '%1234'; QUERY PLAN
--------------------------------------------------------------------------------------
Index Scan using idx_big_customers_name on big_customers
Index Cond: ((name ~>=~ 'customer1234'::text) AND (name ~<~ 'customer1235'::text))
Filter: (name ~~ 'customer1234%'::text)
(3 rows)
QUERY PLAN
-----------------------------------
Seq Scan on big_customers
Filter: (name ~~ '%1234'::text)
(2 rows)text_pattern_ops lets a B-tree serve LIKE prefix searches when the database collation is not C. For a wildcard at the start, use a trigram index from the pg_trgm extension.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_big_customers_name_trgm ON big_customers USING gin (name gin_trgm_ops);
EXPLAIN (COSTS OFF) SELECT * FROM big_customers WHERE name LIKE '%1234'; QUERY PLAN
--------------------------------------------------------
Bitmap Heap Scan on big_customers
Recheck Cond: (name ~~ '%1234'::text)
-> Bitmap Index Scan on idx_big_customers_name_trgm
Index Cond: (name ~~ '%1234'::text)
(4 rows)For full text search, use tsvector and a GIN index. MySQL uses FULLTEXT indexes, SQL Server uses full-text indexes, and Oracle uses Oracle Text.
Example 3: implicit cast
When the two sides of a comparison have different types, one side is cast. If the cast lands on the column, the index cannot be used.
-- before: the column is cast to numeric to compare with 500.0
EXPLAIN (COSTS OFF) SELECT * FROM big_customers WHERE id = 500.0;
-- after: literal has the column's type
EXPLAIN (COSTS OFF) SELECT * FROM big_customers WHERE id = 500;
-- before: column cast to text
EXPLAIN (COSTS OFF) SELECT * FROM big_orders WHERE id::text = '500'; QUERY PLAN
-----------------------------------
Seq Scan on big_customers
Filter: ((id)::numeric = 500.0)
(2 rows)
QUERY PLAN
------------------------------------------------------
Index Scan using big_customers_pkey on big_customers
Index Cond: (id = 500)
(2 rows)
QUERY PLAN
--------------------------------------
Seq Scan on big_orders
Filter: ((id)::text = '500'::text)
(2 rows)Match the literal or parameter type to the column type. In MySQL, comparing a VARCHAR column with a number casts the column to a number and skips the index. In Oracle, comparing a VARCHAR2 column with a number does the same with TO_NUMBER. In SQL Server, data type precedence casts the lower-precedence side, which can be the column.
Example 4: OR across columns
An OR between conditions on different columns needs an index for each branch. If one branch has no index, the whole query scans the table.
-- before: amount has no index
EXPLAIN (COSTS OFF)
SELECT * FROM big_orders WHERE customer_id = 500 OR amount = 999;
CREATE INDEX idx_big_orders_customer ON big_orders (customer_id);
CREATE INDEX idx_big_orders_amount ON big_orders (amount);
-- after: both branches have an index, results are combined with BitmapOr
EXPLAIN (COSTS OFF)
SELECT * FROM big_orders WHERE customer_id = 500 OR amount = 999; QUERY PLAN
---------------------------------------------------
Seq Scan on big_orders
Filter: ((customer_id = 500) OR (amount = 999))
(2 rows)
QUERY PLAN
----------------------------------------------------------
Bitmap Heap Scan on big_orders
Recheck Cond: ((customer_id = 500) OR (amount = 999))
-> BitmapOr
-> Bitmap Index Scan on idx_big_orders_customer
Index Cond: (customer_id = 500)
-> Bitmap Index Scan on idx_big_orders_amount
Index Cond: (amount = 999)
(7 rows)Rewriting the query as UNION ALL of two selects (with the second excluding rows of the first) gives the same effect when the database cannot combine indexes. MySQL calls the combining step index merge.
Example 5: SELECT *
SELECT * reads and sends every column. It also blocks index-only scans, because the index must hold every column requested.
CREATE INDEX idx_big_orders_customer_amount ON big_orders (customer_id) INCLUDE (amount);
DROP INDEX idx_big_orders_customer;
-- before: all columns, so table pages are read
EXPLAIN (COSTS OFF) SELECT * FROM big_orders WHERE customer_id = 500;
-- after: only the columns needed, served from the index
EXPLAIN (COSTS OFF) SELECT customer_id, amount FROM big_orders WHERE customer_id = 500; QUERY PLAN
-----------------------------------------------------------
Bitmap Heap Scan on big_orders
Recheck Cond: (customer_id = 500)
-> Bitmap Index Scan on idx_big_orders_customer_amount
Index Cond: (customer_id = 500)
(4 rows)
QUERY PLAN
--------------------------------------------------------------------
Index Only Scan using idx_big_orders_customer_amount on big_orders
Index Cond: (customer_id = 500)
(2 rows)List the columns the application uses. It also avoids transferring columns that are added to the table later, and it reduces network traffic and memory use.
Example 6: the N+1 query problem
The N+1 pattern loads a list with one query and then runs one more query per row, often hidden inside an ORM's lazy loading. For 100 customers it sends 101 queries. Each is fast, but the round trips add up.
The pattern, on the shop sample data:
-- 1 query for the list
SELECT id, name FROM customers ORDER BY id LIMIT 3;
-- N queries, one per customer, sent by the application in a loop
SELECT count(*) AS orders FROM orders WHERE customer_id = 1;
SELECT count(*) AS orders FROM orders WHERE customer_id = 2;
SELECT count(*) AS orders FROM orders WHERE customer_id = 3; id | name
----+-------
1 | Aarav
2 | Isha
3 | Karan
(3 rows)
orders
--------
3
(1 row)
orders
--------
2
(1 row)
orders
--------
2
(1 row)The fix is one query that joins and groups:
SELECT c.id, c.name, count(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id
LIMIT 3; id | name | orders
----+-------+--------
1 | Aarav | 3
2 | Isha | 2
3 | Karan | 2
(3 rows)When the two queries must stay separate (for example, loading child rows for an ORM), fetch all children with one query: WHERE customer_id = ANY (ARRAY[1, 2, 3]). ORMs call this eager loading (select_related, prefetch_related, includes, JOIN FETCH).
Example 7: OFFSET pagination vs keyset pagination
OFFSET n reads and discards the first n rows. The cost grows with the page number. Keyset pagination (also called seek pagination) continues from the last key seen.
-- before: page 10000 with 10 rows per page
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, amount FROM big_orders ORDER BY id LIMIT 10 OFFSET 100000;
-- after: continue after the last id of the previous page
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, amount FROM big_orders WHERE id > 100000 ORDER BY id LIMIT 10; QUERY PLAN
-----------------------------------------------------------------------------------
Limit (actual rows=10 loops=1)
-> Index Scan using big_orders_pkey on big_orders (actual rows=100010 loops=1)
(2 rows)
QUERY PLAN
-------------------------------------------------------------------------------
Limit (actual rows=10 loops=1)
-> Index Scan using big_orders_pkey on big_orders (actual rows=10 loops=1)
Index Cond: (id > 100000)
(3 rows)The first plan produces 100,010 rows to return 10. The second produces 10. Keyset pagination needs a unique, indexed sort key. For a non-unique key such as order_date, sort by (order_date, id) and compare with a row value: WHERE (order_date, id) > (:last_date, :last_id). Keyset pagination cannot jump to an arbitrary page number.
Example 8: COUNT(*) on big tables
PostgreSQL has to visit rows to count them, because each transaction sees its own set of visible rows. An exact count of a large table is always a scan, sequential or index-only.
EXPLAIN (COSTS OFF) SELECT count(*) FROM big_orders;
-- estimate maintained by VACUUM/ANALYZE
SELECT reltuples::bigint AS estimated_rows FROM pg_class WHERE relname = 'big_orders'; QUERY PLAN
------------------------------
Aggregate
-> Seq Scan on big_orders
(2 rows)
estimated_rows
----------------
200000
(1 row)| Need | Approach |
|---|---|
| Exact total, occasionally | count(*), and accept the scan |
| Approximate total for a UI | pg_class.reltuples, or the planner's estimate from EXPLAIN |
| Exact and frequent | Keep a counter table updated by triggers or by the application |
| Count with a filter | An index on the filter columns, so the count scans only matching entries |
count(*) and count(1) behave the same. count(column) skips NULL values. MySQL MyISAM stored an exact row count, but InnoDB, like PostgreSQL, has to scan.
Example 9: missing foreign key index
A foreign key constraint checks the parent on insert, but it does not create an index on the referencing column in PostgreSQL, SQL Server, or Oracle. MySQL InnoDB creates one automatically. Without the index, looking up children by parent scans the child table, and so does every check when a parent row is deleted or its key is updated.
-- before: no index on big_items.order_id
EXPLAIN (COSTS OFF)
SELECT o.id, i.product_id FROM big_orders o JOIN big_items i ON i.order_id = o.id WHERE o.id = 1234;
-- foreign keys of the demo tables whose columns are not the start of any index
SELECT c.conrelid::regclass AS child_table, c.conname AS foreign_key
FROM pg_constraint c
WHERE c.contype = 'f' AND c.conrelid::regclass::text LIKE 'big%'
AND NOT EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.conrelid
AND (i.indkey::int2[])[0:cardinality(c.conkey) - 1] = c.conkey
)
ORDER BY 1, 2;
CREATE INDEX idx_big_items_order ON big_items (order_id);
-- after
EXPLAIN (COSTS OFF)
SELECT o.id, i.product_id FROM big_orders o JOIN big_items i ON i.order_id = o.id WHERE o.id = 1234; QUERY PLAN
-------------------------------------------------------------
Nested Loop
-> Index Only Scan using big_orders_pkey on big_orders o
Index Cond: (id = 1234)
-> Seq Scan on big_items i
Filter: (order_id = 1234)
(5 rows)
child_table | foreign_key
-------------+-------------------------
big_items | big_items_order_id_fkey
(1 row)
QUERY PLAN
-------------------------------------------------------------
Nested Loop
-> Index Only Scan using big_orders_pkey on big_orders o
Index Cond: (id = 1234)
-> Index Scan using idx_big_items_order on big_items i
Index Cond: (order_id = 1234)
(5 rows)The finder query lists big_items_order_id_fkey before the index exists. The demo table big_orders has an index that starts with customer_id, so its foreign key is not listed.
Example 10: DISTINCT and GROUP BY misuse
DISTINCT is sometimes added to hide duplicates produced by a join. It forces a sort or hash over the whole join result. When the goal is "rows that have a match", use EXISTS.
-- before: join multiplies rows, DISTINCT removes them again
EXPLAIN (COSTS OFF)
SELECT DISTINCT c.id, c.name
FROM big_customers c
JOIN big_orders o ON o.customer_id = c.id
WHERE o.amount > 990;
-- after: semi join
EXPLAIN (COSTS OFF)
SELECT c.id, c.name
FROM big_customers c
WHERE EXISTS (SELECT 1 FROM big_orders o WHERE o.customer_id = c.id AND o.amount > 990); QUERY PLAN
--------------------------------------------------------------
HashAggregate
Group Key: c.id, c.name
-> Hash Join
Hash Cond: (o.customer_id = c.id)
-> Bitmap Heap Scan on big_orders o
Recheck Cond: (amount > 990)
-> Bitmap Index Scan on idx_big_orders_amount
Index Cond: (amount > 990)
-> Hash
-> Seq Scan on big_customers c
(10 rows)
QUERY PLAN
--------------------------------------------------------------------
Hash Join
Hash Cond: (c.id = o.customer_id)
-> Seq Scan on big_customers c
-> Hash
-> HashAggregate
Group Key: o.customer_id
-> Bitmap Heap Scan on big_orders o
Recheck Cond: (amount > 990)
-> Bitmap Index Scan on idx_big_orders_amount
Index Cond: (amount > 990)
(10 rows)The second plan removes duplicates from o.customer_id before joining, so the join input is smaller than in the first plan, where HashAggregate runs on the full join result. Also avoid GROUP BY on columns that are not needed for the result, and do not add DISTINCT to queries that cannot produce duplicates, such as a select of a primary key.
Example 11: IN vs EXISTS vs JOIN
For "customers who have orders", the three forms return the same rows. PostgreSQL turns IN (subquery) and EXISTS into a semi join, so they perform alike. A JOIN can produce duplicates and needs DISTINCT.
EXPLAIN (COSTS OFF)
SELECT id FROM big_customers WHERE id IN (SELECT customer_id FROM big_orders WHERE amount > 990);
EXPLAIN (COSTS OFF)
SELECT id FROM big_customers c WHERE EXISTS (SELECT 1 FROM big_orders o WHERE o.customer_id = c.id AND o.amount > 990); QUERY PLAN
--------------------------------------------------------------------
Hash Join
Hash Cond: (big_customers.id = big_orders.customer_id)
-> Seq Scan on big_customers
-> Hash
-> HashAggregate
Group Key: big_orders.customer_id
-> Bitmap Heap Scan on big_orders
Recheck Cond: (amount > 990)
-> Bitmap Index Scan on idx_big_orders_amount
Index Cond: (amount > 990)
(10 rows)
QUERY PLAN
--------------------------------------------------------------------
Hash Join
Hash Cond: (c.id = o.customer_id)
-> Seq Scan on big_customers c
-> Hash
-> HashAggregate
Group Key: o.customer_id
-> Bitmap Heap Scan on big_orders o
Recheck Cond: (amount > 990)
-> Bitmap Index Scan on idx_big_orders_amount
Index Cond: (amount > 990)
(10 rows)The difference appears with negation. NOT IN returns no rows if the subquery yields a NULL, and it usually cannot become an anti join. NOT EXISTS handles NULL as expected and becomes an anti join.
EXPLAIN (COSTS OFF)
SELECT id FROM big_customers WHERE id NOT IN (SELECT customer_id FROM big_orders);
EXPLAIN (COSTS OFF)
SELECT id FROM big_customers c WHERE NOT EXISTS (SELECT 1 FROM big_orders o WHERE o.customer_id = c.id); QUERY PLAN
------------------------------------
Seq Scan on big_customers
Filter: (NOT (hashed SubPlan 1))
SubPlan 1
-> Seq Scan on big_orders
(4 rows)
QUERY PLAN
-----------------------------------------
Hash Right Anti Join
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on big_orders o
-> Hash
-> Seq Scan on big_customers c
(5 rows)Use NOT EXISTS instead of NOT IN on a subquery. Older MySQL versions (before 5.6) ran IN subqueries as dependent subqueries per row; current versions optimize them.
Example 12: batching inserts
Each statement in autocommit mode is its own transaction. Every commit waits for the write-ahead log to reach disk, and every statement is a network round trip. Batching removes both costs. xmin is the ID of the transaction that inserted a row, so counting distinct values shows how many transactions were used.
CREATE TABLE load_demo (id int, note text);
-- one row per statement, autocommit: 3 transactions
INSERT INTO load_demo VALUES (1, 'row');
INSERT INTO load_demo VALUES (2, 'row');
INSERT INTO load_demo VALUES (3, 'row');
-- many rows in one statement: 1 transaction
INSERT INTO load_demo VALUES (4, 'row'), (5, 'row'), (6, 'row');
-- rows from a query: 1 transaction
INSERT INTO load_demo SELECT i, 'row' FROM generate_series(7, 1000) AS i;
SELECT CASE WHEN id <= 3 THEN 'single-row statements'
WHEN id <= 6 THEN 'multi-row VALUES'
ELSE 'INSERT ... SELECT' END AS method,
count(*) AS rows_inserted,
count(DISTINCT xmin::text) AS transactions
FROM load_demo
GROUP BY 1
ORDER BY 1; method | rows_inserted | transactions
-----------------------+---------------+--------------
INSERT ... SELECT | 994 | 1
multi-row VALUES | 3 | 1
single-row statements | 3 | 3
(3 rows)For files, use COPY (LOAD DATA in MySQL, BULK INSERT in SQL Server, SQL*Loader in Oracle), which is the fastest path. For a very large load: drop or defer secondary indexes, load, then create the indexes, and run ANALYZE afterwards. In application code, use the driver's batch API and commit every few thousand rows.
Example 13: avoiding sorts
A Sort node reads all input before returning the first row. An index whose leading columns match the WHERE equality conditions, followed by the ORDER BY columns, returns rows already sorted.
-- before: read the table, then sort (incremental sort is disabled to keep the plan stable)
SET enable_incremental_sort = off;
EXPLAIN (COSTS OFF)
SELECT id, order_date FROM big_orders WHERE status = 'shipped' ORDER BY order_date, id;
RESET enable_incremental_sort;
CREATE INDEX idx_big_orders_status_date_id ON big_orders (status, order_date, id);
-- after: no Sort node
EXPLAIN (COSTS OFF)
SELECT id, order_date FROM big_orders WHERE status = 'shipped' ORDER BY order_date, id; QUERY PLAN
--------------------------------------------
Sort
Sort Key: order_date, id
-> Seq Scan on big_orders
Filter: (status = 'shipped'::text)
(4 rows)
QUERY PLAN
-------------------------------------------------------------------
Index Only Scan using idx_big_orders_status_date_id on big_orders
Index Cond: (status = 'shipped'::text)
(2 rows)Other ways to reduce sorting: drop ORDER BY in subqueries where the order is not used, use UNION ALL instead of UNION when duplicates are impossible (UNION sorts or hashes to remove them), and index ORDER BY columns in the same direction as the query (or the exact opposite direction for all columns).
Example 14: LIMIT with ORDER BY on an index
With ORDER BY ... LIMIT n and a matching index, the database reads only n index entries. Without one, it must read and sort every row to find the top n.
-- before: no index on quantity
EXPLAIN (COSTS OFF)
SELECT * FROM big_items ORDER BY quantity DESC, id LIMIT 5;
CREATE INDEX idx_big_items_quantity ON big_items (quantity DESC, id);
-- after: 5 entries are read from the index
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM big_items ORDER BY quantity DESC, id LIMIT 5; QUERY PLAN
-------------------------------------
Limit
-> Sort
Sort Key: quantity DESC, id
-> Seq Scan on big_items
(4 rows)
QUERY PLAN
------------------------------------------------------------------------------------
Limit (actual rows=5 loops=1)
-> Index Scan using idx_big_items_quantity on big_items (actual rows=5 loops=1)
(2 rows)A filter in the query changes this. If the index does not include the filter column, the scan may read many entries before it finds n matches. A composite index that starts with the filter column and ends with the sort column avoids that.
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM big_orders WHERE amount = 999 ORDER BY order_date DESC LIMIT 5;
CREATE INDEX idx_big_orders_amount_date ON big_orders (amount, order_date DESC);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM big_orders WHERE amount = 999 ORDER BY order_date DESC LIMIT 5; QUERY PLAN
-------------------------------------------------------------------------------------------
Limit (actual rows=5 loops=1)
-> Index Scan Backward using idx_big_orders_date on big_orders (actual rows=5 loops=1)
Filter: (amount = 999)
Rows Removed by Filter: 4060
(4 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------
Limit (actual rows=5 loops=1)
-> Index Scan using idx_big_orders_amount_date on big_orders (actual rows=5 loops=1)
Index Cond: (amount = 999)
(3 rows)The first plan reads 4,065 entries of idx_big_orders_date to find 5 rows with amount = 999. The second reads 5 entries.
Example 15: tuning checklist
| Step | Action |
|---|---|
| 1 | Find the slow query: pg_stat_statements, slow query log (log_min_duration_statement), MySQL slow log |
| 2 | Run EXPLAIN (ANALYZE, BUFFERS) and find the node with the most time or buffers |
| 3 | Compare estimated and actual rows. Large gaps: run ANALYZE, check pg_stats |
| 4 | Check for Seq Scan on a large table with a selective condition |
| 5 | Check that predicates are sargable: no function or cast on the column, no leading wildcard |
| 6 | Add or adjust an index: equality columns first, then range or sort columns; consider INCLUDE |
| 7 | Select only needed columns and rows; paginate with keyset |
| 8 | Look for repeated queries from the application (N+1) and batch them |
| 9 | Check joins: foreign key indexes, join type, join order |
| 10 | Re-run EXPLAIN ANALYZE after each change, and change one thing at a time |
| 11 | Check write cost and unused indexes before keeping a new index |
Interview questions
- What does sargable mean? A predicate the engine can answer with an index seek, such as
col = 5orcol BETWEEN 1 AND 9.f(col) = 5is not sargable. - Why is
LIKE '%abc'slow? A B-tree is ordered from the first character, so the index cannot narrow the search. A trigram or full-text index can. - What is the N+1 problem and how is it fixed? One query for a list plus one query per row. Fix it with a join, a single
INorANYquery, or eager loading. - Why is OFFSET pagination slow on deep pages? The database reads and discards all skipped rows. Keyset pagination seeks to the last key.
- How would you speed up
COUNT(*)on a table with 500 million rows? Use an estimate, a maintained counter, or a materialized summary. An exact count needs a scan. - Does a foreign key create an index? Not in PostgreSQL, SQL Server, or Oracle. InnoDB does create one.
INvsEXISTS? They plan the same for positive checks.NOT EXISTSis safer thanNOT INwhenNULLcan appear.- An index exists but the query still scans. Why? Non-sargable predicate, type mismatch, low selectivity, stale statistics, or a small table.
- How do you find the slowest queries in production?
pg_stat_statementssorted by total time, and the slow query log. - Is adding an index always safe? It slows writes, uses space, and can change other plans. Test it, and create it with
CREATE INDEX CONCURRENTLYon a busy table.
Summary
- Keep predicates sargable: bare column on one side, no function, cast, or leading wildcard.
- Read only the rows and columns needed: list columns, use
LIMITwith keyset pagination, and avoidDISTINCTas a join fix. - Replace N+1 loops with a join or one batched query, and batch inserts into few transactions or
COPY. - Index foreign keys, and match composite indexes to filters and
ORDER BY. - Use
EXPLAIN (ANALYZE, BUFFERS)before and after every change.