Scansioni “Index-Only” in PostgreSQL 9.2

PostgreSQL 9.2 avrà una novità interessante (tra le altre): il supporto per le scansioni di tipo “Index-only”. Vedremo in breve cosa sono e perchè sono considerate così importanti dagli addetti ai lavori.

Link al commit di riferimento: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a2822fb9337a21f98ac4ce850bb4145acf47ca27

Cosa sono

Le scansioni Index-only sono una novità pensata per migliorare le prestazioni di Postgres. Storicamente ogni miglioramento delle prestazioni viene accolto con entusiasmo dalla comunità (e anche da noi di 2ndQuadrant).

Tramite le scansioni Index-only, una query può essere eseguita sfruttando solo gli Indici, senza andare a interrogare le pagine di memoria su disco dove effettivanente risiede la tabella.

Questo, in determinate circostanze, aumenta notevolmente le prestazioni – dato che vengono risparmiati accessi al disco.

Ma le scansioni “Index” già presenti in Postgres non usano solo gli indici?

Effettivamente no, PostgreSQL esegue le Index scan accedendo sia all’indice che alla tabella sulla quale l’indice è definito.

Questo succede a causa dell’MVCC. Al momento che una tupla viene trovata (tramite ad es. una SELECT) con un Index scan, non è possibile stabilire se quella tupla sia realmente “visibile” a tutte le transazioni attive in quell’istante.

Ad esempio, una trasazione potrebbe aver eseguito un COMMIT che cancellava quella tupla dopo l’inizio della nostra SELECT.

La “visibility map”

L’innovazione che ha portato allo sviluppo delle scansioni “Index-only” si chiama visibility map, ed è presente da PostgreSQL 8.4.

La visibility map è una struttura che contiene un bit per ogni pagina di una tabella. Risulta quindi più piccola della pagina stessa, e risiede facilmente in cache.

Ognuno di questi bit indica se tutte le tuple di quella pagina sono visibili a tutte le transazioni correnti e future.

Sfruttando questa informazione, Postgres è in grado di discriminare se è necessario andare a prelevare dati dalla tabella su disco o meno.

Un esempio pratico

Creiamo, con psql, una tabella con 3000 record composti da un valore intero e da una stringa:

$ CREATE TABLE indexonlytest AS SELECT x AS intvalue, 'stringa' || x AS stringvalue, repeat( md5('2ndQuadrant'),100 ) AS longstring FROM generate_series(1,3000) x; SELECT 3000 

 

Creiamo un indice sulla tabella che contenga tutti i campi.

$ CREATE INDEX indextest ON indexonlytest ( intvalue,stringvalue ); CREATE INDEX 

 

Se provo ad eseguire una SELECT sulla tabella, ottengo un Index Only Scan:

$ EXPLAIN ANALYZE SELECT intvalue,stringvalue FROM indexonlytest ORDER BY intvalue LIMIT 20; QUERY PLAN Limit (cost=0.00..2.01 rows=20 width=36) (actual time=0.039..0.064 rows=20 loops=1) -> Index Only Scan using indextest on indexonlytest (cost=0.00..301.25 rows=3000 width=36) (actual tim e=0.039..0.062 rows=20 loops=1) Total runtime: 0.078 ms (3 rows) 

 

Adesso proviamo la stessa cosa senza includere, nell’indice, tutti i campi della tabella.

$ DROP INDEX indextest DROP INDEX $ CREATE INDEX indextest ON indexonlytest ( intvalue ); CREATE INDEX 

 

Eseguiamo la stessa SELECT di prima, osservando come si comporta il “planner”:

$ EXPLAIN ANALYZE SELECT intvalue,stringvalue FROM indexonlytest ORDER BY intvalue LIMIT 20; QUERY PLAN Limit (cost=0.00..0.95 rows=20 width=15) (actual time=0.027..0.033 rows=20 loops=1) -> Index Scan using indextest on indexonlytest (cost=0.00..142.25 rows=3000 width=15) (actual time=0.0 26..0.029 rows=20 loops=1) Total runtime: 0.665 ms (3 rows) 

 

Come si nota, in questo caso il planner ha scelto di utilizzare un normale Index Scan.

Conclusioni

Ovviamente, le informazioni contenute nell’indice devono essere sufficienti a soddisfare le richieste della query. In altre parole, l’indice deve contenere tutti i campi presenti nelle clausole SELECT, WHERE e/o ORDER BY della query.

Ciò non significa che si debba sfruttare sempre questa funzionalità, piuttosto cercare un compromesso: includere tutti i campi di una SELECT in un indice, aumenterà le dimensioni dello stesso, ma permetterà in alcuni casi di utilizzare le scansioni “Index Only”.