Profiling Queries

Before deciding to cache a query, it's worth investigating the performance impact each query has on your application.

One method for doing so:

Enabling metrics

You can use the pg_stat_statements extension (opens in a new tab) to retrieve detailed information about the queries running against your Postgres instance.

Connect to your database via the shell. Run the following command to see if pg_stat_statements is installed.

SELECT calls, query FROM pg_stat_statements LIMIT 1;

If an error is returned, enable pg_stat_statments with the following command:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

⚠️

In some environments, the pg_stat_statements extension may not be available. In that case, run ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements'; and restart your Postgres instance before re-running the CREATE EXTENSION command.

Analyzing per-query metrics

ReadySet can cache many SELECT queries.

To find SELECT queries with the highest latency, run:

SELECT query, calls, total_exec_time, mean_exec_time from pg_stat_statements WHERE query ILIKE '%SELECT%' order by mean_exec_time DESC;

Similarly, ReadySet can be used to offload high-impact queries to improve throughput.

To find the most frequently-run SELECT queries, run:

SELECT query, calls, total_exec_time, mean_exec_time from pg_stat_statements WHERE query ILIKE '%SELECT%' order by calls DESC;

To find queries that cause the most total load on the database, run:

SELECT query, calls, total_exec_time, mean_exec_time from pg_stat_statements WHERE query ILIKE '%SELECT%' order by total_exec_time DESC;

Connect an application via an ORM View query metrics with the database's slow query log