PostgreSQL - vynutenie pouzitia jedneho query planu

[Pavel Stěhule]

Tu je pomaly query plan
https://explain.depesz.com/s/VuoL

Ty odkazy nejsou validni

[Reklama]

[ondrej _]

Nevies mi poradit ako funguje ta stranka?
Nahral som to tam, vybral som moznost anonymizovat a klikol na Submit. Cakal som, ze tie query plany budu na tej adrese dokial ich nezmazem cez link uvedeny na stranke.

[ondrej _]

netusim ako ta stranka funguje.
Posielam anonymizovane plany
Fast

Kód: [Vybrat]

Unique  (cost=72877.07..72877.13 rows=1 width=234) (actual time=0.107..0.121 rows=0 loops=1)
  ->  Sort  (cost=72877.07..72877.08 rows=1 width=234) (actual time=0.104..0.116 rows=0 loops=1)
          Sort Key: romeo_echo_whiskey.quebec_seven, romeo_echo_whiskey.mike_alpha, romeo_echo_whiskey.quebec_whiskey, romeo_echo_whiskey.three, romeo_echo_whiskey.foxtrot_yankee, romeo_echo_whiskey.whiskey_romeo, romeo_echo_whiskey.charlie, romeo_echo_whiskey.tango_seven, romeo_echo_whiskey.quebec_bravo, romeo_echo_whiskey.juliet, romeo_echo_whiskey.seven_papa, romeo_echo_whiskey.romeo_hotel, romeo_echo_whiskey.romeo_echo_quebec, romeo_echo_whiskey.victor, romeo_echo_whiskey.bravo_zulu, romeo_echo_whiskey.quebec_echo, romeo_echo_whiskey.yankee, romeo_echo_whiskey.tango_lima, romeo_echo_whiskey.whiskey_yankee, romeo_echo_whiskey.uniform
          Sort Method: quicksort  Memory: 25kB
        ->  Nested Loop  (cost=566.52..72877.06 rows=1 width=234) (actual time=0.082..0.093 rows=0 loops=1)
              ->  Nested Loop  (cost=566.10..72629.32 rows=487 width=234) (actual time=0.079..0.089 rows=0 loops=1)
                    ->  Bitmap Heap Scan on whiskey_four  (cost=8.85..12.91 rows=1 width=16) (actual time=0.052..0.060 rows=2 loops=1)
                            Recheck Cond: (((((((foxtrot_juliet -> 'papa_november'::text) -> 'quebec_november'::text) ->> 'oscar'::text))::text = 'bravo_november'::text) AND (((((foxtrot_juliet -> 'papa_november'::text) -> 'quebec_november'::text) ->> 'hotel'::text))::text = 'seven_three'::text) AND (((foxtrot_juliet ->> 'foxtrot_kilo'::text))::text = 'seven_alpha'::text)) OR ((((((foxtrot_juliet -> 'golf_six'::text) -> 'quebec_november'::text) ->> 'oscar'::text))::text = 'bravo_november'::text) AND (((((foxtrot_juliet -> 'golf_six'::text) -> 'quebec_november'::text) ->> 'hotel'::text))::text = 'seven_three'::text) AND (((foxtrot_juliet ->> 'foxtrot_kilo'::text))::text = 'six_seven'::text)))
                            Filter: ((juliet)::text = ANY ('romeo_oscar'::text[]))
                            Rows Removed by Filter: 2
                            Heap Blocks: exact=4
                          ->  BitmapOr  (cost=8.85..8.85 rows=1 width=0) (actual time=0.039..0.043 rows=0 loops=1)
                                ->  Bitmap Index Scan on lima_yankee  (cost=0.00..4.42 rows=1 width=0) (actual time=0.027..0.027 rows=4 loops=1)
                                        Index Cond: ((((((foxtrot_juliet -> 'papa_november'::text) -> 'quebec_november'::text) ->> 'oscar'::text))::text = 'bravo_november'::text) AND (((((foxtrot_juliet -> 'papa_november'::text) -> 'quebec_november'::text) ->> 'hotel'::text))::text = 'seven_three'::text) AND (((foxtrot_juliet ->> 'foxtrot_kilo'::text))::text = 'seven_alpha'::text))
                                ->  Bitmap Index Scan on india_hotel  (cost=0.00..4.42 rows=1 width=0) (actual time=0.009..0.010 rows=0 loops=1)
                                        Index Cond: ((((((foxtrot_juliet -> 'golf_six'::text) -> 'quebec_november'::text) ->> 'oscar'::text))::text = 'bravo_november'::text) AND (((((foxtrot_juliet -> 'golf_six'::text) -> 'quebec_november'::text) ->> 'hotel'::text))::text = 'seven_three'::text) AND (((foxtrot_juliet ->> 'foxtrot_kilo'::text))::text = 'six_seven'::text))
                    ->  Bitmap Heap Scan on romeo_echo_whiskey  (cost=557.24..72388.42 rows=22798 width=234) (actual time=0.008..0.009 rows=0 loops=2)
                            Recheck Cond: (tango_seven = whiskey_four.quebec_seven)
                          ->  Bitmap Index Scan on lima_four  (cost=0.00..551.54 rows=22798 width=0) (actual time=0.006..0.006 rows=0 loops=2)
                                  Index Cond: (tango_seven = whiskey_four.quebec_seven)
              ->  Index Scan using seven_five on golf_sierra  (cost=0.43..0.51 rows=1 width=16) (never executed)
                      Index Cond: (quebec_seven = romeo_echo_whiskey.mike_alpha)
                      Filter: ((kilo >= 'mike_four'::timestamp without time zone) AND (kilo <= 'papa_uniform'::timestamp without time zone) AND (six_delta = 'romeo_echo_charlie'::uuid) AND (india_four = 'india_lima'::uuid))
Planning time: 0.651 ms
Execution time: 0.211 msSlow

Kód: [Vybrat]

Unique  (cost=84298.41..84298.88 rows=9 width=234) (actual time=41216.659..41216.671 rows=0 loops=1)
  ->  Sort  (cost=84298.41..84298.43 rows=9 width=234) (actual time=41216.657..41216.667 rows=0 loops=1)
          Sort Key: romeo_echo_whiskey.quebec_seven, romeo_echo_whiskey.mike_alpha, romeo_echo_whiskey.quebec_whiskey, romeo_echo_whiskey.three_sierra, romeo_echo_whiskey.foxtrot_yankee, romeo_echo_whiskey.whiskey_romeo, romeo_echo_whiskey.charlie, romeo_echo_whiskey.tango_seven, romeo_echo_whiskey.quebec_bravo, romeo_echo_whiskey.juliet, romeo_echo_whiskey.seven_papa, romeo_echo_whiskey.romeo_hotel, romeo_echo_whiskey.romeo_echo_quebec, romeo_echo_whiskey.victor, romeo_echo_whiskey.bravo_zulu, romeo_echo_whiskey.quebec_echo, romeo_echo_whiskey.yankee, romeo_echo_whiskey.tango_lima, romeo_echo_whiskey.whiskey_yankee, romeo_echo_whiskey.uniform
          Sort Method: quicksort  Memory: 25kB
        ->  Nested Loop  (cost=6.69..84298.27 rows=9 width=234) (actual time=41216.636..41216.644 rows=0 loops=1)
              ->  Nested Loop  (cost=6.40..84136.15 rows=453 width=234) (actual time=0.036..1566.054 rows=160730 loops=1)
                    ->  Index Scan using india_foxtrot on golf_sierra  (cost=0.56..525.98 rows=130 width=16) (actual time=0.017..427.026 rows=53864 loops=1)
                            Index Cond: ((six_delta = 'romeo_echo_charlie'::uuid) AND (india_four = 'india_lima'::uuid) AND (kilo >= 'mike_four'::timestamp without time zone) AND (kilo <= 'papa_uniform'::timestamp without time zone))
                    ->  Bitmap Heap Scan on romeo_echo_whiskey  (cost=5.85..641.50 rows=166 width=234) (actual time=0.008..0.013 rows=3 loops=53864)
                            Recheck Cond: (mike_alpha = golf_sierra.quebec_seven)
                            Heap Blocks: exact=75186
                          ->  Bitmap Index Scan on quebec_uniform  (cost=0.00..5.80 rows=166 width=0) (actual time=0.005..0.005 rows=3 loops=53864)
                                  Index Cond: (mike_alpha = golf_sierra.quebec_seven)
              ->  Index Scan using three_foxtrot on whiskey_four  (cost=0.29..0.36 rows=1 width=16) (actual time=0.245..0.245 rows=0 loops=160730)
                      Index Cond: (quebec_seven = romeo_echo_whiskey.tango_seven)
                      Filter: (((juliet)::text = ANY ('romeo_oscar'::text[])) AND (((((((foxtrot_juliet -> 'papa_november'::text) -> 'quebec_november'::text) ->> 'oscar'::text))::text = 'bravo_november'::text) AND (((((foxtrot_juliet -> 'papa_november'::text) -> 'quebec_november'::text) ->> 'hotel'::text))::text = 'sierra'::text) AND (((foxtrot_juliet ->> 'foxtrot_kilo'::text))::text = 'seven_alpha'::text)) OR ((((((foxtrot_juliet -> 'golf_six'::text) -> 'quebec_november'::text) ->> 'oscar'::text))::text = 'bravo_november'::text) AND (((((foxtrot_juliet -> 'golf_six'::text) -> 'quebec_november'::text) ->> 'hotel'::text))::text = 'sierra'::text) AND (((foxtrot_juliet ->> 'foxtrot_kilo'::text))::text = 'six_seven'::text))))
                      Rows Removed by Filter: 1
Planning time: 0.643 ms
Execution time: 41216.752 ms

[Pavel Stěhule]

Nevies mi poradit ako funguje ta stranka?
Nahral som to tam, vybral som moznost anonymizovat a klikol na Submit. Cakal som, ze tie query plany budu na tej adrese dokial ich nezmazem cez link uvedeny na stranke.

Mne to tak funguje viz https://explain.depesz.com/s/8nwt

[Pavel Stěhule]

Kód: [Vybrat]

    ->  Index Scan using india_foxtrot on golf_sierra  (cost=0.56..525.98 rows=130 width=16) (actual time=0.017..427.026 rows=53864 loops=1)
                            Index Cond: ((six_delta = 'romeo_echo_charlie'::uuid) AND (india_four = 'india_lima'::uuid) AND (kilo >= 'mike_four'::timestamp without time zone) AND (kilo <= 'papa_uniform'::timestamp without time zone))

Tam je problém v brutálně špatném odhadu. Ale těžko říct jak by se dal ten odhad opravit. Za uuid bych na hodinu vyhazoval, a odhad x >= t1 and x <= t2 taky není nejšťastnější. Tady možná přepsat ten dotaz, aby se ten predikát nevyhodnocoval naráz. Možná by stálo přepsat tuu druhou podmínky na výskyt uvnitř časové range.

[Reklama]

[Ondrej Nemecek]

Za uuid bych na hodinu vyhazoval

Jak je toto mysleno - muzete rozvest?

[henrik789]

Je možné přinutit PostgreSQL, aby v určitém dotazu používal vždy stejný plán dotazu. Můžete to provést nastavením konfiguračního parametru plan_cache_mode na hodnotu force . Tím dáte PostgreSQL pokyn, aby pro daný dotaz vždy použil první nalezený plán dotazu, i když to není nejefektivnější plán.

[Pavel Stěhule]

Za uuid bych na hodinu vyhazoval

Jak je toto mysleno - muzete rozvest?

V souvislosti s uuid jsem slyšel o dvou hlavních problémech - jsou u nich horší odhady - až na vyjímky negenerují rostoucí řadu, a vzhledem k tomu, že mají velký rozsah a typicky 2 po sobě vygenerované hodnoty se mohou hodně až brutálně lišit tak se při aktualizaci indexu mění velký počet datových stránek, což vede k intenzivnímu zápisu do transakčního logu. Neplatí to pro všechny typy uuid.

Viz https://www.cybertec-postgresql.com/en/uuid-serial-or-identity-columns-for-postgresql-auto-generated-primary-keys/ nebo https://www.2ndquadrant.com/en/blog/sequential-uuid-generators/

[Pavel Stěhule]

Je možné přinutit PostgreSQL, aby v určitém dotazu používal vždy stejný plán dotazu. Můžete to provést nastavením konfiguračního parametru plan_cache_mode na hodnotu force . Tím dáte PostgreSQL pokyn, aby pro daný dotaz vždy použil první nalezený plán dotazu, i když to není nejefektivnější plán.

To platí jen pro prepared statements nebo pro dotazy z PL/pgSQL - tedy pouze tam, kde se uplatní plan cache. Opět pozor - co se týče plan cache, tak Postgres je postavený úplně jinak než Oracle nebo MSSQL - nemá implicitní plan cache (vyjma dotazů z PL/pgSQL).

[Ondrej Nemecek]

Za uuid bych na hodinu vyhazoval

Jak je toto mysleno - muzete rozvest?

V souvislosti s uuid jsem slyšel o dvou hlavních problémech - jsou u nich horší odhady - až na vyjímky negenerují rostoucí řadu, a vzhledem k tomu, že mají velký rozsah a typicky 2 po sobě vygenerované hodnoty se mohou hodně až brutálně lišit tak se při aktualizaci indexu mění velký počet datových stránek, což vede k intenzivnímu zápisu do transakčního logu. Neplatí to pro všechny typy uuid.

Viz https://www.cybertec-postgresql.com/en/uuid-serial-or-identity-columns-for-postgresql-auto-generated-primary-keys/ nebo https://www.2ndquadrant.com/en/blog/sequential-uuid-generators/

Díky.