Jak przyspieszyc postgresowa perspektywe?

Witam,

W obecnym projekcie znajduje sie view, ktore jest polaczeniem dwoch zapytan przez union all. Niby wszystko spoko, tylko samo zapytanie zwraca dosc wolno zapytania nawet po 2-4 sekundy na zapytanie. Nie znam sie zabardzo na perspektywach i nie mam pewnosci jak dziala union all w perspektywnie. Czy istnieje inna optymalna metoda, ktora mozna uzyc zamiast union all? Moze usunac perspektywe i wywolywac samo zapytanie SQL? Tylko, ze ono jest dosc dlugie ... chociaz dosc proste w odczytaniu.

UNION ALL sam w sobie nie jest problemem, po prostu łączy wyniki dwóch zapytań, nie jest to UNION, więc nie usuwa duplikatów. Narzut powinien być marginalny.
To, że zapytanie działa wolno może mieć różne przyczyny. Warto wyjść od planu wykonania zapytania i zastanowić się czy wykonanie nie powinno wyglądać inaczej (np. join leci po nested loopach, a powinien być hash join, nieoptymalna kolejność złączenia itp.).

Wywołaj każde z tych zapytań z osobna i zobacz, które zamula, a potem optymalizuj. Jak wspomniał @yarel, union all nie powinien w kwestii wydajności mieć przesadnego narzutu.

Myslalem, ze moze union all bierze wszystkie rekordy i dopiero pozniej filtruje po tym co znajduje sie w WHERE

SELECT * FROM (
  SELECT *
  FROM 
  (...)
  
  UNION ALL
  
  SELECT *
  FROM 
  (...)
)AS result
WHERE company_id = 9999

widok nie ma tu nic do rzeczy (o ile nie jest materialized view) - po prostu zamiast pisać za każdym razem całe zapytanie masz gotowe view - to tak jak napiszesz sobie np. skrypt zamiast pisać 10 komend za każdym razem.

Jak zawsze w takich przypadkach zaczyna się od analizy planu zapytania, machlowaniem warunkami i dodawanie/modyfikacja indeksów.

Taka ciekawostka - przy jednym dość długim zapytaniu zmiana join na left join (tutaj nie miało to znaczenia bo i tak wiersze, które nie były w tej konkretne tabeli były filtrowane) przyśpieszyło zapytanie 10 razy :p

Fakt, znalazlem cos... LEFT JOIN LATERL() zamula jak cholera. Sub-query bylo w nim osadzone, wrzucilem te sub-query do SELECT i dziala 2-3 razy szybciej.

Wpisz przed zapytaniem SQL EXPLAIN ANALYZE i wklej tutaj wynik tego zapytania oraz samo zapytanie, to będzie można więcej powiedzieć, co Ci dokładnie zamula.

Wynik z EXPLAIN ANALYZE

Append  (cost=269.36..9103.59 rows=399 width=389) (actual time=2.831..123.153 rows=394 loops=1)
  ->  Hash Join  (cost=269.36..8966.44 rows=394 width=383) (actual time=2.831..32.725 rows=394 loops=1)
        Hash Cond: (pe.festival_id = ef.festival_id)
        ->  Seq Scan on payment_events pe  (cost=0.00..702.36 rows=394 width=234) (actual time=0.019..3.514 rows=394 loops=1)
              Filter: (company_id = 541451)
              Rows Removed by Filter: 16601
        ->  Hash  (cost=198.05..198.05 rows=5705 width=49) (actual time=2.596..2.596 rows=5782 loops=1)
              Buckets: 8192  Batches: 1  Memory Usage: 544kB
              ->  Seq Scan on external_festivals ef  (cost=0.00..198.05 rows=5705 width=49) (actual time=0.006..1.438 rows=5782 loops=1)
        SubPlan 1
          ->  Aggregate  (cost=15.66..15.67 rows=1 width=32) (actual time=0.047..0.047 rows=1 loops=394)
                ->  Bitmap Heap Scan on payment_events p  (cost=4.31..15.66 rows=1 width=4) (actual time=0.045..0.046 rows=0 loops=394)
                      Recheck Cond: (festival_id = pe.festival_id)
                      Filter: ((date_paid <= pe.date_paid) AND (status = 'PAID'::payment_event_status))
                      Rows Removed by Filter: 1
                      Heap Blocks: exact=394
                      ->  Bitmap Index Scan on payment_events_festival_id_company_id  (cost=0.00..4.31 rows=3 width=0) (actual time=0.044..0.044 rows=1 loops=394)
                            Index Cond: (festival_id = pe.festival_id)
        SubPlan 2
          ->  Aggregate  (cost=4.61..4.62 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=394)
                ->  Hash Join  (cost=3.31..4.60 rows=1 width=11) (actual time=0.018..0.018 rows=0 loops=394)
                      Hash Cond: (a.id = paf.payment_advance_id)
                      ->  Seq Scan on payment_advances a  (cost=0.00..1.20 rows=20 width=8) (actual time=0.005..0.005 rows=1 loops=1)
                      ->  Hash  (cost=3.30..3.30 rows=1 width=11) (actual time=0.012..0.012 rows=0 loops=394)
                            Buckets: 1024  Batches: 1  Memory Usage: 8kB
                            ->  Seq Scan on payment_advance_festivals paf  (cost=0.00..3.30 rows=1 width=11) (actual time=0.012..0.012 rows=0 loops=394)
                                  Filter: (pe.id = payment_event_id)
                                  Rows Removed by Filter: 105
  ->  Nested Loop Left Join  (cost=8.21..131.16 rows=5 width=893) (actual time=90.376..90.380 rows=0 loops=1)
        Join Filter: (pa.type = 'event'::payment_advance_target)
        ->  Nested Loop Left Join  (cost=7.93..95.53 rows=5 width=572) (actual time=90.375..90.379 rows=0 loops=1)
              Join Filter: (pa.type = 'brand'::payment_advance_target)
              ->  Nested Loop Left Join  (cost=7.65..59.97 rows=5 width=558) (actual time=90.374..90.378 rows=0 loops=1)
                    Join Filter: (pa.type = 'company'::payment_advance_target)
                    ->  Hash Join  (cost=7.36..18.37 rows=5 width=541) (actual time=90.373..90.376 rows=0 loops=1)
                          Hash Cond: (pars.id = pa.repayment_source_id)
                          ->  Seq Scan on payment_advances_repayment_sources pars  (cost=0.00..10.70 rows=70 width=520) (actual time=0.987..0.987 rows=1 loops=1)
                          ->  Hash  (cost=7.30..7.30 rows=5 width=29) (actual time=89.369..89.371 rows=0 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                ->  Hash Join  (cost=1.82..7.30 rows=5 width=29) (actual time=89.368..89.370 rows=0 loops=1)
                                      Hash Cond: (pat.payment_advance_id = pa.id)
                                      Join Filter: (((pa.type = 'company'::payment_advance_target) AND (pat.target_id = dc.instance_id)) OR ((pa.type = 'brand'::payment_advance_target) AND (SubPlan 3)) OR ((pa.type = 'event'::payment_advance_target) AND (SubPlan 4)))
                                      Rows Removed by Join Filter: 27
                                      ->  Nested Loop  (cost=0.29..5.71 rows=20 width=12) (actual time=2.931..2.949 rows=32 loops=1)
                                            ->  Index Only Scan using data_company_instance_id_instance_removed_index on data_company dc  (cost=0.29..4.31 rows=1 width=4) (actual time=1.930..1.931 rows=1 loops=1)
                                                  Index Cond: ((instance_id = 541451) AND (instance_removed IS NULL))
                                                  Heap Fetches: 1
                                            ->  Seq Scan on payment_advance_targets pat  (cost=0.00..1.20 rows=20 width=8) (actual time=0.997..1.004 rows=32 loops=1)
                                      ->  Hash  (cost=1.40..1.40 rows=11 width=21) (actual time=0.032..0.033 rows=27 loops=1)
                                            Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                            ->  Seq Scan on payment_advances pa  (cost=0.00..1.40 rows=11 width=21) (actual time=0.014..0.023 rows=27 loops=1)
                                                  Filter: ((removed IS NULL) AND (status = 'paid'::payment_advance_status) AND ((type = 'company'::payment_advance_target) OR (type = 'brand'::payment_advance_target) OR (type = 'event'::payment_advance_target)))
                                                  Rows Removed by Filter: 5
                                      SubPlan 3
                                        ->  Index Scan using ix_eg_brand_company_id on eg_brand  (cost=0.28..9.19 rows=2 width=4) (actual time=1.100..1.103 rows=3 loops=2)
                                              Index Cond: (company_id = dc.instance_id)
                                      SubPlan 4
                                        ->  Nested Loop  (cost=0.71..226.29 rows=4 width=4) (actual time=1.042..5.972 rows=391 loops=14)
                                              ->  Index Scan using ix_data_event_combined on data_event de  (cost=0.42..61.92 rows=27 width=4) (actual time=0.083..0.425 rows=502 loops=14)
                                                    Index Cond: ((instance_removed IS NULL) AND ((owner)::text = ((dc.instance_id)::character varying)::text))
                                              ->  Index Only Scan using external_festivals_events_event_id_festival_id on external_festivals_events f  (cost=0.28..6.08 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=7028)
                                                    Index Cond: (event_id = de.instance_id)
                                                    Heap Fetches: 5475
                    ->  Index Scan using data_company_instance_id_instance_removed_index on data_company dc_1  (cost=0.29..8.31 rows=1 width=21) (never executed)
                          Index Cond: ((instance_id = pat.target_id) AND (instance_removed IS NULL))
              ->  Index Scan using ix_eg_brand_id on eg_brand b  (cost=0.28..7.10 rows=1 width=18) (never executed)
                    Index Cond: (id = pat.target_id)
        ->  Index Scan using external_festivals_pkey on external_festivals ef_1  (cost=0.28..7.10 rows=1 width=49) (never executed)
              Index Cond: (festival_id = pat.target_id)
Planning Time: 2.276 ms
Execution Time: 124.296 ms

Pisałeś, że to trwa 2-4 sekundy, a tutaj widać, że czas egzekucji to 124 ms.

Co prawda nie wkleiłeś samego zapytania jak prosiłem, ale już po planie widać, że jest miejsce na różne optymalizacje.

Tak czy inaczej jeżeli faktycznie to trwa 2-4 sek. to opóźnienie jest gdzie indziej, nie tutaj.

Execution Time: 124.296 ms to chyba execution EXPLAIN
Planning Time: 2.276 ms a to jest co mnie interesuje? Czy sie myle?

usun post

Nie, Execution time to jest cały potrzebny czas na wyciągnięcie wszystkich danych, 124 ms = 124 tysięcznych sek., jakieś 1/8 sekundy.

Planning time masz jeszcze krótszy, czyli 2 tysięczne sekundy; to jest czas który potrzebuje baza danych na zaplanowanie zapytania.

To jest znowu EXPLAIN bez WHERE powyzej bylo condition na company_id. Na co mam zwracac uwage oraz co zmienic?

Append  (cost=269.36..576610.87 rows=43342 width=693) (actual time=2.637..3192.797 rows=17022 loops=1)
  ->  Hash Join  (cost=269.36..345655.81 rows=16989 width=383) (actual time=2.636..666.221 rows=16995 loops=1)
        Hash Cond: (pe.festival_id = ef.festival_id)
        ->  Seq Scan on payment_events pe  (cost=0.00..659.89 rows=16989 width=234) (actual time=0.007..6.517 rows=16995 loops=1)
        ->  Hash  (cost=198.05..198.05 rows=5705 width=49) (actual time=2.531..2.532 rows=5782 loops=1)
              Buckets: 8192  Batches: 1  Memory Usage: 544kB
              ->  Seq Scan on external_festivals ef  (cost=0.00..198.05 rows=5705 width=49) (actual time=0.006..1.427 rows=5782 loops=1)
        SubPlan 1
          ->  Aggregate  (cost=15.66..15.67 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=16995)
                ->  Bitmap Heap Scan on payment_events p  (cost=4.31..15.66 rows=1 width=4) (actual time=0.015..0.017 rows=4 loops=16995)
                      Recheck Cond: (festival_id = pe.festival_id)
                      Filter: ((date_paid <= pe.date_paid) AND (status = 'PAID'::payment_event_status))
                      Rows Removed by Filter: 17
                      Heap Blocks: exact=50018
                      ->  Bitmap Index Scan on payment_events_festival_id_company_id  (cost=0.00..4.31 rows=3 width=0) (actual time=0.012..0.012 rows=23 loops=16995)
                            Index Cond: (festival_id = pe.festival_id)
        SubPlan 2
          ->  Aggregate  (cost=4.61..4.62 rows=1 width=32) (actual time=0.018..0.018 rows=1 loops=16995)
                ->  Hash Join  (cost=3.31..4.60 rows=1 width=11) (actual time=0.018..0.018 rows=0 loops=16995)
                      Hash Cond: (a.id = paf.payment_advance_id)
                      ->  Seq Scan on payment_advances a  (cost=0.00..1.20 rows=20 width=8) (actual time=0.002..0.005 rows=32 loops=106)
                      ->  Hash  (cost=3.30..3.30 rows=1 width=11) (actual time=0.012..0.012 rows=0 loops=16995)
                            Buckets: 1024  Batches: 1  Memory Usage: 9kB
                            ->  Seq Scan on payment_advance_festivals paf  (cost=0.00..3.30 rows=1 width=11) (actual time=0.012..0.012 rows=0 loops=16995)
                                  Filter: (pe.id = payment_event_id)
                                  Rows Removed by Filter: 105
  ->  Hash Left Join  (cost=2719.38..230304.93 rows=26353 width=893) (actual time=833.795..2524.789 rows=27 loops=1)
        Hash Cond: (pat.target_id = ef_1.festival_id)
        Join Filter: (pa.type = 'event'::payment_advance_target)
        ->  Hash Left Join  (cost=2450.02..229636.94 rows=26353 width=572) (actual time=831.256..2522.196 rows=27 loops=1)
              Hash Cond: (pat.target_id = b.id)
              Join Filter: (pa.type = 'brand'::payment_advance_target)
              ->  Hash Left Join  (cost=2179.22..229296.96 rows=26353 width=558) (actual time=827.179..2518.090 rows=27 loops=1)
                    Hash Cond: (pat.target_id = dc_1.instance_id)
                    Join Filter: (pa.type = 'company'::payment_advance_target)
                    ->  Hash Join  (cost=13.40..224742.91 rows=26353 width=541) (actual time=820.747..2511.630 rows=27 loops=1)
                          Hash Cond: (pa.repayment_source_id = pars.id)
                          ->  Hash Join  (cost=1.82..224657.98 rows=26353 width=29) (actual time=820.705..2511.565 rows=27 loops=1)
                                Hash Cond: (pat.payment_advance_id = pa.id)
                                Join Filter: (((pa.type = 'company'::payment_advance_target) AND (pat.target_id = dc.instance_id)) OR ((pa.type = 'brand'::payment_advance_target) AND (SubPlan 3)) OR ((pa.type = 'event'::payment_advance_target) AND (SubPlan 4)))
                                Rows Removed by Join Filter: 133191
                                ->  Nested Loop  (cost=0.29..2285.72 rows=98200 width=12) (actual time=0.045..44.697 rows=157888 loops=1)
                                      ->  Index Only Scan using data_company_instance_id_instance_removed_index on data_company dc  (cost=0.29..1056.97 rows=4910 width=4) (actual time=0.033..12.999 rows=4934 loops=1)
                                            Index Cond: (instance_removed IS NULL)
                                            Heap Fetches: 694
                                      ->  Materialize  (cost=0.00..1.30 rows=20 width=8) (actual time=0.000..0.002 rows=32 loops=4934)
                                            ->  Seq Scan on payment_advance_targets pat  (cost=0.00..1.20 rows=20 width=8) (actual time=0.006..0.012 rows=32 loops=1)
                                ->  Hash  (cost=1.40..1.40 rows=11 width=21) (actual time=0.024..0.025 rows=27 loops=1)
                                      Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                      ->  Seq Scan on payment_advances pa  (cost=0.00..1.40 rows=11 width=21) (actual time=0.008..0.017 rows=27 loops=1)
                                            Filter: ((removed IS NULL) AND (status = 'paid'::payment_advance_status) AND ((type = 'company'::payment_advance_target) OR (type = 'brand'::payment_advance_target) OR (type = 'event'::payment_advance_target)))
                                            Rows Removed by Filter: 5
                                SubPlan 3
                                  ->  Index Scan using ix_eg_brand_company_id on eg_brand  (cost=0.28..9.19 rows=2 width=4) (actual time=0.006..0.012 rows=1 loops=9868)
                                        Index Cond: (company_id = dc.instance_id)
                                SubPlan 4
                                  ->  Nested Loop  (cost=0.71..226.29 rows=4 width=4) (actual time=0.029..0.033 rows=1 loops=69076)
                                        ->  Index Scan using ix_data_event_combined on data_event de  (cost=0.42..61.92 rows=27 width=4) (actual time=0.005..0.011 rows=11 loops=69076)
                                              Index Cond: ((instance_removed IS NULL) AND ((owner)::text = ((dc.instance_id)::character varying)::text))
                                        ->  Index Only Scan using external_festivals_events_event_id_festival_id on external_festivals_events f  (cost=0.28..6.08 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=781359)
                                              Index Cond: (event_id = de.instance_id)
                                              Heap Fetches: 78431
                          ->  Hash  (cost=10.70..10.70 rows=70 width=520) (actual time=0.014..0.014 rows=3 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                ->  Seq Scan on payment_advances_repayment_sources pars  (cost=0.00..10.70 rows=70 width=520) (actual time=0.009..0.009 rows=3 loops=1)
                    ->  Hash  (cost=2104.44..2104.44 rows=4910 width=21) (actual time=6.401..6.401 rows=4934 loops=1)
                          Buckets: 8192  Batches: 1  Memory Usage: 359kB
                          ->  Seq Scan on data_company dc_1  (cost=0.00..2104.44 rows=4910 width=21) (actual time=0.012..5.436 rows=4934 loops=1)
                                Filter: (instance_removed IS NULL)
                                Rows Removed by Filter: 17474
              ->  Hash  (cost=172.58..172.58 rows=7858 width=18) (actual time=4.028..4.029 rows=7871 loops=1)
                    Buckets: 8192  Batches: 1  Memory Usage: 462kB
                    ->  Seq Scan on eg_brand b  (cost=0.00..172.58 rows=7858 width=18) (actual time=0.005..2.574 rows=7871 loops=1)
        ->  Hash  (cost=198.05..198.05 rows=5705 width=49) (actual time=2.495..2.495 rows=5782 loops=1)
              Buckets: 8192  Batches: 1  Memory Usage: 543kB
              ->  Seq Scan on external_festivals ef_1  (cost=0.00..198.05 rows=5705 width=49) (actual time=0.008..1.394 rows=5782 loops=1)
Planning Time: 2.306 ms
Execution Time: 3220.333 ms

Sorry ale to wymagałoby poznania całego schematu tej bazy danych, odpowiedniej normalizacji/denormalizacji danych, odpowiedniego ustawienia indeksów, itd. Za dużo pracy jak na pomoc na forum.

Wiadomo, nie ma problemu. Jakies podpowiedzi?

Pisząc abstrakcyjnie: naucz się czytać plan zapytania i wyciągać z tego wnioski.

Np. tam gdzie masz "Index Scan" jest generalnie dobrze, bo wtedy ta cząstka zapytania korzysta z indeksu, czyli najszybszej formy wyciągania danych z bazy.

A np. Seq scan to jest skanowanie sekwencyjne, czyli musi przejść przez cały zakres danych / całą tabelę, etc.

Poszukaj w sieci informacji o tych rzeczach, a potem odpowiednio optymalnie dobierz indeksy i strukturę tabel.

Przy czym walenie zbyt dużej ilości indeksów to też nie jest dobre rozwiązanie - każdy indeks to dodatkowe dane w bazie, oraz konieczność aktualizacji przy zmianach w tabeli.

zapytam inaczej - ile rekordów dostajesz jeśli uruchomisz zapytanie

bez WHERE powyzej bylo condition na company_id

? Bo jak ilość idzie w miliony to raczej nie ma co poprawiać w zapytaniu a iść w kierunku ograniczenia ilość zwracanych danych

A co chcesz optymalizować:
a) wersję bez WHERE (czyli po wszystkich COMPANY)
b) wersję z WHERE dla wybranego company_id

?

yarel napisał(a):

A co chcesz optymalizować:
a) wersję bez WHERE (czyli po wszystkich COMPANY)
b) wersję z WHERE dla wybranego company_id

?

Wersja z WHERE. Nie uzywam w ogole VIEW bez jakiegokolwiek condition. Zawsze jest condition z company_id.

No ale przecież przy użyciu warunku na firmę czas wynosił 124ms. To dla Ciebie za długo?