A slow consultation doesn’t prove an index missing. And an index that appears in the scheme doesn’t guarantee that PostgreSQL uses them. The decision belongs to the Consultation Planner, that compares implementation paths from statistics, access costs and the number of rows that you hope to read. An index can drastically reduce the work of a selective search and can also take up space, slow down textures and be worse than passing through the table.
That article retains the three useful mechanisms of the original approach - * B-tree, GIN and partial indexes ** - but puts them into a more complete model: to observe the plan, to propose a hypothesis, to modify a structure and to check the result without treating EXPLAIN ANALYZE as an infallible speedometer.
The technical reference is PostgreSQL 18, with minor review 18.7 se publicó el 12 de noviembre de 2026. The discussion about * snap can * corresponds to that branch. The following examples are proposed laboratories: have not been performed and have been obtained while preparing this article.
Before the Index: What The Planner Decide
PostgreSQL needs to choose from Seq Scan, Index Scan, Bitmap Heap Scan and Index Only Scan as well as from the order of joins, aggregations, signage and boundaries. You can choose a Seq Scan even if there’s a perfectly valid index: if the query will read most of the pages of the table, a sequential reading can avoid random accesses to * heap *.
There is no universal percentage of selectivity from which to “indexe.” They influence the pages of the table, the size and correlation of the columns, common values, cache status, parallelism and cost parameters. That’s why an index should not be simply deleted because a consultation or * an execution ** did not use it.
La guía oficial de EXPLAIN difference between estimated costs and measurements. In a plan, cost=... expressed * relative internal units **, non-milliseconds and rows=... represented an estimated ** * cardinality, but was not observed. By adding ANALYZE, effective times and rows appear, enabling us to detect a discrepancy between model and reality. BUFFERS complete the diagnosis with block activity.
-- No ejecuta la consulta: muestra el plan estimado.
EXPLAIN
SELECT id, created_at
FROM orders_bench
WHERE tenant_id = 7
ORDER BY created_at DESC
LIMIT 20;
-- Sí ejecuta la consulta: úsalo sobre datos de laboratorio.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, created_at
FROM orders_bench
WHERE tenant_id = 7
ORDER BY created_at DESC
LIMIT 20;
Caution: EXPLAIN ANALYZE also runs UPDATE, DELETE, INSERT and other judgements with effect. Don’t run it about production writings as if it’s just showing a simulation. If you have to inspect a write in a controlled environment, you have an explicit design of the test transaction and its recovery.
A small, deliberately synthetic lab
To study plans we need a table with known distributions, not private data and an alleged production capture. That scheme is autocontained and the population using generate_series creates hundreds of examples. Adjust the quantity to your machine’s capacity and size does not mean a representative benchmark.
CREATE TABLE orders_bench (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tenant_id integer NOT NULL,
customer_email text NOT NULL,
status text NOT NULL CHECK (status IN ('open', 'paid', 'cancelled')),
total_cents integer NOT NULL CHECK (total_cents >= 0),
created_at timestamptz NOT NULL,
tags text[] NOT NULL DEFAULT '{}',
metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);
INSERT INTO orders_bench (
tenant_id, customer_email, status, total_cents,
created_at, tags, metadata
)
SELECT
(g % 40) + 1,
'demo-' || (g % 1000) || '@example.invalid',
CASE WHEN g % 20 = 0 THEN 'cancelled'
WHEN g % 3 = 0 THEN 'open' ELSE 'paid' END,
(g * 37) % 100000,
TIMESTAMPTZ '2026-01-01 00:00:00+00'
+ (g * INTERVAL '1 minute'),
CASE WHEN g % 4 = 0 THEN ARRAY['featured']::text[]
ELSE ARRAY['regular']::text[] END,
jsonb_build_object('channel', CASE WHEN g % 5 = 0 THEN 'web' ELSE 'store' END)
FROM generate_series(1, 100000) AS g;
ANALYZE orders_bench;
ANALYZE updates the statistics using the optimizer. Before adding anything, inspect critical consultations and preserve his initial plan to compare. That dataset uses modular rules and does not reproduce organic customer distributions, cancellations or dates and a useful conclusion here later needs validation with representative statistics.
B-tree: equality, range, order and leading columns
B-tree is the default method and cover most conventional searches for equality and range as well as helping to avoid some systems. The original statement “creates an index for each column that you film” is far too broad: an application consultation combinations of predictions, order and limitation. The indexes have to be designed for these consultations, but not for an isolated list of attributes.
CREATE INDEX orders_tenant_created_idx
ON orders_bench (tenant_id, created_at DESC);
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders_bench
WHERE tenant_id = 7
ORDER BY created_at DESC
LIMIT 20;
The tenant_id prefix restricts the subset and the descending order can favor ORDER BY created_at DESC LIMIT 20. It contrasts with that of a consultation without a tenant filter and with one that returns thousands of rows. Do not assume that both will use the same strategy.
Multicolumn and Skip Can Indices at PostgreSQL 18
The order of the columns matters, but they do not matter how they teach many quick rules. In a B-tree (tenant_id, status, created_at), an equality about tenant_id normally allows better delimiting the search than a filter only by created_at. Nevertheless, PostgreSQL 18 document skip can: When it compensates, the engine can generate repeated searches for potential values from an absent leading column. Your convenience depends, among other things, on how many different values that column has.
CREATE INDEX orders_tenant_status_time_idx
ON orders_bench (tenant_id, status, created_at DESC);
-- Sin condición sobre la primera columna; el planner decide.
EXPLAIN
SELECT id
FROM orders_bench
WHERE status = 'open'
AND created_at >= TIMESTAMPTZ '2026-02-01 00:00:00+00';
It is wrong to say that that index will always be useful or to guarantee that skip scan will appear. The right thing to do is to inspect PostgreSQL 18’s plan with cardinalities and representative statistics. The documentación de índices multicolumna describes that mechanism and its boundaries.
GIN: A value can contain many information tools
A GIN (* Generalised Inverted Index) is useful when a row contributes multiple search keys: terms of a tsvector, elements of an array or jsonb’s range / values according to the * operator class *. It doesn’t mean that every JSONB column should have GIN or be the universal response to a textual search.
CREATE INDEX orders_tags_gin_idx
ON orders_bench USING gin (tags);
EXPLAIN
SELECT id FROM orders_bench
WHERE tags @> ARRAY['featured']::text[];
CREATE INDEX orders_metadata_gin_idx
ON orders_bench USING gin (metadata jsonb_path_ops);
EXPLAIN
SELECT id FROM orders_bench
WHERE metadata @> '{"channel":"web"}'::jsonb;
The choice between jsonb_ops (default) and jsonb_path_ops changes covered operators, size and conduct: the example uses @> containment, supported by jsonb_path_ops. A jsonb_path_ops index doesn’t automatically serve any existence operator and select the * operator class * from actual queries. To extract and compare a concrete property a B-tree about an expression, for example (metadata->>'channel'), can also be preferred.
GIN involves maintenance, space and write amplification and you should not activate it “just in case.” The details are at tipos de índice de PostgreSQL and operator classes de GIN.
Partial indexes: To figure only the subset that matters
A partial index contains rows that fulfill a prediction. It can be appropriate if a large part of the table does not appear in the critical consultation. But a partial index about active = true does not save gigabytes by definition: size depends on ratio, width, cardinality and actual table.
CREATE INDEX orders_open_by_tenant_time_idx
ON orders_bench (tenant_id, created_at DESC)
WHERE status = 'open';
EXPLAIN
SELECT id, created_at
FROM orders_bench
WHERE tenant_id = 7 AND status = 'open'
ORDER BY created_at DESC LIMIT 20;
Consultation should involve predicting an index in a way that the planners can recognize. A human considering two “equivalent” terms cannot be sufficient: parametrized predictions, syntactic forms and generic plans can affect this inference. There’s also an operational restriction: if a row changes from open to paid, the index should be updated.
Check índices parciales before designing indexes linked to changing business states.
Coverage indices and MVCC limit
INCLUDE stores additional columns without turning them into search order keys. That can enable an Index Only Can when the index contains all necessary values. However, PostgreSQL cannot always omit heap: you need to check the MVCC visibility of each tuple, except when the * visibility map * allows you to trust that the corresponding page is marked as * all-visible *.
CREATE INDEX orders_tenant_time_cover_idx
ON orders_bench (tenant_id, created_at DESC)
INCLUDE (total_cents);
EXPLAIN (ANALYZE, BUFFERS)
SELECT created_at, total_cents
FROM orders_bench
WHERE tenant_id = 7
ORDER BY created_at DESC
LIMIT 20;
To evaluate the benefit, watch Heap Fetches in plan if the optimizer select an Index Only Scan. The result will vary with updates, VACUUM and visibility. INCLUDE isn’t free either: it makes paper inputs more voluminous and can damage density, cache and maintenance. Reference: Index-Only Scans and Covering Indexes.
Hash, GIST, SP-GIST and BRIN aren’t a fashion catalogue
PostgreSQL 18 offers 6 integrated methods with different structures and * operator classes *:
| Method | Main mechanism | That’s an argument. |
|---|---|---|
| ♪ B-tree ♪ | Orden keys, search / range | Equality, range, management |
| ♪ Hash ♪ | Key Hash code | Equal if an operational comparison justifies using them against B-tree |
| GIN | Key / item → rows | Arrays, full-text, JSONB by operator |
| ♪ GIST ♪ | Research framework by predicting structures | Range, proximity and specialized operators |
| SP-GIST | Spatial / generalized partition of search space | Shaked trees and compatible types / operator classes |
| BRIN | Summaries by range of physical pages | Large Tables with strong correlation between values and location |
BRIN can be attractive for events inserted about in temporary order. If tipprints get retidy or blocks mix values, additional readings can dilute benefits. Don’t confuse the patience of a BRIN with the ability to identify a row as selectively as a B-tree.
CREATE INDEX orders_created_brin_idx
ON orders_bench USING brin (created_at);
EXPLAIN
SELECT count(*)
FROM orders_bench
WHERE created_at >= TIMESTAMPTZ '2026-03-01 00:00:00+00';
A Bitmap Heap Scan result with a BRIN may require filtering many candidate rows: page summaries are about. The referencia de BRIN explain these characteristics.
The hidden cost: scriptures, WAL and growth
Each index occupies disc, affects cache and should be maintained when changing relevant columns. In addition to comparing SELECT latency, you have an approximate size and use of indices and think about insertions, updates and WAL. pg_stat_user_indexes helps to observe scans and by itself does not identify non-existent indices.
SELECT
indexrelname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'orders_bench'
ORDER BY pg_relation_size(indexrelid) DESC;
An index can support an unusual but crucial constraint or consultation. Moreover, accumulated statistics may have been re-initiated. Before eliminating something verifies dependencies, wants and relevant time windows.
In production, CREATE INDEX CONCURRENTLY allows to build indexes without blocking the regular textures in the same way as a conventional build but takes longer, involves several tours / waits and has restrictions: cannot be run within a transaction block and a defect can leave an invalid index to investigate. Do not use it blindly in migrations that wrap up all sentences in a transaction.
A monitoring protocol that prevents false findings
For each indexation candidate, record: minor version of PostgreSQL, outline, volume, data distribution, statistics, relevant configuration, hardware, latency storage, concurrently, cold / hot cache, SQL text, parameters, plan before / after, time measured and textures affected. It runs repetitions and separates isolated actual load tests.
A reasonable sequence at the lab is:
- Create the table and run
ANALYZE. - Capture
EXPLAIN (ANALYZE, BUFFERS)from representative reading queries before creating an index. - Build a candidate structure and register its size.
- Repeat the consultation under the same conditions and identify changes of plan, estimated cardinality versus actual, buffers and times.
- To also measure the impact of writing operations and index growth and not extrapolate by a SELECT only.
- Repeat about different data distributions and score as the
Seq Scanis the right choice.
The eight experiments suggested at the dossier del Bloque 05 - B-tree, selectivity, multicolumn / skip can, partial, textures, cardinality, visibility and BRIN - ** are still outstanding *. The above link points to EXPLAIN’s primary documentation, not to results or an performed experiment.
When the answer is no longer another index
If the application needs relative OLTP and its consultations are select, it usually makes sense to start by tuning model, statistics, SQL and indexes. If the dominant are aggregations about billions of events, an analytic layout can have benefits. If the dominant is textual relevance, autocomplete and tolerance to errors, perhaps the question isn’t what B-tree to add.
That decision is different from the indexation within PostgreSQL: at Block 05 we continue with “Electing a database as per the workload” and “MeilisResearch vs. Tipesense”. I haven’t listed them yet because they’re still under editing and have no public route.
Conclusion
The goal isn’t to get all plans to show Index Scan. It’s reducing total work without introducing a disproportionate cost to write, storage and operation. The process begins by looking at the plan and ends up with a hypothesis against a actual load. B-tree, GIN and partial indexes continue to be very useful tools and their usefulness depends on their care and care and their name’s presence in a list of best practice.
Main References: PostgreSQL 18 — Indexes, Using EXPLAIN, Index-Only Scans, Release 18.7.