Statistiche dettagliate in PostgreSQL con pg_qualstats

pg_qualstats è un’estensione per PostgreSQL che serve a raccogliere statistiche dettagliate sulle condizioni (qualifiers) usate nelle query SQL, in particolare nelle clausole WHERE, JOIN, e simili.

Il suo obiettivo è aiutare a identificare condizioni che potrebbero beneficiare di un indice, o a capire come il planner utilizza i filtri per ottimizzare le query monitorando:

• Quali colonne vengono usate nei predicati (WHERE, ON, ecc.)
• Quali operatori vengono applicati (es. =, <, >, LIKE, ecc.)
• Quante volte una certa condizione viene eseguita
• Quante righe vengono filtrate da quella condizione
• La selettività stimata vs quella effettiva (utile per diagnosticare errori di stima del planner)

In questo articolo vediamo come installarla e usarla.

Se siete su Debian:

$ sudo apt install postgresql-17-pg-qualstats

Io ho la versione 17, voi mettete la vostra.

Se usate Docker, come il sottoscritto:

docker exec -it nome_container bash

# apt-get update
# apt-get install -y postgresql-17-pg-qualstats

In entrambi i casi dobbiamo poi aggiungere la riga qua sotto al file di configurazione (che dovrebbe essere tipo /var/lib/postgresql/data/postgresql.conf):

shared_preload_libraries = 'pg_qualstats'

Riavviate il servizio o il container.

Dopo di che connettetevi al db ed installate l'estensione:

# CREATE EXTENSION pg_qualstats;
CREATE EXTENSION

# SELECT * FROM pg_extension WHERE extname = 'pg_qualstats';
  oid   |   extname    | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
--------+--------------+----------+--------------+----------------+------------+-----------+--------------
 387463 | pg_qualstats |       10 |         2200 | f              | 2.1.3      |           |
(1 row)

Se è tutto ok, possiamo interrogare le statistiche (ovviamente prima lanciate delle query e/o navigate sulla vostra piattaforma):

# SELECT
  c.relname AS table_name,
  a.attname AS column_name,
  o.oprname AS operator,
  q.execution_count,
  q.nbfiltered,
  q.mean_err_estimate_ratio,
  q.mean_err_estimate_num,
  q.constvalue
FROM pg_qualstats q
LEFT JOIN pg_class c ON q.lrelid = c.oid
LEFT JOIN pg_attribute a ON q.lattnum = a.attnum AND a.attrelid = c.oid
LEFT JOIN pg_operator o ON q.opno = o.oid
ORDER BY q.execution_count DESC
LIMIT 20;

       table_name       |  column_name  | operator | execution_count | nbfiltered | mean_err_estimate_ratio | mean_err_estimate_num |  constvalue
------------------------+---------------+----------+-----------------+------------+-------------------------+-----------------------+---------------
 certificati_contents   | content       | =        |            3018 |       3018 |                       1 |                     1 | 'corsi'::text
 certificati_contents   | id_content    | =        |            3018 |       3018 |                       1 |                     1 | 94::bigint
 certificati_contents   | id_content    | =        |            3018 |       3016 |                       0 |                     0 | 89::bigint
 certificati_contents   | content       | =        |            3018 |       3016 |                       0 |                     0 | 'corsi'::text
 utenti_ecm_professioni | id_utente     | =        |              90 |         89 |                       0 |                     0 | 5562::integer
 softskills_utenti      | status        | =        |              48 |         47 |                       0 |                     0 | 1::integer
 softskills_utenti      | id_softskills | =        |              48 |         47 |                       0 |                     0 | 8::integer
(7 rows)

Ho scoperto che alcune versioni hanno anche già una view ottimizzata che si chiama pg_qualstats_pretty:

# SELECT * FROM pg_qualstats_pretty;
 left_schema |       left_table       |  left_column  |   operator   | right_schema | right_table | right_column | occurences | execution_count | nbfiltered
-------------+------------------------+---------------+--------------+--------------+-------------+--------------+------------+-----------------+------------
 public      | certificati_contents   | content       | pg_catalog.= |              |             |              |          2 |            6036 |       6034
 public      | certificati_contents   | id_content    | pg_catalog.= |              |             |              |          2 |            6036 |       6034
 public      | softskills_utenti      | id_softskills | pg_catalog.= |              |             |              |          1 |              48 |         47
 public      | softskills_utenti      | status        | pg_catalog.= |              |             |              |          1 |              48 |         47
 public      | utenti_ecm_professioni | id_utente     | pg_catalog.= |              |             |              |          1 |              90 |         89
(5 rows)

Ma va lanciata da super user; se volete potete impostare le GRANT anche ad altri utenti:

GRANT SELECT ON pg_qualstats TO app_user;
GRANT SELECT ON pg_qualstats_pretty TO app_user;

Se volete resettare le statistiche:

SELECT pg_qualstats_reset();

Enjoy!