Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Indici > Indici parziali
PrecedenteIndici sulle espressioniClassi operatore e famiglie di operatoriSuccessivo

11.8. Indici parziali

Un indice parziale è un indice costruito su un sottoinsieme di una tabella; il sottoinsieme è definito da una espressione condizionale (chiamata predicato dell'indice parziale). L'indice contiene le voci di quelle righe della tabella che soddisfano il predicato. Gli indici parziali sono una caratteristica specializzata, ma ci sono diverse situazioni in cui possono risultare utili.

La ragione principale per usare un indice parziale è di evitare l'indicizzazione di valori comuni. Dato che una query di ricerca di un valore comune (un valore che si ripete per più di una certa percentuale di tutte le righe della tabella) non userà comunque l'indice, non c'è motivo di tenere quelle righe nell'indice. Questo riduce la dimensione dell'indice, che velocizzerà le query che usano l'indice. Esso potrà velocizzare anche molte operazioni di aggiornamento di tabelle perchè l'indice non deve essere aggiornato in tutti i casi. La Esempio 11.1, «Impostare un indice parziale per escludere valori comuni.» mostra una possibile applicazione di questa idea.

Esempio 11.1. Impostare un indice parziale per escludere valori comuni.

Supporre di dover immagazzinare i log di accesso al server in un database. La maggior parte degli accessi proviene dalla gamma di indirizzi IP della propria organizzazione ma alcuni provengono da altre parti (vedi impiegati su connessioni dial-up). Se le ricerche per IP servono principalmente per gli accessi esterni, probabilmente non ci sarà bisogno di indicizzare la gamma di IP che corrisponde alla subnet della propria organizzazione.

Assumere una tabella come la seguente:

CREATE TABLE access_log (
    url varchar,
    client_ip inet,
    ...
);

Per creare un indice parziale che soddisfi questo esempio, utilizzare un comando come questo:

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND
           client_ip < inet '192.168.100.255');

Una tipica query che può utilizzare questo indice sarà:

SELECT *
FROM access_log
WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

Una query che non può usare questo indice è:

SELECT *
FROM access_log
WHERE client_ip = inet '192.168.100.23';

Osservare che questo tipo di indice parziale richiede che i valori comuni siano predeterminati, perciò gli indici parziali sono usati per distribuzioni di dati che non cambiano. Gli indici possono essere ricreati occasionalmente per adattarsi a nuove distribuzioni di dati, ma questo aggiunge sforzi di manutenzione.


Un altro possibile uso di un indice parziale è quello di escludere dall'indice i valori che non sono interessanti nelle normali query di lavoro; questo è mostrato nell'Esempio 11.2, «Impostare di un indice parziale per escludere valori non interessanti». Esso ottiene gli stessi vantaggi elencati sopra, ma impedisce che i valori «non interessanti» siano raggiunti dall'indice, anche se è probabile che una scansione di indice potrebbe essere vantaggiosa in quel caso. Ovviamente, impostare indici parziali per questo tipo di scenario richiederà molta cura e sperimentazione.

Esempio 11.2. Impostare di un indice parziale per escludere valori non interessanti

Se si ha una tabella che contiene sia ordini fatturati che non fatturati, dove gli ordini non fatturati rappresentano una piccola porzione della tabella totale ma anche le righe con i maggiori accessi, è possibile incrementare la prestazioni creando un indice sulle sole righe non fatturate. Il comando per creare l'indice somiglierebbe a questo:

CREATE INDEX orders_unbilled_index ON orders (order_nr)
    WHERE billed is not true;

Una possibile query per utilizzare questo indice potrebbe essere:

SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;

Tuttavia, l'indice potrà anche essere usato nelle query che non coinvolgono per nulla order_nr, p.e.:

SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;

Questo non è efficiente come potrebbe essere un indice parziale sulla colonna amount, dato che il sistema deve scandire l'intero indice. Tuttavia, se ci sono relativamente pochi ordini non fatturati, usando questo indice parziale solo per trovare gli ordini non fatturati potrebbe essere vincente.

Notare che questa query non può usare questo indice:

SELECT * FROM orders WHERE order_nr = 3501;

L'ordine 3501 potrebbe essere negli ordini fatturati o in quelli non fatturati.


La Esempio 11.2, «Impostare di un indice parziale per escludere valori non interessanti» spiega anche che la colonna indice e la colonna usata nel predicato non devono abbinarsi. PostgreSQL™ supporta indici parziali con predicato arbitrario, a condizione che siano implicate soltanto le colonne della tabella indicizzata. Tuttavia, ricordarsi che il predicato deve abbinare le condizioni usate nelle query che si suppone debbano deneficiare dell'indice. Per essere precisi, un indice parziale può essere usato in una query solo se il sistema può riconoscere che la condizione WHERE della query implica matematicamente il predicato dell'indice. PostgreSQL™ non ha un sofisticato dimostratore di teoremi che può riconoscere le espressioni matematicamente equivalenti che sono scritte nelle diverse forme. (Non solo un simile dimostratore generale di teoremi sarebbe estremamente difficile da creare, esso molto probabilmente sarebbe troppo lento per un reale utilizzo). Il sistema può riconoscere semplici implicazioni di disuguaglianza, per esempio «x < 1» implica che «x < 2»; altrimenti la condizione predicato dovrebbe abbinare esattamente la parte della condizione WHERE della query o l'indice non sarebbe riconosciuto come utilizzabile. L'abbinamento avviene durante la pianificazione della query, non durante l'esecuzione. Di conseguenza, le clausule delle query parametrizzate non funzioneranno con un indice parziale. Per esempio una query preparata con un parametro potrebbe specificare «x < ?» che non implicherà mai «x < 2» per tutti i possibili valori del parametro.

Un terzo possibile uso degli indici parziali non richiede che l'indice sia usato nelle query. L'idea è di creare un indice unique in un sottoinsieme di tabella, come nella Esempio 11.3, «Impostare un indice parziale unique». Questo impone l'unicità nel complesso delle righe che soddisfano l'indice predicato, senza vincolare quelle che non lo soddisfano.

Esempio 11.3. Impostare un indice parziale unique

Supporre di avere una tabella che descriva i risultati di una prova. Volendo assicurarsi che ci sia una sola voce «successo» per una determinata combinazione soggetto e obiettivo, in un certo numero di voci «insuccesso». Questo è un modo per farlo:

CREATE TABLE tests (
    subject text,
    target text,
    success boolean,
    ...
);

CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
    WHERE success;

Questo è un modo particolarmente efficiente quando ci sono poche prove "successo" e molte "insuccesso".


Per concludere, un indice parziale può anche essere usato per avere la priorità sul queryplan scelto dal sistema. Inoltre, insiemi di dati con particolari distribuzioni possono indurre il sistema ad usare un indice quando non è realmente necessario. In quel caso l'indice può essere impostato in modo che non sia disponibile per query dannose. Normalmente, PostgreSQL™ effettua scelte ragionevoli circa l'utilizzo degli indici (p.e., li evita quando recupera valori comuni, per cui il primo esempio salva realmente soltanto la dimensione dell'indice, esso non è necessario per evitare l'utilizzo dell'indice), e le scelte di piani grossolanamente scorrette sono causa di segnalazioni di bug.

Ricordarsi che l'impostazione di un indice parziale indica che si conosce almeno quanto conosce il pianificatore delle query, in particolare che si conosce quando l'uso di un indice potrebbe essere vantaggioso. La formazione di questa conoscenza richiede esperienza e anche di capire come gli indici lavorano in PostgreSQL™. Nella maggior parte dei casi, il vantaggio di un indice parziale su un indice regolare sarà minimo.

Ulteriori informazioni su indici parziali possono essere trovate in [STON89b], [OLSON93], e in [SESHADRI95].

Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Indici > Indici parziali
PrecedenteIndici sulle espressioniClassi operatore e famiglie di operatoriSuccessivo