Che cosa fa esattamente PostgreSQL (o altri database) internamente quando "prepari" una query invece di eseguirla direttamente?
Quando affronto un numero enorme di INSERT ripetuti in un ciclo, tendo a creare prima una "query di preparazione dello scheletro" prima del ciclo, e nel ciclo, semplicemente "eseguo" questa query preparata e le invio tutti i valori. Ho sentito molto tempo fa, e posso capire in un certo senso astratto, che questo è più ottimizzato rispetto al semplice avere il ciclo con una normale query parametrizzata al suo interno.
Tuttavia, non capisco cosa stia facendo esattamente PG che lo renda molto più veloce. Se è anche molto più veloce. Francamente non ho mai fatto alcun benchmark reale per confrontare i due diversi metodi.
Inoltre, PG non dovrebbe essere "riscaldato" a una query dopo averla ripetuta diverse (figuriamoci molte) volte in brevissima successione, e forse fare la stessa cosa che faccio io manualmente, ma internamente, quando uso solo un normale query parametrizzata?
Mi ritrovo costantemente a indovinare cosa sta facendo il database internamente. Non ho una reale comprensione di quanto sia "intelligente". Temo di fare molte cose prive di significato perché è già curato internamente dal suo codice intelligente.
Forse "preparare" ed "eseguire" è una pratica arcaica che non ha alcun reale beneficio in questi giorni?
Sembra che PG stia fondamentalmente allocando risorse per "prepararsi" per le imminenti enormi quantità di query INSERT simili, ma non capisco cosa esattamente farebbe di diverso rispetto alla semplice esecuzione di una per una. Inoltre non capisco quale sarebbe il punto di una query / istruzione preparata non INSERT.
PS: Giusto per chiarire ogni confusione: non uso mai query non parametrizzate, che siano preparate o meno. Molte persone confondono "query parametrizzate" e "istruzioni preparate". Anche io ho chiamato queste ultime "query preparate" in questa domanda ...
Risposte
In realtà, la differenza tra un'istruzione parametrizzata e un'istruzione preparata non è così grande in PostgreSQL: la prima è internamente un'istruzione preparata senza nome e la differenza principale è che viene deallocata automaticamente quando arriva la successiva istruzione preparata senza nome.
Quando prepari un'istruzione, PostgreSQL la ricorda nella sessione corrente (nulla è condiviso tra le sessioni).
Per le prime cinque esecuzioni, PostgreSQL genererà un piano di query personalizzato che tiene conto dei valori dei parametri. Dopodiché, PostgreSQL genererà un piano generico indipendente dai parametri di query e, se si stima che tale piano generico non sia più costoso dei precedenti piani personalizzati, verrà utilizzato da quel momento in poi.
Per l'istruzione insert come descrivi, PostgreSQL passerà sempre al piano generico. Il vantaggio principale è che la query non deve più essere pianificata. Con semplici istruzioni, il tempo di pianificazione può superare il tempo di esecuzione, quindi il risparmio può essere considerevole.
PostgreSQL memorizza nella cache solo i piani di query per le istruzioni e le istruzioni preparate nelle funzioni PL / pgSQL.
L'altro vantaggio delle istruzioni preparate (o parametrizzate) è che evitano il pericolo di SQL injection.
Se devi inserire molte righe, l'utilizzo COPYsarebbe ancora più veloce.