= Other topics (Performance, Security, …)

== Performance ==

=== 1. Adding 1000000 mock rows to 'bookings' table ===

{{{
#!sql
ALTER TABLE project.bookings DISABLE TRIGGER trg_booking_validation;

INSERT INTO project.bookings (status, date_from, date_to, address, owner_id, sitter_id)
SELECT
    (ARRAY['Pending', 'Confirmed', 'Completed', 'Canceled', 'Expired', 'Reviewed'])[floor(random()*6)+1] AS status,
    t.dt AS date_from,
    t.dt + (random() * INTERVAL '7 days') AS date_to,
    'Mock Address ' || gs AS address,
    o.user_id AS owner_id,
    s.user_id AS sitter_id
FROM generate_series(1, 1000000) gs
JOIN LATERAL (
    SELECT user_id FROM project.pet_owners ORDER BY random() LIMIT 1
) o ON true
JOIN LATERAL (
    SELECT user_id FROM project.pet_sitters ORDER BY random() LIMIT 1
) s ON true
JOIN LATERAL (
    SELECT CURRENT_DATE - (random() * INTERVAL '365 days') AS dt
) t ON true;

ALTER TABLE project.bookings ENABLE TRIGGER trg_booking_validation;
}}}

Benchmark query:
{{{
#!sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM project.bookings 
WHERE sitter_id = (SELECT user_id FROM project.pet_sitters LIMIT 1)
ORDER BY date_from DESC;
}}}

Execution without indexes:

{{{
Gather Merge  (cost=81030.64..195549.38 rows=967764 width=146) (actual time=94.209..106.394 rows=2 loops=1)
  Workers Planned: 3
  Workers Launched: 3
  Buffers: shared hit=22336
  InitPlan 1
    ->  Limit  (cost=0.00..0.02 rows=1 width=90) (actual time=3.367..3.369 rows=1 loops=1)
          Buffers: shared hit=1
          ->  Seq Scan on pet_sitters  (cost=0.00..16.90 rows=690 width=90) (actual time=0.015..0.016 rows=1 loops=1)
                Buffers: shared hit=1
  ->  Sort  (cost=80030.57..80837.04 rows=322588 width=146) (actual time=60.212..60.213 rows=0 loops=4)
        Sort Key: bookings.date_from DESC
        Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=22335
        Worker 0:  Sort Method: quicksort  Memory: 25kB
        Worker 1:  Sort Method: quicksort  Memory: 25kB
        Worker 2:  Sort Method: quicksort  Memory: 25kB
        ->  Parallel Seq Scan on bookings  (cost=0.00..26256.35 rows=322588 width=146) (actual time=37.611..60.082 rows=0 loops=4)
              Filter: ((sitter_id)::text = ((InitPlan 1).col1)::text)
              Rows Removed by Filter: 250005
              Buffers: shared hit=22224
Planning Time: 0.168 ms
JIT:
  Functions: 12
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 1.466 ms (Deform 0.675 ms), Inlining 0.000 ms, Optimization 1.430 ms, Emission 19.219 ms, Total 22.115 ms
Execution Time: 106.834 ms
}}}

'''Average execution time (10 attempts):''' 105.235ms

Next, we add this index:
{{{
#!sql
CREATE INDEX idx_bookings_sitter_date 
ON project.bookings (sitter_id, date_from DESC);
}}}

(Note, for the exact same performance reasons, we also created to optimize the queries when Pet Owners view their own bookings)
{{{
#!sql
CREATE INDEX idx_bookings_owner_date 
ON project.bookings (owner_id, date_from DESC);
}}}

Execution with indexes:

{{{
Index Scan using idx_bookings_sitter_date on bookings  (cost=0.45..72503.52 rows=1000022 width=146) (actual time=0.066..0.068 rows=2 loops=1)
  Index Cond: ((sitter_id)::text = ((InitPlan 1).col1)::text)
  Buffers: shared hit=2 read=3
  InitPlan 1
    ->  Limit  (cost=0.00..0.02 rows=1 width=90) (actual time=0.012..0.013 rows=1 loops=1)
          Buffers: shared hit=1
          ->  Seq Scan on pet_sitters  (cost=0.00..16.90 rows=690 width=90) (actual time=0.011..0.011 rows=1 loops=1)
                Buffers: shared hit=1
Planning:
  Buffers: shared hit=39 read=2
Planning Time: 0.618 ms
Execution Time: 0.092 ms
}}}

'''Average execution time (10 attempts):''' 0.087ms 

Because the execution time has been massively lowered by bypassing the expensive sequential scan and memory sort, we '''keep''' this index.

*(Note: For the exact same performance reasons, we also created `CREATE INDEX idx_bookings_owner_date ON project.bookings (owner_id, date_from DESC);` to optimize the queries when Pet Owners view their own booking history).*


== 2. Calculate average sitter rating ==

Benchmark query:
{{{
#!sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT AVG(r.rating) 
FROM project.reviews r
JOIN project.bookings b ON r.booking_id = b.booking_id
WHERE b.sitter_id = (SELECT user_id FROM project.pet_sitters LIMIT 1);
}}}

Execution without indexes:

{{{
Finalize Aggregate  (cost=33210.02..33210.03 rows=1 width=32) (actual time=121.837..135.038 rows=1 loops=1)
  Buffers: shared hit=25818
  InitPlan 1
    ->  Limit  (cost=0.00..0.02 rows=1 width=90) (actual time=0.014..0.015 rows=1 loops=1)
          Buffers: shared hit=1
          ->  Seq Scan on pet_sitters  (cost=0.00..16.90 rows=690 width=90) (actual time=0.012..0.013 rows=1 loops=1)
                Buffers: shared hit=1
  ->  Gather  (cost=33209.67..33209.98 rows=3 width=32) (actual time=119.802..135.016 rows=4 loops=1)
        Workers Planned: 3
        Workers Launched: 3
        Buffers: shared hit=25818
        ->  Partial Aggregate  (cost=32209.67..32209.68 rows=1 width=32) (actual time=90.870..90.873 rows=1 loops=4)
              Buffers: shared hit=25817
              ->  Parallel Hash Join  (cost=4971.92..32075.06 rows=53846 width=4) (actual time=77.089..90.867 rows=0 loops=4)
                    Hash Cond: ((b.booking_id)::text = (r.booking_id)::text)
                    Buffers: shared hit=25817
                    ->  Parallel Seq Scan on bookings b  (cost=0.00..26256.35 rows=322588 width=37) (actual time=41.349..55.119 rows=0 loops=4)
                          Filter: ((sitter_id)::text = ((InitPlan 1).col1)::text)
                          Rows Removed by Filter: 250005
                          Buffers: shared hit=22224
                    ->  Parallel Hash  (cost=4102.52..4102.52 rows=69552 width=41) (actual time=34.636..34.637 rows=41731 loops=4)
                          Buckets: 262144  Batches: 1  Memory Usage: 15200kB
                          Buffers: shared hit=3407
                          ->  Parallel Seq Scan on reviews r  (cost=0.00..4102.52 rows=69552 width=41) (actual time=0.026..11.172 rows=41731 loops=4)
                                Buffers: shared hit=3407
Planning:
  Buffers: shared hit=16
Planning Time: 0.444 ms
Execution Time: 135.097 ms
}}}

'''Average execution time (10 attempts):''' 131.722ms

We add this index:
{{{
#!sql
CREATE INDEX idx_reviews_booking_id 
ON project.reviews (booking_id);
}}}

Execution with indexes:

{{{
Finalize Aggregate  (cost=33210.02..33210.03 rows=1 width=32) (actual time=125.303..138.277 rows=1 loops=1)
  Buffers: shared hit=25818
  InitPlan 1
    ->  Limit  (cost=0.00..0.02 rows=1 width=90) (actual time=0.013..0.014 rows=1 loops=1)
          Buffers: shared hit=1
          ->  Seq Scan on pet_sitters  (cost=0.00..16.90 rows=690 width=90) (actual time=0.012..0.012 rows=1 loops=1)
                Buffers: shared hit=1
  ->  Gather  (cost=33209.67..33209.98 rows=3 width=32) (actual time=123.351..138.255 rows=4 loops=1)
        Workers Planned: 3
        Workers Launched: 3
        Buffers: shared hit=25818
        ->  Partial Aggregate  (cost=32209.67..32209.68 rows=1 width=32) (actual time=93.069..93.072 rows=1 loops=4)
              Buffers: shared hit=25817
              ->  Parallel Hash Join  (cost=4971.92..32075.06 rows=53846 width=4) (actual time=78.783..93.066 rows=0 loops=4)
                    Hash Cond: ((b.booking_id)::text = (r.booking_id)::text)
                    Buffers: shared hit=25817
                    ->  Parallel Seq Scan on bookings b  (cost=0.00..26256.35 rows=322588 width=37) (actual time=42.860..57.138 rows=0 loops=4)
                          Filter: ((sitter_id)::text = ((InitPlan 1).col1)::text)
                          Rows Removed by Filter: 250005
                          Buffers: shared hit=22224
                    ->  Parallel Hash  (cost=4102.52..4102.52 rows=69552 width=41) (actual time=34.827..34.828 rows=41731 loops=4)
                          Buckets: 262144  Batches: 1  Memory Usage: 15168kB
                          Buffers: shared hit=3407
                          ->  Parallel Seq Scan on reviews r  (cost=0.00..4102.52 rows=69552 width=41) (actual time=0.029..11.437 rows=41731 loops=4)
                                Buffers: shared hit=3407
Planning:
  Buffers: shared hit=16
Planning Time: 0.554 ms
Execution Time: 138.346 ms
}}}

'''Average execution time (10 attempts):''' 138.346ms

There is practically no change in the execution time, and the query planner completely ignored the index because it determined that performing a Parallel Seq Scan and Parallel Hash Join was cheaper than doing individual index lookups on such a large dataset. Because the index is not utilized for this query and only adds complexity during `INSERT` and `UPDATE` operations, we will '''not''' keep this index.

== 3. Pet Sitter Leaderboard, Analytics ==

Benchmark query:
{{{
#!sql
EXPLAIN (ANALYZE, BUFFERS)
WITH params AS (
    SELECT 
        (CURRENT_DATE - INTERVAL '1 year') AS start_date,
        CURRENT_DATE AS end_date
),
sitter_bookings AS (
    SELECT 
        b.sitter_id,
        COUNT(*) AS total_bookings,
        COUNT(*) FILTER (WHERE b.status = 'Completed') AS completed_bookings,
        COUNT(*) FILTER (WHERE b.status = 'Canceled') AS canceled_bookings
    FROM project.bookings b
    JOIN params p ON b.date_from >= p.start_date AND b.date_from <= p.end_date
    GROUP BY b.sitter_id
),
sitter_reviews AS (
    SELECT 
        b.sitter_id,
        AVG(r.rating)::numeric(10,2) AS avg_rating,
        COUNT(r.review_id) AS total_reviews
    FROM project.reviews r
    JOIN project.bookings b ON r.booking_id = b.booking_id
    GROUP BY b.sitter_id
)
SELECT 
    u.username,
    u.first_name,
    u.last_name,
    COALESCE(sb.completed_bookings, 0) AS completed,
    COALESCE(sb.canceled_bookings, 0) AS canceled,
    COALESCE(sr.avg_rating, 0) AS rating,
    (
        COALESCE(sb.completed_bookings, 0) * 5 
        + COALESCE(sr.avg_rating, 0) * 10 
        - COALESCE(sb.canceled_bookings, 0) * 3
    ) AS activity_score,
    DENSE_RANK() OVER (
        ORDER BY (
            COALESCE(sb.completed_bookings, 0) * 5 
            + COALESCE(sr.avg_rating, 0) * 10 
            - COALESCE(sb.canceled_bookings, 0) * 3
        ) DESC
    ) AS leaderboard_rank
FROM project.pet_sitters ps
JOIN project.users u ON ps.user_id = u.user_id
LEFT JOIN sitter_bookings sb ON sb.sitter_id = ps.user_id
LEFT JOIN sitter_reviews sr ON sr.sitter_id = ps.user_id
ORDER BY leaderboard_rank
LIMIT 10;
}}}

Execution without indexes:

{{{
Limit  (cost=67216.49..67216.51 rows=10 width=115) (actual time=403.788..412.560 rows=4 loops=1)
  Buffers: shared hit=22278 read=25633
  ->  Sort  (cost=67216.49..67218.21 rows=690 width=115) (actual time=403.786..412.557 rows=4 loops=1)
        Sort Key: (dense_rank() OVER (?))
        Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=22278 read=25633
        ->  WindowAgg  (cost=67177.44..67201.58 rows=690 width=115) (actual time=403.716..412.514 rows=4 loops=1)
              Buffers: shared hit=22275 read=25633
              ->  Sort  (cost=67177.43..67179.15 rows=690 width=91) (actual time=403.675..412.445 rows=4 loops=1)
                    Sort Key: (((((COALESCE((count(*) FILTER (WHERE ((b.status)::text = 'Completed'::text))), '0'::bigint) * 5))::numeric + (COALESCE(((avg(r.rating))::numeric(10,2)), '0'::numeric) * '10'::numeric)) - ((COALESCE((count(*) FILTER (WHERE ((b.status)::text = 'Canceled'::text))), '0'::bigint) * 3))::numeric)) DESC
                    Sort Method: quicksort  Memory: 25kB
                    Buffers: shared hit=22275 read=25633
                    ->  Hash Join  (cost=67125.00..67144.89 rows=690 width=91) (actual time=403.585..412.387 rows=4 loops=1)
                          Hash Cond: ((ps.user_id)::text = (u.user_id)::text)
                          Buffers: shared hit=22272 read=25633
                          ->  Merge Left Join  (cost=67101.95..67107.94 rows=690 width=122) (actual time=403.503..412.297 rows=4 loops=1)
                                Merge Cond: ((ps.user_id)::text = (b_1.sitter_id)::text)
                                Buffers: shared hit=22272 read=25632
                                ->  Merge Left Join  (cost=34564.07..34567.93 rows=690 width=106) (actual time=212.894..213.031 rows=4 loops=1)
                                      Merge Cond: ((ps.user_id)::text = (b.sitter_id)::text)
                                      Buffers: shared hit=24 read=22225
                                      ->  Sort  (cost=49.44..51.16 rows=690 width=90) (actual time=0.049..0.051 rows=4 loops=1)
                                            Sort Key: ps.user_id
                                            Sort Method: quicksort  Memory: 25kB
                                            Buffers: shared read=1
                                            ->  Seq Scan on pet_sitters ps  (cost=0.00..16.90 rows=690 width=90) (actual time=0.021..0.022 rows=4 loops=1)
                                                  Buffers: shared read=1
                                      ->  Finalize GroupAggregate  (cost=34514.63..34515.02 rows=1 width=61) (actual time=212.840..212.970 rows=4 loops=1)
                                            Group Key: b.sitter_id
                                            Buffers: shared hit=24 read=22224
                                            ->  Gather Merge  (cost=34514.63..34514.99 rows=3 width=53) (actual time=212.822..212.945 rows=7 loops=1)
                                                  Workers Planned: 3
                                                  Workers Launched: 3
                                                  Buffers: shared hit=24 read=22224
                                                  ->  Sort  (cost=33514.59..33514.60 rows=1 width=53) (actual time=206.606..206.608 rows=2 loops=4)
                                                        Sort Key: b.sitter_id
                                                        Sort Method: quicksort  Memory: 25kB
                                                        Buffers: shared hit=24 read=22224
                                                        Worker 0:  Sort Method: quicksort  Memory: 25kB
                                                        Worker 1:  Sort Method: quicksort  Memory: 25kB
                                                        Worker 2:  Sort Method: quicksort  Memory: 25kB
                                                        ->  Partial HashAggregate  (cost=33514.57..33514.58 rows=1 width=53) (actual time=206.527..206.529 rows=2 loops=4)
                                                              Group Key: b.sitter_id
                                                              Batches: 1  Memory Usage: 24kB
                                                              Buffers: shared read=22224
                                                              Worker 0:  Batches: 1  Memory Usage: 24kB
                                                              Worker 1:  Batches: 1  Memory Usage: 24kB
                                                              Worker 2:  Batches: 1  Memory Usage: 24kB
                                                              ->  Parallel Seq Scan on bookings b  (cost=0.00..29482.22 rows=322588 width=45) (actual time=0.070..120.157 rows=250004 loops=4)
                                                                    Filter: ((date_from <= CURRENT_DATE) AND (date_from >= (CURRENT_DATE - '1 year'::interval)))
                                                                    Rows Removed by Filter: 1
                                                                    Buffers: shared read=22224
                                ->  Finalize GroupAggregate  (cost=32537.88..32538.27 rows=1 width=61) (actual time=190.603..199.257 rows=4 loops=1)
                                      Group Key: b_1.sitter_id
                                      Buffers: shared hit=22248 read=3407
                                      ->  Gather Merge  (cost=32537.88..32538.24 rows=3 width=69) (actual time=190.572..199.221 rows=7 loops=1)
                                            Workers Planned: 3
                                            Workers Launched: 3
                                            Buffers: shared hit=22248 read=3407
                                            ->  Sort  (cost=31537.84..31537.84 rows=1 width=69) (actual time=184.406..184.410 rows=2 loops=4)
                                                  Sort Key: b_1.sitter_id
                                                  Sort Method: quicksort  Memory: 25kB
                                                  Buffers: shared hit=22248 read=3407
                                                  Worker 0:  Sort Method: quicksort  Memory: 25kB
                                                  Worker 1:  Sort Method: quicksort  Memory: 25kB
                                                  Worker 2:  Sort Method: quicksort  Memory: 25kB
                                                  ->  Partial HashAggregate  (cost=31537.82..31537.83 rows=1 width=69) (actual time=184.336..184.340 rows=2 loops=4)
                                                        Group Key: b_1.sitter_id
                                                        Batches: 1  Memory Usage: 24kB
                                                        Buffers: shared hit=22224 read=3407
                                                        Worker 0:  Batches: 1  Memory Usage: 24kB
                                                        Worker 1:  Batches: 1  Memory Usage: 24kB
                                                        Worker 2:  Batches: 1  Memory Usage: 24kB
                                                        ->  Parallel Hash Join  (cost=4971.92..31268.59 rows=53846 width=41) (actual time=43.109..172.841 rows=41731 loops=4)
                                                              Hash Cond: ((b_1.booking_id)::text = (r.booking_id)::text)
                                                              Buffers: shared hit=22224 read=3407
                                                              ->  Parallel Seq Scan on bookings b_1  (cost=0.00..25449.88 rows=322588 width=74) (actual time=0.019..27.882 rows=250006 loops=4)
                                                                    Buffers: shared hit=22224
                                                              ->  Parallel Hash  (cost=4102.52..4102.52 rows=69552 width=41) (actual time=42.288..42.289 rows=41731 loops=4)
                                                                    Buckets: 262144  Batches: 1  Memory Usage: 15168kB
                                                                    Buffers: shared read=3407
                                                                    ->  Parallel Seq Scan on reviews r  (cost=0.00..4102.52 rows=69552 width=41) (actual time=0.063..16.909 rows=41731 loops=4)
                                                                          Buffers: shared read=3407
                          ->  Hash  (cost=15.80..15.80 rows=580 width=64) (actual time=0.049..0.050 rows=12 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                Buffers: shared read=1
                                ->  Seq Scan on users u  (cost=0.00..15.80 rows=580 width=64) (actual time=0.029..0.032 rows=12 loops=1)
                                      Buffers: shared read=1
Planning:
  Buffers: shared hit=402 read=29
Planning Time: 3.607 ms
Execution Time: 413.138 ms
}}}

'''Average execution time (10 attempts):''' 410.225ms

This execution time is perfectly acceptable for a heavy analytical reporting query. Because this query does not block user requests and is usually run as a background task or in an admin dashboard, we do not need to do more optimization with highly specific indexes that can slow down standard `INSERT` and `UPDATE` operations.

== 4. Search for available sitters with highest rankings ==

Benchmark query:
{{{
#!sql
EXPLAIN (ANALYZE, BUFFERS)
WITH requested_dates AS (
    SELECT 
        (CURRENT_DATE + INTERVAL '10 days')::date AS req_start,
        (CURRENT_DATE + INTERVAL '15 days')::date AS req_end
),
unavailable_sitters AS (
    SELECT DISTINCT b.sitter_id
    FROM project.bookings b
    JOIN requested_dates rd ON true
    WHERE b.status IN ('Confirmed', 'Pending')
      AND b.date_from <= rd.req_end 
      AND b.date_to >= rd.req_start
),
highly_rated_sitters AS (
    SELECT b.sitter_id
    FROM project.reviews r
    JOIN project.bookings b ON r.booking_id = b.booking_id
    GROUP BY b.sitter_id
    HAVING AVG(r.rating) >= 4.0
)
SELECT 
    u.username,
    u.first_name,
    u.last_name,
    u.email
FROM project.pet_sitters ps
JOIN project.users u ON ps.user_id = u.user_id
JOIN highly_rated_sitters hrs ON hrs.sitter_id = ps.user_id
LEFT JOIN unavailable_sitters us ON us.sitter_id = ps.user_id
WHERE us.sitter_id IS NULL
LIMIT 20;
}}}

Execution without indexes:

{{{
Limit  (cost=66246.09..66295.07 rows=1 width=51) (actual time=279.739..287.004 rows=3 loops=1)
  Buffers: shared hit=47892 read=2
  ->  Nested Loop Anti Join  (cost=66246.09..66295.07 rows=1 width=51) (actual time=279.736..287.000 rows=3 loops=1)
        Join Filter: ((b_1.sitter_id)::text = (ps.user_id)::text)
        Buffers: shared hit=47892 read=2
        ->  Nested Loop  (cost=32537.88..32586.84 rows=1 width=141) (actual time=184.771..188.940 rows=3 loops=1)
              Join Filter: ((ps.user_id)::text = (u.user_id)::text)
              Rows Removed by Join Filter: 20
              Buffers: shared hit=25659 read=2
              ->  Nested Loop  (cost=32537.88..32563.79 rows=1 width=127) (actual time=184.747..188.897 rows=3 loops=1)
                    Join Filter: ((ps.user_id)::text = (b.sitter_id)::text)
                    Rows Removed by Join Filter: 3
                    Buffers: shared hit=25657 read=1
                    ->  Finalize GroupAggregate  (cost=32537.88..32538.27 rows=1 width=37) (actual time=184.705..188.834 rows=3 loops=1)
                          Group Key: b.sitter_id
                          Filter: (avg(r.rating) >= 4.0)
                          Rows Removed by Filter: 1
                          Buffers: shared hit=25655
                          ->  Gather Merge  (cost=32537.88..32538.24 rows=3 width=69) (actual time=184.688..188.798 rows=7 loops=1)
                                Workers Planned: 3
                                Workers Launched: 3
                                Buffers: shared hit=25655
                                ->  Sort  (cost=31537.84..31537.84 rows=1 width=69) (actual time=178.566..178.571 rows=2 loops=4)
                                      Sort Key: b.sitter_id
                                      Sort Method: quicksort  Memory: 25kB
                                      Buffers: shared hit=25655
                                      Worker 0:  Sort Method: quicksort  Memory: 25kB
                                      Worker 1:  Sort Method: quicksort  Memory: 25kB
                                      Worker 2:  Sort Method: quicksort  Memory: 25kB
                                      ->  Partial HashAggregate  (cost=31537.82..31537.83 rows=1 width=69) (actual time=178.495..178.500 rows=2 loops=4)
                                            Group Key: b.sitter_id
                                            Batches: 1  Memory Usage: 24kB
                                            Buffers: shared hit=25631
                                            Worker 0:  Batches: 1  Memory Usage: 24kB
                                            Worker 1:  Batches: 1  Memory Usage: 24kB
                                            Worker 2:  Batches: 1  Memory Usage: 24kB
                                            ->  Parallel Hash Join  (cost=4971.92..31268.59 rows=53846 width=41) (actual time=36.863..167.092 rows=41731 loops=4)
                                                  Hash Cond: ((b.booking_id)::text = (r.booking_id)::text)
                                                  Buffers: shared hit=25631
                                                  ->  Parallel Seq Scan on bookings b  (cost=0.00..25449.88 rows=322588 width=74) (actual time=0.019..27.928 rows=250006 loops=4)
                                                        Buffers: shared hit=22224
                                                  ->  Parallel Hash  (cost=4102.52..4102.52 rows=69552 width=41) (actual time=36.033..36.034 rows=41731 loops=4)
                                                        Buckets: 262144  Batches: 1  Memory Usage: 15200kB
                                                        Buffers: shared hit=3407
                                                        ->  Parallel Seq Scan on reviews r  (cost=0.00..4102.52 rows=69552 width=41) (actual time=0.021..11.538 rows=41731 loops=4)
                                                              Buffers: shared hit=3407
                    ->  Seq Scan on pet_sitters ps  (cost=0.00..16.90 rows=690 width=90) (actual time=0.014..0.014 rows=2 loops=3)
                          Buffers: shared hit=2 read=1
              ->  Seq Scan on users u  (cost=0.00..15.80 rows=580 width=88) (actual time=0.009..0.009 rows=8 loops=3)
                    Buffers: shared hit=2 read=1
        ->  Unique  (cost=33708.21..33708.22 rows=1 width=37) (actual time=31.654..32.684 rows=0 loops=3)
              Buffers: shared hit=22233
              ->  Sort  (cost=33708.21..33708.22 rows=1 width=37) (actual time=31.654..32.684 rows=0 loops=3)
                    Sort Key: b_1.sitter_id
                    Sort Method: quicksort  Memory: 25kB
                    Buffers: shared hit=22233
                    ->  Gather  (cost=1000.00..33708.20 rows=1 width=37) (actual time=94.936..98.026 rows=0 loops=1)
                          Workers Planned: 3
                          Workers Launched: 3
                          Buffers: shared hit=22233
                          ->  Parallel Seq Scan on bookings b_1  (cost=0.00..32708.10 rows=1 width=37) (actual time=89.286..89.286 rows=0 loops=4)
                                Filter: (((status)::text = ANY ('{Confirmed,Pending}'::text[])) AND (date_from <= ((CURRENT_DATE + '15 days'::interval))::date) AND (date_to >= ((CURRENT_DATE + '10 days'::interval))::date))
                                Rows Removed by Filter: 250006
                                Buffers: shared hit=22233
Planning:
  Buffers: shared hit=11 read=8
Planning Time: 1.269 ms
Execution Time: 287.147 ms
}}}

'''Average execution time (10 attempts):''' 283.421ms

We add this index to optimize the date overlapping:
{{{
#!sql
CREATE INDEX idx_bookings_sitter_status_dates
ON project.bookings (sitter_id, status, date_from, date_to);
}}}

Execution with indexes:

{{{
Limit  (cost=32538.32..48741.33 rows=1 width=51) (actual time=192.798..202.516 rows=3 loops=1)
  Buffers: shared hit=27463 read=901
  ->  Nested Loop Anti Join  (cost=32538.32..48741.33 rows=1 width=51) (actual time=192.796..202.512 rows=3 loops=1)
        Join Filter: ((b_1.sitter_id)::text = (ps.user_id)::text)
        Buffers: shared hit=27463 read=901
        ->  Nested Loop  (cost=32537.88..32586.84 rows=1 width=141) (actual time=180.755..185.162 rows=3 loops=1)
              Join Filter: ((ps.user_id)::text = (u.user_id)::text)
              Rows Removed by Join Filter: 20
              Buffers: shared hit=25661
              ->  Nested Loop  (cost=32537.88..32563.79 rows=1 width=127) (actual time=180.740..185.126 rows=3 loops=1)
                    Join Filter: ((ps.user_id)::text = (b.sitter_id)::text)
                    Rows Removed by Join Filter: 3
                    Buffers: shared hit=25658
                    ->  Finalize GroupAggregate  (cost=32537.88..32538.27 rows=1 width=37) (actual time=180.718..185.081 rows=3 loops=1)
                          Group Key: b.sitter_id
                          Filter: (avg(r.rating) >= 4.0)
                          Rows Removed by Filter: 1
                          Buffers: shared hit=25655
                          ->  Gather Merge  (cost=32537.88..32538.24 rows=3 width=69) (actual time=180.700..185.043 rows=7 loops=1)
                                Workers Planned: 3
                                Workers Launched: 3
                                Buffers: shared hit=25655
                                ->  Sort  (cost=31537.84..31537.84 rows=1 width=69) (actual time=174.919..174.925 rows=2 loops=4)
                                      Sort Key: b.sitter_id
                                      Sort Method: quicksort  Memory: 25kB
                                      Buffers: shared hit=25655
                                      Worker 0:  Sort Method: quicksort  Memory: 25kB
                                      Worker 1:  Sort Method: quicksort  Memory: 25kB
                                      Worker 2:  Sort Method: quicksort  Memory: 25kB
                                      ->  Partial HashAggregate  (cost=31537.82..31537.83 rows=1 width=69) (actual time=174.853..174.857 rows=2 loops=4)
                                            Group Key: b.sitter_id
                                            Batches: 1  Memory Usage: 24kB
                                            Buffers: shared hit=25631
                                            Worker 0:  Batches: 1  Memory Usage: 24kB
                                            Worker 1:  Batches: 1  Memory Usage: 24kB
                                            Worker 2:  Batches: 1  Memory Usage: 24kB
                                            ->  Parallel Hash Join  (cost=4971.92..31268.59 rows=53846 width=41) (actual time=36.095..163.581 rows=41731 loops=4)
                                                  Hash Cond: ((b.booking_id)::text = (r.booking_id)::text)
                                                  Buffers: shared hit=25631
                                                  ->  Parallel Seq Scan on bookings b  (cost=0.00..25449.88 rows=322588 width=74) (actual time=0.019..27.140 rows=250006 loops=4)
                                                        Buffers: shared hit=22224
                                                  ->  Parallel Hash  (cost=4102.52..4102.52 rows=69552 width=41) (actual time=35.280..35.281 rows=41731 loops=4)
                                                        Buckets: 262144  Batches: 1  Memory Usage: 15136kB
                                                        Buffers: shared hit=3407
                                                        ->  Parallel Seq Scan on reviews r  (cost=0.00..4102.52 rows=69552 width=41) (actual time=0.022..11.047 rows=41731 loops=4)
                                                              Buffers: shared hit=3407
                    ->  Seq Scan on pet_sitters ps  (cost=0.00..16.90 rows=690 width=90) (actual time=0.008..0.008 rows=2 loops=3)
                          Buffers: shared hit=3
              ->  Seq Scan on users u  (cost=0.00..15.80 rows=580 width=88) (actual time=0.006..0.006 rows=8 loops=3)
                    Buffers: shared hit=3
        ->  Unique  (cost=0.44..16154.48 rows=1 width=37) (actual time=5.776..5.776 rows=0 loops=3)
              Buffers: shared hit=1802 read=901
              ->  Index Only Scan using idx_bookings_sitter_status_dates on bookings b_1  (cost=0.44..16154.48 rows=1 width=37) (actual time=5.775..5.775 rows=0 loops=3)
                    Index Cond: ((status = ANY ('{Confirmed,Pending}'::text[])) AND (date_from <= ((CURRENT_DATE + '15 days'::interval))::date) AND (date_to >= ((CURRENT_DATE + '10 days'::interval))::date))
                    Heap Fetches: 0
                    Buffers: shared hit=1802 read=901
Planning:
  Buffers: shared hit=49 read=1
Planning Time: 1.463 ms
Execution Time: 202.649 ms
}}}

'''Average execution time (10 attempts):''' 199.530ms

With this index, we eliminated the expensive sequential scan in exchange for a highly efficient index scan. The query is still bottlenecked by `AVG(rating)`, this index provides a crucial performance improvement to the search feature, so we will '''keep''' it.