Changes between Version 7 and Version 8 of OtherTopics


Ignore:
Timestamp:
06/03/26 11:59:52 (3 weeks ago)
Author:
181201
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • OtherTopics

    v7 v8  
    106106
    107107{{{
    108 Gather Merge  (cost=81030.64..195549.38 rows=967764 width=146) (actual time=94.209..106.394 rows=2 loops=1)
     108Gather Merge  (cost=81029.64..195548.38 rows=967764 width=147) (actual time=102.092..113.433 rows=2 loops=1)
    109109  Workers Planned: 3
    110110  Workers Launched: 3
    111   Buffers: shared hit=22336
     111  Buffers: shared hit=22335
    112112  InitPlan 1
    113     ->  Limit  (cost=0.00..0.02 rows=1 width=90) (actual time=3.367..3.369 rows=1 loops=1)
     113    ->  Limit  (cost=0.00..0.02 rows=1 width=90) (actual time=6.105..6.106 rows=1 loops=1)
    114114          Buffers: shared hit=1
    115           ->  Seq Scan on pet_sitters  (cost=0.00..16.90 rows=690 width=90) (actual time=0.015..0.016 rows=1 loops=1)
     115          ->  Seq Scan on pet_sitters  (cost=0.00..16.90 rows=690 width=90) (actual time=0.028..0.028 rows=1 loops=1)
    116116                Buffers: shared hit=1
    117   ->  Sort  (cost=80030.57..80837.04 rows=322588 width=146) (actual time=60.212..60.213 rows=0 loops=4)
     117  ->  Sort  (cost=80029.57..80836.04 rows=322588 width=147) (actual time=65.204..65.206 rows=0 loops=4)
    118118        Sort Key: bookings.date_from DESC
    119119        Sort Method: quicksort  Memory: 25kB
    120         Buffers: shared hit=22335
     120        Buffers: shared hit=22334
    121121        Worker 0:  Sort Method: quicksort  Memory: 25kB
    122122        Worker 1:  Sort Method: quicksort  Memory: 25kB
    123123        Worker 2:  Sort Method: quicksort  Memory: 25kB
    124         ->  Parallel Seq Scan on bookings  (cost=0.00..26256.35 rows=322588 width=146) (actual time=37.611..60.082 rows=0 loops=4)
     124        ->  Parallel Seq Scan on bookings  (cost=0.00..26255.35 rows=322588 width=147) (actual time=41.358..65.056 rows=0 loops=4)
    125125              Filter: ((sitter_id)::text = ((InitPlan 1).col1)::text)
    126126              Rows Removed by Filter: 250005
    127               Buffers: shared hit=22224
    128 Planning Time: 0.168 ms
     127              Buffers: shared hit=22223
     128Planning:
     129  Buffers: shared hit=139 read=1
     130Planning Time: 1.125 ms
    129131JIT:
    130132  Functions: 12
    131133  Options: Inlining false, Optimization false, Expressions true, Deforming true
    132   Timing: Generation 1.466 ms (Deform 0.675 ms), Inlining 0.000 ms, Optimization 1.430 ms, Emission 19.219 ms, Total 22.115 ms
    133 Execution Time: 106.834 ms
    134 }}}
    135 
    136 '''Average execution time (10 attempts):''' 105.235ms
     134  Timing: Generation 1.536 ms (Deform 0.703 ms), Inlining 0.000 ms, Optimization 1.491 ms, Emission 21.760 ms, Total 24.788 ms
     135Execution Time: 148.520 ms
     136}}}
     137
     138'''Average execution time (10 attempts):''' 145.812ms
    137139
    138140Next, we add this index:
    … …  
    153155
    154156{{{
    155 Index Scan using idx_bookings_sitter_date on bookings  (cost=0.45..72503.52 rows=1000022 width=146) (actual time=0.066..0.068 rows=2 loops=1)
     157Index Scan using idx_bookings_sitter_date on bookings  (cost=0.45..72505.21 rows=1000022 width=147) (actual time=0.101..0.104 rows=2 loops=1)
    156158  Index Cond: ((sitter_id)::text = ((InitPlan 1).col1)::text)
    157159  Buffers: shared hit=2 read=3
    158160  InitPlan 1
    159     ->  Limit  (cost=0.00..0.02 rows=1 width=90) (actual time=0.012..0.013 rows=1 loops=1)
     161    ->  Limit  (cost=0.00..0.02 rows=1 width=90) (actual time=0.040..0.040 rows=1 loops=1)
    160162          Buffers: shared hit=1
    161           ->  Seq Scan on pet_sitters  (cost=0.00..16.90 rows=690 width=90) (actual time=0.011..0.011 rows=1 loops=1)
     163          ->  Seq Scan on pet_sitters  (cost=0.00..16.90 rows=690 width=90) (actual time=0.038..0.038 rows=1 loops=1)
    162164                Buffers: shared hit=1
    163165Planning:
    164   Buffers: shared hit=39 read=2
    165 Planning Time: 0.618 ms
    166 Execution Time: 0.092 ms
    167 }}}
    168 
    169 '''Average execution time (10 attempts):''' 0.087ms
     166  Buffers: shared hit=124 read=5
     167Planning Time: 0.969 ms
     168Execution Time: 0.132 ms
     169}}}
     170
     171'''Average execution time (10 attempts):''' 0.125ms
    170172
    171173Because the execution time has been massively lowered by bypassing the expensive sequential scan and memory sort, we '''keep''' this index.
    … …  
    187189
    188190{{{
    189 Finalize Aggregate  (cost=33210.02..33210.03 rows=1 width=32) (actual time=121.837..135.038 rows=1 loops=1)
    190   Buffers: shared hit=25818
    191   InitPlan 1
    192     ->  Limit  (cost=0.00..0.02 rows=1 width=90) (actual time=0.014..0.015 rows=1 loops=1)
    193           Buffers: shared hit=1
    194           ->  Seq Scan on pet_sitters  (cost=0.00..16.90 rows=690 width=90) (actual time=0.012..0.013 rows=1 loops=1)
    195                 Buffers: shared hit=1
    196   ->  Gather  (cost=33209.67..33209.98 rows=3 width=32) (actual time=119.802..135.016 rows=4 loops=1)
    197         Workers Planned: 3
    198         Workers Launched: 3
    199         Buffers: shared hit=25818
    200         ->  Partial Aggregate  (cost=32209.67..32209.68 rows=1 width=32) (actual time=90.870..90.873 rows=1 loops=4)
    201               Buffers: shared hit=25817
    202               ->  Parallel Hash Join  (cost=4971.92..32075.06 rows=53846 width=4) (actual time=77.089..90.867 rows=0 loops=4)
    203                     Hash Cond: ((b.booking_id)::text = (r.booking_id)::text)
    204                     Buffers: shared hit=25817
    205                     ->  Parallel Seq Scan on bookings b  (cost=0.00..26256.35 rows=322588 width=37) (actual time=41.349..55.119 rows=0 loops=4)
    206                           Filter: ((sitter_id)::text = ((InitPlan 1).col1)::text)
    207                           Rows Removed by Filter: 250005
    208                           Buffers: shared hit=22224
    209                     ->  Parallel Hash  (cost=4102.52..4102.52 rows=69552 width=41) (actual time=34.636..34.637 rows=41731 loops=4)
    210                           Buckets: 262144  Batches: 1  Memory Usage: 15200kB
    211                           Buffers: shared hit=3407
    212                           ->  Parallel Seq Scan on reviews r  (cost=0.00..4102.52 rows=69552 width=41) (actual time=0.026..11.172 rows=41731 loops=4)
    213                                 Buffers: shared hit=3407
    214 Planning:
    215   Buffers: shared hit=16
    216 Planning Time: 0.444 ms
    217 Execution Time: 135.097 ms
    218 }}}
    219 
    220 '''Average execution time (10 attempts):''' 131.722ms
    221 
    222 We add this index:
    223 
    224 {{{
    225 #!sql
    226 CREATE INDEX idx_reviews_booking_id
    227 ON project.reviews (booking_id);
    228 }}}
    229 
    230 Execution with indexes:
    231 
    232 {{{
    233 Finalize Aggregate  (cost=33210.02..33210.03 rows=1 width=32) (actual time=125.303..138.277 rows=1 loops=1)
    234   Buffers: shared hit=25818
     191Finalize Aggregate  (cost=32123.42..32123.43 rows=1 width=32) (actual time=106.390..116.499 rows=1 loops=1)
     192  Buffers: shared hit=24792
    235193  InitPlan 1
    236194    ->  Limit  (cost=0.00..0.02 rows=1 width=90) (actual time=0.013..0.014 rows=1 loops=1)
    237195          Buffers: shared hit=1
    238           ->  Seq Scan on pet_sitters  (cost=0.00..16.90 rows=690 width=90) (actual time=0.012..0.012 rows=1 loops=1)
     196          ->  Seq Scan on pet_sitters  (cost=0.00..16.90 rows=690 width=90) (actual time=0.011..0.012 rows=1 loops=1)
    239197                Buffers: shared hit=1
    240   ->  Gather  (cost=33209.67..33209.98 rows=3 width=32) (actual time=123.351..138.255 rows=4 loops=1)
     198  ->  Gather  (cost=32123.08..32123.39 rows=3 width=32) (actual time=104.969..116.470 rows=4 loops=1)
    241199        Workers Planned: 3
    242200        Workers Launched: 3
    243         Buffers: shared hit=25818
    244         ->  Partial Aggregate  (cost=32209.67..32209.68 rows=1 width=32) (actual time=93.069..93.072 rows=1 loops=4)
    245               Buffers: shared hit=25817
    246               ->  Parallel Hash Join  (cost=4971.92..32075.06 rows=53846 width=4) (actual time=78.783..93.066 rows=0 loops=4)
     201        Buffers: shared hit=24792
     202        ->  Partial Aggregate  (cost=31123.08..31123.09 rows=1 width=32) (actual time=75.769..75.773 rows=1 loops=4)
     203              Buffers: shared hit=24791
     204              ->  Parallel Hash Join  (cost=3926.81..31028.95 rows=37651 width=4) (actual time=62.399..75.767 rows=0 loops=4)
    247205                    Hash Cond: ((b.booking_id)::text = (r.booking_id)::text)
    248                     Buffers: shared hit=25817
    249                     ->  Parallel Seq Scan on bookings b  (cost=0.00..26256.35 rows=322588 width=37) (actual time=42.860..57.138 rows=0 loops=4)
     206                    Buffers: shared hit=24791
     207                    ->  Parallel Seq Scan on bookings b  (cost=0.00..26255.35 rows=322588 width=37) (actual time=40.115..53.479 rows=0 loops=4)
    250208                          Filter: ((sitter_id)::text = ((InitPlan 1).col1)::text)
    251209                          Rows Removed by Filter: 250005
    252                           Buffers: shared hit=22224
    253                     ->  Parallel Hash  (cost=4102.52..4102.52 rows=69552 width=41) (actual time=34.827..34.828 rows=41731 loops=4)
    254                           Buckets: 262144  Batches: 1  Memory Usage: 15168kB
    255                           Buffers: shared hit=3407
    256                           ->  Parallel Seq Scan on reviews r  (cost=0.00..4102.52 rows=69552 width=41) (actual time=0.029..11.437 rows=41731 loops=4)
    257                                 Buffers: shared hit=3407
     210                          Buffers: shared hit=22223
     211                    ->  Parallel Hash  (cost=3068.58..3068.58 rows=68658 width=41) (actual time=21.453..21.454 rows=29180 loops=4)
     212                          Buckets: 131072  Batches: 1  Memory Usage: 10208kB
     213                          Buffers: shared hit=2382
     214                          ->  Parallel Seq Scan on reviews r  (cost=0.00..3068.58 rows=68658 width=41) (actual time=0.026..7.819 rows=29180 loops=4)
     215                                Buffers: shared hit=2382
    258216Planning:
    259   Buffers: shared hit=16
    260 Planning Time: 0.554 ms
    261 Execution Time: 138.346 ms
    262 }}}
    263 
    264 '''Average execution time (10 attempts):''' 138.346ms
    265 
    266 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.
     217  Buffers: shared hit=165 read=2
     218Planning Time: 1.200 ms
     219Execution Time: 116.627 ms
     220}}}
     221
     222'''Average execution time (10 attempts):''' 115.143ms
     223
     224We add this index:
     225
     226{{{
     227#!sql
     228CREATE INDEX idx_reviews_booking_id
     229ON project.reviews (booking_id);
     230}}}
     231
     232Execution with indexes:
     233
     234{{{
     235Finalize Aggregate  (cost=32123.42..32123.43 rows=1 width=32) (actual time=102.850..113.265 rows=1 loops=1)
     236  Buffers: shared hit=24792
     237  InitPlan 1
     238    ->  Limit  (cost=0.00..0.02 rows=1 width=90) (actual time=0.014..0.016 rows=1 loops=1)
     239          Buffers: shared hit=1
     240          ->  Seq Scan on pet_sitters  (cost=0.00..16.90 rows=690 width=90) (actual time=0.013..0.013 rows=1 loops=1)
     241                Buffers: shared hit=1
     242  ->  Gather  (cost=32123.08..32123.39 rows=3 width=32) (actual time=101.457..113.235 rows=4 loops=1)
     243        Workers Planned: 3
     244        Workers Launched: 3
     245        Buffers: shared hit=24792
     246        ->  Partial Aggregate  (cost=31123.08..31123.09 rows=1 width=32) (actual time=72.199..72.202 rows=1 loops=4)
     247              Buffers: shared hit=24791
     248              ->  Parallel Hash Join  (cost=3926.81..31028.95 rows=37651 width=4) (actual time=59.642..72.196 rows=0 loops=4)
     249                    Hash Cond: ((b.booking_id)::text = (r.booking_id)::text)
     250                    Buffers: shared hit=24791
     251                    ->  Parallel Seq Scan on bookings b  (cost=0.00..26255.35 rows=322588 width=37) (actual time=37.082..49.436 rows=0 loops=4)
     252                          Filter: ((sitter_id)::text = ((InitPlan 1).col1)::text)
     253                          Rows Removed by Filter: 250005
     254                          Buffers: shared hit=22223
     255                    ->  Parallel Hash  (cost=3068.58..3068.58 rows=68658 width=41) (actual time=21.936..21.937 rows=29180 loops=4)
     256                          Buckets: 131072  Batches: 1  Memory Usage: 10240kB
     257                          Buffers: shared hit=2382
     258                          ->  Parallel Seq Scan on reviews r  (cost=0.00..3068.58 rows=68658 width=41) (actual time=0.020..7.757 rows=29180 loops=4)
     259                                Buffers: shared hit=2382
     260Planning:
     261  Buffers: shared hit=78 read=6
     262Planning Time: 1.082 ms
     263Execution Time: 113.336 ms
     264}}}
     265
     266'''Average execution time (10 attempts):''' 114.288ms
     267
     268There is practically no change in the execution time. Because the index is not utilized for this query and only adds overhead during INSERT and UPDATE operations, we will '''not''' keep this index.
    267269
    268270=== 3. Sitter Performance Analytics (Phase 6 Query) ===
    … …  
    335337Execution without indexes:
    336338{{{
    337 Limit  (cost=151645.74..151645.77 rows=10 width=176) (actual time=2620.994..2631.056 rows=4 loops=1)
    338   Buffers: shared hit=740349
     339Limit  (cost=154247.04..154247.06 rows=10 width=176) (actual time=2600.384..2611.703 rows=4 loops=1)
     340  Buffers: shared hit=735904 read=1
    339341  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    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=1)
     343  ->  Sort  (cost=154247.02..154247.59 rows=230 width=176) (actual time=2544.398..2555.714 rows=4 loops=1)
    342344        Sort Key: (dense_rank() OVER (?))
    343345        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)
     346        Buffers: shared hit=735904 read=1
     347        ->  WindowAgg  (cost=154232.29..154242.05 rows=230 width=176) (actual time=2544.338..2555.668 rows=4 loops=1)
     348              Buffers: shared hit=735901 read=1
     349              ->  Sort  (cost=154232.27..154232.85 rows=230 width=152) (actual time=2544.310..2555.625 rows=4 loops=1)
    348350                    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
    349351                    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                    Buffers: shared hit=735901 read=1
     353                    ->  Hash Join  (cost=154197.60..154223.25 rows=230 width=152) (actual time=2544.282..2555.608 rows=4 loops=1)
    352354                          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)
     355                          Buffers: shared hit=735898 read=1
     356                          ->  Seq Scan on users u  (cost=0.00..15.80 rows=580 width=64) (actual time=0.041..0.045 rows=12 loops=1)
     357                                Buffers: shared read=1
     358                          ->  Hash  (cost=154194.72..154194.72 rows=230 width=146) (actual time=2544.166..2555.480 rows=4 loops=1)
    357359                                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                                Buffers: shared hit=735898
     361                                ->  Merge Left Join  (cost=154190.07..154194.72 rows=230 width=146) (actual time=2544.131..2555.465 rows=4 loops=1)
    360362                                      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                                      Buffers: shared hit=735898
     364                                      ->  Merge Left Join  (cost=109447.58..109451.64 rows=230 width=122) (actual time=2328.303..2328.329 rows=4 loops=1)
    363365                                            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                                            Buffers: shared hit=711107
     367                                            ->  Merge Left Join  (cost=48939.75..48943.22 rows=230 width=114) (actual time=724.264..724.279 rows=4 loops=1)
    366368                                                  Merge Cond: ((ps.user_id)::text = (ss.sitter_id)::text)
    367369                                                  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                                                  Buffers: shared hit=22224
     371                                                  ->  Sort  (cost=49.44..51.16 rows=690 width=90) (actual time=0.018..0.021 rows=4 loops=1)
    370372                                                        Sort Key: ps.user_id
    371373                                                        Sort Method: quicksort  Memory: 25kB
    372374                                                        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)
     375                                                        ->  Seq Scan on pet_sitters ps  (cost=0.00..16.90 rows=690 width=90) (actual time=0.008..0.009 rows=4 loops=1)
    374376                                                              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)
     377                                                  ->  Sort  (cost=48890.31..48890.32 rows=1 width=61) (actual time=724.208..724.212 rows=4 loops=1)
    376378                                                        Sort Key: ss.sitter_id
    377379                                                        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)
     380                                                        Buffers: shared hit=22223
     381                                                        ->  Subquery Scan on ss  (cost=48890.28..48890.30 rows=1 width=61) (actual time=724.190..724.196 rows=4 loops=1)
     382                                                              Buffers: shared hit=22223
     383                                                              ->  HashAggregate  (cost=48890.28..48890.29 rows=1 width=61) (actual time=724.182..724.187 rows=4 loops=1)
    382384                                                                    Group Key: b.sitter_id
    383385                                                                    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)
     386                                                                    Buffers: shared hit=22223
     387                                                                    ->  Nested Loop  (cost=0.00..47223.57 rows=111114 width=83) (actual time=0.044..359.696 rows=1000017 loops=1)
     388                                                                          Join Filter: ((b.date_from >= p.start_date) AND (b.date_from < p.end_date))
     389                                                                          Rows Removed by Join Filter: 5
     390                                                                          Buffers: shared hit=22223
     391                                                                          ->  CTE Scan on params p  (cost=0.00..0.02 rows=1 width=8) (actual time=0.015..0.017 rows=1 loops=1)
     392                                                                          ->  Seq Scan on bookings b  (cost=0.00..32223.22 rows=1000022 width=87) (actual time=0.013..106.436 rows=1000022 loops=1)
     393                                                                                Buffers: shared hit=22223
     394                                            ->  Sort  (cost=60507.84..60507.84 rows=1 width=45) (actual time=1604.016..1604.022 rows=2 loops=1)
    396395                                                  Sort Key: sf.sitter_id
    397396                                                  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)
     397                                                  Buffers: shared hit=688883
     398                                                  ->  Subquery Scan on sf  (cost=60507.81..60507.83 rows=1 width=45) (actual time=1603.989..1603.995 rows=2 loops=1)
     399                                                        Buffers: shared hit=688883
     400                                                        ->  HashAggregate  (cost=60507.81..60507.82 rows=1 width=45) (actual time=1603.982..1603.986 rows=2 loops=1)
    402401                                                              Group Key: b_1.sitter_id
    403402                                                              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)
     403                                                              Buffers: shared hit=688883
     404                                                              ->  Nested Loop  (cost=0.42..60413.23 rows=18915 width=41) (actual time=0.129..1550.156 rows=166665 loops=1)
     405                                                                    Buffers: shared hit=688883
     406                                                                    ->  Nested Loop  (cost=0.00..37276.85 rows=18915 width=74) (actual time=0.043..232.987 rows=166665 loops=1)
    408407                                                                          Join Filter: ((b_1.date_from >= p_1.start_date) AND (b_1.date_from < p_1.end_date))
    409408                                                                          Rows Removed by Join Filter: 3
    410409                                                                          Buffers: shared hit=22223
    411410                                                                          ->  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)
     411                                                                          ->  Seq Scan on bookings b_1  (cost=0.00..34723.28 rows=170237 width=78) (actual time=0.024..189.504 rows=166668 loops=1)
    413412                                                                                Filter: ((status)::text = 'Completed'::text)
    414                                                                                 Rows Removed by Filter: 832704
     413                                                                                Rows Removed by Filter: 833354
    415414                                                                                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)
     415                                                                    ->  Index Scan using payments_booking_id_key on payments pay  (cost=0.42..1.22 rows=1 width=41) (actual time=0.007..0.007 rows=1 loops=166665)
    417416                                                                          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)
     417                                                                          Buffers: shared hit=666660
     418                                      ->  Sort  (cost=44742.49..44742.49 rows=1 width=61) (actual time=215.803..227.106 rows=2 loops=1)
    420419                                            Sort Key: sr.sitter_id
    421420                                            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)
     421                                            Buffers: shared hit=24791
     422                                            ->  Subquery Scan on sr  (cost=44742.45..44742.48 rows=1 width=61) (actual time=215.773..227.079 rows=2 loops=1)
     423                                                  Buffers: shared hit=24791
     424                                                  ->  HashAggregate  (cost=44742.45..44742.47 rows=1 width=61) (actual time=215.766..227.070 rows=2 loops=1)
    426425                                                        Group Key: b_2.sitter_id
    427426                                                        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)
     427                                                        Buffers: shared hit=24791
     428                                                        ->  Nested Loop  (cost=4926.81..44645.18 rows=12969 width=78) (actual time=68.121..195.697 rows=116717 loops=1)
    430429                                                              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)
     430                                                              Rows Removed by Join Filter: 2
     431                                                              Buffers: shared hit=24791
     432                                                              ->  CTE Scan on params p_2  (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.004 rows=1 loops=1)
     433                                                              ->  Gather  (cost=4926.81..42894.38 rows=116719 width=82) (actual time=68.087..176.699 rows=116719 loops=1)
    435434                                                                    Workers Planned: 3
    436435                                                                    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)
     436                                                                    Buffers: shared hit=24791
     437                                                                    ->  Parallel Hash Join  (cost=3926.81..30222.48 rows=37651 width=82) (actual time=36.517..159.380 rows=29180 loops=4)
    439438                                                                          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)
     439                                                                          Buffers: shared hit=24791
     440                                                                          ->  Parallel Seq Scan on bookings b_2  (cost=0.00..25448.88 rows=322588 width=78) (actual time=0.012..34.092 rows=250006 loops=4)
    442441                                                                                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
     442                                                                          ->  Parallel Hash  (cost=3068.58..3068.58 rows=68658 width=78) (actual time=35.713..35.714 rows=29180 loops=4)
     443                                                                                Buckets: 131072  Batches: 1  Memory Usage: 13856kB
     444                                                                                Buffers: shared hit=2382
     445                                                                                ->  Parallel Seq Scan on reviews r  (cost=0.00..3068.58 rows=68658 width=78) (actual time=9.315..16.953 rows=29180 loops=4)
     446                                                                                      Buffers: shared hit=2382
    448447Planning:
    449   Buffers: shared hit=611
    450 Planning Time: 4.704 ms
     448  Buffers: shared hit=227 read=5
     449Planning Time: 3.010 ms
    451450JIT:
    452   Functions: 116
     451  Functions: 115
    453452  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
    455 Execution Time: 2670.429 ms
    456 }}}
    457 
    458 '''Execution time:''' 2670.429 ms
     453  Timing: Generation 9.518 ms (Deform 4.386 ms), Inlining 0.000 ms, Optimization 4.262 ms, Emission 89.334 ms, Total 103.115 ms
     454Execution Time: 2619.146 ms
     455}}}
     456
     457'''Execution time:''' 2619.146 ms
    459458
    460459We attempt to optimize this analytical query by introducing multiple foreign key indexes:
    … …  
    470469Execution with indexes:
    471470{{{
    472 Limit  (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
     471Limit  (cost=110101.62..110101.65 rows=10 width=176) (actual time=3500.078..3500.100 rows=4 loops=1)
     472  Buffers: shared hit=738392, temp read=6439 written=6439
    474473  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)
     474    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.027..0.028 rows=1 loops=1)
     475  ->  Sort  (cost=110101.60..110102.18 rows=230 width=176) (actual time=3442.513..3442.532 rows=4 loops=1)
    477476        Sort Key: (dense_rank() OVER (?))
    478477        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)
     478        Buffers: shared hit=738392, temp read=6439 written=6439
     479        ->  WindowAgg  (cost=110086.87..110096.63 rows=230 width=176) (actual time=3442.451..3442.485 rows=4 loops=1)
     480              Buffers: shared hit=738389, temp read=6439 written=6439
     481              ->  Sort  (cost=110086.86..110087.43 rows=230 width=152) (actual time=3442.434..3442.453 rows=4 loops=1)
    483482                    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
    484483                    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)
     484                    Buffers: shared hit=738389, temp read=6439 written=6439
     485                    ->  Hash Join  (cost=110052.18..110077.83 rows=230 width=152) (actual time=3442.406..3442.435 rows=4 loops=1)
    487486                          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)
     487                          Buffers: shared hit=738386, temp read=6439 written=6439
     488                          ->  Seq Scan on users u  (cost=0.00..15.80 rows=580 width=64) (actual time=0.015..0.020 rows=12 loops=1)
    490489                                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)
     490                          ->  Hash  (cost=110049.30..110049.30 rows=230 width=146) (actual time=3442.349..3442.367 rows=4 loops=1)
    492491                                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)
     492                                Buffers: shared hit=738385, temp read=6439 written=6439
     493                                ->  Merge Left Join  (cost=110044.65..110049.30 rows=230 width=146) (actual time=3442.306..3442.342 rows=4 loops=1)
    495494                                      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)
     495                                      Buffers: shared hit=738385, temp read=6439 written=6439
     496                                      ->  Merge Left Join  (cost=72317.28..72321.34 rows=230 width=122) (actual time=2386.271..2386.298 rows=4 loops=1)
    498497                                            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)
     498                                            Buffers: shared hit=712889
     499                                            ->  Merge Left Join  (cost=28260.39..28263.86 rows=230 width=114) (actual time=797.385..797.401 rows=4 loops=1)
    501500                                                  Merge Cond: ((ps.user_id)::text = (ss.sitter_id)::text)
    502501                                                  Filter: (COALESCE(ss.total_bookings, '0'::bigint) > 0)
    503502                                                  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)
     503                                                  ->  Sort  (cost=49.44..51.16 rows=690 width=90) (actual time=0.024..0.026 rows=4 loops=1)
    505504                                                        Sort Key: ps.user_id
    506505                                                        Sort Method: quicksort  Memory: 25kB
    507506                                                        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)
     507                                                        ->  Seq Scan on pet_sitters ps  (cost=0.00..16.90 rows=690 width=90) (actual time=0.013..0.014 rows=4 loops=1)
    509508                                                              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)
     509                                                  ->  Sort  (cost=28210.95..28210.96 rows=1 width=61) (actual time=797.322..797.327 rows=4 loops=1)
    511510                                                        Sort Key: ss.sitter_id
    512511                                                        Sort Method: quicksort  Memory: 25kB
    513512                                                        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)
     513                                                        ->  Subquery Scan on ss  (cost=28210.92..28210.94 rows=1 width=61) (actual time=797.304..797.311 rows=4 loops=1)
    515514                                                              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)
     515                                                              ->  HashAggregate  (cost=28210.92..28210.93 rows=1 width=61) (actual time=797.297..797.302 rows=4 loops=1)
    517516                                                                    Group Key: b.sitter_id
    518517                                                                    Batches: 1  Memory Usage: 24kB
    519518                                                                    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)
     519                                                                    ->  Nested Loop  (cost=1543.34..26544.21 rows=111114 width=83) (actual time=43.375..439.128 rows=1000017 loops=1)
    521520                                                                          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)
     521                                                                          ->  CTE Scan on params p  (cost=0.00..0.02 rows=1 width=8) (actual time=0.030..0.033 rows=1 loops=1)
     522                                                                          ->  Bitmap Heap Scan on bookings b  (cost=1543.34..25433.05 rows=111114 width=87) (actual time=43.324..251.336 rows=1000017 loops=1)
    524523                                                                                Recheck Cond: ((date_from >= p.start_date) AND (date_from < p.end_date))
    525524                                                                                Heap Blocks: exact=22223
    526525                                                                                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)
     526                                                                                ->  Bitmap Index Scan on idx_bookings_date_owner  (cost=0.00..1515.57 rows=111114 width=0) (actual time=38.673..38.674 rows=1000017 loops=1)
    528527                                                                                      Index Cond: ((date_from >= p.start_date) AND (date_from < p.end_date))
    529528                                                                                      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)
     529                                            ->  Sort  (cost=44056.89..44056.90 rows=1 width=45) (actual time=1588.861..1588.866 rows=2 loops=1)
    531530                                                  Sort Key: sf.sitter_id
    532531                                                  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)
     532                                                  Buffers: shared hit=689774
     533                                                  ->  Subquery Scan on sf  (cost=44056.86..44056.88 rows=1 width=45) (actual time=1588.837..1588.844 rows=2 loops=1)
     534                                                        Buffers: shared hit=689774
     535                                                        ->  HashAggregate  (cost=44056.86..44056.87 rows=1 width=45) (actual time=1588.829..1588.834 rows=2 loops=1)
    537536                                                              Group Key: b_1.sitter_id
    538537                                                              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)
     538                                                              Buffers: shared hit=689774
     539                                                              ->  Nested Loop  (cost=1520.72..43962.29 rows=18915 width=41) (actual time=41.086..1537.591 rows=166665 loops=1)
     540                                                                    Buffers: shared hit=689774
     541                                                                    ->  Nested Loop  (cost=1520.29..25876.96 rows=18915 width=74) (actual time=41.016..285.706 rows=166665 loops=1)
    543542                                                                          Buffers: shared hit=23114
    544543                                                                          ->  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)
     544                                                                          ->  Bitmap Heap Scan on bookings b_1  (cost=1520.29..25687.79 rows=18915 width=78) (actual time=41.004..254.671 rows=166665 loops=1)
    546545                                                                                Recheck Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date))
    547546                                                                                Filter: ((status)::text = 'Completed'::text)
    548                                                                                 Rows Removed by Filter: 832702
     547                                                                                Rows Removed by Filter: 833352
    549548                                                                                Heap Blocks: exact=22223
    550549                                                                                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)
     550                                                                                ->  Bitmap Index Scan on idx_bookings_date_owner  (cost=0.00..1515.57 rows=111114 width=0) (actual time=36.387..36.387 rows=1000017 loops=1)
    552551                                                                                      Index Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date))
    553552                                                                                      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)
     553                                                                    ->  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=166665)
    555554                                                                          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)
     555                                                                          Buffers: shared hit=666660
     556                                      ->  Sort  (cost=37727.37..37727.37 rows=1 width=61) (actual time=1056.010..1056.015 rows=2 loops=1)
    558557                                            Sort Key: sr.sitter_id
    559558                                            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)
     559                                            Buffers: shared hit=25496, temp read=6439 written=6439
     560                                            ->  Subquery Scan on sr  (cost=37727.33..37727.36 rows=1 width=61) (actual time=1055.985..1055.994 rows=2 loops=1)
     561                                                  Buffers: shared hit=25496, temp read=6439 written=6439
     562                                                  ->  HashAggregate  (cost=37727.33..37727.35 rows=1 width=61) (actual time=1055.977..1055.984 rows=2 loops=1)
    564563                                                        Group Key: b_2.sitter_id
    565564                                                        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)
     565                                                        Buffers: shared hit=25496, temp read=6439 written=6439
     566                                                        ->  Hash Join  (cost=8033.52..37630.07 rows=12969 width=78) (actual time=121.902..1017.507 rows=116717 loops=1)
    568567                                                              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)
     568                                                              Buffers: shared hit=25496, temp read=6439 written=6439
     569                                                              ->  Nested Loop  (cost=1543.34..26544.21 rows=111114 width=74) (actual time=40.177..418.927 rows=1000017 loops=1)
    571570                                                                    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)
     571                                                                    ->  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)
     572                                                                    ->  Bitmap Heap Scan on bookings b_2  (cost=1543.34..25433.05 rows=111114 width=78) (actual time=40.156..222.658 rows=1000017 loops=1)
    574573                                                                          Recheck Cond: ((date_from >= p_2.start_date) AND (date_from < p_2.end_date))
    575574                                                                          Heap Blocks: exact=22223
    576575                                                                          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)
     576                                                                          ->  Bitmap Index Scan on idx_bookings_date_owner  (cost=0.00..1515.57 rows=111114 width=0) (actual time=35.548..35.548 rows=1000017 loops=1)
    578577                                                                                Index Cond: ((date_from >= p_2.start_date) AND (date_from < p_2.end_date))
    579578                                                                                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
     579                                                              ->  Hash  (cost=3549.19..3549.19 rows=116719 width=78) (actual time=81.514..81.515 rows=116719 loops=1)
     580                                                                    Buckets: 131072  Batches: 2  Memory Usage: 7251kB
     581                                                                    Buffers: shared hit=2382, temp written=702
     582                                                                    ->  Seq Scan on reviews r  (cost=0.00..3549.19 rows=116719 width=78) (actual time=0.025..30.472 rows=116719 loops=1)
     583                                                                          Buffers: shared hit=2382
    585584Planning:
    586   Buffers: shared hit=657
    587 Planning Time: 4.960 ms
     585  Buffers: shared hit=668
     586Planning Time: 5.315 ms
    588587JIT:
    589588  Functions: 81
    590589  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
    592 Execution Time: 3709.157 ms
    593 
    594 }}}
    595 
    596 '''Execution time:''' 3709.157 ms
    597 
    598 Instead of improving performance, adding these indexes actually '''increased''' the execution time by roughly 1 second, therefore we are '''not''' keeping the indexes.
     590  Timing: Generation 6.369 ms (Deform 2.765 ms), Inlining 0.000 ms, Optimization 2.281 ms, Emission 55.664 ms, Total 64.314 ms
     591Execution Time: 3540.806 ms
     592}}}
     593
     594'''Execution time:''' 3540.806 ms
     595
     596Instead of improving performance, adding these indexes actually '''increased''' the execution time by nearly 1 second. The indexes are also used in Scenario 4 below.
    599597
    600598=== 4. Highest Paying Customers Analytics (Phase 6 Query) ===
    … …  
    665663Execution without indexes:
    666664{{{
    667 Limit  (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
     665Limit  (cost=170067.13..170067.15 rows=10 width=115) (actual time=5322.701..5322.720 rows=2 loops=1)
     666  Buffers: shared hit=726269, temp read=26853 written=26858
    669667  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)
     668    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1)
     669  ->  Sort  (cost=170067.11..170067.68 rows=230 width=115) (actual time=5264.981..5264.999 rows=2 loops=1)
    672670        Sort Key: (dense_rank() OVER (?))
    673671        Sort Method: quicksort  Memory: 25kB
    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)
     672        Buffers: shared hit=726269, temp read=26853 written=26858
     673        ->  WindowAgg  (cost=170057.56..170062.14 rows=230 width=115) (actual time=5264.923..5264.951 rows=2 loops=1)
     674              Buffers: shared hit=726266, temp read=26853 written=26858
     675              ->  Sort  (cost=170057.54..170058.11 rows=230 width=349) (actual time=5264.888..5264.905 rows=2 loops=1)
    678676                    Sort Key: (COALESCE((sum(pay.amount)), '0'::bigint)) DESC, (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint)) DESC
    679677                    Sort Method: quicksort  Memory: 25kB
    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)
     678                    Buffers: shared hit=726266, temp read=26853 written=26858
     679                    ->  Hash Join  (cost=169840.97..170048.52 rows=230 width=349) (actual time=5049.927..5264.888 rows=2 loops=1)
    682680                          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)
     681                          Buffers: shared hit=726266, temp read=26853 written=26858
     682                          ->  Merge Left Join  (cost=169817.92..170024.86 rows=230 width=388) (actual time=5049.843..5264.798 rows=2 loops=1)
    685683                                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)
     684                                Buffers: shared hit=726265, temp read=26853 written=26858
     685                                ->  Merge Left Join  (cost=169816.17..170021.77 rows=230 width=380) (actual time=5049.710..5264.655 rows=2 loops=1)
    688686                                      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)
     687                                      Buffers: shared hit=726264, temp read=26853 written=26858
     688                                      ->  Merge Left Join  (cost=61806.32..61998.99 rows=230 width=106) (actual time=2266.934..2481.844 rows=2 loops=1)
    691689                                            Merge Cond: ((po.user_id)::text = (b.owner_id)::text)
    692690                                            Filter: (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint) > 0)
    693691                                            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)
     692                                            Buffers: shared hit=688884, temp read=1858 written=1863
     693                                            ->  Sort  (cost=49.44..51.16 rows=690 width=90) (actual time=0.040..0.044 rows=7 loops=1)
    696694                                                  Sort Key: po.user_id
    697695                                                  Sort Method: quicksort  Memory: 25kB
    698696                                                  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)
     697                                                  ->  Seq Scan on pet_owners po  (cost=0.00..16.90 rows=690 width=90) (actual time=0.027..0.028 rows=7 loops=1)
    700698                                                        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)
     699                                            ->  Materialize  (cost=61756.88..61946.08 rows=2 width=53) (actual time=2266.857..2481.755 rows=2 loops=1)
     700                                                  Buffers: shared hit=688883, temp read=1858 written=1863
     701                                                  ->  GroupAggregate  (cost=61756.88..61946.05 rows=2 width=53) (actual time=2266.852..2481.746 rows=2 loops=1)
    704702                                                        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)
     703                                                        Buffers: shared hit=688883, temp read=1858 written=1863
     704                                                        ->  Sort  (cost=61756.88..61804.17 rows=18915 width=78) (actual time=2266.784..2442.950 rows=166665 loops=1)
    707705                                                              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)
     706                                                              Sort Method: external merge  Disk: 14864kB
     707                                                              Buffers: shared hit=688883, temp read=1858 written=1863
     708                                                              ->  Nested Loop  (cost=0.42..60413.23 rows=18915 width=78) (actual time=0.185..1616.303 rows=166665 loops=1)
     709                                                                    Buffers: shared hit=688883
     710                                                                    ->  Nested Loop  (cost=0.00..37276.85 rows=18915 width=74) (actual time=0.057..274.855 rows=166665 loops=1)
    713711                                                                          Join Filter: ((b.date_from >= p.start_date) AND (b.date_from < p.end_date))
    714712                                                                          Rows Removed by Join Filter: 3
    715713                                                                          Buffers: shared hit=22223
    716714                                                                          ->  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)
     715                                                                          ->  Seq Scan on bookings b  (cost=0.00..34723.28 rows=170237 width=78) (actual time=0.026..235.609 rows=166668 loops=1)
    718716                                                                                Filter: ((status)::text = 'Completed'::text)
    719                                                                                 Rows Removed by Filter: 832704
     717                                                                                Rows Removed by Filter: 833354
    720718                                                                                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)
     719                                                                    ->  Index Scan using payments_booking_id_key on payments pay  (cost=0.42..1.22 rows=1 width=41) (actual time=0.008..0.008 rows=1 loops=166665)
    722720                                                                          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)
     721                                                                          Buffers: shared hit=666660
     722                                      ->  Materialize  (cost=108009.85..108022.19 rows=2 width=311) (actual time=2782.752..2782.780 rows=2 loops=1)
     723                                            Buffers: shared hit=37380, temp read=24995 written=24995
     724                                            ->  Subquery Scan on service_counts  (cost=108009.85..108022.18 rows=2 width=311) (actual time=2782.740..2782.766 rows=2 loops=1)
    727725                                                  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)
     726                                                  Buffers: shared hit=37380, temp read=24995 written=24995
     727                                                  ->  WindowAgg  (cost=108009.85..108017.43 rows=380 width=327) (actual time=2782.732..2782.756 rows=2 loops=1)
    730728                                                        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)
     729                                                        Buffers: shared hit=37380, temp read=24995 written=24995
     730                                                        ->  Sort  (cost=108009.83..108010.78 rows=380 width=319) (actual time=2782.709..2782.717 rows=5 loops=1)
    733731                                                              Sort Key: b_1.owner_id, (count(bs.service_id)) DESC
    734732                                                              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)
     733                                                              Buffers: shared hit=37380, temp read=24995 written=24995
     734                                                              ->  HashAggregate  (cost=107989.75..107993.55 rows=380 width=319) (actual time=2782.681..2782.692 rows=5 loops=1)
    737735                                                                    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)
     736                                                                    Batches: 1  Memory Usage: 37kB
     737                                                                    Buffers: shared hit=37380, temp read=24995 written=24995
     738                                                                    ->  Hash Join  (cost=50037.77..107156.39 rows=111114 width=348) (actual time=792.873..2447.981 rows=1000017 loops=1)
    741739                                                                          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)
     740                                                                          Buffers: shared hit=37380, temp read=24995 written=24995
     741                                                                          ->  Hash Join  (cost=50023.50..106843.94 rows=111114 width=74) (actual time=792.796..2158.768 rows=1000017 loops=1)
    744742                                                                                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
     743                                                                                Buffers: shared hit=37379, temp read=24995 written=24995
     744                                                                                ->  Seq Scan on booking_services bs  (cost=0.00..25156.22 rows=1000022 width=74) (actual time=0.034..182.963 rows=1000022 loops=1)
     745                                                                                      Buffers: shared hit=15156
     746                                                                                ->  Hash  (cost=47223.57..47223.57 rows=111114 width=74) (actual time=789.943..789.945 rows=1000017 loops=1)
     747                                                                                      Buckets: 131072 (originally 131072)  Batches: 16 (originally 2)  Memory Usage: 7487kB
     748                                                                                      Buffers: shared hit=22223, temp written=10750
     749                                                                                      ->  Nested Loop  (cost=0.00..47223.57 rows=111114 width=74) (actual time=0.034..347.150 rows=1000017 loops=1)
     750                                                                                            Join Filter: ((b_1.date_from >= p_1.start_date) AND (b_1.date_from < p_1.end_date))
     751                                                                                            Rows Removed by Join Filter: 5
     752                                                                                            Buffers: shared hit=22223
    753753                                                                                            ->  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)
     754                                                                                            ->  Seq Scan on bookings b_1  (cost=0.00..32223.22 rows=1000022 width=78) (actual time=0.010..102.527 rows=1000022 loops=1)
     755                                                                                                  Buffers: shared hit=22223
     756                                                                          ->  Hash  (cost=11.90..11.90 rows=190 width=364) (actual time=0.044..0.045 rows=4 loops=1)
    762757                                                                                Buckets: 1024  Batches: 1  Memory Usage: 9kB
    763758                                                                                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)
     759                                                                                ->  Seq Scan on services s  (cost=0.00..11.90 rows=190 width=364) (actual time=0.031..0.033 rows=4 loops=1)
    765760                                                                                      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)
     761                                ->  GroupAggregate  (cost=1.75..2.15 rows=23 width=98) (actual time=0.093..0.104 rows=6 loops=1)
    767762                                      Group Key: pets.owner_id
    768763                                      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)
     764                                      ->  Sort  (cost=1.75..1.81 rows=23 width=180) (actual time=0.068..0.071 rows=16 loops=1)
    770765                                            Sort Key: pets.owner_id
    771                                             Sort Method: quicksort  Memory: 27kB
     766                                            Sort Method: quicksort  Memory: 26kB
    772767                                            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)
     768                                            ->  Seq Scan on pets  (cost=0.00..1.23 rows=23 width=180) (actual time=0.030..0.034 rows=16 loops=1)
    774769                                                  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)
     770                          ->  Hash  (cost=15.80..15.80 rows=580 width=51) (actual time=0.057..0.058 rows=12 loops=1)
    776771                                Buckets: 1024  Batches: 1  Memory Usage: 9kB
    777772                                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)
     773                                ->  Seq Scan on users u  (cost=0.00..15.80 rows=580 width=51) (actual time=0.033..0.036 rows=12 loops=1)
    779774                                      Buffers: shared hit=1
    780775Planning:
    781   Buffers: shared hit=34
    782 Planning Time: 1.751 ms
     776  Buffers: shared hit=570
     777Planning Time: 4.934 ms
    783778JIT:
    784   Functions: 86
     779  Functions: 85
    785780  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
    787 Execution Time: 5033.000 ms
    788 
    789 }}}
    790 
    791 '''Execution time:''' 5033.000 ms
     781  Timing: Generation 6.167 ms (Deform 2.815 ms), Inlining 0.000 ms, Optimization 2.209 ms, Emission 55.891 ms, Total 64.267 ms
     782Execution Time: 5367.798 ms
     783}}}
     784
     785'''Execution time:''' 5367.798 ms
    792786
    793787Execution with the indexes we created in Scenario 3:
    794788{{{
    795 Limit  (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
     789Limit  (cost=133535.97..133535.99 rows=10 width=115) (actual time=11115.289..11115.307 rows=2 loops=1)
     790  Buffers: shared hit=4705758 read=7205, temp read=1858 written=1863
    797791  CTE params
    798792    ->  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)
     793  ->  Sort  (cost=133535.95..133536.52 rows=230 width=115) (actual time=11060.413..11060.429 rows=2 loops=1)
    800794        Sort Key: (dense_rank() OVER (?))
    801795        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)
     796        Buffers: shared hit=4705758 read=7205, temp read=1858 written=1863
     797        ->  WindowAgg  (cost=133526.40..133530.98 rows=230 width=115) (actual time=11060.340..11060.367 rows=2 loops=1)
     798              Buffers: shared hit=4705755 read=7205, temp read=1858 written=1863
     799              ->  Sort  (cost=133526.38..133526.95 rows=230 width=349) (actual time=11060.310..11060.326 rows=2 loops=1)
    806800                    Sort Key: (COALESCE((sum(pay.amount)), '0'::bigint)) DESC, (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint)) DESC
    807801                    Sort Method: quicksort  Memory: 25kB
    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)
     802                    Buffers: shared hit=4705755 read=7205, temp read=1858 written=1863
     803                    ->  Hash Join  (cost=133310.05..133517.35 rows=230 width=349) (actual time=10845.768..11060.317 rows=2 loops=1)
    810804                          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)
     805                          Buffers: shared hit=4705755 read=7205, temp read=1858 written=1863
     806                          ->  Merge Left Join  (cost=133287.00..133493.70 rows=230 width=388) (actual time=10845.686..11060.231 rows=2 loops=1)
    813807                                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)
     808                                Buffers: shared hit=4705754 read=7205, temp read=1858 written=1863
     809                                ->  Merge Left Join  (cost=133285.52..133491.12 rows=230 width=380) (actual time=10845.563..11060.100 rows=2 loops=1)
    816810                                      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)
     811                                      Buffers: shared hit=4705753 read=7205, temp read=1858 written=1863
     812                                      ->  Merge Left Join  (cost=45355.37..45548.05 rows=230 width=106) (actual time=2279.274..2493.782 rows=2 loops=1)
    819813                                            Merge Cond: ((po.user_id)::text = (b.owner_id)::text)
    820814                                            Filter: (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint) > 0)
    821815                                            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)
     816                                            Buffers: shared hit=689775, temp read=1858 written=1863
     817                                            ->  Sort  (cost=49.44..51.16 rows=690 width=90) (actual time=0.048..0.053 rows=7 loops=1)
    824818                                                  Sort Key: po.user_id
    825819                                                  Sort Method: quicksort  Memory: 25kB
    826820                                                  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)
     821                                                  ->  Seq Scan on pet_owners po  (cost=0.00..16.90 rows=690 width=90) (actual time=0.036..0.038 rows=7 loops=1)
    828822                                                        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)
     823                                            ->  Materialize  (cost=45305.94..45495.13 rows=2 width=53) (actual time=2279.186..2493.682 rows=2 loops=1)
     824                                                  Buffers: shared hit=689774, temp read=1858 written=1863
     825                                                  ->  GroupAggregate  (cost=45305.94..45495.11 rows=2 width=53) (actual time=2279.181..2493.674 rows=2 loops=1)
    832826                                                        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)
     827                                                        Buffers: shared hit=689774, temp read=1858 written=1863
     828                                                        ->  Sort  (cost=45305.94..45353.23 rows=18915 width=78) (actual time=2279.104..2454.786 rows=166665 loops=1)
    835829                                                              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)
     830                                                              Sort Method: external merge  Disk: 14864kB
     831                                                              Buffers: shared hit=689774, temp read=1858 written=1863
     832                                                              ->  Nested Loop  (cost=1520.72..43962.29 rows=18915 width=78) (actual time=43.262..1631.393 rows=166665 loops=1)
     833                                                                    Buffers: shared hit=689774
     834                                                                    ->  Nested Loop  (cost=1520.29..25876.96 rows=18915 width=74) (actual time=43.200..340.897 rows=166665 loops=1)
    841835                                                                          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)
     836                                                                          ->  CTE Scan on params p  (cost=0.00..0.02 rows=1 width=8) (actual time=0.020..0.022 rows=1 loops=1)
     837                                                                          ->  Bitmap Heap Scan on bookings b  (cost=1520.29..25687.79 rows=18915 width=78) (actual time=43.171..311.549 rows=166665 loops=1)
    844838                                                                                Recheck Cond: ((date_from >= p.start_date) AND (date_from < p.end_date))
    845839                                                                                Filter: ((status)::text = 'Completed'::text)
    846                                                                                 Rows Removed by Filter: 832702
     840                                                                                Rows Removed by Filter: 833352
    847841                                                                                Heap Blocks: exact=22223
    848842                                                                                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)
     843                                                                                ->  Bitmap Index Scan on idx_bookings_date_owner  (cost=0.00..1515.57 rows=111114 width=0) (actual time=38.503..38.504 rows=1000017 loops=1)
    850844                                                                                      Index Cond: ((date_from >= p.start_date) AND (date_from < p.end_date))
    851845                                                                                      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)
     846                                                                    ->  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=166665)
    853847                                                                          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)
     848                                                                          Buffers: shared hit=666660
     849                                      ->  Materialize  (cost=87930.14..87942.48 rows=2 width=311) (actual time=8566.266..8566.290 rows=2 loops=1)
    856850                                            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)
     851                                            ->  Subquery Scan on service_counts  (cost=87930.14..87942.47 rows=2 width=311) (actual time=8566.261..8566.283 rows=2 loops=1)
    858852                                                  Filter: (service_counts.rank_num = 1)
    859853                                                  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)
     854                                                  ->  WindowAgg  (cost=87930.14..87937.72 rows=380 width=327) (actual time=8566.254..8566.274 rows=2 loops=1)
    861855                                                        Run Condition: (row_number() OVER (?) <= 1)
    862856                                                        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)
     857                                                        ->  Sort  (cost=87930.12..87931.07 rows=380 width=319) (actual time=8566.230..8566.236 rows=5 loops=1)
    864858                                                              Sort Key: b_1.owner_id, (count(bs.service_id)) DESC
    865859                                                              Sort Method: quicksort  Memory: 25kB
    866860                                                              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)
     861                                                              ->  HashAggregate  (cost=87910.04..87913.84 rows=380 width=319) (actual time=8566.200..8566.209 rows=5 loops=1)
    868862                                                                    Group Key: b_1.owner_id, s.type
    869                                                                     Batches: 1  Memory Usage: 40kB
     863                                                                    Batches: 1  Memory Usage: 37kB
    870864                                                                    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)
     865                                                                    ->  Hash Join  (cost=1558.04..87076.68 rows=111114 width=348) (actual time=41.196..8143.541 rows=1000017 loops=1)
    872866                                                                          Hash Cond: ((bs.service_id)::text = (s.service_id)::text)
    873867                                                                          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)
     868                                                                          ->  Nested Loop  (cost=1543.77..86764.23 rows=111114 width=74) (actual time=41.122..7819.032 rows=1000017 loops=1)
    875869                                                                                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)
     870                                                                                ->  Nested Loop  (cost=1543.34..26544.21 rows=111114 width=74) (actual time=41.009..461.885 rows=1000017 loops=1)
    877871                                                                                      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)
     872                                                                                      ->  CTE Scan on params p_1  (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.004 rows=1 loops=1)
     873                                                                                      ->  Bitmap Heap Scan on bookings b_1  (cost=1543.34..25433.05 rows=111114 width=78) (actual time=40.988..259.041 rows=1000017 loops=1)
    880874                                                                                            Recheck Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date))
    881875                                                                                            Heap Blocks: exact=22223
    … …  
    887881                                                                                      Index Cond: ((booking_id)::text = (b_1.booking_id)::text)
    888882                                                                                      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)
     883                                                                          ->  Hash  (cost=11.90..11.90 rows=190 width=364) (actual time=0.042..0.043 rows=4 loops=1)
    890884                                                                                Buckets: 1024  Batches: 1  Memory Usage: 9kB
    891885                                                                                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)
     886                                                                                ->  Seq Scan on services s  (cost=0.00..11.90 rows=190 width=364) (actual time=0.030..0.031 rows=4 loops=1)
    893887                                                                                      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)
     888                                ->  GroupAggregate  (cost=1.48..1.76 rows=16 width=98) (actual time=0.096..0.105 rows=6 loops=1)
    895889                                      Group Key: pets.owner_id
    896890                                      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)
     891                                      ->  Sort  (cost=1.48..1.52 rows=16 width=180) (actual time=0.060..0.062 rows=16 loops=1)
    898892                                            Sort Key: pets.owner_id
    899                                             Sort Method: quicksort  Memory: 27kB
     893                                            Sort Method: quicksort  Memory: 26kB
    900894                                            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)
     895                                            ->  Seq Scan on pets  (cost=0.00..1.16 rows=16 width=180) (actual time=0.029..0.034 rows=16 loops=1)
    902896                                                  Buffers: shared hit=1
    903897                          ->  Hash  (cost=15.80..15.80 rows=580 width=51) (actual time=0.046..0.047 rows=12 loops=1)
    … …  
    907901                                      Buffers: shared hit=1
    908902Planning:
    909   Buffers: shared hit=163 read=6 dirtied=2
    910 Planning Time: 2.821 ms
     903  Buffers: shared hit=706 read=6 dirtied=2
     904Planning Time: 5.877 ms
    911905JIT:
    912906  Functions: 84
    913907  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
    915 Execution 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 
    923 Because Sequential Scans and Hash Joins are better at processing millions of rows in large analytical reporting, we '''drop''' these indexes:
     908  Timing: Generation 5.747 ms (Deform 2.377 ms), Inlining 0.000 ms, Optimization 2.047 ms, Emission 53.180 ms, Total 60.973 ms
     909Execution Time: 11158.152 ms
     910}}}
     911
     912'''Execution time:''' 11158.152 ms
     913
     914'''Conclusion:''' The execution time increased from 5.3 seconds to 11.1 seconds. Creating foreign key indexes actually decreased the query performance.
     915
     916Because Sequential Scans and Hash Joins are better for processing millions of rows in large analytical reporting workloads, we '''drop''' these trap indexes to return to the previous performance state:
    924917{{{
    925918#!sql
    926919DROP INDEX project.idx_booking_services_booking_id;
     920DROP INDEX project.idx_payments_booking_id;
    927921DROP INDEX project.idx_bookings_date_sitter;
    928 }}}
     922DROP INDEX project.idx_bookings_date_owner;
     923}}}
     924
    929925
    930926