Optymalizacja zapytań z funkcjami okienkowymi

Został jeszcze jeden ważny szczegół, o którym jeszcze nie gadaliśmy — wydajność zapytań z funkcjami okienkowymi. Nawet najbardziej eleganckie zapytanie może zamienić się w powolnego żółwia, jeśli nie pomyślisz o optymalizacji. Dzisiaj właśnie tym się zajmiemy!

Funkcje okienkowe są niesamowicie elastyczne i potężne. Ale ta elastyczność to nie tylko prezent, ale też potencjalne zagrożenie dla wydajności. PostgreSQL, niestety, nie działa na zasadzie "magii" i potrzebuje zasobów, żeby przetworzyć dane. A jeśli dorzucisz funkcje okienkowe do wielkich tabel, twoje zapytanie może robić coś, co przypomina maraton w miejscu.

Optymalizacja pozwoli ci:

• Przyspieszyć zapytania pracujące na dużych ilościach danych.
• Zminimalizować obciążenie bazy danych.
• Sprawić, że twoje zapytania będą bardziej przyjazne dla serwera (i kolegów, jeśli też pracują z bazą!).

No to zanurzamy się i sprawdzamy, co można zrobić, żeby twoje zapytania śmigały jak bolid na torze wyścigowym.

Podstawy działania funkcji okienkowych

Zanim zaczniemy optymalizować, warto zrozumieć, co dokładnie spowalnia nasze zapytanie. PostgreSQL działa z funkcjami okienkowymi tak:

1. Sortuje dane, jeśli w zapytaniu jest ORDER BY wewnątrz OVER().
2. Przetwarza każdy wiersz w ramach określonego okna lub grupy.
3. Zwraca wynik dla każdego wiersza.

Wyobraź sobie teraz, że mamy tabelę sales z 10 milionami wierszy. Jeśli w twoim zapytaniu nie ma filtrów, PostgreSQL będzie obrabiał każdy z tych wierszy. To już nie maraton — to prawdziwa bieżnia bez końca.

Jak przyspieszyć funkcje okienkowe?

1. Używanie indeksów do przyspieszenia sortowania

Większość funkcji okienkowych korzysta z ORDER BY wewnątrz OVER(), żeby kontrolować kolejność wierszy. To znaczy, że PostgreSQL musi posortować twoje dane przed wykonaniem funkcji okienkowej.

Jeśli masz indeks na kolumnie (albo kolumnach), które są używane w ORDER BY, PostgreSQL może znacznie przyspieszyć to sortowanie.

Przykład

CREATE INDEX idx_sales_date ON sales (sale_date);

Teraz, jeśli napiszesz zapytanie z sortowaniem po sale_date, indeks wchodzi do gry:

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Bez indeksu na sale_date każde wykonanie zapytania będzie wymagało kosztownego sortowania — PostgreSQL będzie w panice szukał, jak szybciej uporządkować wiersze.

2. Stosowanie filtrów za pomocą WHERE

Ograniczenie ilości danych — to kluczowa technika optymalizacji. Jeśli nie musisz obrabiać wszystkich 10 milionów wierszy, ale tylko ostatni rok — zawęź zakres danych przez WHERE!

Przykład

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales
WHERE sale_date >= '2023-01-01';

To jak filtrowanie brudnej wody przez sito, żeby zostawić tylko to, co potrzebne.

3. Wybór odpowiedniego okna

Przy pracy z funkcjami okienkowymi z agregacją, np. SUM(), ważne jest, żeby wybrać właściwe okno. Jeśli używasz domyślnego okna (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), PostgreSQL będzie brał wszystkie wiersze do bieżącego włącznie. To może być nieoptymalne dla dużych tabel.

Przykład: użycie ROWS

Jeśli potrzebujesz tylko kilku wierszy przed bieżącym, lepiej to jasno określić przez ROWS:

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (
        PARTITION BY product_id 
        ORDER BY sale_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM sales;

W tym przypadku PostgreSQL przetwarza tylko trzy wiersze (dwa poprzednie + bieżący) dla każdego wiersza. To efektywniejsze niż domyślne obrabianie setek wierszy.

4. Minimalizowanie liczby funkcji okienkowych

Każda funkcja okienkowa jest przetwarzana przez PostgreSQL osobno. Jeśli używasz kilku funkcji okienkowych, PostgreSQL może sortować dla każdej z nich oddzielnie, co spowalnia zapytanie. Ale jeśli parametry okien (np. PARTITION BY i ORDER BY) są takie same, PostgreSQL może to zoptymalizować.

Przykład: optymalizacja z tym samym oknem

SELECT
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) AS row_num
FROM sales;

Obie funkcje (SUM() i ROW_NUMBER()) używają tego samego okna. PostgreSQL wykona tylko jedno sortowanie — i to jest super.

5. Partyckjonowanie tabel

Jeśli twoja tabela jest za duża, warto pomyśleć o fizycznym podziale jej na mniejsze części. PostgreSQL pozwala tworzyć partycjonowane tabele, żeby dane trafiały do różnych segmentów. To może znacznie przyspieszyć przetwarzanie.

Przykład tworzenia partycjonowanej tabeli

CREATE TABLE sales_partitioned (
    sale_date DATE NOT NULL,
    product_id INT NOT NULL,
    amount NUMERIC NOT NULL
) PARTITION BY RANGE (sale_date);

Potem tworzysz odpowiednią liczbę partycji, np. na lata:

CREATE TABLE sales_2022 PARTITION OF sales_partitioned
FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');

CREATE TABLE sales_2023 PARTITION OF sales_partitioned
FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

Teraz, jeśli użyjesz WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31', PostgreSQL automatycznie sięgnie tylko do odpowiedniej partycji.

Więcej o partycjonowaniu tabel dowiesz się pod koniec kursu :P

6. Unikaj zbędnych danych (SELECT tylko to, co trzeba)

Wybieraj tylko te kolumny, które są potrzebne do działania funkcji i twojego wyniku. Jeśli twojej funkcji okienkowej potrzebne są tylko product_id, sale_date i amount, nie ciągnij za sobą całego zoo tabeli z bio-danymi klienta.

Przykład "oszczędnego" zapytania

SELECT
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Mniej danych — mniej roboty dla PostgreSQL.

7. Używanie materializacji (MATERIALIZED VIEW)

Jeśli często wykonujesz te same obliczenia z funkcjami okienkowymi, możesz zapisać wyniki w widoku materializowanym. Materialized View trzyma dane na dysku, żeby nie powtarzać ciężkich zapytań.

Przykład tworzenia widoku materializowanego

CREATE MATERIALIZED VIEW sales_running_total AS
SELECT 
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Teraz dane możesz po prostu pobrać:

SELECT * FROM sales_running_total WHERE product_id = 10;

8. Planowanie zapytań z użyciem EXPLAIN i EXPLAIN ANALYZE

Jak w innych aspektach SQL, możesz użyć EXPLAIN lub EXPLAIN ANALYZE, żeby zobaczyć, jak PostgreSQL wykonuje twoje zapytanie — i gdzie są wąskie gardła.

Przykład analizy zapytania

EXPLAIN ANALYZE
SELECT 
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

To narzędzie pokaże ci, gdzie PostgreSQL traci najwięcej czasu i będziesz mógł zoptymalizować wąskie gardła.

Funkcje okienkowe to potężne narzędzie do analizy danych, ale trzeba z nimi uważać. Chcesz szybkości? Wybieraj indeksy, dodawaj filtry, nie zapominaj o partycjach i śmiało korzystaj z widoków materializowanych. PostgreSQL lubi, gdy korzystasz z niego z głową!