Ottimizzazione

Ottimizzazione delle query (seconda parte)

Ottimizzazione di un database PostgreSQL: definizione degli indici.

In Postgres esistono 3 tipi di indici, ognuno dei quali si basa su un diverso algoritmo:

• B-tree
  Sono gli indici creati di default e sono particolarmente adatti alle query che utilizzano i seguenti operatori: <, <=, =, >=, > . Esempio:
  CREATE INDEX nome-indice ON nome-tabella(elenco-campi);
  
• R-tree
  Le query che usufruiscono di questi indici sono quelle che utilizzano i seguenti operatori: <<, &<, &>, >>, @, ~=, &&
  In fase di creazione dell'indice si deve specificare che si intende crearne uno di questo tipo:
  CREATE INDEX nome-indice ON nome-tabella USING RTREE (elenco-campi);
  
• Hash
  Questo genere di indice e' indicato quando si confronta l'uguaglianza fra due campi.
  Anche in questo caso e' necessario specificare, in fase di creazione, il tipo di indice:
  CREATE INDEX nome-indice ON nome-tabella USING HASH (elenco-campi);
  

Come gia' accennato nel precedente intervento, un indice puo' essere definito su piu' campi. In questo caso e' necessario indicare per primi quelli che vengono utilizzati piu' spesso nelle query. Postgres non fa eccezione, se ad esempio, si definisce un indice su tre colonne c1, c2, c3; questo viene utilizzato nel caso in cui nella condizione di where vengano utilizzate le colonne c1, c2, c3 oppure c1,c2 o c1. Nel caso c1, c3 verrebbe utilizzato l'indice solo per la colonna c1.
Esistono alcune limitazioni all'uso degli indici definiti su piu' campi:

• Sono supportati solo dai B-Tree
• Sono utilizzati solo con l'operatore AND
• Il numero di colonne puo' essere al massimo 16
• Generalmente gli indici su piu' di tre colonne sono poco performanti e quindi inutili

Anche in Postgres e' possibile definire indici unici, cioe' che non contengono valori ripetuti (escluso il NULL):

CREATE UNIQUE INDEX nome-indice
ON nome-tabella (elenco-campi);

Questo genere di indici viene creato automaticamente per le primary key e per gli unique.
Anche in questo caso e' possibile definire indici unici solo per i B_Tree.
In Postgres e' possibile definire indici basati sul risultato di una funzione applicata ad una o piu' colonne della tabella.
Per comprenderne l'utilita' supponiamo di dover eseguire spesso una query di questo tipo:

select * from tabella
where upper(col1) = valore

Un indice definito sul campo col1 non verrebbe utilizzato in quanto la funzione upper ne modifica il valore che non e' quindi confrontabile con quello memorizzato nell'indice.
In questo caso e' necessario definire un indice calcolato con la funzione upper:

CREATE INDEX nome-indice ON tabella (upper(col1));

Questo tipo di indice e' sempre composto da una sola colonna.

• » Invia tramite EMAIL
• » Versione per la STAMPA
• » Le vostre opinioni

I link correlati all'argomento

• Ottimizzazione delle query (prima parte)
• Ottimizzazione delle query (terza parte)