Changes between Version 2 and Version 3 of QueryOptimization


Ignore:
Timestamp:
05/27/26 00:19:19 (2 weeks ago)
Author:
231108
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v2 v3  
    11= Фаза 3 - Индекси и оптимизација на прашалници =
    22
    3 == View 1: v_companies_by_industry ==
     3Во оваа фаза се анализирани перформансите на 8 погледи (views) креирани врз базата на податоци на проектот Alumni Career Tracker. За секој поглед е измерено иницијалното време на извршување, идентификувани се најбавните операции преку Execution Plan, и каде што е потребно се креирани индекси за оптимизација.
    44
    5  1. Примарен филтер за погледот v_companies_by_industry ќе биде според industry_name.
    6  2. Погледот се користи за преглед на компании групирани по индустрија. Перформансите се важни за течно работење на апликацијата.
    7  3. Иницијалното време за извршување на погледот е 296ms.
    8 {{{
    9 SELECT * FROM v_companies_by_industry WHERE industry_name = 'Information Technology';
    10 }}}
    11  4. Execution планот покажува Seq Scan на Industry табелата, но времето е мало (0.025ms) бидејќи табелата е мала. Нема потреба од индекс. Времето изминато во извршување на операциите INSERT и UPDATE изнесува:
    12 {{{
    13 INSERT INTO Industry(industry_name, sector_category) VALUES('!TestIndustryAAA', '!TestSector');  -- 79ms
    14 UPDATE Industry SET sector_category = '!UpdatedSector' WHERE industry_name = '!TestIndustryAAA';  -- 64ms
    15 }}}
    16  5. Нема потреба од индексирање — сите операции во execution планот се брзи.
    17  6. Времето на извршување на операциите останува исто.
     5Резултатите покажуваат значително подобрување на перформансите кај погледите кои вршат скенирање на големите табели (User, Employment, Studies, Founder). По индексирањето, времињата се намалени од неколку секунди на под 200ms.
    186
    19 ----
    20 
    21 == View 2: v_user_full_career ==
    22 
    23  1. Примарен филтер за погледот v_user_full_career ќе биде според user_id.
    24  2. Погледот се користи за преглед на целосната кариера на корисник (студии и вработување). Перформансите се важни.
    25  3. Иницијалното време за извршување на погледот е 5.356s.
    26 {{{
    27 SELECT * FROM v_user_full_career WHERE user_id = 2676094;
    28 }}}
    29 Ова не е прифатливо време па затоа пристапуваме кон индексирање.
    30  4. Најбавните операции се Parallel Seq Scan на табелите Employment (660ms) и Studies (364ms). Времето изминато во извршување на операциите INSERT и UPDATE пред индексирање изнесува:
    31 {{{
    32 INSERT INTO "User"(first_name, last_name, email) VALUES('Test', 'User', 'testuser111@test.com');  -- 465ms
    33 UPDATE "User" SET city = 'Skopje' WHERE email = 'testuser111@test.com';  -- 345ms
    34 }}}
    35  5. Времето изминато во извршување на query-то со индекси изнесува 55ms, и тоа е прифатливо време.
    36 {{{
    37 CREATE INDEX idx_employment_user_id ON Employment(user_id DESC NULLS LAST);
    38 CREATE INDEX idx_studies_user_id ON Studies(user_id DESC NULLS LAST);
    39 SELECT * FROM v_user_full_career WHERE user_id = 2676094;
    40 }}}
    41 Execution планот по индексирање — сите операции користат Index Scan.
    42  6. Времето изминато во извршување на операциите INSERT и UPDATE по индексирање изнесува:
    43 {{{
    44 INSERT INTO "User"(first_name, last_name, email) VALUES('Test2', 'User2', 'testuser222@test.com');  -- 619ms
    45 UPDATE "User" SET city = 'Kicevo' WHERE email = 'testuser222@test.com';  -- 289ms
    46 }}}
    47 
    48 ----
    49 
    50 == View 3: v_student_academic_profile ==
    51 
    52  1. Примарен филтер за погледот v_student_academic_profile ќе биде според user_id.
    53  2. Погледот се користи за преглед на академскиот профил на студент. Перформансите се важни.
    54  3. Иницијалното време за извршување на погледот е 51ms. Ова е прифатливо време.
    55 {{{
    56 SELECT * FROM v_student_academic_profile WHERE user_id = 2676094;
    57 }}}
    58  4. Execution планот покажува дека сите операции се брзи благодарение на веќе постојните индекси. Времето изминато во извршување на операциите INSERT и UPDATE изнесува:
    59 {{{
    60 INSERT INTO "User"(first_name, last_name, email) VALUES('Test3', 'User3', 'testuser333@test.com');  -- 466ms
    61 UPDATE "User" SET city = 'Ohrid' WHERE email = 'testuser333@test.com';  -- 50ms
    62 }}}
    63  5. Нема потреба да се преуреди прашалникот.
    64  6. Времето на извршување на операциите останува исто.
    65 
    66 ----
    67 
    68 == View 4: v_event_participants ==
    69 
    70  1. Примарен филтер за погледот v_event_participants ќе биде според event_id.
    71  2. Погледот се користи за преглед на учесници на настан. Перформансите се важни.
    72  3. Иницијалното време за извршување на погледот е 318ms.
    73 {{{
    74 SELECT * FROM v_event_participants WHERE event_id = 1;
    75 }}}
    76  4. Execution планот покажува дека сите операции користат Index Scan — нема бавни операции. Времето изминато во извршување на операциите INSERT и UPDATE изнесува:
    77 {{{
    78 INSERT INTO Event(name, date, location, company_id) VALUES('TestEvent111', '2026-01-01', 'Skopje', 1);  -- 1.389s
    79 UPDATE Event SET location = 'Berovo' WHERE name = 'TestEvent111';  -- 389ms
    80 }}}
    81  5. Нема потреба да се преуреди прашалникот.
    82  6. Времето на извршување на операциите останува исто.
    83 
    84 ----
    85 
    86 == View 5: v_faculty_programs ==
    87 
    88  1. Примарен филтер за погледот v_faculty_programs ќе биде според faculty_id.
    89  2. Погледот се користи за преглед на студиски програми по факултет. Перформансите се важни.
    90  3. Иницијалното време за извршување на погледот е 206ms. Ова е прифатливо време.
    91 {{{
    92 SELECT * FROM v_faculty_programs WHERE faculty_id = 1;
    93 }}}
    94  4. Execution планот покажува Seq Scan на мали табели (University, Faculty, StudyProgram) — нема потреба од индекс. Времето изминато во извршување на операциите INSERT и UPDATE изнесува:
    95 {{{
    96 INSERT INTO Faculty(name, university_id) VALUES('TestFaculty111', 1);  -- 310ms
    97 UPDATE Faculty SET address = 'Test Address' WHERE name = 'TestFaculty111';  -- 187ms
    98 }}}
    99  5. Нема потреба да се преуреди прашалникот.
    100  6. Времето на извршување на операциите останува исто.
    101 
    102 ----
    103 
    104 == View 6: v_employment_history ==
    105 
    106  1. Примарен филтер за погледот v_employment_history ќе биде според user_id.
    107  2. Погледот се користи за преглед на историјата на вработување на корисник. Перформансите се важни.
    108  3. Иницијалното време за извршување на погледот е 96ms. Ова е прифатливо време.
    109 {{{
    110 SELECT * FROM v_employment_history WHERE user_id = 2676094;
    111 }}}
    112  4. Execution планот покажува дека сите операции користат Index Scan благодарение на индексите додадени во View 2. Времето изминато во извршување на операциите INSERT и UPDATE изнесува:
    113 {{{
    114 INSERT INTO "User"(first_name, last_name, email) VALUES('Test4', 'User4', 'testuser444@test.com');  -- 207ms
    115 UPDATE "User" SET city = 'Stip' WHERE email = 'testuser444@test.com';  -- 357ms
    116 }}}
    117  5. Нема потреба да се преуреди прашалникот.
    118  6. Времето на извршување на операциите останува исто.
    119 
    120 ----
    121 
    122 == View 7: v_user_skills ==
    123 
    124  1. Примарен филтер за погледот v_user_skills ќе биде според user_id.
    125  2. Погледот се користи за преглед на вештините на корисник. Перформансите се важни.
    126  3. Иницијалното време за извршување на погледот е 103ms. Ова е прифатливо време.
    127 {{{
    128 SELECT * FROM v_user_skills WHERE user_id = 2676094;
    129 }}}
    130  4. Execution планот покажува дека сите операции се брзи — Index Scan и Seq Scan на мала табела Skill. Времето изминато во извршување на операциите INSERT и UPDATE изнесува:
    131 {{{
    132 INSERT INTO Skill(skill_name, type) VALUES('TestSkill111', '!TestType');  -- 799ms
    133 UPDATE Skill SET type = '!UpdatedType' WHERE skill_name = 'TestSkill111';  -- 235ms
    134 }}}
    135  5. Нема потреба да се преуреди прашалникот.
    136  6. Времето на извршување на операциите останува исто.
    137 
    138 ----
    139 
    140 == View 8: v_startups_with_founders ==
    141 
    142  1. Примарен филтер за погледот v_startups_with_founders ќе биде според startup_id.
    143  2. Погледот се користи за преглед на стартапи и нивните основачи. Перформансите се важни.
    144  3. Иницијалното време за извршување на погледот е 1.737s.
    145 {{{
    146 SELECT * FROM v_startups_with_founders WHERE startup_id = 1;
    147 }}}
    148 Ова не е прифатливо време па затоа пристапуваме кон индексирање.
    149  4. Најбавната операција е Parallel Index Scan на User табелата (1909ms) при Merge Join со 464778 редови. Времето изминато во извршување на операциите INSERT и UPDATE пред индексирање изнесува:
    150 {{{
    151 INSERT INTO "User"(first_name, last_name, email) VALUES('Test8', 'User8', 'testuser888x@test.com');  -- 51ms
    152 UPDATE "User" SET city = 'Skopje' WHERE email = 'testuser888x@test.com';  -- 81ms
    153 }}}
    154  5. Времето изминато во извршување на query-то со индекси изнесува 205ms, и тоа е прифатливо време.
    155 {{{
    156 CREATE INDEX idx_founder_startup_id ON Founder(startup_id);
    157 CREATE INDEX idx_founder_user_id ON Founder(user_id);
    158 SELECT * FROM v_startups_with_founders WHERE startup_id = 1;
    159 }}}
    160 Execution планот по индексирање — сите операции користат Index Only Scan.
    161  6. Времето изминато во извршување на операциите INSERT и UPDATE по индексирање изнесува:
    162 {{{
    163 INSERT INTO "User"(first_name, last_name, email) VALUES('Test8b', 'User8b', 'testuser888b@test.com');  -- 51ms
    164 UPDATE "User" SET city = 'Ohrid' WHERE email = 'testuser888b@test.com';  -- 81ms
    165 }}}
    166 
    167 ----
    168 
    169 == View 9: v_students_per_program ==
    170 
    171  1. Примарен филтер за погледот v_students_per_program ќе биде според study_program_id.
    172  2. Погледот се користи за преглед на бројот на студенти по студиска програма. Перформансите се важни.
    173  3. Иницијалното време за извршување на погледот е 2.27s.
    174 {{{
    175 SELECT * FROM v_students_per_program WHERE study_program_id = 1;
    176 }}}
    177 Ова не е прифатливо време па затоа пристапуваме кон индексирање.
    178  4. Најбавната операција е Parallel Seq Scan на Studies табелата (361ms) со 13765 редови. Времето изминато во извршување на операциите INSERT и UPDATE пред индексирање изнесува:
    179 {{{
    180 INSERT INTO !StudyProgram(program_name, degree_type, duration_years, faculty_id) VALUES('TestProgram111', 'bachelor', 4, 1);  -- 443ms
    181 UPDATE !StudyProgram SET duration_years = 3 WHERE program_name = 'TestProgram111';  -- 146ms
    182 }}}
    183  5. Времето изминато во извршување на query-то со индекс изнесува 119ms, и тоа е прифатливо време.
    184 {{{
    185 CREATE INDEX idx_studies_program_user ON Studies(study_program_id, user_id);
    186 SELECT * FROM v_students_per_program WHERE study_program_id = 1;
    187 }}}
    188 Execution планот по индексирање — сите операции користат Index Only Scan.
    189  6. Времето изминато во извршување на операциите INSERT и UPDATE по индексирање изнесува:
    190 {{{
    191 INSERT INTO !StudyProgram(program_name, degree_type, duration_years, faculty_id) VALUES('TestProgram222', 'bachelor', 4, 1);  -- 371ms
    192 UPDATE !StudyProgram SET duration_years = 3 WHERE program_name = 'TestProgram222';  -- 91ms
    193 }}}
     7Детален извештај со execution планови, CREATE INDEX команди и споредба на времиња пред и по индексирањето е достапен во приложениот PDF документ.