SQL Indexes and Query Optimization

An index is a lookup structure that lets the database find rows without scanning the whole table. On a 10-row table it makes no difference. On a 10-crore-row orders table, the right index turns a query from minutes into milliseconds.

Syntax

CREATE INDEX index_name ON table_name (column);
CREATE INDEX index_name ON table_name (column1, column2);   -- composite
CREATE UNIQUE INDEX index_name ON table_name (column);
DROP INDEX index_name;
EXPLAIN SELECT ...;   -- show the query plan

Setup: a bigger table

ShopKart's tables are too small for indexes to matter, so let's generate 50,000 fake orders with generate_series:

CREATE TABLE big_orders AS
SELECT g AS order_id,
       (g % 5000) + 1 AS customer_id,
       DATE '2024-01-01' + (g % 730) AS order_date,
       (ARRAY['Delivered','Shipped','Cancelled','Returned','Pending'])[(g % 5) + 1] AS status,
       ROUND((100 + (g * 37) % 30000)::numeric, 2) AS amount
FROM generate_series(1, 50000) AS g;

ANALYZE big_orders;
SELECT COUNT(*) AS rows FROM big_orders;

Result:

(1 row)

ANALYZE collects statistics the planner uses to choose a strategy.

Reading EXPLAIN

EXPLAIN shows how the database plans to run a query, without running it:

EXPLAIN
SELECT * FROM big_orders WHERE customer_id = 42;

Result:

Seq Scan on big_orders  (cost=0.00..1009.00 rows=10 width=26)
  Filter: (customer_id = 42)

Seq Scan means it will read all 50,000 rows to find the few that match. The cost numbers are the planner's estimate: lower is better, and they're only meaningful when compared.

Examples

1. Add an index

CREATE INDEX idx_big_orders_customer ON big_orders (customer_id);

EXPLAIN
SELECT * FROM big_orders WHERE customer_id = 42;

Result:

Bitmap Heap Scan on big_orders  (cost=4.37..39.65 rows=10 width=26)
  Recheck Cond: (customer_id = 42)
  ->  Bitmap Index Scan on idx_big_orders_customer  (cost=0.00..4.37 rows=10 width=0)
        Index Cond: (customer_id = 42)

Now it's a Bitmap Index Scan (or Index Scan): it jumps straight to customer 42's rows. Compare the total cost with the seq scan above.

2. When an index isn't used

If a query matches most of the table, scanning everything is cheaper than jumping around an index:

EXPLAIN
SELECT * FROM big_orders WHERE customer_id > 10;

Result:

Seq Scan on big_orders  (cost=0.00..1009.00 rows=49908 width=26)
  Filter: (customer_id > 10)

Indexes help selective filters, the ones that return a small fraction of rows.

3. Functions on columns block indexes

CREATE INDEX idx_big_orders_date ON big_orders (order_date);

EXPLAIN
SELECT * FROM big_orders WHERE EXTRACT(YEAR FROM order_date) = 2025 AND EXTRACT(MONTH FROM order_date) = 3;

Result:

Seq Scan on big_orders  (cost=0.00..1384.00 rows=1 width=26)
  Filter: ((EXTRACT(year FROM order_date) = '2025'::numeric) AND (EXTRACT(month FROM order_date) = '3'::numeric))

Wrapping the column in a function hides it from the index. Write the condition as a range instead:

EXPLAIN
SELECT * FROM big_orders WHERE order_date >= '2025-03-01' AND order_date < '2025-04-01';

Result:

Bitmap Heap Scan on big_orders  (cost=29.99..445.74 rows=2117 width=26)
  Recheck Cond: ((order_date >= '2025-03-01'::date) AND (order_date < '2025-04-01'::date))
  ->  Bitmap Index Scan on idx_big_orders_date  (cost=0.00..29.46 rows=2117 width=0)
        Index Cond: ((order_date >= '2025-03-01'::date) AND (order_date < '2025-04-01'::date))

4. Composite index

Queries that filter on customer and date benefit from an index on both, in that order:

CREATE INDEX idx_big_orders_cust_date ON big_orders (customer_id, order_date);

EXPLAIN
SELECT order_id, amount
FROM big_orders
WHERE customer_id = 42 AND order_date >= '2025-01-01';

Result:

Bitmap Heap Scan on big_orders  (cost=4.34..22.70 rows=5 width=10)
  Recheck Cond: ((customer_id = 42) AND (order_date >= '2025-01-01'::date))
  ->  Bitmap Index Scan on idx_big_orders_cust_date  (cost=0.00..4.34 rows=5 width=0)
        Index Cond: ((customer_id = 42) AND (order_date >= '2025-01-01'::date))

The leftmost column rule: this index also helps WHERE customer_id = 42 alone, but not WHERE order_date >= ... alone.

5. See which indexes exist

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'big_orders'
ORDER BY indexname;

Result:

(3 rows)

idx_big_orders_customer is now redundant, because the composite index starts with customer_id. Dropping it saves space and speeds up writes:

DROP INDEX idx_big_orders_customer;

Result:

Query OK, 0 row(s) affected.

What to index

Query-writing tips that don't need an index

• Select only the columns you need instead of SELECT *.
• Filter early. Put conditions in WHERE, not after a join in an outer query.
• Aggregate before joining large tables (see CTEs).
• Use EXISTS instead of COUNT(*) > 0 to check for related rows.
• Avoid functions on filtered columns, as shown above.
• Use UNION ALL instead of UNION when duplicates are impossible or acceptable.

Common mistakes

• Indexing everything. Every index slows INSERT/UPDATE/DELETE and uses storage.
• Judging speed on tiny tables. Test on realistic data volumes.
• Reading EXPLAIN costs as milliseconds. They're relative units. Use EXPLAIN ANALYZE to measure actual time (it runs the query).

Practice question

Create an index that helps this query, then check the plan: SELECT * FROM big_orders WHERE status = 'Returned' AND amount > 29000;

CREATE INDEX idx_big_orders_status_amount ON big_orders (status, amount);

EXPLAIN
SELECT * FROM big_orders WHERE status = 'Returned' AND amount > 29000;

Bitmap Heap Scan on big_orders  (cost=12.10..410.82 rows=360 width=26)
  Recheck Cond: ((status = 'Returned'::text) AND (amount > '29000'::numeric))
  ->  Bitmap Index Scan on idx_big_orders_status_amount  (cost=0.00..12.01 rows=360 width=0)
        Index Cond: ((status = 'Returned'::text) AND (amount > '29000'::numeric))

Key takeaways

• Indexes let the database find rows without a full table scan.
• Use EXPLAIN to see whether a query uses a Seq Scan or an index.
• Indexes help selective filters, joins and sorts, and cost write speed.
• Keep indexed columns "bare" in WHERE: use ranges, not functions.