Мониторинг

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.