Výkonnostní spiky v trvání queries v Postgres

[Logik]

Rozdíl je v tom, že druhý dotaz najde v indexu záznamy v daném rozsahu, a pak do primárního souboru jde jen pro data z daného rozsahu.

První dotaz musí projít celý index, aby zjistil, který záznam je číslo 100000. Mám za to, že musí navíc i do primárního souboru, aby ověřil, které záznamy jsou pro danou transakci viditelné (a platné, tedy které má do těch 100000 počítat). A pak teprv začne číst ty záznamy, které má vrátit.

[Reklama]

[registrovany123]

Rozumím, dobře, díky za vysvětlení.

Takže příště stránkování se budu snažit dělat by date nebo něco podobného, a nikoliv by offset. Tím sice nejsem schopen pohlídat velikost stránky, ale může se to přesto v určitých chvílích hodit.

[Pavel Stěhule]

Ve druhém případě však musí udělat totéž: musí si proiterovat date_index, zjistit, které záznamy zplňují podmínku, a potom si je vytáhnout z tabulky Orders.

To vyžaduje trochu vysvětlení, proč je v tom případě OFFSET antipatern.

Vyhledávání rozsahu je operace indexem podporovaná. Pokud tam máte index, tak na 3-4 seek to najde začátek v stovkách miliónů záznamů.

Můžete si vyzkoušet, kolik Postgres přečte diskových stránek - použijte EXPLAIN (ANALYZE, BUFFERS) SELECT ...

Postgres vám vypíše, kolik která operace načetla diskových bloků. Případně si můžete vynutit index scan (pro porovnání) nastavením SET enable_seqscan TO OFF;

Zlaté stránky - tel seznam můžete vzít jako jednoduchou databázi. Když vám řeknu, abyste mi našel které číslo je na 10000 řádku, tak mne pošlete do pr... nebo to budete počítat měsíc (pokud nemají stránky fixní počet tel čísel - což neměli někdy tam byla třeba reklama nebo půl stránky prázdné). Když vám řeknu abyste tam našel Stěhuleho z Benešova, tak to bude pro vás otázka minuty s rezervou. Databáze fungují plus mínus podobně.

[snugar_i]

Jak říkám, cíl je stránkovat přes ty záznamy a vracet je přes API - to je cíl - není cíl se kochat jak se DB nenadře - máte business requirement na vaši službu a potřebujete implementovat stránkování. Buďto přes OFFSET a když je to teda antipattern, dobrá, tak tedy přes date.

To by nebylo špatný říct hned na začátku. Že vlastně nepotřebuješ OFFSET 500000, ale stránkování. Bez offsetu se dá stránkovat pomocí tzv. cursor-based pagination - místo abych v příštím dotazu řekl "chci stránku 10 s 50 záznamy na stránku", tak řeknu "Chci dalších 10 záznamů po X" (kde X je hodnota poslední položky na mojí stránce, podle které to mám seřazený)

[Logik]

snugar:
To není tak jednoduchý, když např. potřebuješ, aby ta stránka měla persistentní URL.

[Reklama]

[Pavel Stěhule]

snugar:
To není tak jednoduchý, když např. potřebuješ, aby ta stránka měla persistentní URL.

tak za předpokladu, že se nemaže z db (aby to persistentní URL mělo smysl), tak pak bych si pomohl jednoduchou pomocnou tabulkou, kde bych mapoval číslo stránky na rozsah id, a pak přes to už vyhledával. Perzistentní číslo stránky mi ale přijde dost nepraktický - to už bych raději do URL zakódoval rozsah id. Tam ještě záleží o jakých velikostech se bavíme - OFFSET do nižších desítek tisíc řádek bude mít akceptovatelnou režii - což je nějakých 100 stránek po sto záznamech. Pokud chcete použít stránkování na tabulku o 1Mřádek, tak tam je OFFSET nepoužitelný, plus z hlediska UI je to už 1000 stránek, což mi také nepřijde použitelné - a pokud by data žrala nějaká aplikace, tak ta ať si říká o rozsah - a maximálně zkontroluji, jestli na jeden get si neřekla o příliš řádek.

Před lety jsem viděl jednu monitorovací aplikaci, s šíleným UI, kde se zobrazovalo nějakých 720 stránek - a uživatelé věděli, že na každý den připadá 24 stránek a samy si překódovali, kterou chtějí stránku podle toho z které hodiny, kterého dne měsíce chtějí vidět data. Nicméně UI naprosto na palici. Jinak požadavek na stránkování s pevnou velikostí stránky je dost často důsledkem přenesení UI ze souborových databází do web appek. Ale u těch souborových databází to bylo relativně laciná operace (fixní délka věty)  a v těch starých databázích bylo málokdy víc než pár set tisíc záznamů, takže tam nebyla žádná extra režie.

[luvar]

snugar:
To není tak jednoduchý, když např. potřebuješ, aby ta stránka měla persistentní URL.

Ak je možnosť, tak upraviť API tak, aby to bolo REST. Teda, napriklad po nejakom vyhladavani (napriklad ked otvorime google a napiseme tam daco a klikneme enter), tak nam pride odpoved. V odpovedi mame linky na dalsie stranky a dane linky si neupravujeme v url rucne podla nejakeho magickeho pravidla typu "&page=%number+1", alebo "&stránočka=%number+1", ale proste klikneme na linku, ktorá má názov "next", "previous", "first", alebo "last". A v tej linke moze byt co potrebujete. Kludne aj odkaz na ini server.

Tuto vlastnost rest api zacinaju preberat rozne api, ako je napriklad apicko gitlab-u. am napriklad ale neposielaju url linky v obsahu samotnej odpovede, ale v headroch http. Aj to je mozne riesenie.

Kusok ustabilizovanejsie api sa da dosiahnut pouzitim standardu ako je HAL napr. Vid ukazku (je to kusok ukecanejsie): https://stateless.group/hal_specification.html

[registrovany123]

Jak říkám, cíl je stránkovat přes ty záznamy a vracet je přes API - to je cíl - není cíl se kochat jak se DB nenadře - máte business requirement na vaši službu a potřebujete implementovat stránkování. Buďto přes OFFSET a když je to teda antipattern, dobrá, tak tedy přes date.

To by nebylo špatný říct hned na začátku. Že vlastně nepotřebuješ OFFSET 500000, ale stránkování. Bez offsetu se dá stránkovat pomocí tzv. cursor-based pagination - místo abych v příštím dotazu řekl "chci stránku 10 s 50 záznamy na stránku", tak řeknu "Chci dalších 10 záznamů po X" (kde X je hodnota poslední položky na mojí stránce, podle které to mám seřazený)

Jo rozumím, na to potřebuju mít číslo záznamu a to musí být rostoucí, to je doable. Akorát teda když user přijde k UI a chce udělat jump v tabulce záznamů na stránku 100, tak se musí stejně proiterovat všechny předchozí stránky, aby si ono číslo záznamu na stránce 100 získal. Ale na některé věci se ten tvůj pagination určitě hodí víc.

[Zopper]

Nenasel jsem v diskuzi. Meni se ty zaznamy z hlediska radicich sloupcu (treba lastModified), pripadne vklada se doprostred seznamu, nebo je to insert-only (sloupecek created)?

Mozna by sel pouzit ohejbak na rovnak a proste si pridat numericke id jako sloupecky pro jednotlive typy razeni, kde se pri vkladani udela MAX(sorting_by_date) +1 a nad tim se pak uz da rucne skocit na X-ty zaznam pres where.

[Death Walker]

Akorát teda když user přijde k UI a chce udělat jump v tabulce záznamů na stránku 100, tak se musí stejně proiterovat všechny předchozí stránky, aby si ono číslo záznamu na stránce 100 získal.

Pre uzivatela moze byt skok na konkretnu stranku uzitocny iba vtedy ak tam najde hodnoty ktore potrebuje. To je mozne iba vtedy ak sa zaznamy v tabulke nemenia. Kazdy insert, update alebo delete zmeni poziciu udajov tak, ze offset udajov ktore ho zaujimaju sa zmeni. Ak mas uzivatela, ktory vie ake data potrebuje, tak daleko vhodnejsi je normalny filter (where). Ak nevie ake udaje potrebuje, len vie ze potrebuje udaje zo stranky 100, tak mu je zrejme jedno co mu naservirujes.

A preco DB nerobi seek na OFFSET podla INDEXU? Proste index je strom. Vo vacsine pripadov. Vetvy obsahuju rozsahy s odkazmi na dalsie vetvy a az na koniec na listy kde su konkretne hodnoty s odkazmi na zaznamy. Je to idealne ked potrebujes dostat odkaz na zaklade hodnoty. Seek na poziciu je vhodny ak mas data ulozene ako vektor (tak je ulozena tabulka).

Ta magia by sa dala realizovat cez pomocnu tabulku ktora by obsahovala pravidelne aktualizovany histogram hodnot, na zaklade ktorych vies povedat aky rozsah hodnot pripada na konkretnu "stranku".

Ale podla mojej skusenosti je daleko jednoduchsie vysvetlit uzivatelom, ze by mali vediet ake parametre maju zadat do filtra aby sa dostali k udajom ktore potrebuju. Zvysi to jednak ich efektivitu prace, tak aj tvoju ked nebudes musiet vymyslat algoritmy ktore by suplovaly nedostatky uzivatelov. Pretoze v pripade zivej DB kde sa hodnoty pravidelne menia je poziadavka typu "Chcem stranku 100, pretoze pred tyzdnom tam boli hodnoty ktore chcem vidiet!' je neskutocne dementna.

A este preco ten dotaz ktory si pouzil nie je efektivny.
Pozri si plan toho dotazu. Pouzi napr. pgadmin, ten ti tu textovu informaciu o plane prevedie aj na graf. Co sa tam teda deje:

• Planovac vytvori plan ktory je optimalny podla statistik. Na jeho vystupe je kompletna sada udajov, z dovodu v 2.
• Udaje z 1. spracovava iterator pre sort. Samozrejme ze z 1. potrebuje uplne vsetky hodnoty. Tak sort proste funguje. Je irelevantne ci ma k dispozicii index alebo nie.
• Teraz pride na radu iteratot pre offset, konzumuje data a zahadzuje ich kym nenapocita pozadovany ofset. Ked ma pozadovany ofset tak data posiela na vystup, kym pocitadlo nedosiahne hodnoty offset+limit. Potom konci, a oznami predhadzajucemu iteratoru ze uz dalsie data nepotrebuje cim ukonci jeho cinnost, rovnako ako aj pracu dalsich iteratorov v retazi pred nim.

Ak by mal iterator v 3. ovplyvnovat iteratory v 1. tak na to nedokazes napisat planovac, ktory by nemal natvrdo nakodovane varianty toho ako je mozne napisat dotaz a ku kazdej variante zvlast exekutor.