Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Indici > Indici e ORDER BY
PrecedenteIndici multicolonnaCombinare indici multipliSuccessivo

11.4. Indici e ORDER BY

In aggiunta alla semplice ricerca delle righe che saranno restituite da una query, un indice può essere in grado di fornirle ordinate in una specificata maniera. Questo consente che una specificazione ORDER BY di una query sia soddisfatta senza una separata fase di ordinamento. Dei tipi di indice attualmente supportati da PostgreSQL™, solo i B-tree possono produrre uscite ordinate -- gli altri tipi di indice restituiscono le righe corrispondenti in un ordine non specificato e dipendente dall'implementazione.

Il planner considererà soddisfatta una specificazione ORDER BY o tramite la scansione di tutti gli indici disponibili che soddisfano la specificazione oppure tramite la scansione della tabella nell'ordine fisico e facendo un ordinamento esplicito. Per una query che richiede di scansionare una grande frazione della tabella, l'ordinamento esplicito sarà probabilmente più veloce perchè richiede meno accessi I/O su disco dovuti ad un modello di accesso meglio-ordinato, Gli indici sono più utili quando solo poche righe hanno bisogno di essere prelevate. Un caso particolarmente importante è ORDER BY in combinazione con LIMIT n: un ordinamento esplicito che dovrà processare tutti i dati per identificare le prime n righe, ma se c'è un indice che abbina l'ORDER BY allora le prime n righe possono essere recuperate direttamente, senza scandire per niente il resto.

Per impostazione predefinita, gli indici B-tree memorizzano le loro voci in ordine ascendente con i null in fondo. Questo significa che una scansione in avanti di un indice su una colonna x produce un uscita che soddisfa ORDER BY x (o più verbosamente, ORDER BY x ASC NULLS LAST). L'indice può anche essere scandito all'indietro, producendo un uscita che soddisfa ORDER BY x DESC (o più verbosamente, ORDER BY x DESC NULLS FIRST, dato che NULLS FIRST è l'impostazione predefinita per ORDER BY DESC).

È possibile modificare l'ordinamento di un indice B-tree includendo le opzioni ASC, DESC, NULLS FIRST, e/o NULLS LAST quando si crea l'indice; Per esempio:

CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);

Un indice memorizzato in ordine ascendente con i null in testa può essere soddisfatto sia da ORDER BY x ASC NULLS FIRST che da ORDER BY x DESC NULLS LAST in dipendenza della direzione nella quale esso viene scansionato.

È lecito domandarsi perchè fornire tutte le quattro opzioni, quando due opzioni insieme con le possibilità di scansione a rovescio coprirebbero tutte le varianti di ORDER BY. Negli indici a singola colonna le opzioni sono veramente rindondanti, ma negli indici multicolonna essi possono essere utili. Considerare un indice a due-colonne su (x, y): questo può soddisfare ORDER BY x, y se si scandisce in avanti, oppure ORDER BY x DESC, y DESC se si scandisce all'indietro. Ma è probabile che l'applicazione necessita di usare frequentemente ORDER BY x ASC, y DESC. Non c'è alcun modo per ottenere quell'ordinamento da un indice regolare, ma sarà possibile se l'indice viene definito come (x ASC, y DESC) o (x DESC, y ASC).

Ovviamente, gli indici con un ordinamento di tipo non-predefinito sono una caratteristica avanzata, ma qualche volta possono produrre notevoli velocizzazioni per certe query. Se conviene mantenere un tale indice dipende da quanto spesso si utilizzano le query che richiedono uno speciale tipo di ordinamento.

Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Indici > Indici e ORDER BY
PrecedenteIndici multicolonnaCombinare indici multipliSuccessivo