PostgreSQL Indexes Explained Through Queries That Actually Need Them

An orders page takes three seconds to load. The table has a million rows, so someone suggests adding an index on every column in the filter.

That sounds reasonable until you look at the actual query. It filters by company, sorts by creation time, and returns only 50 rows. Three separate indexes may still leave PostgreSQL doing work that one carefully chosen index could avoid.

The useful question is not “Which columns should have indexes?” It is “Which work should this query stop doing?”

We will answer that question through a small SaaS order system. Each example starts with a query, chooses an index for a specific reason, and explains what to inspect afterward. The goal is to make index decisions you can defend, rather than collect rules that sound convincing until a query breaks them.

Start with a small order system

Our application stores customers and orders for 100 companies. Each company normally sees only its own records.

Here is the schema for the exercise:

CREATE TABLE customers (
  id bigint PRIMARY KEY,
  company_id integer NOT NULL,
  email text NOT NULL
);

CREATE TABLE orders (
  id bigint PRIMARY KEY,
  company_id integer NOT NULL,
  customer_id bigint NOT NULL REFERENCES customers(id),
  status text NOT NULL CHECK (status IN ('paid', 'pending')),
  total_cents integer NOT NULL,
  created_at timestamptz NOT NULL,
  metadata jsonb NOT NULL DEFAULT '{}'
);

This is a teaching schema. The foreign key checks that a customer exists; it does not prove that the customer belongs to the order's company. A production design needs that tenant relationship enforced too, for example through an appropriate composite foreign key. We keep the exercise focused on access paths.

Populate it with deterministic data so that you can repeat the comparison:

INSERT INTO customers (id, company_id, email)
SELECT
  g,
  ((g - 1) % 100) + 1,
  'User' || g || '@example.test'
FROM generate_series(1, 100000) AS g;

INSERT INTO orders (
  id, company_id, customer_id, status,
  total_cents, created_at, metadata
)
SELECT
  g,
  ((g - 1) % 100) + 1,
  ((g - 1) % 100000) + 1,
  CASE WHEN g % 97 = 0 THEN 'pending' ELSE 'paid' END,
  1000 + (g % 50000),
  timestamptz '2026-01-01 00:00:00+00'
    + g * interval '1 second',
  CASE WHEN g % 997 = 0
    THEN '{"priority": "rush"}'::jsonb
    ELSE '{"priority": "normal"}'::jsonb
  END
FROM generate_series(1, 1000000) AS g;

ANALYZE customers;
ANALYZE orders;

There are 100,000 customers, one million orders, and a small proportion of pending and rush orders. Real data will be less even. That difference matters: an index that helps a company with 100 orders may behave differently for one with five million.

The examples target PostgreSQL 18 and are checked against its documentation. The complete SQL walkthrough contains the setup and comparisons in execution order. Run it in a new disposable database, not against application tables. The plan descriptions below explain what to look for; they are not measured timings or a promise of a particular planner choice.

Measure the query before changing it

Start with the customer's order history:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_cents
FROM orders
WHERE company_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50;

EXPLAIN shows the plan. ANALYZE actually runs the statement and adds execution information. BUFFERS shows buffer activity, including blocks already in PostgreSQL's cache and blocks read into it. A buffer read does not necessarily mean a physical disk read because the operating system may also cache data.

With no suitable secondary index, PostgreSQL may scan many rows, discard those belonging to other companies, and sort the remaining rows before returning 50. Parallel execution can change the shape of this plan. Read the work performed, not just the node names.

Look at the estimated and actual row counts, rows removed by filters, sort operations, buffer activity, and total execution time. If you compare runs, keep the query and data unchanged. Run several times and note whether the cache is warm. A second run can be faster even when you have changed nothing.

The PostgreSQL EXPLAIN guide explains these fields and the overhead of collecting execution statistics. Be especially careful with writes: EXPLAIN ANALYZE UPDATE ... performs the update. Use the read-only queries here while learning the tool.

Find one company's latest orders

A B-tree is a good fit for this combination of equality, ordering, and a small limit:

CREATE INDEX orders_company_recent_idx
ON orders (company_id, created_at DESC, id DESC);

Think of the index as entries grouped by company, then ordered by creation time and ID inside each group. PostgreSQL can locate company 42 and walk its most recent entries instead of finding every matching row and sorting them all.

The ID gives the ordering a stable tie-breaker. Two orders can have the same timestamp in a real system. Without a second ordering column, their relative order is unspecified.

Run the same query again:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_cents
FROM orders
WHERE company_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50;

Look for an index scan with company_id = 42 as an index condition and no separate sort. The index does not contain total_cents, so retrieving the selected rows still requires table access. That is perfectly reasonable for 50 rows.

What about separate indexes on company_id and created_at? PostgreSQL can sometimes combine indexes using a bitmap scan, but that does not preserve the ordering needed here. You can still end up with a sort. A composite index describes this query's path more directly.

Column order should follow the work. Leading equality conditions often let the index narrow the search before a range or ordering requirement. “Put the most selective column first” is an incomplete rule when the query also needs ordered results and a limit.

There is another important qualification. A composite index is not automatically unusable when the query omits its first column. PostgreSQL 18 can use a B-tree skip scan in suitable cases, especially when an omitted leading column has few distinct values. Whether that helps depends on the data and cost estimates. The multicolumn index documentation describes the conditions. Design for your common query, then inspect the plan for other shapes instead of assuming that one rule settles every case.

Index the small part of the table you actually need

Now consider the billing team's list of outstanding orders:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE company_id = 42 AND status = 'pending'
ORDER BY created_at, id
LIMIT 50;

Most of the table is paid history. An index containing only pending orders can be much smaller:

CREATE INDEX orders_pending_company_idx
ON orders (company_id, created_at, id)
WHERE status = 'pending';

Repeat the preceding EXPLAIN after creating it. The index already excludes paid rows, so a suitable plan no longer needs to search through paid history to find the next pending order.

This is a partial index. Its predicate is part of its contract. PostgreSQL must be able to establish that the query needs only rows included by that predicate.

That matters with prepared statements. Suppose the application sends WHERE status = $1. A generic plan cannot assume that $1 will always be 'pending'. A custom plan may have enough information when the parameter value is known, but you should verify the behaviour of the actual application query. For a dedicated pending-orders endpoint, keeping the fixed status in the SQL often makes the intent clearer. Continue binding user-supplied values such as company IDs.

The partial index documentation explains predicate matching and its limitations. A logically similar condition is not always enough for the planner to prove that the index applies.

There is still a write cost. When an order changes from pending to paid, index maintenance is involved. The attraction is that you maintain a focused access path for a small, important working set instead of indexing every historical status combination.

Match the expression in the query

Support needs to find a customer by email within a company. The product treats email addresses as case-insensitive identifiers for this lookup.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM customers
WHERE company_id = 42
  AND lower(email) = lower('user142@example.test');

An ordinary index on email stores the original value. This query searches a calculated value, lower(email). Give it an index on the same expression:

CREATE INDEX customers_company_email_lower_idx
ON customers (company_id, lower(email));

Run the lookup again and inspect the index condition. The query should not need to calculate and compare the lowercase email for every customer.

If the product also requires email uniqueness within a company, a unique index on those keys can enforce that rule. Do not introduce uniqueness merely as a speed optimisation. Existing duplicates, shared addresses, and the product's normalisation policy need a decision first.

For international addresses and identifiers, define the case and collation behaviour deliberately. This example uses simple ASCII data. lower() is not a complete identity policy for every language or every email system.

Expression indexes also cost work during inserts and relevant updates. PostgreSQL's expression index guide is useful when deciding whether a frequent read justifies storing the calculated key.

Use GIN for JSON containment

An operations screen needs rush orders based on metadata:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM orders
WHERE company_id = 42
  AND metadata @> '{"priority": "rush"}'::jsonb;

The @> operator asks whether the JSON document contains the supplied structure. A B-tree on the whole JSON value is not the access path we want for this search.

For this containment workload, use GIN:

CREATE INDEX orders_metadata_gin_idx
ON orders USING gin (metadata jsonb_path_ops);

Repeat the query and look for a bitmap index scan, possibly combined with the company index. PostgreSQL might instead choose another plan if its estimates make that cheaper. The company condition remains essential regardless of the access path.

jsonb_path_ops supports a narrower set of operators than the default jsonb_ops. It can be a useful choice for containment searches, but it does not support the key-existence operator ?. If the application needs that operator, examine the default operator class instead. The JSONB indexing documentation explains the trade-off.

Also ask why the value is in JSON. If priority becomes a central field used in almost every order screen, a typed column with a normal index may make the application easier to validate and query. An index can speed up a data model; it cannot decide whether that model is appropriate.

Decide whether a covering index is worth it

Return to the recent-orders query. It selects total_cents, which is not in our composite index.

We can include that value without making it another search key:

CREATE INDEX orders_company_recent_cover_idx
ON orders (company_id, created_at DESC, id DESC)
INCLUDE (total_cents);

Now every selected column is available from the index. In the disposable exercise database, remove the earlier overlapping index so that you can compare this alternative clearly:

DROP INDEX orders_company_recent_idx;
VACUUM (ANALYZE) orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_cents
FROM orders
WHERE company_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50;

This creates the possibility of an index-only scan. It does not guarantee that PostgreSQL can avoid every table visit.

PostgreSQL must still establish whether a row is visible to the current transaction. It uses a visibility map to avoid heap checks for pages known to be all-visible. Recently changed pages may still require heap fetches. Inspect Heap Fetches in the plan rather than assuming that the node name means zero table access. The index-only scan documentation explains this distinction.

The VACUUM here helps demonstrate the favourable case on a settled dataset. A busy orders table will not always resemble that case. Do not use a freshly vacuumed benchmark to promise identical production results.

Would I add this covering index immediately? Usually I would first measure the original index. Fifty heap fetches may be cheap. Including large or frequently updated values makes an index larger and more expensive to maintain. Avoid putting a complete JSON document into INCLUDE simply to eliminate a small amount of table work.

Understand why PostgreSQL still chooses a table scan

An index existing does not mean it is the cheapest way to answer every query.

Consider a report that returns nearly every order. Walking an index and then visiting most of the table can cost more than scanning the table directly. A small table can also be cheaper to scan than to access through an index.

When a plan surprises you, check the following before forcing it:

Question	Why it matters
Does the query return a large share of the table?	Sequential access may be cheaper
Are estimates far from actual row counts?	Statistics may not describe the data well
Does the query apply a function or cast?	The expression may not match the index
Does a partial index cover every possible result?	The planner must prove that it is safe to use
Is the application using a generic prepared plan?	The plan may not know the current parameter value
Are several filters strongly related?	Independent estimates can miss correlations

Running ANALYZE after a bulk load is a sensible first step. If estimates remain wrong, investigate data distribution and, where appropriate, extended statistics. Do not assume the cure is another index.

Temporarily disabling sequential scans can help explore alternative plans in a diagnostic session. It is not a production fix. The real task is to understand why the planner considers the alternative more expensive.

Add the index without surprising production

The ordinary CREATE INDEX statements above are convenient for a disposable database. On a busy production table, they can block writes while the index is built.

PostgreSQL supports CREATE INDEX CONCURRENTLY to avoid blocking ordinary inserts, updates, and deletes for the full build. That does not make the operation free: it performs additional work, can wait on transactions, and consumes resources. It also cannot run inside a transaction block. Check whether your migration tool wraps migrations automatically.

If a concurrent build fails, it can leave an invalid index behind. Check its state and follow the documented recovery procedure before considering the migration complete. The CREATE INDEX reference covers these operational details.

Treat rollout as a small experiment. Capture the original query behaviour, build during an appropriate window, verify that the index is valid, and compare representative requests. Watch write latency and storage as well as read latency.

Keep an index only while it earns its place

Indexes outlive the feature that introduced them surprisingly easily. A screen changes, a query disappears, and the database keeps paying to maintain the old access path.

Review index size and usage over a representative period. Statistics can reset, and a monthly report may not appear in a short sample. An index supporting a uniqueness constraint has a correctness role even if its scan count is low. PostgreSQL's statistics views explain what the counters represent.

For each proposed index, keep a short note with the query it supports, the evidence that justified it, and the write or storage cost you accepted. That makes future cleanup a decision rather than a guessing exercise.

The best starting point is still the slow query. Find the work it is doing, choose the smallest useful access path, and measure again. Once you can explain why the index helps that query, the syntax becomes the easy part.