Optymalizacja Postgresa z crosstab

Milo Team

Milo Team

Wstęp

Jeden z ciekawszych projektów realizowanych w ostatnim czasie przez naszą sekcję Web polegał na stworzeniu serwisu pozwalającego na analizowanie trendów i prognoz sprzedaży produktów klienta obliczonych na podstawie ogromnej ilości danych historycznych i z uwzględnieniem grupy definiowanych wskaźników.

W artykule przeczytasz:

Tło

Program generujący prognozy został opracowany na jednej z polskich politechnik według wytycznych klienta. Naszym zadaniem było stworzenie jak najbardziej wydajnej integracji między serwisem prezentującym dane, programem obliczającym, a źródłową bazą, do której mieliśmy ograniczony dostęp. 

W większości naszych projektów realizowanych za pomocą Django wykorzystujemy znakomitą, relacyjną bazę danych PostgreSQL, która jest najlepszą (lub wystarczająco dobrą) bazą danych dla większości zastosowań. Także i w tym wypadku zdecydowaliśmy się użyć Postgresa.

Głównym zadaniem naszej bazy było przechowywanie zaimportowanych informacji o produktach z głównej bazy danych klienta (ID, podstawowe informacje oraz szereg danych o sprzedaży, dostawach i stanach magazynowych), synchronizowanych z częstotliwością tygodniową i zwracanie do aplikacji odpowiednio spreparowanych danych. Ponadto baza przechowywała dodatkowe parametry dla każdego z produktów, istotne dla programu obliczającego oraz wynikowe prognozy sprzedaży i potrzeb magazynowych.

Optymalizacja przez design

Klient oczekiwał, że będzie mógł dokonywać analiz w trzech dowolnych agregacjach - tygodniowej, miesięcznej i rocznej. Dane historyczne sięgały dziesięciu lat wstecz, stąd uznaliśmy, że zmuszanie silnika bazy do obliczania poszczególnych agregacji w locie nie ma sensu. Dane historyczne są niezmienne, raz zaimportowane i zagregowane zawsze już takie pozostaną. Stosowne obliczenia wykonaliśmy podczas importu, a dane o sprzedaży umieściliśmy w trzech osobnych tabelach dedykowanych dla poszczególnych agregacji. 

Zauważyliśmy też, że dane sprzedażowe są typu rzadkiego, tzn. dla większości produktów na jedną wartość znaczącą (np. 5 sprzedanych sztuk w danym miesiącu) przypadało kilka lub kilkanaście kolejnych wpisów, gdzie wartość wynosiła zero w kolejnych miesiącach. W efekcie oznaczało to, że na 15 tysięcy wszystkich analizowanych produktów z każdą tygodniową synchronizacją, trzeba by było dodać ponad połowę wpisów z wartością zero. Zgodnie z tą logiką tabela z historią sprzedaży przyrastałaby o 15 tysięcy wpisów co okres, a to daje niemal milion wpisów rocznie dla wszystkich agregacji ((52 tygodnie + 12 miesięcy + 1 rok) * 15 000), z czego większość z nich niosła nikłą wartość poznawczą. Ok, zero to też informacja, ale prościej i ekonomiczniej było w tym wypadku uznać, że brak informacji to informacja o braku sprzedaży.

Problem

Aplikacja prezentująca dane miała postać tabeli (dane wyświetlane przez framework JavaScript, pobierane prez API). Każdy produkt znajdował się w osobnym wierszu, a poszczególne informacje w odpowiednich kolumnach. Dodatkowo, zależnie od wybranego okresu czasu i agregacji, seria kolumn z danymi historycznymi o sprzedaży oraz prognozami. Nic prostszego - dynamicznie dodaliśmy do serializera (Django REST Framework) odpowiednie pola, a on się już zatroszczył o rozszerzenie zapytania SQL generowanego przez DjangoORM.

Niestety efekt nie był zadowalający. Dla każdego rozważanego okresu agregacji zapytanie wyglądało następująco:

Postgres-1.png

Klauzule CASE-WHEN powtórzone dla każdego wyświetlanego okresu, dla każdego wyświetlanego produktu. W efekcie dane z 30 miesięcy (18 historycznych i 12 prognoz, wartości domyślne) dla stu produktów generowały się przez 42(sic!) sekundy.

Oczywiście sprawa byłaby prostsza, gdyby nie nasza wcześniejsza optymalizacja. Wówczas po prostu Postgres po znalezieniu odpowiedniego wpisu odczytywałyby wartość (0 lub większą). Ale wówczas wymogiem by było, żeby wartość dla danego okresu, dla każdego produktu istniał w bazie. To by skutkowało tym, że dla wszystkich okresów historycznych z ostatnich 10 lat, gdzie nie ma danych, musielibyśmy dodać odpowiednie wpisy z wartością zero. To samo musielibyśmy zrobić w momencie wprowadzenia nowego produktu. 

Nie ma mowy. Musiał istnieć na to lepszy sposób.

Rozwiązanie

Najpierw próbowaliśmy optymalizować część zapytania dodawanego przez serializer, lecz bez wymiernych efektów. Wszystko i tak sprowadza się do bardziej lub mniej kosztownych operacji powtarzanych dla każdego okresu i każdego produktu.

Spróbowaliśmy więc podejść do problemu od innej strony. Pobranie danych historycznych i prognoz dla jednego produktu z danego zakresu czasu trwa kilka milisekund.

Postgres-2.png

Otrzymujemy wówczas prostą tabelę:

Postgres-3.png

Można ją w prosty sposób rozszerzyć o brakujące etykiety, definiując je jako tymczasową tabelę, którą rozszerza się przez LEFT OUTER JOIN z danymi. Dodając liczbę porządkową (ord), możemy swobodnie zrezygnować z sortowania po dacie (sortowanie z subquery nie będzie miało znaczenia dla tabeli wynikowej, ale, żeby ograniczyć ilość procesowanych danych, można zostawić filtrowanie daty w klauzuli WHERE).

Postgres-4.png

Dzięki czemu otrzymujemy tabelę wypełnioną pustymi wartościami w miejscach, dla których nie ma danych.

Postgres-5.png

Łatwo zauważyć, że tabela, gdyby ją transponować, byłaby idealna do użycia w klauzuli JOIN głównego zapytania. Wówczas wszystkie wartości kolumny label stałyby się osobnymi kolumnami z jedną tylko wartością (amount). 

Oczywiście Postgres posiada odpowiednią funkcję. Jest to crosstab() w module tablefunc, która zwraca zpivotowaną tabelę wynikową zapytania podanego jako argument.

Postgres-6.png

Funkcja crosstab() przyjmuje jako argument tekst zawierający zapytanie SQL, które po wykonaniu zwraca tabelę do transformacji. Musi też zostać użyta funkcja formatująca wiersz (funkcja ct()), która będzie zawierać kolumnę oznaczającą ID oraz nazwy i typ kolumn, które odpowiadają kolejnym wierszom tabeli wynikowej. Stąd dodatkowa, pierwsza kolumna z wartością 1 (id produktu) dodana do zapytania zwracającego listę okresów i ilości sprzedanych produktów. 

Alternatywnie jako argument funkcji można podać dwa zapytania, z czego drugie powinno zwracać listę kategorii.

W efekcie otrzymujemy następującą tabelę (część danych została obcięta ze względu na czytelność):

Postgres-7.png

Teraz wystarczy wykorzystać id jako wektor łączenia z głównym zapytaniem o listę produktów i dynamiczne kolumny z danymi historycznymi oraz prognozami - gotowe.

Tu pojawił się jednak dodatkowy problem - zapytanie przekazywane do funkcji crosstab() ma postać tekstową, w związku z czym nie jest w stanie adaptować się do każdego wiersza głównego zapytania (zmienne id). 

Na szczęście i na to Postgres ma rozwiązanie - funkcja format(), która pozwala użyć zmiennej w tekście oraz JOIN LATERAL, które pozwala w podrzędnym zapytaniu odwoływać się do wartości zapytania nadrzędnego.

Ostateczna forma zapytania, przyjmuje więc następującą postać:

Postgres-8.png

Wynik zapytania to oczekiwana tabela kolejnych 10 produktów:

Postgres-9.png

SQL i ORM

Powyższe zmiany w wygenerowanym przez ORM zapytaniu były na tyle duże, że pojawiła się myśl, aby zupełnie porzucić ORM w tej części aplikacji i operować na bezpośrednich zapytaniach do bazy. Nie zrobiliśmy tego ze względu na to, że w samym serializerze mieliśmy zakodowaną dość skomplikowaną logikę formatowania danych, na których zależało klientowi, a którą teraz musielibyśmy przepisać w innym miejscu z uwzględnieniem innej formy otrzymywania danych. 

Zdecydowaliśmy się skorzystać z funkcji raw() managera DjangoORM, która pozwala umieścić bezpośrednie zapytanie SQL, a zwraca dany model z atrybutami odpowiadającymi wynikowi zapytania. Taką listę obiektów mogliśmy przekazać do serializera, który następnie aplikował wcześniej zdefiniowaną logikę.

Postgres-10.png

Warto odnotować, że pierwotne zapytanie umieszczane jako ciąg znaków w kodzie Pythona musiało być odrobinę zmodyfikowane, ze względu specjalne traktowanie znaków %s przez interpreter języka. Dodanie drugiego znaku % przed formułę %s informuje interpreter, że dany ciąg nie powinien być traktowany specjalnie. Do sterownika bazy danych przekazane zapytanie będzie zawierało już prawidłową formułę %s.

Podsumowanie

Analizując dalej zapytanie wygenerowane przez ORM zlokalizowaliśmy jeszcze kilka nieefektywnych fragmentów i kilka podwójnych JOIN tej samej tabeli w różnym kontekście. Przy zastosowaniu funkcji raw() mogliśmy je wszystkie zoptymalizować. 

Zastosowane zmiany pozwoliły zredukować czas przeładowania strony z 42 sekund do niecałych dwóch, co jest czasem bardzo dobrym, biorąc pod uwagę ilość danych i jest zarazem czasem akceptowalnym dla użytkownika.

Udostępnij w social mediach

Wybierz sposób realizacji i wspólnie zacznijmy realizować Twój projekt

Wycena projektu
Cofnij