Sorğuların optimallaşdırılması window funksiyaları ilə

Hələ danışmadığımız bir vacib detal qalıb — window funksiyaları ilə sorğuların performansı. Axı, ən gözəl sorğu belə optimallaşdırılmasa, tısbağa kimi yavaş işləyə bilər. Bu gün məhz bununla məşğul olacağıq!

Window funksiyaları inanılmaz dərəcədə çevik və güclüdür. Amma bu çeviklik həm də performans üçün potensial təhlükədir. PostgreSQL, təəssüf ki, "sehr" prinsipi ilə işləmir və məlumatları emal etmək üçün resurslara ehtiyacı var. Əgər window funksiyaları böyük cədvəllərə qoşsan, sorğun sanki yerində qaçan marafona çevrilə bilər.

Optimallaşdırma sənə imkan verəcək:

• Böyük həcmli məlumatlarla işləyən sorğuları sürətləndirməyə.
• Verilənlər bazasına düşən yükü minimuma endirməyə.
• Sorğularını server üçün (və həmçinin bazada işləyən dostların üçün) daha dostyana etməyə.

Gəlin başlayaq və baxaq, sorğularımızı necə Formula-1 maşını kimi uçura bilərik.

Window funksiyalarının işləmə əsasları

Optimallaşdırmaya başlamazdan əvvəl, nəyi yavaşlatdığını başa düşmək yaxşı olar. PostgreSQL window funksiyaları ilə belə işləyir:

1. Əgər sorğuda ORDER BY OVER() içində varsa, məlumatları sıralayır.
2. Hər bir sətri təyin olunmuş window frame və ya qrup daxilində emal edir.
3. Hər sətrə nəticə qaytarır.

Təsəvvür elə ki, səndə sales adlı cədvəl var və orda 10 milyon sətir var. Əgər sorğunda filtr yoxdursa, PostgreSQL bu sətirlərin hamısını emal edəcək. Bu artıq marafon yox, çıxışı olmayan qaçış yoludur.

Window funksiyaları necə sürətləndirmək olar?

1. Sıralamanı sürətləndirmək üçün indekslərdən istifadə

Əksər window funksiyaları ORDER BY OVER() içində istifadə edir ki, sətirlərin sırasını idarə etsin. Bu o deməkdir ki, PostgreSQL məlumatlarını window funksiyasını icra etməzdən əvvəl sıralamalıdır.

Əgər ORDER BY-da istifadə olunan sütun(lar)da indeks varsa, PostgreSQL bu sıralamanı xeyli sürətləndirə bilər.

Nümunə

CREATE INDEX idx_sales_date ON sales (sale_date);

İndi əgər sale_date üzrə sıralama ilə sorğu yazsan, indeks işə düşəcək:

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

sale_date-də indeks olmasa, hər dəfə sorğunu işə salanda baha başa gələn sıralama olacaq — PostgreSQL panikada sətirləri necə tez düzəltməyi axtaracaq.

2. WHERE ilə filtrlərdən istifadə

Məlumatların həcmini azaltmaq — əsas optimallaşdırma texnikasıdır. Əgər sənə 10 milyon sətirin hamısı lazım deyil, təkcə son ili işləmək lazımdırsa — WHERE ilə məlumat aralığını daralt!

Nümunə

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';

Bu, sanki çirkli suyu süzgəcdən keçirib, yalnız faydalı məlumatı saxlayırsan.

3. Düzgün window frame seçimi

Aggregation ilə window funksiyaları işləyərkən, məsələn, SUM() ilə, düzgün window frame seçmək vacibdir. Əgər default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) istifadə edirsənsə, PostgreSQL cari sətrə qədər bütün sətirləri daxil edəcək. Bu, böyük cədvəllər üçün optimal deyil.

Nümunə: ROWS istifadə

Əgər sənə yalnız cari sətrə qədər bir neçə sətir lazımdırsa, bunu ROWS ilə açıq şəkildə göstərmək daha yaxşıdır:

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;

Bu halda PostgreSQL hər sətrə görə yalnız üç sətir (iki əvvəlki + cari) emal edir. Bu, default olaraq yüzlərlə sətri emal etməkdən daha effektivdir.

4. Window funksiyalarının sayını minimuma endir

Hər bir window funksiyası PostgreSQL tərəfindən ayrıca emal olunur. Əgər bir neçə window funksiyası istifadə edirsənsə, PostgreSQL hər biri üçün ayrıca sıralama edə bilər, bu da yavaşıq gətirir. Amma əgər window parametrləri (PARTITION BY və ORDER BY kimi) eynidirsə, PostgreSQL bunu daha effektiv emal edə bilər.

Nümunə: eyni window ilə optimallaşdırma

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;

Hər iki funksiya (SUM() və ROW_NUMBER()) eyni frame istifadə edir. PostgreSQL yalnız bir dəfə sıralama edəcək — və bu superdir.

5. Cədvəllərin partitioning-i

Əgər cədvəlin çox böyükdürsə, onu fiziki olaraq kiçik hissələrə bölmək barədə düşünmək lazımdır. PostgreSQL partitioned table-lar yaratmağa imkan verir ki, məlumatlar müxtəlif seqmentlərdə saxlanılsın. Bu, emalı xeyli sürətləndirə bilər.

Partitioned table yaradılması nümunəsi

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

Bundan sonra, məsələn, illərə görə bir neçə partition yaradılır:

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');

İndi əgər WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31' istifadə etsən, PostgreSQL avtomatik olaraq yalnız uyğun partition-a müraciət edəcək.

Cədvəllərin partitioning-i barədə daha ətraflı kursun sonunda öyrənəcəksən :P

6. Artıq məlumatlardan qaç (SELECT yalnız lazım olanı seç)

Funksiya və nəticə üçün lazım olan sütunları seç. Əgər window funksiyan üçün yalnız product_id, sale_date və amount lazımdırsa, bütün cədvəldəki əlavə məlumatları daşımağa ehtiyac yoxdur.

"Qənaətcil" sorğu nümunəsi

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

Daha az məlumat — PostgreSQL üçün daha az iş deməkdir.

7. Materializasiya istifadə et (MATERIALIZED VIEW)

Əgər tez-tez eyni window funksiyaları ilə hesablamalar aparırsansa, nəticələri materialized view-da saxlamaq olar. Materialized View məlumatları diskdə saxlayır ki, mürəkkəb sorğuları təkrar-təkrar hesablamaq lazım olmasın.

Materialized view yaradılması nümunəsi

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;

İndi məlumatı sadəcə soruşmaq olar:

SELECT * FROM sales_running_total WHERE product_id = 10;

8. EXPLAIN və EXPLAIN ANALYZE ilə sorğuları planlaşdır

SQL-in digər hissələrində olduğu kimi, EXPLAIN və ya EXPLAIN ANALYZE istifadə edə bilərsən ki, PostgreSQL-in sorğunu necə icra etdiyini və dar boğazların harada olduğunu başa düşəsən.

Sorğuların analizi nümunəsi

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

Bu alət sənə göstərəcək ki, PostgreSQL ən çox vaxtı harada sərf edir və dar boğazları optimallaşdıra bilərsən.

Window funksiyaları — məlumat analizində güclü alətdir, amma onlarla ehtiyatlı olmaq lazımdır. Sürət istəyirsən? İndekslərdən istifadə et, filtrlər əlavə elə, partition-ları unutma və materialized view-lardan çəkinmə. PostgreSQL düşünülmüş istifadəni sevir!