Vlákno názorů k článku PostgreSQL 12 – bude rok 2019 rokem Postgresu? od anonym - Dobrý deň. Chcel by som k CTE a ich...

Dobrý deň.

Chcel by som k CTE a ich fungovaniu / zmenám pridať aj svoj pohľad / skúsenosť.

Používame (s kolegami) ich často, aj pre bežné nerekurzívne dotazy. Sú pre nás intuitívnejšie na členenie / pochopenie / udržovanie kódu, aj keď to je do istej miery vec osobnej preferencie a zvyku.

Samozrejme máte pravdu v tom, že keď niekde spôsobujú väčšie než zanedbateľné spomalenie, tak to píšeme / členíme podľa potreby aj iným spôsobom.

Avšak to, že sa CTE materializuje, sa mi zdá intuitívne správne, práve to by som čakal.

Nie je to predsa "common view expression" alebo "common subquery definition for reuse".

A nie je to čiste kozmetická vec týkajúca sa len preferencií členenia kódu:

1) Keď sa vo vnútri CTE používajú funkcie / výrazy, ktoré pri viacnásobnom volaní nevracajú rovnaký výsledok.

Je to rovnaký príncíp, ktorý ste uviedli ako dôvod v kapitole "Generované sloupce" -

"Aby byla zajištěna konzistence mezi uloženými a virtuálními počítanými sloupci, tak (podle ANSI) jsou umožněny pouze výrazy, které jsou (terminologií Postgresu) neměnné (immutable)".

T.j. ak v CTE použijem napr. random(), pri viacnásobnom inlinovaní tohto CTE nebude jeho obsah rovnaký.

2) Sú prípady použitia, keď pri definovaní jednotlivých CTE v rámci celého bloku / sekvencie CTE (tvoriacich jeden dotaz) je dôležité aj poradie ich definície.

Toto sa inlinovaním poruší / nedodrží. V rýchlosti ma napadajú napr. časové stopy (clock_timestampt() ), ale určite budú existovať aj iné prípady.

To súčasné kritérium, ktoré uvádzate, "jedno použitie - inlinuj, viac použití - materializuj", by zatiaľ stačilo pre "ošetrenie" prípadu 1) , ale už nie prípadu 2) .

Neviem, či sa do budúcna toto kritérium bude upravovať. Ale očakával by som, že zrýchlenie / optimalizácia nebude jediným, ani prvoradým kritériom.

Z môjho pohľadu to najprv má fungovať správne (myslené ako doterajší / existujúci kód pod novou verziou), potom až sledujem rýchlosť.

26. 6. 2019, 13:20 editováno autorem komentáře

Možná jste přehlédl, že jsem v textu psal, že pokud je relace referencovaná více krát, tak automaticky dojde k materializaci. Tudíž problémy s random() nebo clock_timestamp nemohou nastat. Automatický inline je pouze tehdy, když je relace použitá pouze jednou.

ANSI SQL vůbec nic neříká o optimalizaci a inlining jednou použité reference mně osobně dává větší smysl než materializace. Vývojáři této funkce spíš z jednoduchosti a konzistence implementace inlining vůbec nebrali v potaz - a časem se používání nerekurzivního CTE v Postgresu stalo určitým typem hintu - v podstatě jediným jednoduchým způsobem, jak si vynutit materializaci, a případně jak zahodit odhady, pokud byly chybné. Inlining se v tomto kontextu vůbec neuvažoval - v případě, že by chtěl někdo inlining, tak prostě napsal derivovaný poddotaz.

Všechny jiné databáze ovšem zvolily jinou strategii - a jelikož se v posledních 5 letech intenzivně začalo migrovat z těchto databází do Postgresu, tak se začalo ukazovat, že ta jednoduchá implementace "materializace vždy" je příliš jednoduchá - a nedává smysl (pro pouze jednou referencované CTE). To nikdo nerozporoval - problém byl jen, jak minimalizovat impakt na stávající uživatele - jak moc porušit svá pravidla - defacto se jedná o hint. Drtivé většině uživatelů by inline měl spíše pomoc, ale určitě budou existovat uživatelé, kteří CTE použili kvůli zahození odhadů, a těm se výpočet může zhoršit.

Nevím o tom, že by ve standardu bylo něco o garanci pořadí provedení CTE. To i při materializaci pokud tam nemáte závislosti, tak se teoreticky může vykonávat v náhodném pořadí - teoreticky i např. v jiném procesu, kdy se bude synchronizovat pouze výsledek (což zatím není implementováno, ale bere se to jako možnost).

Porušení zpětné kompatibility je vždy problém, a vývojáři to neberou na lehkou váhu. Vždy někoho naštvete - noví uživatelé preferují co nejmodernější implementaci (optimalizaci) pokud možno kompatibilní se světem. Stávající uživatelé preferují kompatibilitu. V tomto případě by asi nejčastějším nepříjemným efektem mohlo být zpomalení některých dotazů (ale to je v každé verzi, že cca 2-5% dotazů je v nové verzi pomalejší v důsledku jiné optimalizace, jinak počítaných statistik, odhadů). Počítat jinak by to nemělo, pokud ano, tak bych to spíš než na bugu viděl jako zákaznický kód, který se pohybuje v zóně nejednoznačně definovaného chování, který v nějaké verzi fungovat může, ale v další nemusí (v každé verzi Postgresu se hodně mění optimalizátor, executor, ..)

Neprehliadol, ale skúsim vysvetliť lepšie, ako som to myslel.
Rozdiely, ktoré si myslím, že môže spôsobiť inlinovanie CTE oproti materializácii som vysvetlil v bodoch 1) a 2) . Tiež som uviedol (tu súhlasím s Vami) že to pravidlo "jedno použitie - inlinuj, viac použití - materializuj" stačí na to, aby sa problém 1) nevyskytol. Ale to pravidlo ("jedno použitie - inlinuj, viac použití - materializuj") nestačí, aby sa ošetril alebo zamedzil výskyt problému podľa 2) , t.j. poradie, v akom sa vyhodnotia (napr. ten clock_timestamp() ). Príklad - skúšal som ho vo verzii 11, tak ako chápem a predpokladám, že to bude fungovať vo verzii 12 (bohužiaľ neviem, ako to lepšie naformátovať v komentári):

with tmp_1 as
(
select clock_timestamp() as cas_zaciatku
),
tmp_2 as
(
/* simulacia casovo narocnej casti (v reale tu moze byt viacero CTE, nez sa dopracujem k finalnemu dotazu a casu), parameter v generate_series sa da upravit pre viditelnejsie casove rozdiely */
select *,
row_number() over(order by 0 = 1) as nejaka_podmienka
from generate_series(1, 10000000) as gs
)
select
t1.cas_zaciatku as cas_zaciatku_ma­terialise,
clock_timestamp() as cas_zaciatku_inline
from tmp_1 t1
join tmp_2 t2 on
t2.nejaka_podmienka = 1
;


Rozumiem, a nerozporujem, že s novými verziami sa rýchlosť / plánovanie / optimalizácia mení. Len v tomto prípade chcem poukázať na to, že doterajší kód už môže fungovať nielen pomalšie, ale aj s inou logikou.
Ak pre uvedený príklad (hore) s clock_timestamp() doteraz vždy bol naplnený čas začiatku spracovania, tak zaručoval aj poradie (naplnených dát) podľa tohto času začiatku. Po novom už to neplatí - zoradené to bude podľa toho, čo skôr dokončí spracovanie (t.j. kde sa inlinované CTE vo výslednom dotaze dostane do spracovania skôr).

Ten Váš příklad dává na 11 i na 12ce téměř stejné výsledky

-- 12
┌─────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                             QUERY PLAN                                              │
╞═════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Nested Loop  (cost=0.02..375625.04 rows=50000 width=16)                                             │
│   CTE tmp_1                                                                                         │
│     ->  Result  (cost=0.00..0.01 rows=1 width=8)                                                    │
│   ->  CTE Scan on tmp_1 t1  (cost=0.00..0.02 rows=1 width=8)                                        │
│   ->  Subquery Scan on t2  (cost=0.00..375000.00 rows=50000 width=0)                                │
│         Filter: (t2.nejaka_podmienka = 1)                                                           │
│         ->  WindowAgg  (cost=0.00..250000.00 rows=10000000 width=13)                                │
│               ->  Function Scan on generate_series gs  (cost=0.00..100000.00 rows=10000000 width=1) │
└─────────────────────────────────────────────────────────────────────────────────────────────────────┘
(8 rows)

-11
┌─────────────────────────────────────────────────────────────────────────────────────────┐
│                                       QUERY PLAN                                        │
╞═════════════════════════════════════════════════════════════════════════════════════════╡
│ Nested Loop  (cost=25.01..47.60 rows=5 width=16)                                        │
│   CTE tmp_1                                                                             │
│     ->  Result  (cost=0.00..0.01 rows=1 width=8)                                        │
│   CTE tmp_2                                                                             │
│     ->  WindowAgg  (cost=0.00..25.00 rows=1000 width=13)                                │
│           ->  Function Scan on generate_series gs  (cost=0.00..10.00 rows=1000 width=5) │
│   ->  CTE Scan on tmp_1 t1  (cost=0.00..0.02 rows=1 width=8)                            │
│   ->  CTE Scan on tmp_2 t2  (cost=0.00..22.50 rows=5 width=0)                           │
│         Filter: (nejaka_podmienka = 1)                                                  │
└─────────────────────────────────────────────────────────────────────────────────────────┘
(9 řádek)

Implementované je to jinak, ale chování je stejné (v tomto případě) Jakýkoliv dotaz, který by byl závislý na pořadí provádění je koledování si o průser.

Snažím sa pochopiť Vaše komentáre, zatiaľ bez úspechu. Uviedol som príklad, na ktorom ukazujem, že výsledky sú odlišné (aj keď boli len simulované vo verzii 11 tak, ako si predstavujem, že bude fungovať verzia 12, to uznávam a uviedol som).
Vy ste uviedli, že sú to "téměř stejné výsledky". Veď "téměř stejné" znamená nie stejné, a teda odlišné, teda to, čo sa snažím ukázať.
Ďalšia vec - hovoríte "výsledky", ale pripojili ste exekučné plány. O plánoch nehovorím, nerozumiem, s čím tu súvisia.
S tým, že poradie nezávislých dotazov v rámci CTE nie je garantované, to je mi jasné, uznávam, že som z tohto pohľadu nezvolil správny príklad.

Mám už teraz nainštalovanú betu verzie 12 (výpis z funkcie version: PostgreSQL 12beta2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit) .
Chcel som si priamo na nej otestovať a možno pripraviť príklad, ktorý by lepšie ilustroval, čo chcem povedať.
Teraz mám ale problém nasimulovať funkčnosť CTE pre NOT MATERIALIZED variantu (inline).

with tmp_1 as not materialized
(
  select
    pg_sleep(3) as sleep,
    clock_timestamp() as cas
)
select * from tmp_1 union all
select * from tmp_1
;

Tu to vráti rovnaké časy, a dotaz beží 3 sekundy, z toho usudzujem, že sa CTE pustilo iba raz, a chová sa rovnako, ako keby bolo materializované.

select * from
  (
    select
      pg_sleep(3) as sleep,
      clock_timestamp() as cas
  ) tmp_1
union all
select * from
  (
    select
      pg_sleep(3) as sleep,
      clock_timestamp() as cas
  ) tmp_1
;

Keď obsah toho CTE inlinujem ručne, tak už to vráti rôzne časy, a beží to 6 sekúnd (t.j. oba 3-sekundové sleepy).
Ako teda simulovať CTE, ktoré sa bude chovať ako naozaj inlinovaný kód ?