Postgres Index Type Advisor

PostgreSQL ships six index access methods plus extension types, and choosing wrongly is expensive in both directions: a GIN index where a B-tree would do wastes gigabytes and slows every write, while a B-tree on a jsonb containment query is simply never used. Describe the column and the query pattern and this advisor names the access method, picks the operator class that actually matches your operator, writes the CREATE INDEX statement, and lists the runner-up options and the traps for that combination. Everything runs in your browser — no schema or data is uploaded.

SchemaTableColumnData type

Query patternPhysical order

RowsDistinct valuesPartial WHERECONCURRENTLY

Recommendation

Recommended: BRIN

The column is physically correlated with insertion order on a very large table — the ideal BRIN case. BRIN stores only the min/max per block range, so the index is typically thousands of times smaller than a B-tree while still skipping most of the table for a range scan.

Estimated selectivity: 40,000,000 distinct values over 50,000,000 rows — about 1 row(s) per value (80.0000%).

Also consider:
  - B-tree: far larger but exact, and it also supports ORDER BY and small LIMIT queries. Use it if you need the top N rows quickly.

CREATE INDEX

CREATE INDEX CONCURRENTLY events_created_at_brin
  ON public.events USING brin (created_at);

-- CONCURRENTLY does not block reads or writes, but it cannot run inside a
-- transaction block. Most migration tools wrap migrations in one, so run this
-- outside the tool or mark the migration as non-transactional.

-- Smaller pages_per_range = bigger index, better filtering. 128 is the default.
-- CREATE INDEX ... USING brin (created_at) WITH (pages_per_range = 32, autosummarize = on);

Warnings and gotchas

1. BRIN is only effective while the physical/logical correlation holds. Heavy UPDATEs or out-of-order backfills degrade it; check the correlation query below and re-run brin_summarize_new_values() after bulk loads.

Verify it works

-- 1. Does the planner actually use it? Look for an Index Scan, not a Seq Scan.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM public.events WHERE ...;

-- 2. How big is the index, and is it ever used?
SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'events'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 3. Physical correlation — BRIN needs this close to 1 or -1.
SELECT attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'events'
  AND attname = 'created_at';

-- 4. After CREATE INDEX CONCURRENTLY, check nothing was left invalid.
SELECT indexrelid::regclass AS index_name
FROM pg_index WHERE NOT indisvalid;

Do more than postgres index type advisor — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Enter the schema, table and column, then pick the column's data type and roughly how many rows and distinct values it holds.
Choose the query pattern you need to speed up — equality, range, sorting, LIKE, full-text, jsonb containment, array overlap or nearest-neighbour.
Copy the generated CREATE INDEX statement, read the warnings and alternatives, then confirm with the verification queries that the planner really uses the index.

Frequently asked questions

When should I use a GIN index instead of a B-tree in Postgres?

Use GIN when a single column value contains many searchable items and your query looks inside it: jsonb documents queried with @>, arrays queried with @> or &&, tsvector full-text search, and trigram search for ILIKE '%text%'. GIN stores one index entry per element, so it can find every row containing an element. Use a B-tree when you compare the whole value — equality, ranges, sorting and unique constraints. The costs differ sharply: GIN indexes are often several times larger than the data and slow inserts down, because each row adds many entries. If you only ever filter on one known key inside a jsonb column, skip GIN entirely and build a B-tree on the expression ((data ->> 'status')) — it is far smaller and faster.

What is a BRIN index and when is it worth using?

BRIN (Block Range Index) stores only the minimum and maximum value for each group of table blocks, typically 128 pages. That makes it tiny — often a few hundred kilobytes where a B-tree would need many gigabytes — but it only helps when the column's physical order on disk matches its logical order. The classic fit is an append-only table with a created_at or auto-incrementing id column, queried by wide ranges. Check the correlation column in pg_stats: values near 1 or -1 mean BRIN will work well, values near 0 mean it will scan almost the whole table. BRIN is a poor choice for fetching a handful of rows or for ORDER BY ... LIMIT, because it always returns block ranges that Postgres must then recheck row by row.

Why is Postgres ignoring my index?

The most common causes are a mismatched operator class, a mismatched expression, low selectivity and stale statistics. A B-tree on a text column will not serve LIKE 'abc%' unless it was built with text_pattern_ops or the database collation is C. An index on lower(email) is only used if the query also writes lower(email). A jsonb_path_ops index cannot answer the ? key-existence operator. And if a value matches a large share of the table, a sequential scan genuinely is cheaper, so the planner is right to skip the index. Run ANALYZE to refresh statistics, then read EXPLAIN (ANALYZE, BUFFERS) to see what the planner actually chose. Chat2DB visualises execution plans and index usage next to your schema, so you can spot unused and duplicate indexes quickly — download it at https://chat2db.ai/download or use the web version at https://app.chat2db.ai.