---
title = "Steps to improve query performance with indexes"
github_url = "https://github.com/orgs/supabase/discussions/22449"
date_created = "2024-04-03T20:10:02+00:00"
topics = [ "database", "platform", "cli" ]
keywords = [ "indexes", "performance", "optimization", "grafana", "query" ]
database_id = "fb1cbd42-e172-44b2-af2b-fda5aecde5c2"

[api]
cli = [ "supabase-inspect-db" ]
---

# Optimizing your database

This is an intermediate and actionable guide for Postgres optimization within the Supabase ecosystem.

> Consider checking out [Index_advisor](/docs/guides/database/extensions/index_advisor) and the [performance advisor](/dashboard/project/_/database/performance-advisor) now available in the Dashboard!

## Installing Supabase Grafana

Supabase has an [open-source Grafana Repo](https://github.com/supabase/supabase-grafana) that displays real-time metrics of your database. Although the [Observability Dashboard](/dashboard/project/_/observability) provides similar metrics, it averages the data by the hour or day. Having visibility over how your database responds to changes helps to ensure that the database is not stressed by the index-building process.

_Visual of Grafana Dashboard_
![image](/docs/img/troubleshooting/18ed2c88-332e-4e66-b9b4-c37e99a39104.png)

It can be run locally within Docker or can be deployed for free to fly.io. Installation instructions can be found in [Supabase's metrics docs](/docs/guides/platform/metrics#deploying-supabase-grafana)

## Query optimization through indexes

Disk (storage) is relatively slow compared to memory, so Postgres will take frequently accessed data and cache it in memory for fast access.

Ideally, you want the cache hit rate (cache-hits/total-hits) to be 99%. You should try to run the following query on your instance:

```sql
select
  'index hit rate' as name,
  (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read), 0) as ratio
from pg_statio_user_indexes
union all
select
  'table hit rate' as name,
  sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) as ratio
from pg_statio_user_tables;
```

If the cache hit rate is relatively low, it often means that you need to increase your memory capacity. The second metric that is often inspected is index usage. Indexes are data structures that allow Postgres to search for information quickly - think of them like you would think of an index at the back of a book. Instead of scanning every page (or row), you can use an index to find the contents you need quickly. For a better understanding of how Postgres decides on whether to use an index or not, check out this [explainer](https://github.com/orgs/supabase/discussions/26959).

The index hit rate (how often an index is used) can usually be improved moderately.

There's a query to find out how often an index is used when accessing a table:

```sql
select
  relname,
  100 * idx_scan / (seq_scan + idx_scan) as percent_of_times_index_used,
  n_live_tup as rows_in_table
from pg_stat_user_tables
where seq_scan + idx_scan > 0
order by n_live_tup desc;
```

A lot of the [queries for inspecting performance](/docs/reference/cli/supabase-inspect-db) are actually pre-bundled as part of the [Supabase CLI](/docs/guides/cli/getting-started). For instance, there is a command for testing which indexes of yours are unnecessary and are needlessly taking up space:

```bash
npx supabase login

npx supabase link

npx supabase inspect db unused-indexes
```

There is an extension called [index_advisor](/docs/guides/database/extensions/index_advisor) that creates virtual indexes on your queries and then checks which ones increase performance the best. Unlike normal index creation, virtual indexes can be made rapidly, which makes uncovering the most performant solutions fast. The [Query Performance Advisor](/dashboard/project/_/database/query-performance) in the Dashboard is configured to use index_advisor to make optimization suggestions and you should check it out to see where you can improve.

Index_advisor won't test indexes added through extensions nor will it test GIN/GIST indexes. For JSON or ARRAY columns, consider exploring GIN/GIST indexes separately from index_advisor. If you're using pg_vector, it's crucial to use an [HSNW index](https://github.com/orgs/supabase/discussions/21379).

Indexes can significantly speed up reads, sometimes boosting performance by 100 times. However, they come with a trade-off: they need to track all column changes, which can slow down data-modifying queries like UPDATEs, DELETEs, and INSERTs.

Generally, indexes offer more benefits. For example, primary key columns automatically have a B-Tree index, enhancing read and join operations without significantly affecting write queries. Nonetheless, it's wise to avoid carelessly adding indexes.

Some indexes may take a long time to build. A [guide](https://github.com/orgs/supabase/discussions/21379) was written for applying HSNW indexes, but it can be generalized and referenced for applying others, too.

When building an index, the affected table is locked, preventing write operations. If this poses an issue, use the [`CONCURRENTLY` modifier](https://www.postgresql.org/docs/current/sql-createindex.html). However, reserve this for necessary cases only, as it entails building the index twice, prolonging the process and increasing computational costs.
