Builder.cz - Informacni server o programovani

Odběr fotomagazínu

Fotografický magazín "iZIN IDIF" každý týden ve Vašem e-mailu.
Co nového ve světě fotografie!

 

Zadejte Vaši e-mailovou adresu:

Kamarád fotí rád?

Přihlas ho k odběru fotomagazínu!

 

Zadejte e-mailovou adresu kamaráda:



Pomalý dotaz v postgres

Seznam témat     Nová odpověď

Přihlásit se     Registrace     Zapomenuté heslo

Re: Pomalý dotaz v postgres

Autor: marrra

0:46:06 18.11.2011

Diky moc za rady.

marrra

Citovat příspěvek

 

Re: Pomalý dotaz v postgres

Autor: Pavel Stehule

6:12:55 17.11.2011

Maaartin Napsal:
-------------------------------------------------------
> Aha... ja si naivne myslel ze si to v kazdym uzlu
> toho BTREE drzi celkovy pocet poduzlu, takze by
> stacilo neco projet to od korene ke hledany
> hodnote body a poscitat kolik toho lezi vlevo od
> projety cesty. To by znamenalo projet jen malou
> cast indexu, zejmena pro velky tabule.
>
> Ale odhaduju ze se nevyplati tam ten pocet drzet.
>
>

Ne - on tam nesmí být - to byste pak při přidání/zrušení záznamu musel aktualizovat celý index (a to ještě pod zámkem) - tím by se zpomalily DML příkazy a prodloužilo se zamknutí indexu. V klasickém BTree jsou všechny změny indexu poměrně dobře izolovány, což vede k dobrému výkonu při více-uživatelském přístupu.

COUNT() je v relačních db jedna z nejhůře optimalizovaných a optimalizovatelných funkcí.

Pavel

Citovat příspěvek

 

Re: Pomalý dotaz v postgres

Autor: Maaartin

23:09:31 16.11.2011

Aha... ja si naivne myslel ze si to v kazdym uzlu toho BTREE drzi celkovy pocet poduzlu, takze by stacilo neco projet to od korene ke hledany hodnote body a poscitat kolik toho lezi vlevo od projety cesty. To by znamenalo projet jen malou cast indexu, zejmena pro velky tabule.

Ale odhaduju ze se nevyplati tam ten pocet drzet.

Citovat příspěvek

 

Re: Pomalý dotaz v postgres

Autor: Pavel Stehule

22:42:14 16.11.2011

> podle me INDEbody) pro u2 pouzit musi, protoze v
> nem jsou data o tom kolik ceho je a do tabulky
> vubec netreba lezt. O postgresu ale celkem nic
> nevim, je mozny ze indexy lze pouzit jen jednim
> smerem (jako treba ve Firebirdu), v tom pripade
> bych zkusil pridat INDEbody DESC) nebo cvicne
> vymenil ">" za "<". Jen tak hadam.
>

Index only scan bude Pg podporovat az od 9.2, takze do tabulek musi vzdy. Navic index je vhodny, pouze tehdy, kdyz ctete mene nez 5-10% tabulky, coz zrovna v tomto pripade pro vetsinu zaznamu neplati.

Indexy v PostgreSQL jsou obousmerne - v tomto pripade si proste Postgres mysli, ze se mu index nevyplati, bo bude cist temer minimalne 1/4 tabulky, takze preferuje seq scan - navic pro COUNT staci filtr, neni tam nutny sort, takze index opravdu neni nezbytny.

Citovat příspěvek

 

Re: Pomalý dotaz v postgres

Autor: Pavel Stehule

22:35:58 16.11.2011

marrra Napsal:
-------------------------------------------------------
> Zdravim,
>
> mám dotaz na zjištění pořadí uživatele - dle počtu
> získaných bodů.
> řeším to tak, že zjistím počet uživatelů, kteří
> mají vyšší počet bodů, než daný uživatel.
>
> SELECT *, (SELECT COUNT(uid) from users u2 where
> u2.body > u1.body) + 1 AS rank FROM users u1
> WHERE uid = 108271;
>
> BTREE Index mám vytvořený na uid, a body.
>
> Index se však nepoužije a provede se seq. scan
> tabulky users. Dotaz poté trvá 50ms
>
> Plán vypadá takto:
> Index Scan using pk_users on users u1
> (cost=0.00..7986.70 rows=1 width=277)
> Index Cond: (uid = 108271)
> SubPlan 1
> -> Aggregate (cost=7978.39..7978.40
> rows=1 width=0)
> -> Seq Scan on users u2
> (cost=0.00..7884.05 rows=37735 width=0)
> Filter: (body > $0)
>

U 9.2 se mi index chyta i na vnoreny poddotaz. Ale těch 50 ms +/- odpovídá podle rychlosti procesoru, disků a obsahu cache. Myslím si, že tam nic nevymyslíte. Jedíná možnost je si vytvořit materializovaný pohled

postgres=# create table hodnoceni as select id, name, rank() over (order by score )from users;
SELECT 40000

postgres=# CREATE INDEX on hodnoceni (id);
CREATE INDEX
Time: 173,043 ms

postgres=# select * from hodnoceni where id = 30000;
id │ name │ rank
───────┼────────────────────────────┼───────
30000 │ aaaa afasfdasdf asdfasfdaf │ 19223
(1 row)

Time: 1,249 ms

Pavel Stehule

Citovat příspěvek

 

Re: Pomalý dotaz v postgres

Autor: Maaartin

18:39:14 16.11.2011

> [ital]BTREE Index mám vytvořený na uid, a body.[/ital]

To jako ADD INDEX (uid), ADD INDEX (body)? Predpokladam ze ADD INDEX (uid, body) by se pouzil spis, aspon by v nem byly vsecky data co jsou potreba (coz muze hrat roli pokud je tabulka siroka). Ale to je nejpis celkem jedno.

Pokud uid je opravdu ajdicko, pak nemuze byt NULL a COUNT(uid) je totez co COUNT(*). Nicmene bych zkusil COUNT(*) protoze DB na to nemusi prijit.

Optimalni plan pro

select
count(*) + 1 as rank
from users u2
where u1.body > (select body from users u2 where uid=108271);

podle me INDEX(body) pro u2 pouzit musi, protoze v nem jsou data o tom kolik ceho je a do tabulky vubec netreba lezt. O postgresu ale celkem nic nevim, je mozny ze indexy lze pouzit jen jednim smerem (jako treba ve Firebirdu), v tom pripade bych zkusil pridat INDEX(body DESC) nebo cvicne vymenil ">" za "<". Jen tak hadam.

Citovat příspěvek

 

Re: Pomalý dotaz v postgres

Autor: marrra

17:01:06 16.11.2011

grafnev Napsal:
-------------------------------------------------------
> co udela dotaz?

Ano, zachová se to úplně stejně.

Citovat příspěvek

 

Re: Pomalý dotaz v postgres

Autor: grafnev

16:50:12 16.11.2011

Neznam postgres

co udela dotaz?

select
count(uid) +1 as rank
from users u2
where u1.body > (select body from users u2 where uid=108271);

... predpokladam, ze se to asi zachova stejne :(

Citovat příspěvek

 

Re: Pomalý dotaz v postgres

Autor: marrra

15:49:15 16.11.2011

Zdravím,

díky za rady.

Dotaz jsem zkusil spustit na devel databázi (stejná konfigurace, ale běží tam pg 8.3 - oproti 8.4 na ostré DB) a tam se použije 2x Heap scan, přičemž čas je cca 20ms.

Window funkce jsem neznal (díky za tip), nicméně při použití fce rank dotaz trvá cca 200ms.

Citovat příspěvek

 

Re: Pomalý dotaz v postgres

Autor: Jakub Vrbas

15:05:55 16.11.2011

Zdravím,
můžete zkusit postgres donutit použít index (http://www.postgresql.org/docs/8.4/static/indexes-examine.html), ale optimalizátor není hloupý a pokud se rozhodl index nepoužít, má k tomu většinou dobrý důvod :-) (např. malá variabilita dat ve sloupci na kterém je index)

Případně můžete zkusit window funkce (http://www.postgresql.org/docs/8.4/interactive/functions-window.html), ale nejsem si jistý, jestli tím dosáhnete lepšího výsledku - mám s nimi minimum zkušeností.

Citovat příspěvek

 

Pomalý dotaz v postgres

Autor: marrra

12:51:51 16.11.2011

Zdravim,

mám dotaz na zjištění pořadí uživatele - dle počtu získaných bodů.
řeším to tak, že zjistím počet uživatelů, kteří mají vyšší počet bodů, než daný uživatel.

SELECT *, (SELECT COUNT(uid) from users u2 where u2.body > u1.body) + 1 AS rank FROM users u1 WHERE uid = 108271;

BTREE Index mám vytvořený na uid, a body.

Index se však nepoužije a provede se seq. scan tabulky users. Dotaz poté trvá 50ms :(

Plán vypadá takto:
Index Scan using pk_users on users u1 (cost=0.00..7986.70 rows=1 width=277)
Index Cond: (uid = 108271)
SubPlan 1
-> Aggregate (cost=7978.39..7978.40 rows=1 width=0)
-> Seq Scan on users u2 (cost=0.00..7884.05 rows=37735 width=0)
Filter: (body > $0)

Můžete mi prosím poradit, jak docílit kratší dobu vykonávání dotazu (aby se použil index, případně nějak přepsat daný dotaz)

Díky moc za jakékoliv rady.
marrra

Citovat příspěvek

 

 

 

Přihlášení k mému účtu

Uživatelské jméno:

Heslo: