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:
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.
Otrzymujemy wówczas prostą tabelę:
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).
Dzięki czemu otrzymujemy tabelę wypełnioną pustymi wartościami w miejscach, dla których nie ma danych.
Ł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.
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ść):
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ć:
Wynik zapytania to oczekiwana tabela kolejnych 10 produktów:
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ę.
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.