update - Indicizzazione dei valori Null in PostgreSQL




sql update postgresql (4)

Gli indici parziali non ti aiuteranno qui poiché troveranno solo i record che non desideri. Vuoi creare un indice che contenga i record che desideri.

CREATE INDEX findDaNulls ON othertable ((COALESCE(col1,col2,col3,'Empty')))
WHERE col1 IS NULL AND col2 IS NULL AND col3 IS NULL;

SELECT * 
FROM mytable m
JOIN othertable o ON m.id = o.m_id
WHERE COALESCE(col1,col2,col3,'Empty') = 'Empty';

Cercare BTW per i join di sinistra nulli in genere non è veloce come usare EXISTS o NOT EXISTS in Postgres.

Ho una richiesta del modulo:

select m.id from mytable m
left outer join othertable o on o.m_id = m.id
    and o.col1 is not null and o.col2 is not null and o.col3 is not null
where o.id is null

La query restituisce poche centinaia di record, anche se le tabelle hanno milioni di righe e il tempo di esecuzione è di circa un'ora.

Quando controllo le statistiche dell'indice utilizzando:

select * from pg_stat_all_indexes
where schemaname <> 'pg_catalog' and (indexrelname like 'othertable_%' or indexrelname like 'mytable_%')

Vedo che viene utilizzato solo l'indice per othertable.m_id e che gli indici per col1..3 non vengono utilizzati affatto. Perchè è questo?

Ho letto in few places che PG non è stato in grado di indicizzare i valori NULL. Tuttavia, ho letto che questo è presumibilmente cambiato da PG 8.3? Attualmente sto usando PostgreSQL 8.4 su Ubuntu 10.04. Devo fare un indice "parziale" o "funzionale" specificamente per velocizzare le query IS NOT NULL, o sta già indicizzando NULL e sto solo fraintendendo il problema?


Hai provato a creare un indice combinato su othertable (m_id, col1, col2, col3)?

È inoltre necessario verificare il piano di esecuzione (utilizzando EXPLAIN) anziché controllare le tabelle di sistema per l'utilizzo dell'indice.

PostgreSQL 9.0 (attualmente in versione beta) sarà in grado di utilizzare e indicizzare per una condizione IS NULL. Quella caratteristica è stata posticipata


Un indice parziale sembra la strada giusta qui:

Se hai una tabella che contiene sia ordini fatturati che non fatturati, in cui gli ordini non riempiti occupano una piccola frazione della tabella totale e tuttavia quelle sono le righe più accessibili, puoi migliorare le prestazioni creando un indice solo sulle righe non completate.

Forse quelle colonne nullable (col1, col2, col3) agiscono nel tuo scenario come una sorta di flag per distinguere alcune sottoclassi di record nella tua tabella? (ad esempio, una sorta di "cancellazione logica")? In tal caso, oltre alla soluzione di indice parziale, potresti preferire ripensare il tuo progetto e inserirli in tabelle fisiche diverse (magari usando l'ereditarietà), uno per i "record live" altro per i "record storici" e accedere al set completo (solo quando necessario) visualizza una vista.


Un singolo indice su m_id, col1, col2 e o.col3 sarebbe il mio primo pensiero per questa query.

E usa EXPLAIN su questa query per vedere come viene eseguito e cosa richiede così tanto tempo. Puoi mostrarci i risultati per aiutarti.





indexing