Индексирање

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

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

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 да ги искористи истите индексни записи и за филтрирање и за сортирање.

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 преку користење помал индекс, односно не се трошат ресурси за да се индексираат и малите трансфери ако не се фокусираме на нив.

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 индексот овозможува не само пронаоѓање на релевантните записи, туку и читање на дел од потребните атрибути директно од индексната структура, со што се намалува пристапот до самата табела.

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.

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

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

На крај правиме 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.