Indexes in SQL: B-Tree, Composite and Partial Indexes with Examples

CREATE INDEX is easy to memorise. Choosing an index is a workload decision. A poor column, order or predicate adds storage and write work without helping the slow query. Single-column B-tree, composite, partial, expression and covering indexes each change the access path, while EXPLAIN reveals the planner's choice. An index changes the access path, not the rows a correct query returns. The CS Fundamentals for Exams & Placements category places indexing beside the DBMS concepts it relies on.

Indexes in SQL: what the database stores and pays for

An index is a separately maintained set of search keys and row locators. B-trees suit equality, range and order. The table remains the full-row source unless an index-only plan answers.

A selective read may inspect a small key range. Relevant INSERT, UPDATE and DELETE operations maintain indexes and consume storage. Indexes do not always improve queries.

In PostgreSQL, a PRIMARY KEY creates a unique B-tree index. For height, fan-out and leaf mechanics, read B+ Trees and Database Indexing: Worked Insert Example. Hash, full-text and spatial indexes serve specialised workloads.

Create a single-column SQL index on one exact dataset

Create this PostgreSQL table and reuse it throughout. Use SQL WHERE Clause and Filtering: Syntax, Operators, and Worked Examples to practise predicate semantics on the original eight-order fixture. The changed identifiers, timestamps and emails below support a different task: choosing index access paths.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_email VARCHAR(80) NOT NULL,
  city VARCHAR(30) NOT NULL,
  ordered_at TIMESTAMP NOT NULL,
  amount INTEGER NOT NULL,
  status VARCHAR(12) NOT NULL
);

INSERT INTO orders VALUES
  (1001, 'asha@example.in',   'Delhi',  '2026-07-02 09:15:00', 1200, 'PAID'),
  (1002, 'bharat@example.in', 'Pune',   '2026-07-02 10:10:00',  800, 'PENDING'),
  (1003, 'asha@example.in',   'Delhi',  '2026-07-03 14:20:00',  450, 'PAID'),
  (1004, 'charu@example.in',  'Delhi',  '2026-07-05 11:00:00', 2200, 'PAID'),
  (1005, 'deepak@example.in', 'Jaipur', '2026-07-07 16:40:00',  900, 'CANCELLED'),
  (1006, 'esha@example.in',   'Pune',   '2026-07-08 08:30:00', 1600, 'PAID'),
  (1007, 'farhan@example.in', 'Delhi',  '2026-07-09 18:05:00',  700, 'PENDING'),
  (1008, 'gita@example.in',   'Pune',   '2026-07-10 12:00:00', 1100, 'PAID');

Run this query before and after creating the index:

SELECT order_id, ordered_at, amount
FROM orders
WHERE customer_email = 'asha@example.in'
ORDER BY order_id;

CREATE INDEX idx_orders_customer_email ON orders(customer_email);

Both runs return 1001 | 2026-07-02 09:15:00 | 1200 and 1003 | 2026-07-03 14:20:00 | 450. PostgreSQL maintains it without application inserts. Use table-key naming. After the plan exercise, run DROP INDEX idx_orders_customer_email;.

Composite indexes in SQL: column order and range predicates

Create CREATE INDEX idx_orders_city_ordered_at ON orders(city, ordered_at);. Keys sort by city, then time. The leftmost prefix supports city = ... alone and a time range after city is fixed. ordered_at alone lacks that advantage. A planner may still use the index, but date-only workloads usually need a date-led index.

SELECT order_id, ordered_at, amount
FROM orders
WHERE city = 'Delhi'
  AND ordered_at >= TIMESTAMP '2026-07-04 00:00:00'
  AND ordered_at <  TIMESTAMP '2026-07-10 00:00:00'
ORDER BY ordered_at;

The rows are 1004 | 2026-07-05 11:00:00 | 2200, then 1007 | 2026-07-09 18:05:00 | 700. Total: 2200 + 700 = 2900. Orders 1001 and 1003 precede the lower bound; 10 July midnight is excluded. The index fits filtering and ordering, but the planner may scan a tiny table. ORDER BY and LIMIT in SQL: Tutorial with Exact Outputs teaches stable sorting, tie-breakers and top-N output; here ordering matters only because the composite index can supply it after fixing city.

Read an SQL EXPLAIN plan without fabricating performance numbers

Run EXPLAIN (ANALYZE, BUFFERS) on the Asha lookup. Inspect the scan node, Index Cond or filter, estimated and actual rows, loops and buffers. A sequential scan can be correct for eight rows. Never copy another machine's costs or timings.

For a deterministic PostgreSQL selectivity lab, create 10,000 rows, then refresh statistics:

CREATE TABLE order_lookup AS
SELECT n AS order_id,
       'user' || LPAD(n::text, 5, '0') || '@example.in' AS customer_email,
       500 + (n % 5) * 100 AS amount
FROM generate_series(1, 10000) AS g(n);

CREATE INDEX idx_order_lookup_email ON order_lookup(customer_email);
ANALYZE order_lookup;

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, amount FROM order_lookup
WHERE customer_email = 'user07500@example.in';

The result is 7500 | 500: 7500 % 5 = 0, then 500 + 0 * 100 = 500. Compare plans with and without the index; never claim a universal speed-up. generate_series and these EXPLAIN options are PostgreSQL-specific.

Partial, expression, covering and unique indexes in SQL

Four designs make different promises:

CREATE INDEX idx_orders_unsettled_date ON orders(ordered_at)
WHERE status IN ('PENDING', 'CANCELLED');

SELECT order_id, status FROM orders
WHERE status IN ('PENDING', 'CANCELLED') ORDER BY ordered_at;

CREATE INDEX idx_orders_lower_email ON orders(LOWER(customer_email));
CREATE INDEX idx_orders_email_cover ON orders(customer_email)
INCLUDE (order_id, amount);

The partial index contains 1002 PENDING, 1005 CANCELLED, 1007 PENDING, in date order. It is smaller than an all-status index, but a query must imply its predicate. WHERE LOWER(customer_email) = 'asha@example.in' matches idx_orders_lower_email and returns 1001 and 1003. A raw-email index differs.

SELECT order_id, amount FROM orders WHERE customer_email = 'asha@example.in'; is an index-only candidate, but visibility and cost decide the plan. Keep both only with evidence. INCLUDE, partial and expression syntax are PostgreSQL-specific. Other engines vary in syntax and storage.

CREATE UNIQUE INDEX uq_orders_customer_email ON orders(customer_email); fails because Asha occurs twice. Email could be unique in customers, not orders; constraints follow the entity. The primary-key index already makes order_id unique, making a duplicate redundant.

SQL index selectivity, write cost and cases to leave unindexed

Here, order_id = 1004 matches 1/8 = 12.5%; Asha matches 2/8 = 25%; Delhi matches 4/8 = 50%; and PAID matches 5/8 = 62.5%. These are teaching ratios, not thresholds. A status index may cost more than scanning five of eight rows. One email in the larger lab is 1/10000 = 0.01%. Measure the real plan, distribution and workload.

Before adding an index, record query text and parameters, frequency, matched rows, ordering, returned columns, current plan, write rate and overlapping indexes. Inserts and key-changing updates maintain relevant indexes. “Index every WHERE column” is not a sound rule.

SQL index mistakes and exact practice checks

Practice from the fixed rows:

1. Pune from 1 July inclusive to 9 July exclusive with (city, ordered_at) returns 1002 and 1006. Total: 800 + 1600 = 2400.

2. LOWER(customer_email) = 'asha@example.in' directly matches idx_orders_lower_email, returning 1001 and 1003.

3. PAID IDs are 1001, 1003, 1004, 1006 and 1008. That is 5/8 = 62.5%, so a sequential scan may win.

Typical checks ask you to choose key order, test a leftmost prefix, distinguish index types or interpret a plan node. These are stable concepts, not exam-weightage claims.

Indexes in SQL: the short version and next step

Use a six-step decision sequence:

1. Start from the slow query.

2. Identify equality, range and ordering columns.

3. Choose the narrowest useful key order.

4. Check selectivity.

5. Compare EXPLAIN (ANALYZE, BUFFERS) before and after.

6. Keep the index only when read benefit justifies storage and write maintenance.

Indexes optimise access paths, not incorrect SQL or weak schemas. For a structured placement route, follow CS Fundamentals for Placements by Sanchit Sir.

Recreate the table, predict Pune's two rows, verify them, then inspect the local plan.