Changes between Version 6 and Version 7 of QueryOptimization
- Timestamp:
- 05/21/26 12:44:55 (5 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
QueryOptimization
v6 v7 1 = Фаза 3 – Оптимизација на прашалници и погледи1 = Фаза 3: Оптимизација на прашалници и погледи = 2 2 3 == Документ за оптимизација3 == Проект: Public Transport System == 4 4 5 [[html(<a href="https://develop.finki.ukim.mk/projects/public_transport_system/attachment/wiki/Optimization/optimizacija.pdf">optimizacija.pdf</a>)]] 5 '''Членови:''' 6 6 7 == Оптимизирани Views 7 * Дамјан Илиевски 231003 8 * Викторија Георгиевска 231006 9 * Стефан Марковиќ 231196 8 10 9 === driver_shift_info 11 --- 10 12 11 Погледот се користи за приказ на информации за смените на даден возач, заедно со автобусот, распоредот и линијата. 13 == View 1: driver_shift_info == 12 14 13 * Примарен филтер: verification_code 14 * Почетно време: 7528.971 ms 15 * Оптимизирано време: 147.643 ms 15 Погледот '''driver_shift_info''' се користи за приказ на информации на смените на даден возач, заедно со автобусот, распоредот и линијата на која е назначен. 16 Примарен филтер за овој поглед е '''verification_code''' на возачот. 16 17 17 Додадени индекси: 18 Пред оптимизацијата, времето на извршување изнесуваше '''7528.971 ms''', што е над дозволената граница од 2 секунди. 18 19 19 * idx_line_assignment_schedule_id 20 * idx_line_assignment_chassis_number 21 * idx_schedule_line_id 20 Во execution plan се забележуваше дека најголем дел од времето се троши при join операциите со ''Line_assignment'', ''Schedule'' и ''Bus_instance''. 22 21 23 === line_info 22 За оптимизација беа додадени следните индекси: 24 23 25 Погледот се користи за приказ на активна автобуска линија заедно со почетна и крајна станица. 24 * ''idx_line_assignment_schedule_id'' на ''Line_assignment(schedule_id)'' 25 * ''idx_line_assignment_chassis_number'' на ''Line_assignment(chassis_number)'' 26 * ''idx_schedule_line_id'' на ''Schedule(line_id)'' 26 27 27 * Примарен филтер: line_number 28 * Време на извршување: 0.322 ms 28 [[Image(driver_shift_before.png, 800px)]] 29 29 30 Не беше потребна дополнителна оптимизација. 30 [[Image(driver_shift_after.png, 800px)]] 31 31 32 === payments_info 32 По додавањето на индексите и повторното извршување на ANALYZE, времето на извршување се намали на '''147.643 ms'''. Ова време е под 2 секунди и е прифатливо за апликацијата. 33 33 34 Погледот се користи за приказ на плаќања според статус и тип на плаќање. 34 --- 35 35 36 * Примарен филтер: payment_status = 'Completed' 37 * Време на извршување: 1487 ms 36 == View 2: line_info == 38 37 39 Не беше потребно дополнително индексирање.38 Погледот '''line_info''' се користи за приказ на активна автобуска линија, заедно со почетната и крајната станица. Примарен филтер за овој поглед е ''line_number''. 40 39 41 === line_stations 40 Иницијалното време на извршување изнесуваше '''0.322 ms''', што е значително под дозволената граница од 2 секунди. 42 41 43 Погледот се користи за приказ на сите станици низ кои поминува дадена линија.42 Во execution plan се забележува Seq Scan на табелата ''Line'', но тоа не претставува проблем бидејќи табелата има мал број записи. PostgreSQL проценува дека секвенцијалното читање е поефикасно од користење индекс. 44 43 45 * Примарен филтер: line_number 46 * SELECT време: 0.590 ms 47 * INSERT време: 1733.457 ms 48 * UPDATE време: 421.400 ms 44 [[Image(line_info_plan.png, 800px)]] 49 45 50 === station_info 46 Поради добрите перформанси не беше потребно дополнително индексирање за овој поглед. 51 47 52 Погледот се користи за приказ на информации за станица и активните линии. 48 --- 53 49 54 * Примарен филтер: station_name 55 * SELECT време: 145.144 ms 56 * INSERT време: 807.786 ms 57 * UPDATE време: 19810.098 ms 50 == View 3: payments_info == 58 51 59 Execution plan покажува користење на постоечки индекси.52 Погледот '''payments_info''' се користи за приказ на плаќања според статус и тип на плаќање. Примарен филтер за овој поглед е ''payment_status = Completed''. 60 53 61 === admin_info 54 При извршувањето беше забележано дека query-то враќа голем број редови, па времето на извршување зависи не само од пребарувањето, туку и од количината на податоци што треба да се прикажат. 62 55 63 Погледот се користи за приказ на активности поврзани со администратор.56 Во execution plan не се забележаа критични проблеми со join операциите или пребарувањето. Дополнителните индекси што беа тестирани не дадоа стабилно подобрување на перформансите. 64 57 65 * Примарен филтер: verification_code 66 * Почетно време: 6881.689 ms 67 * Оптимизирано време: 1450.234 ms 58 [[Image(payments_info_plan.png, 800px)]] 68 59 69 Дополнителни тестирања: 60 При повторени тестирања времето на извршување достигна '''1487 ms''', што е значително под дозволената граница од 2 секунди. Поради тоа беше одлучено да не се додаваат дополнителни индекси за овој поглед. 70 61 71 * INSERT: 307.9 ms 72 * UPDATE: 41.553 ms 62 --- 73 63 74 == = ticket_info64 == View 4: line_stations == 75 65 76 Погледот се користи за приказ на информации за билети според тип на корисник.66 Погледот '''line_stations''' се користи за приказ на сите станици низ кои поминува дадена автобуска линија. 77 67 78 * Примарен филтер: type = 'Student' 79 * SELECT време: 7.030 ms 80 * INSERT време: 172.295 ms 81 * UPDATE време: 0.260 ms 68 Резултатите се сортираат според ''num_of_station'', бидејќи оваа колона го претставува редоследот на станиците во рамки на линијата. 82 69 83 === bus_service_history 70 Примарен филтер за овој поглед е ''line_number''. 84 71 85 Погледот се користи за приказ на сервисна историја на автобус.72 Времето на извршување изнесуваше '''0.590 ms''', што е значително под дозволената граница од 2 секунди. 86 73 87 * Примарен филтер: registration_number 88 * SELECT време: 1.371 ms 89 * INSERT време: 1.931 ms 90 * UPDATE време: 0.241 ms 74 [[Image(line_stations_plan.png, 800px)]] 91 75 92 === monthly_ticket_sales 76 Поради добрите перформанси не беше потребно дополнително индексирање за овој поглед. 93 77 94 Погледот се користи за месечен преглед на продадени билети и приходи.78 Дополнително беа тестирани INSERT и UPDATE операции врз табелите ''Station'' и ''Position'', бидејќи ''line_stations'' е обичен view и не складира податоци самостојно. 95 79 96 * Примарен филтер: payment_month = '2025-02-01' 97 * Почетно време: 199499.209 ms 98 * Оптимизирано време: 0.038 ms 80 * INSERT тестот симулираше додавање нова станица и нејзино поврзување со линија преку Position. Времето на извршување изнесуваше '''1733.457 ms'''; 81 * UPDATE тестот симулираше промена на адреса на станица која припаѓа на дадена линија. Времето на извршување изнесуваше '''421.400 ms'''; 99 82 100 Користен е materialized view: 83 Сите измерени времиња се под 2 секунди, па овој поглед е прифатлив за апликацијата. 101 84 102 * monthly_ticket_sales_mv 85 --- 103 86 104 Дополнителни тестирања: 87 == View 5: station_info == 105 88 106 * INSERT: 359.437 ms 107 * UPDATE: 136.430 ms 89 Погледот '''station_info''' се користи за приказ на информации за дадена станица и активните линии што минуваат низ неа. 90 91 Примарен филтер за овој поглед е ''station_name''. 92 93 Времето на извршување изнесуваше '''145.144 ms''', што е под дозволената граница од 2 секунди. 94 95 Во execution plan се гледа користење на постоечки индекси, како ''station_station_name_key'', ''position_pkey'', ''line_pkey'' и ''idx_schedule_line_id''. 96 97 [[Image(station_info_plan.png, 800px)]] 98 99 Поради добрите перформанси не беше потребно дополнително индексирање за овој поглед. 100 101 Дополнително беа тестирани INSERT и UPDATE операции врз основните табели ''Station'' и ''Position'', бидејќи ''station_info'' е обичен view и не складира податоци самостојно. 102 103 * INSERT тестот симулира додавање нова станица и нејзино поврзување со линија преку Position. Времето на извршување изнесуваше '''807.786 ms'''; 104 * UPDATE тестот симулира промена на адреса на постоечка станица. Времето на извршување изнесуваше '''19810.098 ms'''; 105 106 Иако UPDATE операцијата имаше повисоко време на извршување, execution plan покажува дека PostgreSQL го користи индексот ''station_pkey''. 107 108 --- 109 110 == View 6: admin_info == 111 112 Погледот '''admin_info''' се користи за приказ на активности поврзани со даден администратор. 113 114 Примарен филтер за овој поглед е ''verification_code'' на администраторот. 115 116 Пред оптимизацијата, времето на извршување изнесуваше '''6881.689 ms''', што е над дозволената граница од 2 секунди. 117 118 Во execution plan се забележуваше дека најголем дел од времето се троши при join операциите со табелата ''Line_assignment''. 119 120 [[Image(admin_info_before.png, 800px)]] 121 122 [[Image(admin_info_after.png, 800px)]] 123 124 По додавањето на индексите и повторното извршување на ANALYZE, времето на извршување се намали на '''1450.234 ms'''. 125 126 Дополнително беа измерени INSERT и UPDATE операции врз основните табели што го формираат погледот. 127 128 * INSERT операцијата пред оптимизацијата изнесуваше околу 10 секунди, а по додавањето на индексите се намали на '''307.9 ms'''; 129 * UPDATE операцијата пред оптимизацијата изнесуваше околу 7 секунди, а по оптимизацијата се намали на '''41.553 ms'''; 130 131 --- 132 133 == View 7: ticket_info == 134 135 Погледот '''ticket_info''' се користи за приказ на информации за билети според тип на корисник. 136 137 Примарен филтер за овој поглед е ''type = Student''. 138 139 Тестирањето беше направено со LIMIT 100, бидејќи во реална апликација резултатите би се прикажувале странично. 140 141 Времето на извршување на SELECT query-то изнесуваше '''7.030 ms''', што е далеку под дозволената граница од 2 секунди. 142 143 [[Image(ticket_info_plan.png, 800px)]] 144 145 Дополнително беа измерени INSERT и UPDATE операции врз основните табели ''Ticket'' и ''Single_ticket''. 146 147 * INSERT тестот симулираше креирање нов билет за корисник од тип Student. Времето на извршување изнесуваше '''172.295 ms'''; 148 * UPDATE тестот симулираше ажурирање на запис во ''Single_ticket''. Времето на извршување изнесуваше '''0.260 ms'''; 149 150 --- 151 152 == View 8: bus_service_history == 153 154 Погледот '''bus_service_history''' се користи за приказ на сервисната историја на даден автобус. 155 156 Примарен филтер за овој поглед е ''registration_number''. 157 158 Времето на извршување изнесуваше '''1.371 ms''', што е далеку под дозволената граница од 2 секунди. 159 160 Во execution plan се гледа дека PostgreSQL го користи постоечкиот индекс ''bus_instance_registration_number_key''. 161 162 [[Image(bus_service_history_plan.png, 800px)]] 163 164 Дополнително беа измерени INSERT и UPDATE операции врз табелата ''Maintenance''. 165 166 * INSERT тестот симулираше додавање нов сервисен запис за автобус. Времето на извршување изнесуваше '''1.931 ms'''; 167 * UPDATE тестот симулираше промена на описот на сервисен запис. Времето на извршување изнесуваше '''0.241 ms'''; 168 169 --- 170 171 == View 9: monthly_ticket_sales == 172 173 Погледот '''monthly_ticket_sales''' се користи за месечен преглед на продадени билети и вкупен приход според начин на плаќање. 174 175 Примарен филтер за овој поглед е ''payment_month = 2025-02-01''. 176 177 Овој поглед е најсложен од анализираните погледи, бидејќи врши агрегација врз повеќе големи табели. 178 179 Првичното време на извршување изнесуваше '''199499.209 ms''', што е далеку над дозволената граница од 2 секунди. 180 181 Во execution plan се забележува Parallel Seq Scan на табелата ''Payment'' и голем број join операции. 182 183 Поради тоа беше креиран materialized view ''monthly_ticket_sales_mv''. 184 185 [[Image(monthly_ticket_sales_before.png, 800px)]] 186 187 [[Image(monthly_ticket_sales_after.png, 800px)]] 188 189 По креирањето на materialized view, времето на извршување се намали на '''0.038 ms'''. 190 191 Дополнително беа измерени INSERT и UPDATE операции врз основните табели ''Payment'' и ''Customer_Payment_Ticket''. 192 193 * INSERT тестот симулираше додавање ново успешно плаќање и негово поврзување со постоечки билет. Времето на извршување изнесуваше '''359.437 ms'''; 194 * UPDATE тестот симулираше промена на статус на плаќање во Completed. Времето на извршување изнесуваше '''136.430 ms'''; 195 196 Сите измерени времиња се под 2 секунди, па овој поглед е прифатлив за апликацијата.
