Una query per le ultime 20 issue può leggere 200.000 righe in PostgreSQL
Testo scritto con l’intelligenza artificiale a partire da una fonte citata, senza revisione umana prima della pubblicazione.
Con un indice solo su project_id la query ORDER BY costringe Postgres a leggere e ordinare tutte le righe del progetto; un indice composito su (project_id, created_at DESC, id DESC) riduce le letture a poche decine di buffer.
Una query che mostra le ultime 20 issue di un progetto ha letto 200.000 righe e 1.941 buffer, pur avendo un indice su project_id. Con un indice composito, le letture sono scese a 23 buffer1.
La prova arriva da un test su PostgreSQL 16 con 2.000.000 di issue distribuite su 2.000 progetti. Il progetto 1 aveva 200.000 issue. Il progetto 500 ne aveva 901. Le misure sono mediane di cinque EXPLAIN (ANALYZE, BUFFERS) con dati in memoria. L’indice composito è risultato circa 230 volte più veloce sul progetto grande. I buffer sono passati da 1.941 a 231.
La query è quella che tutti scrivono per la paginazione recente: SELECT id, title, status, created_at FROM issues WHERE project_id = $1 ORDER BY created_at DESC, id DESC LIMIT 20;. Lo schema tipico ha solo CREATE INDEX ON issues (project_id). In sviluppo è veloce1.
Con il solo indice su project_id PostgreSQL fa una Parallel Bitmap Heap Scan sulle righe del progetto, poi un Sort top-N heapsort. L’indice trova le righe del progetto, ma non conosce created_at. Per decidere le ultime 20, il motore legge tutte le 200.000 righe del progetto, le ordina per data e tiene la coda. Tre worker si dividono il lavoro1.
Il costo cresce con la dimensione del progetto, non con il LIMIT. Un progetto tipico paga poco (2,4 ms). Il cliente più grande paga di più, a ogni pagina. I 20 ms tengono finché tutto è in cache. Quando i dati vengono da disco, quei 1.941 buffer diventano letture reali1.
La correzione è allineare l’indice alla WHERE e all’ORDER BY: CREATE INDEX CONCURRENTLY idx_issues_project_created ON issues (project_id, created_at DESC, id DESC);. A quel punto il piano diventa un Index Scan sull’indice ordinato, con condizione project_id = 1. Niente sort. PostgreSQL cammina nell’indice dalle righe più nuove e si ferma dopo 20. Grande o piccolo il progetto, legge 23 buffer1.
Tre dettagli reggono l’ottimizzazione. Le colonne di uguaglianza vanno prima, poi le colonne dell’ordinamento. Con (created_at, project_id) il motore non salta al progetto, scorre la timeline globale e filtra, e va peggio sui progetti piccoli. La direzione dell’ordinamento deve coincidere, incluso il tiebreaker id. È ciò che rende stabile anche la keyset pagination con WHERE (created_at, id) < ($2, $3). Infine, l’indice va costruito con CONCURRENTLY, per non bloccare le scritture su issues durante il build1.
Una volta creato l’indice composito, quello vecchio su project_id diventa ridondante. Il nuovo soddisfa da solo WHERE project_id = $1. Rimuoverlo riduce l’overhead in scrittura1.
Come riconoscere il problema nel piano: un Sort, spesso top-N heapsort, subito sotto il Limit, uno scan con rows= ben oltre il LIMIT, buffer nell’ordine delle migliaia per una pagina da 20. Se vedi uno di questi segnali su una query “ultimi N”, l’indice non corrisponde all’ORDER BY1.
L’autore ha inserito questa regola nel suo skills pack per Claude Code su TypeScript, GraphQL e Postgres. Il backend di partenza include già l’indice composito su issues. Due skill sono gratuite su GitHub. Il pacchetto completo, con la skill di profilazione delle query lente e altre sei, è su Gumroad1.
La stessa struttura di indice serve sia la paginazione classica con LIMIT sia la keyset pagination1.
Fonti
Come è nato questo articolo Scritto da un sistema di intelligenza artificiale, con controlli automatici
Millebit non ha giornalisti che scrivono gli articoli: li scrive un motore editoriale basato su modelli di intelligenza artificiale, a partire dalle fonti elencate qui sopra. Prima di uscire, ogni testo passa questi controlli:
- Rilettura. Un passaggio toglie i tic della prosa generata da un modello, come i contrasti costruiti e le frasi che annunciano invece di informare, senza toccare fatti e fonti.
- Fedeltà alle fonti. Un passaggio dedicato toglie ogni affermazione che le fonti non sostengono. Valutazione su questo pezzo: 10/10.
- Anti-cliché. Un filtro scarta frasi fatte, enfasi e tic della prosa artificiale: superato.
- Giudizio editoriale. Un modello valuta il pezzo su sette criteri (apertura, chiarezza, economia, concretezza, voce, struttura, fedeltà) e sotto 7,8/10 non si pubblica. Questo articolo ha preso 8,5/10.
I controlli riducono gli errori ma non li eliminano: un modello può fraintendere una fonte. Per le decisioni importanti, risali alla fonte originale. Se trovi un errore, segnalacelo.
Per vedere Millebit nelle notizie principali di Google, aggiungilo alle tue fonti preferite. Oppure segui l’edizione di oggi, il feed RSS o La Sintesi.