{"id":6064,"date":"2026-09-11T07:00:00","date_gmt":"2026-09-11T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=6064"},"modified":"2026-09-11T11:11:52","modified_gmt":"2026-09-11T08:11:52","slug":"pos-vriskoyme-ta-missing-indexes-se-postgresql","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-vriskoyme-ta-missing-indexes-se-postgresql\/","title":{"rendered":"How to find Missing Indexes in PostgreSQL"},"content":{"rendered":"

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

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

This Query:<\/p>\n\n\n\n

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

    <\/p>\n\n\n\n

    Prerequisites:<\/h2>\n\n\n\n

    For the script to work, two extensions are required:<\/p>\n\n\n\n

      \n
    1. pg_stat_statements<\/strong>: Records the queries executed in the database.<\/li>\n\n\n\n
    2. pg_qualstats<\/strong>: Records the WHERE<\/code>, JOIN<\/code> and HAVING<\/code> clauses, offering the necessary metrics (executions, filtered rows).<\/li>\n<\/ol>\n\n\n\n

      In the example we will setup on RHEL Unix with PostgreSQL version 11:<\/p>\n\n\n\n

      sudo yum install -y pg_qualstats11 --nogpgcheck -y\nsudo yum install -y postgresql11-contrib --nogpgcheck -y<\/pre>\n\n\n\n
      Because in the example we have included a version that is now out of support, we can also do a manual setup:<\/p>\n\n\n\n
      curl -O https:\/\/yum-archive.postgresql.org\/11\/redhat\/rhel-7-x86_64\/pg_qualstats11-2.0.2-1.rhel7.x86_64.rpm\ncurl -O https:\/\/yum-archive.postgresql.org\/11\/redhat\/rhel-7-x86_64\/postgresql11-libs-11.9-1PGDG.rhel7.x86_64.rpm\ncurl -O https:\/\/yum-archive.postgresql.org\/11\/redhat\/rhel-7-x86_64\/postgresql11-contrib-11.9-1PGDG.rhel7.x86_64.rpm\n\nsudo yum localinstall -y pg_qualstats11-2.0.2-1.rhel7.x86_64.rpm\nsudo yum localinstall -y postgresql11-libs-11.9-1PGDG.rhel7.x86_64.rpm postgresql11-contrib-11.9-1PGDG.rhel7.x86_64.rpm<\/pre>\n\n\n\n
      Next we need to declare the extentions in postgresql.conf<\/code>:<\/p>\n\n\n\n
      su postgres\nvi $PGDATA\/postgresql.conf<\/pre>\n\n\n\n
      shared_preload_libraries = 'pg_stat_statements, pg_qualstats'<\/code><\/pre>\n\n\n\n
      Then we need to restart the service:<\/p>\n\n\n\n
      sudo systemctl restart postgresql-11<\/pre>\n\n\n\n
      After PostgreSQL is up, we connect and enable the extensions:<\/p>\n\n\n\n
      sudo -u postgres psql -d db_test\n\nCREATE EXTENSION IF NOT EXISTS pg_stat_statements;\n\nCREATE EXTENSION IF NOT EXISTS pg_qualstats;\n<\/pre>\n\n\n\n
      To see that they have been activated, run the following:<\/p>\n\n\n\n
      SHOW shared_preload_libraries;\nSHOW pg_qualstats.enabled;\nSHOW pg_qualstats.sample_rate;<\/pre>\n\n\n\n
      Depending on the version, the default sample_rate <\/code>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:<\/p>\n\n\n\n
      SET pg_qualstats.sample_rate = 1;<\/pre>\n\n\n\n
      If we want to reset the statistics, we run the following:<\/p>\n\n\n\n
      SELECT pg_qualstats_reset();<\/pre>\n\n\n\n
      <\/p>\n\n\n\n
      The query:<\/h2>\n\n\n\n
       \nWITH qual_details AS (\n    SELECT \n        q.queryid,\n        q.qualid,\n        q.lrelid::regclass::text AS table_name,\n        q.lrelid AS table_oid,\n        a.attname AS filter_column,\n        q.occurences,\n        q.nbfiltered\n    FROM pg_qualstats q\n    JOIN pg_attribute a ON a.attrelid = q.lrelid AND a.attnum = q.lattnum\n    WHERE q.lrelid IS NOT NULL AND q.lrelid > 0\n),\nwhere_columns_per_qual AS (\n    SELECT \n        qualid,\n        table_name,\n        table_oid,\n        array_agg(DISTINCT filter_column) AS all_where_cols\n    FROM qual_details\n    GROUP BY qualid, table_name, table_oid\n),\nquery_payload AS (\n    SELECT \n        qd.qualid,\n        qd.table_name,\n        qd.table_oid,\n        qd.filter_column,\n        qd.occurences,\n        qd.nbfiltered,\n        ARRAY(\n            SELECT DISTINCT a_select.attname\n            FROM pg_stat_statements pss\n            JOIN pg_attribute a_select ON a_select.attrelid = (qd.table_name::regclass)\n            JOIN where_columns_per_qual w ON w.qualid = qd.qualid\n            WHERE pss.queryid = qd.queryid\n              AND a_select.attnum > 0 \n              AND NOT a_select.attisdropped\n              AND NOT (a_select.attname = ANY(w.all_where_cols))\n              AND pss.query ILIKE '%' || a_select.attname || '%'\n        ) AS payload_columns\n    FROM qual_details qd\n),\ndedup_where AS (\n    SELECT DISTINCT\n        qualid,\n        table_name,\n        table_oid,\n        filter_column,\n        occurences,\n        nbfiltered\n    FROM query_payload\n),\npayload_flattened AS (\n    SELECT DISTINCT\n        qualid,\n        unnest(payload_columns) AS inc_col\n    FROM query_payload\n),\ninclude_aggregated AS (\n    SELECT \n        qualid,\n        string_agg(quote_ident(inc_col), ', ' ORDER BY quote_ident(inc_col)) AS include_list\n    FROM payload_flattened\n    GROUP BY qualid\n),\ncalculated_metrics AS (\n    SELECT \n        dw.qualid,\n        dw.table_name,\n        dw.table_oid,\n        string_agg(dw.filter_column, ', ' ORDER BY dw.filter_column) AS where_columns,\n        COALESCE(inc.include_list, '\u2014') AS include_columns,\n        MAX(dw.occurences) AS execution_count,\n        MAX(dw.nbfiltered) AS total_rows_filtered,\n        ROUND((pg_relation_size(dw.table_oid)::numeric \/ 1073741824.0), 2) AS table_size_gb,\n        (MAX(dw.occurences) * MAX(dw.nbfiltered)) AS impact_score,\n        \n        FORMAT(\n            'CREATE INDEX CONCURRENTLY idx_%s_%s%s ON %s (%s)%s;',\n            dw.table_name,\n            string_agg(dw.filter_column, '_' ORDER BY dw.filter_column),\n            CASE WHEN inc.include_list IS NOT NULL THEN '_covering' ELSE '' END,\n            dw.table_name,\n            string_agg(quote_ident(dw.filter_column), ', ' ORDER BY quote_ident(dw.filter_column)),\n            CASE \n                WHEN inc.include_list IS NOT NULL AND inc.include_list != ''\n                THEN ' INCLUDE (' || inc.include_list || ')'\n                ELSE ''\n            END\n        ) AS create_smart_index_command\n    FROM dedup_where dw\n    LEFT JOIN include_aggregated inc ON inc.qualid = dw.qualid\n    GROUP BY dw.qualid, dw.table_name, dw.table_oid, inc.include_list\n)\nSELECT \n    cm.table_name,\n    cm.where_columns,\n    cm.include_columns,\n    cm.execution_count,\n    cm.total_rows_filtered,\n    cm.table_size_gb,\n    cm.impact_score,\n    CASE \n        WHEN cm.impact_score > 100000 AND cm.table_size_gb >= 1.00 THEN '\ud83d\udd25 HIGH'\n        WHEN cm.impact_score BETWEEN 1000 AND 100000 THEN '\u26a0\ufe0f MEDIUM'\n        ELSE '\u2139\ufe0f LOW'\n    END AS priority,\n    cm.create_smart_index_command\nFROM calculated_metrics cm\n-- \u0395\u039e\u0391\u0399\u03a1\u0395\u03a3\u0397: \u039c\u03b7\u03bd \u03c0\u03c1\u03bf\u03c4\u03b5\u03af\u03bd\u03b5\u03b9\u03c2 \u03b1\u03bd \u03c5\u03c0\u03ac\u03c1\u03c7\u03b5\u03b9 \u03ae\u03b4\u03b7 Index\nWHERE NOT EXISTS (\n    SELECT 1 \n    FROM pg_index i\n    JOIN pg_class c ON c.oid = i.indrelid\n    WHERE c.oid = cm.table_oid\n      AND i.indkey[0] IN (\n          SELECT attnum \n          FROM pg_attribute \n          WHERE attrelid = cm.table_oid \n            AND attname = ANY(string_to_array(cm.where_columns, ', '))\n      )\n)\nORDER BY cm.impact_score DESC, cm.execution_count DESC;<\/pre>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      <\/p>\n\n\n\n
      Sources:<\/h2>\n\n\n\n