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

Проект: Public Transport System

View 1: driver_shift_info

Погледот driver_shift_info се користи за приказ на информации на смените на даден возач, заедно со автобусот, распоредот и линијата на која е назначен. Примарен филтер за овој поглед е verification_code на возачот.

Пред оптимизацијата, времето на извршување изнесуваше 7528.971 ms, што е над дозволената граница од 2 секунди.

Во execution plan се забележуваше дека најголем дел од времето се троши при join операциите со Line_assignment, Schedule и Bus_instance.

За оптимизација беа додадени следните индекси:

• idx_line_assignment_schedule_id на Line_assignment(schedule_id)
• idx_line_assignment_chassis_number на Line_assignment(chassis_number)
• idx_schedule_line_id на Schedule(line_id)

По додавањето на индексите и повторното извршување на ANALYZE, времето на извршување се намали на 147.643 ms. Ова време е под 2 секунди и е прифатливо за апликацијата.

---

View 2: line_info

Погледот line_info се користи за приказ на активна автобуска линија, заедно со почетната и крајната станица. Примарен филтер за овој поглед е line_number.

Иницијалното време на извршување изнесуваше 0.322 ms, што е значително под дозволената граница од 2 секунди.

Во execution plan се забележува Seq Scan на табелата Line, но тоа не претставува проблем бидејќи табелата има мал број записи. PostgreSQL проценува дека секвенцијалното читање е поефикасно од користење индекс.

Поради добрите перформанси не беше потребно дополнително индексирање за овој поглед.

---

View 3: payments_info

Погледот payments_info се користи за приказ на плаќања според статус и тип на плаќање. Примарен филтер за овој поглед е payment_status = Completed.

При извршувањето беше забележано дека query-то враќа голем број редови, па времето на извршување зависи не само од пребарувањето, туку и од количината на податоци што треба да се прикажат.

Во execution plan не се забележаа критични проблеми со join операциите или пребарувањето. Дополнителните индекси што беа тестирани не дадоа стабилно подобрување на перформансите.

При повторени тестирања времето на извршување достигна 1487 ms, што е значително под дозволената граница од 2 секунди. Поради тоа беше одлучено да не се додаваат дополнителни индекси за овој поглед.

---

View 4: line_stations

Погледот line_stations се користи за приказ на сите станици низ кои поминува дадена автобуска линија.

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

Примарен филтер за овој поглед е line_number.

Времето на извршување изнесуваше 0.590 ms, што е значително под дозволената граница од 2 секунди.

Поради добрите перформанси не беше потребно дополнително индексирање за овој поглед.

Дополнително беа тестирани INSERT и UPDATE операции врз табелите Station и Position, бидејќи line_stations е обичен view и не складира податоци самостојно.

• INSERT тестот симулираше додавање нова станица и нејзино поврзување со линија преку Position. Времето на извршување изнесуваше 1733.457 ms;
• UPDATE тестот симулираше промена на адреса на станица која припаѓа на дадена линија. Времето на извршување изнесуваше 421.400 ms;

Сите измерени времиња се под 2 секунди, па овој поглед е прифатлив за апликацијата.

---

View 5: station_info

Погледот station_info се користи за приказ на информации за дадена станица и активните линии што минуваат низ неа.

Примарен филтер за овој поглед е station_name.

Времето на извршување изнесуваше 145.144 ms, што е под дозволената граница од 2 секунди.

Во execution plan се гледа користење на постоечки индекси, како station_station_name_key, position_pkey, line_pkey и idx_schedule_line_id.

Поради добрите перформанси не беше потребно дополнително индексирање за овој поглед.

Дополнително беа тестирани INSERT и UPDATE операции врз основните табели Station и Position, бидејќи station_info е обичен view и не складира податоци самостојно.

• INSERT тестот симулира додавање нова станица и нејзино поврзување со линија преку Position. Времето на извршување изнесуваше 807.786 ms;
• UPDATE тестот симулира промена на адреса на постоечка станица. Времето на извршување изнесуваше 19810.098 ms;

Иако UPDATE операцијата имаше повисоко време на извршување, execution plan покажува дека PostgreSQL го користи индексот station_pkey.

---

View 6: admin_info

Погледот admin_info се користи за приказ на активности поврзани со даден администратор.

Примарен филтер за овој поглед е verification_code на администраторот.

Пред оптимизацијата, времето на извршување изнесуваше 6881.689 ms, што е над дозволената граница од 2 секунди.

Во execution plan се забележуваше дека најголем дел од времето се троши при join операциите со табелата Line_assignment.

По додавањето на индексите и повторното извршување на ANALYZE, времето на извршување се намали на 1450.234 ms.

Дополнително беа измерени INSERT и UPDATE операции врз основните табели што го формираат погледот.

• INSERT операцијата пред оптимизацијата изнесуваше околу 10 секунди, а по додавањето на индексите се намали на 307.9 ms;
• UPDATE операцијата пред оптимизацијата изнесуваше околу 7 секунди, а по оптимизацијата се намали на 41.553 ms;

---

View 7: ticket_info

Погледот ticket_info се користи за приказ на информации за билети според тип на корисник.

Примарен филтер за овој поглед е type = Student.

Тестирањето беше направено со LIMIT 100, бидејќи во реална апликација резултатите би се прикажувале странично.

Времето на извршување на SELECT query-то изнесуваше 7.030 ms, што е далеку под дозволената граница од 2 секунди.

Дополнително беа измерени INSERT и UPDATE операции врз основните табели Ticket и Single_ticket.

• INSERT тестот симулираше креирање нов билет за корисник од тип Student. Времето на извршување изнесуваше 172.295 ms;
• UPDATE тестот симулираше ажурирање на запис во Single_ticket. Времето на извршување изнесуваше 0.260 ms;

---

View 8: bus_service_history

Погледот bus_service_history се користи за приказ на сервисната историја на даден автобус.

Примарен филтер за овој поглед е registration_number.

Времето на извршување изнесуваше 1.371 ms, што е далеку под дозволената граница од 2 секунди.

Во execution plan се гледа дека PostgreSQL го користи постоечкиот индекс bus_instance_registration_number_key.

Дополнително беа измерени INSERT и UPDATE операции врз табелата Maintenance.

• INSERT тестот симулираше додавање нов сервисен запис за автобус. Времето на извршување изнесуваше 1.931 ms;
• UPDATE тестот симулираше промена на описот на сервисен запис. Времето на извршување изнесуваше 0.241 ms;

---

View 9: monthly_ticket_sales

Погледот monthly_ticket_sales се користи за месечен преглед на продадени билети и вкупен приход според начин на плаќање.

Примарен филтер за овој поглед е payment_month = 2025-02-01.

Овој поглед е најсложен од анализираните погледи, бидејќи врши агрегација врз повеќе големи табели.

Првичното време на извршување изнесуваше 199499.209 ms, што е далеку над дозволената граница од 2 секунди.

Во execution plan се забележува Parallel Seq Scan на табелата Payment и голем број join операции.

Поради тоа беше креиран materialized view monthly_ticket_sales_mv.

По креирањето на materialized view, времето на извршување се намали на 0.038 ms.

Дополнително беа измерени INSERT и UPDATE операции врз основните табели Payment и Customer_Payment_Ticket.

• INSERT тестот симулираше додавање ново успешно плаќање и негово поврзување со постоечки билет. Времето на извршување изнесуваше 359.437 ms;
• UPDATE тестот симулираше промена на статус на плаќање во Completed. Времето на извршување изнесуваше 136.430 ms;

Сите измерени времиња се под 2 секунди, па овој поглед е прифатлив за апликацијата.