PostgreSQL -> optymalizacja zapytania

0

Hej,
Próbowałem przeanalizować plan zapytań i indeksy jakie mamy pozakładane, ale za bardzo nie wiem jak się do tego zabrać. Najczęściej pracuję na MSSQL, a tutaj mi się postgresql trafił.
Mamy tabelki w postgresql, w których mamy miliony wierszy, dosyć szybki przyrost dzienny (dziesiątki tysięcy urządzeń generuje ruch/operacje).
Szukam rozwiązania jak można by przyspieszyć zapytanie:

       client_name                                                                                 AS client_name,
       brand_name                                                                                  AS brand_name,
       model_name                                                                                  AS model_name,
       pos_name                                                                                    AS pos_name,
       material_name                                                                               AS material_name,
       foil_size_name                                                                              AS foil_size_name,
       outline_type_name                                                                           AS outline_type_name,
       cuts.plotter_name                                                                           AS plotter_name,
       devices.cu_id                                                                               AS cu_id,
       client_parent.name                                                                          AS client_parent_name,
       SUM(quantity)                                                              AS total_cuts,
       SUM(CASE WHEN client_status IN (0, 2, 4) OR client_status IS NULL THEN quantity ELSE 0 END) AS success_cuts,
       SUM(CASE WHEN client_status = 1 THEN quantity ELSE 0 END)                                   AS fail_cuts,
       SUM(CASE WHEN client_status = 3 THEN quantity ELSE 0 END)                                   AS fail_installs
from "pg68126_cutterdev".public."cuts"
         left join public."clients" on "cuts"."client_id" = "clients"."id"
         left join public."clients" as "client_parent" on "clients"."parent_id" = "client_parent"."id"
         left join public."devices" on "cuts"."device_id" = "devices"."id"
where "cuts"."created" between '2022-01-01 00:00:00' and '2022-05-01 23:59:59'
  and "quantity" = 1
group by date, client_name, brand_name, model_name, pos_name, material_name, foil_size_name, outline_type_name,
         cuts.plotter_name, cu_id, client_parent_name
order by MIN(cuts.created);

Plan zapytania dla powyższego:

Sort  (cost=490087.84..492067.63 rows=791918 width=293) (actual time=1614.411..1699.115 rows=661124 loops=1)
  Sort Key: (min(cuts.created))
  Sort Method: external merge  Disk: 126696kB
  ->  Finalize GroupAggregate  (cost=139788.09..273292.21 rows=791918 width=293) (actual time=654.045..1364.967 rows=661124 loops=1)
"        Group Key: (date(cuts.created)), cuts.client_name, cuts.brand_name, cuts.model_name, cuts.pos_name, cuts.material_name, cuts.foil_size_name, cuts.outline_type_name, cuts.plotter_name, devices.cu_id, client_parent.name"
        ->  Gather Merge  (cost=139788.09..236995.95 rows=659932 width=293) (actual time=654.038..1063.162 rows=693280 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              ->  Partial GroupAggregate  (cost=138788.07..159823.40 rows=329966 width=293) (actual time=631.108..784.251 rows=231093 loops=3)
"                    Group Key: (date(cuts.created)), cuts.client_name, cuts.brand_name, cuts.model_name, cuts.pos_name, cuts.material_name, cuts.foil_size_name, cuts.outline_type_name, cuts.plotter_name, devices.cu_id, client_parent.name"
                    ->  Sort  (cost=138788.07..139612.98 rows=329966 width=267) (actual time=631.094..657.615 rows=262495 loops=3)
"                          Sort Key: (date(cuts.created)), cuts.client_name, cuts.brand_name, cuts.model_name, cuts.pos_name, cuts.material_name, cuts.foil_size_name, cuts.outline_type_name, cuts.plotter_name, devices.cu_id, client_parent.name"
                          Sort Method: external merge  Disk: 46472kB
                          Worker 0:  Sort Method: external merge  Disk: 42856kB
                          Worker 1:  Sort Method: external merge  Disk: 43168kB
                          ->  Hash Left Join  (cost=1084.93..54889.95 rows=329966 width=267) (actual time=3.190..158.377 rows=262495 loops=3)
                                Hash Cond: (cuts.device_id = devices.id)
                                ->  Hash Left Join  (cost=611.94..52725.61 rows=329966 width=250) (actual time=1.392..122.963 rows=262495 loops=3)
                                      Hash Cond: (clients.parent_id = client_parent.id)
                                      ->  Hash Left Join  (cost=306.19..51552.58 rows=329966 width=230) (actual time=0.940..98.505 rows=262495 loops=3)
                                            Hash Cond: (cuts.client_id = clients.id)
                                            ->  Parallel Index Scan using key_created on cuts  (cost=0.43..50379.54 rows=329966 width=230) (actual time=0.020..56.162 rows=262495 loops=3)
                                                  Index Cond: ((created >= '2022-01-01 00:00:00'::timestamp without time zone) AND (created <= '2022-05-01 23:59:59'::timestamp without time zone))
                                                  Filter: (quantity = 1)
                                                  Rows Removed by Filter: 225
                                            ->  Hash  (cost=264.78..264.78 rows=3278 width=8) (actual time=0.896..0.897 rows=3279 loops=3)
                                                  Buckets: 4096  Batches: 1  Memory Usage: 161kB
                                                  ->  Seq Scan on clients  (cost=0.00..264.78 rows=3278 width=8) (actual time=0.005..0.707 rows=3279 loops=3)
                                      ->  Hash  (cost=264.78..264.78 rows=3278 width=28) (actual time=0.444..0.444 rows=3279 loops=3)
                                            Buckets: 4096  Batches: 1  Memory Usage: 230kB
                                            ->  Seq Scan on clients client_parent  (cost=0.00..264.78 rows=3278 width=28) (actual time=0.001..0.203 rows=3279 loops=3)
                                ->  Hash  (cost=306.33..306.33 rows=13333 width=21) (actual time=1.773..1.773 rows=13335 loops=3)
                                      Buckets: 16384  Batches: 1  Memory Usage: 847kB
                                      ->  Seq Scan on devices  (cost=0.00..306.33 rows=13333 width=21) (actual time=0.004..0.911 rows=13335 loops=3)
Planning Time: 0.281 ms
Execution Time: 1758.698 ms

Zapytanie służy do wygenerowania raportu dla klienta, na żądanie w przeglądarce. Może wybrać dowolny zakres dat. Dane prezentowane w formie wykresu na stronie (php+react).
Nie pobieramy jednocześnie całego query, ponieważ RAM jest skończony, a liczba klientów, która mogłaby wywołać raport spora. Wywołujemy całe zapytanie w pętli w interwałach dniowych, ale i tak. Trwa to długo + rendering raportu po stronie przeglądarki też swoje trwa (pobranie i przerobienie jsona troszkę trwa i waży).

Może ktoś mógłby pokierować jak to sensownie zoptymalizować?
Myślałem o Materialized View, ale wtedy jesteśmy ograniczeni do przetwarzania danych widoku w nocy. Pytanie czy to coś da? Jakoś przekonany nie jestem.

3

Cześć,

jeśli serwer na to pozwala możecie podnieść parametr work_mem, żeby nie używało dysku do sortowania. To na początek.
(linijki:
Sort Method: external merge Disk: 126696kB
Worker 0: Sort Method: external merge Disk: 42856kB
Worker 1: Sort Method: external merge Disk: 43168kB
)

Jak się nie poprawi wystarczająco zapisywałbym dane do tabeli, dopisując w nocy tylko jeden dzień.... Dane za poprzednie dni przecież się nie zmienią....
Odczyt wtedy byłby z jednej tabeli więc szybciej się już nie da....

2
  1. Może trzymać dedykowany widok zmaterializowany dla hierarchii klienta? Wtedy 2 joiny z "clients" zamieniasz na 1 z "client_hierarchy" (mniejszy obiekt, w którym trzymasz: client_id, parent_cliend_id, parent_name, ... )
   left join public."clients" on "cuts"."client_id" = "clients"."id"
   left join public."clients" as "client_parent" on "clients"."parent_id" = "client_parent"."id"
  1. Nie wiem jak wygląda "typowe" zapytanie po zakresie dat. Ostatni tydzień, dzień, rok, miesiąc, ale może warto rozważyć partycjonowanie "cuts" po "created" ?
0
areklipno napisał(a):

Cześć,

jeśli serwer na to pozwala możecie podnieść parametr work_mem, żeby nie używało dysku do sortowania. To na początek.
(linijki:
Sort Method: external merge Disk: 126696kB
Worker 0: Sort Method: external merge Disk: 42856kB
Worker 1: Sort Method: external merge Disk: 43168kB
)

Jak się nie poprawi wystarczająco zapisywałbym dane do tabeli, dopisując w nocy tylko jeden dzień.... Dane za poprzednie dni przecież się nie zmienią....
Odczyt wtedy byłby z jednej tabeli więc szybciej się już nie da....

Hej,
work_mem ustalaliśmy troszkę taką metodą chałupniczą :) Było ustawione na 16MB, zwiększyłem na ~100MB. Fakt plan zapytania się poprawił, ale chyba będzie trzeba pójść w widok zmaterializowany / tabelę, która będzie miała w sobie wszystkie dane.

Obecnie plan zapytania wygląda tak:

Sort  (cost=191567.04..193546.83 rows=791918 width=293) (actual time=1299.077..1368.633 rows=661124 loops=1)
  Sort Key: (min(cuts.created))
  Sort Method: quicksort  Memory: 201864kB
  ->  HashAggregate  (cost=104079.93..113978.91 rows=791918 width=293) (actual time=892.195..1061.697 rows=661124 loops=1)
"        Group Key: date(cuts.created), cuts.client_name, cuts.brand_name, cuts.model_name, cuts.pos_name, cuts.material_name, cuts.foil_size_name, cuts.outline_type_name, cuts.plotter_name, devices.cu_id, client_parent.name"
        Batches: 1  Memory Usage: 294953kB
        ->  Hash Left Join  (cost=1084.93..65473.93 rows=791918 width=267) (actual time=2.792..447.692 rows=787484 loops=1)
              Hash Cond: (cuts.device_id = devices.id)
              ->  Hash Left Join  (cost=611.94..60941.70 rows=791918 width=250) (actual time=1.145..346.293 rows=787484 loops=1)
                    Hash Cond: (clients.parent_id = client_parent.id)
                    ->  Hash Left Join  (cost=306.19..58554.48 rows=791918 width=230) (actual time=0.730..269.823 rows=787484 loops=1)
                          Hash Cond: (cuts.client_id = clients.id)
                          ->  Index Scan using key_created on cuts  (cost=0.43..56167.25 rows=791918 width=230) (actual time=0.020..143.500 rows=787484 loops=1)
                                Index Cond: ((created >= '2022-01-01 00:00:00'::timestamp without time zone) AND (created <= '2022-05-01 23:59:59'::timestamp without time zone))
                                Filter: (quantity = 1)
                                Rows Removed by Filter: 674
                          ->  Hash  (cost=264.78..264.78 rows=3278 width=8) (actual time=0.703..0.704 rows=3279 loops=1)
                                Buckets: 4096  Batches: 1  Memory Usage: 161kB
                                ->  Seq Scan on clients  (cost=0.00..264.78 rows=3278 width=8) (actual time=0.006..0.543 rows=3279 loops=1)
                    ->  Hash  (cost=264.78..264.78 rows=3278 width=28) (actual time=0.410..0.411 rows=3279 loops=1)
                          Buckets: 4096  Batches: 1  Memory Usage: 230kB
                          ->  Seq Scan on clients client_parent  (cost=0.00..264.78 rows=3278 width=28) (actual time=0.002..0.200 rows=3279 loops=1)
              ->  Hash  (cost=306.33..306.33 rows=13333 width=21) (actual time=1.616..1.617 rows=13335 loops=1)
                    Buckets: 16384  Batches: 1  Memory Usage: 847kB
                    ->  Seq Scan on devices  (cost=0.00..306.33 rows=13333 width=21) (actual time=0.003..0.738 rows=13335 loops=1)
Planning Time: 0.275 ms
Execution Time: 1457.276 ms
yarel napisał(a):
  1. Może trzymać dedykowany widok zmaterializowany dla hierarchii klienta? Wtedy 2 joiny z "clients" zamieniasz na 1 z "client_hierarchy" (mniejszy obiekt, w którym trzymasz: client_id, parent_cliend_id, parent_name, ... )
   left join public."clients" on "cuts"."client_id" = "clients"."id"
   left join public."clients" as "client_parent" on "clients"."parent_id" = "client_parent"."id"
  1. Nie wiem jak wygląda "typowe" zapytanie po zakresie dat. Ostatni tydzień, dzień, rok, miesiąc, ale może warto rozważyć partycjonowanie "cuts" po "created" ?
  1. O tym nie myślałem, bardziej zastanawiałem się nad wykorzystaniem CTE do ogarnięcia tej hierarchi, ale jakoś nie byłem przekonany do tego.
  2. No i tu jest problem, nie ma typowego schematu. Każdy klient wybiera różny dziwny okres. Ostatni miesiąc, ostatni tydzień, część cofa się do całego roku lub całego okresu. Z partycjonowaniem chodzi Ci o tworzenie tabel per, np. Kwartał Rok?
0
Lilpri napisał(a):

work_mem ustalaliśmy troszkę taką metodą chałupniczą :) Było ustawione na 16MB, zwiększyłem na ~100MB. Fakt plan zapytania się poprawił, ale chyba będzie trzeba pójść w widok zmaterializowany / tabelę, która będzie miała w sobie wszystkie dane.

To sprawdźcie czy pamięci starczy - tzn. max liczba połączeń x 100MB odpowiada ilości dostępnego ramu... Bo w najgorszym wypadku może być zonk.....

Wpadło mi jeszcze do głowy - spróbuj policzyć sumy bez joinów (zamiast pól z innych tabel zostawić te, które potrzebne są do łączenia). Dopiero do wyniku dołóż joiny... - może będzie coś szybciej. Z tym co pisał @yarel w pkt1 zawartym w CTE też powinno być szybciej. Może uda się zbliżyć do sekundy, ale więcej bym się nie spodziewał...

0

No ale ta pamięć to przecież nie jest cały czas używana? Zakładam, że proces ją zwalnia gdy już jej nie potrzebuje.
Generalnie to operowanie w obszarach pamięci typu 16-100 MB kojarzy mi się z rokiem 2005. Dowal tej pamięci ile się da, sprawdź czy jest lepiej - jak nie to zmniejsz z powrotem i szukaj innych metod.

0

W serwerze mamy 32GB RAM.
Połączenia oraz operacje są realizowane przez API, dla większości urządzeń jest to prosty select pojedynczej danej / dodanie jakiegoś klienta, urządzenia, operacji maszyny do bazy.
Niewielu klientów generuje raporty (może 10% z wszystkich klientów w systemie), ale chcemy to przyspieszyć w najlepszy możliwy sposób.
@areklipno:
czyli chodzi Ci o coś na styl tego?

select x1, x2, x3 from (pierwotny select bez joinów) x
join client 1, client 2...

Sama idea spoko, i wtedy grupować w podzapytaniu po ID clienta. Sprawdzę sobie później.

@robertos7778:
Sam PG ma przydzielone 25% pamięci (8GB), to o czym rozmawiamy to "buffor" dla workerów, który jest zależy od całkowitej pamięci serwera i ilości możliwych połączeń.

2
  1. O tym nie myślałem, bardziej zastanawiałem się nad wykorzystaniem CTE do ogarnięcia tej hierarchi, ale jakoś nie byłem przekonany do tego.>

Nie wiem czy CTE jakoś znacznie zmieni sytuację (nadal silnik potrzebuje wykonać pracę i taką hierarchię zbudować).

  1. Każdy klient wybiera różny dziwny okres. Ostatni miesiąc, ostatni tydzień, część cofa się do całego roku lub całego okresu. Z partycjonowaniem chodzi Ci o tworzenie tabel per, np. Kwartał Rok?

Chodzi mi o mechanizm partycjonowania wspierany przez silnik -> https://www.postgresql.org/docs/current/ddl-partitioning.htm

Silnik jest w stanie wykonać tzw. "partition pruning", czyli pominąć obiekty, które nie są potrzebne do realizacji zapytania. Jeśli na półce masz 20+ tomów encyklopedii, to jak szukasz hasła na literkę T, to idziesz prosto do tomu "T" (partycji), nie musisz szukać hasła sekwencyjnie, jadąc po wszystkich tomach od początku.

from "pg68126_cutterdev".public."cuts"
         left join public."clients" on "cuts"."client_id" = "clients"."id"
         left join public."clients" as "client_parent" on "clients"."parent_id" = "client_parent"."id"
         left join public."devices" on "cuts"."device_id" = "devices"."id"
where "cuts"."created" between '2022-01-01 00:00:00' and '2022-05-01 23:59:59'
  and "quantity" = 1
group by date, client_name, brand_name, model_name, pos_name, material_name, foil_size_name, outline_type_name,
         cuts.plotter_name, cu_id, client_parent_name
order by MIN(cuts.created);

Piszesz, że zapytanie wykonywane jest przez klienta, ale w tym zapytaniu nie ma odniesienia do konkretnego klienta, więc nie mówisz całej prawdy ;-)

Czy w DEVICE masz odniesienie do klienta? Jeśli tak, to można by CUTS/CLIENTS/DEVICES partycjonować po kliencie. Wówczas zapytanie per konkretny klient operowałoby na konkretnych partycjach.
np.
-> bez partycjonowania: CUTS - 100gb , DEVICES - 10b, CLIENTS - 1gb,
-> z partycjonowaniem (np. 100 partycji typu hasz i przy równomiernym rozkładzie danych) -> CUTS - 1gb, DEVICES - 0.1GB, CLIENTS - 0.01 GB

Zapytanie po kliencie -> partition pruning wkracza do gry i silnik operuje na 3 małych partycjach , a nie 3 dużych tabelach.

1
Lilpri napisał(a):

@areklipno:
czyli chodzi Ci o coś na styl tego?

select x1, x2, x3 from (pierwotny select bez joinów) x
join client 1, client 2...

Sama idea spoko, i wtedy grupować w podzapytaniu po ID clienta. Sprawdzę sobie później.

Tak o coś takiego mi chodziło.

@yarel: dobrze piszesz o partycjonowaniu, ale wg mnie nie będzie to miało w tym przypadku większego wpływu na wykonanie zapytania. Samo pobranie danych z głównej tabeli to

 Index Scan using key_created on cuts  (cost=0.43..56167.25 rows=791918 width=230) (actual time=0.020..143.500 rows=787484 loops=1)

Problem zaczyna się z tym, że trzeba pojoinować a potem jeszcze group by zrobić... Zwracanych wierszy w przykładzie jest ok 787 tys.
Żeby wykonać każdy z joinów postgres robi hashe i potem je "skleja" i tutaj sporo czasu ucieka.
Może rozwiązaniem byłoby to co pisałeś z klientami - jeden join mniej przy głównej tabeli (sklejenie 2 małych tabel klientów potrwa zapewne krócej).

Przy devices może pomógłby indeks na tej tabeli zawierający pola, które są potrzebne (id, cu_id) - wtedy może byłoby bez tworzenia hasha - leciałoby tylko po indeksie, ale ciężko w ciemno powiedzieć.

Group by ciężko zoptymalizować - jest dużo pól i znalezienie unikatów kombinacji 10 pól na ~800k wierszy też trochę trwa.

Pytanie czy order by jest potrzebne - niby nie dużo, ale też by przyspieszyło.

Mimo wysiłków chyba i tak skończy się na dodatkowej tabeli/widoku...

Zarejestruj się i dołącz do największej społeczności programistów w Polsce.

Otrzymaj wsparcie, dziel się wiedzą i rozwijaj swoje umiejętności z najlepszymi.

Utwórz konto