---
id: 1352d80b-e103-4d57-8bbc-f08c7d6fc387
---

# Slow query performance in PostgreSQL
---

This incident type generally refers to situations where queries in a PostgreSQL database are taking longer than expected to execute. This can lead to slow application performance and a poor user experience. It may require a deep dive into the database schema and query execution plans to diagnose and optimize the slow queries. Common solutions may include indexing, rewriting the query, or tuning the database configuration.

### Parameters
```shell
export DATABASE_NAME="PLACEHOLDER"

export TABLE_NAME="PLACEHOLDER"

export IDLE="PLACEHOLDER"

export QUERY="PLACEHOLDER"

export VERSION="PLACEHOLDER"

export COLUMN_NAME="PLACEHOLDER"
```

## Debug

### Check current connections to the database
```shell
sudo -u postgres psql -c "SELECT * FROM pg_stat_activity;"
```

### Check the size of the database
```shell
sudo -u postgres psql -c "SELECT pg_size_pretty(pg_database_size('${DATABASE_NAME}'));"
```

### Check the size of individual tables
```shell
sudo -u postgres psql -c "SELECT pg_size_pretty(pg_total_relation_size('${TABLE_NAME}'));"
```

### Check for long-running queries
```shell
sudo -u postgres psql -c "SELECT pid, now() - query_start AS duration, query FROM pg_stat_activity WHERE query != '${IDLE}' AND now() - query_start > INTERVAL '5 minutes' ORDER BY duration DESC;"
```

### Explain a specific query to see its execution plan
```shell
sudo -u postgres psql -c "EXPLAIN ANALYZE ${QUERY};"
```

### Check the slow query log to identify frequently executed slow queries
```shell
sudo grep -i "duration" /var/log/postgresql/postgresql-${VERSION}-main.log | grep -v "duration: 0." | sort -u
```

## Repair

### Lack of proper indexing: If a large amount of data is being queried without proper indexing, the database engine may have to perform a lot of disk I/O to find the requested data. This can slow down query execution time significantly.
```shell
bash

#!/bin/bash



# Set variables

DB_NAME=${DATABASE_NAME}

TABLE_NAME=${TABLE_NAME}

COLUMN_NAME=${COLUMN_NAME}



# Check if indexing exists on column

INDEX_EXISTS=$(psql -U postgres -d $DB_NAME -c "\d+ $TABLE_NAME" | grep -o "$COLUMN_NAME" | wc -l)



# If indexing does not exist, create an index

if [ "$INDEX_EXISTS" -eq "0" ]; then

  psql -U postgres -d $DB_NAME -c "CREATE INDEX idx_$TABLE_NAME\_$COLUMN_NAME ON $TABLE_NAME ($COLUMN_NAME);"

  echo "Index created successfully."

else

  echo "Index already exists."

fi


```

This incident type generally refers to situations where queries in a PostgreSQL database are taking longer than expected to execute. This can lead to slow application performance and a poor user experience. It may require a deep dive into the database schema and query execution plans to diagnose and optimize the slow queries. Common solutions may include indexing, rewriting the query, or tuning the database configuration.


```shell
export DATABASE_NAME="PLACEHOLDER"

export TABLE_NAME="PLACEHOLDER"

export IDLE="PLACEHOLDER"

export QUERY="PLACEHOLDER"

export VERSION="PLACEHOLDER"

export COLUMN_NAME="PLACEHOLDER"
```


### Check current connections to the database

```shell
sudo -u postgres psql -c "SELECT * FROM pg_stat_activity;"
```

### Check the size of the database

```shell
sudo -u postgres psql -c "SELECT pg_size_pretty(pg_database_size('${DATABASE_NAME}'));"
```

### Check the size of individual tables

```shell
sudo -u postgres psql -c "SELECT pg_size_pretty(pg_total_relation_size('${TABLE_NAME}'));"
```

### Check for long-running queries

```shell
sudo -u postgres psql -c "SELECT pid, now() - query_start AS duration, query FROM pg_stat_activity WHERE query != '${IDLE}' AND now() - query_start > INTERVAL '5 minutes' ORDER BY duration DESC;"
```

### Explain a specific query to see its execution plan

```shell
sudo -u postgres psql -c "EXPLAIN ANALYZE ${QUERY};"
```

### Check the slow query log to identify frequently executed slow queries

```shell
sudo grep -i "duration" /var/log/postgresql/postgresql-${VERSION}-main.log | grep -v "duration: 0." | sort -u
```


### Lack of proper indexing: If a large amount of data is being queried without proper indexing, the database engine may have to perform a lot of disk I/O to find the requested data. This can slow down query execution time significantly.

```shell
bash

#!/bin/bash



# Set variables

DB_NAME=${DATABASE_NAME}

TABLE_NAME=${TABLE_NAME}

COLUMN_NAME=${COLUMN_NAME}



# Check if indexing exists on column

INDEX_EXISTS=$(psql -U postgres -d $DB_NAME -c "\d+ $TABLE_NAME" | grep -o "$COLUMN_NAME" | wc -l)



# If indexing does not exist, create an index

if [ "$INDEX_EXISTS" -eq "0" ]; then

  psql -U postgres -d $DB_NAME -c "CREATE INDEX idx_$TABLE_NAME\_$COLUMN_NAME ON $TABLE_NAME ($COLUMN_NAME);"

  echo "Index created successfully."

else

  echo "Index already exists."

fi


```


Slow query performance in PostgreSQL

Overview

Parameters

Debug

Check current connections to the database

Check the size of the database

Check the size of individual tables

Check for long-running queries

Explain a specific query to see its execution plan

Check the slow query log to identify frequently executed slow queries

Repair

Lack of proper indexing: If a large amount of data is being queried without proper indexing, the database engine may have to perform a lot of disk I/O to find the requested data. This can slow down query execution time significantly.

Learn more

Related Runbooks

Support