Index advisor and query insights

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.

#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.

#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 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 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.

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

#Plans

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