---
id: b2672139-3a63-4343-ab80-2d30bdab26d6
---

# Slow Performance due to Full Table Scans on Large PostgreSQL Tables
---

This incident type refers to situations where PostgreSQL queries are performing full table scans on large tables, leading to slow performance. This occurs when the database management system has to scan the entire table to locate the required data, which can be a time-consuming process. The issue can arise due to a variety of reasons, such as missing or outdated indexes, complex queries, or inefficient query design. To resolve the issue, optimizing the queries on large tables is necessary.

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

export TABLE_NAME="PLACEHOLDER"

export QUERY="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export COLUMN_NAME="PLACEHOLDER"

export INDEX_NAME="PLACEHOLDER"

export PARTITION_INTERVAL="PLACEHOLDER"
```

## Debug

### Check PostgreSQL version
```shell
psql -U ${USERNAME} -c "SELECT version();"
```

### Get list of all tables in the database
```shell
psql -U ${USERNAME} -c "\dt"
```

### Check table size
```shell
psql -U ${USERNAME} -c "SELECT pg_size_pretty(pg_total_relation_size('${TABLE_NAME}'))"
```

### Check if table has an index
```shell
psql -U ${USERNAME} -c "SELECT indexname, indexdef FROM pg_indexes WHERE tablename='${TABLE_NAME}'"
```

### Check query plan for a specific query
```shell
psql -U ${USERNAME} -c "EXPLAIN ANALYZE ${QUERY};"
```

### Check for long running queries
```shell
psql -U ${USERNAME} -c "SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE datname='${DATABASE_NAME}' AND state='active' ORDER BY query_start desc;"
```

## Repair

### Adding appropriate indexes to the large tables can help the database management system locate the required data more efficiently, thereby avoiding full table scans.
```shell
#!/bin/bash



# Define variables

DB_NAME=${DATABASE_NAME}

TABLE_NAME=${TABLE_NAME}

INDEX_NAME=${INDEX_NAME}



# Check if index exists

index_status=$(psql -U postgres -d $DB_NAME -tAc "SELECT EXISTS(SELECT 1 FROM pg_indexes WHERE tablename = '$TABLE_NAME' AND indexname = '$INDEX_NAME')")

if [ "$index_status" = "f" ]; then

  # If index does not exist, create it

  psql -U postgres -d $DB_NAME -c "CREATE INDEX $INDEX_NAME ON $TABLE_NAME (${COLUMN_NAME})"

  echo "Index $INDEX_NAME created on table $TABLE_NAME"

else

  echo "Index $INDEX_NAME already exists on table $TABLE_NAME"

fi


```

### Partitioning large tables into smaller ones based on specific criteria, such as date or region, can help to reduce the amount of data scanned.
```shell
bash

#!/bin/bash



# Set variables

DB_NAME=${DATABASE_NAME}

TABLE_NAME=${TABLE_NAME}

PARTITION_COLUMN=${COLUMN_NAME}

PARTITION_INTERVAL=${PARTITION_INTERVAL}



# Create new partitions

psql -d $DB_NAME -c "CREATE TABLE ${TABLE_NAME}_new PARTITION OF ${TABLE_NAME} FOR VALUES FROM ('2000-01-01') TO ('${PARTITION_INTERVAL}');"

psql -d $DB_NAME -c "CREATE TABLE ${TABLE_NAME}_old PARTITION OF ${TABLE_NAME} FOR VALUES FROM ('${PARTITION_INTERVAL}') TO (MAXVALUE);"



# Move data from old table to new partition

psql -d $DB_NAME -c "INSERT INTO ${TABLE_NAME}_new SELECT * FROM ${TABLE_NAME} WHERE ${PARTITION_COLUMN} >= '2000-01-01' AND ${PARTITION_COLUMN} < '${PARTITION_INTERVAL}';"



# Drop old table

psql -d $DB_NAME -c "DROP TABLE ${TABLE_NAME};"



# Rename new table to old table name

psql -d $DB_NAME -c "ALTER TABLE ${TABLE_NAME}_new RENAME TO ${TABLE_NAME};"


```

This incident type refers to situations where PostgreSQL queries are performing full table scans on large tables, leading to slow performance. This occurs when the database management system has to scan the entire table to locate the required data, which can be a time-consuming process. The issue can arise due to a variety of reasons, such as missing or outdated indexes, complex queries, or inefficient query design. To resolve the issue, optimizing the queries on large tables is necessary.


```shell
export USERNAME="PLACEHOLDER"

export TABLE_NAME="PLACEHOLDER"

export QUERY="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export COLUMN_NAME="PLACEHOLDER"

export INDEX_NAME="PLACEHOLDER"

export PARTITION_INTERVAL="PLACEHOLDER"
```


### Check PostgreSQL version

```shell
psql -U ${USERNAME} -c "SELECT version();"
```

### Get list of all tables in the database

```shell
psql -U ${USERNAME} -c "\dt"
```

### Check table size

```shell
psql -U ${USERNAME} -c "SELECT pg_size_pretty(pg_total_relation_size('${TABLE_NAME}'))"
```

### Check if table has an index

```shell
psql -U ${USERNAME} -c "SELECT indexname, indexdef FROM pg_indexes WHERE tablename='${TABLE_NAME}'"
```

### Check query plan for a specific query

```shell
psql -U ${USERNAME} -c "EXPLAIN ANALYZE ${QUERY};"
```

### Check for long running queries

```shell
psql -U ${USERNAME} -c "SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE datname='${DATABASE_NAME}' AND state='active' ORDER BY query_start desc;"
```


### Adding appropriate indexes to the large tables can help the database management system locate the required data more efficiently, thereby avoiding full table scans.

```shell
#!/bin/bash



# Define variables

DB_NAME=${DATABASE_NAME}

TABLE_NAME=${TABLE_NAME}

INDEX_NAME=${INDEX_NAME}



# Check if index exists

index_status=$(psql -U postgres -d $DB_NAME -tAc "SELECT EXISTS(SELECT 1 FROM pg_indexes WHERE tablename = '$TABLE_NAME' AND indexname = '$INDEX_NAME')")

if [ "$index_status" = "f" ]; then

  # If index does not exist, create it

  psql -U postgres -d $DB_NAME -c "CREATE INDEX $INDEX_NAME ON $TABLE_NAME (${COLUMN_NAME})"

  echo "Index $INDEX_NAME created on table $TABLE_NAME"

else

  echo "Index $INDEX_NAME already exists on table $TABLE_NAME"

fi


```

### Partitioning large tables into smaller ones based on specific criteria, such as date or region, can help to reduce the amount of data scanned.

```shell
bash

#!/bin/bash



# Set variables

DB_NAME=${DATABASE_NAME}

TABLE_NAME=${TABLE_NAME}

PARTITION_COLUMN=${COLUMN_NAME}

PARTITION_INTERVAL=${PARTITION_INTERVAL}



# Create new partitions

psql -d $DB_NAME -c "CREATE TABLE ${TABLE_NAME}_new PARTITION OF ${TABLE_NAME} FOR VALUES FROM ('2000-01-01') TO ('${PARTITION_INTERVAL}');"

psql -d $DB_NAME -c "CREATE TABLE ${TABLE_NAME}_old PARTITION OF ${TABLE_NAME} FOR VALUES FROM ('${PARTITION_INTERVAL}') TO (MAXVALUE);"



# Move data from old table to new partition

psql -d $DB_NAME -c "INSERT INTO ${TABLE_NAME}_new SELECT * FROM ${TABLE_NAME} WHERE ${PARTITION_COLUMN} >= '2000-01-01' AND ${PARTITION_COLUMN} < '${PARTITION_INTERVAL}';"



# Drop old table

psql -d $DB_NAME -c "DROP TABLE ${TABLE_NAME};"



# Rename new table to old table name

psql -d $DB_NAME -c "ALTER TABLE ${TABLE_NAME}_new RENAME TO ${TABLE_NAME};"


```


Slow Performance due to Full Table Scans on Large PostgreSQL Tables

Overview

Parameters

Debug

Check PostgreSQL version

Get list of all tables in the database

Check table size

Check if table has an index

Check query plan for a specific query

Check for long running queries

Repair

Adding appropriate indexes to the large tables can help the database management system locate the required data more efficiently, thereby avoiding full table scans.

Partitioning large tables into smaller ones based on specific criteria, such as date or region, can help to reduce the amount of data scanned.

Learn more

Related Runbooks

Support