Analiza typowych błędów przy tworzeniu procedur analitycznych

Dzisiaj, żeby zakończyć tę epicką podróż po PL/pgSQL, ustalmy jedno: błędy w procedurach analitycznych są nieuniknione. Dlaczego? Bo w analizie pracujemy z dużą ilością danych, skomplikowanymi obliczeniami i czasem naprawdę podchwytliwymi warunkami. Im bardziej złożone zapytanie lub procedura, tym bardziej przypomina labirynt, gdzie kilka złych kroków może prowadzić do błędnych wyników.

Na szczęście większość błędów jest typowa i można je przewidzieć (i zapobiec im). Przejdźmy przez nie po kolei.

1. Brak indeksów na kluczowych polach

Indeksy — to jak nawigacja w świecie baz danych. Jeśli ich nie ma, baza musi przechodzić pieszo przez wszystkie wiersze tabeli. W małych tabelach da się to przeżyć, ale jak tylko danych robi się miliony, twoje zapytania zaczynają działać wolniej niż Windows XP na Pentium III.

Załóżmy, że masz tabelę zamówień i chcesz policzyć sprzedaż za ostatni miesiąc:

SELECT SUM(order_total)
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 month';

Jeśli na polu order_date nie ma indeksu, PostgreSQL robi pełny skan tabeli (Seq Scan). A to prawie zawsze jest wolne.

Rozwiązanie: używaj indeksów! Wystarczy taka komenda:

CREATE INDEX idx_order_date ON orders (order_date);

Teraz PostgreSQL będzie mógł szukać po polu order_date dużo szybciej.

Używanie nieefektywnych zapytań

Niektóre zapytania wyglądają ładnie, ale działają jak betonowa cegła zamiast klucza. Na przykład używanie podzapytań, które można zastąpić joinami (JOIN), albo zbędne filtrowanie.

Zamiast tego:

SELECT product_id, SUM(order_total)
FROM orders
WHERE product_id IN (SELECT id FROM products WHERE category = 'electronics')
GROUP BY product_id;

Lepiej zrobić tak:

SELECT o.product_id, SUM(o.order_total)
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE p.category = 'electronics'
GROUP BY o.product_id;

To uwalnia PostgreSQL od konieczności wykonywania podzapytania dla każdego wiersza i znacznie przyspiesza wykonanie.

Zła struktura tabel tymczasowych

Tabele tymczasowe mogą być potężnym narzędziem, jeśli używasz ich z głową. Ale jeśli zapomnisz dodać potrzebne kolumny albo indeksy, tabela tymczasowa zamienia się w wąskie gardło, spowalniając całą procedurę.

Przykład. Tworzymy tabelę tymczasową do obliczeń pośrednich:

CREATE TEMP TABLE temp_sales AS
SELECT region, SUM(order_total) AS total_sales
FROM orders
GROUP BY region;

A potem musisz filtrować po kolumnie total_sales, a indeksu na tym polu nie ma.

Zanim użyjesz tabeli tymczasowej, pomyśl, jak będziesz z nią pracować. Jeśli potrzebny jest filtr po kolumnie, dodaj indeks:

CREATE INDEX idx_temp_sales_total_sales ON temp_sales (total_sales);

Błędy w obliczeniach (np. dzielenie przez zero)

Dzielenie przez zero — klasyka analityki. SQL nie przymknie na to oka, po prostu przerwie wykonanie zapytania.

Załóżmy, że chcesz policzyć średnią wartość zamówień:

SELECT SUM(order_total) / COUNT(*) AS avg_order_value
FROM orders;

Jeśli w tabeli orders nie ma danych, nastąpi dzielenie przez zero i zapytanie zakończy się błędem.

Żeby tego uniknąć, użyj obsługi przypadku, gdy licznik jest zerem:

SELECT
    CASE 
        WHEN COUNT(*) = 0 THEN 0
        ELSE SUM(order_total) / COUNT(*)
    END AS avg_order_value
FROM orders;

Brak logowania i kontroli wykonania

Procedury PL/pgSQL mogą być złożone i składać się z kilku etapów: od obliczeń pośrednich po końcowe raporty. Jeśli w tym łańcuchu coś się wysypie, bez logowania nie dowiesz się, na którym etapie i dlaczego wszystko poszło nie tak.

Załóżmy, że tworzymy procedurę do liczenia metryk, ale zapominamy sprawdzać oczekiwane dane na każdym etapie. W efekcie cała procedura pada, gdy trafi na nieoczekiwane dane (np. puste tabele).

Żeby tego uniknąć, można dodać logowanie na każdym ważnym etapie procedury. Na przykład:

RAISE NOTICE 'Początek obliczania sprzedaży';
-- Twój kod tutaj...

RAISE NOTICE 'Moduł % zakończony pomyślnie', modul;

Dla bardziej złożonych procedur lepiej zapisywać logi do specjalnej tabeli:

CREATE TABLE log_analytics (
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    log_message TEXT
);

W procedurze dodaj:

INSERT INTO log_analytics (log_message)
VALUES ('Procedura zakończona pomyślnie');

Problemy z wydajnością przez brak optymalizacji

Optymalizacja jest ważna nie tylko dla zapytań, ale i dla samych procedur. Jeśli z procedury korzysta wielu użytkowników, jej wykonanie może stać się wąskim gardłem systemu.

Na przykład, oto procedura, która przelicza metryki dla wszystkich regionów, nawet jeśli potrzebne są dane tylko dla jednego regionu:

CREATE OR REPLACE FUNCTION calculate_sales()
RETURNS VOID AS $$
BEGIN
    -- Przeliczanie dla wszystkich regionów
    INSERT INTO sales_metrics(region, total_sales)
    SELECT region, SUM(order_total)
    FROM orders
    GROUP BY region;
END;
$$ LANGUAGE plpgsql;

To generuje zbędne obciążenie.

Jak sobie z tym radzić? Dodaj możliwość filtrowania danych, przekazując region jako parametr:

CREATE OR REPLACE FUNCTION calculate_sales(p_region TEXT)
RETURNS VOID AS $$
BEGIN
    INSERT INTO sales_metrics(region, total_sales)
    SELECT region, SUM(order_total)
    FROM orders
    WHERE region = p_region
    GROUP BY region;
END;
$$ LANGUAGE plpgsql;

Teraz procedura nie będzie obrabiać niepotrzebnych danych, a zapytanie skończy się szybciej.

Ignorowanie narzędzi do analizy wydajności

Narzędzia typu EXPLAIN ANALYZE — to twoi kumple, którzy pokazują, gdzie zapytania zamulają i jak to naprawić. Jeśli piszesz procedurę, ale nie analizujesz jej wydajności, to jakbyś był programistą komputera kwantowego bez oscyloskopu — niby działa, ale co się dzieje, nikt nie wie.

Przykład. Problem w tym zapytaniu będzie widoczny przez EXPLAIN ANALYZE:

SELECT *
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2023;

To zapytanie jest nieefektywne, bo funkcja EXTRACT() wyłącza użycie indeksów.

Można to naprawić tak. Przeanalizuj zapytanie przez:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE order_date >= DATE '2023-01-01' AND order_date < DATE '2024-01-01';

Jak unikać typowych błędów?

Żeby zapobiegać błędom, stosuj te praktyki:

1. Używaj indeksów na polach, które są w filtrach lub joinach.
2. Optymalizuj zapytania: usuwaj zbędne podzapytania, używaj JOIN.
3. Loguj wykonanie. To ułatwi debugowanie, jeśli coś pójdzie nie tak.
4. Zawsze sprawdzaj swoje procedury narzędziami typu EXPLAIN ANALYZE.
5. Widzisz problem z wydajnością? Pomyśl o partycjonowaniu albo zmianie logiki zapytania.

Teraz jesteś uzbrojony w wiedzę, żeby przewidywać i zapobiegać błędom, które mogłyby zostawić twoich analityków bez ekspresu do kawy i Wi-Fi przez wolne zapytania.