Disabilitare un indice in PostgreSQL

Ho gia' scritto in precedenti post come PostgreSQL sia un database fortemente orientato al catalogo: ogni azione che il database esegue e' fortemente dipendente dal catalogo di sistema.

Anche gli indici usabili lo sono, ed essere a conoscenza di come disabilitare temporaneamente un indice puo' risultare molto importante. PostgreSQL infatti non mette a disposizione, per scelta, i "query hints", ovvero non e' possibile specificare con quale indice PostgreSQL deve forzatamente eseguire una query. Come conseguenza, a volte puo' risultare molto complicato valutare l'efficacia di un indice, e spesso il modo che si usa e' quello di disabilitare, a livello di intero cluster, alcuni metodi di accesso. Ma ecco che tramite il catalogo di sistema e' possibile inibire un singolo indice al fine di escluderlo dalle scelte possibili dell'ottimizzatore, e quindi di valutare gli indici rimanenti.

Per prima cosa si crei un semplice use case fatto da una sola tabella piena di tuple casuali:

CREATE TABLE people( name text,
surname text,
pk serial not null,
primary key(pk) );
INSERT INTO people( pk, name, surname )
SELECT generate_series(1, 100000),
md5( random()::text ),
md5( random()::text );

Si passi poi alla creazione di due indici sui campi della tabella:

CREATE INDEX name_idx ON people(name);
CREATE INDEX surname_idx ON people(surname);

E' ora possibile verificare che uno dei due indici viene usato in query sufficientemente filtranti:

EXPLAIN SELECT surname, name FROM people WHERE surname like 'f%';
QUERY PLAN
-----------------------------------------------------------------------------
Bitmap Heap Scan on people (cost=21.54..967.48 rows=500 width=64)
Filter: (surname ~~ 'f%'::text)
-> Bitmap Index Scan on surname_idx (cost=0.00..21.42 rows=500 width=0)
Index Cond: ((surname >= 'f'::text) AND (surname < 'g'::text)) 


 Il catalogo di sistema pg_index fornisce informazioni circa la disponibilita' degli indici su ogni tabella, e quindi e' possibile ottenere lo stato "attuale" degli indici per una tabella specifica con una query simile alla seguente:

SELECT cc.relname, indexrelid, indnatts, indisclustered, indisvalid, indisready FROM pg_index JOIN pg_class c ON c.oid = indrelid JOIN pg_class cc ON indexrelid = cc.oid WHERE c.relname = 'people' AND c.relkind = 'r' AND cc.relkind = 'i'; 

relname | indexrelid | indnatts | indisclustered | indisvalid | indisready -------------+------------+----------+----------------+------------+------------ people_pkey | 16541 | 1 | f | t | t 
name_idx | 16558 | 1 | f | t | t 
surname_idx | 16559 | 1 | f | t | t 

La colonna indnatts indica quanti attributi della tabella fanno parte dell'indice; la colonna indisclustered indica la cluster-ness dell'indice, o meglio se la tabella e' ordinata in base a questo indice. La colonna indisvalid e' quella forse piu' importante: indica se l'indice e' usabile dall'ottimizzatore, o meglio se usabile in modo "sicuro". In altre parole, un valore false in questa colonna indica che l'indice al momento non deve essere preso in considerazione perche', ad esempio, una operazione di INSERT/UPDATE sulla tabella e' ancora in corso e a seguito di questa l'indice deve essere riorganizzato. L'ultima colonna mostrata, indisready, indica se l'indice e' pronto a subire modifiche, ossia puo' essere aggiornato a seguito di modifiche nella tabella che referenzia. Da quanto descritto sopra si puo' concludere che indisvalid contribuisce a dare "visibilita'" e "validita'" all'indice, e quindi valori false nelle colonne indicano a PostgreSQL di ignorare l'indice. E' facile verificare quanto appena detto disabilitando un indice nel catalogo di sistema (ammesso che sia super utente): 

UPDATE pg_index SET indisvalid = false WHERE indexrelid = 16559; e il piano di esecuzione ora cambia: EXPLAIN SELECT surname, name FROM people WHERE surname like 'f%'; QUERY PLAN ------------------------------------------------------------ 
Seq Scan on people (cost=0.00..2485.00 rows=500 width=64) 

Con questa semplice modifica al catalogo e' quindi possibile verificare altri indici, scartati a priori dall'ottimizzatore, senza intaccare il funzionamento dell'intero cluster. Si noti che l'ottimizzatore di PostgreSQL e' molto bravo nel fare il suo lavoro, e quindi se ha deciso di scartare altri metodi di accesso e' quasi certo che questi ultimi siano poco efficienti. E' possibile verificare il comportamento degli indici anche a seguito di una operazione di UPDATE: 

EXPLAIN UPDATE people SET surname = 'TEST' WHERE surname like 'f%'; QUERY PLAN ------------------------------------------------------------------ Update on people (cost=0.00..2485.00 rows=500 width=42) -> Seq Scan on people (cost=0.00..2485.00 rows=500 width=42)
Filter: (surname ~~ 'f%'::text)

e riabilitando l'indice si ottiene che questo viene nuovamente usato:

UPDATE pg_index SET indisvalid = true WHERE indexrelid = 16559;
EXPLAIN UPDATE people SET surname = 'TEST' WHERE surname like 'f%';
QUERY PLAN
-----------------------------------------------------------------------------------
Update on people (cost=21.54..967.48 rows=500 width=42)
-> Bitmap Heap Scan on people (cost=21.54..967.48 rows=500 width=42)
Filter: (surname ~~ 'f%'::text)
-> Bitmap Index Scan on surname_idx (cost=0.00..21.42 rows=500 width=0)
Index Cond: ((surname >= 'f'::text) AND (surname < 'g'::text))