Мониторинг

Benchmark Report – Оптимизација на индекси и query-и во PostgreSQL

Целта на оваа анализа е да се испита влијанието на индексите, query оптимизацијата, scan стратегиите, join операциите и sorting механизмите врз перформансите на PostgreSQL при извршување на комплексни аналитички SQL query-иња.

За секој query беа направени мерења пред оптимизација и мерења по оптимизација. Анализата е направена со: EXPLAIN ANALYZE; EXPLAIN (ANALYZE, BUFFERS).

Q1 - Анализа на player valuations

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * 
FROM ( 
    SELECT 
        p.player_id, 
        p.last_name, 
        pv.market_value_in_eur, 
        ROW_NUMBER() OVER ( 
            PARTITION BY p.player_id 
            ORDER BY pv.date DESC 
        ) AS rn, 
        AVG(pv.market_value_in_eur) OVER ( 
            PARTITION BY p.player_id 
        ) AS avg_value 
    FROM players p 
    JOIN player_valuations pv 
        ON p.player_id = pv.player_id 
    WHERE pv.market_value_in_eur > 1000000 
) t 
WHERE rn = 1 
ORDER BY avg_value DESC 
LIMIT 10000;

Пред оптимизацијата, прашалникот ги дава следниве резултати: 273мс време на извршување, Seq Scan, процесирани редици 131462, одстранети редици 339478, Sort Type: External Merge. Главните проблеми се тоа што има Full Sequential Scan на player_valuations и скапо филтрирање.

Ги додаваме следниве индекси:

CREATE INDEX idx_pv_player_date
ON player_valuations(player_id, date DESC);

CREATE INDEX idx_pv_value
ON player_valuations(market_value_in_eur);

По оптимизацијата добиваме 248мс време на извршување, Bitmap Heap Scan и повторно за sorting се користи External Merge. Најголемата промена е тоа што за филтерот market_value_in_eur > 1000000 се користи Bitmap Index Scan, наместо скенирање на цела табела, се читаат само потребните heap pages. Сепак и покрај индексите има многу редици што се процесираат и сортингот останува главен bottleneck.

Q2 – Анализа на трансфери

EXPLAIN ANALYZE 
SELECT DISTINCT 
    p.last_name, 
    t.from_club_id, 
    t.to_club_id, 
    TO_CHAR(t.transfer_fee, '999,999,999') AS transfer_fee, 
    cg.club_id 
FROM transfers t 
JOIN players p ON p.player_id = t.player_id 
LEFT JOIN club_games cg ON cg.club_id = t.from_club_id 
WHERE t.transfer_date >= '2021-01-01' AND t.transfer_fee IS NOT NULL 
ORDER BY transfer_fee DESC  
LIMIT 10000; 

Query-то прави join помеѓу transfers, players, club_games, филтрира трансфери по 2021 година, ги исклучува NULL transfer fees, сортира по transfer fee, враќа top 10,000 резултати.

ПРЕД ОПТИМИЗАЦИЈА прашалникот има време на извршување од 7.8с, чита 2.5 милиони редици, користи Seq Scan, Incremental Sort и Hash Join + Nested Loop. Главен проблем е што PostgreSQL прави SeqScan на transfers и players - прашалникот враќа голем дел од табелата. Потоа, ги додаваме следниве индекси:

CREATE INDEX idx_transfers_date
ON transfers(transfer_date);

CREATE INDEX idx_transfers_player
ON transfers(player_id);

CREATE INDEX idx_transfers_date_player
ON transfers(transfer_date, player_id);

и со мерењето ги добиваме следниве резултати: 3.5с време на извршување, што е значително подобрување, и повторно користење на Seq Scan. Сепак, главното прашање е зашто сеуште користиме Seq Scan. Кога враќаме 20% или повеќе од табелата, PostgreSQL знае дека е поевтино да користи Seq Scan од Index Scan.

Q3 - Комплексен aggregation query

EXPLAIN (ANALYZE, BUFFERS) 
SELECT 
    p.player_id, 
    p.last_name, 
    COUNT(a.game_id) AS appearances_count, 
    MAX(pv.market_value_in_eur) AS max_market_value 
FROM players p 
LEFT JOIN appearances a 
    ON p.player_id = a.player_id 
LEFT JOIN player_valuations pv 
    ON p.player_id = pv.player_id 
WHERE EXISTS ( 
    SELECT 1 
    FROM transfers t 
    WHERE t.player_id = p.player_id 
    AND t.transfer_fee > 5000000 
) 
GROUP BY 
    p.player_id, 
    p.last_name 
HAVING COUNT(a.game_id) > 10 
ORDER BY max_market_value DESC 
LIMIT 8000;

Главни проблеми се full scans на appearances и player_valuations, join спојува 9 милиони редици пред агрегацијата и скапа агрегација.

Ги додаваме следниве индекси:

CREATE INDEX idx_transfers_player_fee
ON transfers(player_id, transfer_fee);

CREATE INDEX idx_appearances_player_game
ON appearances(player_id, game_id);

CREATE INDEX idx_player_valuations_player_value
ON player_valuations(player_id, market_value_in_eur);

Ако ги споредиме резултатите пред и по оптимизацијата ќе видиме дека времето на извршување се намалило 4119мс -> 3325мс, за transfers access наместо Seq Scan користиме Index Only Scan и за appearances access наместо Seq Scan користиме Partial Index Only Scan. Најголемо подобрување имаме во EXISTS subquery-то поради индексот на transfers, додека appearances не е целосно оптимизирано и сеуште има heap fetches, односно PostgreSQL мора да оди во table pages. Најголем проблем останува join explosion.

Овој пример ќе го извршиме и со CTEs табели и ќе видиме дали има разлика пред и по оптимизацијата.

EXPLAIN (ANALYZE, BUFFERS)
WITH expensive_players AS (
    SELECT DISTINCT player_id
    FROM transfers
    WHERE transfer_fee > 10000000
),
player_appearances AS (
    SELECT
        player_id,
        COUNT(DISTINCT game_id) AS appearances
    FROM appearances
    GROUP BY player_id
    HAVING COUNT(DISTINCT game_id) > 20
),
latest_value AS (
    SELECT DISTINCT ON (player_id)
        player_id,
        market_value_in_eur,
        date
    FROM player_valuations
    ORDER BY player_id, date DESC
),
market_stats AS (
    SELECT
        player_id,
        AVG(market_value_in_eur) AS avg_market_value,
        MAX(market_value_in_eur) AS max_market_value
    FROM player_valuations
    GROUP BY player_id
)
SELECT
    p.player_id,
    p.last_name,
    pa.appearances,
    ms.avg_market_value,
    ms.max_market_value,
    lv.market_value_in_eur AS latest_market_value
FROM players p
JOIN expensive_players ep
    ON p.player_id = ep.player_id
JOIN player_appearances pa
    ON p.player_id = pa.player_id
JOIN market_stats ms
    ON p.player_id = ms.player_id
JOIN latest_value lv
    ON p.player_id = lv.player_id
ORDER BY ms.max_market_value DESC
LIMIT 5000;

{| class="wikitable sortable" style="text-align:center; width:100%;" ! Metric ! Non-CTE Query ! WITH CTE Query

! Improvement / Observation Execution Time ~3274 ms ~1753 ms ✅ ~46% побрзо извршување - Final Rows Returned 1038 690 ✅ Помал финален dataset - Intermediate Rows Processed ~9.25M rows ~15k rows Огромно намалување на обработени редови - Join Explosion Многу висок Значително намален

Подобар execution flow

- Scan Strategy Hash Right Join + Seq Scan Merge Join + Index Scan ✅ Пооптимизиран planner strategy - Seq Scan Usage Високо Намалено ✅ Помал full table scanning - Index Usage Делумно искористени индекси Значително подобро искористени индекси ✅ Подобрен access path - Temporary Files ✅ Да ❌ Не ✅ Нема disk spill - Disk Spill ✅ External spill ❌ Нема spill ✅ Подобра memory utilization - Sort Performance Temp read/write operations In-memory quicksort ✅ Побрзо сортирање - Buffers Hit ~92k ~947k ⚠️ Повеќе cache processing поради index traversal - Heap Fetches Високи Сè уште присутни ⚠️ Visibility map не е целосно оптимизиран - Planner Complexity Висока Подобро организирана ✅ Cleaner execution plan - Aggregation Cost Многу висока Намалена ✅ Помал aggregation workload - Overall Efficiency Тежок аналитички query Значително поефикасен query }