Indexing Is Query Design, Not Column Decoration

A PostgreSQL indexing piece that reframes indexes as physical access paths for query shapes, not generic column-level speed buttons.

Most indexing advice starts with the wrong instinct.

This query is slow. Add an index.

That is not wrong, but it is incomplete. An index is not a generic speed button. An index is a physical access path for a specific way of asking for data.

If you have ever asked why your Postgres index is not used, the answer is almost always that the shape of the query and the shape of the index do not match. EXPLAIN ANALYZE shows exactly where they part ways, and the rest of this piece is about reading that mismatch before you create the next index.

The useful question is not Which column should I index?

The useful question is:

What shape is this query?

Equality lookup, ordered timeline, prefix search, substring search, JSON containment, array containment, spatial lookup, and derived aggregation are different query shapes. They need different index designs.

The rule:

Do not index columns. Index access patterns.

This article uses one cargo logistics schema throughout. Each example introduces one query shape, explains why the first obvious index is incomplete, then shows the index that matches the query.

Setup: Cargo Logistics Data

The schema is intentionally small. It is not a full logistics system. It gives us enough real tables to discuss indexing without switching domains.

Run this in PostgreSQL:

DROP SCHEMA IF EXISTS cargo_indexing_demo CASCADE;
CREATE SCHEMA cargo_indexing_demo;
SET search_path = cargo_indexing_demo;

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE TABLE facilities (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code text NOT NULL UNIQUE,
  name text NOT NULL,
  coordinates point NOT NULL
);

CREATE TABLE shipments (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tracking_number text NOT NULL UNIQUE,
  customer_reference text NOT NULL,
  status text NOT NULL,
  origin_facility_id bigint NOT NULL REFERENCES facilities(id),
  destination_facility_id bigint NOT NULL REFERENCES facilities(id),
  created_at timestamptz NOT NULL
);

CREATE TABLE parcels (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  shipment_id bigint NOT NULL REFERENCES shipments(id),
  current_facility_id bigint NOT NULL REFERENCES facilities(id),
  tracking_number text NOT NULL UNIQUE,
  recipient_name text NOT NULL,
  recipient_name_search text NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL,
  delivery_address jsonb NOT NULL
);

CREATE TABLE shipment_events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  shipment_id bigint NOT NULL REFERENCES shipments(id),
  facility_id bigint REFERENCES facilities(id),
  correlation_id uuid NOT NULL,
  event_type text NOT NULL,
  occurred_at timestamptz NOT NULL,
  payload jsonb NOT NULL
);
CREATE TABLE cargo_items (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  shipment_id bigint NOT NULL REFERENCES shipments(id),
  description text NOT NULL,
  hazard_classes text[] NOT NULL DEFAULT '{}',
  weight_kg numeric(12, 2) NOT NULL
);
CREATE TABLE customs_documents (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  shipment_id bigint NOT NULL REFERENCES shipments(id),
  document_type text NOT NULL,
  created_at timestamptz NOT NULL,
  payload jsonb NOT NULL
);
CREATE TABLE carrier_webhook_events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  carrier text NOT NULL,
  external_event_id text NOT NULL,
  received_at timestamptz NOT NULL,
  payload jsonb NOT NULL
);

Seed rows:

INSERT INTO facilities (code, name, coordinates)
VALUES
  ('KTM-PORT', 'Kathmandu Inland Terminal', point(85.3240, 27.7172)),
  ('BIR-HUB', 'Birgunj Cargo Hub', point(84.8580, 27.0104)),
  ('PKR-DEPOT', 'Pokhara Delivery Depot', point(83.9856, 28.2096));

INSERT INTO shipments (
  tracking_number,
  customer_reference,
  status,
  origin_facility_id,
  destination_facility_id,
  created_at
)
VALUES
  ('SHP-2026-0001', 'ACME-PO-1001', 'in_transit', 1, 3, '2026-05-01 08:00:00+00'),
  ('SHP-2026-0002', 'ACME-PO-1002', 'customs_hold', 1, 2, '2026-05-02 09:00:00+00'),
  ('SHP-2026-0003', 'BETA-PO-9001', 'delivered', 2, 3, '2026-05-03 10:00:00+00');
INSERT INTO parcels (
  shipment_id,
  current_facility_id,
  tracking_number,
  recipient_name,
  recipient_name_search,
  status,
  created_at,
  delivery_address
)
VALUES
  (1, 1, 'PCL-0001-A', 'Sabudh Thapa', 'sabudh thapa', 'at_origin', '2026-05-01 08:05:00+00', '{"city":"Kathmandu","country":"NP"}'),
  (1, 1, 'PCL-0001-B', 'S. Thapa', 's thapa', 'at_origin', '2026-05-01 08:06:00+00', '{"city":"Kathmandu","country":"NP"}'),
  (2, 2, 'PCL-0002-A', 'Jose Alvarez', 'jose alvarez', 'customs_hold', '2026-05-02 09:10:00+00', '{"city":"Birgunj","country":"NP"}'),
  (3, 3, 'PCL-0003-A', 'Maya Gurung', 'maya gurung', 'delivered', '2026-05-03 10:10:00+00', '{"city":"Pokhara","country":"NP"}');
INSERT INTO shipment_events (
  shipment_id,
  facility_id,
  correlation_id,
  event_type,
  occurred_at,
  payload
)
VALUES
  (1, 1, '11111111-1111-1111-1111-111111111111', 'picked_up', '2026-05-01 08:10:00+00', '{"source":"scanner"}'),
  (1, 1, '11111111-1111-1111-1111-111111111111', 'departed_facility', '2026-05-01 11:30:00+00', '{"truck":"TRK-44"}'),
  (1, 2, '11111111-1111-1111-1111-111111111111', 'arrived_facility', '2026-05-02 02:15:00+00', '{"dock":"D7"}'),
  (2, 1, '22222222-2222-2222-2222-222222222222', 'picked_up', '2026-05-02 09:30:00+00', '{"source":"edi"}'),
  (2, 2, '22222222-2222-2222-2222-222222222222', 'customs_hold', '2026-05-03 14:00:00+00', '{"reason":"missing_invoice"}'),
  (3, 2, '33333333-3333-3333-3333-333333333333', 'picked_up', '2026-05-03 10:30:00+00', '{"source":"scanner"}'),
  (3, 3, '33333333-3333-3333-3333-333333333333', 'delivered', '2026-05-04 16:45:00+00', '{"signed_by":"Maya Gurung"}');
INSERT INTO cargo_items (shipment_id, description, hazard_classes, weight_kg)
VALUES
  (1, 'Laptop batteries', ARRAY['lithium_battery'], 18.50),
  (2, 'Industrial solvent', ARRAY['flammable', 'liquid'], 240.00),
  (3, 'Textile samples', ARRAY[]::text[], 35.00);
INSERT INTO customs_documents (shipment_id, document_type, created_at, payload)
VALUES
  (1, 'commercial_invoice', '2026-05-01 08:20:00+00', '{"declared_value":1200,"currency":"USD","hazmat":false}'),
  (2, 'commercial_invoice', '2026-05-02 09:20:00+00', '{"declared_value":9000,"currency":"USD","hazmat":true,"missing":["packing_list"]}'),
  (3, 'proof_of_delivery', '2026-05-04 16:50:00+00', '{"signed_by":"Maya Gurung","photos":2}');
INSERT INTO carrier_webhook_events (carrier, external_event_id, received_at, payload)
VALUES
  ('DHL', 'evt_dhl_01JX9A000001', '2026-05-01 08:12:00+00', '{"type":"pickup_confirmed"}'),
  ('DHL', 'evt_dhl_01JX9A000002', '2026-05-02 02:16:00+00', '{"type":"arrived_facility"}'),
  ('FEDEX', 'evt_fdx_01JX9B000001', '2026-05-04 16:46:00+00', '{"type":"delivered"}');

1. Exact Identity Lookup

Query:

SELECT *
FROM shipments
WHERE tracking_number = 'SHP-2026-0001';

This asks for one shipment by its business identity.

The schema already has:

tracking_number text NOT NULL UNIQUE

PostgreSQL backs that uniqueness with a B-tree index.

If it did not exist, create it:

CREATE UNIQUE INDEX shipments_tracking_number_idx
ON shipments(tracking_number);

Why this is the right shape:

• The query compares one scalar value for equality.
• The value is a business identity.
• The index should both speed lookup and reject duplicates.

Do not use hash here. Hash can help equality, but this value must be unique. A unique B-tree encodes both performance and correctness.

2. Prefix Search Is Still Ordered Search

Now change the query:

SELECT *
FROM shipments
WHERE tracking_number LIKE 'SHP-2026%';

This no longer asks for one exact tracking number.

It asks:

Find tracking numbers that begin with this prefix.

Prefix search can still use ordered access because all values starting with SHP-2026 are adjacent in the right B-tree ordering.

Index:

CREATE INDEX shipments_tracking_prefix_idx
ON shipments(tracking_number text_pattern_ops);

text_pattern_ops is a B-tree operator class.

An operator class tells PostgreSQL how index values should be compared for a family of operators. Here, the operator family is pattern matching with LIKE 'prefix%'.

Why this matters:

• Normal text ordering follows collation rules.
• Collation is human-language sorting and comparison behavior.
• Tracking numbers are machine codes, not human words.
• For prefix matching on codes, byte-like pattern ordering is the useful behavior.

This does not mean an ordinary B-tree can never help prefix search. Under the C locale, PostgreSQL can use the normal B-tree for left-anchored patterns like LIKE 'SHP-2026%'. The pattern operator class is the deliberate choice when you want reliable prefix LIKE behavior for machine codes under non-C collations.

Local rule:

Exact identity lookup: unique B-tree. Prefix code search: B-tree with pattern operator class.

This section does not solve substring search. Substring search is a different shape and appears in the text-search section.

3. Equality Plus Order Needs a Composite B-tree

Query:

SELECT *
FROM shipment_events
WHERE shipment_id = 1
ORDER BY occurred_at DESC
LIMIT 100;

This asks:

For one shipment, show the newest 100 events.

A single-column index on shipment_id helps only the first part:

CREATE INDEX shipment_events_shipment_idx
ON shipment_events(shipment_id);

With that index, the work shape is:

1. Find events for shipment_id = 1.
2. Fetch matching rows.
3. Sort matching rows by occurred_at DESC.
4. Return the first 100.

The database avoids a full table scan, but it may still sort the matching shipment events.

The query shape includes ordering and early stop. Match that shape:

CREATE INDEX shipment_events_shipment_occurred_desc_idx
ON shipment_events(shipment_id, occurred_at DESC);

Now the work shape is:

1. Seek to shipment_id = 1.
2. Read events already newest first.
3. Stop after LIMIT 100.

The composite B-tree is ordered by the combined key:

• shipment_id first
• occurred_at DESC inside each shipment_id

The index is not “shipment_id plus an extra indexed column.” It is the physical layout for one shipment’s events, newest first, stop after 100.

The Same Pattern With Workflow Correlation

Query:

SELECT *
FROM shipment_events
WHERE correlation_id = '22222222-2222-2222-2222-222222222222'
ORDER BY occurred_at DESC
LIMIT 100;

This asks the same kind of question, but with a different equality key:

For one workflow correlation, show the newest 100 events.

Index:

CREATE INDEX shipment_events_correlation_occurred_desc_idx
ON shipment_events(correlation_id, occurred_at DESC);

Same mechanism:

• equality key first
• order inside that key
• stop at LIMIT

The Cost of Adding Every Useful Index

Both indexes are defensible:

CREATE INDEX shipment_events_shipment_occurred_desc_idx
ON shipment_events(shipment_id, occurred_at DESC);

CREATE INDEX shipment_events_correlation_occurred_desc_idx
ON shipment_events(correlation_id, occurred_at DESC);

But every index has write cost:

• insert: maintain every relevant index
• update: possibly rewrite index entries
• delete: remove index entries
• storage: more disk and cache pressure
• planner: more access paths to estimate

The decision is not:

Can this query use an index?

The decision is:

Is this query important enough, frequent enough, and expensive enough to justify another maintained access path?

Use production frequency and query plans. A query plan shows how PostgreSQL actually ran the query: scan type, rows read, buffers touched, sort work, and execution time. For a deeper introduction to plans, see Why Your App Slows Down When Users Increase?.

4. Foreign Keys Are Equality Paths

Query:

SELECT *
FROM parcels
WHERE shipment_id = 1;

This asks:

Find parcels that reference one shipment.

The parent side, shipments.id, is indexed because it is a primary key.

The child side, parcels.shipment_id, is not automatically indexed by PostgreSQL.

Add it when this access pattern is common:

CREATE INDEX parcels_shipment_id_idx
ON parcels(shipment_id);

Why B-tree works:

• The predicate is equality.
• B-tree supports equality lookup.
• The same B-tree family can extend to equality plus ordering later.

The same child-side index also matters for referential actions. If a shipment is deleted or its primary key is updated, PostgreSQL must check whether child parcel rows reference it. Without an index on the child foreign-key column, that check can become expensive on a large child table.

Why Hash Is Not the Default Here

Hash indexes can serve pure equality:

CREATE INDEX parcels_shipment_hash_idx
ON parcels USING hash(shipment_id);

For:

SELECT *
FROM parcels
WHERE shipment_id = 1;

The hash index can find rows whose shipment_id equals 1.

It does not cause a full sequential scan. Hash is useful for the equality predicate.

But this is not a strong hash-index candidate:

• many parcels may share one shipment_id
• the query may later need ordering or pagination
• foreign-key paths often become composite paths

If the query becomes:

SELECT *
FROM parcels
WHERE shipment_id = 1
ORDER BY created_at DESC
LIMIT 50;

the hash index can find matching rows, then PostgreSQL still has to sort them:

1. Find parcels where shipment_id = 1.
2. Fetch matching rows.
3. Sort matching rows by created_at DESC.
4. Return the first 50.

The matching B-tree:

CREATE INDEX parcels_shipment_created_at_idx
ON parcels(shipment_id, created_at DESC);

lets PostgreSQL:

1. Seek to shipment_id = 1.
2. Read parcels newest first.
3. Stop after LIMIT 50.

Hash indexes still exist because pure equality exists. A cleaner candidate is a high-cardinality value queried only by equality:

SELECT *
FROM carrier_webhook_events
WHERE carrier = $1
  AND external_event_id = $2;

Even here, if the carrier event ID is used for idempotency, prefer a unique B-tree:

CREATE UNIQUE INDEX carrier_webhook_events_carrier_external_event_id_key
ON carrier_webhook_events(carrier, external_event_id);

That index encodes the real identity rule:

One carrier cannot deliver the same external event twice.

A global unique index on only external_event_id would be too strong if two carriers can emit the same ID format.

Hash is a benchmark candidate only when all of this is true:

• equality only
• high-cardinality values
• no uniqueness requirement
• no ORDER BY
• no range filter
• no likely composite query shape
• measured win over B-tree

Hash can be tested for pure equality. B-tree is the default when equality may need uniqueness, order, range, or composite access.

5. Text Search Depends on the Operator

These four queries mention the same column but ask different questions:

-- exact
SELECT *
FROM parcels
WHERE recipient_name = 'Sabudh Thapa';

-- case-insensitive prefix
SELECT *
FROM parcels
WHERE lower(recipient_name) LIKE 'sab%';

-- case-insensitive substring
SELECT *
FROM parcels
WHERE lower(recipient_name) LIKE '%thapa%';

Exact lookup can use a B-tree on the stored value.

Case-insensitive lookup transforms the value, so index the expression:

CREATE INDEX parcels_recipient_name_lower_idx
ON parcels(lower(recipient_name));

Prefix search on the transformed value needs pattern behavior:

CREATE INDEX parcels_recipient_name_lower_prefix_idx
ON parcels(lower(recipient_name) text_pattern_ops);

Substring search is different. The match can start anywhere inside the name, so a normal B-tree ordered by the whole string cannot group all matches together.

Use trigram GIN:

CREATE INDEX parcels_recipient_name_trgm_idx
ON parcels USING gin (lower(recipient_name) gin_trgm_ops);

GIN means Generalized Inverted Index.

An inverted index maps searchable pieces to rows that contain them.

For trigram search, PostgreSQL breaks text into overlapping three-character chunks. A query like:

WHERE lower(recipient_name) LIKE '%thapa%'

can use trigrams from thapa to find candidate rows.

Exact or prefix text search: B-tree over the searched expression. Substring text search: trigram GIN over the searched expression.

6. Name Search Is a Product Contract, Not Just an Index

I am from Nepal. My name in Nepali can be written as:

सबुध थापा

That is not a lowercase form of Sabudh Thapa. It is a different script.

Before indexing multilingual names, define the search contract. The same column can support very different promises:

Exact Nepali-script match

recipient_name = 'सबुध थापा'

Index shape:

CREATE INDEX parcels_recipient_name_idx ON parcels(recipient_name);

This answers only exact stored-value lookup.

Nepali-script prefix

recipient_name LIKE 'सबुध%'

Index shape, if this query is hot:

CREATE INDEX parcels_recipient_name_prefix_idx ON parcels(recipient_name text_pattern_ops);

This answers prefix lookup in the stored script.

Latin case-insensitive prefix

lower(recipient_name) LIKE 'sabudh%'

Index shape:

CREATE INDEX parcels_recipient_name_lower_prefix_idx 
ON parcels(lower(recipient_name) text_pattern_ops);

This answers one Latin-script prefix behavior. It does not define accent handling, tokenization, ranking, or cross-script matching.

Latin substring

lower(recipient_name) LIKE '%thapa%'

Index shape:

CREATE INDEX parcels_recipient_name_trgm_idx 
ON parcels USING gin (lower(recipient_name) gin_trgm_ops);

This answers substring search over the Latin representation.

Cross-script search

If searching sabudh should match सबुध, the index cannot invent that semantic. Store a maintained search representation and index that representation:

CREATE INDEX parcels_recipient_name_search_prefix_idx 
ON parcels(recipient_name_search text_pattern_ops);

This works only if recipient_name_search is maintained with the same normalization and transliteration rules the product promises.

This is not a global name-search design:

WHERE lower(recipient_name) LIKE 'sab%'

It only defines one Latin-script prefix behavior. It does not define accents, Devanagari, transliteration, tokenization, or ranking.

7. JSONB and Arrays Need Inverted Lookup

Query:

SELECT *
FROM customs_documents
WHERE payload @> '{"hazmat": true}';

This asks:

Find JSON documents containing this key/value structure.

A B-tree orders each payload value as a whole. That is not what this query needs.

The query is not:

Is this entire JSON document equal to that entire JSON document?

The query is:

Does this document contain key hazmat with value true?

That is an inverted-lookup problem.

Use GIN:

CREATE INDEX customs_documents_payload_gin_idx
ON customs_documents USING gin (payload);

Why GIN works:

• GIN indexes searchable pieces inside each value.
• For JSONB, those pieces include keys, values, and key/value structures.
• The index can map hazmat=true to rows whose payload contains it.

That index uses PostgreSQL’s default JSONB GIN operator class, jsonb_ops. It is the flexible choice: it supports containment with @>, key-existence operators such as ?, and JSONPath operators.

If the product contract is mostly containment, a narrower candidate is jsonb_path_ops:

CREATE INDEX customs_documents_payload_path_gin_idx
ON customs_documents USING gin (payload jsonb_path_ops);

The tradeoff:

• jsonb_ops: broader operator support
• jsonb_path_ops: usually smaller and faster for containment-heavy searches

So the question is not “JSONB means GIN.” The question is which JSONB operators the product actually uses.

Arrays have the same shape:

SELECT *
FROM cargo_items
WHERE hazard_classes @> ARRAY['flammable'];

This asks:

Which rows contain the array element flammable?

Use GIN:

CREATE INDEX cargo_items_hazard_classes_gin_idx
ON cargo_items USING gin (hazard_classes);

When Containment Meets a Numeric Range

SELECT *
FROM cargo_items
WHERE hazard_classes @> ARRAY['flammable']
  AND weight_kg > 100;

The GIN index helps the array-containment predicate. It does not order or range-filter weight_kg.

If this query is hot, test a B-tree on the scalar range value:

CREATE INDEX cargo_items_weight_idx
ON cargo_items(weight_kg);

PostgreSQL may use one index and filter the other predicate, or it may combine row locations from multiple indexes with a bitmap plan.

A bitmap plan means:

1. Build candidate row-location sets from indexes.
2. Combine those sets.
3. Visit the matching table rows.

Containment inside a value: GIN. Range or order over a scalar: B-tree. Both together: verify the plan.

8. Coordinates Need Spatial Indexing

Query:

SELECT *
FROM facilities
ORDER BY coordinates <-> point(85.0, 27.5)
LIMIT 3;

This asks:

Which facilities are nearest to this point?

B-tree indexes one-dimensional order. Spatial distance is not a simple one-dimensional scalar order stored in the table.

Use GiST:

CREATE INDEX facilities_coordinates_gist_idx
ON facilities USING gist (coordinates);

GiST means Generalized Search Tree. PostgreSQL can use it for access patterns that do not fit ordinary scalar B-tree ordering.

This example uses PostgreSQL’s built-in point type to keep the setup runnable. Production geospatial systems often use PostGIS, but the indexing lesson is the same:

Spatial query: spatial access path.

Do not read this demo as production earth-distance logic. PostgreSQL point with <-> gives distance in the coordinate plane. For real latitude/longitude routing, radius search, or distance in meters, use a geospatial model such as PostGIS geometry or geography and index that model with the operator class that matches the query.

Now compare that with a simple code lookup:

SELECT *
FROM facilities
WHERE code = 'KTM-PORT';

That is not spatial. It is exact lookup by facility code, so the unique B-tree on code is the right index.

9. Append-Only Event Logs May Want BRIN

Query:

SELECT *
FROM shipment_events
WHERE occurred_at >= now() - interval '1 day';

This asks:

Find recent events in a large event log.

A B-tree on occurred_at can help. But for a huge append-only table, a BRIN index can be much smaller:

CREATE INDEX shipment_events_occurred_at_brin_idx
ON shipment_events USING brin(occurred_at);

BRIN means Block Range Index.

It stores summaries for ranges of table pages, not one index entry per row.

Why this works:

• If table pages near the beginning contain old timestamps,
• and pages near the end contain recent timestamps,
• PostgreSQL can skip page ranges that cannot match the time predicate.

Limitation:

BRIN depends on physical row order roughly matching the indexed value.

If old events are constantly backfilled into random locations, page-range summaries become less selective.

Large append-only time-window scan: consider BRIN. Random physical order: BRIN is weak.

10. CTEs Do Not Make Derived Values Indexable

CTE refers to Common Table Expression

Query:

WITH shipment_lifecycle AS (
  SELECT
    shipment_id,
    min(occurred_at) FILTER (WHERE event_type = 'picked_up') AS picked_up_at,
    min(occurred_at) FILTER (WHERE event_type = 'delivered') AS delivered_at
  FROM shipment_events
  WHERE occurred_at >= now() - interval '30 days'
    AND event_type IN ('picked_up', 'delivered')
  GROUP BY shipment_id
),
delivery_metrics AS (
  SELECT
    shipment_id,
    picked_up_at,
    delivered_at,
    delivered_at - picked_up_at AS transit_duration
  FROM shipment_lifecycle
  WHERE picked_up_at IS NOT NULL
    AND delivered_at IS NOT NULL
)
SELECT shipment_id, transit_duration
FROM delivery_metrics
WHERE transit_duration > interval '72 hours';

This query has three stages:

1. Scan base shipment events.
2. Aggregate pickup and delivery timestamps per shipment.
3. Filter by derived transit duration.

First, index the base scan. One candidate is:

CREATE INDEX shipment_events_type_time_shipment_idx
ON shipment_events(event_type, occurred_at, shipment_id)
WHERE event_type IN ('picked_up', 'delivered');

That index helps:

WHERE event_type IN ('picked_up', 'delivered')
  AND occurred_at >= ...

It is a candidate, not a universal answer. Index column order depends on which predicate cuts the search space first:

• (event_type, occurred_at, shipment_id): good when event_type is a strong filter and the time window is still broad
• (occurred_at, event_type, shipment_id): good when the recent time window is highly selective
• (shipment_id, event_type, occurred_at): good when the hot query reconstructs lifecycle for known shipments

The point of this section is not that one base index is canonical. The point is that all of these indexes can only help read base events.

It does not directly help:

WHERE transit_duration > interval '72 hours'

because transit_duration is not stored in shipment_events. It is computed after grouping:

delivered_at - picked_up_at

A CTE gives the value a name. It does not make that value an indexed column.

If late-delivery search is a hot product query, materialize the derived metric:

CREATE TABLE shipment_delivery_metrics (
  shipment_id bigint PRIMARY KEY REFERENCES shipments(id),
  picked_up_at timestamptz NOT NULL,
  delivered_at timestamptz NOT NULL,
  transit_duration interval NOT NULL
);

CREATE INDEX shipment_delivery_metrics_duration_idx
ON shipment_delivery_metrics(transit_duration);

Now this query has a stored value to index:

SELECT shipment_id, transit_duration
FROM shipment_delivery_metrics
WHERE transit_duration > interval '72 hours';

Occasional derivation: index base facts. Hot derived metric: materialize and index the metric.

Why Postgres is not using your index

Every case above ends the same way when it fails: the index exists and the planner ignores it. The reasons repeat.

• Selectivity. If the predicate matches a large share of the table, a sequential scan is cheaper than following an index and then fetching heap pages one by one. The planner is right to skip it.
• Column order. A composite B-tree on (shipment_id, occurred_at DESC) serves equality on the first column plus order on the second. Filter on occurred_at alone and the leading column is unconstrained, so the index cannot be walked.
• Operator and expression mismatch. An index on recipient_name does nothing for lower(recipient_name), a plain B-tree does nothing for LIKE '%text%', and a GIN index built for @> does nothing for a range comparison. The index has to match the operator the query uses.
• Derived values. Anything computed inside a CTE or subquery is not indexable unless it is stored, so filtering on it is always a scan of the intermediate result.
• Stale statistics or small tables. After a bulk load, run ANALYZE. On a tiny table the planner will read the whole thing regardless, which is correct and not a bug.

Before adding another index, name which of these applies. The fix is usually a different index shape, a rewritten predicate, or accepting the scan, and the plan tells you which.

Verification: Plans Prove Indexes

Use:

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

This actually runs the query and reports how PostgreSQL executed it.

Look for:

• scan type: sequential scan, index scan, bitmap scan
• actual rows read
• rows removed by filters
• buffers touched
• sort nodes
• execution time under realistic parameters

Also check write cost. Every index that speeds up a read path must be maintained by inserts, updates, and deletes.

An index is not proven by its existence. It is proven by the workload it improves.

Cheat Sheet

Exact identity

Question: find shipment by tracking number.

Index shape:

CREATE UNIQUE INDEX shipments_tracking_number_idx
ON shipments(tracking_number);

Prefix code search

Question: find tracking numbers that start with a prefix.

Index shape:

CREATE INDEX shipments_tracking_prefix_idx
ON shipments(tracking_number text_pattern_ops);

Equality plus order plus limit

Question: show the latest events for one shipment.

Index shape:

CREATE INDEX shipment_events_shipment_occurred_desc_idx
ON shipment_events(shipment_id, occurred_at DESC);

Question: show the latest events for one workflow correlation.

Index shape:

CREATE INDEX shipment_events_correlation_occurred_desc_idx
ON shipment_events(correlation_id, occurred_at DESC);

Foreign-key equality

Question: find parcels for one shipment.

Index shape:

CREATE INDEX parcels_shipment_id_idx
ON parcels(shipment_id);

Equality plus recent order

Question: find recent parcels for one shipment.

Index shape:

CREATE INDEX parcels_shipment_created_at_idx
ON parcels(shipment_id, created_at DESC);

Pure high-cardinality equality

Question: find a row by a high-cardinality equality value with no uniqueness, order, range, or composite requirement.

Index shape: benchmark hash against B-tree. Do not assume hash wins.

Case-insensitive recipient lookup

Question: match the lowercase form of a recipient name.

Index shape:

CREATE INDEX parcels_recipient_name_lower_idx
ON parcels(lower(recipient_name));

Recipient prefix search

Question: match a case-insensitive recipient-name prefix.

Index shape:

CREATE INDEX parcels_recipient_name_lower_prefix_idx
ON parcels(lower(recipient_name) text_pattern_ops);

Recipient substring search

Question: find a recipient name containing text anywhere inside it.

Index shape:

CREATE INDEX parcels_recipient_name_trgm_idx
ON parcels USING gin (lower(recipient_name) gin_trgm_ops);

Cross-script recipient search

Question: search sabudh and match सबुध.

Index shape: maintained recipient_name_search column plus an index that matches the promised search behavior.

JSON containment

Question: find JSON documents containing a key/value structure.

Index shape:

CREATE INDEX customs_documents_payload_gin_idx
ON customs_documents USING gin (payload);

For containment-heavy workloads, also test:

CREATE INDEX customs_documents_payload_path_gin_idx
ON customs_documents USING gin (payload jsonb_path_ops);

Array containment

Question: find cargo items containing one hazard class.

Index shape:

CREATE INDEX cargo_items_hazard_classes_gin_idx
ON cargo_items USING gin (hazard_classes);

Spatial lookup

Question: find nearest facilities.

Index shape:

CREATE INDEX facilities_coordinates_gist_idx
ON facilities USING gist (coordinates);

Recent append-only events

Question: scan recent rows in a large append-only event log.

Index shape:

CREATE INDEX shipment_events_occurred_at_brin_idx
ON shipment_events USING brin(occurred_at);

Derived metric

Question: find shipments whose stored transit duration is greater than a threshold.

Index shape:

CREATE INDEX shipment_delivery_metrics_duration_idx
ON shipment_delivery_metrics(transit_duration);

PostgreSQL indexing best practices

The rules this piece arrives at, stated once.

• Start from the query, not the column. Write the predicate, the order, and the limit first, then choose the index that serves that shape.
• Equality plus order needs a composite B-tree with the equality column leading and the order column matching direction.
• Prefix search is ordered search. Use text_pattern_ops when the collation is not C.
• Expression queries need expression indexes. lower(col) in the query means lower(col) in the index.
• Containment and substring need inverted indexes: GIN for JSONB, arrays, and trigrams; GiST for spatial.
• Append-only time series can use BRIN when rows arrive in order and the table is large.
• Every index is a write cost. Keep the ones a measured workload uses and drop the rest.
• Prove each index with EXPLAIN (ANALYZE, BUFFERS) under realistic parameters, never by its existence.

Final Rule

If you cannot name the query shape, you cannot justify the index.

No query shape, no index.