Index Advisor on managed PostgreSQL

Free credit available Limited time

Find the indexes your PostgreSQL queries are missing.

Index Advisor watches your real workload and shows the evidence for every index it suggests and every one it would drop. It never changes your database: you copy the SQL and run it when you choose.

Start a PostgreSQL database How it decides
On for new PostgreSQL databases Statistics hourly, analysis daily Never reads your rows Recommendations only
Replica on sample data Open a finding, copy its SQL, press Analyse now or Settings.

shop-db

PostgreSQL 17 · ap-south-1

Running

Index Advisor

On by default for new databases. Recommendations only: nothing is created or dropped for you. Watching database "shop".

On
Workload coverage Ready
Last collected31 Oct 2026, 22:5612 min ago
Distinct queries observed214+2% last 24h
Observation period41 days

Based on 214 statements observed (38 analysed), 30 Oct 22:56 to 22:56.

Data

486 MB

6 tables

Indexes

212 MB

8 indexes

Writes

38 / min

Measured over the last 60 min

Reclaimable

9.4 MB

From indexes suggested for removal

High confidence

Add an index on public.orders (customer_id)

Filtered by equality on customer_id.

public.orderscustomer_id
Query impactHighEst. size26 MB to 31 MB
Low confidence

Add an index on public.orders (created_at)

Filtered by a range on created_at.

public.orderscreated_at
Query impactMediumEst. size26 MB to 31 MB
High confidence

Add an index on public.orders (customer_id)

Filtered by equality on customer_id.

Tablepublic.orders
Column(s)customer_id
Estimated index size26 MB to 31 MB

public.orders (about 1,204,377 rows) is filtered on customer_id and no existing index serves that.

Recommended index

SQL
CREATE INDEX CONCURRENTLY "selfhost_idx_orders_5c1e9a07" ON "public"."orders" ("customer_id");

Run it yourself in a quiet moment. CREATE INDEX CONCURRENTLY does not block writes but takes longer, cannot run inside a transaction and if it fails it leaves an invalid index holding its space (drop it and retry).

Index Advisor never runs this for you.

Why this is recommended

We observed 48,213 calls filtering on orders.customer_id in the analysed period. The current plan reads the table with Seq Scan, which reads more rows than needed.

High confidence: with a hypothetical copy of the index, the query planner chose it and its estimated cost fell 99% (a planner estimate, not measured time).

Est. 26 MB to 31 MB: an estimate, not a measurement, from PostgreSQL's B-tree page layout applied to the table's statistics (rows, column widths), never its data.

A replica of the console's Index Advisor tab with its own words, on a sample database. In the console nothing here creates or drops an index: Copy SQL is the only way a statement leaves the tab.

On by default

On from the first query.
Your call on older databases.

Often an index advisor is an extension to install and a function to call, one query at a time. Here it watches the whole workload of every new PostgreSQL database from day one.

New databases

On from the start

Every managed PostgreSQL database created from scratch since 27 October 2026 has Index Advisor on. pg_stat_statements is installed before PostgreSQL first starts, so nothing restarts. The first collection follows within the hour.

Older databases

One switch, your call

An owner or admin turns it on from the Index Advisor tab. If the database already loads pg_stat_statements, nothing restarts. If it does not, PostgreSQL restarts once (a few seconds, open connections dropped) and only after you confirm.

Any time

Off whenever you like

Turning it off stops collection and analysis for that database. pg_stat_statements stays installed, so turning it on again needs no restart. Your settings and past findings are kept.

What it finds

Indexes to add.
Indexes to drop.

Each finding carries a confidence label with what it rests on, an estimated impact for ordering and, for a new index, a size range estimated from statistics, with a warning when the build may not fit on the disk.

Add

Missing indexes

Statements that filter, sort under a LIMIT or join on columns no index serves, ranked by estimated impact.

Review, then drop

Unused indexes

Indexes not scanned once over the days observed, still costing storage and work on every write.

Drop one

Duplicate indexes

Two indexes with the same definition. One of the two is enough.

Review

Redundant indexes

An index whose columns another index already leads with.

Rebuild or drop

Invalid indexes

Indexes left invalid by a failed build for at least a day, still holding their space.

Leave it

Already served or not an index

When an existing index already serves a statement or an index is not the answer, it says so instead of guessing.

How it decides

Every suggestion
shows its working.

  1. 1

    Collect, every hour

    A light look each hour reads pg_stat_statements (how often each statement ran and how long it took) plus table and index statistics. A full look runs once a day. Each database has its own minute of the hour, so collections are spread out.

  2. 2

    Analyse, every day

    Once a day it reads the workload for statements that filter, sort or join on columns no index serves and for indexes nothing uses. Analyse now runs one sooner, up to three times a day per database.

  3. 3

    Check with the planner

    Each proposal is checked against the statement's query plan. With hypothetical index checks on (HypoPG, one setting, no restart) the planner is asked whether it would use the index and how far its cost estimate falls. Nothing is built and no statement is run.

  4. 4

    You decide

    Copy the CREATE INDEX CONCURRENTLY statement and run it in a quiet moment from psql or your migration tool. Or dismiss the finding for 30 days or mark it as seen.

A worked example

-- 1. What pg_stat_statements kept: the shape, values as $1, $2
SELECT id, status, total, created_at
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC LIMIT $2
-- 41,833 calls, 41.7 ms on average

-- 2. What Index Advisor concluded
Add an index on public.orders (customer_id)
High confidence: with a hypothetical copy of the
index, the query planner chose it and its estimated
cost fell 99% (a planner estimate, not measured time).
Today:          Seq Scan, cost 25,413
With the index: Index Scan, cost 98.7

-- 3. What you copy and run yourself, in a quiet moment
CREATE INDEX CONCURRENTLY "selfhost_idx_orders_5c1e9a07"
  ON "public"."orders" ("customer_id");

Costs are planner units, not milliseconds: the tab says so next to its numbers. CREATE INDEX CONCURRENTLY does not block writes; it cannot run inside a transaction, so run it on its own from psql or a migration that allows it. The console's SQL editor is read-only, so it is the place to check the plan with EXPLAIN, not to build the index.

What it reads

Statistics in.
Your rows never.

It reads
  • ✓pg_stat_statements: how often each statement ran and how long it took
  • ✓Each statement's shape only: PostgreSQL's own parser turns every value into a parameter and cuts comments out. A statement that still holds a value keeps no text at all
  • ✓Table and index statistics: sizes, scans, row estimates and column widths
  • ✓The planner's cost estimate for a statement, with a hypothetical index when HypoPG is on
It never
  • ×Reads your rows: size estimates come from statistics, never from your data
  • ×Runs your statements to test them: the planner estimates, nothing executes
  • ×Creates, drops or changes an index for you
  • ×Restarts your database without your confirmation

Careful about drops

Slow to suggest a drop.
Never on the indexes that matter.

An index that looks unused may still serve a monthly report, so Index Advisor waits a long time before it says so. It never flags the indexes your keys and constraints depend on.

  • ●Never flagged as unused: primary keys, unique and constraint indexes, indexes under 1 MB.
  • ●Unused indexes are judged only after 7 days and 5 daily collections.
  • ●Removal is suggested only after 35 days and 25 daily collections, longer than a monthly job's month. Until then an unused index is marked for review.
  • ●When statistics are reset or the database restarts, observation starts again, so a fresh window never reads as unused.
  • ●Reads on replicas are not counted, so on a database with replicas removal is never suggested.
  • ●Every drop statement comes with a reminder to check that no rare job, report or replica needs the index.

Before you rely on it

Six things we would
want to know first.

Careful by design still has edges. None of them should surprise you after the first analysis, so here they are up front.

  1. 1 PostgreSQL only. Other engines, ClickHouse included, do not have Index Advisor.
  2. 2 One database per instance is watched: the one your application uses, set in Settings. Changing it starts observation again.
  3. 3 Costs are planner estimates in planner units and sizes are ranges. It never promises a measured speed-up.
  4. 4 On a very large database the busiest statements and tables are read and the tab says so.
  5. 5 Owners and admins turn it on, change its settings and see the findings, because findings name your tables and queries.
  6. 6 History is kept 14, 40, 60 or 90 days (40 by default). Under 35 days, unused indexes are only marked for review.

Next to Index Advisor

Find it, check it,
ship it yourself.

SQL editor EXPLAIN it in the browser Read-only SQL on managed PostgreSQL and ClickHouse: run EXPLAIN before and after you build the index. See the SQL editor → Managed PostgreSQL Where Index Advisor runs PostgreSQL 16, 17 and 18 on AWS with PITR, PgBouncer pooling, Multi-AZ, users and roles. See managed PostgreSQL → Talk to Database Ask questions, get answers Ask a managed PostgreSQL database a question and get a sentence, a chart, a table and the SQL. See Talk to Database →

Frequently asked questions

Does Index Advisor create indexes automatically?
No. It recommends and you decide. Each finding comes with the CREATE INDEX CONCURRENTLY or DROP INDEX CONCURRENTLY statement to copy and run yourself, from psql or your migration tool. Nothing is created or dropped for you.
Is it on for my database?
If your managed PostgreSQL database was created from scratch since 27 October 2026 (11:35 IST), yes: Index Advisor is on by default. On an older database (or a restored or copied one) an owner or admin turns it on from the Index Advisor tab.
Will turning it on restart my database?
Only if the database does not load pg_stat_statements yet. Then PostgreSQL restarts once (a few seconds, open connections dropped) and only after you confirm. If pg_stat_statements is already loaded, nothing restarts.
Does it read my data?
No. It reads PostgreSQL's statistics: how often each statement ran and how long it took, with every value replaced by $1, $2, plus table and index sizes, scans and row estimates. It never reads your rows.
Which databases does it support?
Managed PostgreSQL on Selfhost.dev. Other engines, ClickHouse included, do not have Index Advisor.
How often does it analyse?
It collects statistics every hour and analyses the workload once a day. Analyse now runs an extra analysis, up to three times a day per database.
Does it work on read replicas?
A replica's page shows the same findings as its primary, read-only: Index Advisor is managed on the primary. Reads on replicas are not counted, so on a database with replicas it never suggests removing an index.
Does it cost anything?
There is no charge for Index Advisor itself. It runs on the database you already pay for by the hour.

The index your query needs.
On from the first day.

Start a PostgreSQL database

$5 in free credit, no card needed.