Analyze PostgreSQL query performance

While working on the following Monday request:

https://customoffice.monday.com/boards/2715683423/pulses/12277523190

users reported slow load times for appendices.

Some servers responded almost immediately, while others were significantly slower. After investigating several possible causes, the issue appeared to be related to the database query being executed.

The problem was identified by using a dump from one of the affected servers and enabling query logging in the local PostgreSQL Docker container. This made it possible to see the exact query being executed in the logs.

Once the query was visible, the $1, $2, etc. parameters could be replaced manually with the actual values from the log. The query could then be run locally using:

EXPLAIN ANALYZE SELECT ...

This returns both the estimated query cost and the actual execution time.

Because the query was a SELECT query in this case, it was safe to run locally and on the affected server with EXPLAIN ANALYZE. This made it possible to compare the local result with the result from the server.

The workflow was:

1. Reproduce or inspect the query locally.
2. Replace the $n parameters with actual values from the logs.
3. Run the query locally with EXPLAIN ANALYZE.
4. Make local query or index changes.
5. Run the updated query again locally.
6. Copy the query and run it on the affected server using EXPLAIN ANALYZE.
7. Compare whether the change improved the execution plan or execution time.

However, when adding an index locally, remember that the index only exists in the local database. It will not affect the affected server until the index change has been released and applied there.

Enable host networking in Docker Desktop

To allow a local psql client to connect to the PostgreSQL container, host networking must be enabled in Docker Desktop.

Open Docker Desktop and go to:

Settings -> Resources -> Network

Enable:

Enable host networking

Docker describes this setting as:

Host networking allows containers that are started with --net=host to use localhost to connect to TCP and UDP services on the host. It will automatically allow software on the host to use localhost to connect to TCP and UDP services in the container.

Important security note

Disable this setting again after use.

Host networking can expose more than intended between the host machine and containers, so it should only be enabled while actively needed for debugging.

Install the PostgreSQL client locally

If psql is not already installed locally, install the PostgreSQL client.

For example:

sudo apt install postgresql-client-16

The version may have changed by the time this guide is read. Install the currently relevant PostgreSQL client version for your environment.

Start the local development container

Make sure the local development container is running before trying to connect with psql.

Otherwise, you may see an error like this:

jonasq8@jonasq8:~$ psql postgres://customoffice@127.0.0.1/anlaegnord
psql: error: connection to server at "127.0.0.1", port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?

This means that nothing is currently accepting PostgreSQL connections on 127.0.0.1:5432.

Connect to the local PostgreSQL database

Use psql to connect to the relevant local customer database.

Example:

psql postgres://postgres@127.0.0.1/anlaegnord

In this example, anlaegnord is the local customer database used for testing.

Adjust the database name and user depending on the local setup.

Find the query in the PostgreSQL logs

With query logging enabled in the local PostgreSQL Docker container, locate the slow query in the logs.

You can enabled logging with the following command within the psql cli:

ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_duration = true;
SELECT pg_reload_conf();

Then all queries will be display with the docker desktop log viewer.

The query may contain placeholders such as:

$1
$2
$3

These are parameter placeholders. Replace them with the actual values shown in the log output.

For example, a logged query might contain:

WHERE work_from_timestamp >= $1
AND work_from_timestamp <= $2

After replacing the parameters, it might look like:

WHERE work_from_timestamp >= '2026-05-31 22:00:00+00'
AND work_from_timestamp <= '2026-06-01 22:00:00+00'

Run the query with EXPLAIN ANALYZE

Inside the psql CLI, add EXPLAIN ANALYZE in front of the query.

Example:

EXPLAIN ANALYZE
SELECT ...

This executes the query and returns the query plan, including estimated cost and actual execution time.

Example output

Example output:

Nested Loop  (cost=0.84..198.74 rows=1 width=905) (actual time=2.923..54.437 rows=61 loops=1)
  ->  Index Scan using tasks_task_work_from_timestamp_986c77c5 on tasks_task  (cost=0.43..173.43 rows=3 width=905) (actual time=2.157..40.083 rows=63 loops=1)
        Index Cond: ((work_from_timestamp >= '2026-05-31 22:00:00+00'::timestamp with time zone) AND (work_from_timestamp <= '2026-06-01 22:00:00+00'::timestamp with time zone))
        Filter: (completed AND (version_end_date IS NULL))
        Rows Removed by Filter: 1258
  ->  Index Scan using orders_order_id_66f898cd_like on orders_order j0  (cost=0.42..8.44 rows=1 width=37) (actual time=0.225..0.225 rows=1 loops=63)
        Index Cond: (((id)::text = (tasks_task.order_id)::text) AND (id IS NOT NULL))
        Filter: (version_end_date IS NULL)
Planning Time: 35.672 ms
Execution Time: 54.587 ms

How to read the output

The first line describes the overall operation:

Nested Loop  (cost=0.84..198.74 rows=1 width=905) (actual time=2.923..54.437 rows=61 loops=1)

Important parts:

Field	Meaning
cost=0.84..198.74	PostgreSQL’s estimated cost for the operation
rows=1	Estimated number of rows
width=905	Estimated average row size in bytes
actual time=2.923..54.437	Actual measured execution time for this step
rows=61	Actual number of rows returned
loops=1	Number of times this operation was executed

The time values are intervals.

For example:

actual time=2.923..54.437

means:

Value	Meaning
2.923 ms	Time until the first row was returned
54.437 ms	Time until the operation finished

This pattern is repeated for each part of the query plan, including subqueries, joins, index scans, sequential scans, filters, and nested loops.

Things to pay attention to

When reviewing the output, pay special attention to:

Execution time

Execution Time: 54.587 ms

This is the total time PostgreSQL spent executing the query.

Planning time

Planning Time: 35.672 ms

This is the time PostgreSQL spent planning how to execute the query before actually running it.

Rows removed by filter

Rows Removed by Filter: 1258

This can indicate that PostgreSQL is scanning many rows and discarding most of them afterward.

Estimated rows vs. actual rows

Example:

rows=3

versus:

actual rows=63

Large differences between estimated and actual row counts can indicate that PostgreSQL has poor statistics or that the query planner is making bad assumptions.

Repeated loops

Example:

loops=63

If a slow operation is executed many times, it can significantly increase the total execution time.

Summary

The debugging flow is:

1. Use a database dump from the affected server.

2. Start the local development PostgreSQL container.

3. Enable query logging.

4. Find the executed query in the logs.

5. Replace $1, $2, etc. with the actual parameter values.

6. Connect to the local database using psql.

7. Run the query with EXPLAIN ANALYZE.

8. Review cost, actual time, row counts, filters, and loops.

9. Use the output to identify the expensive part of the query.

Remember to disable Docker host networking again when finished.