Indici parziali in PostgreSQL: verifica con EXPLAIN ANALYZE e controllo negativo

Obiettivo

Misurare, su un carico sintetico ma riproducibile, se un indice parziale allineato al predicato sposta il piano da sequential scan sulla heap a accesso per indice e se, leggendo BUFFERS, le letture si concentrano sull’indice invece che sull’intera tabella. Il test include anche una query il cui predicato non entra nell’indice parziale, per vedere che il planner non lo “presta” per errore.

Riferimento di versione: comportamento descritto allineato a PostgreSQL 16 (nomi dei nodi e opzioni EXPLAIN come in documentazione ufficiale). Su altre major i costi e i tempi cambiano; la forma del confronto resta valida.

Ipotesi

Con ~500k righe e una quota piccola di righe open, una query del tipo WHERE tenant_id = costante AND status = 'open' dovrebbe, dopo CREATE INDEX … WHERE (status = 'open'), favorire un piano che attinge al sottoinsieme indicizzato invece di leggere quasi tutta la heap.

Dataset (deterministico)

random() rende il file diverso a ogni run. Qui uso hashtextextended su stringhe derivate da n così, a parità di major Postgres, stesse righe a ogni ricreazione della tabella.

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id           bigserial PRIMARY KEY,
  tenant_id    int NOT NULL,
  status       text NOT NULL CHECK (status IN ('open', 'closed', 'archived')),
  amount_cents int NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);

INSERT INTO orders (tenant_id, status, amount_cents)
SELECT
  (abs(hashtextextended('t' || n::text, 0)) % 5) + 1 AS tenant_id,
  CASE
    WHEN (abs(hashtextextended('o' || n::text, 0)) % 1000) < 12 THEN 'open'
    WHEN (abs(hashtextextended('c' || n::text, 0)) % 1000) < 12 THEN 'closed'
    ELSE 'archived'
  END AS status,
  abs(hashtextextended('a' || n::text, 0)) % 100000 AS amount_cents
FROM generate_series(1, 500000) AS s(n);

ANALYZE orders;

Verifiche consigliate subito dopo l’insert (output numerico dipende solo da questa formula, non da “un feeling”):

SELECT status, count(*) FROM orders GROUP BY 1 ORDER BY 1;
SELECT count(*) FROM orders WHERE tenant_id = 3 AND status = 'open';

Ci si aspetta pochi open rispetto ad archived, e un conteggio > 0 di open per tenant_id = 3 (il mix tenant/status è decorrelato rispetto al vecchio schema n % 100 / n % 5, che incollava open su un solo tenant).

La stessa istruzione EXPLAIN sotto va eseguita in fase A (solo PK) e di nuovo in fase B (dopo l’indice parziale).

Fase A — solo chiave primaria

Con la tabella così popolata e senza indice secondario su (tenant_id, …) filtrato per open, il piano atteso è un Seq Scan su orders: attraversamento della heap (o gran parte), filtro su tenant_id e status, soddisfacimento di ORDER BY id, poi LIMIT.

Esegui:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, amount_cents
FROM orders
WHERE tenant_id = 3 AND status = 'open'
ORDER BY id
LIMIT 50;

Esempio di piano (sintetico, PG 16 — costi/tempi/buffer sono indicativi; sul tuo host saranno diversi, la gerarchia dei nodi è ciò che conta):

Limit  (cost=... rows=50 width=...) (actual time=... rows=50 loops=1)
  ->  Sort  (...)
        Sort Key: id
        Sort Method: top-N heapsort
        ->  Seq Scan on orders  (...)
              Filter: ((tenant_id = 3) AND (status = 'open'::text))
              Rows Removed by Filter: 499xxx   -- quasi tutta la tabella scartata qui
              Buffers: shared hit=... read=...  -- molta heap, poco risultato

Cosa verificare a occhio:

• compare Seq Scan on orders (non un indice sul predicato open);
• Rows Removed by Filter molto alto rispetto alle 50 righe finali;
• Buffers sulla relazione heap ampia rispetto al risultato.

Nota: non ricreare la tabella tra fase A e fase B. L’indice si aggiunge sulla stessa heap già popolata; se rifai DROP/INSERT nel mezzo, i piani non sono più confrontabili con la baseline appena misurata.

Fase B — indice parziale allineato al predicato

CREATE INDEX orders_open_by_tenant_partial
  ON orders (tenant_id, id)
  WHERE status = 'open';

ANALYZE orders;

Ripeti lo stesso identico EXPLAIN della fase A:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, amount_cents
FROM orders
WHERE tenant_id = 3 AND status = 'open'
ORDER BY id
LIMIT 50;

Esempio di piano dopo l’indice (sintetico — Postgres può scegliere Index Scan o catena Bitmap Index Scan → Bitmap Heap Scan; l’obiettivo è vedere il nome dell’indice parziale nel piano):

Limit  (...)
  ->  Sort  (...)
        ->  Bitmap Heap Scan on orders  (...)
              Recheck Cond: ((tenant_id = 3) AND (status = 'open'::text))
              ->  Bitmap Index Scan on orders_open_by_tenant_partial  (...)
                    Index Cond: (tenant_id = 3)
              Buffers: shared hit=... read=...   -- compaiono letture sull’INDICE + heap ridotta

Cosa verificare a occhio:

• compare il nome orders_open_by_tenant_partial (o accesso equivalente che lo cita);
• in Buffers, comparsa di blocchi letti/hit sull’indice e heap molto più leggera rispetto alla fase A per la stessa SELECT.

Il confronto A vs B non è teorico: è la stessa query, due volte, con solo l’DDL dell’indice in mezzo.

Controllo negativo

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM orders
WHERE tenant_id = 3 AND status = 'closed'
LIMIT 50;

Il predicato non soddisfa WHERE status = 'open' dell’indice parziale. Non deve comparire orders_open_by_tenant_partial come pezzo del piano per filtrare closed.

Esempio (sintetico) — atteso: seq scan o altro indice, ma non il parziale “open”:

Limit  (...)
  ->  Seq Scan on orders  (...)
        Filter: ((tenant_id = 3) AND (status = 'closed'::text))
        Rows Removed by Filter: ...
        Buffers: ...
-- nessuna riga "Bitmap Index Scan on orders_open_by_tenant_partial"

Se vedessi quel nome indicizzato per risolvere closed, è un segnale da investigare (statistiche, predicato, ipotesi sbagliata), non un’ottimizzazione silenziosa.

Cosa resta fuori

Niente parallel workers, niente INCLUDE, niente partizionamento: solo heap + un indice parziale e due famiglie di query (open vs closed) per isolare l’effetto.

Chiusura

Gli output in riquadro sono ancore visive: mostrano dove guardare nel testo del piano (Seq Scan vs nome indice, Rows Removed by Filter, Buffers). I numeri precisi (tempi, conteggi buffer) li conferma solo la tua istanza con gli script sopra. Se dopo l’indice Postgres resta sul seq scan, il materiale operativo è lo stesso che in produzione: allineamento predicato/indice, cast impliciti, freschezza di ANALYZE, work_mem, cardinalità stimate vs reali.

Riferimenti

• CREATE INDEX — clausola WHERE
• EXPLAIN — ANALYZE, BUFFERS