Индексирање

Замислете да имаме база на податоци со милиони податоци. Ако сакаме да дојдеме до одредена редица ќе треба да ги изминеме сите податоци во истата. Овој пристап е навистина неефикасен и спор. Токму затоа постои индексирањето, односно забрзување на пристап до саканите податоци, така што базата користи индексни структури за да ги лоцираат податоците по нивните клучеви.

Ќе видиме како се прават индексирање врз нашата база и ќе видиме какви видови индекси може да направиме.

1. Single-column index

explain analyze 
select player_name, transfer_date, transfer_fee, from_club_name, to_club_name 
from transfers 
where player_id = 16136 
order by transfer_date desc; 

потоа правиме индекс:

create index idx_transfers_player_id 
on transfers(player_id); 

и повторно:

explain analyze 
select player_name, transfer_date, transfer_fee, from_club_name, to_club_name 
from transfers 
where player_id = 16136 
order by transfer_date desc; 

Од добиените резултати може да видиме дека без индекс се користи Seq Scan, додека со индекс Index Scan. Индексот е само по player_id и може и понатаму да има Sort - го решава Filter, но не и Sort. Ова е наједноставен индекс и со него не се чита цела табела, туку само одредени редици.

2. Композитен индекс што решава и Filter и Sort

Креираме индекс

create index idx_transfers_player_date_desc 
on transfers(player_id, transfer_date desc); 

explain analyze 
select player_name, transfer_date, transfer_fee, to_club_name 
from transfers 
where player_id = 16136 
order by transfer_date desc 
limit 10; 

Со овој индекс прво се филтрира по player_id, потоа редовите се во редослед по transfer_date desc, за на крај limit 10 дополнително да го забрза query-то. Ако го споредиме со претходниот едноставен индекс, овој композитен индекс овозможува PostgreSQL да ги искористи истите индексни записи и за филтрирање и за сортирање, со што се елиминира потребата од дополнителна Sort операција и се добива поефикасен query plan.

3. Range query & Equality condition

explain analyze 
select player_name, transfer_date, transfer_fee 
from transfers 
where player_id = 16136 
 and transfer_date >= '2023-01-01'; 

креираме композитен индекс:

create index idx_transfers_player_date 
on transfers(player_id, transfer_date); 

и повторно го извршуваме:

explain analyze 
select player_name, transfer_date, transfer_fee 
from transfers 
where player_id = 16136 
 and transfer_date >= '2023-01-01'; 

Може да видиме дека equality условите одат први, а range условот треба да е после нив. Сепак, кај композитните индекси е многу важен и распоредот на колоните, односно доколку наместо горниот индекс ние го креираме следниот:

create index idx_transfers_date_player 
on transfers(transfer_date, player_id); 

PostgreSQL ќе треба да разгледува прво поширок опсег по датум, што ја намалува селективноста на индексот.

Одличен пример кој е типичен за оваа датабаза со реален use case е креирање индекс за најскапи трансфери по сезона.

Креираме индекс:

create index idx_transfers_season_fee_desc 
on transfers(transfer_season, transfer_fee desc); 

explain analyze 
select player_name, transfer_fee, transfer_date, to_club_name 
from transfers 
where transfer_season = '24/25' 
order by transfer_fee desc 
limit 20; 

Ова е одличен пример бидејќи користиме equality filter на transfer_season, сортирање по transfer_fee desc и limit 20. Како што рековме, доколку колоните си ги заменат местата во композитниот индекс, резултатите би биле полоши.

Неправилен редослед на колоните може да направи индексот да биде речиси неупотреблив, иако технички постои.

4. Partial index

Да кажеме дека сакаме да ставиме фокус на големите трансфери и почесто ги анализираме само нив. Тогаш го креираме следниот индекс:

create index idx_transfers_big_fees 
on transfers(transfer_fee desc) 
where transfer_fee >= 10000000; 

explain analyze 
select player_name, transfer_fee, transfer_date, from_club_name, to_club_name 
from transfers 
where transfer_fee >= 10000000 
order by transfer_fee desc; 

Целта на користење Partial index е да добиеме побрзо извршување за одредена категорија query преку користење помал индекс, односно не се трошат ресурси за да се индексираат и малите трансфери ако не се фокусираме на нив.

Bitmap стратегијата овозможува прво да се идентификуваат релевантните редови преку индекс, а потоа да се пристапи до нив во оптимизиран редослед. Сепак, бидејќи индексот не го поддржува редоследот на резултатите, дополнително се извршува Sort операција. Овој пример ја нагласува разликата помеѓу различните стратегии за пристап до податоци и важноста на правилен избор на индекс.

5. Covering index

create index idx_transfers_player_date_cover 
on transfers(player_id, transfer_date desc) 
include (player_name, transfer_fee, to_club_name)

explain analyze 
select player_name, transfer_date, transfer_fee, to_club_name 
from transfers 
where player_id = 16136 
order by transfer_date desc 
limit 10

Covering индексот овозможува не само пронаоѓање на релевантните записи, туку и читање на дел од потребните атрибути директно од индексната структура и се овозможува PostgreSQL во многу случаи да ги прочита потребните атрибути директно од индексот, со што се намалува или дури се елиминира пристапот до heap табелата.

6. Expression index

Го имаме следното query:

explain analyze 
select * 
from transfers 
where transfer_fee + market_value_in_eur > 100000000; 

Тука, обичен индекс на transfer_fee нема баш да ни помогне. Затоа го правиме следниот индекс:

create index idx_transfers_total_value 
on transfers((transfer_fee + market_value_in_eur)); 

и повторно го извршуваме query-то.

Кога во WHERE делот имаме израз, како во овој случај, најдобро е да направиме expression index. Бидејќи условот transfer_fee + market_value_in_eur > 100000000 не може ефикасно да се поддржи со обичен индекс на една колона, се креира индекс врз самиот израз. PostgreSQL го пресметува изразот при внесување или ажурирање на податоците и го зачувува во индексната структура. При извршување на query-то, planner-от може директно да го искористи индексот, што значително го намалува бројот на обработени редици. Важно е да се напомене дека expression index ќе се користи само ако изразот во query-то точно се совпаѓа со оној дефиниран во индексот.

Организација на податоци

Heap file - податоците се чуваат како што доаѓаат (нема редослед). PostgreSQL секогаш користи heap за табели. Има брз INSERT/UPDATE додека пребарувањето е поспоро, поради тоа што ја пребарува целата табела. При додавање не му е важен редоследот и поради тоа не губи време за тоа.

Sorted file - податоците се чуваат физички сортирани (најчесто според еден клуч). Овозможува брзо пребарување, но скап INSERT/UPDATE.

Најпрво ќе направиме 3 табели:

• првата е обична heap табела (оригиналната players табела)
• втората е физички сортирана копија:

  create table players_sorted as 
  select * 
  from players 
  order by highest_market_value_in_eur; 
  

• третата е heap табела + индекс:

  create table players_heap_copy as 
  select * 
  from players; 
   
  create index idx_players_heap_value 
  on players_heap_copy(highest_market_value_in_eur); 
  

Потоа ги споредуваме табелите преку различни query-ја. Најпрво почнуваме со Range query:

explain analyze 
select * 
from players 
where highest_market_value_in_eur between 3000000 and 10000000; 

explain analyze 
select * 
from players_sorted 
where highest_market_value_in_eur between 3000000 and 10000000; 

explain analyze 
select * 
from players_heap_copy 
where highest_market_value_in_eur between 3000000 and 10000000; 

Обичната heap табела најчесто прави Seq Scan, Sorted табелата може да има подобра локалност на податоци ама PostgreSQL и понатаму користи heap пристап. Heap+index најчесто е најдобра за селективни range queries. Индексот овозможува директен пристап до релевантните редици, без да се скенира целата табела.

Следно правиме ORDER BY + LIMIT:

explain analyze 
select * 
from players 
order by highest_market_value_in_eur desc 
limit 20; 

explain analyze 
select * 
from players_sorted 
order by highest_market_value_in_eur desc 
limit 20; 

explain analyze 
select * 
from players_heap_copy 
order by highest_market_value_in_eur desc 
limit 20; 

Може да видиме дека без индекс PostgreSQL мора да сортира, кај sorted copy може читањето да биде поповолно ако редоследот е близок до физичкиот распоред , додека со индекс врз highest_market_value_in_eur може да се добијат топ-N резултати многу побрзо. Во PostgreSQL, „sorted table“ не постои како стабилен концепт. За да се одржи редослед, потребна е експлицитна реорганизација (на пример со CLUSTER) или користење на индекс.

На крај правиме Insert cost comparison:

insert into players_sorted 
select * 
from players 
limit 1; 

insert into players_heap_copy 
select * 
from players 
limit 1; 

Во PostgreSQL, insert во players_sorted нема автоматски да го одржи редоследот како кај theoretical sorted file. Исто така, insert во sorted file е скапа операција бидејќи мора да се зачува редоследот. За да се одржи редоследот, би била потребна реорганизација или index-based access path. PostgreSQL нема вградена поддршка за автоматско одржување на sorted file структура. Затоа, секој insert не гарантира зачувување на редоследот. Во теоретски sorted file системи, insert операцијата е скапа бидејќи мора да се зачува редоследот (што може да бара поместување на редици или реорганизација на датотеката).

Реални примери

1. Анализа на перформанс на клуб во одредено натпреварување (број на настапи, голови и асистенции) со оптимизирано индексирање

explain analyze
select a.player_club_id as club_id,
       c.name as competition_name,
       count(a.appearance_id) as total_appearances,
       sum(a.goals) as total_goals,
       sum(a.assists) as total_assists
from appearances a
join competitions c on c.competition_id = a.competition_id
where a.player_club_id = 6710
  and c.competition_code = 'GB1'
group by a.player_club_id, c.name
order by total_goals desc;

Без индекс, PostgreSQL користи Parallel Seq Scan и чита цела табела appearances, при што се филтрираат стотици илјади редови. Ова е неефикасно.

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

create index idx_competitions_code
on competitions(competition_code, competition_id);

create index idx_appearances_club_comp
on appearances(player_club_id, competition_id)
include (goals, assists);

Индексот на competitions овозможува брзо филтрирање по competition_code и веднаш обезбедува competition_id за join. Индексот на appearances е композитен и овозможува истовремено филтрирање по player_club_id и competition_id, додека include колоните овозможуваат PostgreSQL да ги чита потребните вредности директно од индексот без пристап до табелата, што значително ја подобрува перформансата.

2. Идентификација на највредни играчи во одредено натпреварување врз основа на пазарна вредност и број на настапи

Ги наоѓаме топ 20 највредни играчи кои играле во одредена лига:

explain analyze
select p.player_id,
       p.last_name,
       max(pv.market_value_in_eur) as max_value,
       count(a.appearance_id) as appearances
from players p
join appearances a on a.player_id = p.player_id
join competitions c on c.competition_id = a.competition_id
join player_valuations pv on pv.player_id = p.player_id
where c.competition_code = 'laliga'
group by p.player_id, p.last_name
order by max_value desc
limit 20;

Индекси:

create index idx_appearences_player_comp
on appearances(player_id, competition_id);

create index idx_player_valuations_player_value
on player_valuations(player_id, market_value_in_eur desc);

create index idx_competitions_code
on competitions(competition_code, competition_id);

Индексот на competitions овозможува брзо филтрирање по competition_code, индексот на appearances го оптимизира join-от и филтрирањето по player_id и competition_id, додека индексот на player_valuations овозможува поефикасно пресметување на максималната пазарна вредност. Заедно, овие индекси значително го намалуваат бројот на обработени редови и ја подобруваат перформансата на query-то.

3. Анализа на трансфери на играчи со историска вредност и активности пред трансфер (настапи и настани) со оптимизирано индексирање

explain analyze
select p.last_name,
       t.transfer_date,
       t.transfer_fee,
       pv.market_value_in_eur,
       count(a.player_id) as appearances_before_transfer,
       count(e.game_event_id) as events_before_transfer
from players p
join transfers t on t.player_id = p.player_id

left join lateral (
    select pv.market_value_in_eur
    from player_valuations pv
    where pv.player_id = p.player_id
      and pv.date <= t.transfer_date
    order by pv.date desc
    limit 1
) pv on true

left join appearances a
       on a.player_id = p.player_id
      and a.date < t.transfer_date

left join game_events e
       on e.player_id = p.player_id
      and e.date < t.transfer_date

where t.transfer_season = '23/24'
group by p.last_name, t.transfer_date, t.transfer_fee, pv.market_value_in_eur
order by t.transfer_fee desc
limit 20;

create index idx_transfers_season_player
on transfers(transfer_season, player_name, transfer_date desc, transfer_fee desc);

create index idx_player_valuations_player_date
on player_valuations(player_id, date desc)
include (market_value_in_eur);

create index idx_appearences_player_date
on appearances(player_id, date);

create index idx_game_events_player_date
on game_events(player_id, date);

Индексот на transfers овозможува брзо филтрирање по сезона и помага при сортирање по трансфер сума. Индексот на player_valuations овозможува брзо пронаоѓање на најновата вредност пред трансфер. Индексите на appearances и game_events овозможуваат ефикасно пребарување по player_id и филтрирање по датум, што значително го намалува бројот на обработени редови.