Πώς βρίσκουμε τα Missing Indexes σε PostgreSQL

• Σχετικά με
• τελευταίες δημοσιεύσεις

Stratos Matzouranis

Το ταξίδι άρχισε το 2008 όπου ξεκίνησα να εργάζομαι επίσημα στον τομέα της τεχνολογίας πληροφορικής (IT). Ξεκινώντας το πρώτο εξάμηνο της σχολής κατάλαβα μια ιδιαίτερη έλξη προς τις βάσεις δεδομένων και τους αυτοματισμούς.Έχω ασχοληθεί με βάσεις δεδομένων όπως Microsoft SQL Server / Oracle Database, ανάλυση δεδομένων και αυτοματισμούς με τη χρήση γραμμής εντολών (CLI), Visual Basic for Applications και Python. Μέσα από τα χρόνια εμπειρίας εξέλιξα αυτές τις δυνατότητες ώστε να μπορώ να κάνω τη ζωή μου πιο εύκολη. Για μένα ο σκοπός του κάθε IT guy αλλά και κάθε υπαλλήλου γραφείου είναι το να έχει τις γνώσεις ώστε μέσω εργαλείων να μπορεί να δουλεύει λίγο αλλά να παράγει πολύ. Μέσα από αυτό τον ιστότοπο του DataPlatform.gr προσπαθώ να προσφέρω γνώσεις και να προτείνω λύσεις σε καθημερινά προβλήματα.

Πιστοποιήσεις:

Τελευταίες δημοσιεύσεις από Stratos Matzouranis (Προβολή όλων)

• Πώς βρίσκουμε τα Missing Indexes σε PostgreSQL - 11 Σεπτέμβριος 2026
• Πώς κάνουμε Performance Testing στη PostgreSQL με το pgbench - 9 Σεπτέμβριος 2026
• Πώς βρίσκουμε με ένα query τα alerts σε PostgreSQL μέσα από τα Plain Text Logs - 7 Σεπτέμβριος 2026

Όσοι προερχόμαστε από τον κόσμο του SQL Server, έχουμε συνηθίσει στα αγαπημένα μας Missing Index DMVs (sys.dm_db_missing_index_details, sys.dm_db_missing_index_group_stats), τα οποία μας δίνουν έτοιμο το CREATE INDEX command μαζί με το Avg User Impact και το Equality/Inequality/Include columns.

Στην PostgreSQL, η default εγκατάσταση δεν προσφέρει έτοιμη αυτή τη πληροφορία. Ωστόσο, συνδυάζοντας δύο ισχυρά extensions, το pg_qualstats (που καταγράφει τα WHERE / JOIN clauses) και το pg_stat_statements (που καταγράφει το SQL text), μπορούμε να φτιάξουμε ένα πολύ χρήσιμο query που μας προτείνει Indexes και κάνει Generate το DDL του δυναμικά.

Το Query αυτό:

1. Εντοπίζει τα πεδία φιλτραρίσματος (WHERE / JOIN).
2. Προτείνει αυτόματα Covering Indexes τοποθετώντας τα υπόλοιπα πεδία του SELECT στο INCLUDE.
3. Αποκλείει διπλότυπα πεδία (δεν βάζει στο INCLUDE πεδία που υπάρχουν ήδη στο WHERE).
4. Υπολογίζει το Impact Score.
5. Ελέγχει το μέγεθος του πίνακα σε GB και βάζει 🔥 HIGH Priority μόνο όταν ο πίνακας είναι >= 1GB και το Impact Score είναι υψηλό.
6. Εξαιρεί πίνακες που έχουν ήδη Index στα ίδια πεδία (WHERE NOT EXISTS).

Προαπαιτούμενα:

Για να μπορέσει να λειτουργήσει το script, απαιτούνται δύο extensions:

1. pg_stat_statements: Καταγράφει τα queries που εκτελούνται στη βάση.
2. pg_qualstats: Καταγράφει τα WHERE, JOIN και HAVING clauses, προσφέροντας τα απαραίτητα metrics (executions, filtered rows).

Στο παράδειγμα θα κάνουμε setup σε RHEL Unix σε έκδοση PostgreSQL 11:

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

Επειδή στο παράδειγμα έχουμε βάλει μια έκδοση που είναι πλέον out of support μπορούμε να κάνουμε και 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

Στη συνέχεια πρέπει να δηλώσουμε τα extentions στο postgresql.conf:

su postgres
vi $PGDATA/postgresql.conf

shared_preload_libraries = 'pg_stat_statements, pg_qualstats'

Έπειτα πρέπει να κάνουμε restart το service:

sudo systemctl restart postgresql-11

Αφού σηκωθεί η PostgreSQL συνδεόμαστε και ενεργοποιούμε τα extentions:

sudo -u postgres psql -d db_test

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

CREATE EXTENSION IF NOT EXISTS pg_qualstats;

Για να δούμε ότι έχουν ενεργοποιηθεί τρέχουμε τα παρακάτω:

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

Ανάλογα την έκδοση το default sample_rate είναι 0.01 οπότε αν θέλουμε να τρέξουμε κάτι με το χέρι απο το ίδιο session και να το καταγράψει σίγουρα τρέχουμε απο το query session που θα τρέξουμε το query το παρακάτω:

SET pg_qualstats.sample_rate = 1;

Αν θέλουμε να κάνουμε reset τα στατιστικά τρέχουμε το εξής:

SELECT pg_qualstats_reset();

Το 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;

Πηγές:

• PostgreSQL 18.4 Documentation