CTE ilə işləyərkən tipik səhvlər və onlardan necə qaçmaq olar

Və budur, nəhayət, gəldik CTE ilə işləməyin qaranlıq tərəfinə — tipik səhvlərə. Ən bomba query belə, bu güclü alətləri səhv istifadə etsən, asanlıqla çökə bilər. Amma narahat olma, sənin üçün diaqnostika və qarşısını almaq üçün tam bir təlimatımız var!

1. Səhv: CTE-nin materializasiyası və onun nəticələri

PostgreSQL-də CTE ilə işləyərkən əsas xüsusiyyətlərdən biri — onların default olaraq materializasiya olunmasıdır. Yəni, CTE-nin nəticəsi əvvəlcə işlənir və yaddaşda (və ya data çoxdursa, diskdə) müvəqqəti saxlanılır. Əgər query-lər çoxdursa və ya data böyükdürsə, bu, icranı ciddi şəkildə ləngidə bilər.

Nümunə:

WITH heavy_data AS (
    SELECT * FROM large_table
)
SELECT * FROM heavy_data WHERE column_a > 100;

İlk baxışda elə gəlir ki, CTE sadəcə datanı filtr edir. Amma əslində heavy_data əvvəlcə tam yüklənir və materializasiya olunur, yalnız bundan sonra filtrasiya tətbiq olunur. Bu, həqiqətən çox vaxt apara bilər.

Necə qaçmaq olar?

PostgreSQL 12-dən etibarən CTE-ləri inline expression (subquery kimi) kimi istifadə etmək olur və bu, materializasiya problemini həll edir. Bunun üçün sadəcə həmin CTE-ləri bir dəfə istifadə etmək və aralıq nəticələri saxlamağa ehtiyac olmamalıdır.

Optimallaşdırılmış yanaşma nümunəsi:

WITH inline_data AS MATERIALIZED (
    SELECT * FROM large_table
)
SELECT * FROM inline_data WHERE column_a > 100;

Məsləhət: Əgər materializasiya istəsən, MATERIALIZED yaz. İstəmirsənsə — NOT MATERIALIZED istifadə et.

2. Səhv: rekursiv CTE-lər sonsuz dövrə düşür

Rekursiv CTE-lər güclüdür, amma iterasiya dərinliyinə limit qoymadan istifadə etsən, sonsuz dövrə düşə bilər. Bu, təkcə icranı ləngitmir, həm də bütün resursları yeyir.

Nümunə:

WITH RECURSIVE endless_loop AS (
    SELECT 1 AS value

    UNION ALL

    SELECT value + 1
    FROM endless_loop
)
SELECT * FROM endless_loop;

Bu query sonsuz sayda sətir yaradacaq, çünki rekursiyanı dayandıran şərt yoxdur.

Necə qaçmaq olar?

WHERE ilə aydın stop şərti əlavə et. Məsələn:

WITH RECURSIVE limited_loop AS (
    SELECT 1 AS value

    UNION ALL

    SELECT value + 1
    FROM limited_loop
    WHERE value < 10
)
SELECT * FROM limited_loop;

Məsləhət: Əgər böyük hierarxiyalar üçün rekursiv CTE istifadə edirsənsə, PostgreSQL-də max_recursion_depth ilə rekursiya dərinliyini limitlə.

3. Səhv: UNION və UNION ALL səhv istifadə olunur

CTE-də əsas və rekursiv query-ləri birləşdirəndə, UNION və UNION ALL arasında səhv seçim gözlənilməz nəticələrə gətirib çıxara bilər. Məsələn, UNION təkrarlanan sətirləri silir və əlavə hesablama xərci yaradır.

Nümunə:

WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION  -- Burada UNION ALL daha yaxşıdır

    SELECT e.employee_id, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

Bu nümunədə UNION təsadüfən təkrarlanan vacib sətirləri silə bilər. Üstəlik, query-ni ləngidir!

Necə düzəltmək olar?

Əgər təkrarlananları silmək mütləq deyilsə, UNION ALL istifadə et:

UNION ALL

4. Səhv: bir query-də həddindən artıq çox CTE

Query-ni daha strukturlaşdırılmış etmək istəyənlər bəzən onlarla CTE əlavə edirlər. Bu, kodu qarışdırır və PostgreSQL-in query planner-i üçün yük yaradır.

Nümunə:

WITH cte1 AS (...),
     cte2 AS (...),
     cte3 AS (...),
     ...
     cte20 AS (...)
SELECT ...
FROM cte20;

Bu, istənilən developer üçün kabus kimidir.

Necə düzəltmək olar?

— Query-ni bir neçə daha sadə query-yə böl. Onlarla CTE olan mega-query əvəzinə, bir neçə müstəqil query yaz.

— Başqa variant: aralıq nəticələri bir neçə dəfə istifadə etmək lazımdırsa, müvəqqəti cədvəllərdə saxla.

5. Səhv: indeks olmadan mürəkkəb CTE-lər

Əgər CTE böyük data ilə işləyir, amma cədvəllərə index əlavə etməyi unutmusan, query-lər çox yavaş işləyəcək. İndex — sənin database-in üçün turbo kimidir.

Nümunə:

WITH filtered_data AS (
    SELECT * FROM large_table WHERE unindexed_column = 'value'
)
SELECT * FROM filtered_data;

Necə düzəltmək olar?

CTE istifadə etməzdən əvvəl cədvəllərinin optimallaşdırıldığından əmin ol:

CREATE INDEX idx_large_table ON large_table(unindexed_column);

6. Səhv: CTE-dən dəfələrlə data çağırmaq üçün istifadə cəhdi

CTE yaradılır, icra olunur və sonra "dondurulur". Əgər nəticəni bir neçə yerdə istifadə etmək lazımdırsa, data yenidən hesablanmayacaq — və bu, bəzən səhvlərə gətirib çıxarır.

Nümunə:

WITH data AS (
    SELECT x, y FROM some_table
)
SELECT x FROM data
WHERE y > 10;
-- Əgər bir də data-nı hesablamaq lazım olsa, bu baş verməyəcək.

Necə düzəltmək olar?

Əgər dinamika və ya yenidən hesablama lazımdırsa, bəlkə də CTE ən yaxşı seçim deyil. Subquery istifadə et.

7. Səhv: şərhlərin olmaması

CTE-lər bomba alətdir, amma kimə lazımdır ki, iki həftə sonra heç kim, hətta özün belə oxuya bilməyəcəyin mürəkkəb SQL-query?

Nümunə:

WITH data_filtered AS (
    SELECT *
    FROM large_table
    WHERE some_column > 100
)
SELECT * FROM data_filtered;

Bir ay sonra artıq heç kim xatırlamayacaq ki, bu data niyə filtr olunurdu!

Ona görə də query-lərə şərh yaz, xüsusilə mürəkkəb və ya rekursiv CTE istifadə edirsənsə:

WITH data_filtered AS (
    -- some_column > 100 şərti ilə datanın filtrasiyası
    SELECT *
    FROM large_table
    WHERE some_column > 100
)
SELECT * FROM data_filtered;

8. Səhv: CTE-dən müvəqqəti cədvəllər əvəzinə həddindən artıq istifadə

Bəzən müvəqqəti cədvəllər daha yaxşı işləyir. Məsələn, nəticəni bir neçə fərqli query-də istifadə etmək lazımdırsa və ya çox böyük data ilə işləyirsənsə.

Nümunə:

WITH temp_data AS (
    SELECT * FROM large_table
)
SELECT * FROM temp_data WHERE column_a > 100;
SELECT * FROM temp_data WHERE column_b < 50;

Belə query CTE ilə iki dəfə icra olunacaq, halbuki data dəyişmir!

Necə düzəltmək olar?

Əgər data bir neçə dəfə istifadə olunacaqsa, müvəqqəti cədvəl yarat:

CREATE TEMP TABLE temp_table AS
SELECT * FROM large_table;

SELECT * FROM temp_table WHERE column_a > 100;
SELECT * FROM temp_table WHERE column_b < 50;

Son məsləhət

Hər güclü funksiya kimi, CTE ilə işləyərkən başa düşmək lazımdır ki, onlar həmişə ən yaxşı alət deyil. Niyə və necə istifadə etdiyini düşün. "Nə qədər çox CTE, o qədər yaxşı" yanaşması performansı və kodun oxunaqlığını ciddi şəkildə poza bilər. Və əlbəttə, performans testləri apar və query-ləri optimallaşdırmağı unutma.