Changes between Version 13 and Version 14 of QueryOptimization


Ignore:
Timestamp:
05/26/26 23:03:56 (13 hours ago)
Author:
231003
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

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