Indexing and Query Performance

OFFSET is scan-and-discard. The engine still produces the first 200,000 rows in ORDER BY sequence and throws them away before emitting 20, so the work grows with the offset and deep pages get steadily slower.

The index on id already holds rows in id order, so the engine walks it and stops after ten entries. Nothing indexes last_name, so every row must be read and sorted before the first ten are known.

SELECT * projects every column, so the engine reads and ships bytes the caller discards, gives up access paths a narrower projection would allow, and returns a result whose shape silently changes when the table changes.

A loop pays a network round trip, a parse and a statement execution per row, and often a commit per row. One set-based UPDATE pays all of that once and lets the engine work through the whole row set in bulk.

EXISTS and IN both express a semi-join: each outer row is kept once, as soon as one match is found. INNER JOIN emits every matching pair, so it needs a DISTINCT that sorts or hashes the whole result afterwards.

Remember the sort-key values of the last row shown and filter on them instead of counting rows: WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20. Every page then costs the same as page one.

Those whose keys form a leading prefix of the index keys, in the same sequence and with consistent directions: ORDER BY customer_id, and ORDER BY customer_id, created_at. Adding an equality filter on customer_id also frees ORDER BY created_at alone.

Turn the suffix match into a prefix match. Either store the part you actually search on in its own column and compare with =, or keep an indexed reversed copy of the value and match the reversed pattern, which anchors the search at the start of the string.

Split the OR into two separate SELECT statements, each with a single-column predicate, and combine them with UNION. Each branch can then be served by its own index, while UNION removes the rows that satisfy both conditions and would otherwise appear twice.

An index condition positions the index scan, so only matching entries are visited. A filter is evaluated on every row the scan already produced, and rows failing it are thrown away — work you paid for and discarded.

Replace the truncation with a half-open range on the bare column: created_at >= DATE '2024-03-01' AND created_at < DATE '2024-03-02'. That matches every instant in the day, leaves the column unwrapped, and avoids the endpoint bugs a BETWEEN on timestamps causes.

A view's star is normally expanded and stored when the view is created, so the view keeps its original columns and drifts from the table. INSERT ... SELECT * matches by position, so it fails on a count mismatch or lands values in the wrong columns.

Standard SQL's DELETE has no LIMIT, so bound each chunk with a predicate: delete rows whose key falls in a fixed window, or whose ids come from an ordered FETCH FIRST subquery. Repeat the statement until it reports zero affected rows.

One statement carrying 1,000 rows costs one round trip, one parse and one execution; 1,000 separate INSERTs cost all of that a thousand times. The per-row writing and index maintenance is unchanged — only the per-statement overhead disappears.

COUNT must visit every matching row to produce a total; EXISTS only has to find one and can stop there. Both answer the same yes/no question, but COUNT's work grows with the number of matches while EXISTS's does not.

SELECT DISTINCT deduplicates the entire result after the joins have already run, so the engine sorts or hashes every row produced just to throw most of them away. It hides the real cause, usually a fan-out join, instead of removing duplicates at the source.

Plain EXPLAIN does not execute the statement; it returns the execution plan the engine would use instead of any rows. EXPLAIN ANALYZE really runs the statement and reports measured timings, so on an UPDATE or DELETE it changes data.

A predicate is sargable when the column appears bare on one side of a comparison and the other side evaluates to a constant, so the engine can turn it into an index range. Wrapping the column in a function or arithmetic destroys that.

Use INSERT INTO archive ... SELECT ... FROM source, then a DELETE over the identical row set, both server-side in one transaction. Pin that set — capture the keys once — so the DELETE cannot remove rows the INSERT never copied.

Bulk-load the changes into a staging table keyed on the target's join column, then run one UPDATE that reads from it — portably, a scalar subquery in SET plus a WHERE EXISTS guard so target rows with no staging match are not overwritten with NULL.

It is outdated as a blanket rule. For an existence filter both express the same semi-join, and cost-based optimizers commonly produce the same plan for either. The honest answer is that the shape you write is a hint, and the plan decides.

Whichever side is converted decides. Converting a constant happens once and leaves the column bare, so its index still works. Converting the column happens per row and produces a derived value no index on that column stores.

Without a unique final sort column the order has ties, so the page boundary is ambiguous: a strict comparison skips every remaining row sharing the boundary value, a non-strict one repeats rows already shown. A unique tie-breaker makes the ordering total and the anchor exact.

The index stores the sort order of last_name, not of LOWER(last_name), and lowercasing can reorder values under a case-sensitive collation. Since the engine cannot assume a function preserves order, it sorts the result instead.

An index scan yields key order forwards or its exact reverse backwards, nothing else. The exact reverse of (created_at ASC, id ASC) is (created_at DESC, id DESC); a list that flips one key but not the other is neither, so a sort is added.

A huge literal IN list makes the statement text enormous, so it must be transmitted and parsed on every call, each different list length is a different statement, some engines impose a hard limit on list size, and row estimates degrade. Pass the set as a table and join instead.

Read the scan node: it names the index it went through. PostgreSQL prints 'Index Scan using idx_name on table'; MySQL's EXPLAIN reports the chosen index in its key column. A different name, or no index at all, means yours was not used.

The column sits inside an arithmetic expression, so the engine must compute price * 1.2 for every row instead of seeking a range. Move the arithmetic to the constant side — WHERE price > 120 / 1.2 — leaving the column bare.

List only columns the index already stores, counting columns used in WHERE, ORDER BY and GROUP BY too. If nothing outside the index is referenced, the engine can answer from the index instead of fetching each matching table row.