Query Optimization

View 1 - vw_booking_overview

Without indexes we get

[2026-04-30 18:58:12] postgres.public> select * from vw_booking_overview where booking_status = 'CONFIRMED'
[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)

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.