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”.

Ecco una lista delle principali novità in ambito "amministrazione" di PostgreSQL 8.4.

Collation a livello di database

Il tipo dell’ordine alfabetico delle stringhe di caratteri è da adesso una proprietà dei database invece che una proprietà del server definita a livello di inizializzazione. Questo permette agli utenti di avere molteplici linguaggi naturali (lingue) pienamente supportati dalla stessa installazione di PostgreSQL.

Questa funzionalità spiana la strada alle collation a livello di tabella e di colonna, presumibilmente già dalle prossime release di PostgreSQL.

Approfondimento sul comando CREATE DATABASE.

Mappa di visibilità (Visibility Map)

Grazie ad un registro in memoria che tiene traccia delle pagine "sporcate" dalle transazioni (la cosiddetta "mappa di visibilità"), il processo di VACUUM riesce a leggere solamente le pagine che necessitano di pulizia invece di scandire tutte le pagine della tabella. La mappa di visibilità permette pertanto di ridurre notevolmente i tempi di vacuum per tabelle di grandi dimensioni.

Approfondimento su "Visibility Map".

Mappa di spazio libero ottimizzata in modo automatico (free space map)

Una modifica molto importante al backend di PostgreSQL 8.4 è la gestione automatica della mappa di spazio libero (free space map). Fino alla versione 8.3 compresa, gli amministratori dovevano impostare il parametro max_fsm_pages per stabilire il numero di pagine su disco delle quali tenere traccia nella mappa di spazio libero all’interno della memoria condivisa. Questo parametro richiedeva un attento studio da parte degli amministratori del server di PostgreSQL.

Ebbene, con PostgreSQL 8.4, l’opzione max_fsm_pages è definitivamente sparita: il sistema è in grado di tenere traccia in modo automatico e con molta più precisione lo spazio libero all’interno di ogni pagina. Ad ogni tabella infatti viene affiancata una mappa di spazio libero memorizzata su disco.

Configurazione di auto-vacuum a livello di tabella con i comandi CREATE TABLE e ALTER TABLE / SET

Rispetto a PostgreSQL 8.3, non è più necessario inserire manualmente nel catalogo pg_autovacuum le configurazioni a livello di tabella per auto-vacuum. Adesso è possibile gestire la configurazione a livello di tabella con i comandi CREATE TABLE e ALTER TABLE. Inoltre (e finalmente), la configurazione di auto-vacuum è adesso salvata da pg_dump.

Per maggiori informazioni:

• il comando CREATE TABLE
• il comando ALTER TABLE

Esecuzione a tempo per pgbench

E’ possibile richiedere a pgbench "quante cose riesce a fare in uno specifico lasso di tempo", piuttosto che "quanto tempo impiega a eseguire uno specifico set di operazioni". La differenza è soprattutto nell’organizzazione dei test e nell’analisi dei risultati: paragonare il numero di query eseguito in un intervallo di tempo è molto più semplice ed efficace.

La funzione pg_conf_load_time()

Non dovrai più indovinare se il file postgresql.conf è più nuovo di quello utilizzato dal server PostgreSQL in azione: è possibile confrontare la sua data di modifica con quella del risultato della funzione pg_conf_load_time().

Visualizzazione fino a livello di colonna per EXPLAIN verbose

Adesso il comando EXPLAIN VERBOSE fornisce informazioni utili sulle colonne che ogni nodo dell’albero delle query restituisce al padre: questo tipo di informazioni mostra quanto pessima sia "SELECT *".

Riporta tutte le query coinvolte in un errore di deadlock

Informazioni sui deadlock sono fornite direttamente dal server PostgreSQL, senza necessità di scoprirle dai log.

Migliorate informazioni di pg_settings

pg_settings è in grado di mostrare le opzioni disponibili per le istruzioni globali di configurazione utente (GUC) con un insieme definito di valori. Molte impostazioni infatti accettano valori enumerati, e pg_settings è adesso in grado di mostrarli, facilitando la vita all’utente.

Migliorata la gestione dei certificati SSL

Le connessioni SSL possono adesso prevenire da attacchi del tipo man-in-the-middle, grazie alla verifica dei certificati del client.

Indici GIN multi-colonna

E’ possibile adesso avere indici GIN multi-colonna.

Approfondimento su indici GIN.

Miglioramenti al file pg_hba.conf

Adesso è possibile specificare opzioni di autenticazione nella forma NOME=VALORE. E’ stata inoltre rimossa l’opzione ident sameuser, rendendola di default nel caso in cui la usermap non sia specificata.

Per maggiori informazioni e per gli altri cambiamenti, si rimanda alla sezione della documentazione sul file pg_hba.conf.

Questo articolo è una traduzione da me riadattata del documento "What’s new in 8.4" del PostgreSQL Global Development Group. Ringrazio inoltre Hubert Lubaczewski per la serie di articoli "Waiting for 8.4".