Мониторинг

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

Целта на оваа анализа е да се испита влијанието на:

• индексирањето,
• query оптимизацијата,
• scan стратегиите,
• join операциите,
• sorting механизмите

врз перформансите на комплексни аналитички SQL query-и во PostgreSQL.

За секој 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;

->  Bitmap Index Scan on idx_pv_value  (cost=0.00..1714.81 rows=156585 width=0) (actual time=6.292..6.293 rows=157128.00 loops=1)"
Index Cond: (market_value_in_eur > 1000000)"
Index Searches: 1"
Buffers: shared read=138"
->  Hash  (cost=1123.00..1123.00 rows=20000 width=12) (actual time=5.863..5.865 rows=20000.00 loops=1)"
Buckets: 32768  Batches: 1  Memory Usage: 1120kB"
Buffers: shared hit=923"
->  Seq Scan on players p  (cost=0.00..1123.00 rows=20000 width=12) (actual time=0.032..3.416 rows=20000.00 loops=1)"
Buffers: shared hit=923"

Пред оптимизацијата беше забележано Sequential Scan на player_valuations, голем број обработени редици (≈131k+), External Merge Sort за сортирање, скапа филтрација на market_value_in_eur и 273мс време на извршување.

Ова резултираше со зголемена I/O активност и нестабилни перформанси.

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

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 наместо full 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 резултати.

->  Hash Join  (cost=1373.00..3229.21 rows=12332 width=20) (actual time=7.704..30.039 rows=4131.00 loops=2)"
Hash Cond: (t.player_id = p.player_id)"
Buffers: shared hit=1853 read=1094"
->  Parallel Seq Scan on transfers t  (cost=0.00..1686.65 rows=12332 width=16) (actual time=0.312..17.129 rows=12001.50 loops=2)"
Filter: ((transfer_fee IS NOT NULL) AND ((transfer_date)::text >= '2021-01-01'::text))"
Rows Removed by Filter: 27822"
Buffers: shared hit=7 read=1094"

Пред оптимизацијата прашалникот има време на извршување од 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);

->  Index Only Scan using idx_transfers_player_fee on transfers t  (cost=0.42..1686.15 rows=2782 width=4) (actual time=0.048..5.984 rows=2786.00 loops=1)"
Index Cond: (transfer_fee > '5000000'::double precision)"
Heap Fetches: 34"
Index Searches: 1"
Buffers: shared hit=23 read=251"
->  Hash  (cost=1123.00..1123.00 rows=20000 width=12) (actual time=7.017..7.018 rows=20000.00 loops=1)"
Buckets: 32768  Batches: 1  Memory Usage: 1120kB"
Buffers: shared read=923"
->  Seq Scan on players p  (cost=0.00..1123.00 rows=20000 width=12) (actual time=0.175..4.014 rows=20000.00 loops=1)"
Buffers: shared read=923"
->  Index Only Scan using idx_appearances_player_game on appearances a  (cost=0.43..4.67 rows=119 width=8) (actual time=0.008..0.087 rows=271.04 loops=1038)"
Index Cond: (player_id = p.player_id)"
Heap Fetches: 84854"
Index Searches: 1038"
Buffers: shared hit=88323 read=405"

Ако ги споредиме резултатите пред и по оптимизацијата ќе видиме дека времето на извршување се намалило 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;

Според добиените резултати, може да забележиме дека времето на извршување се намалило од 3325мс на 1753мс (46% побрзо), наместо 1038 редици добиваме 690, без CTE обработувавме 9 милиони редови додека сега само 15 илјади, немаме толкав join explosion.

->  Bitmap Heap Scan on transfers  (cost=41.46..1213.45 rows=1701 width=4) (actual time=0.267..2.623 rows=1640.00 loops=1)"
Recheck Cond: (transfer_fee > '10000000'::double precision)"
Heap Blocks: exact=514"
Buffers: shared hit=6 read=514"
->  Bitmap Index Scan on idx_transfers_big_fees  (cost=0.00..41.04 rows=1701 width=0) (actual time=0.208..0.208 rows=1640.00 loops=1)"
Index Cond: (transfer_fee > '10000000'::double precision)"
Index Searches: 1"
Buffers: shared read=6"

Може да забележиме дека CTE значително го намалуваат dataset-от, овозможува подобар planner strategy и подобро користи индекси. Најважни промени кај CTE верзијата се: рано ги филтрира потребните играчи (немаме милиони редици за обработување), го намалува бројот на редови пред JOIN операциите (со што избегнува join explosion). Сепак индексите сами по себе не се доволни да направат огромна разлика, туку најважно е како ќе го структурираме прашалникот.

/	Before		After
/	Execution time	Scan Type	Execution time	Scan Type
Q1	273ms	SeqScan	248ms	BitMap Heap
Q2	7.8s	SeqScan	3.5s	SeqScan
Q3	4119ms	SeqScan	3325ms	Index
Q3 w/ CTE	3325ms		1753ms

Оптимизацијата покажува дека индексите иако се корисни, не се доволни сами по себе. Најголемото подобрување доаѓа од редуцирање на dataset-от рано (CTE) и избегнување на join explosion. Sorting-от останува една од најскапите операции.