Changes between Version 6 and Version 7 of OtherTopics


Ignore:
Timestamp:
06/03/26 10:05:38 (3 weeks ago)
Author:
181201
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • OtherTopics

    v6 v7  
    1 = Other topics (Performance, Security, …)
    2 
    31== Performance ==
    42
    5 === 1. Adding 1000000 mock rows to 'bookings' table ===
    6 
    7 {{{
    8 #!sql
     3=== 1. Adding mock data for bookings ===
     4
     5To properly benchmark our database, we first generate a realistic dataset of 1000000 bookings and related entities which.
     6
     7{{{
     8#!sql
     9-- 1. Insert 1,000,000 mock bookings
    910ALTER TABLE project.bookings DISABLE TRIGGER trg_booking_validation;
    1011
    … …  
    2930
    3031ALTER TABLE project.bookings ENABLE TRIGGER trg_booking_validation;
     32}}}
     33
     34{{{
     35#!sql
     36-- 2. Insert relevant mock payments
     37INSERT INTO project.payments (booking_id, amount, payment_type)
     38SELECT
     39    b.booking_id,
     40    floor(random() * 100 + 20)::int AS amount,
     41    (ARRAY['Card', 'Cash', 'Bank Transfer'])[floor(random()*3)+1] AS payment_type
     42FROM project.bookings b
     43WHERE NOT EXISTS (
     44    SELECT 1 FROM project.payments p WHERE p.booking_id = b.booking_id
     45);
     46}}}
     47
     48{{{
     49#!sql
     50-- 3. Insert mock reviews for completed bookings
     51INSERT INTO project.reviews (booking_id, rating, comment)
     52SELECT
     53    b.booking_id,
     54    floor(random()*5)+1 AS rating,
     55    'Mock Review ' || b.booking_id AS comment
     56FROM project.bookings b
     57WHERE b.status = 'Completed'
     58  AND random() > 0.3
     59  AND NOT EXISTS (
     60      SELECT 1 FROM project.reviews r WHERE r.booking_id = b.booking_id
     61  );
     62}}}
     63
     64{{{
     65#!sql
     66-- 4. Insert mock pets
     67INSERT INTO project.pets (owner_id, name, age, pettype_id)
     68SELECT
     69    po.user_id,
     70    'Mock Pet ' || po.user_id || '-' || gs AS name,
     71    floor(random()*15)+1 AS age,
     72    pt.pettype_id
     73FROM project.pet_owners po
     74CROSS JOIN LATERAL generate_series(1, floor(random()*3 + 1)::int) gs
     75JOIN LATERAL (
     76    SELECT pettype_id FROM project.pet_types ORDER BY random() LIMIT 1
     77) pt ON true;
     78}}}
     79
     80{{{
     81#!sql
     82-- 5. Link random services to bookings
     83INSERT INTO project.booking_services (booking_id, service_id)
     84SELECT
     85    b.booking_id,
     86    s.service_id
     87FROM project.bookings b
     88JOIN LATERAL (
     89    SELECT service_id FROM project.services ORDER BY random() LIMIT 1
     90) s ON true
     91WHERE NOT EXISTS (
     92    SELECT 1 FROM project.booking_services bs WHERE bs.booking_id = b.booking_id
     93);
    3194}}}
    3295
    … …  
    106169'''Average execution time (10 attempts):''' 0.087ms
    107170
    108 Because the execution time has been massively lowered by bypassing the expensive sequential scan and memory sort, we '''keep''' this index
    109 
    110 
    111 == 2. Calculate average sitter rating ==
     171Because the execution time has been massively lowered by bypassing the expensive sequential scan and memory sort, we '''keep''' this index.
     172
     173=== 2. Calculate average sitter rating ===
    112174
    113175Benchmark query:
     176
    114177{{{
    115178#!sql
    … …  
    158221
    159222We add this index:
     223
    160224{{{
    161225#!sql
    … …  
    200264'''Average execution time (10 attempts):''' 138.346ms
    201265
    202 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.
    203 
    204 == 3. Pet Sitter Leaderboard, Analytics ==
     266There 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.
     267
     268=== 3. Sitter Performance Analytics (Phase 6 Query) ===
    205269
    206270Benchmark query:
    … …  
    210274WITH params AS (
    211275    SELECT
    212         (CURRENT_DATE - INTERVAL '1 year') AS start_date,
    213         CURRENT_DATE AS end_date
     276        (CURRENT_DATE - INTERVAL '1 year')::DATE AS start_date,
     277        CURRENT_DATE::DATE AS end_date
    214278),
    215 sitter_bookings AS (
     279sitter_stats AS (
    216280    SELECT
    217281        b.sitter_id,
    218         COUNT(*) AS total_bookings,
    219         COUNT(*) FILTER (WHERE b.status = 'Completed') AS completed_bookings,
    220         COUNT(*) FILTER (WHERE b.status = 'Canceled') AS canceled_bookings
     282        COUNT(b.booking_id) AS total_bookings,
     283        COUNT(b.booking_id) FILTER (WHERE b.status = 'Completed') AS completed_bookings,
     284        COUNT(b.booking_id) FILTER (WHERE b.status IN ('Canceled', 'Rejected')) AS missed_bookings
    221285    FROM project.bookings b
    222     JOIN params p ON b.date_from >= p.start_date AND b.date_from <= p.end_date
     286    JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date
    223287    GROUP BY b.sitter_id
    224288),
    225 sitter_reviews AS (
     289sitter_reports AS (
     290    SELECT
     291        b.sitter_id,
     292        SUM(pay.amount) AS total_revenue
     293    FROM project.bookings b
     294    JOIN project.payments pay ON b.booking_id = pay.booking_id
     295    JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date
     296    WHERE b.status = 'Completed'
     297    GROUP BY b.sitter_id
     298),
     299sitter_ratings AS (
    226300    SELECT
    227301        b.sitter_id,
    228302        AVG(r.rating)::numeric(10,2) AS avg_rating,
    229303        COUNT(r.review_id) AS total_reviews
    230     FROM project.reviews r
    231     JOIN project.bookings b ON r.booking_id = b.booking_id
     304    FROM project.bookings b
     305    JOIN project.reviews r ON b.booking_id = r.booking_id
     306    JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date
    232307    GROUP BY b.sitter_id
    233308)
    234309SELECT
    235     u.username,
    236     u.first_name,
    237     u.last_name,
    238     COALESCE(sb.completed_bookings, 0) AS completed,
    239     COALESCE(sb.canceled_bookings, 0) AS canceled,
    240     COALESCE(sr.avg_rating, 0) AS rating,
    241     (
    242         COALESCE(sb.completed_bookings, 0) * 5
    243         + COALESCE(sr.avg_rating, 0) * 10
    244         - COALESCE(sb.canceled_bookings, 0) * 3
    245     ) AS activity_score,
     310    u.user_id, u.username, u.first_name, u.last_name,
     311    COALESCE(ss.total_bookings, 0) AS total_bookings,
     312    COALESCE(ss.completed_bookings, 0) AS completed_bookings,
     313    COALESCE(ss.missed_bookings, 0) AS missed_bookings,
     314    COALESCE(sr.avg_rating, 0) AS avg_rating,
     315    COALESCE(sr.total_reviews, 0) AS total_reviews,
     316    COALESCE(sf.total_revenue, 0) AS total_revenue,
    246317    DENSE_RANK() OVER (
    247318        ORDER BY (
    248             COALESCE(sb.completed_bookings, 0) * 5
    249             + COALESCE(sr.avg_rating, 0) * 10
    250             - COALESCE(sb.canceled_bookings, 0) * 3
     319            COALESCE(sf.total_revenue, 0) * 0.5
     320            + COALESCE(ss.completed_bookings, 0) * 10
     321            + COALESCE(sr.avg_rating, 0) * 15
     322            - COALESCE(ss.missed_bookings, 0) * 5
    251323        ) DESC
    252     ) AS leaderboard_rank
    253 FROM project.pet_sitters ps
    254 JOIN project.users u ON ps.user_id = u.user_id
    255 LEFT JOIN sitter_bookings sb ON sb.sitter_id = ps.user_id
    256 LEFT JOIN sitter_reviews sr ON sr.sitter_id = ps.user_id
    257 ORDER BY leaderboard_rank
     324    ) AS sitter_rank
     325FROM project.users u
     326JOIN project.pet_sitters ps ON u.user_id = ps.user_id
     327LEFT JOIN sitter_stats ss ON ss.sitter_id = ps.user_id
     328LEFT JOIN sitter_reports sf ON sf.sitter_id = ps.user_id
     329LEFT JOIN sitter_ratings sr ON sr.sitter_id = ps.user_id
     330WHERE COALESCE(ss.total_bookings, 0) > 0
     331ORDER BY sitter_rank
    258332LIMIT 10;
    259333}}}
    260334
    261335Execution without indexes:
    262 
    263 {{{
    264 Limit  (cost=67216.49..67216.51 rows=10 width=115) (actual time=403.788..412.560 rows=4 loops=1)
    265   Buffers: shared hit=22278 read=25633
    266   ->  Sort  (cost=67216.49..67218.21 rows=690 width=115) (actual time=403.786..412.557 rows=4 loops=1)
     336{{{
     337Limit  (cost=151645.74..151645.77 rows=10 width=176) (actual time=2620.994..2631.056 rows=4 loops=1)
     338  Buffers: shared hit=740349
     339  CTE params
     340    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.024..0.025 rows=1 loops=1)
     341  ->  Sort  (cost=151645.72..151646.30 rows=230 width=176) (actual time=2562.262..2572.322 rows=4 loops=1)
     342        Sort Key: (dense_rank() OVER (?))
     343        Sort Method: quicksort  Memory: 26kB
     344        Buffers: shared hit=740349
     345        ->  WindowAgg  (cost=151631.00..151640.75 rows=230 width=176) (actual time=2562.200..2572.274 rows=4 loops=1)
     346              Buffers: shared hit=740346
     347              ->  Sort  (cost=151630.98..151631.55 rows=230 width=152) (actual time=2562.183..2572.243 rows=4 loops=1)
     348                    Sort Key: ((((((COALESCE(sf.total_revenue, '0'::bigint))::numeric * 0.5) + ((COALESCE(ss.completed_bookings, '0'::bigint) * 10))::numeric) + (COALESCE(sr.avg_rating, '0'::numeric) * '15'::numeric)) - ((COALESCE(ss.missed_bookings, '0'::bigint) * 5))::numeric)) DESC
     349                    Sort Method: quicksort  Memory: 25kB
     350                    Buffers: shared hit=740346
     351                    ->  Hash Join  (cost=151596.30..151621.96 rows=230 width=152) (actual time=2562.155..2572.225 rows=4 loops=1)
     352                          Hash Cond: ((u.user_id)::text = (ps.user_id)::text)
     353                          Buffers: shared hit=740343
     354                          ->  Seq Scan on users u  (cost=0.00..15.80 rows=580 width=64) (actual time=0.015..0.018 rows=12 loops=1)
     355                                Buffers: shared hit=1
     356                          ->  Hash  (cost=151593.43..151593.43 rows=230 width=146) (actual time=2562.102..2572.160 rows=4 loops=1)
     357                                Buckets: 1024  Batches: 1  Memory Usage: 9kB
     358                                Buffers: shared hit=740342
     359                                ->  Merge Left Join  (cost=151588.77..151593.43 rows=230 width=146) (actual time=2562.070..2572.145 rows=4 loops=1)
     360                                      Merge Cond: ((ps.user_id)::text = (sr.sitter_id)::text)
     361                                      Buffers: shared hit=740342
     362                                      ->  Merge Left Join  (cost=99928.44..99932.50 rows=230 width=122) (actual time=2328.059..2328.084 rows=4 loops=1)
     363                                            Merge Cond: ((ps.user_id)::text = (sf.sitter_id)::text)
     364                                            Buffers: shared hit=714518
     365                                            ->  Merge Left Join  (cost=43617.47..43620.94 rows=230 width=114) (actual time=779.217..779.233 rows=4 loops=1)
     366                                                  Merge Cond: ((ps.user_id)::text = (ss.sitter_id)::text)
     367                                                  Filter: (COALESCE(ss.total_bookings, '0'::bigint) > 0)
     368                                                  Buffers: shared hit=23035
     369                                                  ->  Sort  (cost=49.44..51.16 rows=690 width=90) (actual time=0.024..0.026 rows=4 loops=1)
     370                                                        Sort Key: ps.user_id
     371                                                        Sort Method: quicksort  Memory: 25kB
     372                                                        Buffers: shared hit=1
     373                                                        ->  Seq Scan on pet_sitters ps  (cost=0.00..16.90 rows=690 width=90) (actual time=0.014..0.015 rows=4 loops=1)
     374                                                              Buffers: shared hit=1
     375                                                  ->  Sort  (cost=43568.03..43568.04 rows=1 width=61) (actual time=779.154..779.159 rows=4 loops=1)
     376                                                        Sort Key: ss.sitter_id
     377                                                        Sort Method: quicksort  Memory: 25kB
     378                                                        Buffers: shared hit=23034
     379                                                        ->  Subquery Scan on ss  (cost=43568.00..43568.02 rows=1 width=61) (actual time=779.137..779.144 rows=4 loops=1)
     380                                                              Buffers: shared hit=23034
     381                                                              ->  HashAggregate  (cost=43568.00..43568.01 rows=1 width=61) (actual time=779.130..779.134 rows=4 loops=1)
     382                                                                    Group Key: b.sitter_id
     383                                                                    Batches: 1  Memory Usage: 24kB
     384                                                                    Buffers: shared hit=23034
     385                                                                    ->  Nested Loop  (cost=16900.42..41901.29 rows=111114 width=83) (actual time=44.143..421.383 rows=1000017 loops=1)
     386                                                                          Buffers: shared hit=23034
     387                                                                          ->  CTE Scan on params p  (cost=0.00..0.02 rows=1 width=8) (actual time=0.028..0.030 rows=1 loops=1)
     388                                                                          ->  Bitmap Heap Scan on bookings b  (cost=16900.42..40790.13 rows=111114 width=87) (actual time=44.095..233.073 rows=1000017 loops=1)
     389                                                                                Recheck Cond: ((date_from >= p.start_date) AND (date_from < p.end_date))
     390                                                                                Heap Blocks: exact=22223
     391                                                                                Buffers: shared hit=23034
     392                                                                                ->  Bitmap Index Scan on idx_bookings_owner_date  (cost=0.00..16872.65 rows=111114 width=0) (actual time=39.433..39.433 rows=1000017 loops=1)
     393                                                                                      Index Cond: ((date_from >= p.start_date) AND (date_from < p.end_date))
     394                                                                                      Buffers: shared hit=811
     395                                            ->  Sort  (cost=56310.97..56310.97 rows=1 width=45) (actual time=1548.817..1548.822 rows=2 loops=1)
     396                                                  Sort Key: sf.sitter_id
     397                                                  Sort Method: quicksort  Memory: 25kB
     398                                                  Buffers: shared hit=691483
     399                                                  ->  Subquery Scan on sf  (cost=56310.94..56310.96 rows=1 width=45) (actual time=1548.794..1548.800 rows=2 loops=1)
     400                                                        Buffers: shared hit=691483
     401                                                        ->  HashAggregate  (cost=56310.94..56310.95 rows=1 width=45) (actual time=1548.786..1548.791 rows=2 loops=1)
     402                                                              Group Key: b_1.sitter_id
     403                                                              Batches: 1  Memory Usage: 24kB
     404                                                              Buffers: shared hit=691483
     405                                                              ->  Nested Loop  (cost=0.42..56216.86 rows=18815 width=41) (actual time=0.103..1495.703 rows=167315 loops=1)
     406                                                                    Buffers: shared hit=691483
     407                                                                    ->  Nested Loop  (cost=0.00..37263.35 rows=18815 width=74) (actual time=0.043..225.930 rows=167315 loops=1)
     408                                                                          Join Filter: ((b_1.date_from >= p_1.start_date) AND (b_1.date_from < p_1.end_date))
     409                                                                          Rows Removed by Join Filter: 3
     410                                                                          Buffers: shared hit=22223
     411                                                                          ->  CTE Scan on params p_1  (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.003 rows=1 loops=1)
     412                                                                          ->  Seq Scan on bookings b_1  (cost=0.00..34723.28 rows=169337 width=78) (actual time=0.026..183.123 rows=167318 loops=1)
     413                                                                                Filter: ((status)::text = 'Completed'::text)
     414                                                                                Rows Removed by Filter: 832704
     415                                                                                Buffers: shared hit=22223
     416                                                                    ->  Index Scan using payments_booking_id_key on payments pay  (cost=0.42..1.01 rows=1 width=41) (actual time=0.007..0.007 rows=1 loops=167315)
     417                                                                          Index Cond: ((booking_id)::text = (b_1.booking_id)::text)
     418                                                                          Buffers: shared hit=669260
     419                                      ->  Sort  (cost=51660.34..51660.34 rows=1 width=61) (actual time=233.986..244.032 rows=2 loops=1)
     420                                            Sort Key: sr.sitter_id
     421                                            Sort Method: quicksort  Memory: 25kB
     422                                            Buffers: shared hit=25824
     423                                            ->  Subquery Scan on sr  (cost=51660.30..51660.33 rows=1 width=61) (actual time=233.958..244.007 rows=2 loops=1)
     424                                                  Buffers: shared hit=25824
     425                                                  ->  HashAggregate  (cost=51660.30..51660.32 rows=1 width=61) (actual time=233.948..243.997 rows=2 loops=1)
     426                                                        Group Key: b_2.sitter_id
     427                                                        Batches: 1  Memory Usage: 24kB
     428                                                        Buffers: shared hit=25824
     429                                                        ->  Nested Loop  (cost=5983.61..51520.87 rows=18591 width=78) (actual time=76.620..198.922 rows=167316 loops=1)
     430                                                              Join Filter: ((b_2.date_from >= p_2.start_date) AND (b_2.date_from < p_2.end_date))
     431                                                              Rows Removed by Join Filter: 3
     432                                                              Buffers: shared hit=25824
     433                                                              ->  CTE Scan on params p_2  (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.003 rows=1 loops=1)
     434                                                              ->  Gather  (cost=5983.61..49011.08 rows=167318 width=82) (actual time=76.604..171.175 rows=167319 loops=1)
     435                                                                    Workers Planned: 3
     436                                                                    Workers Launched: 3
     437                                                                    Buffers: shared hit=25824
     438                                                                    ->  Parallel Hash Join  (cost=4983.61..31279.28 rows=53974 width=82) (actual time=45.331..167.054 rows=41830 loops=4)
     439                                                                          Hash Cond: ((b_2.booking_id)::text = (r.booking_id)::text)
     440                                                                          Buffers: shared hit=25824
     441                                                                          ->  Parallel Seq Scan on bookings b_2  (cost=0.00..25448.88 rows=322588 width=78) (actual time=0.017..27.723 rows=250006 loops=4)
     442                                                                                Buffers: shared hit=22223
     443                                                                          ->  Parallel Hash  (cost=4112.16..4112.16 rows=69716 width=78) (actual time=44.396..44.398 rows=41830 loops=4)
     444                                                                                Buckets: 262144  Batches: 1  Memory Usage: 20448kB
     445                                                                                Buffers: shared hit=3415
     446                                                                                ->  Parallel Seq Scan on reviews r  (cost=0.00..4112.16 rows=69716 width=78) (actual time=9.212..20.518 rows=41830 loops=4)
     447                                                                                      Buffers: shared hit=3415
     448Planning:
     449  Buffers: shared hit=611
     450Planning Time: 4.704 ms
     451JIT:
     452  Functions: 116
     453  Options: Inlining false, Optimization false, Expressions true, Deforming true
     454  Timing: Generation 9.344 ms (Deform 4.395 ms), Inlining 0.000 ms, Optimization 4.065 ms, Emission 91.907 ms, Total 105.316 ms
     455Execution Time: 2670.429 ms
     456}}}
     457
     458'''Execution time:''' 2670.429 ms
     459
     460We attempt to optimize this analytical query by introducing multiple foreign key indexes:
     461{{{
     462#!sql
     463CREATE INDEX idx_bookings_date_sitter ON project.bookings (date_from, sitter_id);
     464CREATE INDEX idx_bookings_date_owner ON project.bookings (date_from, owner_id);
     465CREATE INDEX idx_payments_booking_id ON project.payments (booking_id);
     466CREATE INDEX idx_booking_services_booking_id ON project.booking_services (booking_id);
     467CREATE INDEX idx_pets_owner_id ON project.pets (owner_id);
     468}}}
     469
     470Execution with indexes:
     471{{{
     472Limit  (cost=113555.49..113555.51 rows=10 width=176) (actual time=3667.482..3667.503 rows=4 loops=1)
     473  Buffers: shared hit=742025, temp read=10108 written=10108
     474  CTE params
     475    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1)
     476  ->  Sort  (cost=113555.47..113556.04 rows=230 width=176) (actual time=3609.923..3609.942 rows=4 loops=1)
     477        Sort Key: (dense_rank() OVER (?))
     478        Sort Method: quicksort  Memory: 26kB
     479        Buffers: shared hit=742025, temp read=10108 written=10108
     480        ->  WindowAgg  (cost=113540.74..113550.50 rows=230 width=176) (actual time=3609.856..3609.894 rows=4 loops=1)
     481              Buffers: shared hit=742022, temp read=10108 written=10108
     482              ->  Sort  (cost=113540.72..113541.30 rows=230 width=152) (actual time=3609.838..3609.856 rows=4 loops=1)
     483                    Sort Key: ((((((COALESCE(sf.total_revenue, '0'::bigint))::numeric * 0.5) + ((COALESCE(ss.completed_bookings, '0'::bigint) * 10))::numeric) + (COALESCE(sr.avg_rating, '0'::numeric) * '15'::numeric)) - ((COALESCE(ss.missed_bookings, '0'::bigint) * 5))::numeric)) DESC
     484                    Sort Method: quicksort  Memory: 25kB
     485                    Buffers: shared hit=742022, temp read=10108 written=10108
     486                    ->  Hash Join  (cost=113506.05..113531.70 rows=230 width=152) (actual time=3609.810..3609.839 rows=4 loops=1)
     487                          Hash Cond: ((u.user_id)::text = (ps.user_id)::text)
     488                          Buffers: shared hit=742019, temp read=10108 written=10108
     489                          ->  Seq Scan on users u  (cost=0.00..15.80 rows=580 width=64) (actual time=0.022..0.027 rows=12 loops=1)
     490                                Buffers: shared hit=1
     491                          ->  Hash  (cost=113503.17..113503.17 rows=230 width=146) (actual time=3609.749..3609.766 rows=4 loops=1)
     492                                Buckets: 1024  Batches: 1  Memory Usage: 9kB
     493                                Buffers: shared hit=742018, temp read=10108 written=10108
     494                                ->  Merge Left Join  (cost=113498.52..113503.17 rows=230 width=146) (actual time=3609.711..3609.745 rows=4 loops=1)
     495                                      Merge Cond: ((ps.user_id)::text = (sr.sitter_id)::text)
     496                                      Buffers: shared hit=742018, temp read=10108 written=10108
     497                                      ->  Merge Left Join  (cost=72271.51..72275.57 rows=230 width=122) (actual time=2380.858..2380.883 rows=4 loops=1)
     498                                            Merge Cond: ((ps.user_id)::text = (sf.sitter_id)::text)
     499                                            Buffers: shared hit=715489
     500                                            ->  Merge Left Join  (cost=28260.39..28263.86 rows=230 width=114) (actual time=781.667..781.682 rows=4 loops=1)
     501                                                  Merge Cond: ((ps.user_id)::text = (ss.sitter_id)::text)
     502                                                  Filter: (COALESCE(ss.total_bookings, '0'::bigint) > 0)
     503                                                  Buffers: shared hit=23115
     504                                                  ->  Sort  (cost=49.44..51.16 rows=690 width=90) (actual time=0.018..0.020 rows=4 loops=1)
     505                                                        Sort Key: ps.user_id
     506                                                        Sort Method: quicksort  Memory: 25kB
     507                                                        Buffers: shared hit=1
     508                                                        ->  Seq Scan on pet_sitters ps  (cost=0.00..16.90 rows=690 width=90) (actual time=0.007..0.008 rows=4 loops=1)
     509                                                              Buffers: shared hit=1
     510                                                  ->  Sort  (cost=28210.95..28210.96 rows=1 width=61) (actual time=781.612..781.617 rows=4 loops=1)
     511                                                        Sort Key: ss.sitter_id
     512                                                        Sort Method: quicksort  Memory: 25kB
     513                                                        Buffers: shared hit=23114
     514                                                        ->  Subquery Scan on ss  (cost=28210.92..28210.94 rows=1 width=61) (actual time=781.595..781.601 rows=4 loops=1)
     515                                                              Buffers: shared hit=23114
     516                                                              ->  HashAggregate  (cost=28210.92..28210.93 rows=1 width=61) (actual time=781.588..781.593 rows=4 loops=1)
     517                                                                    Group Key: b.sitter_id
     518                                                                    Batches: 1  Memory Usage: 24kB
     519                                                                    Buffers: shared hit=23114
     520                                                                    ->  Nested Loop  (cost=1543.34..26544.21 rows=111114 width=83) (actual time=42.444..423.581 rows=1000017 loops=1)
     521                                                                          Buffers: shared hit=23114
     522                                                                          ->  CTE Scan on params p  (cost=0.00..0.02 rows=1 width=8) (actual time=0.016..0.018 rows=1 loops=1)
     523                                                                          ->  Bitmap Heap Scan on bookings b  (cost=1543.34..25433.05 rows=111114 width=87) (actual time=42.408..230.648 rows=1000017 loops=1)
     524                                                                                Recheck Cond: ((date_from >= p.start_date) AND (date_from < p.end_date))
     525                                                                                Heap Blocks: exact=22223
     526                                                                                Buffers: shared hit=23114
     527                                                                                ->  Bitmap Index Scan on idx_bookings_date_owner  (cost=0.00..1515.57 rows=111114 width=0) (actual time=37.787..37.787 rows=1000017 loops=1)
     528                                                                                      Index Cond: ((date_from >= p.start_date) AND (date_from < p.end_date))
     529                                                                                      Buffers: shared hit=891
     530                                            ->  Sort  (cost=44011.12..44011.12 rows=1 width=45) (actual time=1599.167..1599.172 rows=2 loops=1)
     531                                                  Sort Key: sf.sitter_id
     532                                                  Sort Method: quicksort  Memory: 25kB
     533                                                  Buffers: shared hit=692374
     534                                                  ->  Subquery Scan on sf  (cost=44011.09..44011.11 rows=1 width=45) (actual time=1599.144..1599.151 rows=2 loops=1)
     535                                                        Buffers: shared hit=692374
     536                                                        ->  HashAggregate  (cost=44011.09..44011.10 rows=1 width=45) (actual time=1599.136..1599.141 rows=2 loops=1)
     537                                                              Group Key: b_1.sitter_id
     538                                                              Batches: 1  Memory Usage: 24kB
     539                                                              Buffers: shared hit=692374
     540                                                              ->  Nested Loop  (cost=1520.69..43917.01 rows=18815 width=41) (actual time=40.913..1546.819 rows=167315 loops=1)
     541                                                                    Buffers: shared hit=692374
     542                                                                    ->  Nested Loop  (cost=1520.27..25875.93 rows=18815 width=74) (actual time=40.876..285.030 rows=167315 loops=1)
     543                                                                          Buffers: shared hit=23114
     544                                                                          ->  CTE Scan on params p_1  (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.003 rows=1 loops=1)
     545                                                                          ->  Bitmap Heap Scan on bookings b_1  (cost=1520.27..25687.76 rows=18815 width=78) (actual time=40.864..253.061 rows=167315 loops=1)
     546                                                                                Recheck Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date))
     547                                                                                Filter: ((status)::text = 'Completed'::text)
     548                                                                                Rows Removed by Filter: 832702
     549                                                                                Heap Blocks: exact=22223
     550                                                                                Buffers: shared hit=23114
     551                                                                                ->  Bitmap Index Scan on idx_bookings_date_owner  (cost=0.00..1515.57 rows=111114 width=0) (actual time=36.257..36.257 rows=1000017 loops=1)
     552                                                                                      Index Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date))
     553                                                                                      Buffers: shared hit=891
     554                                                                    ->  Index Scan using idx_payments_booking_id on payments pay  (cost=0.42..0.96 rows=1 width=41) (actual time=0.007..0.007 rows=1 loops=167315)
     555                                                                          Index Cond: ((booking_id)::text = (b_1.booking_id)::text)
     556                                                                          Buffers: shared hit=669260
     557                                      ->  Sort  (cost=41227.01..41227.02 rows=1 width=61) (actual time=1228.829..1228.835 rows=2 loops=1)
     558                                            Sort Key: sr.sitter_id
     559                                            Sort Method: quicksort  Memory: 25kB
     560                                            Buffers: shared hit=26529, temp read=10108 written=10108
     561                                            ->  Subquery Scan on sr  (cost=41226.98..41227.00 rows=1 width=61) (actual time=1228.801..1228.810 rows=2 loops=1)
     562                                                  Buffers: shared hit=26529, temp read=10108 written=10108
     563                                                  ->  HashAggregate  (cost=41226.98..41226.99 rows=1 width=61) (actual time=1228.793..1228.801 rows=2 loops=1)
     564                                                        Group Key: b_2.sitter_id
     565                                                        Batches: 1  Memory Usage: 24kB
     566                                                        Buffers: shared hit=26529, temp read=10108 written=10108
     567                                                        ->  Hash Join  (cost=10848.00..41087.54 rows=18591 width=78) (actual time=157.648..1174.825 rows=167316 loops=1)
     568                                                              Hash Cond: ((b_2.booking_id)::text = (r.booking_id)::text)
     569                                                              Buffers: shared hit=26529, temp read=10108 written=10108
     570                                                              ->  Nested Loop  (cost=1543.34..26544.21 rows=111114 width=74) (actual time=40.133..417.946 rows=1000017 loops=1)
     571                                                                    Buffers: shared hit=23114
     572                                                                    ->  CTE Scan on params p_2  (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)
     573                                                                    ->  Bitmap Heap Scan on bookings b_2  (cost=1543.34..25433.05 rows=111114 width=78) (actual time=40.113..221.102 rows=1000017 loops=1)
     574                                                                          Recheck Cond: ((date_from >= p_2.start_date) AND (date_from < p_2.end_date))
     575                                                                          Heap Blocks: exact=22223
     576                                                                          Buffers: shared hit=23114
     577                                                                          ->  Bitmap Index Scan on idx_bookings_date_owner  (cost=0.00..1515.57 rows=111114 width=0) (actual time=35.509..35.509 rows=1000017 loops=1)
     578                                                                                Index Cond: ((date_from >= p_2.start_date) AND (date_from < p_2.end_date))
     579                                                                                Buffers: shared hit=891
     580                                                              ->  Hash  (cost=5088.18..5088.18 rows=167318 width=78) (actual time=117.367..117.368 rows=167319 loops=1)
     581                                                                    Buckets: 131072  Batches: 4  Memory Usage: 5507kB
     582                                                                    Buffers: shared hit=3415, temp written=1501
     583                                                                    ->  Seq Scan on reviews r  (cost=0.00..5088.18 rows=167318 width=78) (actual time=0.026..42.889 rows=167319 loops=1)
     584                                                                          Buffers: shared hit=3415
     585Planning:
     586  Buffers: shared hit=657
     587Planning Time: 4.960 ms
     588JIT:
     589  Functions: 81
     590  Options: Inlining false, Optimization false, Expressions true, Deforming true
     591  Timing: Generation 6.320 ms (Deform 2.740 ms), Inlining 0.000 ms, Optimization 2.235 ms, Emission 55.696 ms, Total 64.251 ms
     592Execution Time: 3709.157 ms
     593
     594}}}
     595
     596'''Execution time:''' 3709.157 ms
     597
     598Instead of improving performance, adding these indexes actually '''increased''' the execution time by roughly 1 second, therefore we are '''not''' keeping the indexes.
     599
     600=== 4. Highest Paying Customers Analytics (Phase 6 Query) ===
     601
     602Benchmark query:
     603{{{
     604#!sql
     605EXPLAIN (ANALYZE, BUFFERS)
     606WITH params AS (
     607    SELECT
     608        (CURRENT_DATE - INTERVAL '1 year')::DATE AS start_date,
     609        CURRENT_DATE::DATE AS end_date
     610),
     611owner_reports AS (
     612    SELECT
     613        b.owner_id,
     614        SUM(pay.amount) AS total_profit_generated,
     615        COUNT(DISTINCT b.booking_id) AS successful_bookings
     616    FROM project.bookings b
     617    JOIN project.payments pay ON b.booking_id = pay.booking_id
     618    JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date
     619    WHERE b.status = 'Completed'
     620    GROUP BY b.owner_id
     621),
     622service_counts AS (
     623    SELECT
     624        b.owner_id,
     625        s.type AS service_type,
     626        COUNT(bs.service_id) AS times_booked,
     627        ROW_NUMBER() OVER(PARTITION BY b.owner_id ORDER BY COUNT(bs.service_id) DESC) as rank_num
     628    FROM project.bookings b
     629    JOIN project.booking_services bs ON b.booking_id = bs.booking_id
     630    JOIN project.services s ON bs.service_id = s.service_id
     631    JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date
     632    GROUP BY b.owner_id, s.type
     633),
     634favorite_service AS (
     635    SELECT owner_id, service_type AS top_interest
     636    FROM service_counts
     637    WHERE rank_num = 1
     638),
     639pet_portfolio AS (
     640    SELECT owner_id, COUNT(pet_id) AS registered_pets
     641    FROM project.pets
     642    GROUP BY owner_id
     643)
     644SELECT
     645    u.user_id, u.first_name, u.last_name,
     646    COALESCE(ofin.successful_bookings, 0) AS successful_bookings,
     647    COALESCE(pp.registered_pets, 0) AS total_pets,
     648    COALESCE(fs.top_interest, 'Unknown') AS top_interest,
     649    COALESCE(ofin.total_profit_generated, 0) AS total_profit_generated,
     650    DENSE_RANK() OVER (
     651        ORDER BY
     652            COALESCE(ofin.total_profit_generated, 0) DESC,
     653            COALESCE(ofin.successful_bookings, 0) DESC
     654    ) AS customer_rank
     655FROM project.users u
     656JOIN project.pet_owners po ON u.user_id = po.user_id
     657LEFT JOIN owner_reports ofin ON po.user_id = ofin.owner_id
     658LEFT JOIN favorite_service fs ON po.user_id = fs.owner_id
     659LEFT JOIN pet_portfolio pp ON po.user_id = pp.owner_id
     660WHERE COALESCE(ofin.successful_bookings, 0) > 0
     661ORDER BY customer_rank
     662LIMIT 10;
     663}}}
     664
     665Execution without indexes:
     666{{{
     667Limit  (cost=161052.28..161052.30 rows=10 width=115) (actual time=5023.716..5023.735 rows=2 loops=1)
     668  Buffers: shared hit=730190, temp read=26867 written=26872
     669  CTE params
     670    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=1)
     671  ->  Sort  (cost=161052.26..161052.83 rows=230 width=115) (actual time=4971.843..4971.859 rows=2 loops=1)
    267672        Sort Key: (dense_rank() OVER (?))
    268673        Sort Method: quicksort  Memory: 25kB
    269         Buffers: shared hit=22278 read=25633
    270         ->  WindowAgg  (cost=67177.44..67201.58 rows=690 width=115) (actual time=403.716..412.514 rows=4 loops=1)
    271               Buffers: shared hit=22275 read=25633
    272               ->  Sort  (cost=67177.43..67179.15 rows=690 width=91) (actual time=403.675..412.445 rows=4 loops=1)
    273                     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
     674        Buffers: shared hit=730190, temp read=26867 written=26872
     675        ->  WindowAgg  (cost=161042.71..161047.29 rows=230 width=115) (actual time=4971.808..4971.835 rows=2 loops=1)
     676              Buffers: shared hit=730190, temp read=26867 written=26872
     677              ->  Sort  (cost=161042.69..161043.26 rows=230 width=349) (actual time=4971.773..4971.789 rows=2 loops=1)
     678                    Sort Key: (COALESCE((sum(pay.amount)), '0'::bigint)) DESC, (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint)) DESC
    274679                    Sort Method: quicksort  Memory: 25kB
    275                     Buffers: shared hit=22275 read=25633
    276                     ->  Hash Join  (cost=67125.00..67144.89 rows=690 width=91) (actual time=403.585..412.387 rows=4 loops=1)
    277                           Hash Cond: ((ps.user_id)::text = (u.user_id)::text)
    278                           Buffers: shared hit=22272 read=25633
    279                           ->  Merge Left Join  (cost=67101.95..67107.94 rows=690 width=122) (actual time=403.503..412.297 rows=4 loops=1)
    280                                 Merge Cond: ((ps.user_id)::text = (b_1.sitter_id)::text)
    281                                 Buffers: shared hit=22272 read=25632
    282                                 ->  Merge Left Join  (cost=34564.07..34567.93 rows=690 width=106) (actual time=212.894..213.031 rows=4 loops=1)
    283                                       Merge Cond: ((ps.user_id)::text = (b.sitter_id)::text)
    284                                       Buffers: shared hit=24 read=22225
    285                                       ->  Sort  (cost=49.44..51.16 rows=690 width=90) (actual time=0.049..0.051 rows=4 loops=1)
    286                                             Sort Key: ps.user_id
    287                                             Sort Method: quicksort  Memory: 25kB
    288                                             Buffers: shared read=1
    289                                             ->  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)
    290                                                   Buffers: shared read=1
    291                                       ->  Finalize GroupAggregate  (cost=34514.63..34515.02 rows=1 width=61) (actual time=212.840..212.970 rows=4 loops=1)
    292                                             Group Key: b.sitter_id
    293                                             Buffers: shared hit=24 read=22224
    294                                             ->  Gather Merge  (cost=34514.63..34514.99 rows=3 width=53) (actual time=212.822..212.945 rows=7 loops=1)
    295                                                   Workers Planned: 3
    296                                                   Workers Launched: 3
    297                                                   Buffers: shared hit=24 read=22224
    298                                                   ->  Sort  (cost=33514.59..33514.60 rows=1 width=53) (actual time=206.606..206.608 rows=2 loops=4)
    299                                                         Sort Key: b.sitter_id
    300                                                         Sort Method: quicksort  Memory: 25kB
    301                                                         Buffers: shared hit=24 read=22224
    302                                                         Worker 0:  Sort Method: quicksort  Memory: 25kB
    303                                                         Worker 1:  Sort Method: quicksort  Memory: 25kB
    304                                                         Worker 2:  Sort Method: quicksort  Memory: 25kB
    305                                                         ->  Partial HashAggregate  (cost=33514.57..33514.58 rows=1 width=53) (actual time=206.527..206.529 rows=2 loops=4)
    306                                                               Group Key: b.sitter_id
    307                                                               Batches: 1  Memory Usage: 24kB
    308                                                               Buffers: shared read=22224
    309                                                               Worker 0:  Batches: 1  Memory Usage: 24kB
    310                                                               Worker 1:  Batches: 1  Memory Usage: 24kB
    311                                                               Worker 2:  Batches: 1  Memory Usage: 24kB
    312                                                               ->  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)
    313                                                                     Filter: ((date_from <= CURRENT_DATE) AND (date_from >= (CURRENT_DATE - '1 year'::interval)))
    314                                                                     Rows Removed by Filter: 1
    315                                                                     Buffers: shared read=22224
    316                                 ->  Finalize GroupAggregate  (cost=32537.88..32538.27 rows=1 width=61) (actual time=190.603..199.257 rows=4 loops=1)
    317                                       Group Key: b_1.sitter_id
    318                                       Buffers: shared hit=22248 read=3407
    319                                       ->  Gather Merge  (cost=32537.88..32538.24 rows=3 width=69) (actual time=190.572..199.221 rows=7 loops=1)
    320                                             Workers Planned: 3
    321                                             Workers Launched: 3
    322                                             Buffers: shared hit=22248 read=3407
    323                                             ->  Sort  (cost=31537.84..31537.84 rows=1 width=69) (actual time=184.406..184.410 rows=2 loops=4)
    324                                                   Sort Key: b_1.sitter_id
     680                    Buffers: shared hit=730190, temp read=26867 written=26872
     681                    ->  Hash Join  (cost=160830.10..161033.66 rows=230 width=349) (actual time=4755.875..4971.782 rows=2 loops=1)
     682                          Hash Cond: ((po.user_id)::text = (u.user_id)::text)
     683                          Buffers: shared hit=730190, temp read=26867 written=26872
     684                          ->  Merge Left Join  (cost=160807.05..161010.01 rows=230 width=388) (actual time=4755.799..4971.700 rows=2 loops=1)
     685                                Merge Cond: ((po.user_id)::text = (pets.owner_id)::text)
     686                                Buffers: shared hit=730189, temp read=26867 written=26872
     687                                ->  Merge Left Join  (cost=160791.71..160990.09 rows=230 width=380) (actual time=4755.644..4971.535 rows=2 loops=1)
     688                                      Merge Cond: ((po.user_id)::text = (service_counts.owner_id)::text)
     689                                      Buffers: shared hit=730188, temp read=26867 written=26872
     690                                      ->  Merge Left Join  (cost=57602.12..57793.76 rows=230 width=106) (actual time=2088.524..2304.386 rows=2 loops=1)
     691                                            Merge Cond: ((po.user_id)::text = (b.owner_id)::text)
     692                                            Filter: (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint) > 0)
     693                                            Rows Removed by Filter: 5
     694                                            Buffers: shared hit=691484, temp read=1865 written=1870
     695                                            ->  Sort  (cost=49.44..51.16 rows=690 width=90) (actual time=0.023..0.027 rows=7 loops=1)
     696                                                  Sort Key: po.user_id
    325697                                                  Sort Method: quicksort  Memory: 25kB
    326                                                   Buffers: shared hit=22248 read=3407
    327                                                   Worker 0:  Sort Method: quicksort  Memory: 25kB
    328                                                   Worker 1:  Sort Method: quicksort  Memory: 25kB
    329                                                   Worker 2:  Sort Method: quicksort  Memory: 25kB
    330                                                   ->  Partial HashAggregate  (cost=31537.82..31537.83 rows=1 width=69) (actual time=184.336..184.340 rows=2 loops=4)
    331                                                         Group Key: b_1.sitter_id
    332                                                         Batches: 1  Memory Usage: 24kB
    333                                                         Buffers: shared hit=22224 read=3407
    334                                                         Worker 0:  Batches: 1  Memory Usage: 24kB
    335                                                         Worker 1:  Batches: 1  Memory Usage: 24kB
    336                                                         Worker 2:  Batches: 1  Memory Usage: 24kB
    337                                                         ->  Parallel Hash Join  (cost=4971.92..31268.59 rows=53846 width=41) (actual time=43.109..172.841 rows=41731 loops=4)
    338                                                               Hash Cond: ((b_1.booking_id)::text = (r.booking_id)::text)
    339                                                               Buffers: shared hit=22224 read=3407
    340                                                               ->  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)
    341                                                                     Buffers: shared hit=22224
    342                                                               ->  Parallel Hash  (cost=4102.52..4102.52 rows=69552 width=41) (actual time=42.288..42.289 rows=41731 loops=4)
    343                                                                     Buckets: 262144  Batches: 1  Memory Usage: 15168kB
    344                                                                     Buffers: shared read=3407
    345                                                                     ->  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)
    346                                                                           Buffers: shared read=3407
    347                           ->  Hash  (cost=15.80..15.80 rows=580 width=64) (actual time=0.049..0.050 rows=12 loops=1)
    348                                 Buckets: 1024  Batches: 1  Memory Usage: 10kB
    349                                 Buffers: shared read=1
    350                                 ->  Seq Scan on users u  (cost=0.00..15.80 rows=580 width=64) (actual time=0.029..0.032 rows=12 loops=1)
    351                                       Buffers: shared read=1
     698                                                  Buffers: shared hit=1
     699                                                  ->  Seq Scan on pet_owners po  (cost=0.00..16.90 rows=690 width=90) (actual time=0.009..0.011 rows=7 loops=1)
     700                                                        Buffers: shared hit=1
     701                                            ->  Materialize  (cost=57552.69..57740.86 rows=1 width=53) (actual time=2088.462..2304.313 rows=2 loops=1)
     702                                                  Buffers: shared hit=691483, temp read=1865 written=1870
     703                                                  ->  GroupAggregate  (cost=57552.69..57740.85 rows=1 width=53) (actual time=2088.458..2304.305 rows=2 loops=1)
     704                                                        Group Key: b.owner_id
     705                                                        Buffers: shared hit=691483, temp read=1865 written=1870
     706                                                        ->  Sort  (cost=57552.69..57599.73 rows=18815 width=78) (actual time=2088.388..2265.596 rows=167315 loops=1)
     707                                                              Sort Key: b.owner_id, b.booking_id
     708                                                              Sort Method: external merge  Disk: 14920kB
     709                                                              Buffers: shared hit=691483, temp read=1865 written=1870
     710                                                              ->  Nested Loop  (cost=0.42..56216.86 rows=18815 width=78) (actual time=0.077..1445.651 rows=167315 loops=1)
     711                                                                    Buffers: shared hit=691483
     712                                                                    ->  Nested Loop  (cost=0.00..37263.35 rows=18815 width=74) (actual time=0.043..218.948 rows=167315 loops=1)
     713                                                                          Join Filter: ((b.date_from >= p.start_date) AND (b.date_from < p.end_date))
     714                                                                          Rows Removed by Join Filter: 3
     715                                                                          Buffers: shared hit=22223
     716                                                                          ->  CTE Scan on params p  (cost=0.00..0.02 rows=1 width=8) (actual time=0.015..0.017 rows=1 loops=1)
     717                                                                          ->  Seq Scan on bookings b  (cost=0.00..34723.28 rows=169337 width=78) (actual time=0.015..182.152 rows=167318 loops=1)
     718                                                                                Filter: ((status)::text = 'Completed'::text)
     719                                                                                Rows Removed by Filter: 832704
     720                                                                                Buffers: shared hit=22223
     721                                                                    ->  Index Scan using payments_booking_id_key on payments pay  (cost=0.42..1.01 rows=1 width=41) (actual time=0.007..0.007 rows=1 loops=167315)
     722                                                                          Index Cond: ((booking_id)::text = (b.booking_id)::text)
     723                                                                          Buffers: shared hit=669260
     724                                      ->  Materialize  (cost=103189.58..103195.74 rows=1 width=311) (actual time=2667.097..2667.120 rows=2 loops=1)
     725                                            Buffers: shared hit=38704, temp read=25002 written=25002
     726                                            ->  Subquery Scan on service_counts  (cost=103189.58..103195.74 rows=1 width=311) (actual time=2667.092..2667.114 rows=2 loops=1)
     727                                                  Filter: (service_counts.rank_num = 1)
     728                                                  Buffers: shared hit=38704, temp read=25002 written=25002
     729                                                  ->  WindowAgg  (cost=103189.58..103193.36 rows=190 width=327) (actual time=2667.085..2667.106 rows=2 loops=1)
     730                                                        Run Condition: (row_number() OVER (?) <= 1)
     731                                                        Buffers: shared hit=38704, temp read=25002 written=25002
     732                                                        ->  Sort  (cost=103189.56..103190.04 rows=190 width=319) (actual time=2667.062..2667.070 rows=5 loops=1)
     733                                                              Sort Key: b_1.owner_id, (count(bs.service_id)) DESC
     734                                                              Sort Method: quicksort  Memory: 25kB
     735                                                              Buffers: shared hit=38704, temp read=25002 written=25002
     736                                                              ->  HashAggregate  (cost=103180.47..103182.37 rows=190 width=319) (actual time=2667.034..2667.043 rows=5 loops=1)
     737                                                                    Group Key: b_1.owner_id, s.type
     738                                                                    Batches: 1  Memory Usage: 40kB
     739                                                                    Buffers: shared hit=38704, temp read=25002 written=25002
     740                                                                    ->  Hash Join  (cost=44715.49..102347.12 rows=111114 width=348) (actual time=1301.306..2326.037 rows=1000017 loops=1)
     741                                                                          Hash Cond: ((bs.service_id)::text = (s.service_id)::text)
     742                                                                          Buffers: shared hit=38704, temp read=25002 written=25002
     743                                                                          ->  Hash Join  (cost=44701.22..102034.66 rows=111114 width=74) (actual time=1301.240..2045.345 rows=1000017 loops=1)
     744                                                                                Hash Cond: ((bs.booking_id)::text = (b_1.booking_id)::text)
     745                                                                                Buffers: shared hit=38703, temp read=25002 written=25002
     746                                                                                ->  Seq Scan on booking_services bs  (cost=0.00..25669.22 rows=1000022 width=74) (actual time=0.017..158.668 rows=1000022 loops=1)
     747                                                                                      Buffers: shared hit=15669
     748                                                                                ->  Hash  (cost=41901.29..41901.29 rows=111114 width=74) (actual time=844.625..844.628 rows=1000017 loops=1)
     749                                                                                      Buckets: 131072 (originally 131072)  Batches: 16 (originally 2)  Memory Usage: 7550kB
     750                                                                                      Buffers: shared hit=23034, temp written=10744
     751                                                                                      ->  Nested Loop  (cost=16900.42..41901.29 rows=111114 width=74) (actual time=41.046..409.694 rows=1000017 loops=1)
     752                                                                                            Buffers: shared hit=23034
     753                                                                                            ->  CTE Scan on params p_1  (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.004 rows=1 loops=1)
     754                                                                                            ->  Bitmap Heap Scan on bookings b_1  (cost=16900.42..40790.13 rows=111114 width=78) (actual time=41.023..223.873 rows=1000017 loops=1)
     755                                                                                                  Recheck Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date))
     756                                                                                                  Heap Blocks: exact=22223
     757                                                                                                  Buffers: shared hit=23034
     758                                                                                                  ->  Bitmap Index Scan on idx_bookings_owner_date  (cost=0.00..16872.65 rows=111114 width=0) (actual time=36.458..36.459 rows=1000017 loops=1)
     759                                                                                                        Index Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date))
     760                                                                                                        Buffers: shared hit=811
     761                                                                          ->  Hash  (cost=11.90..11.90 rows=190 width=364) (actual time=0.037..0.038 rows=4 loops=1)
     762                                                                                Buckets: 1024  Batches: 1  Memory Usage: 9kB
     763                                                                                Buffers: shared hit=1
     764                                                                                ->  Seq Scan on services s  (cost=0.00..11.90 rows=190 width=364) (actual time=0.026..0.027 rows=4 loops=1)
     765                                                                                      Buffers: shared hit=1
     766                                ->  GroupAggregate  (cost=15.34..17.44 rows=120 width=98) (actual time=0.103..0.115 rows=7 loops=1)
     767                                      Group Key: pets.owner_id
     768                                      Buffers: shared hit=1
     769                                      ->  Sort  (cost=15.34..15.64 rows=120 width=180) (actual time=0.078..0.081 rows=23 loops=1)
     770                                            Sort Key: pets.owner_id
     771                                            Sort Method: quicksort  Memory: 27kB
     772                                            Buffers: shared hit=1
     773                                            ->  Seq Scan on pets  (cost=0.00..11.20 rows=120 width=180) (actual time=0.044..0.051 rows=23 loops=1)
     774                                                  Buffers: shared hit=1
     775                          ->  Hash  (cost=15.80..15.80 rows=580 width=51) (actual time=0.044..0.045 rows=12 loops=1)
     776                                Buckets: 1024  Batches: 1  Memory Usage: 9kB
     777                                Buffers: shared hit=1
     778                                ->  Seq Scan on users u  (cost=0.00..15.80 rows=580 width=51) (actual time=0.027..0.030 rows=12 loops=1)
     779                                      Buffers: shared hit=1
    352780Planning:
    353   Buffers: shared hit=402 read=29
    354 Planning Time: 3.607 ms
    355 Execution Time: 413.138 ms
    356 }}}
    357 
    358 '''Average execution time (10 attempts):''' 410.225ms
    359 
    360 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.
    361 
    362 == 4. Search for available sitters with highest rankings ==
    363 
    364 Benchmark query:
    365 {{{
    366 #!sql
    367 EXPLAIN (ANALYZE, BUFFERS)
    368 WITH requested_dates AS (
    369     SELECT
    370         (CURRENT_DATE + INTERVAL '10 days')::date AS req_start,
    371         (CURRENT_DATE + INTERVAL '15 days')::date AS req_end
    372 ),
    373 unavailable_sitters AS (
    374     SELECT DISTINCT b.sitter_id
    375     FROM project.bookings b
    376     JOIN requested_dates rd ON true
    377     WHERE b.status IN ('Confirmed', 'Pending')
    378       AND b.date_from <= rd.req_end
    379       AND b.date_to >= rd.req_start
    380 ),
    381 highly_rated_sitters AS (
    382     SELECT b.sitter_id
    383     FROM project.reviews r
    384     JOIN project.bookings b ON r.booking_id = b.booking_id
    385     GROUP BY b.sitter_id
    386     HAVING AVG(r.rating) >= 4.0
    387 )
    388 SELECT
    389     u.username,
    390     u.first_name,
    391     u.last_name,
    392     u.email
    393 FROM project.pet_sitters ps
    394 JOIN project.users u ON ps.user_id = u.user_id
    395 JOIN highly_rated_sitters hrs ON hrs.sitter_id = ps.user_id
    396 LEFT JOIN unavailable_sitters us ON us.sitter_id = ps.user_id
    397 WHERE us.sitter_id IS NULL
    398 LIMIT 20;
    399 }}}
    400 
    401 Execution without indexes:
    402 
    403 {{{
    404 Limit  (cost=66246.09..66295.07 rows=1 width=51) (actual time=279.739..287.004 rows=3 loops=1)
    405   Buffers: shared hit=47892 read=2
    406   ->  Nested Loop Anti Join  (cost=66246.09..66295.07 rows=1 width=51) (actual time=279.736..287.000 rows=3 loops=1)
    407         Join Filter: ((b_1.sitter_id)::text = (ps.user_id)::text)
    408         Buffers: shared hit=47892 read=2
    409         ->  Nested Loop  (cost=32537.88..32586.84 rows=1 width=141) (actual time=184.771..188.940 rows=3 loops=1)
    410               Join Filter: ((ps.user_id)::text = (u.user_id)::text)
    411               Rows Removed by Join Filter: 20
    412               Buffers: shared hit=25659 read=2
    413               ->  Nested Loop  (cost=32537.88..32563.79 rows=1 width=127) (actual time=184.747..188.897 rows=3 loops=1)
    414                     Join Filter: ((ps.user_id)::text = (b.sitter_id)::text)
    415                     Rows Removed by Join Filter: 3
    416                     Buffers: shared hit=25657 read=1
    417                     ->  Finalize GroupAggregate  (cost=32537.88..32538.27 rows=1 width=37) (actual time=184.705..188.834 rows=3 loops=1)
    418                           Group Key: b.sitter_id
    419                           Filter: (avg(r.rating) >= 4.0)
    420                           Rows Removed by Filter: 1
    421                           Buffers: shared hit=25655
    422                           ->  Gather Merge  (cost=32537.88..32538.24 rows=3 width=69) (actual time=184.688..188.798 rows=7 loops=1)
    423                                 Workers Planned: 3
    424                                 Workers Launched: 3
    425                                 Buffers: shared hit=25655
    426                                 ->  Sort  (cost=31537.84..31537.84 rows=1 width=69) (actual time=178.566..178.571 rows=2 loops=4)
    427                                       Sort Key: b.sitter_id
    428                                       Sort Method: quicksort  Memory: 25kB
    429                                       Buffers: shared hit=25655
    430                                       Worker 0:  Sort Method: quicksort  Memory: 25kB
    431                                       Worker 1:  Sort Method: quicksort  Memory: 25kB
    432                                       Worker 2:  Sort Method: quicksort  Memory: 25kB
    433                                       ->  Partial HashAggregate  (cost=31537.82..31537.83 rows=1 width=69) (actual time=178.495..178.500 rows=2 loops=4)
    434                                             Group Key: b.sitter_id
    435                                             Batches: 1  Memory Usage: 24kB
    436                                             Buffers: shared hit=25631
    437                                             Worker 0:  Batches: 1  Memory Usage: 24kB
    438                                             Worker 1:  Batches: 1  Memory Usage: 24kB
    439                                             Worker 2:  Batches: 1  Memory Usage: 24kB
    440                                             ->  Parallel Hash Join  (cost=4971.92..31268.59 rows=53846 width=41) (actual time=36.863..167.092 rows=41731 loops=4)
    441                                                   Hash Cond: ((b.booking_id)::text = (r.booking_id)::text)
    442                                                   Buffers: shared hit=25631
    443                                                   ->  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)
    444                                                         Buffers: shared hit=22224
    445                                                   ->  Parallel Hash  (cost=4102.52..4102.52 rows=69552 width=41) (actual time=36.033..36.034 rows=41731 loops=4)
    446                                                         Buckets: 262144  Batches: 1  Memory Usage: 15200kB
    447                                                         Buffers: shared hit=3407
    448                                                         ->  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)
    449                                                               Buffers: shared hit=3407
    450                     ->  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)
    451                           Buffers: shared hit=2 read=1
    452               ->  Seq Scan on users u  (cost=0.00..15.80 rows=580 width=88) (actual time=0.009..0.009 rows=8 loops=3)
    453                     Buffers: shared hit=2 read=1
    454         ->  Unique  (cost=33708.21..33708.22 rows=1 width=37) (actual time=31.654..32.684 rows=0 loops=3)
    455               Buffers: shared hit=22233
    456               ->  Sort  (cost=33708.21..33708.22 rows=1 width=37) (actual time=31.654..32.684 rows=0 loops=3)
    457                     Sort Key: b_1.sitter_id
     781  Buffers: shared hit=34
     782Planning Time: 1.751 ms
     783JIT:
     784  Functions: 86
     785  Options: Inlining false, Optimization false, Expressions true, Deforming true
     786  Timing: Generation 4.189 ms (Deform 1.818 ms), Inlining 0.000 ms, Optimization 1.556 ms, Emission 50.729 ms, Total 56.474 ms
     787Execution Time: 5033.000 ms
     788
     789}}}
     790
     791'''Execution time:''' 5033.000 ms
     792
     793Execution with the indexes we created in Scenario 3:
     794{{{
     795Limit  (cost=133693.17..133693.19 rows=10 width=115) (actual time=11494.356..11494.375 rows=2 loops=1)
     796  Buffers: shared hit=4708355 read=7205, temp read=1865 written=1870
     797  CTE params
     798    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1)
     799  ->  Sort  (cost=133693.15..133693.72 rows=230 width=115) (actual time=11443.882..11443.898 rows=2 loops=1)
     800        Sort Key: (dense_rank() OVER (?))
     801        Sort Method: quicksort  Memory: 25kB
     802        Buffers: shared hit=4708355 read=7205, temp read=1865 written=1870
     803        ->  WindowAgg  (cost=133683.60..133688.18 rows=230 width=115) (actual time=11443.849..11443.877 rows=2 loops=1)
     804              Buffers: shared hit=4708355 read=7205, temp read=1865 written=1870
     805              ->  Sort  (cost=133683.58..133684.15 rows=230 width=349) (actual time=11443.814..11443.830 rows=2 loops=1)
     806                    Sort Key: (COALESCE((sum(pay.amount)), '0'::bigint)) DESC, (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint)) DESC
    458807                    Sort Method: quicksort  Memory: 25kB
    459                     Buffers: shared hit=22233
    460                     ->  Gather  (cost=1000.00..33708.20 rows=1 width=37) (actual time=94.936..98.026 rows=0 loops=1)
    461                           Workers Planned: 3
    462                           Workers Launched: 3
    463                           Buffers: shared hit=22233
    464                           ->  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)
    465                                 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))
    466                                 Rows Removed by Filter: 250006
    467                                 Buffers: shared hit=22233
     808                    Buffers: shared hit=4708355 read=7205, temp read=1865 written=1870
     809                    ->  Hash Join  (cost=133474.23..133674.56 rows=230 width=349) (actual time=11228.070..11443.823 rows=2 loops=1)
     810                          Hash Cond: ((po.user_id)::text = (u.user_id)::text)
     811                          Buffers: shared hit=4708355 read=7205, temp read=1865 written=1870
     812                          ->  Merge Left Join  (cost=133451.18..133650.90 rows=230 width=388) (actual time=11227.999..11443.746 rows=2 loops=1)
     813                                Merge Cond: ((po.user_id)::text = (pets.owner_id)::text)
     814                                Buffers: shared hit=4708354 read=7205, temp read=1865 written=1870
     815                                ->  Merge Left Join  (cost=133449.43..133647.81 rows=230 width=380) (actual time=11227.862..11443.598 rows=2 loops=1)
     816                                      Merge Cond: ((po.user_id)::text = (service_counts.owner_id)::text)
     817                                      Buffers: shared hit=4708353 read=7205, temp read=1865 written=1870
     818                                      ->  Merge Left Join  (cost=45302.27..45493.91 rows=230 width=106) (actual time=2137.209..2352.916 rows=2 loops=1)
     819                                            Merge Cond: ((po.user_id)::text = (b.owner_id)::text)
     820                                            Filter: (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint) > 0)
     821                                            Rows Removed by Filter: 5
     822                                            Buffers: shared hit=692375, temp read=1865 written=1870
     823                                            ->  Sort  (cost=49.44..51.16 rows=690 width=90) (actual time=0.021..0.025 rows=7 loops=1)
     824                                                  Sort Key: po.user_id
     825                                                  Sort Method: quicksort  Memory: 25kB
     826                                                  Buffers: shared hit=1
     827                                                  ->  Seq Scan on pet_owners po  (cost=0.00..16.90 rows=690 width=90) (actual time=0.009..0.011 rows=7 loops=1)
     828                                                        Buffers: shared hit=1
     829                                            ->  Materialize  (cost=45252.84..45441.01 rows=1 width=53) (actual time=2137.153..2352.849 rows=2 loops=1)
     830                                                  Buffers: shared hit=692374, temp read=1865 written=1870
     831                                                  ->  GroupAggregate  (cost=45252.84..45441.00 rows=1 width=53) (actual time=2137.148..2352.840 rows=2 loops=1)
     832                                                        Group Key: b.owner_id
     833                                                        Buffers: shared hit=692374, temp read=1865 written=1870
     834                                                        ->  Sort  (cost=45252.84..45299.88 rows=18815 width=78) (actual time=2137.081..2313.817 rows=167315 loops=1)
     835                                                              Sort Key: b.owner_id, b.booking_id
     836                                                              Sort Method: external merge  Disk: 14920kB
     837                                                              Buffers: shared hit=692374, temp read=1865 written=1870
     838                                                              ->  Nested Loop  (cost=1520.69..43917.01 rows=18815 width=78) (actual time=40.128..1500.355 rows=167315 loops=1)
     839                                                                    Buffers: shared hit=692374
     840                                                                    ->  Nested Loop  (cost=1520.27..25875.93 rows=18815 width=74) (actual time=40.077..281.535 rows=167315 loops=1)
     841                                                                          Buffers: shared hit=23114
     842                                                                          ->  CTE Scan on params p  (cost=0.00..0.02 rows=1 width=8) (actual time=0.016..0.018 rows=1 loops=1)
     843                                                                          ->  Bitmap Heap Scan on bookings b  (cost=1520.27..25687.76 rows=18815 width=78) (actual time=40.052..253.659 rows=167315 loops=1)
     844                                                                                Recheck Cond: ((date_from >= p.start_date) AND (date_from < p.end_date))
     845                                                                                Filter: ((status)::text = 'Completed'::text)
     846                                                                                Rows Removed by Filter: 832702
     847                                                                                Heap Blocks: exact=22223
     848                                                                                Buffers: shared hit=23114
     849                                                                                ->  Bitmap Index Scan on idx_bookings_date_owner  (cost=0.00..1515.57 rows=111114 width=0) (actual time=35.462..35.463 rows=1000017 loops=1)
     850                                                                                      Index Cond: ((date_from >= p.start_date) AND (date_from < p.end_date))
     851                                                                                      Buffers: shared hit=891
     852                                                                    ->  Index Scan using idx_payments_booking_id on payments pay  (cost=0.42..0.96 rows=1 width=41) (actual time=0.007..0.007 rows=1 loops=167315)
     853                                                                          Index Cond: ((booking_id)::text = (b.booking_id)::text)
     854                                                                          Buffers: shared hit=669260
     855                                      ->  Materialize  (cost=88147.15..88153.31 rows=1 width=311) (actual time=9090.628..9090.652 rows=2 loops=1)
     856                                            Buffers: shared hit=4015978 read=7205
     857                                            ->  Subquery Scan on service_counts  (cost=88147.15..88153.31 rows=1 width=311) (actual time=9090.623..9090.645 rows=2 loops=1)
     858                                                  Filter: (service_counts.rank_num = 1)
     859                                                  Buffers: shared hit=4015978 read=7205
     860                                                  ->  WindowAgg  (cost=88147.15..88150.93 rows=190 width=327) (actual time=9090.616..9090.636 rows=2 loops=1)
     861                                                        Run Condition: (row_number() OVER (?) <= 1)
     862                                                        Buffers: shared hit=4015978 read=7205
     863                                                        ->  Sort  (cost=88147.13..88147.61 rows=190 width=319) (actual time=9090.592..9090.598 rows=5 loops=1)
     864                                                              Sort Key: b_1.owner_id, (count(bs.service_id)) DESC
     865                                                              Sort Method: quicksort  Memory: 25kB
     866                                                              Buffers: shared hit=4015978 read=7205
     867                                                              ->  HashAggregate  (cost=88138.04..88139.94 rows=190 width=319) (actual time=9090.565..9090.573 rows=5 loops=1)
     868                                                                    Group Key: b_1.owner_id, s.type
     869                                                                    Batches: 1  Memory Usage: 40kB
     870                                                                    Buffers: shared hit=4015978 read=7205
     871                                                                    ->  Hash Join  (cost=1558.04..87304.69 rows=111114 width=348) (actual time=40.705..8657.362 rows=1000017 loops=1)
     872                                                                          Hash Cond: ((bs.service_id)::text = (s.service_id)::text)
     873                                                                          Buffers: shared hit=4015978 read=7205
     874                                                                          ->  Nested Loop  (cost=1543.77..86992.23 rows=111114 width=74) (actual time=40.620..8294.445 rows=1000017 loops=1)
     875                                                                                Buffers: shared hit=4015977 read=7205
     876                                                                                ->  Nested Loop  (cost=1543.34..26544.21 rows=111114 width=74) (actual time=40.489..464.589 rows=1000017 loops=1)
     877                                                                                      Buffers: shared hit=23114
     878                                                                                      ->  CTE Scan on params p_1  (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.003 rows=1 loops=1)
     879                                                                                      ->  Bitmap Heap Scan on bookings b_1  (cost=1543.34..25433.05 rows=111114 width=78) (actual time=40.468..260.041 rows=1000017 loops=1)
     880                                                                                            Recheck Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date))
     881                                                                                            Heap Blocks: exact=22223
     882                                                                                            Buffers: shared hit=23114
     883                                                                                            ->  Bitmap Index Scan on idx_bookings_date_owner  (cost=0.00..1515.57 rows=111114 width=0) (actual time=35.914..35.914 rows=1000017 loops=1)
     884                                                                                                  Index Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date))
     885                                                                                                  Buffers: shared hit=891
     886                                                                                ->  Index Scan using idx_booking_services_booking_id on booking_services bs  (cost=0.42..0.53 rows=1 width=74) (actual time=0.007..0.007 rows=1 loops=1000017)
     887                                                                                      Index Cond: ((booking_id)::text = (b_1.booking_id)::text)
     888                                                                                      Buffers: shared hit=3992863 read=7205
     889                                                                          ->  Hash  (cost=11.90..11.90 rows=190 width=364) (actual time=0.054..0.055 rows=4 loops=1)
     890                                                                                Buckets: 1024  Batches: 1  Memory Usage: 9kB
     891                                                                                Buffers: shared hit=1
     892                                                                                ->  Seq Scan on services s  (cost=0.00..11.90 rows=190 width=364) (actual time=0.041..0.043 rows=4 loops=1)
     893                                                                                      Buffers: shared hit=1
     894                                ->  GroupAggregate  (cost=1.75..2.15 rows=23 width=98) (actual time=0.109..0.122 rows=7 loops=1)
     895                                      Group Key: pets.owner_id
     896                                      Buffers: shared hit=1
     897                                      ->  Sort  (cost=1.75..1.81 rows=23 width=180) (actual time=0.084..0.088 rows=23 loops=1)
     898                                            Sort Key: pets.owner_id
     899                                            Sort Method: quicksort  Memory: 27kB
     900                                            Buffers: shared hit=1
     901                                            ->  Seq Scan on pets  (cost=0.00..1.23 rows=23 width=180) (actual time=0.049..0.056 rows=23 loops=1)
     902                                                  Buffers: shared hit=1
     903                          ->  Hash  (cost=15.80..15.80 rows=580 width=51) (actual time=0.046..0.047 rows=12 loops=1)
     904                                Buckets: 1024  Batches: 1  Memory Usage: 9kB
     905                                Buffers: shared hit=1
     906                                ->  Seq Scan on users u  (cost=0.00..15.80 rows=580 width=51) (actual time=0.030..0.033 rows=12 loops=1)
     907                                      Buffers: shared hit=1
    468908Planning:
    469   Buffers: shared hit=11 read=8
    470 Planning Time: 1.269 ms
    471 Execution Time: 287.147 ms
    472 }}}
    473 
    474 '''Average execution time (10 attempts):''' 283.421ms
    475 
    476 We add this index to optimize the date overlapping:
    477 {{{
    478 #!sql
    479 CREATE INDEX idx_bookings_sitter_status_dates
    480 ON project.bookings (sitter_id, status, date_from, date_to);
    481 }}}
    482 
    483 Execution with indexes:
    484 
    485 {{{
    486 Limit  (cost=32538.32..48741.33 rows=1 width=51) (actual time=192.798..202.516 rows=3 loops=1)
    487   Buffers: shared hit=27463 read=901
    488   ->  Nested Loop Anti Join  (cost=32538.32..48741.33 rows=1 width=51) (actual time=192.796..202.512 rows=3 loops=1)
    489         Join Filter: ((b_1.sitter_id)::text = (ps.user_id)::text)
    490         Buffers: shared hit=27463 read=901
    491         ->  Nested Loop  (cost=32537.88..32586.84 rows=1 width=141) (actual time=180.755..185.162 rows=3 loops=1)
    492               Join Filter: ((ps.user_id)::text = (u.user_id)::text)
    493               Rows Removed by Join Filter: 20
    494               Buffers: shared hit=25661
    495               ->  Nested Loop  (cost=32537.88..32563.79 rows=1 width=127) (actual time=180.740..185.126 rows=3 loops=1)
    496                     Join Filter: ((ps.user_id)::text = (b.sitter_id)::text)
    497                     Rows Removed by Join Filter: 3
    498                     Buffers: shared hit=25658
    499                     ->  Finalize GroupAggregate  (cost=32537.88..32538.27 rows=1 width=37) (actual time=180.718..185.081 rows=3 loops=1)
    500                           Group Key: b.sitter_id
    501                           Filter: (avg(r.rating) >= 4.0)
    502                           Rows Removed by Filter: 1
    503                           Buffers: shared hit=25655
    504                           ->  Gather Merge  (cost=32537.88..32538.24 rows=3 width=69) (actual time=180.700..185.043 rows=7 loops=1)
    505                                 Workers Planned: 3
    506                                 Workers Launched: 3
    507                                 Buffers: shared hit=25655
    508                                 ->  Sort  (cost=31537.84..31537.84 rows=1 width=69) (actual time=174.919..174.925 rows=2 loops=4)
    509                                       Sort Key: b.sitter_id
    510                                       Sort Method: quicksort  Memory: 25kB
    511                                       Buffers: shared hit=25655
    512                                       Worker 0:  Sort Method: quicksort  Memory: 25kB
    513                                       Worker 1:  Sort Method: quicksort  Memory: 25kB
    514                                       Worker 2:  Sort Method: quicksort  Memory: 25kB
    515                                       ->  Partial HashAggregate  (cost=31537.82..31537.83 rows=1 width=69) (actual time=174.853..174.857 rows=2 loops=4)
    516                                             Group Key: b.sitter_id
    517                                             Batches: 1  Memory Usage: 24kB
    518                                             Buffers: shared hit=25631
    519                                             Worker 0:  Batches: 1  Memory Usage: 24kB
    520                                             Worker 1:  Batches: 1  Memory Usage: 24kB
    521                                             Worker 2:  Batches: 1  Memory Usage: 24kB
    522                                             ->  Parallel Hash Join  (cost=4971.92..31268.59 rows=53846 width=41) (actual time=36.095..163.581 rows=41731 loops=4)
    523                                                   Hash Cond: ((b.booking_id)::text = (r.booking_id)::text)
    524                                                   Buffers: shared hit=25631
    525                                                   ->  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)
    526                                                         Buffers: shared hit=22224
    527                                                   ->  Parallel Hash  (cost=4102.52..4102.52 rows=69552 width=41) (actual time=35.280..35.281 rows=41731 loops=4)
    528                                                         Buckets: 262144  Batches: 1  Memory Usage: 15136kB
    529                                                         Buffers: shared hit=3407
    530                                                         ->  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)
    531                                                               Buffers: shared hit=3407
    532                     ->  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)
    533                           Buffers: shared hit=3
    534               ->  Seq Scan on users u  (cost=0.00..15.80 rows=580 width=88) (actual time=0.006..0.006 rows=8 loops=3)
    535                     Buffers: shared hit=3
    536         ->  Unique  (cost=0.44..16154.48 rows=1 width=37) (actual time=5.776..5.776 rows=0 loops=3)
    537               Buffers: shared hit=1802 read=901
    538               ->  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)
    539                     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))
    540                     Heap Fetches: 0
    541                     Buffers: shared hit=1802 read=901
    542 Planning:
    543   Buffers: shared hit=49 read=1
    544 Planning Time: 1.463 ms
    545 Execution Time: 202.649 ms
    546 }}}
    547 
    548 '''Average execution time (10 attempts):''' 199.530ms
    549 
    550 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.
     909  Buffers: shared hit=163 read=6 dirtied=2
     910Planning Time: 2.821 ms
     911JIT:
     912  Functions: 84
     913  Options: Inlining false, Optimization false, Expressions true, Deforming true
     914  Timing: Generation 4.125 ms (Deform 1.641 ms), Inlining 0.000 ms, Optimization 1.547 ms, Emission 49.279 ms, Total 54.951 ms
     915Execution Time: 11502.974 ms
     916
     917}}}
     918
     919'''Execution time:''' 11502.974 ms
     920
     921'''Conclusion:''' The execution time heavily degraded from ~5 seconds to ~11.5 seconds. This represents the "Nested Loop / Index Scan Trap". Creating foreign key indexes actually decreased the query performance.
     922
     923Because Sequential Scans and Hash Joins are better at processing millions of rows in large analytical reporting, we '''drop''' these indexes:
     924{{{
     925#!sql
     926DROP INDEX project.idx_booking_services_booking_id;
     927DROP INDEX project.idx_bookings_date_sitter;
     928}}}
    551929
    552930