Changes between Version 1 and Version 2 of QueryOptimization

Timestamp:

05/03/26 18:12:30 (21 hours ago)

Author:

231035

Comment:

--

QueryOptimization

@@ -8 +8 @@
 5s is not an acceptable time so we add indexes to try and optimize it
 From the picture we can see that the longest is from executing Full Scans
+== View 2 - vw_payment_status
+== View 3 - vw_property_summary
+== View 4 - vw_host_performance
+== View 5 - vw_guest_activity
+== View 6 - vw_revenue_by_property
+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.
+{{{
+[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
+[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)
+}}}
+== View 7 - vw_review_summary
+Without optimization
+{{{
+[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
+[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)
+}}}
+* Can't optimze this query because it needs to get all the reviews to calculate the average rating.
+== View 8 - vw_room_details
+Without optimization
+{{{
+[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
+[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)
+}}}
+With adding index on listing_types we get
+{{{
+[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
+[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)
+}}}
+Final
+Specifying what do we need and not with just *, we optimze the query plus I added an index to rooms;
+{{{
+CREATE INDEX idx_rooms_active_lowprice_capacity
+ON rooms (price_per_night, capacity, extra_capacity)
+WHERE status = 'ACTIVE';
+}}}
+{{{
+[2026-05-03 17:11:49] postgres.public> select
+                                           room_name,
+                                           room_status,
+                                           capacity,
+                                           extra_capacity,
+                                           max_capacity,
+                                           price_per_night,
+                                           extra_guest_price,
+                                           room_description,
+                                           room_type,
+                                           property_title,
+                                           property_status,
+                                           property_base_price,
+                                           listing_type,
+                                           street,
+                                           country_name,
+                                           host_full_name,
+                                           amenity_count
+                                           from vw_room_details where max_capacity> 4 and room_status='ACTIVE' and price_per_night<50
+[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)
+}}}
+This is acceptable.
+== View 9 - vw_availability_windows
+Without optimization
+{{{
+[2026-05-03 16:54:28] postgres.public> SELECT
+                                           availability_window_id,
+                                           room_id,
+                                           room_name,
+                                           property_id,
+                                           available_date_range,
+                                           price_per_night,
+                                           room_status
+                                       FROM vw_availability_windows
+                                       WHERE available_date_range && daterange(DATE '2026-05-01', DATE '2026-05-10', '[]')
+                                         AND room_status = 'ACTIVE' and price_per_night<50
+[2026-05-03 16:54:30] 500 rows retrieved starting from 1 in 1 s 382 ms (execution: 800 ms, fetching: 582 ms)
+}}}
+* This is an acceptable time so we don't need to optimize this query.