When a query drags on for two or three seconds—or duplicates pop up in a report—most teams reach for one of two habits: buy a beefier server or pull the rows into backend code and loop over them. Both fixes only last until the table grows again. Here are three telling cases from SQL Portal where the fix happens right inside the query using indexes, cardinality, and window functions.
Speeding Up Product Search from 2.5s to 80ms Without New Hardware
The authors start with a sharp rule: most "database performance problems" are poorly written SQL disguised as database issues. Take a products catalog taking 2.5 seconds on a powerful server:
-- Original query (~2.5s): SELECT *, LOWER(), and OFFSET
SELECT *
FROM products p
WHERE p.category_id = $1
AND p.active = TRUE
AND p.price_cents BETWEEN $2 AND $3
AND LOWER(p.name) LIKE LOWER('%' || $4 || '%')
ORDER BY p.created_at DESC
OFFSET $5 LIMIT $6;
Four traps collide here. SELECT * drags in heavy description columns. LOWER(p.name) LIKE '%...%' with a leading wildcard disables a normal B-tree index. Sorting by created_at happens after filtering, and OFFSET forces the server to read and discard all previous rows on every page.
You can fix all of this without upgrading hardware. First, build a covering index following the "equality → range → sort" rule with INCLUDE (name). Since PostgreSQL 11, this enables an Index-Only Scan without touching the table heap (as long as VACUUM keeps the visibility map updated). Next, enable pg_trgm and add a trigram GIN index on name for ILIKE (set a UX minimum of 3 characters so 1–2 char queries don't stall). Finally, replace OFFSET with keyset (seek) pagination using the last row's coordinates:
-- Covering index (CONCURRENTLY avoids blocking writes)
CREATE INDEX CONCURRENTLY idx_products_feed
ON products (category_id, active, price_cents,
created_at DESC, product_id DESC)
INCLUDE (name);
-- Trigram GIN index for substring search
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_products_name_trgm
ON products USING gin (name gin_trgm_ops);
-- Fast query with keyset pagination (~80ms)
SELECT p.product_id, p.name, p.price_cents, p.created_at
FROM products p
WHERE p.category_id = $1
AND p.active = TRUE
AND p.price_cents BETWEEN $2 AND $3
AND p.name ILIKE '%' || $4 || '%'
AND (p.created_at, p.product_id) < ($6, $7)
ORDER BY p.created_at DESC, p.product_id DESC
LIMIT $5;
Response time drops from 2.5 seconds to 80ms—over 30x faster. You can verify the plan with EXPLAIN (ANALYZE, BUFFERS). Fast hardware only hides bad architecture until the next traffic spike.
Cardinality: Why Indexing User Gender Wastes Resources
Interviewers love a classic trick question: "What's wrong with this script?" The author admits he looked for a syntax typo at first, only to realize the issue was in the data itself.
-- Clean syntax, but one index does nothing useful
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Name VARCHAR(100),
Department VARCHAR(50),
Gender CHAR(1)
);
CREATE INDEX idx_name ON Employees(Name);
CREATE INDEX idx_gender ON Employees(Gender);
It comes down to cardinality—the share of unique values in a column. On a million-row Employees table, the gap is obvious:
| Column | Unique values | Cardinality | Index usefulness |
|---|---|---|---|
ID | 1,000,000 | High | Maximum |
Name | Hundreds of thousands | High | Useful |
Department | 10–20 | Medium | Limited |
Gender | 2–3 | Very low | Near zero |
Each Gender value matches a third or half of the table, so the index doesn't narrow the search. The query planner ignores it and runs a Full Table Scan anyway. Meanwhile, every INSERT, UPDATE, and DELETE still pays the disk and CPU cost to maintain idx_gender.
Low-cardinality columns belong in a composite index paired with a selective column, or a partial index targeting a rare slice of rows (say, the 5% of orders that are still open):
-- Works for Department and Department + ID,
-- but NOT for ID alone (B-tree leftmost prefix rule)
CREATE INDEX idx_dept_emp ON Employees (Department, ID);
-- Partial index in PostgreSQL for a rare status
CREATE INDEX idx_orders_open ON orders (created_at)
WHERE active = TRUE;
Before running CREATE INDEX, check the value distribution. A weak index only slows down writes.
Catching Repeat Purchases Within 5 Minutes Using the LAG Window Function
Here's a classic payment-system task: given a CustomerPurchases table (PurchaseId, CustomerId, StoreId, ProductId, PurchaseTimestamp), find cases where a customer bought the same product at the same store twice within five minutes—a sign of duplicates or double charges.
You don't need a heavy self-JOIN. A CTE (WITH ... AS) paired with the LAG window function solves it in a single pass by peeking at the previous purchase time inside each group (PARTITION BY):
-- PostgreSQL: subtract timestamps and compare to INTERVAL
WITH PurchasesWithLag AS (
SELECT
PurchaseId, CustomerId, StoreId, ProductId,
PurchaseTimestamp,
LAG(PurchaseTimestamp) OVER (
PARTITION BY CustomerId, StoreId, ProductId
ORDER BY PurchaseTimestamp
) AS PreviousPurchaseTimestamp
FROM CustomerPurchases
)
SELECT
PurchaseId, CustomerId, StoreId, ProductId,
PreviousPurchaseTimestamp, PurchaseTimestamp
FROM PurchasesWithLag
WHERE PreviousPurchaseTimestamp IS NOT NULL
AND PurchaseTimestamp - PreviousPurchaseTimestamp
<= INTERVAL '5 minutes'
ORDER BY CustomerId, PurchaseTimestamp;
Watch out for two details. For the first purchase in a group, there is no prior row, so LAG returns NULL—without IS NOT NULL, your filter breaks. Also mind your SQL dialect: the original SQL Server example used DATEDIFF (DATEDIFF(MINUTE, prev, curr) <= 5), which counts minute-boundary crossings (12:00:59 and 12:01:01 return 1). For an exact 300-second window in T-SQL, use DATEDIFF(SECOND, ...) <= 300; in PostgreSQL, use INTERVAL '5 minutes'.

