Log slow and failing queries

Find slow and failing queries by recording them in your Postgres logs.

Use this guide when a statement runs slowly and you want to know which one. It covers anything that connects as the postgres role, such as a backend service, an ORM, or a migration. Postgres records each slow statement with how long it took and, if you want it, how it ran the query. When an error shows up in your logs, you can also find the query that caused it.

The slow query settings don't cover requests through the Data API, such as supabase-js calls, which connect as other roles. If your slow queries come from your app that way, start with the Query Performance page and filter by role. To see the plan of a slow Data API query, use explain(). Query Performance doesn't show queries that time out or fail. For those, see Find the query behind an error, which covers every role.

To read the logs, see Logs in Studio. To search them with SQL, see Query logs with SQL. For every log setting, see Postgres log configurations.

Logging a large amount of query activity can slow your queries and increase logging costs. Lower a threshold while you investigate, and raise it again when you're done.

Log slow queries#

To log the statements that run longer than a threshold:

1. Set the threshold. Postgres logs each statement whose run time meets or exceeds the threshold. Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

   1
   alter role "postgres" set "log_min_duration_statement" to '2s';

2. Open a new connection, or restart your app's connection pool, and run the queries you want to investigate. The setting applies only to connections opened after you change it, and a pool can keep reusing older ones.

3. In the Postgres logs, find the entries that start with duration:. To search them with SQL instead, see Query logs with SQL.

Each slow statement produces a log like this:

1
duration: 2107.797 ms  statement: select count(*), pg_sleep(2.1) from example

If a slow query is missing from the logs, see How query logging works.

Log the query plans of slow queries#

To log the query plan of each query that runs longer than a threshold:

1. Set the threshold. The auto_explain module logs the statement and the query plan of each query whose run time meets or exceeds the threshold. For how it works, see auto_explain in the Postgres documentation.

   1
   alter role "postgres" set "auto_explain.log_min_duration" to '2s';

2. Open a new connection, or restart your app's connection pool, and run the queries you want to investigate.

3. In the Postgres logs, find the entries that contain plan:. To search them with SQL instead, see Query logs with SQL.

Each slow query produces a log like this:

1
duration: 2107.319 ms  plan:
2
  Query Text: select count(*), pg_sleep(2.1) from example
3
  Aggregate  (cost=1855.06..1855.08 rows=1 width=12)
4
    ->  Seq Scan on example  (cost=0.00..1572.65 rows=112965 width=0)

Other auto_explain settings, such as auto_explain.log_analyze and auto_explain.log_buffers, control how much detail each plan records. For every setting, see auto_explain in the Postgres documentation.

Find the query behind an error#

Postgres records the query behind every error by default, so you don't need to change a setting.

To find the query that caused an error:

1. In the Postgres logs, open the error's entry.
2. Read its query subfield.

A failed insert produces a log like this:

1
duplicate key value violates unique constraint "example_pkey"
2
...
3
query: "insert into example (id) values (1), (1);"

Restore the defaults#

If the postgres role had its own value for either setting before you started, set it back to that value instead. To see the role's current settings, run select rolconfig from pg_roles where rolname = 'postgres';.

To return the postgres role to its default logging:

1. Reset both settings on the postgres role:

   1
   alter role "postgres" set "log_min_duration_statement" to default;
   2
   alter role "postgres" set "auto_explain.log_min_duration" to default;

2. Open new connections, or restart your app's connection pool, so the change applies.

The default plan threshold logs the query plan of each query that runs longer than 10 seconds. To stop logging query plans entirely, set the threshold to -1:

1
alter role "postgres" set "auto_explain.log_min_duration" to '-1';

The change applies to new connections.

How query logging works#

log_min_duration_statement records each statement that runs longer than its threshold, with its duration and nothing else, so it's lighter than logging query plans. It logs only statements that finish. A statement canceled by a timeout produces an error log instead, which records the query as described in Find the query behind an error.

Two cases log less than you might expect:

• If log_statement already logged a statement, its duration entry leaves the statement out. log_statement defaults to ddl, so a slow schema change, such as one in a migration, appears as two entries: the statement, and then its duration. To match the two, compare their session_id.
• With the extended query protocol, which most drivers and ORMs use, Postgres times the parse, bind, and execute steps separately. A query whose steps each stay under the threshold isn't logged, even when their total is over it.

When Postgres logs an event, it also records subfields, such as the error code. log_min_error_statement decides whether the query that caused the event is recorded too, in the query subfield. Postgres records the query when the event is as severe as the setting or more. The setting defaults to error. For the severity levels, see log_min_messages.