Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Suggerimenti per migliorare le prestazioni > Controllare il pianificatore con clausole JOIN esplicite
PrecedenteStatistiche usate dal pianificatorePopolare un databaseSuccessivo

14.3. Controllare il pianificatore con clausole JOIN esplicite

È possibile controllare il pianificatore di query fino ad un certo punto usando la sintassi esplicita JOIN. Per vedere come mai è importante, abbiamo bisogno di alcune informazioni di base.

In una join semplice, tipo:

SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;

il pianificatore è libero di fare il join per le tabelle specificate in qualsiasi ordine. Per esempio, potrebbe generare un piano di query che fa il join tra A e B, usando la condizione WHEREa.id = b.id, e quindi fare il join di C con questa tabella, usando l'altra condizione WHERE. O potrebbe fare il join di B con C e quindi fare il join del risultato con A. O potrebbe fare il join di A con C e poi fare il join con B - ma questo sarebbe inefficiente, dato che si dovrebbe calcolare l'intero prodotto Cartesiano tra A e C, non ci sono condizioni applicabili nella clausola WHERE che permettano l'ottimizzazione della join. (Tutte le join nell'esecutore di PostgreSQL™ avvengono tra 2 tabelle di input, così è necessario costruire il risultato nell'uno o l'altro di questi due modi). Il punto è che queste diverse possibilità di join danno risultati semanticamente equivalenti ma potrebbero avere costi di esecuzione immensamente diversi. Inoltre, il pianificatore li esplorerà tutti provando a trovare il piano query più efficiente.

Quando una query coinvolge solo due o tre tabelle, non ci sono molti ordini di join di cui preoccuparsi. Ma il numero di possibili ordini di join cresce esponenzialmente man mano che il numero di tabelle si espande. Sotto le dieci o più tabelle di input non è più pratico fare una ricerca completa di tutte le possibilità, ed anche per sei o sette tabelle la pianificazione potrebbe richiedere un tempo fastidiosamente lungo. Quando ci sono ttroppe tabelle di input, il pianificatore di PostgreSQL™ passerà da una ricerca completa a una ricerca genetica probabilistica attraverso un numero limitato di possibilità. (La soglia di passaggio è definita dal parametro geqo_threshold). La ricerca genetica impiega meno tempo, ma non troverà necessariamente il miglior piano possibile.

Quando la query coinvolge outer join, il pianificatore ha meno libertà rispetto a normali (inner) join. Per esempio, considerare:

SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

Anche se le restrizioni di questa query sono simili, superficialmente, all'esempio precedente, le semantiche sono diverse perchè una riga dev'essere emessa per ogni riga di A che non ha una riga corrispondente nella join di A con C. Inoltre il pianificatore non ha scelta per l'ordine delle join qui: deve fare il join di B con C e quindi il join di A con quel risultato. Di conseguenza, questa query impiega meno tempo a pianificare della query precedente. In altri casi, il pianificatore potrebbe stabilire che più di un ordine di join è sicuro. Per esempio, dato:

SELECT * FROM a LEFT JOIN b ON (a.bid = b.id) LEFT JOIN c ON (a.cid = c.id);

è valido fare il join di A sia con B che con C prima. Attualmente, solo FULL JOIN vincola completamente l'ordine di join. La maggior parte dei casi pratici che coinvolgono LEFT JOIN o RIGHT JOIN per certi versi possono essere risistemati.

La sintassi esplicita delle inner join (INNER JOIN, CROSS JOIN, o JOIN) è semantimante lo stesso di elencare le relazioni di input nella FROM, così non vincola l'ordine di join.

Anche se la maggior parte dei tipo di JOIN non vincola completamente l'ordine di join, è possibile istruire il pianificatore di query di PostgreSQL™ a trattare tutte le clausole JOIN comunque come vincolanti per l'ordine di join. Per esempio, queste tre query sono logicalmente equivalenti:

SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

Ma se si dice al pianificatore di onorare l'ordine JOIN, il secondo e il terzo prende meno tempo per pianificare rispetto al primo. Questo effetto non varrà la pena quando si ha a che fare con solo tre tabelle, ma può essere un salvavita con molte tabelle.

Per forzare il pianificatore a seguire l'ordine di join predisposto escplicitamente dalle JOIN, impostare il parametro join_collapse_limit a 1. (Altri valori possibili sono discussi sotto).

Non si ha bisogno di vincolare completamente l'ordine della join per risparmiare tempo di ricerca, dato che è giusto usare gli operatori JOIN all'interno di elementi di un elenco di una normale FROM. Per esempio, considerare:

SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...;

Con join_collapse_limit = 1, questo forza il pianificatore a fare il join di A con B prima di fare il join con altre tabelle, ma altrimenti non vincola le sue scelte. In questo esempio, il numero di possibili ordini di join viene ridotto di un fattore di 5.

Vincolare la ricerca del planner in questo modo è una tecnica utile sia per ridurre il tempo di pianificazione sia per direzionare il pianificatore verso un buon piano di query. Se il pianificatore sceglie un cattivo ordine di join in modo predefinito, è possibile costringerlo a scegliere un ordine migliore con la sintassi JOIN - assumendo che si conosca un ordine migliore, ovviamente. Si raccomanda di sperimentare.

Una questione correlata che influisce sul tempo di pianificazione è di collassare le sottoquery nelle loro query genitore. Per esempio, considerare:

SELECT *
FROM x, y,
    (SELECT * FROM a, b, c WHERE something) AS ss
WHERE somethingelse;

Questa situazione potrebbe presentarsi dall'uso di una vista che contiene una join; la regola SELECT della vista sarà inserita al posto del riferimento alla vista, generando una query molto simile a quella sopra. Normalmente, il pianificatore proverà a collassare la sottoquery nella query genitore, generando:

SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;

Questo di solito risulta in un piano migliore rispetto a pianificare le sottoquery separatamente. (Per esempio, le condizioni WHERE esterne potrebbero essere tali che il join di X con A elimini prima molte righe di A, eliminando così il bisogno di formare l'intero output logico della sottoquery). Ma allo stesso tempo, abbiamo incrementato il tempo di pianificazione; qui, abbiamo un problema di join a cinque vie che sostituisce due problemi di join a tre vie. Data la crescita esponenziale del numero di possibilità, questo fa molta differenza. Il pianificatore prova ad evitare di bloccarsi in enormi problemi di ricerche join non collassando una sottoquery se risultassero più di from_collapse_limit elementi FROM nella query genitore. È possibile variare il tempo di pianificazione a dispetto della qualità del piano aggiustando questo parametro.

from_collapse_limit e join_collapse_limit hanno nomi simili perchè fanno quasi la stessa cosa: una controlla quando il pianificatore «distruggerà» le sottoquery, e l'altra controlla quando distruggerà le join esplicite. Tipicamente si vorrà o impostare join_collapse_limit uguale a from_collapse_limit (così che le join esplicite e le sottoquery si comportino similmente) o impostare join_collapse_limit a 1 (se di vuole controllare l'ordine di join con join esplicite). Ma si potrebbe impostarli diversamente se si sta provando a mettere a punto il passaggio tra il tempo di pianificazione e il tempo di esecuzione.

Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Suggerimenti per migliorare le prestazioni > Controllare il pianificatore con clausole JOIN esplicite
PrecedenteStatistiche usate dal pianificatorePopolare un databaseSuccessivo