SQL Performance Lab

Tables

Select a table to see its columns, indexes and data.

ERD

Foreign key relationships between tables. Click a table to view its data above.

Examples · Performance comparison

Compare the execution time of two queries (A and B) that answer the same question.

Times are measured on the Next.js server and cover query execution + receiving the result (including the network round trip). To see only the time spent inside the database, click Explain (EXPLAIN ANALYZE). The first run can be slower because caches are cold, so run each query a few times before comparing.

1.Foreign key column with vs. without an index

order_items.order_id has an index but product_id does not. Even on the same 750k-row table, a missing index forces a full table scan (Seq Scan).

AColumn without index

SELECT *
FROM order_items
WHERE product_id = 777;

BColumn with index

SELECT *
FROM order_items
WHERE order_id = 777;

2.Wrapping an indexed column in a function

orders.ordered_at is indexed, but casting it or wrapping it in a function prevents the index from being used. Rewriting it as a range condition allows an Index Scan.

AFunction on column

SELECT count(*)
FROM orders
WHERE ordered_at::date = current_date - 30;

BRange condition

SELECT count(*)
FROM orders
WHERE ordered_at >= current_date - 30
  AND ordered_at <  current_date - 29;

3.Correlated subquery vs. JOIN + GROUP BY

Count sales for 50 products. A correlated subquery in SELECT runs once per outer row, and since order_items.product_id has no index, each run scans all 750k rows. Aggregating once and joining is far more efficient.

ACorrelated subquery

SELECT p.id, p.name,
  (SELECT count(*) FROM order_items oi WHERE oi.product_id = p.id) AS sold_count
FROM products p
WHERE p.id <= 50
ORDER BY sold_count DESC
LIMIT 20;

BJOIN + GROUP BY

SELECT p.id, p.name, count(oi.id) AS sold_count
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE p.id <= 50
GROUP BY p.id, p.name
ORDER BY sold_count DESC
LIMIT 20;

4.OFFSET pagination vs. keyset pagination

OFFSET reads every skipped row and then throws it away. Finding the next page from the last id you saw (keyset) stays fast no matter how deep the page is.

AOFFSET 280000

SELECT id, user_id, status, total_amount
FROM orders
ORDER BY id
LIMIT 20 OFFSET 280000;

BKeyset (id > 280000)

SELECT id, user_id, status, total_amount
FROM orders
WHERE id > 280000
ORDER BY id
LIMIT 20;

5.SELECT * vs. only the columns you need

Fetching columns you don't need increases the data the database reads and sends over the network. The more rows returned, the bigger the gap.

ASELECT *

SELECT *
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.ordered_at >= now() - interval '60 days';

BOnly needed columns

SELECT o.id, u.name, oi.product_id, oi.quantity
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.ordered_at >= now() - interval '60 days';

6.Checking existence: COUNT(*) vs. EXISTS

When you only need to know whether at least one row exists, COUNT(*) still counts every match, while EXISTS stops as soon as it finds the first one.

ACOUNT(*) > 0

SELECT count(*) > 0 AS has_cancelled
FROM orders
WHERE status = 'CANCELLED';

BEXISTS

SELECT EXISTS (
  SELECT 1 FROM orders WHERE status = 'CANCELLED'
) AS has_cancelled;

7.NOT IN vs. NOT EXISTS

Find VIP users who joined in the last 200 days and never wrote a review. Because of its NULL semantics, NOT IN can't be turned into an anti join and compares against the subquery result row by row, while NOT EXISTS runs as a single Hash Anti Join.

ANOT IN

SELECT count(*)
FROM users u
WHERE u.grade = 'VIP'
  AND u.created_at >= now() - interval '200 days'
  AND u.id NOT IN (SELECT r.user_id FROM reviews r);

BNOT EXISTS

SELECT count(*)
FROM users u
WHERE u.grade = 'VIP'
  AND u.created_at >= now() - interval '200 days'
  AND NOT EXISTS (SELECT 1 FROM reviews r WHERE r.user_id = u.id);

8.LIKE '%...' vs. exact match

users.email has a UNIQUE index, but a LIKE pattern with a leading % can't use it, so all 50k rows are checked.

ALIKE '%12345@...'

SELECT id, email, name
FROM users
WHERE email LIKE '%12345@example.com';

B= (uses index)

SELECT id, email, name
FROM users
WHERE email = 'user12345@example.com';