Оптимизација на прашалници и погледи

View 1. Head to head

Филтер кој ќе се користи за vw_head_to_head ќе биде според id на двата тима. Случај на употреба е преглед на претходни средби помеѓу двата тима за еден натпревар.

Иницијалното време на извршување е 3s 123ms. Времето не е прифатливо. Најскапите операции се full scan на табелите event и event_attribute. Прво се преуредува кверито да филтрира според полето event.event_type_id наместо според event_type.type, со цел да се искористи индексот кој ќе биде поставен на Event табелата:

Before:
FROM Event e
JOIN Event_type et ON et.event_type_id = e.event_type_id
LEFT JOIN Event_attribute ea ON ea.event_id = e.event_id
AND ea.attribute_name = 'shot.outcome'
WHERE
(et.type = 'Shot' AND ea.attribute_value = 'Goal')
OR et.type = 'Own Goal For'

After:
FROM Event e
LEFT JOIN Event_attribute ea ON ea.event_id = e.event_id
AND ea.attribute_name = 'shot.outcome'
WHERE
(e.event_type_id = (SELECT event_type_id FROM Event_type WHERE type = 'Shot')
AND ea.attribute_value = 'Goal')
OR e.event_type_id = (SELECT event_type_id FROM Event_type WHERE type = 'Own Goal For') Додаваме индекс на Event(event_type_id, match_id, team_id). По преуредувањето на погледот и користење на индексот на Event табелата времето се намалува на 738ms.

WHERE (et.type = 'Shot' AND ea.attribute_value = 'Goal') OR et.type = 'Own Goal For' – во овој случај, едната гранка треба да дојде до event attribute, додека другата (за автогол) нема event_attributes. Планерот мора да избере еден план и во случајов го бира да ги спои сите евенти со event_attributes па потоа да го провери условот. Поради тоа овој услов го преуредуваме со UNION за да се оптимизира додатно и да се искористи индексот на event_attribute табелата.
Додаваме индекс на Event_attribute(attribute_value, event_id) where attribute_name = ‘shot.outcome’ Времето се намалува на 485ms што е прифатливо. План на евалуација по индексите:

View 2. Season overview

Филтер кој ќе се користи за vw_season_overview ќе биде според id на сезоната и дивизијата. Случај на употреба е преглед на статистики за една сезона во една лига (дивизија).

Иницијалното време на извршување е 4s 792ms. Времето не е прифатливо. Се прави истото преуредување за агрегирање на головите како и во првиот поглед за да се искористат истите индекси на Event и Event_attribute табелите. Дополнително се додава индекс на Ticket(match_id) where is_scanned = true
По реискористување на индексите од првиот поглед и додавање на новиот, времето се намалува на 1s 64ms што е прифатливо земајќи в предвид дека се агрегираат податоци помеѓу 3 табели со по ~15 милиони редици: План на евалуација по оптимизациите:

View 3. Stadium utilization

Филтер кој ќе се користи за vw_utilization ќе биде според id на стадионот и сезоната. Случај на употреба е преглед на статистики за користење на стадион во една сезона.
Иницијалното време на извршување е 1s 571ms. Времето не е прифатливо. Од планот на евалуација гледаме дека најскапата операција е full scan на Ticket табелата. Додаваме индекс на Ticket(match_id)
Оптимизираното време на извршување е 433ms што е прифатливо. План на евалуација по додавање индекси:

View 4. vw_match_summary

Филтер кој ќе се користи за vw_match_summary ќе биде според match_id. Случај на употреба е преглед на резултати и статистики за натпревари — голови, картони, судија и стадион.

Иницијалното време на извршување е 8s 751ms. Времето не е прифатливо.

Најскапите операции се Parallel Seq Scan на табелата event_attribute — скенира ~26 милиони редици при барање картони, и Parallel Seq Scan на табелата event — скенира ~12 милиони редици при барање голови.

Додаваме индекс на Event_attribute(attribute_value, event_id) where attribute_name = 'shot.outcome' за да се забрза филтрирањето на голови:
CREATE INDEX idx_event_attr_shot ON public.event_attribute USING btree (attribute_value, event_id) WHERE ((attribute_name)::text = 'shot.outcome'::text);

Додаваме индекс на Event_attribute(attribute_value, event_id) where attribute_name IN ('foul_committed.card', 'bad_behaviour.card') за да се елиминира Seq Scan при броење картони:
CREATE INDEX idx_event_attr_cards ON public.event_attribute USING btree (attribute_value, event_id) WHERE ((attribute_name)::text = ANY ('{foul_committed.card,bad_behaviour.card}'));

Додаваме индекс на Event(event_type_id, match_id, team_id) за да се забрза спојувањето на Event табелата:
CREATE INDEX h2h_idx ON public.event USING btree (event_type_id, match_id, team_id);

По додавањето на индексите времето се намалува на 373ms што е прифатливо (~23x побрзо).

План на евалуација по индексите:

Иницијално време на инсерт (пред индекси):

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

Иницијално време на упдате (пред индекси):

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

View 5. vw_team_season_table

Филтер кој ќе се користи за vw_team_season_table ќе биде според id на сезоната и дивизијата. Случај на употреба е преглед на табела за лига — победи, порази, голови и бодови за секој тим во сезона.

Иницијалното време на извршување е 6s 250ms. Времето не е прифатливо.

Овој поглед внатрешно го повикува vw_match_summary преку CTE match_results, па ги наследува истите проблеми — Parallel Seq Scan на event и event_attribute табелите. Се реискористуваат истите индекси додадени за vw_match_summary: idx_event_attr_shot за голови, idx_event_attr_cards за картони и h2h_idx за event табелата. Бидејќи vw_team_season_table се базира на vw_match_summary, нема потреба од додатни индекси.

По реискористување на индексите од првиот поглед времето се намалува на 164ms што е прифатливо (~38x побрзо).

План на евалуација по оптимизациите:

View 6. vw_player_season_stats

Филтер кој ќе се користи за vw_player_season_stats ќе биде според id на играчот и сезоната. Случај на употреба е преглед на статистики за играч во сезона — настапи, голови, асистенции, картони и проценка на изиграни минути.

Иницијалното време на извршување е 29s. Времето не е прифатливо.

Од планот на евалуација гледаме дека најскапите операции се Parallel Seq Scan на event табелата за Pass и Shot — скенира ~12 милиони редици × 5 паралелни работници, Parallel Seq Scan на event_attribute × 2 пати за жолти и црвени картони, и Seq Scan на lineup табелата со 131,707 редици без индекс со external merge sort на диск (2968kB).

Се реискористуваат индексите idx_event_attr_shot, idx_event_attr_cards и h2h_idx од претходните погледи. Дополнително се додава индекс на Event_attribute(attribute_value, event_id) where attribute_name = 'pass.goal_assist' за да го замениме Seq Scan за асистенции со Index Only Scan:
CREATE INDEX idx_event_attr_assist ON public.event_attribute USING btree (attribute_value, event_id) WHERE ((attribute_name)::text = 'pass.goal_assist'::text);

Додаваме индекс на Lineup(player_id, match_id, team_id, is_starter) за да го елиминираме external merge sort на диск при групирање на настапи по играч и сезона:
CREATE INDEX idx_lineup_player_match ON public.lineup USING btree (player_id, match_id, team_id, is_starter);

По реискористувањето на постоечките индекси и додавањето на новите, времето се намалува на 324ms што е прифатливо (~89x побрзо).

План на евалуација по додавање индекси:

Иницијално време на инсерт (пред индекси):

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

Иницијално време на упдате (пред индекси):

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