Profiling Queries

You can use the pg_stat_statements extension 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;

To enable Readyset metrics, start Readyset with the following options:

• --prometheus-metrics
• --metrics-address

To include query-specific execution metrics, also pass:

• --query-log-mode=verbose

You can access Readyset metrics at <metrics address>/metrics, where the metrics address is defined by the --metrics-address option (default: 0.0.0.0:6034/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;

Readyset metrics are formatted for easy integration with Prometheus. However, the quickest way to examine per-query metrics is to use a simple metrics utility written in Python that queries the metrics endpoint and displays latencies for queries received by Readyset.

1. Download the metrics utility and its requirements.txt:

curl -L -O "https://readyset.io/quickstart/metrics.py"
curl -L -O "https://readyset.io/quickstart/requirements.txt"

2. Install dependencies for the utility:

pip3 install -r requirements.txt

3. Set the METRICS_HOST environment variable to the IP address/hostname portion of --metrics-address:

export METRICS_HOST="<metrics-host>"

4. Run the script with

python3 metrics.py --host=${METRICS_HOST}

5. You can filter the output of this script to show only queries displayed in SHOW PROXIED QUERIES or SHOW CACHES by passing the --filter-queries flag and piping the output of those Readyset commands into the script like so:

PGPASSWORD=noria psql --host=127.0.0.1 --port=5433 --username=postgres --dbname=noria -c "SHOW CACHES" | python3 metrics.py --filter-queries