# Index advisor and query insights
Source: https://sovaria.in/docs/index-advisor

See which SQL statements cost your database the most time, and get indexes that are proven to make them cheaper before anything is built.

**Query insights** list your project's statements from `pg_stat_statements`: how often each ran, total and average time, the worst run, rows, cache hit rate and its share of all database time. The **index advisor** takes the statements with the most total time and recommends indexes for them — and it tests each candidate first, as a *hypothetical* index, so it only recommends indexes the planner would actually use and that make the statement cheaper.

(Diagram: How the advisor decides — Nothing is built and nothing runs: statements are EXPLAINed with their $1 placeholders kept; hypothetical indexes exist only inside the advisor's session.)

## Query insights

Open **Database › Performance advisor** (or Observability › Query performance). Sort by *most total time* to find what to fix first, *slowest on average* for user-facing latency, *most frequent* for chatty code, and *worst single run* for outliers. Rows that average more than the slow threshold are highlighted. **Reset** clears this database's statistics only (other databases on a shared instance keep theirs) — useful right after you add an index.

> **Note:** Query statistics are on for new databases. Older dedicated databases need one restart (about 5 seconds) to turn them on; the page offers it.

## Index recommendations

- Each recommendation shows the table, the columns in index order, the `CREATE INDEX CONCURRENTLY` statement, the plan cost before and after for every statement it helps, and an estimate of the time it saves.
- Up to three indexes are chosen per statement (for joins, one per table), greedily: each must cut the remaining cost by the minimum gain (20% by default).
- Tables under 1,000 rows get no recommendation: a sequential scan is cheaper there.
- Columns used inside a function (`lower(email) = $1`) need an expression index, which the advisor doesn't propose; [Performance](https://sovaria.in/docs/performance#find) shows how to add one by hand.
- "Unverified" means the database has no `hypopg` yet (databases created before it was added, or a shared instance): the suggestions come from the plan alone. Try them on a [staging environment](https://sovaria.in/docs/environments-and-staging) first.

### Creating a recommended index

**Create index** builds it with `CREATE INDEX CONCURRENTLY`, so reads and writes keep working while it builds; the card shows progress until Postgres marks it ready. If a build is interrupted the index is left *invalid* — drop it and create it again. Free shows the SQL to copy instead; one-click create is on paid plans.

CLI:
```bash
sov db insights --sort total --limit 20
sov db advisor                       # recommendations for the slowest statements
sov db advisor query "SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC"
sov db advisor apply --table public.orders --columns customer_id,created_at
sov db advisor status idx_orders_customer_id_created_at
```

JavaScript:
```js
const insights = await sov.api.governance.queryInsights({ sort: 'mean', limit: 20 })
const advice = await sov.api.governance.indexAdvisor({})
const build = await sov.api.governance.applyIndex({ table: 'orders', columns: ['customer_id', 'created_at'] })
```

Python:
```python
advice = await sov.api.governance.index_advisor()
for rec in advice["recommendations"]:
    print(rec["sql"], rec["statements"][0]["gain_pct"])
```

HTTP:
```bash
curl -s $URL/api/v1/governance/tenants/$ORG/databases/$PROJECT/index-advisor -H "Authorization: Bearer $TOKEN"
curl -s -X POST $URL/api/v1/governance/tenants/$ORG/databases/$PROJECT/index-advisor/apply \
  -d '{"schema":"public","table":"orders","columns":["customer_id","created_at"]}' …
```

> **Tip:** Sovariq can do this for you: "why is my orders page slow?" runs query insights and the advisor, explains the plan and asks before creating anything.

## Plans

|  | Free | Pro | Ultra | Team | Enterprise |
|---|---|---|---|---|---|
| Index advisor (tests candidate indexes as hypothetical indexes) | ✓ | ✓ | ✓ | ✓ | ✓ |
| Slow queries the index advisor examines per run | 10 | 25 | 50 | 100 | 200 |
| Create a recommended index in one click | — | ✓ | ✓ | ✓ | ✓ |
| Statements shown in query insights | 20 | 100 | 250 | 500 | 1,000 |

The minimum gain, candidates per statement, EXPLAIN timeout, minimum table size and the slow threshold are platform settings Sovaria tunes for every project.