Dotaz: postgresql optimalizace vykonu

[chinook]

Dobry den, mame pomale dotazy v postgresu. Zdrzuje to disk aspon podle me. Pomohlo by koupit nove disky nebo vice ram? Resp. asi levnejsi by byly ty ram.

free -m
             total       used       free     shared    buffers     cached
Mem:          3286       3164        122          0        121       1874
-/+ buffers/cache:       1168       2118
Swap:         3811          1       3810

Vsech 8 jader nikdy nebezi na 100%

vmstat -n 1
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 0  0   1048 136552 130960 1901096    0    0     0     0    0    0  3  0 95  2
 0  0   1048 136108 131072 1901148    0    0   108  1204  288 1403  2  1 96  1
 2  0   1048 135656 131224 1900860    0    0   180    40  782 5657  2  1 96  2
 0  0   1048 138532 131276 1901040    0    0    72    40  489 1555  2  1 97  1
 1  0   1048 137420 131312 1901088    0    0    36    16  307  807  1  0 99  0
 0  0   1048 137464 131384 1901060    0    0    84   516  219 1088  1  0 98  1
 1  3   1048 137312 131408 1901160    0    0    32  1492  106  439  1  0 75 24
 0  4   1048 137312 131412 1901448    0    0     0     0   70  409  2  0 65 33
 0  6   1048 142020 131420 1895380    0    0     8    44   86  572  9  0 60 30
 0  1   1048 142560 131516 1894964    0    0    84  2052  204  853  4  0 82 14

Respektive muze to byt i o nastaveni postgresu jako serveru co by pomohlo zvetsit?

shared_buffers = 200MB                  # min 128kB or max_connections*16kB
                                        # (change requires restart)
                                        # (change requires restart)
work_mem = 10MB                         # min 64kB
maintenance_work_mem = 32MB             # min 1MB
max_fsm_pages = 153600                  # min max_fsm_relations*16, 6 bytes each
                                        # (change requires restart)
                                        # (change requires restart)
                                        # (change requires restart)
fsync = off                     # turns forced synchronization on or off
                                        # supported by the operating system:
                                        #   open_datasync
                                        #   fdatasync
                                        #   fsync
                                        #   fsync_writethrough
                                        #   open_sync
                                        # (change requires restart)
checkpoint_segments = 10        # in logfile segments, min 1, 16MB each
                                # (change requires restart)
                                # time; 0 is off
effective_cache_size = 1280MB

---

---

Odpovědi

[trekker.dk]

Pokud je ten server jenom pro Postgres, tak by podle toho výpisu free možná mohlo být větší effective_cache_size

Nějaké další info ohledně ladění pro výkon najdeš tady: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

[chinook]

Server se používá i na něco jiného. Ale není problém dokoupit RAM pokud to nějak výrazně pomůže. Ten článek jsem četl, snažím se s tím laborovat, ale nevím na jaké parametry se zaměřit. Nemá tu někdo funkční konf, kterou bych si upravil?

[okbob]

a) napřed se podívejte, proč máte pomalé dotazy a zda-li by nepomohly indexy b) víc paměti neuškodí, nicméně v některých případech to pomůže pouze tehdy pokud se pamětí dostanete nad velikost tabulek se kterými pracujete. Jinak koupě další paměti Vám nemusí pomoci. c) server nesmí nikdy swapovat - na to bacha, tuplem jestli server používáte i s něčím dalším

viz http://www.pgsql.cz/index.php/Desatero.

[trekker.dk]

Jen tak mimochodem, když vidím v tom desateru odkaz http://www.pgsql.cz/skoleni/skoleni_postgres_web.pdf ... na straně 62 máš nejspíš v tom prvním dlouhém SQL chybu:

SELECT n.nspname AS schema, c.relname AS name, stattuple(c.oid) AS st

A pár chybek ve wiki:

"Optimalizátor, použitý v PostgreSQL, vychází ze statistik." - IMO nemají být ty čárky.

SQL příkaz, který, díky cache, ušetříte je nejrychlejší a nejméně zatěžuje databázi. -> SQL příkaz, který díky cache ušetříte, je nejrychlejší a nejméně zatěžuje databázi.

[Šangala]

To mě pobavilo na gramatice netrvám, bo ji nejsem schopen dodržovat, ale:
"Optimalizátor, použitý v PostgreSQL, vychází ze statistik."
"SQL příkaz, který, díky cache, ušetříte je nejrychlejší…"
Je tak ± stejná vsuvka a řekl bych, že obojí je správně :)

[trekker.dk]

Ok, je to OT, ale bije to do očí a správně to je jenom sotva.

U toho druhého je to naprosto jasné, "ušetříte je" jsou dvě slovesa a každé patří do jiné věty - musí mezi nimi být čárka nebo spojka, před kterou se čárka nepíše (ta tam ale není).

No a pak by se dalo dohadovat o tom, jestli se "díky cache" a "použitý v PostgreSQL" dá považovat za vsuvky, jenže v tomhle případě obojí sděluje podstatnou informaci a v takovém případě to není vsuvka, ale normální větné členy.

(Zlaté pravidlo: i když dělám v řeči pauzu, ještě to neznamená, že tam bude čárka.)

[Šangala]

Je asi zbytečné se tady rozvádět o gramatice - prostě mě to pobavilo a chtěl jsem to někomu říct :), ale větu:
"Optimalizátor, použitý v PostgreSQL, vychází ze statistik."
vnímám jako:
„Optimalizátor, a to i ten, který je použitý v PostgreSQL, vychází ze statistik.“

Ale větu "Optimalizátor použitý v PostgreSQL vychází ze statistik." vnímám jako:
„Právě optimalizátor v PostgreSQL vychází ze statistik.“
Prostě si myslím, že i první varianta je správně. :)

[trekker.dk]

Ale větu "Optimalizátor použitý v PostgreSQL vychází ze statistik." vnímám jako: „Právě optimalizátor v PostgreSQL vychází ze statistik.“

Netahej matematiku a výrokovou logiku do gramatiky, to nefunguje Ta věta gramaticky znamená, že optimalizátor v pgsql vychází ze statistik a neříká nic jiného o jiných optimalizátorech.

Vsuvka by byla třeba "Optimalizátor použitý v PostgreSQL, jehož kvalita je nesporná, vychází ze statistik." Ta část "použitý v PostgreSQL" je přívlastek.

[Šangala]

Nebudu slibuji, ale stejně… :)

[okbob]

Ten dotaz je ok. Proc by tam mela byt chyba?

[trekker.dk]

Fakt? Když jsem to zkoušel, tak mi to vynadalo, že nezná stattuple, musel jsem to nahradit za pgstattuple.

[okbob]

to mate pravdu, opravim to v nove verzi.

diky

[chinook]

Tak jsem upravil některé hodnoty, db se podle mě trochu zrychlila, ale RAM více nezabíra. Chtěl bych ji efektivněji využít.

shared_buffers                  | 1GB 
work_mem                        | 50MB 
effective_cache_size            | 2560MB

Co ještě doporučujete změnit?

[okbob]

Musíte zjistit, co je tím hrdlem. Dohledejte pomalé dotazy a pošlete prováděcí plán.

[chinook]

Co myslíte tím prováděcím plánem? Když najdu pomalý dotaz, jak podle něho poznám proč tak dlouho trvá?

[Heron]

EXPLAIN, auto_explain.

[okbob]

http://www.root.cz/clanky/cteni-provadecich-planu-v-postgresql/

[chinook]

Například tento dotaz trvá dlouho

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=27666.09..27666.11 rows=1 width=4)
   ->  Nested Loop  (cost=6.01..27666.09 rows=1 width=4)
         ->  Nested Loop  (cost=6.01..27586.29 rows=267 width=8)
               ->  Index Scan using vydejdl_idprodejny_idx on vydejdl vydejdl2_  (cost=0.00..583.21 rows=39 width=4)
                     Index Cond: (idprodejny = 317)
                     Filter: (datum >= '2010-03-29 00:00:00+02'::timestamp with time zone)
               ->  Bitmap Heap Scan on vydejpol vydejpol0_  (cost=6.01..689.74 rows=212 width=12)
                     Recheck Cond: (vydejpol0_.iddl = vydejdl2_.iddl)
                     Filter: (vydejpol0_.typradku = 'Z'::bpchar)
                     ->  Bitmap Index Scan on vydejpol_iddl  (cost=0.00..5.98 rows=212 width=0)
                           Index Cond: (vydejpol0_.iddl = vydejdl2_.iddl)
         ->  Index Scan using pkey_idzbozi on zbozi zbozi1_  (cost=0.00..0.29 rows=1 width=4)
               Index Cond: (zbozi1_.idzbozi = vydejpol0_.idzbozi)
               Filter: (zbozi1_.idzatrid = 53)
(14 rows)

nebo toto

 QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=3349.08..3349.08 rows=1 width=211)
   Sort Key: zb.nazevzbozi
   ->  Nested Loop Left Join  (cost=1834.64..3349.07 rows=1 width=211)
         Join Filter: (zb.idzbozi = zb_cenik.idzbozi)
         ->  Nested Loop Left Join  (cost=1564.44..2993.79 rows=1 width=187)
               ->  Nested Loop Left Join  (cost=1564.44..2993.33 rows=1 width=171)
                     Join Filter: (zb.idzbozi = zs.idzbozi)
                     ->  Nested Loop Left Join  (cost=4.42..269.13 rows=1 width=49)
                           Join Filter: (substr((li.klic)::text, 1, 4) = (ra.klic)::text)
                           ->  Nested Loop Left Join  (cost=4.42..177.53 rows=1 width=55)
                                 Join Filter: (zn.idzatrid = zna.idznacky)
                                 ->  Nested Loop  (cost=4.42..174.46 rows=1 width=59)
                                       ->  Nested Loop  (cost=0.00..89.30 rows=1 width=14)
                                             Join Filter: (substr((li.klic)::text, 1, 2) = (zn.klic)::text)
                                             ->  Index Scan using pkey_idzatrid on zatrid zn  (cost=0.00..8.27 rows=1 width=10)
                                                   Index Cond: (idzatrid = 1)
                                             ->  Seq Scan on zatrid li  (cost=0.00..80.96 rows=5 width=10)
                                                   Filter: (length((li.klic)::text) = 6)
                                       ->  Bitmap Heap Scan on zbozi zb  (cost=4.42..84.88 rows=22 width=53)
                                             Recheck Cond: (zb.idzatrid = li.idzatrid)
                                             Filter: (zb.typkarty = 'Z'::bpchar)
                                             ->  Bitmap Index Scan on zbozi_idzatrid_idx  (cost=0.00..4.42 rows=22 width=0)
                                                   Index Cond: (zb.idzatrid = li.idzatrid)
                                 ->  Seq Scan on znacky zna  (cost=0.00..3.05 rows=1 width=4)
                                       Filter: (zna.idznacky = 1)
                           ->  Seq Scan on zatrid ra  (cost=0.00..75.64 rows=1064 width=6)
                     ->  Hash Left Join  (cost=1560.01..2723.68 rows=23 width=50)
                           Hash Cond: (zs.idstatuszb = sz.idstatuszb)
                           ->  Merge Join  (cost=1556.48..2719.84 rows=23 width=12)
                                 Merge Cond: (zs.idzbozi = zb_statusy.idzbozi)
                                 Join Filter: (zs.zmeneno = (max(zb_statusy.zmeneno)))
                                 ->  Index Scan using zb_statusy_idzbozi on zb_statusy zs  (cost=0.00..782.79 rows=19028 width=12)
                                 ->  Sort  (cost=1556.48..1588.66 rows=12870 width=8)
                                       Sort Key: zb_statusy.idzbozi
                                       ->  HashAggregate  (cost=388.42..549.30 rows=12870 width=8)
                                             ->  Seq Scan on zb_statusy  (cost=0.00..293.28 rows=19028 width=8)
                           ->  Hash  (cost=3.47..3.47 rows=5 width=42)
                                 ->  Hash Left Join  (cost=2.35..3.47 rows=5 width=42)
                                       Hash Cond: (sz.idkodpopis = ko.idkodpopis)
                                       ->  Seq Scan on statusy_zb sz  (cost=0.00..1.05 rows=5 width=42)
                                       ->  Hash  (cost=1.60..1.60 rows=60 width=8)
                                             ->  Seq Scan on ikodypopis ko  (cost=0.00..1.60 rows=60 width=8)
               ->  Index Scan using zb_ceny_idzbozi on zb_ceny  (cost=0.00..0.45 rows=1 width=20)
                     Index Cond: (zb.idzbozi = zb_ceny.idzbozi)
                     Filter: ((zb_ceny.typceny = 1) AND (('now'::text)::date >= zb_ceny.platiod) AND (('now'::text)::date <= COALESCE(zb_ceny.platido, '2100-01-01'::date)))
         ->  HashAggregate  (cost=270.20..324.64 rows=1361 width=8)
               ->  Seq Scan on zb_cenik  (cost=0.00..217.23 rows=3027 width=8)
                     Filter: (('2011-03-30'::date >= platiod) AND ('2011-03-30'::date <= COALESCE(platido, '2100-01-01'::date)))
(48 rows)

[okbob]

poslete jeste vysledek EXPLAIN ANALYZE

[okbob]

a poslete jeste ty samotne dotazy :)

[chinook]

select zb.idzbozi, zb.TypKarty, st.Status, moc.CenaBez, zb.CisloZbozi, zb.NazevZbozi, st.PopisStZb, zb.stavsklad, moc.
vis-# CenasDPH
vis-#                , coalesce(cen.Cen3=1, false) as Cen3, coalesce(cen.Cen1=1, false) as Cen1, coalesce(cen.Cen5=1, false) as Cen5
vis-#         , coalesce(cen.OCen3=1, false) as OCen3, coalesce(cen.OCen1=1, false) as OCen1, coalesce(cen.OCen5=1, false) as OCen5
vis-#           from visgl.zbozi zb
vis-#           left join (
vis(#                 select idzbozi , max(case when idcenik = 3 then 1 else 0 end) as Cen3, max(case when idcenik = 1 then 1 else 0 end) as Cen1, max(case when idcenik = 5 then 1 else 0 end) as Cen5
vis(#         , max(case when idcenik = 3 then 1 else 0 end) as OCen3, max(case when idcenik = 1 then 1 else 0 end) as OCen1, max(case when idcenik = 5 then 1 else 0 end) as OCen5
vis(#                   from visgl.zb_cenik
vis(#               where '2011-03-30' BETWEEN PlatiOd AND coalesce(PlatiDo, '2100-01-01')
vis(#                  group by 1
vis(#          ) cen on zb.idzbozi = cen.idzbozi
vis-#           left join visgl.Zb_Status_Akt st ON zb.IDZbozi = st.IDZbozi
vis-#           left join visgl.Zb_Cena_Akt moc ON zb.IDZbozi = moc.IDZbozi
vis-#           left join visgl.Zatrid_LRZ lrz ON zb.IDZatrid = lrz.IDLinie
vis-#           left join visgl.Znacky zna ON lrz.IDZnacky = zna.IDZnacky
vis-#          where 1=1  AND zb.TypKarty = 'Z' AND (lrz.IDZnacky IN ( 1))
vis-#          order by zb.TypKarty desc, zb.NazevZbozi
vis-# ;

 select cast(Sum(vydejpol0_.pocetks) as int4) as col_0_0_ from visgl.vydejpol vydejpol0_, visgl.zbozi zbozi1_, visgl.vydejdl vydejdl2_ where vydejpol0_.idzbozi=zbozi1_.idzbozi and vydejpol0_.iddl=vydejdl2_.iddl  and zbozi1_.idzatrid=((53)::int4) and vydejdl2_.idprodejny=((317)::int4) and vydejdl2_.datum>=((E'2010-03-29 00:00:00.000000')::timestamptz) and vydejpol0_.typradku='Z'
vis-# ;

[chinook]

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=27666.09..27666.11 rows=1 width=4) (actual time=95.882..95.883 rows=1 loops=1)
   ->  Nested Loop  (cost=6.01..27666.09 rows=1 width=4) (actual time=18.954..95.862 rows=22 loops=1)
         ->  Nested Loop  (cost=6.01..27586.29 rows=267 width=8) (actual time=18.472..86.896 rows=2002 loops=1)
               ->  Index Scan using vydejdl_idprodejny_idx on vydejdl vydejdl2_  (cost=0.00..583.21 rows=39 width=4) (actual time=18.391..18.967 rows=122 loops=1)
                     Index Cond: (idprodejny = 317)
                     Filter: (datum >= '2010-03-29 00:00:00+02'::timestamp with time zone)
               ->  Bitmap Heap Scan on vydejpol vydejpol0_  (cost=6.01..689.74 rows=212 width=12) (actual time=0.531..0.546 rows=16 loops=122)
                     Recheck Cond: (vydejpol0_.iddl = vydejdl2_.iddl)
                     Filter: (vydejpol0_.typradku = 'Z'::bpchar)
                     ->  Bitmap Index Scan on vydejpol_iddl  (cost=0.00..5.98 rows=212 width=0) (actual time=0.523..0.523 rows=24 loops=122)
                           Index Cond: (vydejpol0_.iddl = vydejdl2_.iddl)
         ->  Index Scan using pkey_idzbozi on zbozi zbozi1_  (cost=0.00..0.29 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=2002)
               Index Cond: (zbozi1_.idzbozi = vydejpol0_.idzbozi)
               Filter: (zbozi1_.idzatrid = 53)
 Total runtime: 95.971 ms
(15 rows)

 QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=3349.08..3349.08 rows=1 width=211) (actual time=9332.918..9332.952 rows=138 loops=1)
   Sort Key: zb.nazevzbozi
   Sort Method:  quicksort  Memory: 49kB
   ->  Nested Loop Left Join  (cost=1834.64..3349.07 rows=1 width=211) (actual time=66.152..9331.983 rows=138 loops=1)
         Join Filter: (zb.idzbozi = zb_cenik.idzbozi)
         ->  Nested Loop Left Join  (cost=1564.44..2993.79 rows=1 width=187) (actual time=53.922..8986.338 rows=138 loops=1)
               ->  Nested Loop Left Join  (cost=1564.44..2993.33 rows=1 width=171) (actual time=53.892..8984.451 rows=138 loops=1)
                     Join Filter: (zb.idzbozi = zs.idzbozi)
                     ->  Nested Loop Left Join  (cost=4.42..269.13 rows=1 width=49) (actual time=13.984..252.988 rows=138 loops=1)
                           Join Filter: (substr((li.klic)::text, 1, 4) = (ra.klic)::text)
                           ->  Nested Loop Left Join  (cost=4.42..177.53 rows=1 width=55) (actual time=13.962..112.700 rows=138 loops=1)
                                 Join Filter: (zn.idzatrid = zna.idznacky)
                                 ->  Nested Loop  (cost=4.42..174.46 rows=1 width=59) (actual time=13.931..109.832 rows=138 loops=1)
                                       ->  Nested Loop  (cost=0.00..89.30 rows=1 width=14) (actual time=0.089..1.148 rows=12 loops=1)
                                             Join Filter: (substr((li.klic)::text, 1, 2) = (zn.klic)::text)
                                             ->  Index Scan using pkey_idzatrid on zatrid zn  (cost=0.00..8.27 rows=1 width=10) (actual time=0.038..0.040 rows=1 loops=1)
                                                   Index Cond: (idzatrid = 1)
                                             ->  Seq Scan on zatrid li  (cost=0.00..80.96 rows=5 width=10) (actual time=0.044..0.654 rows=798 loops=1)
                                                   Filter: (length((li.klic)::text) = 6)
                                       ->  Bitmap Heap Scan on zbozi zb  (cost=4.42..84.88 rows=22 width=53) (actual time=8.969..9.040 rows=12 loops=12)
                                             Recheck Cond: (zb.idzatrid = li.idzatrid)
                                             Filter: (zb.typkarty = 'Z'::bpchar)
                                             ->  Bitmap Index Scan on zbozi_idzatrid_idx  (cost=0.00..4.42 rows=22 width=0) (actual time=8.934..8.934 rows=34 loops=12)
                                                   Index Cond: (zb.idzatrid = li.idzatrid)
                                 ->  Seq Scan on znacky zna  (cost=0.00..3.05 rows=1 width=4) (actual time=0.010..0.018 rows=1 loops=138)
                                       Filter: (zna.idznacky = 1)
                           ->  Seq Scan on zatrid ra  (cost=0.00..75.64 rows=1064 width=6) (actual time=0.006..0.363 rows=1064 loops=138)
                     ->  Hash Left Join  (cost=1560.01..2723.68 rows=23 width=50) (actual time=0.293..58.861 rows=14735 loops=138)
                           Hash Cond: (zs.idstatuszb = sz.idstatuszb)
                           ->  Merge Join  (cost=1556.48..2719.84 rows=23 width=12) (actual time=0.290..42.820 rows=14735 loops=138)
                                 Merge Cond: (zs.idzbozi = zb_statusy.idzbozi)
                                 Join Filter: (zs.zmeneno = (max(zb_statusy.zmeneno)))
                                 ->  Index Scan using zb_statusy_idzbozi on zb_statusy zs  (cost=0.00..782.79 rows=19028 width=12) (actual time=0.012..12.367 rows=19040 loops=138)
                                 ->  Sort  (cost=1556.48..1588.66 rows=12870 width=8) (actual time=0.277..5.505 rows=19040 loops=138)
                                       Sort Key: zb_statusy.idzbozi
                                       Sort Method:  quicksort  Memory: 832kB
                                       ->  HashAggregate  (cost=388.42..549.30 rows=12870 width=8) (actual time=18.613..25.000 rows=14735 loops=1)
                                             ->  Seq Scan on zb_statusy  (cost=0.00..293.28 rows=19028 width=8) (actual time=0.006..6.174 rows=19040 loops=1)
                           ->  Hash  (cost=3.47..3.47 rows=5 width=42) (actual time=0.086..0.086 rows=5 loops=1)
                                 ->  Hash Left Join  (cost=2.35..3.47 rows=5 width=42) (actual time=0.075..0.082 rows=5 loops=1)
                                       Hash Cond: (sz.idkodpopis = ko.idkodpopis)
                                       ->  Seq Scan on statusy_zb sz  (cost=0.00..1.05 rows=5 width=42) (actual time=0.005..0.005 rows=5 loops=1)
                                       ->  Hash  (cost=1.60..1.60 rows=60 width=8) (actual time=0.063..0.063 rows=60 loops=1)
                                             ->  Seq Scan on ikodypopis ko  (cost=0.00..1.60 rows=60 width=8) (actual time=0.007..0.033 rows=60 loops=1)
               ->  Index Scan using zb_ceny_idzbozi on zb_ceny  (cost=0.00..0.45 rows=1 width=20) (actual time=0.011..0.012 rows=1 loops=138)
                     Index Cond: (zb.idzbozi = zb_ceny.idzbozi)
                     Filter: ((zb_ceny.typceny = 1) AND (('now'::text)::date >= zb_ceny.platiod) AND (('now'::text)::date <= COALESCE(zb_ceny.platido, '2100-01-01'::date)))
         ->  HashAggregate  (cost=270.20..324.64 rows=1361 width=8) (actual time=0.072..1.643 rows=2980 loops=138)
               ->  Seq Scan on zb_cenik  (cost=0.00..217.23 rows=3027 width=8) (actual time=0.013..4.594 rows=5964 loops=1)
                     Filter: (('2011-03-30'::date >= platiod) AND ('2011-03-30'::date <= COALESCE(platido, '2100-01-01'::date)))
 Total runtime: 9333.427 ms
(51 rows)

[okbob]

Ten první dotaz je v pohodě - 95 ms, u toho druhého je to divočina - nesedí Vám statistiky, tak zkuste před spuštěním provést příkaz "SET enable_nestedloop to 'off';"

[chinook]

A co to brzdi CPU? Musí to brzdit nějakej HW ne?

[Heron]

To sice ano, ale (obecně, ten dotaz jsem nezkoumal, ani nehodlám) si u DB příliš nepomůžeš lepším HW, pokud jsou špatně napsané dotazy a navržená schémata. Na blbě napsaný dotaz (se složitostí horší než lineární) nepomůže ani ten nejlepší dostupný HW. Nejprve je vždy nutné opravit dotazy.

[happy barney]

nejak v tom asi stracam prehlad ... odkial sa mu tam vzali tie substr ?

[okbob]

z pohledů. Je to dotaz nad pohledama - hlavně nějak nesedí podmínka Join Filter: (zb.idzbozi = zs.idzbozi).

Správná připomínka od Herona, kdy jste naposledy ANALYZEovali?

[Heron]

nesedí Vám statistiky

To mi připomnělo, že tu ještě nikde není informace o verzi PostgreSQL (nebo jsem to přehlíd) a také jestli běží (auto)vacuum a analyze. Ani z toho úvodního dumpu nastavení to není patrné.

[chinook]

verze postgres je 8.3. Tady sjou konf. parm.

 add_missing_from                | off                                      | Automatically adds missing table references to FROM clauses.
 allow_system_table_mods         | off                                      | Allows modifications of the structure of system tables.
 archive_command                 | (disabled)                               | Sets the shell command that will be called to archive a WAL file.
 archive_mode                    | off                                      | Allows archiving of WAL files using archive_command.
 archive_timeout                 | 0                                        | Forces a switch to the next xlog file if a new file has not been started within N seconds.
 array_nulls                     | on                                       | Enable input of NULL elements in arrays.
 authentication_timeout          | 1min                                     | Sets the maximum allowed time to complete client authentication.
 autovacuum                      | on                                       | Starts the autovacuum subprocess.
 autovacuum_analyze_scale_factor | 0.1                                      | Number of tuple inserts, updates or deletes prior to analyze as a fraction of reltuples.
 autovacuum_analyze_threshold    | 50                                       | Minimum number of tuple inserts, updates or deletes prior to analyze.
 autovacuum_freeze_max_age       | 200000000                                | Age at which to autovacuum a table to prevent transaction ID wraparound.
 autovacuum_max_workers          | 3                                        | Sets the maximum number of simultaneously running autovacuum worker processes.
 autovacuum_naptime              | 1min                                     | Time to sleep between autovacuum runs.
 autovacuum_vacuum_cost_delay    | 20ms                                     | Vacuum cost delay in milliseconds, for autovacuum.
 autovacuum_vacuum_cost_limit    | -1                                       | Vacuum cost amount available before napping, for autovacuum.
 autovacuum_vacuum_scale_factor  | 0.2                                      | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
 autovacuum_vacuum_threshold     | 50                                       | Minimum number of tuple updates or deletes prior to vacuum.
 backslash_quote                 | safe_encoding                            | Sets whether "\'" is allowed in string literals.
 bgwriter_delay                  | 200ms                                    | Background writer sleep time between rounds.
 bgwriter_lru_maxpages           | 100                                      | Background writer maximum number of LRU pages to flush per round.
 bgwriter_lru_multiplier         | 2                                        | Background writer multiplier on average buffers to scan per round.
 block_size                      | 8192                                     | Shows the size of a disk block.
 bonjour_name                    |                                          | Sets the Bonjour broadcast service name.
 check_function_bodies           | on                                       | Check function bodies during CREATE FUNCTION.
 checkpoint_completion_target    | 0.5                                      | Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval.
 checkpoint_segments             | 10                                       | Sets the maximum distance in log segments between automatic WAL checkpoints.
 checkpoint_timeout              | 5min                                     | Sets the maximum time between automatic WAL checkpoints.
 checkpoint_warning              | 30s                                      | Enables warnings if checkpoint segments are filled more frequently than this.
 client_encoding                 | UTF8                                     | Sets the client's character set encoding.
 client_min_messages             | notice                                   | Sets the message levels that are sent to the client.
 commit_delay                    | 0                                        | Sets the delay in microseconds between transaction commit and flushing WAL to disk.
 commit_siblings                 | 5                                        | Sets the minimum concurrent open transactions before performing commit_delay.
 config_file                     | /etc/postgresql/8.3/main/postgresql.conf | Sets the server's main configuration file.
 constraint_exclusion            | off                                      | Enables the planner to use constraints to optimize queries.
 cpu_index_tuple_cost            | 0.005                                    | Sets the planner's estimate of the cost of processing each index entry during an index scan.
 cpu_operator_cost               | 0.0025                                   | Sets the planner's estimate of the cost of processing each operator or function call.
 cpu_tuple_cost                  | 0.01                                     | Sets the planner's estimate of the cost of processing each tuple (row).
 custom_variable_classes         |                                          | Sets the list of known custom variable classes.
 data_directory                  | /var/lib/postgresql/8.3/main             | Sets the server's data directory.
 DateStyle                       | ISO, MDY                                 | Sets the display format for date and time values.
 db_user_namespace               | off                                      | Enables per-database user names.
 deadlock_timeout                | 1s                                       | Sets the time to wait on a lock before checking for deadlock.
 debug_assertions                | off                                      | Turns on various assertion checks.
 debug_pretty_print              | off                                      | Indents parse and plan tree displays.
 debug_print_parse               | off                                      | Prints the parse tree to the server log.
 debug_print_plan                | off                                      | Prints the execution plan to server log.
 debug_print_rewritten           | off                                      | Prints the parse tree after rewriting to server log.
 default_statistics_target       | 10                                       | Sets the default statistics target.
 default_tablespace              |                                          | Sets the default tablespace to create tables and indexes in.
 default_text_search_config      | pg_catalog.english                       | Sets default text search configuration.
 default_transaction_isolation   | read committed                           | Sets the transaction isolation level of each new transaction.
 default_transaction_read_only   | off                                      | Sets the default read-only status of new transactions.
 default_with_oids               | off                                      | Create new tables with OIDs by default.
 dynamic_library_path            | $libdir                                  | Sets the path for dynamically loadable modules.
 effective_cache_size            | 2560MB                                   | Sets the planner's assumption about the size of the disk cache.
 enable_bitmapscan               | on                                       | Enables the planner's use of bitmap-scan plans.
 enable_hashagg                  | on                                       | Enables the planner's use of hashed aggregation plans.
 enable_hashjoin                 | on                                       | Enables the planner's use of hash join plans.
 enable_indexscan                | on                                       | Enables the planner's use of index-scan plans.
 enable_mergejoin                | on                                       | Enables the planner's use of merge join plans.
 enable_nestloop                 | off                                      | Enables the planner's use of nested-loop join plans.
 enable_seqscan                  | on                                       | Enables the planner's use of sequential-scan plans.
 enable_sort                     | on                                       | Enables the planner's use of explicit sort steps.
 enable_tidscan                  | on                                       | Enables the planner's use of TID scan plans.
 escape_string_warning           | on                                       | Warn about backslash escapes in ordinary string literals.
 explain_pretty_print            | on                                       | Uses the indented output format for EXPLAIN VERBOSE.
 external_pid_file               | /var/run/postgresql/8.3-main.pid         | Writes the postmaster PID to the specified file.
 extra_float_digits              | 0                                        | Sets the number of digits displayed for floating-point values.
 from_collapse_limit             | 8                                        | Sets the FROM-list size beyond which subqueries are not collapsed.
 fsync                           | on                                       | Forces synchronization of updates to disk.
 full_page_writes                | on                                       | Writes full pages to WAL when first modified after a checkpoint.
 geqo                            | on                                       | Enables genetic query optimization.
 geqo_effort                     | 5                                        | GEQO: effort is used to set the default for other GEQO parameters.
 geqo_generations                | 0                                        | GEQO: number of iterations of the algorithm.
 geqo_pool_size                  | 0                                        | GEQO: number of individuals in the population.
 geqo_selection_bias             | 2                                        | GEQO: selective pressure within the population.
 geqo_threshold                  | 12                                       | Sets the threshold of FROM items beyond which GEQO is used.
 gin_fuzzy_search_limit          | 0                                        | Sets the maximum allowed result for exact search by GIN.
 hba_file                        | /etc/postgresql/8.3/main/pg_hba.conf     | Sets the server's "hba" configuration file.
 ident_file                      | /etc/postgresql/8.3/main/pg_ident.conf   | Sets the server's "ident" configuration file.
 ignore_system_indexes           | off                                      | Disables reading from system indexes.
 integer_datetimes               | on                                       | Datetimes are integer based.
 join_collapse_limit             | 8                                        | Sets the FROM-list size beyond which JOIN constructs are not flattened.
 krb_caseins_users               | off                                      | Sets whether Kerberos and GSSAPI user names should be treated as case-insensitive.
 krb_realm                       |                                          | Sets realm to match Kerberos and GSSAPI users against.
 krb_server_hostname             |                                          | Sets the hostname of the Kerberos server.
 krb_server_keyfile              | FILE:/etc/postgresql-common/krb5.keytab  | Sets the location of the Kerberos server key file.
 krb_srvname                     | postgres                                 | Sets the name of the Kerberos service.
 lc_collate                      | en_US.UTF-8                              | Shows the collation order locale.
 lc_ctype                        | en_US.UTF-8                              | Shows the character classification and case conversion locale.
 lc_messages                     | en_US.UTF-8                              | Sets the language in which messages are displayed.
 lc_monetary                     | en_US.UTF-8                              | Sets the locale for formatting monetary amounts.
 lc_numeric                      | en_US.UTF-8                              | Sets the locale for formatting numbers.
 lc_time                         | en_US.UTF-8                              | Sets the locale for formatting date and time values.
 listen_addresses                | 192.168.120.4,127.0.0.1                  | Sets the host name or IP address(es) to listen to.
 local_preload_libraries         |                                          | Lists shared libraries to preload into each backend.
 log_autovacuum_min_duration     | -1                                       | Sets the minimum execution time above which autovacuum actions will be logged.
 log_checkpoints                 | off                                      | Logs each checkpoint.
 log_connections                 | off                                      | Logs each successful connection.
 log_destination                 | stderr                                   | Sets the destination for server log output.
 log_directory                   | pg_log                                   | Sets the destination directory for log files.
 log_disconnections              | off                                      | Logs end of a session, including duration.
 log_duration                    | off                                      | Logs the duration of each completed SQL statement.
 log_error_verbosity             | default                                  | Sets the verbosity of logged messages.
 log_executor_stats              | off                                      | Writes executor performance statistics to the server log.
 log_filename                    | postgresql-%Y-%m-%d_%H%M%S.log           | Sets the file name pattern for log files.
 log_hostname                    | on                                       | Logs the host name in the connection logs.
 log_line_prefix                 | %t:%r:%u@%d:[%p]:                        | Controls information prefixed to each log line.
log_lock_waits                  | on                                       | Logs long lock waits.
 log_min_duration_statement      | 2s                                       | Sets the minimum execution time above which statements will be logged.
 log_min_error_statement         | error                                    | Causes all statements generating error at or above this level to be logged.
 log_min_messages                | notice                                   | Sets the message levels that are logged.
 log_parser_stats                | off                                      | Writes parser performance statistics to the server log.
 log_planner_stats               | off                                      | Writes planner performance statistics to the server log.
 log_rotation_age                | 1d                                       | Automatic log file rotation will occur after N minutes.
 log_rotation_size               | 10MB                                     | Automatic log file rotation will occur after N kilobytes.
 log_statement                   | none                                     | Sets the type of statements logged.
 log_statement_stats             | off                                      | Writes cumulative performance statistics to the server log.
 log_temp_files                  | -1                                       | Log the use of temporary files larger than this number of kilobytes.
 log_timezone                    | localtime                                | Sets the time zone to use in log messages.
 log_truncate_on_rotation        | off                                      | Truncate existing log files of same name during log rotation.
 logging_collector               | off                                      | Start a subprocess to capture stderr output and/or csvlogs into log files.
 maintenance_work_mem            | 64MB                                     | Sets the maximum memory to be used for maintenance operations.
 max_connections                 | 50                                       | Sets the maximum number of concurrent connections.
 max_files_per_process           | 1000                                     | Sets the maximum number of simultaneously open files for each server process.
 max_fsm_pages                   | 153600                                   | Sets the maximum number of disk pages for which free space is tracked.
 max_fsm_relations               | 1000                                     | Sets the maximum number of tables and indexes for which free space is tracked.
 max_function_args               | 100                                      | Shows the maximum number of function arguments.
 max_identifier_length           | 63                                       | Shows the maximum identifier length.
 max_index_keys                  | 32                                       | Shows the maximum number of index keys.
 max_locks_per_transaction       | 64                                       | Sets the maximum number of locks per transaction.
 max_prepared_transactions       | 5                                        | Sets the maximum number of simultaneously prepared transactions.
 max_stack_depth                 | 2MB                                      | Sets the maximum stack depth, in kilobytes.
 password_encryption             | on                                       | Encrypt passwords.
 port                            | 5432                                     | Sets the TCP port the server listens on.
 post_auth_delay                 | 0                                        | Waits N seconds on connection startup after authentication.
 pre_auth_delay                  | 0                                        | Waits N seconds on connection startup before authentication.
 random_page_cost                | 4                                        | Sets the planner's estimate of the cost of a nonsequentially fetched disk page.
 regex_flavor                    | advanced                                 | Sets the regular expression "flavor".
 search_path                     | "$user",public                           | Sets the schema search order for names that are not schema-qualified.
 seq_page_cost                   | 1                                        | Sets the planner's estimate of the cost of a sequentially fetched disk page.
 server_encoding                 | UTF8                                     | Sets the server (database) character set encoding.
 server_version                  | 8.3.14                                   | Shows the server version.
 server_version_num              | 80314                                    | Shows the server version as an integer.
 session_replication_role        | origin                                   | Sets the session's behavior for triggers and rewrite rules.
 shared_buffers                  | 1GB                                      | Sets the number of shared memory buffers used by the server.
 shared_preload_libraries        |                                          | Lists shared libraries to preload into server.
 silent_mode                     | off                                      | Runs the server silently.
 sql_inheritance                 | on                                       | Causes subtables to be included by default in various commands.
 ssl                             | off                                      | Enables SSL connections.
 ssl_ciphers                     | ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH        | Sets the list of allowed SSL ciphers.
 ssl_renegotiation_limit         | 512MB                                    | Set the amount of traffic to send and receive before renegotiating the encryption keys.
 standard_conforming_strings     | off                                      | Causes '...' strings to treat backslashes literally.
 statement_timeout               | 0                                        | Sets the maximum allowed duration of any statement.
 superuser_reserved_connections  | 3                                        | Sets the number of connection slots reserved for superusers.
 synchronize_seqscans            | on                                       | Enable synchronized sequential scans.
 synchronous_commit              | on                                       | Sets immediate fsync at commit.
 syslog_facility                 | LOCAL0                                   | Sets the syslog "facility" to be used when syslog enabled.
 syslog_ident                    | postgres                                 | Sets the program name used to identify PostgreSQL messages in syslog.
 tcp_keepalives_count            | 0                                        | Maximum number of TCP keepalive retransmits.
 tcp_keepalives_idle             | 0                                        | Time between issuing TCP keepalives.
 tcp_keepalives_interval         | 0                                        | Time between TCP keepalive retransmits.
 temp_buffers                    | 1024                                     | Sets the maximum number of temporary buffers used by each session.
temp_tablespaces                |                                          | Sets the tablespace(s) to use for temporary tables and sort files.
 TimeZone                        | localtime                                | Sets the time zone for displaying and interpreting time stamps.
 timezone_abbreviations          | Default                                  | Selects a file of time zone abbreviations.
 trace_notify                    | off                                      | Generates debugging output for LISTEN and NOTIFY.
 trace_sort                      | off                                      | Emit information about resource usage in sorting.
 track_activities                | on                                       | Collects information about executing commands.
 track_counts                    | on                                       | Collects statistics on database activity.
 transaction_isolation           | read committed                           | Sets the current transaction's isolation level.
 transaction_read_only           | off                                      | Sets the current transaction's read-only status.
 transform_null_equals           | off                                      | Treats "expr=NULL" as "expr IS NULL".
 unix_socket_directory           | /var/run/postgresql                      | Sets the directory where the Unix-domain socket will be created.
 unix_socket_group               |                                          | Sets the owning group of the Unix-domain socket.
 unix_socket_permissions         | 511                                      | Sets the access permissions of the Unix-domain socket.
 update_process_title            | on                                       | Updates the process title to show the active SQL command.
 vacuum_cost_delay               | 0                                        | Vacuum cost delay in milliseconds.
 vacuum_cost_limit               | 200                                      | Vacuum cost amount available before napping.
 vacuum_cost_page_dirty          | 20                                       | Vacuum cost for a page dirtied by vacuum.
 vacuum_cost_page_hit            | 1                                        | Vacuum cost for a page found in the buffer cache.
 vacuum_cost_page_miss           | 10                                       | Vacuum cost for a page not found in the buffer cache.
 vacuum_freeze_min_age           | 100000000                                | Minimum age at which VACUUM should freeze a table row.
 wal_buffers                     | 64kB                                     | Sets the number of disk-page buffers in shared memory for WAL.
 wal_sync_method                 | fdatasync                                | Selects the method used for forcing WAL updates to disk.
 wal_writer_delay                | 200ms                                    | WAL writer sleep time between WAL flushes.
 work_mem                        | 50MB                                     | Sets the maximum memory to be used for query workspaces.
 xmlbinary                       | base64                                   | Sets how binary values are to be encoded in XML.
 xmloption                       | content                                  | Sets whether XML data in implicit parsing and serialization operations is to be considered as documents or content fragments.
 zero_damaged_pages              | off                                      | Continues processing past damaged page headers.
(190 rows)

Je mně jasné, že efektivnější než nákup HW bude optimalizace dotazů, ale chci mít jistotu, že to nebrzdí konfigurace postgresu.

[chinook]

před spuštěním jsem provdl příkaz "SET enable_nestedloop to 'off';" a příkaz trval jen 200ms. Čím to je?

[chinook]

Když jsem tu db vydumpoval a hodil na jiny stroj 64bit(ten předtím je jen 32bit) postgres verze 8.4, tak to trva asi 200ms. Může to být verzí DB?

[Heron]

Ne, tohle vypadá na nedostatečnou analýzu na původním stroji. Při loadu na jiný stroj se provede analýza, reindex a také jsou datové soubory čisté (tj. ještě nepotřebují vacuum).

Zkuste na vaší původní DB udělat VACUUM FULL ANALYZE, mělo by to pomoci (pokud ještě nemůže být problém ve špatném provádecím plánu k vůli jinému optimalizátoru dotazů).

[chinook]

Po provedeni prikazu

'vacuum full analyze;'

se cas nijak nezmenil.

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=3254.21..3254.21 rows=1 width=211) (actual time=9325.087..9325.129 rows=138 loops=1)
   Sort Key: zb.nazevzbozi
   Sort Method:  quicksort  Memory: 49kB
   ->  Nested Loop Left Join  (cost=1861.57..3254.20 rows=1 width=211) (actual time=93.950..9324.131 rows=138 loops=1)
         Join Filter: (zb.idzbozi = zb_cenik.idzbozi)
         ->  Nested Loop Left Join  (cost=1606.01..2915.00 rows=1 width=187) (actual time=41.060..8933.466 rows=138 loops=1)
               Join Filter: (zb.idzbozi = zs.idzbozi)
               ->  Nested Loop Left Join  (cost=4.42..149.11 rows=1 width=65) (actual time=2.062..143.264 rows=138 loops=1)
                     ->  Nested Loop Left Join  (cost=4.42..148.66 rows=1 width=49) (actual time=2.043..141.194 rows=138 loops=1)
                           Join Filter: (substr((li.klic)::text, 1, 4) = (ra.klic)::text)
                           ->  Nested Loop Left Join  (cost=4.42..110.06 rows=1 width=55) (actual time=1.082..5.322 rows=138 loops=1)
                                 Join Filter: (zn.idzatrid = zna.idznacky)
                                 ->  Nested Loop  (cost=4.42..106.99 rows=1 width=59) (actual time=1.066..2.348 rows=138 loops=1)
                                       ->  Nested Loop  (cost=0.00..36.30 rows=1 width=14) (actual time=1.017..1.069 rows=12 loops=1)
                                             Join Filter: (substr((li.klic)::text, 1, 2) = (zn.klic)::text)
                                             ->  Index Scan using pkey_idzatrid on zatrid zn  (cost=0.00..8.27 rows=1 width=10) (actual time=0.010..0.011 rows=1 loops=1)
                                                   Index Cond: (idzatrid = 1)
                                             ->  Seq Scan on zatrid li  (cost=0.00..27.96 rows=5 width=10) (actual time=0.012..0.605 rows=798 loops=1)
                                                   Filter: (length((li.klic)::text) = 6)
                                       ->  Bitmap Heap Scan on zbozi zb  (cost=4.42..70.42 rows=22 width=53) (actual time=0.035..0.088 rows=12 loops=12)
                                             Recheck Cond: (zb.idzatrid = li.idzatrid)
                                             Filter: (zb.typkarty = 'Z'::bpchar)
                                             ->  Bitmap Index Scan on zbozi_idzatrid_idx  (cost=0.00..4.42 rows=22 width=0) (actual time=0.020..0.020 rows=34 loops=12)
                                                   Index Cond: (zb.idzatrid = li.idzatrid)
                                 ->  Seq Scan on znacky zna  (cost=0.00..3.05 rows=1 width=4) (actual time=0.011..0.019 rows=1 loops=138)
                                       Filter: (zna.idznacky = 1)
                           ->  Seq Scan on zatrid ra  (cost=0.00..22.64 rows=1064 width=6) (actual time=0.003..0.333 rows=1064 loops=138)
                     ->  Index Scan using zb_ceny_idzbozi on zb_ceny  (cost=0.00..0.44 rows=1 width=20) (actual time=0.012..0.013 rows=1 loops=138)
                           Index Cond: (zb.idzbozi = zb_ceny.idzbozi)
                           Filter: ((zb_ceny.typceny = 1) AND (('now'::text)::date >= zb_ceny.platiod) AND (('now'::text)::date <= COALESCE(zb_ceny.platido, '2100-01-01'::date)))
               ->  Hash Left Join  (cost=1601.59..2765.37 rows=23 width=50) (actual time=0.297..59.049 rows=14735 loops=138)
                     Hash Cond: (zs.idstatuszb = sz.idstatuszb)
                     ->  Merge Join  (cost=1598.06..2761.52 rows=23 width=12) (actual time=0.295..42.808 rows=14735 loops=138)
                           Merge Cond: (zs.idzbozi = zb_statusy.idzbozi)
                           Join Filter: (zs.zmeneno = (max(zb_statusy.zmeneno)))
                           ->  Index Scan using zb_statusy_idzbozi on zb_statusy zs  (cost=0.00..782.66 rows=19040 width=12) (actual time=0.011..12.400 rows=19040 loops=138)
                           ->  Sort  (cost=1598.06..1631.29 rows=13292 width=8) (actual time=0.281..5.542 rows=19040 loops=138)
                                 Sort Key: zb_statusy.idzbozi
                                 Sort Method:  quicksort  Memory: 832kB
                                 ->  HashAggregate  (cost=388.60..554.75 rows=13292 width=8) (actual time=18.451..25.219 rows=14735 loops=1)
                                       ->  Seq Scan on zb_statusy  (cost=0.00..293.40 rows=19040 width=8) (actual time=0.006..5.846 rows=19040 loops=1)
                     ->  Hash  (cost=3.47..3.47 rows=5 width=42) (actual time=0.082..0.082 rows=5 loops=1)
                           ->  Hash Left Join  (cost=2.35..3.47 rows=5 width=42) (actual time=0.071..0.077 rows=5 loops=1)
                                 Hash Cond: (sz.idkodpopis = ko.idkodpopis)
                                 ->  Seq Scan on statusy_zb sz  (cost=0.00..1.05 rows=5 width=42) (actual time=0.003..0.004 rows=5 loops=1)
                                 ->  Hash  (cost=1.60..1.60 rows=60 width=8) (actual time=0.062..0.062 rows=60 loops=1)
                                       ->  Seq Scan on ikodypopis ko  (cost=0.00..1.60 rows=60 width=8) (actual time=0.005..0.034 rows=60 loops=1)
         ->  HashAggregate  (cost=255.56..309.08 rows=1338 width=8) (actual time=0.382..1.969 rows=2980 loops=138)
               ->  Seq Scan on zb_cenik  (cost=0.00..202.49 rows=3033 width=8) (actual time=12.264..46.749 rows=5964 loops=1)
                     Filter: (('2011-03-30'::date >= platiod) AND ('2011-03-30'::date <= COALESCE(platido, '2100-01-01'::date)))
 Total runtime: 9325.588 ms
(51 rows)

[okbob]

8.4 má ještě podrobnější statistiky - zkuste v 8.3 nastavit default_statistics_target na 100 a provést příkaz ANALYZE

[chinook]

Tak se zlepšil čas zpracování asi o 3s. Ještě nějaký parametr, který by to pomohl zrychlit? Btw. když jsem dal default_statistics_target na 300 žádný rozdíl tam již nebyl.

[okbob]

neexistují žádné magické parametry :).

Tak pro Vás je asi základ migrace na 8.4.

[okbob]

Tak 8.4 už má v defaultu aktivní autovacuum - takže se Vám možná jen aktualizovaly statistiky.

Jinak v každé verzi PostgreSQL se o něco vylepší planner, provedou různé optimalizace - které z většiny případů vedou ke zrychlení pomalejších dotazů. Vždy ovšem záleží na tom, kde a proč to vázne.

[okbob]

Databáze má jakousi představu o datech uvnitř - a na základě této představy (statistik) generuje prováděcí plán dotazu - program pro executor, který vrátí tabulku. Jednou z technik, která se používá je nested_loop - je to klasický cyklus - který se použije tehdy, když se "tělo cyklu" vykonává rychle, nebo když se provádí několik málo iterací - výhodou je malá paměťová náročnost a minimální doba příprav. V okamžiku, kdy nesedí statistiky se může stát, že planner zvolí nested_loop přičemž si myslí, že k výsledku bude potřebovat 1 iteraci nicméně realita je taková, že je potřeba několik set iterací. Optimální plán je jinde - planner měl zvolit jinou techniku, která je třeba pamětově rozežranější nebo potřebuje víc času na přípravu, ovšem ve výsledku je rychlejší. SET enable_nestedloop to off brání v planneru používání nested loopu. Je to ovšem něco, co by se mělo používat opatrně - je hodně případů, kde je nested loop použit správně.

Musí sedět statistiky (být aktuální) - to je základ. Jak sedí (souhlasí) se vyčte z EXPLAIN ANALYZE

[okbob]

kdo to nastavoval - fsync=off

Chcete přijít o data?

[chinook]

Pokud funguje HW, tak o data nepřijdu ne?

[okbob]

A kdo Vám zaručí, že bude fungovat sw? Stačí pořádná špička, a když budete nucen restartovat databázi, tak můžete mít problém. Můžete mít chybu v O.S., můžete mít chybu v PostgreSQL - může se Vám tam někdo nabořit, prostě pokud tam máte data, o která nechcete přijít, tak to nedělejte. Něco jiného je, pokud byste pracoval s generovanými daty, pokud by to fungovalo jako warehouse, který lze vygenerovat znova.