Changes between Version 2 and Version 3 of QueryOptimization


Ignore:
Timestamp:
05/11/26 21:11:08 (2 weeks ago)
Author:
231116
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v2 v3  
    1 = Query Optimization
    2 == View 1 - vw_booking_overview
    3 Without indexes we get
     1== 1. Анализа на view vw_booking_details ==
     2
     3Доколку сакаме да ги прикажеме сите комплетирани резервации со детали за гостин, хост, соба и локација, извршуваме:
     4
    45{{{
    5 [2026-04-30 18:58:12] postgres.public> select * from vw_booking_overview where booking_status = 'CONFIRMED'
    6 [2026-04-30 18:58:18] 500 rows retrieved starting from 1 in 6 s 361 ms (execution: 5 s 882 ms, fetching: 479 ms)
     6SELECT *
     7FROM vw_booking_details
     8LIMIT 100;
    79}}}
    8 5s is not an acceptable time so we add indexes to try and optimize it
    9 From the picture we can see that the longest is from executing Full Scans
    10 == View 2 - vw_payment_status
    11 == View 3 - vw_property_summary
    12 == View 4 - vw_host_performance
    13 == View 5 - vw_guest_activity
    14 == View 6 - vw_revenue_by_property
    15 Can't optimize the queries because the view needs all the bookings for a property to calculate averaget rating,total booking etc. So even with indexes the time doesn't change.
     10
     11Време потребно за пребарување:
     12
    1613{{{
    17 [2026-05-03 17:32:09] postgres.public> select  * from vw_revenue_by_property where review_count >5 and avg_revenue_per_booking>30000 and cancelled_bookings<3
    18 [2026-05-03 17:35:09] 500 rows retrieved starting from 1 in 3 m 0 s 196 ms (execution: 2 m 59 s 730 ms, fetching: 466 ms)
     14~18 sec
    1915}}}
    20 == View 7 - vw_review_summary
    21 Without optimization
     16
     17Најбавна операција е sequential scan на bookings табелата со филтер:
     18
    2219{{{
    23 [2026-05-03 17:16:41] postgres.public> select * from vw_review_summary where rating <3 and booking_status = 'COMPLETED' and property_avg_rating>3
    24 [2026-05-03 17:17:26] 500 rows retrieved starting from 1 in 45 s 44 ms (execution: 44 s 521 ms, fetching: 523 ms)
     20booking_status = 'COMPLETED'
    2521}}}
    26 * Can't optimze this query because it needs to get all the reviews to calculate the average rating.
    27 == View 8 - vw_room_details
    28 Without optimization
     22
     23Со цел да се избегне sequential scan, додадовме partial index:
     24
    2925{{{
    30 [2026-05-03 17:01:01] postgres.public> select * from vw_room_details where max_capacity> 4 and room_status='ACTIVE' and price_per_night<50
    31 [2026-05-03 17:01:05] 500 rows retrieved starting from 1 in 4 s 716 ms (execution: 4 s 246 ms, fetching: 470 ms)
     26CREATE INDEX CONCURRENTLY idx_bookings_completed
     27ON bookings (guest_id, room_id)
     28WHERE booking_status = 'COMPLETED';
    3229}}}
    33 With adding index on listing_types we get
     30
     31Време на извршување по додавање на индекс:
     32
    3433{{{
    35 [2026-05-03 17:04:05] postgres.public> select * from vw_room_details where max_capacity> 4 and room_status='ACTIVE' and price_per_night<50
    36 [2026-05-03 17:04:07] 500 rows retrieved starting from 1 in 1 s 729 ms (execution: 1 s 336 ms, fetching: 393 ms)
     34~18 sec -> ~11-12 sec
    3735}}}
    38 Final
    39 Specifying what do we need and not with just *, we optimze the query plus I added an index to rooms;
     36
     37Со индексот добивме значително подобрување бидејќи PostgreSQL користи index scan наместо целосно пребарување на bookings.
     38
     39== 2. Анализа на view vw_guest_activity ==
     40
     41Доколку сакаме да ги прикажеме најактивните гости според број на комплетирани резервации, извршуваме:
     42
    4043{{{
    41 CREATE INDEX idx_rooms_active_lowprice_capacity
    42 ON rooms (price_per_night, capacity, extra_capacity)
     44SELECT *
     45FROM vw_guest_activity
     46WHERE completed_bookings > 3;
     47}}}
     48
     49Време потребно за пребарување:
     50
     51{{{
     52~276 sec
     53}}}
     54
     55Главниот проблем е огромен број на GROUP BY, DISTINCT и сортирања врз милиони редови.
     56
     57Нема дополнителен индекс што значително би ги подобрил перформансите на овој view.
     58
     59== 3. Анализа на view vw_revenue_by_property ==
     60
     61Доколку сакаме да ја анализираме заработката по сместување, извршуваме:
     62
     63{{{
     64SELECT *
     65FROM vw_revenue_by_property
     66WHERE total_revenue > 10000;
     67}}}
     68
     69Време потребно за пребарување:
     70
     71{{{
     72~93 sec
     73}}}
     74
     75Најголем проблем претставуваат:
     76
     77* massive sorting
     78* group aggregate операции
     79* join со payments и bookings
     80
     81Нема дополнителен индекс што значително би помогнал, бидејќи PostgreSQL сепак мора да процесира милиони редови за агрегација.
     82
     83== 4. Анализа на view vw_room_details ==
     84
     85Доколку сакаме да прикажеме детали за собите, нивната достапност и резервации, извршуваме:
     86
     87{{{
     88SELECT *
     89FROM vw_room_details
    4390WHERE status = 'ACTIVE';
    4491}}}
     92
     93Време потребно за пребарување:
     94
    4595{{{
    46 [2026-05-03 17:11:49] postgres.public> select
    47                                            room_name,
    48                                            room_status,
    49                                            capacity,
    50                                            extra_capacity,
    51                                            max_capacity,
    52                                            price_per_night,
    53                                            extra_guest_price,
    54                                            room_description,
    55                                            room_type,
    56                                            property_title,
    57                                            property_status,
    58                                            property_base_price,
    59                                            listing_type,
    60                                            street,
    61                                            country_name,
    62                                            host_full_name,
    63                                            amenity_count
    64                                            from vw_room_details where max_capacity> 4 and room_status='ACTIVE' and price_per_night<50
    65 [2026-05-03 17:11:51] 500 rows retrieved starting from 1 in 1 s 596 ms (execution: 1 s 146 ms, fetching: 450 ms)
     96~274 sec
    6697}}}
    67 This is acceptable.
    68 == View 9 - vw_availability_windows
    69 Without optimization
     98
     99Главниот bottleneck е:
     100
     101* massive joins
     102* огромен број редови од availability_blocks
     103* aggregate операции
     104
     105Нема дополнителен индекс што значително би помогнал.
     106
     107== 5. Анализа на view vw_review_summary ==
     108
     109Доколку сакаме да ги прикажеме најдобро оценетите сместувања, извршуваме:
     110
    70111{{{
    71 [2026-05-03 16:54:28] postgres.public> SELECT
    72                                            availability_window_id,
    73                                            room_id,
    74                                            room_name,
    75                                            property_id,
    76                                            available_date_range,
    77                                            price_per_night,
    78                                            room_status
    79                                        FROM vw_availability_windows
    80                                        WHERE available_date_range && daterange(DATE '2026-05-01', DATE '2026-05-10', '[]')
    81                                          AND room_status = 'ACTIVE' and price_per_night<50
    82 [2026-05-03 16:54:30] 500 rows retrieved starting from 1 in 1 s 382 ms (execution: 800 ms, fetching: 582 ms)
     112SELECT *
     113FROM vw_review_summary
     114WHERE rating >= 4;
    83115}}}
    84 * This is an acceptable time so we don't need to optimize this query.
     116
     117Време потребно за пребарување:
     118
     119{{{
     120~2.3 sec
     121}}}
     122
     123Додадовме индекс:
     124
     125{{{
     126CREATE INDEX CONCURRENTLY idx_reviews_booking_id
     127ON reviews (booking_id);
     128}}}
     129
     130Време на извршување по додавање на индекс:
     131
     132{{{
     133~2.3 sec -> ~2.0 sec
     134}}}
     135
     136Со индексот добивме мало подобрување бидејќи PostgreSQL побрзо ги извршува join операциите помеѓу reviews и bookings.
     137
     138== 6. Анализа на availability query ==
     139
     140Доколку сакаме да ги прикажеме сите идни availability windows за активни соби, извршуваме:
     141
     142{{{
     143SELECT *
     144FROM availability_windows aw
     145JOIN rooms r ON aw.room_id = r.room_id
     146WHERE aw.available_date >= CURRENT_DATE
     147AND r.status = 'ACTIVE';
     148}}}
     149
     150Време потребно за пребарување:
     151
     152{{{
     153~17 sec
     154}}}
     155
     156Главниот проблем е sequential scan на availability_windows.
     157
     158Додадовме индекс:
     159
     160{{{
     161CREATE INDEX CONCURRENTLY idx_availability_windows_date_room
     162ON 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.