Czy Postgres jest w stanie rozpoznać i „cachować” powtarzające się wyliczenia? Studium przypadku

Ostatnio na liście mailingowej Postgresa pojawiło się interesujące pytanie dotyczące możliwości cachowania powtarzających się wyliczeń. Przypuszczam, że jest to problem, z którym część z Was już się zetknęła, dlatego postanowiłam go przybliżyć – tym bardziej że na wątek odpowiedział niekwestionowany autorytet: Tom Lane.

Do zilustrowania problemu posłużono się zapytaniem widocznym poniżej:

SELECT
   (a + b) * c AS c1,
   (a + b) * d AS d1,
   (a + b) * e AS e1
FROM table;

Dla uproszczenia skomplikowana, powtarzająca się kalkulacja została przedstawiona jako dodawanie do siebie dwóch wartości a+b

Pytanie: Czy Postgres jest w stanie rozpoznać i “cachować” powtarzające się wyliczenia?
Tom Lane: Nie.

Co więc można zrobić, zakładając, że to wyrażenie jest kosztowne? Jak dać znać Postgresowi, że lepiej będzie wykonać je jednokrotnie a wynik przekazać dalej?

Zaproponowano kilka rozwiązań:

1. Może lepiej zastosować podzapytanie?
2. Może przepisać je na CTE?

Ad 1. Analogiczna do powyższego zapytania konstrukcja z użyciem podzapytania wyglądałaby tak:

SELECT
   x * c AS c1,
   x * d AS d1,
   x * e AS e1
FROM (SELECT
     a + b AS x,
     *
   FROM table) x;

Mimo że w widoku logicznym nasza kalkulacja pojawia się tylko raz, pamiętajmy, że język SQL jest językiem deklaratywnym, nie możemy więc założyć, że silnik wykona ją jednokrotnie. Planer ma prawo przekształcić je do dowolnej postaci, w tym wykonać operację zwaną „wypłaszczaniem” (ang. flattening). Będzie to oznaczać przekształcenie zapytania przez silnik do pierwotnej postaci – czyli podstawienie wyliczenia w zapytaniu głównym pod alias w kolumnie. Nie stanie się tak tylko w jednym przypadku, jeśli planer uzna wyliczenie za „wystarczająco drogie”. Jeśli więc wydaje nam się, że wyliczenie jest drogie i za wszelką cenę chcemy uniknąć jego wypłaszczenia, możemy do tego celu wykorzystać klauzulę OFFSET 0:

SELECT
   x * c AS c1,
   x * d AS d1,
   x * e AS e1
FROM (SELECT
     a + b AS x,
     *
    FROM table OFFSET 0) x;

Należy jednak pamiętać, że zastosowanie tego „hacku” spowoduje nie tylko brak wypłaszczenia, oznacza zaniechanie przez planer wszelkich optymalizacji – w tym flatteningu. Niejako „przy okazji” możemy więc uniemożliwić wykonanie optymalizacji o znacznie większej wadze.

Ad. 2
Użycie CTE, czyli konstrukcji z użyciem WITH, o której pisałam w poprzednim artykule, zostało skomentowane przez Toma Lane’a krótko:
To dość tępe narzędzie, którego koszty mogą przewyższyć ewentualne zyski.

Co należy więc zrobić? Jak podejść do problemu?
Sugerowanym przez Toma Lane’a rozwiązaniem jest użycie klauzuli LATERAL:

SELECT
   x * c AS c1,
   x * d AS d1,
   x * e AS e1
FROM tab,
  LATERAL (SELECT a + b AS x
     OFFSET 0) ss;

Konstrukcja z użyciem złączenia LATERAL i podzapytania powinna zapobiec większości problemów optymalizacyjnych. Oprócz jednego: a+b zostanie wykonane dla każdego wiersza z lewej tabeli (“tab”) bez względu na to czy dane użycie będzie wymagać wartości z kolumny x.