Changes between Version 3 and Version 4 of QueryOptimization


Ignore:
Timestamp:
05/15/26 17:54:08 (11 days ago)
Author:
231085
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v3 v4  
    11== 1. Анализа на view vw_booking_details ==
    22
    3 Доколку сакаме да ги прикажеме сите комплетирани резервации со детали за гостин, хост, соба и локација, извршуваме:
     3Погледот vw_booking_overview се користи за анализа на резервации според нивниот статус, со цел добивање комплетен преглед на сите поврзани информации за секоја резервација (гостин, соба, имот и домаќин).
     4Во оваа операција се користи филтрирање booking_status = ‘COMPLETED’ за да се извлечат само завршените резервации.
     5Иницијалното време за извршување на погледот е 23sec
     6
     7[[Image("booking query.png")]]
     8
     9Ова не е прифатливо време за апликацијата па затоа пристапуваме кон индексирање.
     10
     11[[Image("booking analysis.png")]]
     12
     13Со креирање на индекс
    414
    515{{{
    6 SELECT *
    7 FROM vw_booking_details
    8 LIMIT 100;
     16create index idx_guests_user_id on guests(user_id);
    917}}}
    1018
    11 Време потребно за пребарување:
     19Се добива подобрување:
    1220
    13 {{{
    14 ~18 sec
    15 }}}
     21[[Image("index booking query.png")]]
    1622
    17 Најбавна операција е sequential scan на bookings табелата со филтер:
    18 
    19 {{{
    20 booking_status = 'COMPLETED'
    21 }}}
    22 
    23 Со цел да се избегне sequential scan, додадовме partial index:
    24 
    25 {{{
    26 CREATE INDEX CONCURRENTLY idx_bookings_completed
    27 ON bookings (guest_id, room_id)
    28 WHERE booking_status = 'COMPLETED';
    29 }}}
    30 
    31 Време на извршување по додавање на индекс:
    32 
    33 {{{
    34 ~18 sec -> ~11-12 sec
    35 }}}
    36 
    37 Со индексот добивме значително подобрување бидејќи PostgreSQL користи index scan наместо целосно пребарување на bookings.
    3823
    3924== 2. Анализа на view vw_guest_activity ==
    4025
    41 Доколку сакаме да ги прикажеме најактивните гости според број на комплетирани резервации, извршуваме:
     26Погледот vw_guest_activity се користи за анализа на активноста на гостите во системот, со цел добивање комплетен преглед на нивното однесување и интеракции.
     27Доколку сакаме да ги прикажеме најактивните гости според број на комплетирани резервации користиме филтер completed_bookings > 3
    4228
    43 {{{
    44 SELECT *
    45 FROM vw_guest_activity
    46 WHERE completed_bookings > 3;
    47 }}}
     29Времето за извршување: 9 минути
    4830
    49 Време потребно за пребарување:
     31[[Image("guest_activity query.png")]]
    5032
    51 {{{
    52 ~276 sec
    53 }}}
     33За оптимизација се користи Materialized View со цел да се избегнат повторливи и скапи JOIN операции со табелите bookings, reviews и favorite_listings, како и тешки агрегатни пресметки (COUNT, SUM, AVG) и групирање на големи сетови податоци. Со претходно пресметување и физичко складирање на резултатите се намалува времето на извршување и се подобрува перформансата при анализа на активноста на гостите.
    5434
    55 Главниот проблем е огромен број на GROUP BY, DISTINCT и сортирања врз милиони редови.
     35Времето потребно сега е: 213ms
    5636
    57 Нема дополнителен индекс што значително би ги подобрил перформансите на овој view.
     37[[Image("guest_activity opt.png")]]
     38
    5839
    5940== 3. Анализа на view vw_revenue_by_property ==
    6041
    61 Доколку сакаме да ја анализираме заработката по сместување, извршуваме:
     42Овој поглед служи за анализа на приход и перформанси по недвижнина (property). Ги комбинира податоците за резервации, плаќања, локација, тип на оглас и рецензии, и дава агрегирани метрики како вкупен приход, број на резервации и просечен рејтинг.
     43Ако сакаме да видиме заработката по сместување, извршуваме со филтер total_revenue>10 000
     44
     45Време на извршување: 5 минути
     46
     47[[Image("revenue_by_property query.png")]]
     48
     49За оптимизација се користи Materialized View со цел да се избегнат скапи JOIN операции со табелите bookings и payments, како и тешки агрегатни пресметки (SUM, COUNT, AVG) и групирање над големи сетови податоци. Со претходно пресметување и физичко складирање на резултатите значително се намалува времето на извршување и се подобрува перформансата при анализа на приходите и статистиките по недвижност.
     50Новото време на извршување: 510ms
     51
     52[[Image("revenue by property opt.png")]]
     53
     54== 4.  Aнализа на vw_payment_status ==
     55
     56Овој поглед служи за анализа на статусот на плаќањата за секоја резервација. Ги комбинира податоците од резервации, плаќања, методи на плаќање, гости, хостови и недвижнини, со цел да даде целосен преглед на секоја трансакција и нејзината финансиска состојба.
     57Ако сакаме да видиме само успешно платени резервации, користиме филтер payment_status = ‘PAID’
     58Време на извршување: 16 минути
     59
     60[[Image("payment_status query.png")]]
     61
     62Ова не е прифатливо време за апликацијата па затоа пристапуваме кон индексирање
     63
     64[[Image("payment_status analysis.png")]]
     65
     66Се креира индекс
    6267
    6368{{{
    64 SELECT *
    65 FROM vw_revenue_by_property
    66 WHERE total_revenue > 10000;
     69create index idx_payments_status_booking on payments(payment_status, booking_id);
    6770}}}
    6871
    69 Време потребно за пребарување:
     72Со што имаме значително подобрување:
     73
     74[[Image("paymnet status index query.png")]]
     75
     76== 5. Aнализа на vw_host_performance ==
     77
     78Овој поглед служи за анализа на перформансите на хостовите (домаќините) на платформата. Ги комбинира податоците за хостови, корисници, недвижнини, соби, резервации, плаќања и рецензии, и дава агрегирани метрики за секој хост.
     79Доколку сакаме да ги анализираме најуспешните хостови, можеме да извршиме пребарување со филтер avg_rating >= 4.
     80Време на извршување: 2 минути
     81
     82[[Image("host_performance query.png")]]
     83
     84За оптимизација се користи Materialized View со цел да се избегнат скапи JOIN операции помеѓу табелите hosts, users, properties, rooms, bookings, payments и reviews, како и комплексни агрегатни функции како COUNT(DISTINCT), SUM и AVG над големи количини на податоци. Со претходно пресметување и физичко складирање на резултатите значително се намалува времето на извршување и се подобрува перформансата при анализа на успешноста на хостовите.
     85
     86[[Image("host performance opt.png")]]
     87
     88Новото време на извршување: 389 милисекунди
     89
     90
     91== 6.  Aнализа на vw_room_details ==
     92
     93Овој поглед служи за детална анализа на собите во рамки на објектите. Ги комбинира податоците за соби, нивните типови, припадните недвижнини, локацијата, хостот, како и дополнителни информации како капацитет, цени, удобности и резервации.
     94Доколку сакаме да гледаме собите од активни објекти користиме филтер: property_status = ‘ACTIVE’
     95Времето: 11 минути
     96
     97[[Image("room_details query.png")]]
     98
     99Ова не е прифатливо време за апликацијата па затоа пристапуваме кон индексирање.
     100
     101[[Image("room details analysis.png")]]
     102
     103Со креирање на индекс
    70104
    71105{{{
    72 ~93 sec
     106create index idx_bookings_room_status on bookings (room_id, booking_status);
    73107}}}
    74108
    75 Најголем проблем претставуваат:
     109Новото време е 2 минути
    76110
    77 * massive sorting
    78 * group aggregate операции
    79 * join со payments и bookings
     111[[Image("room details index query.png")]]
    80112
    81 Нема дополнителен индекс што значително би помогнал, бидејќи PostgreSQL сепак мора да процесира милиони редови за агрегација.
    82113
    83 == 4. Анализа на view vw_room_details ==
     114== 7. Анализа на vw_review_summary ==
    84115
    85 Доколку сакаме да прикажеме детали за собите, нивната достапност и резервации, извршуваме:
     116Овој поглед служи за анализа на рецензиите и задоволството на гостите за секоја недвижнина. Ги комбинира податоците за рецензии, резервации, гости, хостови и локацијата на сместувањето, со цел да обезбеди детален преглед на оценките и коментарите.
     117Ако сакаме да ги видиме само позитивните рецензии со оцена користиме филтер:
     118rating>=4
     119
     120Време на извршување: 6 минути
     121
     122[[Image(review_summary_quary.png)]]
     123
     124Ова не е прифатливо време за апликацијата па затоа пристапуваме кон индексирање.
     125
     126[[Image(review_summary_analysis.png)]]
     127
     128Со креирање на индекс
    86129
    87130{{{
    88 SELECT *
    89 FROM vw_room_details
    90 WHERE status = 'ACTIVE';
     131create index idx_bookings_booking_id on bookings (booking_id);
    91132}}}
    92133
    93 Време потребно за пребарување:
     134Времето е: 10s
    94135
    95 {{{
    96 ~274 sec
    97 }}}
     136[[Image("review_summary index query.png")]]
    98137
    99 Главниот bottleneck е:
    100138
    101 * massive joins
    102 * огромен број редови од availability_blocks
    103 * aggregate операции
     139== 8.  Aнализа за vw_property_summary ==
    104140
    105 Нема дополнителен индекс што значително би помогнал.
     141Овој поглед служи за анализа и преглед на недвижнините (properties) на платформата. Ги комбинира податоците за сместувањата, нивната локација, хостовите, собите, рецензиите и политиките за откажување, со цел да обезбеди детална статистика за секоја недвижнина.Доколку сакаме да видиме недвижини кои има поголем број рецензии користиме филтер: review_count >15
     142Време: 1 минута
    106143
    107 == 5. Анализа на view vw_review_summary ==
     144[[Image("property_summary quary.png")]]
    108145
    109 Доколку сакаме да ги прикажеме најдобро оценетите сместувања, извршуваме:
     146Оптимизација: Погледот е претворен во Materialized View поради големиот број JOIN операции, агрегатни функции (COUNT, AVG, MIN, MAX) и обработка на милиони редови од табелите reviews и rooms. Со физичко зачувување на пресметаните резултати се избегнува повторно извршување на сложените пресметки при секое пребарување, што значително ги подобрува перформансите и го намалува оптоварувањето на базата при аналитички извештаи.
    110147
    111 {{{
    112 SELECT *
    113 FROM vw_review_summary
    114 WHERE rating >= 4;
    115 }}}
     148Време: 134ms
    116149
    117 Време потребно за пребарување:
     150[[Image("property summary opt.png")]]
    118151
    119 {{{
    120 ~2.3 sec
    121 }}}
    122152
    123 Додадовме индекс:
     153== 9.  Aнализа на vw_avalability_windows ==
    124154
    125 {{{
    126 CREATE INDEX CONCURRENTLY idx_reviews_booking_id
    127 ON reviews (booking_id);
    128 }}}
     155Овој поглед служи за анализа на достапноста на собите во одредени временски периоди. Ги комбинира податоците за временските прозорци на достапност и информациите за собите, со цел да прикаже кога и кои соби се слободни за резервација
     156Доколку сакаме да ги прикажеме сите идни достапни термини за резервација, можеме да извршиме пребарување со филтер available_date >= CURRENT_DATE.
    129157
    130 Време на извршување по додавање на индекс:
     158Време на извршување: 18 секунди
    131159
    132 {{{
    133 ~2.3 sec -> ~2.0 sec
    134 }}}
     160[[Image("avalability window query.png")]]
    135161
    136 Со индексот добивме мало подобрување бидејќи PostgreSQL побрзо ги извршува join операциите помеѓу reviews и bookings.
    137162
    138 == 6. Анализа на availability query ==
     163Со додавање на индекси беше забележано влошување на перформансите, при што времето на извршување се зголеми од 18 на 23 секунди. Поради тоа, заклучено е дека за овој поглед индексите не придонесуваат за подобрување на извршувањето, па затоа не се користени дополнителни индекси.
    139164
    140 Доколку сакаме да ги прикажеме сите идни availability windows за активни соби, извршуваме:
    141165
    142 {{{
    143 SELECT *
    144 FROM availability_windows aw
    145 JOIN rooms r ON aw.room_id = r.room_id
    146 WHERE aw.available_date >= CURRENT_DATE
    147 AND r.status = 'ACTIVE';
    148 }}}
    149 
    150 Време потребно за пребарување:
    151 
    152 {{{
    153 ~17 sec
    154 }}}
    155 
    156 Главниот проблем е sequential scan на availability_windows.
    157 
    158 Додадовме индекс:
    159 
    160 {{{
    161 CREATE INDEX CONCURRENTLY idx_availability_windows_date_room
    162 ON availability_windows (available_date, room_id);
    163 }}}
    164 
    165 Време на извршување по додавање на индекс:
    166 
    167 {{{
    168 ~17 sec -> ~8-9 sec
    169 }}}
    170 
    171 Со индексот добивме значително подобрување бидејќи PostgreSQL избегнува целосно sequential scan на availability_windows.