PostgreSQL - vynutenie pouzitia jedneho query planu

[ondrej _]

Je mozne PostgreSQL donutit aby v konkretnom query vzdy pouzil rovnaky query plan?

Mam problem s jednym query, kde sa vyhladavaju zaznamy aj podla typu spravy. Zistil som, ze  PostgreSQL pri danom sql query pouziva dva rozne query plany.

Jeden je rychly a druhy velmi pomaly. Pri tom rychlom sa pouziju indexy, ktore boli pre toto query vytvorene. Pri pomalom sa nepouzije a PostgeSQL skenuje celu tabulku za pomoci primarneho kluca(ID je v primarnom kluci)

PostgreSQL ten pomaly query plan pouzije v pripade ked pocet zaznamov s danym typom je nizky alebo celkovy pocet zazamov je nizky.

Priklad.
1.Pouzijem typ spravy XML, ktoreho je v databaze malo a pouzijem maly rozsah datumov aby som prehladaval maly pocet zaznamov. Query je pomale(prehladavanie 15 tis. zaznamov mu trva 5-6s). Postupne zvecsujem rozsah az v urcitom momente sa sql zrychli(cas klesne na uroven jednej skundy). Je to vdaka tomu, ze sa zmenil query plan v ktorom sa okrem ineho pouzije ocakvany index.

2.Ak pouzijem typ spravy JSON, ktoreho je v databaze vecsina, tak sa to chova rovnako az na to, ze sql sa zrychli az ovela neskor.
V prvom pripade sqlko zrychli  na urovni +- 100 tisic zaznamov. V druhom pripade az ked sa pohybujeme v stovkach tisic zaznamov.

Query obsahuje viacere joiny a typ spravy je ulozeny v jsone(stlpec typu jsonb). PostgreSQL je verzie 11.

SQL-ko tu bohuzial nemozem zdielat. Urcite sa da vylepsit ale mam obmedzene moznosti, co si mozem dovolit a som presvedceny ak by PostgreSQL pouzival stale rovnaky query plan, tak by to pomohlo.

Studoval som, hladal som, skusal som ale zatial som neprisiel na to ako presvedcit PostgreSQL aby ten pomaly query plan nepouzival.

Za kazdu radu budem vdacny.

[Reklama]

[Pavel Stěhule]

Těch důvodů proč se použije špatný plán může být vícero. Od špatných odhadů až po nehomogenní uložení dat. Bez znalosti dotazu a prováděcího plánu se nedá říct vůbec nic. Postgres žádné fixování plánů nemá. Pokud nemůžete zveřejnit informace, obraťte se na svůj placený support.

[LeosB]

Zkuste snížit hodnotu random_page_cost

[Pavel Stěhule]

Zkuste snížit hodnotu random_page_cost

Pokud oba plány používají index, tak to nepomůže. Navíc pokud nemáte SSD, tak je snížení random_page_cost dost problematické, něco zrychlí, něco může zase fatálně zpomalit. Základem je pochopit proč se použije špatný plán, slepě mačkat všechna tlačítka většinou nepomůže.

[jjrsk]

Ze pry bys nemel, ale muzes pouzit SET SESSION enable_seqscan=false

[Reklama]

[Karel Karlik]

První co mi nedá: je to opravdu pokaždé textově stejné SQL (a ty vyhledávací parametry se předávají nabindovanou proměnnou), nebo se do něj parametry stringově naconcatujou?
Tj "select ... where datum between ? and ?" vs "select ... where datum between to_date('2023-01-01') and to_date('2023-02-01')"
(jestli to není stejný string, tak se klidně může nacacheovat úplně jiný plán ...)

[Pavel Stěhule]

První co mi nedá: je to opravdu pokaždé textově stejné SQL (a ty vyhledávací parametry se předávají nabindovanou proměnnou), nebo se do něj parametry stringově naconcatujou?
Tj "select ... where datum between ? and ?" vs "select ... where datum between to_date('2023-01-01') and to_date('2023-02-01')"
(jestli to není stejný string, tak se klidně může nacacheovat úplně jiný plán ...)

Bacha, Postgres není Oracle. Nemá implicitní plan cache.

[LeosB]

Zkuste snížit hodnotu random_page_cost

Pokud oba plány používají index, tak to nepomůže.

Já jsem z toho jak je to popsané pochopil že index se v jednom plánu nepoužívá a tento plán se volí se dokud je v tabulce málo záznamů. To je přesně případ ve kterém mi pomohlo snížit hodnotu random_page_cost - samozřejmě netvrdím že to pomůže i v tomto případě, ale připadá mi že by stálo za to to zkusit.

[Pavel Stěhule]

Zkuste snížit hodnotu random_page_cost

Pokud oba plány používají index, tak to nepomůže.

Já jsem z toho jak je to popsané pochopil že index se v jednom plánu nepoužívá a tento plán se volí se dokud je v tabulce málo záznamů. To je přesně případ ve kterém mi pomohlo snížit hodnotu random_page_cost - samozřejmě netvrdím že to pomůže i v tomto případě, ale připadá mi že by stálo za to to zkusit.

Ja to pochopil jinak - viz věta "Pri pomalom sa nepouzije a PostgeSQL skenuje celu tabulku za pomoci primarneho kluca(ID je v primarnom kluci)". Nicmeně jelikož tazatel nemůže zveřejnit prováděcí plány, tak stejně všichni tu střílíme naslepo, což je jen ztráta času. Kdo ví, co tam je za problém - také to může být nezvakuovaná nebo bloatnutá tabulka, nebo chybějící více sloupcová statistika.

[ondrej _]

Oba plany pouzivaju index.
V pripade pomaleho query

Kód: [Vybrat]

Index Scan using dep_document_pkey on dep_document  (cost=0.29..0.36 rows=1 width=16) (actual time=0.234..0.234 rows=0 loops=160742)

V pripade rychleho queru

Kód: [Vybrat]

Bitmap Index Scan on idx_doc_type_format  (cost=0.00..80.48 rows=1607 width=0) (actual time=0.531..0.531 rows=4115 loops=1)

Podotykam, tie query plany su dost odlisne.
Ako som pisal, tak od urciteho mnozstva zaznamov sa pouziva ten druhy query plan. Kedy zalezi od typu, ktory hladam. Ak je to menej bezny typ, tak sa to preklopi skor.

Je to zakazdym textovo rovnake SQL. Zmenim typ hladaneho dokumentu a pouzije sa iny plan.

Hladany typ je ulozeny v JSONB stlpci a dany index obsahuje kombinaciu troch klucov z json-u ukladaneho do tohto stlpca.
V sqlku su este dalsie podmienky s inymi klucmi z jsonu. 3 podmienky sa zhoduju s tymi v indexe a jedna podmienka nie.
Prvom pomalom pripade spoji vsetky podmienky a vyhladava zaznamy, ktore splnaju vsetky podmienky v jednom kroku za pomoci primarneho kluca.
V druhom pripade je to rozdelene na 2 kroky. V prvom kroku pouzije index a 3 podmienky s klucami, ktore su v danom indexe. Pomocou toho indexu odfiltruje zaznamy, co trva velmi kratko. Nasledne este odfiltruje zaznamy podla zostavajucej podmienky, co je tiez rychle.

Nemam ziadne skusenosti s indexami a hladanim v JSON-e. Vypisal som si aj statistiky a vyzera, ze udrzba tabuliek pravidelne prebieha.
Spustil som ju aj rucne a nepomoholo.

Postupil som s danym problemom
Podarilo sa mi upravit SQL-ko. Skusal som rozne kombinacie. Pri pouziti subselectu sa uz stale pouzivaju ocakavane indexy a zaroven sa SQL zrychlilo. Zrychlilo sa vo vsetkych pripadoch. Nove SQL je dokonca rychlejsie aj ako povodne, ked sa pouzivali indexy.
Nove SQL vyzera hrozne/zlozitejsie oproti povodnemu ale funguje to.

Rad by som sa ale dozvedel viacej o PostgreSQL a jeho spravani sa v takomto pripade. Viete mi poradit dobry zdroj, ktory sa zaobera touto tematikou?

[Pavel Stěhule]

Muzete ukazat cele provadeci plany? https://explain.depesz.com/ umi anonymizaci. Evidentne tam mate nested loop nad primarnim klicem. Zkontrolujte si jestli nemate ten index bloatly, pripadne muzete penalizovat nested loop - `set enable_nestloop to off`. Z toho zlomku, co jste ukazal by mozna mohl pomoct podmineny index. Postgres pri odhadech nejde na 0, nicmene ve vasem pripade tam ty hodnoty, ktere se hledaji, evidentne nemate.

V zasade jde o to pochopit, jak funguje optimalizace dotazu - v pripade modernich SQL databazi optimalizace zalozena na statistikach, a v cem a jak muze tato optimalizace chybovat. A pak se snazite a) eliminovat chyby, nebo b) vytvarite prostor nebo zuzujete prostor pro optimalizaci. Jednodussi dotaz vytvari mensi prostor pro optimalizaci, komplexnejsi vetsi prostor pro optimalizaci. Na internetu nic moc uceleneho nenajdete, alespon ja o tom nevim. Jsou dobre prezentace, ale vetsinou zamerene na jeden konkretni problem (protoze se vetsinou musite vejit do limitu 50 nebo 90 minut). Videl jsem par knizek, a mam i "PostgreSQL Query Optimization, Dombrovskaya, Novikov, Bailliekova", ktera je slusna a je urcena pro zacatecniky (jak znam typicke znalosti vyvojaru pouzivajicich databaze, tak vyjma par expertu, jsou prakticky vsichni vecni zacatecnici). Pak je to o zkusenostech. Ja se tim zivim. Budto skolim, anebo resim performance problemy. Za 20 let uz si pak clovek udela docela prehled (a jeste se motam kolem vyvoje Postgresu, i kdyz hodne daleko k optimalizaci). Tady v CR (i svetove) je spicka Tomas Vondra, ktery napsal do Postgresu podporu vicesloupcovych statistik, ted se mota kolem BRIN indexu a replikace. Muzete zkusit hledat nejake jeho prezentace na netu.

[Pavel Stěhule]

Oba plany pouzivaju index.
V pripade pomaleho query

Kód: [Vybrat]

Index Scan using dep_document_pkey on dep_document  (cost=0.29..0.36 rows=1 width=16) (actual time=0.234..0.234 rows=0 loops=160742)

Ten index scan u vás není extra rychlý - ještě bych tabulku zvakuoval (což by se mělo dělat jako první krok při řešení performance problému), a pak zkontroloval nastavení shared_buffers a random_page_cost. Tak se může chovat podstřelené nastavení random_page_cost. Na mém notebooku mám o 2 řády rychlejší čas.

[ondrej _]

Ak ich je mozne anonymizovat, tak ich ukazem. Mal by som ma niekde zjednodusenu verziu orginal SQL, kde sa ten problem rovnako prejavoval.

To bola vzorka z uz neviem presne akej verzie. Skusal som rozne hodnoty a porovnaval plany ked som sa snazil pochopit preco sa to tak chova.

(jak znam typicke znalosti vyvojaru pouzivajicich databaze, tak vyjma par expertu, jsou prakticky vsichni vecni zacatecnici).

S tymto musim suhlasit. Motal som sa trochu okolo databaz v minulosti a teraz po rokoch som sa znovu k tomu dostal a po tyzdni studia, skusania si pripadam ako expert oproti kolegom vratane senior/tech lead.

Ten index scan u vás není extra rychlý - ještě bych tabulku zvakuoval (což by se mělo dělat jako první krok při řešení performance problému), a pak zkontroloval nastavení shared_buffers a random_page_cost. Tak se může chovat podstřelené nastavení random_page_cost. Na mém notebooku mám o 2 řády rychlejší čas.

Vacuum bola prva vec, ktoru som skusil. Nemalo to ziaden vplyv. Skusal som aj updatnut statistiky, ci PostgreSQL si to potom nerozmysli, rovnako bez vysledku. Nastavenia databazy som neriesil. Databaza bezi neviem kde a neviem na com. Riesit to by bolo na 2 tyzdne vysvetlovania a spekulovania ci to je nutne a obav z produkcneho nasadenia akejkolvek takejto zmeny. Je to aj z dovodu vyssie spomenuteho developerskeho vecneho zaciatocnictva a databazy vnimanej ako nutne zlo.
Mam obmedzene moznosti a tak sa snazim s tym urobit co sa da.

[Pavel Stěhule]

Vacuum bola prva vec, ktoru som skusil. Nemalo to ziaden vplyv. Skusal som aj updatnut statistiky, ci PostgreSQL si to potom nerozmysli, rovnako bez vysledku. Nastavenia databazy som neriesil. Databaza bezi neviem kde a neviem na com. Riesit to by bolo na 2 tyzdne vysvetlovania a spekulovania ci to je nutne a obav z produkcneho nasadenia akejkolvek takejto zmeny. Je to aj z dovodu vyssie spomenuteho developerskeho vecneho zaciatocnictva a databazy vnimanej ako nutne zlo.
Mam obmedzene moznosti a tak sa snazim s tym urobit co sa da.

Ono jde o to, že pokud máte default shared_buffers 128MB a ta tabulka se do této velikosti nevejde, navíc běžíte na kdoví jakém železe, jak sám píšete, tak index scan může být dost pomalý. Navíc si někdo může přečíst radu typu sniž random_page_cost třeba na nějaký extrém typu 1, a pak optimalizátor může dělat dost divné věci. To jestli je dotaz rychlý ovlivňuje prováděcí plán, aktuální zdraví tabulek a indexů a částečně i konfigurace. Ta konfigurace spíš v extrémech - při rozumném nastavení a rozumném železe nemá velký vliv, ale setkáte se s šílemým nastavením (které u některých dotazů pomůže, ale u jiných hodně uškodí), a setkáte se i s šíleným železem (patologicky poddimenzované železo nebo divně nízký výkon IO nebo CPU (u virtualizace je to docela časté)).

[ondrej _]

Tu je pomaly query plan
https://explain.depesz.com/s/VuoL
Tu je rychly query plan
https://explain.depesz.com/s/UALc
 
Rozdiel medzi nimi je ten, ze sa zmenil len vyhladavany typ dokumentu.

Podotykam, ze sa jedna o zjednodusenu verziu povodneho SQL, kde sa to chova rovnako.