PostgreSQL - INDICI

Gli indici sono tabelle di ricerca speciali che il motore di ricerca del database può utilizzare per accelerare il recupero dei dati. In poche parole, un indice è un puntatore ai dati in una tabella. Un indice in un database è molto simile a un indice sul retro di un libro.

Ad esempio, se desideri fare riferimento a tutte le pagine di un libro che tratta un determinato argomento, devi prima fare riferimento all'indice, che elenca tutti gli argomenti in ordine alfabetico e poi fare riferimento a uno o più numeri di pagina specifici.

Un indice aiuta ad accelerare le query SELECT e le clausole WHERE; tuttavia, rallenta l'immissione dei dati, con le istruzioni UPDATE e INSERT. Gli indici possono essere creati o eliminati senza alcun effetto sui dati.

La creazione di un indice implica l'istruzione CREATE INDEX, che consente di denominare l'indice, di specificare la tabella e la colonna o le colonne da indicizzare e di indicare se l'indice è in ordine crescente o decrescente.

Gli indici possono anche essere univoci, in modo simile al vincolo UNIQUE, in quanto l'indice impedisce voci duplicate nella colonna o combinazione di colonne su cui è presente un indice.

Il comando CREATE INDEX

La sintassi di base di CREATE INDEX è il seguente -

CREATE INDEX index_name ON table_name;

Tipi di indice

PostgreSQL fornisce diversi tipi di indice: B-tree, Hash, GiST, SP-GiST e GIN. Ogni tipo di indice utilizza un algoritmo diverso che è più adatto a diversi tipi di query. Per impostazione predefinita, il comando CREATE INDEX crea indici B-tree, che si adattano alle situazioni più comuni.

Indici a colonna singola

Un indice a colonna singola è quello creato in base a una sola colonna della tabella. La sintassi di base è la seguente:

CREATE INDEX index_name
ON table_name (column_name);

Indici a più colonne

Un indice a più colonne è definito su più di una colonna di una tabella. La sintassi di base è la seguente:

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

Indipendentemente dal fatto che si crei un indice a colonna singola o a più colonne, prendere in considerazione le colonne che è possibile utilizzare molto frequentemente nella clausola WHERE di una query come condizioni di filtro.

Se deve essere utilizzata una sola colonna, la scelta dovrebbe essere un indice a colonna singola. Dovrebbero esserci due o più colonne utilizzate frequentemente nella clausola WHERE come filtri, l'indice a più colonne sarebbe la scelta migliore.

Indici unici

Gli indici univoci vengono utilizzati non solo per le prestazioni, ma anche per l'integrità dei dati. Un indice univoco non consente l'inserimento di valori duplicati nella tabella. La sintassi di base è la seguente:

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Indici parziali

Un indice parziale è un indice costruito su un sottoinsieme di una tabella; il sottoinsieme è definito da un'espressione condizionale (chiamata predicato dell'indice parziale). L'indice contiene voci solo per quelle righe della tabella che soddisfano il predicato. La sintassi di base è la seguente:

CREATE INDEX index_name
on table_name (conditional_expression);

Indici impliciti

Gli indici impliciti sono indici che vengono creati automaticamente dal server di database quando viene creato un oggetto. Gli indici vengono creati automaticamente per vincoli di chiave primaria e vincoli univoci.

Esempio

Quello che segue è un esempio in cui creeremo un indice nella tabella AZIENDA per la colonna salario -

# CREATE INDEX salary_index ON COMPANY (salary);

Ora, elenchiamo tutti gli indici disponibili nella tabella AZIENDA utilizzando \d company comando.

# \d company

Questo produrrà il seguente risultato, dove company_pkey è un indice implicito, che è stato creato quando è stata creata la tabella.

Table "public.company"
 Column  |     Type      | Modifiers
---------+---------------+-----------
 id      | integer       | not null
 name    | text          | not null
 age     | integer       | not null
 address | character(50) |
 salary  | real          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)
    "salary_index" btree (salary)

È possibile elencare l'intero database degli indici utilizzando l'estensione \di comando -

Il comando DROP INDEX

Un indice può essere eliminato utilizzando PostgreSQL DROPcomando. È necessario prestare attenzione quando si elimina un indice perché le prestazioni potrebbero essere rallentate o migliorate.

La sintassi di base è la seguente:

DROP INDEX index_name;

È possibile utilizzare la seguente dichiarazione per eliminare l'indice creato in precedenza:

# DROP INDEX salary_index;

Quando si dovrebbero evitare gli indici?

Sebbene gli indici abbiano lo scopo di migliorare le prestazioni di un database, a volte dovrebbero essere evitati. Le seguenti linee guida indicano quando è necessario riconsiderare l'uso di un indice:

  • Gli indici non devono essere utilizzati su tavolini.

  • Tabelle con aggiornamenti batch frequenti e di grandi dimensioni o operazioni di inserimento.

  • Gli indici non devono essere utilizzati su colonne che contengono un numero elevato di valori NULL.

  • Le colonne che vengono manipolate di frequente non devono essere indicizzate.