= 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.