Closure
Closure
Home » Databases » PostgreSQL » How to find Missing Indexes in PostgreSQL

How to find Missing Indexes in PostgreSQL

How to find Missing Indexes in PostgreSQL
, , , Leave a comment
How to find Missing Indexes in PostgreSQL
Latest posts by Stratos Matzouranis (see all)

Those of us who come from the world of SQL Server, we are used to our favorites Missing Index DMVs (sys.dm_db_missing_index_details, sys.dm_db_missing_index_group_stats), which give us the ready-made CREATE INDEX command along with Avg User Impact and Equality/Inequality/Include columns.

In PostgreSQL, the default installation does not offer this information out of the box. However, by combining two powerful extensions, the pg_qualstats (which records the WHERE / JOIN clauses) and the pg_stat_statements (which records the SQL text), we can create a very useful query that suggests Indexes and generates DDL of it dynamically.

This Query:

  1. Locates the filtering fields (WHERE / JOIN).
  2. Automatically suggests Covering Indexes placing the remaining fields of SELECT in the INCLUDE.
  3. Excludes duplicate fields (does not put in INCLUDE fields that already exist in WHERE).
  4. Calculates the Impact Score.
  5. Checks the size of the table in GB and puts 🔥 HIGH Priority only when the table is >= 1GB and the Impact Score is high.
  6. Excludes tables that already have an Index in the same fields (WHERE DOES NOT EXIST).

Prerequisites:

For the script to work, two extensions are required:

  1. pg_stat_statements: Records the queries executed in the database.
  2. pg_qualstats: Records the WHERE, JOIN and HAVING clauses, offering the necessary metrics (executions, filtered rows).

In the example we will setup on RHEL Unix with PostgreSQL version 11:

sudo yum install -y pg_qualstats11 --nogpgcheck -y
sudo yum install -y postgresql11-contrib --nogpgcheck -y

Because in the example we have included a version that is now out of support, we can also do a manual setup:

curl -O https://yum-archive.postgresql.org/11/redhat/rhel-7-x86_64/pg_qualstats11-2.0.2-1.rhel7.x86_64.rpm
curl -O https://yum-archive.postgresql.org/11/redhat/rhel-7-x86_64/postgresql11-libs-11.9-1PGDG.rhel7.x86_64.rpm
curl -O https://yum-archive.postgresql.org/11/redhat/rhel-7-x86_64/postgresql11-contrib-11.9-1PGDG.rhel7.x86_64.rpm

sudo yum localinstall -y pg_qualstats11-2.0.2-1.rhel7.x86_64.rpm
sudo yum localinstall -y postgresql11-libs-11.9-1PGDG.rhel7.x86_64.rpm postgresql11-contrib-11.9-1PGDG.rhel7.x86_64.rpm

Next we need to declare the extentions in postgresql.conf:

su postgres
vi $PGDATA/postgresql.conf
shared_preload_libraries = 'pg_stat_statements, pg_qualstats'

Then we need to restart the service:

sudo systemctl restart postgresql-11

After PostgreSQL starts, we connect and enable the extensions:

sudo -u postgres psql -d db_test

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

CREATE EXTENSION IF NOT EXISTS pg_qualstats;

To see that they have been activated, run the following:

SHOW shared_preload_libraries;
SHOW pg_qualstats.enabled;
SHOW pg_qualstats.sample_rate;

Depending on the version, the default sample_rate is 0.01 so if we want to run something manually from the same session and have it record it, we definitely run it from the query session where we will run the query below:

SET pg_qualstats.sample_rate = 1;

If we want to reset the statistics, we run the following:

SELECT pg_qualstats_reset();

The query:

 
WITH qual_details AS (
    SELECT 
        q.queryid,
        q.qualid,
        q.lrelid::regclass::text AS table_name,
        q.lrelid AS table_oid,
        a.attname AS filter_column,
        q.occurences,
        q.nbfiltered
    FROM pg_qualstats q
    JOIN pg_attribute a ON a.attrelid = q.lrelid AND a.attnum = q.lattnum
    WHERE q.lrelid IS NOT NULL AND q.lrelid > 0
),
where_columns_per_qual AS (
    SELECT 
        qualid,
        table_name,
        table_oid,
        array_agg(DISTINCT filter_column) AS all_where_cols
    FROM qual_details
    GROUP BY qualid, table_name, table_oid
),
query_payload AS (
    SELECT 
        qd.qualid,
        qd.table_name,
        qd.table_oid,
        qd.filter_column,
        qd.occurences,
        qd.nbfiltered,
        ARRAY(
            SELECT DISTINCT a_select.attname
            FROM pg_stat_statements pss
            JOIN pg_attribute a_select ON a_select.attrelid = (qd.table_name::regclass)
            JOIN where_columns_per_qual w ON w.qualid = qd.qualid
            WHERE pss.queryid = qd.queryid
              AND a_select.attnum > 0 
              AND NOT a_select.attisdropped
              AND NOT (a_select.attname = ANY(w.all_where_cols))
              AND pss.query ILIKE '%' || a_select.attname || '%'
        ) AS payload_columns
    FROM qual_details qd
),
dedup_where AS (
    SELECT DISTINCT
        qualid,
        table_name,
        table_oid,
        filter_column,
        occurences,
        nbfiltered
    FROM query_payload
),
payload_flattened AS (
    SELECT DISTINCT
        qualid,
        unnest(payload_columns) AS inc_col
    FROM query_payload
),
include_aggregated AS (
    SELECT 
        qualid,
        string_agg(quote_ident(inc_col), ', ' ORDER BY quote_ident(inc_col)) AS include_list
    FROM payload_flattened
    GROUP BY qualid
),
calculated_metrics AS (
    SELECT 
        dw.qualid,
        dw.table_name,
        dw.table_oid,
        string_agg(dw.filter_column, ', ' ORDER BY dw.filter_column) AS where_columns,
        COALESCE(inc.include_list, '—') AS include_columns,
        MAX(dw.occurences) AS execution_count,
        MAX(dw.nbfiltered) AS total_rows_filtered,
        ROUND((pg_relation_size(dw.table_oid)::numeric / 1073741824.0), 2) AS table_size_gb,
        (MAX(dw.occurences) * MAX(dw.nbfiltered)) AS impact_score,
        
        FORMAT(
            'CREATE INDEX CONCURRENTLY idx_%s_%s%s ON %s (%s)%s;',
            dw.table_name,
            string_agg(dw.filter_column, '_' ORDER BY dw.filter_column),
            CASE WHEN inc.include_list IS NOT NULL THEN '_covering' ELSE '' END,
            dw.table_name,
            string_agg(quote_ident(dw.filter_column), ', ' ORDER BY quote_ident(dw.filter_column)),
            CASE 
                WHEN inc.include_list IS NOT NULL AND inc.include_list != ''
                THEN ' INCLUDE (' || inc.include_list || ')'
                ELSE ''
            END
        ) AS create_smart_index_command
    FROM dedup_where dw
    LEFT JOIN include_aggregated inc ON inc.qualid = dw.qualid
    GROUP BY dw.qualid, dw.table_name, dw.table_oid, inc.include_list
)
SELECT 
    cm.table_name,
    cm.where_columns,
    cm.include_columns,
    cm.execution_count,
    cm.total_rows_filtered,
    cm.table_size_gb,
    cm.impact_score,
    CASE 
        WHEN cm.impact_score > 100000 AND cm.table_size_gb >= 1.00 THEN '🔥 HIGH'
        WHEN cm.impact_score BETWEEN 1000 AND 100000 THEN '⚠️ MEDIUM'
        ELSE 'ℹ️ LOW'
    END AS priority,
    cm.create_smart_index_command
FROM calculated_metrics cm
-- ΕΞΑΙΡΕΣΗ: Μην προτείνεις αν υπάρχει ήδη Index
WHERE NOT EXISTS (
    SELECT 1 
    FROM pg_index i
    JOIN pg_class c ON c.oid = i.indrelid
    WHERE c.oid = cm.table_oid
      AND i.indkey[0] IN (
          SELECT attnum 
          FROM pg_attribute 
          WHERE attrelid = cm.table_oid 
            AND attname = ANY(string_to_array(cm.where_columns, ', '))
      )
)
ORDER BY cm.impact_score DESC, cm.execution_count DESC;
How to find Missing Indexes in PostgreSQL

Sources:

Share it

Leave a reply