Dlouho trvá první volání dotazu s určitými parametry - jak postupovat při optimalizaci

[Jan Michálek]

Ahoj

Potřeboval bych nasměrovat. Mám dotaz (geokódování), kterej potřebuju aby fakt svištěl (sekunda je už příliš), první volání dotazu trvá neúměrně dlouho (desítky sekund). Předpokládám (a uvítám, když mi to někdo potvrdí), že si postgre potřebuje nasyslovat řádky do ramky.

Jaký mi to dává vodítka pro optimalizaci? Předpokládám, že by mi mělo pomoct na začátku co nejvíce ořezat vstupní poddotaz. Je to správná úvaha?

Díky Je;

--

Jelen

Starší čeledín datovýho chlíva

[Josef Šimánek]

po 21. 2. 2022 v 13:58 odesílatel Jan Michálek <godzil...@gmail.com> napsal:
>
> Ahoj
ahoj

> Potřeboval bych nasměrovat. Mám dotaz (geokódování), kterej potřebuju aby fakt svištěl (sekunda je už příliš), první volání dotazu trvá neúměrně dlouho (desítky sekund). Předpokládám (a uvítám, když mi to někdo potvrdí), že si postgre potřebuje nasyslovat řádky do ramky.

Co vím, tak PostgreSQL si může při tom prvním dotazu teprve načítat
data do "shared buffers". Pokud zavoláš EXPLAIN ANALYZE s BUFFERS ON
tak při prvním a dalších voláních by to tam mělo bejt vidět. Připojuju
ukázku dvou EXPLAINů.

Seq Scan on users (cost=0.00..9317.93 rows=493 width=826) (actual
time=0.050..142.198 rows=497 loops=1)
Buffers: shared hit=209 read=9104 # << tady je vidět že se data
načítaj z disku do shared buffers
Planning:
Buffers: shared hit=164
Planning Time: 1.446 ms
Execution Time: 142.293 ms

Seq Scan on users (cost=0.00..9317.93 rows=493 width=826) (actual
time=0.022..14.335 rows=497 loops=1)
Buffers: shared hit=9313 # << data se čtou pouze ze shared buffers,
není třeba číst z disku
Planning Time: 0.108 ms
Execution Time: 14.432 ms

> Jaký mi to dává vodítka pro optimalizaci? Předpokládám, že by mi mělo pomoct na začátku co nejvíce ořezat vstupní poddotaz. Je to správná úvaha?
>
> Díky Je;
>
> --
> Jelen
> Starší čeledín datovýho chlíva
>

> --
> Tuto zprávu jste obdrželi, protože jste přihlášeni k odběru skupiny „PostgreSQL-cz“ ve Skupinách Google.
> Chcete-li zrušit odběr skupiny a přestat dostávat e‑maily ze skupiny, zašlete e-mail na adresu postgresql-c...@googlegroups.com.
> Chcete-li tuto diskusi zobrazit na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/CAAYBy8Yfzk1sY6GJVK97iNxtfETK_On2%2BHmahZ72mYnqgk%3D%3DVw%40mail.gmail.com.

[Jan Michálek]

Díky

Čiliže, když tady mam třeba

Buffers: shared hit=15787 read=559

Tak to znamená, že necelejch 16k řádků našel ve shared buffers? Tj, kdybych otočil server (což nemůžu), tak tam bude nula?

S timhle mi to běží asi sekundu (přepsal jsem dotaz na lateral a dolepil nějaké indexy).

Nícméně 16k řádků je fakt ranec a tolik by jich neměl potřebovat.

Je;

po 21. 2. 2022 v 14:05 odesílatel Josef Šimánek <josef....@gmail.com> napsal:

Chcete-li zobrazit tuto diskusi na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/CAFp7Qwp84mGx6USMJ9EFJZbLs2JoE8SKAR8-u8gWhX09JosGGw%40mail.gmail.com.

[Pavel Stehule]

po 21. 2. 2022 v 14:45 odesílatel Jan Michálek <godzil...@gmail.com> napsal:

Díky

Čiliže, když tady mam třeba

Buffers: shared hit=15787 read=559

Tak to znamená, že necelejch 16k řádků našel ve shared buffers? Tj, kdybych otočil server (což nemůžu), tak tam bude nula?

S timhle mi to běží asi sekundu (přepsal jsem dotaz na lateral a dolepil nějaké indexy).

Nícméně 16k řádků je fakt ranec a tolik by jich neměl potřebovat.

to neni 16K radku, ale 16K * 8K bajtu

jedna datova stranka je 8KB, a ty jsi jich precet 15787

Pavel

Chcete-li tuto diskusi zobrazit na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/CAAYBy8amB7K0A9qHhtVU%3D_4-0%2B2LhxJKozAFT-ex8N6gsY%2BiAw%40mail.gmail.com.

[Jan Michálek]

po 21. 2. 2022 v 15:19 odesílatel Pavel Stehule <pavel....@gmail.com> napsal:

po 21. 2. 2022 v 14:45 odesílatel Jan Michálek <godzil...@gmail.com> napsal:

Díky

Čiliže, když tady mam třeba

Buffers: shared hit=15787 read=559

Tak to znamená, že necelejch 16k řádků našel ve shared buffers? Tj, kdybych otočil server (což nemůžu), tak tam bude nula?

S timhle mi to běží asi sekundu (přepsal jsem dotaz na lateral a dolepil nějaké indexy).

Nícméně 16k řádků je fakt ranec a tolik by jich neměl potřebovat.

to neni 16K radku, ale 16K * 8K bajtu

jedna datova stranka je 8KB, a ty jsi jich precet 15787

Díky

Aha, rozumím. No, každopádně předpokládám, že tenhle rozdíl (read), které při opakovaném spuštěním zmizí mi dělá docela podstatný výkon v odezvě. Jakej mi to dává hint pro lepší optimalizaci?

Díky Je;

 

Chcete-li tuto diskusi zobrazit na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/CAFj8pRBfCgJw1bsTDnbHDnuLZ8%3DG4KoU5EFMhYpTpHXzEjBFVw%40mail.gmail.com.

[Pavel Stehule]

po 21. 2. 2022 v 15:22 odesílatel Jan Michálek <godzil...@gmail.com> napsal:

po 21. 2. 2022 v 15:19 odesílatel Pavel Stehule <pavel....@gmail.com> napsal:

po 21. 2. 2022 v 14:45 odesílatel Jan Michálek <godzil...@gmail.com> napsal:

Díky

Čiliže, když tady mam třeba

Buffers: shared hit=15787 read=559

Tak to znamená, že necelejch 16k řádků našel ve shared buffers? Tj, kdybych otočil server (což nemůžu), tak tam bude nula?

S timhle mi to běží asi sekundu (přepsal jsem dotaz na lateral a dolepil nějaké indexy).

Nícméně 16k řádků je fakt ranec a tolik by jich neměl potřebovat.

to neni 16K radku, ale 16K * 8K bajtu

jedna datova stranka je 8KB, a ty jsi jich precet 15787

Díky

Aha, rozumím. No, každopádně předpokládám, že tenhle rozdíl (read), které při opakovaném spuštěním zmizí mi dělá docela podstatný výkon v odezvě. Jakej mi to dává hint pro lepší optimalizaci?

ze by jsi chtel pouzit lepsi indexy - nekde jedes pres seq scan nebo bitmap heap scan nebo skrz neefektivni index

 

Chcete-li tuto diskusi zobrazit na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/CAAYBy8amT_0pcqg3qVs-T662WcQRSFfj7%2BWqO8uf0%2BMHp16Uuw%40mail.gmail.com.

[Josef Šimánek]

po 21. 2. 2022 v 15:22 odesílatel Jan Michálek <godzil...@gmail.com> napsal:

>
>
>
> po 21. 2. 2022 v 15:19 odesílatel Pavel Stehule <pavel....@gmail.com> napsal:
>>
>>
>>
>> po 21. 2. 2022 v 14:45 odesílatel Jan Michálek <godzil...@gmail.com> napsal:
>>>
>>> Díky
>>> Čiliže, když tady mam třeba
>>> Buffers: shared hit=15787 read=559
>>> Tak to znamená, že necelejch 16k řádků našel ve shared buffers? Tj, kdybych otočil server (což nemůžu), tak tam bude nula?
>>> S timhle mi to běží asi sekundu (přepsal jsem dotaz na lateral a dolepil nějaké indexy).
>>> Nícméně 16k řádků je fakt ranec a tolik by jich neměl potřebovat.
>>
>>
>> to neni 16K radku, ale 16K * 8K bajtu
>>
>> jedna datova stranka je 8KB, a ty jsi jich precet 15787
>
> Díky
> Aha, rozumím. No, každopádně předpokládám, že tenhle rozdíl (read), které při opakovaném spuštěním zmizí mi dělá docela podstatný výkon v odezvě. Jakej mi to dává hint pro lepší optimalizaci?

Doporučuju https://explain.dalibo.com/. Na tý úvodní stránce je dole
uvedeno že nejvíc dat lze dosáhnout výstupem z EXPLAIN (ANALYZE,
COSTS, VERBOSE, BUFFERS, FORMAT JSON). Pak u každýho uzlu z toho plánu
jsou k dispozici hezky všechny informace. Například na záložce "IO &
Buffers" je vidět kolik dat se muselo přečíst (a jestli jdou z paměti
nebo ne). Je dobrý se podívat kde to stojí a (jak psal Pavel) ideálně
zjistit jestli to zbytečně nečte víc dat než je potřeba (právě
například diky seq. scanům). Pokud tam není nic tajnýho, lze ten plán
i nasdílet (vpravo nahoře tlačítko Share) a poslat ho sem. Třeba
někoho něco napadne.

> Chcete-li tuto diskusi zobrazit na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/CAAYBy8amT_0pcqg3qVs-T662WcQRSFfj7%2BWqO8uf0%2BMHp16Uuw%40mail.gmail.com.

[Jan Michálek]

Jo, máte oba pravdu. Žere mi to tam ten bitmap heap scan
https://explain.dalibo.com/plan/qqE

indexy tam potřebný jsou, asi mám někde nešikovně definovanou podmínku.

Je to zděděnej kód, trochu se bojím do toho moc hrabat, nevim, co je "hrbolatě napsaný" a co má nějakej smysl.

Ještě se v tom povrtám.

Je;

po 21. 2. 2022 v 15:50 odesílatel Josef Šimánek <josef....@gmail.com> napsal:

Chcete-li zobrazit tuto diskusi na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/CAFp7QwoGsjGMUP2LMCCoq5GBH6EAhkGJ72GXd9775hpVVkLvfA%40mail.gmail.com.

[Pavel Stehule]

po 21. 2. 2022 v 17:39 odesílatel Jan Michálek <godzil...@gmail.com> napsal:

Jo, máte oba pravdu. Žere mi to tam ten bitmap heap scan
https://explain.dalibo.com/plan/qqE

indexy tam potřebný jsou, asi mám někde nešikovně definovanou podmínku.

Je to zděděnej kód, trochu se bojím do toho moc hrabat, nevim, co je "hrbolatě napsaný" a co má nějakej smysl.

jak tam mas BitmapAnd - ja ten vizualni explain neumim cist, tak tam by mozna mohl pomoct vicesloupcovy index

 

Chcete-li tuto diskusi zobrazit na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/CAAYBy8aRxWsfjOtM7EfCe7ZhESAHzG0U33Ycrzri%2B6Sz-Do%2BkQ%40mail.gmail.com.

[Josef Šimánek]

.

po 21. 2. 2022 v 17:49 odesílatel Pavel Stehule
<pavel....@gmail.com> napsal:
>
>
>
> po 21. 2. 2022 v 17:39 odesílatel Jan Michálek <godzil...@gmail.com> napsal:

>>
>> Jo, máte oba pravdu. Žere mi to tam ten bitmap heap scan
>> https://explain.dalibo.com/plan/qqE
>> indexy tam potřebný jsou, asi mám někde nešikovně definovanou podmínku.
>> Je to zděděnej kód, trochu se bojím do toho moc hrabat, nevim, co je "hrbolatě napsaný" a co má nějakej smysl.
>
>
> jak tam mas BitmapAnd - ja ten vizualni explain neumim cist, tak tam by mozna mohl pomoct vicesloupcovy index

Jestli hledáš podmínky, tak ty jsou schovaný tady - https://imgur.com/a/yZyEqtf.

> Chcete-li tuto diskusi zobrazit na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/CAFj8pRD%3Dqmv4nkp3Noa8C%2BMOwi_fx%3DC_xUsvcbL5eNz0bktKZQ%40mail.gmail.com.

[Jan Michálek]

Tak skutečně pomohl vícesloupcovej index, jakože se to zrychlilo fakt o parník. Hustý. Sakra práce, to bych se fakt potřeboval naučit tomuhle pořádně rozumět.

Je;

po 21. 2. 2022 v 18:12 odesílatel Josef Šimánek <josef....@gmail.com> napsal:

Chcete-li zobrazit tuto diskusi na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/CAFp7Qwqt84N9-NuYcEDS7ATwUv-JP43VxJnofXTahfuLjH%2B%3Dpw%40mail.gmail.com.

[Pavel Stehule]

út 22. 2. 2022 v 10:11 odesílatel Jan Michálek <godzil...@gmail.com> napsal:

Tak skutečně pomohl vícesloupcovej index, jakože se to zrychlilo fakt o parník. Hustý. Sakra práce, to bych se fakt potřeboval naučit tomuhle pořádně rozumět.

je to jednoduchy - snazis se minimalizovat cteni z disku (dneska pripadne z RAMky). Cim min dat ctes, tim je dotaz rychlejsi. A cim mas presnejsi index, tim min dat ctes z disku. A kdyz honis ms, tak pak hledas neefektivity - mas dve podminky, ale pouzivas index pres jeden sloupec - to je signal, ze je neco neoptimalni.

Predstavit si telefonni seznam - je setrideny podle prijmeni, a jmena - fakticky dvou sloupcovy index, predstav si, jak by se ti hledal Novak Jan, pokud bys mel seznam setrideny jen podle prijmeni.

O nicem jinem to neni - v zasade. Pak mas jeste milion veci, ktery muzou nebo nemusi mit vliv, ale ten zaklad je jednoduchy - pokud mozno z disku cist jen to, co bude do vysledku. A uz jsem to opakoval 100x, kazdy programator by mel umet SQLko, a mel by umet cist provadeci plany. V nich to je videt dobre.

 

Chcete-li tuto diskusi zobrazit na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/CAAYBy8ZUGBzNr64%2BUqKb3qzN29xAYxXM0%3DwUo8fDHMfRzAprTQ%40mail.gmail.com.

[Jan Michálek]

út 22. 2. 2022 v 11:17 odesílatel Pavel Stehule <pavel....@gmail.com> napsal:

út 22. 2. 2022 v 10:11 odesílatel Jan Michálek <godzil...@gmail.com> napsal:

Tak skutečně pomohl vícesloupcovej index, jakože se to zrychlilo fakt o parník. Hustý. Sakra práce, to bych se fakt potřeboval naučit tomuhle pořádně rozumět.

je to jednoduchy - snazis se minimalizovat cteni z disku (dneska pripadne z RAMky). Cim min dat ctes, tim je dotaz rychlejsi. A cim mas presnejsi index, tim min dat ctes z disku. A kdyz honis ms, tak pak hledas neefektivity - mas dve podminky, ale pouzivas index pres jeden sloupec - to je signal, ze je neco neoptimalni.

Predstavit si telefonni seznam - je setrideny podle prijmeni, a jmena - fakticky dvou sloupcovy index, predstav si, jak by se ti hledal Novak Jan, pokud bys mel seznam setrideny jen podle prijmeni.

O nicem jinem to neni - v zasade. Pak mas jeste milion veci, ktery muzou nebo nemusi mit vliv, ale ten zaklad je jednoduchy - pokud mozno z disku cist jen to, co bude do vysledku. A uz jsem to opakoval 100x, kazdy programator by mel umet SQLko, a mel by umet cist provadeci plany. V nich to je videt dobre.

Jasně, tomuhle rozumim, nicméně, nebyl mi jasnej ten "mechanismus složené podmínky" já měl ty sloupce indexovaný, ale každej zvlášť.

Tj. v tom příkladu, co popisuješ by to byl jeden index přes jméno, druhej přes příjmení. Myslel jsem si, že si s tim "postgre nějak poradí" a ty indexy "nějak složí". Chápu to správně tak, že jedna podmínka = jeden index - tj potřebuju "proindexovat všechny kombinace atributů, které budu chtít rychle dotazovat a mají nějakou selektivitu"? Jak si poradím se situací, kdy bych chtěl dotazovat dva sloupce, z nichž se každý indexuje jiným typem indexu? Dejme tomu geometrii a kód katastru?

Díky Je;

 

Chcete-li tuto diskusi zobrazit na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/CAFj8pRAysgaFgZigc8YyCs5P-5iyxn_5-4Dt4%3D8YfH4eaQibjg%40mail.gmail.com.

[Pavel Stehule]

út 22. 2. 2022 v 12:32 odesílatel Jan Michálek <godzil...@gmail.com> napsal:

út 22. 2. 2022 v 11:17 odesílatel Pavel Stehule <pavel....@gmail.com> napsal:

út 22. 2. 2022 v 10:11 odesílatel Jan Michálek <godzil...@gmail.com> napsal:

Tak skutečně pomohl vícesloupcovej index, jakože se to zrychlilo fakt o parník. Hustý. Sakra práce, to bych se fakt potřeboval naučit tomuhle pořádně rozumět.

je to jednoduchy - snazis se minimalizovat cteni z disku (dneska pripadne z RAMky). Cim min dat ctes, tim je dotaz rychlejsi. A cim mas presnejsi index, tim min dat ctes z disku. A kdyz honis ms, tak pak hledas neefektivity - mas dve podminky, ale pouzivas index pres jeden sloupec - to je signal, ze je neco neoptimalni.

Predstavit si telefonni seznam - je setrideny podle prijmeni, a jmena - fakticky dvou sloupcovy index, predstav si, jak by se ti hledal Novak Jan, pokud bys mel seznam setrideny jen podle prijmeni.

O nicem jinem to neni - v zasade. Pak mas jeste milion veci, ktery muzou nebo nemusi mit vliv, ale ten zaklad je jednoduchy - pokud mozno z disku cist jen to, co bude do vysledku. A uz jsem to opakoval 100x, kazdy programator by mel umet SQLko, a mel by umet cist provadeci plany. V nich to je videt dobre.

Jasně, tomuhle rozumim, nicméně, nebyl mi jasnej ten "mechanismus složené podmínky" já měl ty sloupce indexovaný, ale každej zvlášť.

Tj. v tom příkladu, co popisuješ by to byl jeden index přes jméno, druhej přes příjmení. Myslel jsem si, že si s tim "postgre nějak poradí" a ty indexy "nějak složí". Chápu to správně tak, že jedna podmínka = jeden index - tj potřebuju "proindexovat všechny kombinace atributů, které budu chtít rychle dotazovat a mají nějakou selektivitu"? Jak si poradím se situací, kdy bych chtěl dotazovat dva sloupce, z nichž se každý indexuje jiným typem indexu? Dejme tomu geometrii a kód katastru?

Postgres umi "slozit" indexy prave bitmap index scanem. Ale to nemusi byt moc efektivni, jakkoliv je to lepsi nez seq scan. Michani indexu je problem - mam pocit, ze jsem videl nejaky triky, ale moc jisty si nejem.

Chcete-li tuto diskusi zobrazit na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/CAAYBy8ZVmuAkVUnOAo_VJ6TC-j6YMtXB8%2BLO_HMw2A0kqdzbUA%40mail.gmail.com.

[Jan Michálek]

út 22. 2. 2022 v 12:39 odesílatel Pavel Stehule <pavel....@gmail.com> napsal:

út 22. 2. 2022 v 12:32 odesílatel Jan Michálek <godzil...@gmail.com> napsal:

út 22. 2. 2022 v 11:17 odesílatel Pavel Stehule <pavel....@gmail.com> napsal:

út 22. 2. 2022 v 10:11 odesílatel Jan Michálek <godzil...@gmail.com> napsal:

Tak skutečně pomohl vícesloupcovej index, jakože se to zrychlilo fakt o parník. Hustý. Sakra práce, to bych se fakt potřeboval naučit tomuhle pořádně rozumět.

je to jednoduchy - snazis se minimalizovat cteni z disku (dneska pripadne z RAMky). Cim min dat ctes, tim je dotaz rychlejsi. A cim mas presnejsi index, tim min dat ctes z disku. A kdyz honis ms, tak pak hledas neefektivity - mas dve podminky, ale pouzivas index pres jeden sloupec - to je signal, ze je neco neoptimalni.

Predstavit si telefonni seznam - je setrideny podle prijmeni, a jmena - fakticky dvou sloupcovy index, predstav si, jak by se ti hledal Novak Jan, pokud bys mel seznam setrideny jen podle prijmeni.

O nicem jinem to neni - v zasade. Pak mas jeste milion veci, ktery muzou nebo nemusi mit vliv, ale ten zaklad je jednoduchy - pokud mozno z disku cist jen to, co bude do vysledku. A uz jsem to opakoval 100x, kazdy programator by mel umet SQLko, a mel by umet cist provadeci plany. V nich to je videt dobre.

Jasně, tomuhle rozumim, nicméně, nebyl mi jasnej ten "mechanismus složené podmínky" já měl ty sloupce indexovaný, ale každej zvlášť.

Tj. v tom příkladu, co popisuješ by to byl jeden index přes jméno, druhej přes příjmení. Myslel jsem si, že si s tim "postgre nějak poradí" a ty indexy "nějak složí". Chápu to správně tak, že jedna podmínka = jeden index - tj potřebuju "proindexovat všechny kombinace atributů, které budu chtít rychle dotazovat a mají nějakou selektivitu"? Jak si poradím se situací, kdy bych chtěl dotazovat dva sloupce, z nichž se každý indexuje jiným typem indexu? Dejme tomu geometrii a kód katastru?

Postgres umi "slozit" indexy prave bitmap index scanem. Ale to nemusi byt moc efektivni, jakkoliv je to lepsi nez seq scan. Michani indexu je problem - mam pocit, ze jsem videl nejaky triky, ale moc jisty si nejem.

Jasně, už to chápu. Čiliže, postgres umí zkombinovat různý indexy a ale teda jenom tak, že se zeptá jednoho, zeptá druhýho a plácne to přes sebe. Což má režii, kombinovat takhle třeba čtyři sloupce se už "nevyplatí". To jsem v tom plánu viděl (s jednotlivejma indexama) a nebylo mi jasný, proč nepoužije všechny. Teď je mi to jasný.

Díky za vysvětlení.

Je;

 

Chcete-li tuto diskusi zobrazit na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/CAFj8pRDqaPys6hRUtKgX6%2BZwOh8ugSiQ_xuw8APPkm%3DLEzodZg%40mail.gmail.com.

[Josef Šimánek]

) a

út 22. 2. 2022 v 13:34 odesílatel Jan Michálek <godzil...@gmail.com> napsal:
>
>
>
> út 22. 2. 2022 v 12:39 odesílatel Pavel Stehule <pavel....@gmail.com> napsal:

>>
>>
>>
>> út 22. 2. 2022 v 12:32 odesílatel Jan Michálek <godzil...@gmail.com> napsal:
>>>
>>>
>>>
>>> út 22. 2. 2022 v 11:17 odesílatel Pavel Stehule <pavel....@gmail.com> napsal:
>>>>
>>>>
>>>>
>>>> út 22. 2. 2022 v 10:11 odesílatel Jan Michálek <godzil...@gmail.com> napsal:
>>>>>
>>>>> Tak skutečně pomohl vícesloupcovej index, jakože se to zrychlilo fakt o parník. Hustý. Sakra práce, to bych se fakt potřeboval naučit tomuhle pořádně rozumět.
>>>>
>>>>
>>>> je to jednoduchy - snazis se minimalizovat cteni z disku (dneska pripadne z RAMky). Cim min dat ctes, tim je dotaz rychlejsi. A cim mas presnejsi index, tim min dat ctes z disku. A kdyz honis ms, tak pak hledas neefektivity - mas dve podminky, ale pouzivas index pres jeden sloupec - to je signal, ze je neco neoptimalni.
>>>>
>>>> Predstavit si telefonni seznam - je setrideny podle prijmeni, a jmena - fakticky dvou sloupcovy index, predstav si, jak by se ti hledal Novak Jan, pokud bys mel seznam setrideny jen podle prijmeni.
>>>>
>>>> O nicem jinem to neni - v zasade. Pak mas jeste milion veci, ktery muzou nebo nemusi mit vliv, ale ten zaklad je jednoduchy - pokud mozno z disku cist jen to, co bude do vysledku. A uz jsem to opakoval 100x, kazdy programator by mel umet SQLko, a mel by umet cist provadeci plany. V nich to je videt dobre.
>>>>
>>>>
>>> Jasně, tomuhle rozumim, nicméně, nebyl mi jasnej ten "mechanismus složené podmínky" já měl ty sloupce indexovaný, ale každej zvlášť.

>>> Tj. v tom příkladu, co popisuješ by to byl jeden index přes jméno, druhej přes příjmení. Myslel jsem si, že si s tim "postgre nějak poradí" a ty indexy "nějak složí". hápu to správně tak, že jedna podmínka = jeden index - tj potřebuju "proindexovat všechny kombinace atributů, které budu chtít rychle dotazovat a mají nějakou selektivitu"? Jak si poradím se situací, kdy bych chtěl dotazovat dva sloupce, z nichž se každý indexuje jiným typem indexu? Dejme tomu geometrii a kód katastru?

>>
>>
>> Postgres umi "slozit" indexy prave bitmap index scanem. Ale to nemusi byt moc efektivni, jakkoliv je to lepsi nez seq scan. Michani indexu je problem - mam pocit, ze jsem videl nejaky triky, ale moc jisty si nejem.
>>
>>
>
> Jasně, už to chápu. Čiliže, postgres umí zkombinovat různý indexy a ale teda jenom tak, že se zeptá jednoho, zeptá druhýho a plácne to přes sebe. Což má režii, kombinovat takhle třeba čtyři sloupce se už "nevyplatí". To jsem v tom plánu viděl (s jednotlivejma indexama) a nebylo mi jasný, proč nepoužije všechny. Teď je mi to jasný.

PostgreSQL přes ten uzel "BitmapAnd"
(https://explain.dalibo.com/plan/qqE#plan/node/10) sesbírá data z obou
indexu (https://explain.dalibo.com/plan/qqE#plan/node/11 a
https://explain.dalibo.com/plan/qqE#plan/node/12) po jednom (ale umí
to dělat ve stejnej čas najednou ve dvou procesech vedle sebe) a složí
z výsledků tzv. bitový mapy.

Ty si představuju cca následovně (příklad):

1 1 0 1 1
0 1 0 1 0

A ten uzel "BitmapAnd"
(https://explain.dalibo.com/plan/qqE#plan/node/10) vezme tyhle dvě
bitmapy a udělá nad nima logickou operaci "AND". Čili výsledek pak
vypadá takhle:

0 1 0 1 0

Tahle bitmapa se pak pošle dál do uzlu "Bitmap Heap Scan"
(https://explain.dalibo.com/plan/qqE#plan/node/9). Ten podle tý
bitmapy dokáže najít data odpovídající těm podmínkám z indexů. Jelikož
ta bitmapa ale neobsahuje přímo cestu k jednotlivým řádkům, ale adresu
stránek, tak je potřeba všechny data z těch stránek ještě jednou
přejet jestli sedí na ten požadovaný filtr (je to tam vidět v explainu
tuším jako "Recheck Cond"). Takhle něják to chápu já.

Tohle vše v tom původním plánu trva cca 3 vteřiny a nevrátí to žádný
řádek tomu left joinu
(https://explain.dalibo.com/plan/qqE#plan/node/3) nad tím. Čili je pak
otázka, jestli není lepší ty data načítat jinak a jen tehdy, pokud je
nějáká šance že tam vůbec budou. Ale to už záleží dost na logice
aplikace.

Snad nejsem úplně mimo mísu. Případně mě snad někdo opraví.

> Chcete-li tuto diskusi zobrazit na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/CAAYBy8b%2B6ix%3DRs%3DVYcP3jZOEKyzeUc58zWkCygTSNr7%2BdAa%3DrQ%40mail.gmail.com.

[Jan Michálek]

Díky

Je;

út 22. 2. 2022 v 13:52 odesílatel Josef Šimánek <josef....@gmail.com> napsal:

Chcete-li zobrazit tuto diskusi na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/CAFp7QwpeWX1dTEdmS68gcQpaKVXs%2Bj3ZxsZhCpdyovQDnZi%3Dww%40mail.gmail.com.

[Peter]

Ak chces lepsie pochopit mechanizmus indexov a moznych algoritmov, z ktorych si planner vybera, silno odporucam Winandovu "SQL Performance Explained". Je to do knihy zorganizovany obsah jeho https://use-the-index-luke.com/ - ale kniha mne osobne vyhovovala viac. Citala sa velmi svizne, ako napinava jednohubka pre pokrocilych. Obsah uz asi trochu zastaral a nenajdes tam nove triky typu CREATE STATISTICS, ale tie zakladne principy okolo b-tree indexov, plannera a citania exekucneho planu zostavaju aj v aktualnych verziach postgresu nezmenene.

https://sql-performance-explained.com/

.pl.

Dátum: utorok 22. februára 2022, čas: 13:59:13 UTC+1, odosielateľ: mluvicí hrášek - jeleniste

[Tomas Vondra]

On 2/22/22 12:39, Pavel Stehule wrote:
> ...

>
> Jasně, tomuhle rozumim, nicméně, nebyl mi jasnej ten "mechanismus
> složené podmínky" já měl ty sloupce indexovaný, ale každej zvlášť.
> Tj. v tom příkladu, co popisuješ by to byl jeden index přes jméno,
> druhej přes příjmení. Myslel jsem si, že si s tim "postgre nějak
> poradí" a ty indexy "nějak složí". Chápu to správně tak, že jedna
> podmínka = jeden index - tj potřebuju "proindexovat všechny
> kombinace atributů, které budu chtít rychle dotazovat a mají nějakou
> selektivitu"? Jak si poradím se situací, kdy bych chtěl dotazovat
> dva sloupce, z nichž se každý indexuje jiným typem indexu? Dejme
> tomu geometrii a kód katastru?
>
>
> Postgres umi "slozit" indexy prave bitmap index scanem. Ale to nemusi
> byt moc efektivni, jakkoliv je to lepsi nez seq scan. Michani indexu je
> problem - mam pocit, ze jsem videl nejaky triky, ale moc jisty si nejem.
>

Index může používat jenom jeden typ indexu, takže pokud je potřeba nad
jedním sloupcem udělat BTREE index a nad druhým GiST, tak to samozřejmě
nefunguje.

Ale existují extenze btree_gin/btree_gist které umožňují dělat GIN/GiST
indexy i nad "skaláry", takže pak nad tím jde udělat jeden index.

T.

[Tomas Vondra]

On 2/22/22 10:11, Jan Michálek wrote:
> Tak skutečně pomohl vícesloupcovej index, jakože se to zrychlilo fakt o
> parník. Hustý. Sakra práce, to bych se fakt potřeboval naučit tomuhle
> pořádně rozumět.
>

Ten trik je v tom jak velkou část toho indexu musí Postgres prolézt při
vytváření té bitmapy. Když máš jeden sloupec, tak vesměs musí prolézt
větší část - ono se to pak v tom BitmapAnd složí a dostaneš stejnou
bitmapu jako s vícesloupcovým indexem, ale její vytvoření je dražší. A
čím větší kus indexu se musí prolézt, tím víc I/O, samozřejmě.

Druhá věc je že to dělá nested loop, tj. ta bitmapa se tam reálně
generuje několikrát - konkrétně 53x. Jako experiment bych zkusil vypnout
nested loop, co to udělá. Ty odhady nejsou úplně špatné, ale možná je
potřeba potunit random_page_cost apod.

T.

[Peter]

Kombinovat skalarne a neskalarne stlpce v GIN ide by default (modul btree_gin sa mi v Ubuntu nainstaloval spolu backendom) - pouzivam to uz par verzii dozadu:
CREATE TABLE t (id int, txt text, a int[]);
CREATE INDEX g3_t_txt_id ON t USING GIN (txt gin_trgm_ops, id);
CREATE INDEX gx_t_a_id ON t USING GIN (a, id);

.pl.

Dátum: utorok 22. februára 2022, čas: 14:57:30 UTC+1, odosielateľ: Tomáš Vondra