Changes between Version 9 and Version 10 of OtherTopics
- Timestamp:
- 08/28/26 00:53:04 (5 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
OtherTopics
v9 v10 10 10 ALTER TABLE project.bookings DISABLE TRIGGER trg_booking_validation; 11 11 12 INSERT INTO project.bookings (status, date_from, date_to, address, owner_id, sitter_id )12 INSERT INTO project.bookings (status, date_from, date_to, address, owner_id, sitter_id, service_id) 13 13 SELECT 14 14 (ARRAY['Pending', 'Confirmed', 'Completed', 'Canceled', 'Expired', 'Reviewed'])[floor(random()*6)+1] AS status, … … 17 17 'Mock Address ' || gs AS address, 18 18 o.user_id AS owner_id, 19 s.user_id AS sitter_id 19 s.user_id AS sitter_id, 20 sv.service_id AS service_id 20 21 FROM generate_series(1, 1000000) gs 21 22 JOIN LATERAL ( … … 25 26 SELECT user_id FROM project.pet_sitters ORDER BY random() LIMIT 1 26 27 ) s ON true 28 JOIN LATERAL ( 29 SELECT service_id FROM project.services ORDER BY random() LIMIT 1 30 ) sv ON true 27 31 JOIN LATERAL ( 28 32 SELECT CURRENT_DATE - (random() * INTERVAL '365 days') AS dt … … 78 82 }}} 79 83 80 {{{81 #!sql82 -- 5. Link random services to bookings83 INSERT INTO project.booking_services (booking_id, service_id)84 SELECT85 b.booking_id,86 s.service_id87 FROM project.bookings b88 JOIN LATERAL (89 SELECT service_id FROM project.services ORDER BY random() LIMIT 190 ) s ON true91 WHERE NOT EXISTS (92 SELECT 1 FROM project.booking_services bs WHERE bs.booking_id = b.booking_id93 );94 }}}95 96 84 Benchmark query: 97 85 {{{ … … 106 94 107 95 {{{ 108 Gather Merge (cost= 81029.64..195548.38 rows=967764 width=147) (actual time=102.092..113.433 rows=2 loops=1)96 Gather Merge (cost=118911.59..233428.55 rows=967749 width=183) (actual time=85.181..92.703 rows=2 loops=1) 109 97 Workers Planned: 3 110 98 Workers Launched: 3 111 Buffers: shared hit=2 233599 Buffers: shared hit=27139 112 100 InitPlan 1 113 -> Limit (cost=0.00..0.02 rows=1 width=90) (actual time= 6.105..6.106rows=1 loops=1)101 -> Limit (cost=0.00..0.02 rows=1 width=90) (actual time=3.311..3.312 rows=1 loops=1) 114 102 Buffers: shared hit=1 115 -> Seq Scan on pet_sitters (cost=0.00..16.90 rows=690 width=90) (actual time=0.0 28..0.028rows=1 loops=1)103 -> Seq Scan on pet_sitters (cost=0.00..16.90 rows=690 width=90) (actual time=0.018..0.019 rows=1 loops=1) 116 104 Buffers: shared hit=1 117 -> Sort (cost= 80029.57..80836.04 rows=322588 width=147) (actual time=65.204..65.206rows=0 loops=4)105 -> Sort (cost=117911.52..118717.98 rows=322583 width=183) (actual time=54.026..54.027 rows=0 loops=4) 118 106 Sort Key: bookings.date_from DESC 119 107 Sort Method: quicksort Memory: 25kB 120 Buffers: shared hit=2 2334108 Buffers: shared hit=27138 121 109 Worker 0: Sort Method: quicksort Memory: 25kB 122 110 Worker 1: Sort Method: quicksort Memory: 25kB 123 111 Worker 2: Sort Method: quicksort Memory: 25kB 124 -> Parallel Seq Scan on bookings (cost=0.00.. 26255.35 rows=322588 width=147) (actual time=41.358..65.056rows=0 loops=4)112 -> Parallel Seq Scan on bookings (cost=0.00..31059.29 rows=322583 width=183) (actual time=33.675..53.902 rows=0 loops=4) 125 113 Filter: ((sitter_id)::text = ((InitPlan 1).col1)::text) 126 Rows Removed by Filter: 250005 127 Buffers: shared hit=22223 128 Planning: 129 Buffers: shared hit=139 read=1 130 Planning Time: 1.125 ms 114 Rows Removed by Filter: 250002 115 Buffers: shared hit=27027 116 Planning Time: 0.168 ms 131 117 JIT: 132 118 Functions: 12 133 119 Options: Inlining false, Optimization false, Expressions true, Deforming true 134 Timing: Generation 1. 536 ms (Deform 0.703 ms), Inlining 0.000 ms, Optimization 1.491 ms, Emission 21.760 ms, Total 24.788ms135 Execution Time: 148.520ms136 }}} 137 138 '''Average execution time (10 attempts):''' 145.812ms120 Timing: Generation 1.400 ms (Deform 0.651 ms), Inlining 0.000 ms, Optimization 1.233 ms, Emission 18.598 ms, Total 21.231 ms 121 Execution Time: 93.143 ms 122 }}} 123 124 '''Average execution time (10 attempts):''' 93.093ms 139 125 140 126 Next, we add this index: … … 155 141 156 142 {{{ 157 Index Scan using idx_bookings_sitter_date on bookings (cost=0.45.. 72505.21 rows=1000022 width=147) (actual time=0.101..0.104rows=2 loops=1)143 Index Scan using idx_bookings_sitter_date on bookings (cost=0.45..83614.21 rows=1000008 width=183) (actual time=0.051..0.053 rows=2 loops=1) 158 144 Index Cond: ((sitter_id)::text = ((InitPlan 1).col1)::text) 159 Buffers: shared hit= 2 read=3145 Buffers: shared hit=5 160 146 InitPlan 1 161 -> Limit (cost=0.00..0.02 rows=1 width=90) (actual time=0.0 40..0.040rows=1 loops=1)147 -> Limit (cost=0.00..0.02 rows=1 width=90) (actual time=0.018..0.019 rows=1 loops=1) 162 148 Buffers: shared hit=1 163 -> Seq Scan on pet_sitters (cost=0.00..16.90 rows=690 width=90) (actual time=0.0 38..0.038rows=1 loops=1)149 -> Seq Scan on pet_sitters (cost=0.00..16.90 rows=690 width=90) (actual time=0.017..0.017 rows=1 loops=1) 164 150 Buffers: shared hit=1 165 Planning: 166 Buffers: shared hit=124 read=5 167 Planning Time: 0.969 ms 168 Execution Time: 0.132 ms 169 }}} 170 171 '''Average execution time (10 attempts):''' 0.125ms 151 Planning Time: 0.179 ms 152 Execution Time: 0.081 ms 153 }}} 154 155 '''Average execution time (10 attempts):''' 0.082ms 172 156 173 157 Because the execution time has been massively lowered by bypassing the expensive sequential scan and memory sort, we '''keep''' this index. … … 189 173 190 174 {{{ 191 Finalize Aggregate (cost=3 2123.42..32123.43 rows=1 width=32) (actual time=106.390..116.499rows=1 loops=1)192 Buffers: shared hit=2 4792175 Finalize Aggregate (cost=36930.42..36930.43 rows=1 width=32) (actual time=114.078..121.145 rows=1 loops=1) 176 Buffers: shared hit=29598 193 177 InitPlan 1 194 -> Limit (cost=0.00..0.02 rows=1 width=90) (actual time=0.01 3..0.014rows=1 loops=1)178 -> Limit (cost=0.00..0.02 rows=1 width=90) (actual time=0.014..0.015 rows=1 loops=1) 195 179 Buffers: shared hit=1 196 -> Seq Scan on pet_sitters (cost=0.00..16.90 rows=690 width=90) (actual time=0.01 1..0.012rows=1 loops=1)180 -> Seq Scan on pet_sitters (cost=0.00..16.90 rows=690 width=90) (actual time=0.012..0.013 rows=1 loops=1) 197 181 Buffers: shared hit=1 198 -> Gather (cost=3 2123.08..32123.39 rows=3 width=32) (actual time=104.969..116.470rows=4 loops=1)182 -> Gather (cost=36930.08..36930.39 rows=3 width=32) (actual time=112.766..121.113 rows=4 loops=1) 199 183 Workers Planned: 3 200 184 Workers Launched: 3 201 Buffers: shared hit=2 4792202 -> Partial Aggregate (cost=3 1123.08..31123.09 rows=1 width=32) (actual time=75.769..75.773rows=1 loops=4)203 Buffers: shared hit=2 4791204 -> Parallel Hash Join (cost=392 6.81..31028.95 rows=37651 width=4) (actual time=62.399..75.767rows=0 loops=4)185 Buffers: shared hit=29598 186 -> Partial Aggregate (cost=35930.08..35930.09 rows=1 width=32) (actual time=86.975..86.979 rows=1 loops=4) 187 Buffers: shared hit=29597 188 -> Parallel Hash Join (cost=3929.82..35835.89 rows=37676 width=4) (actual time=71.573..86.969 rows=0 loops=4) 205 189 Hash Cond: ((b.booking_id)::text = (r.booking_id)::text) 206 Buffers: shared hit=2 4791207 -> Parallel Seq Scan on bookings b (cost=0.00.. 26255.35 rows=322588 width=37) (actual time=40.115..53.479rows=0 loops=4)190 Buffers: shared hit=29597 191 -> Parallel Seq Scan on bookings b (cost=0.00..31059.29 rows=322583 width=37) (actual time=46.200..61.590 rows=0 loops=4) 208 192 Filter: ((sitter_id)::text = ((InitPlan 1).col1)::text) 209 Rows Removed by Filter: 25000 5210 Buffers: shared hit=2 2223211 -> Parallel Hash (cost=30 68.58..3068.58 rows=68658 width=41) (actual time=21.453..21.454 rows=29180loops=4)212 Buckets: 131072 Batches: 1 Memory Usage: 102 08kB213 Buffers: shared hit=238 2214 -> Parallel Seq Scan on reviews r (cost=0.00..30 68.58 rows=68658 width=41) (actual time=0.026..7.819 rows=29180loops=4)215 Buffers: shared hit=238 2193 Rows Removed by Filter: 250002 194 Buffers: shared hit=27027 195 -> Parallel Hash (cost=3071.03..3071.03 rows=68703 width=41) (actual time=24.567..24.568 rows=29199 loops=4) 196 Buckets: 131072 Batches: 1 Memory Usage: 10240kB 197 Buffers: shared hit=2384 198 -> Parallel Seq Scan on reviews r (cost=0.00..3071.03 rows=68703 width=41) (actual time=0.027..8.694 rows=29199 loops=4) 199 Buffers: shared hit=2384 216 200 Planning: 217 Buffers: shared hit=1 65 read=2218 Planning Time: 1.2 00ms219 Execution Time: 1 16.627 ms220 }}} 221 222 '''Average execution time (10 attempts):''' 11 5.143ms201 Buffers: shared hit=170 dirtied=1 202 Planning Time: 1.219 ms 203 Execution Time: 121.247 ms 204 }}} 205 206 '''Average execution time (10 attempts):''' 116.413ms 223 207 224 208 We add this index: … … 233 217 234 218 {{{ 235 Finalize Aggregate (cost=3 2123.42..32123.43 rows=1 width=32) (actual time=102.850..113.265rows=1 loops=1)236 Buffers: shared hit=2 4792219 Finalize Aggregate (cost=36930.42..36930.43 rows=1 width=32) (actual time=114.523..121.209 rows=1 loops=1) 220 Buffers: shared hit=29598 237 221 InitPlan 1 238 222 -> Limit (cost=0.00..0.02 rows=1 width=90) (actual time=0.014..0.016 rows=1 loops=1) … … 240 224 -> 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 225 Buffers: shared hit=1 242 -> Gather (cost=3 2123.08..32123.39 rows=3 width=32) (actual time=101.457..113.235rows=4 loops=1)226 -> Gather (cost=36930.08..36930.39 rows=3 width=32) (actual time=113.185..121.193 rows=4 loops=1) 243 227 Workers Planned: 3 244 228 Workers Launched: 3 245 Buffers: shared hit=2 4792246 -> Partial Aggregate (cost=3 1123.08..31123.09 rows=1 width=32) (actual time=72.199..72.202rows=1 loops=4)247 Buffers: shared hit=2 4791248 -> Parallel Hash Join (cost=392 6.81..31028.95 rows=37651 width=4) (actual time=59.642..72.196 rows=0 loops=4)229 Buffers: shared hit=29598 230 -> Partial Aggregate (cost=35930.08..35930.09 rows=1 width=32) (actual time=83.754..83.757 rows=1 loops=4) 231 Buffers: shared hit=29597 232 -> Parallel Hash Join (cost=3929.82..35835.89 rows=37676 width=4) (actual time=68.170..83.746 rows=0 loops=4) 249 233 Hash Cond: ((b.booking_id)::text = (r.booking_id)::text) 250 Buffers: shared hit=2 4791251 -> Parallel Seq Scan on bookings b (cost=0.00.. 26255.35 rows=322588 width=37) (actual time=37.082..49.436rows=0 loops=4)234 Buffers: shared hit=29597 235 -> Parallel Seq Scan on bookings b (cost=0.00..31059.29 rows=322583 width=37) (actual time=46.730..62.303 rows=0 loops=4) 252 236 Filter: ((sitter_id)::text = ((InitPlan 1).col1)::text) 253 Rows Removed by Filter: 25000 5254 Buffers: shared hit=2 2223255 -> Parallel Hash (cost=30 68.58..3068.58 rows=68658 width=41) (actual time=21.936..21.937 rows=29180loops=4)237 Rows Removed by Filter: 250002 238 Buffers: shared hit=27027 239 -> Parallel Hash (cost=3071.03..3071.03 rows=68703 width=41) (actual time=20.662..20.663 rows=29199 loops=4) 256 240 Buckets: 131072 Batches: 1 Memory Usage: 10240kB 257 Buffers: shared hit=238 2258 -> Parallel Seq Scan on reviews r (cost=0.00..30 68.58 rows=68658 width=41) (actual time=0.020..7.757 rows=29180loops=4)259 Buffers: shared hit=238 2241 Buffers: shared hit=2384 242 -> Parallel Seq Scan on reviews r (cost=0.00..3071.03 rows=68703 width=41) (actual time=0.023..7.095 rows=29199 loops=4) 243 Buffers: shared hit=2384 260 244 Planning: 261 Buffers: shared hit= 78read=6262 Planning Time: 1.082ms263 Execution Time: 1 13.336ms264 }}} 265 266 '''Average execution time (10 attempts ):''' 114.288ms245 Buffers: shared hit=35 read=6 246 Planning Time: 0.815 ms 247 Execution Time: 121.271 ms 248 }}} 249 250 '''Average execution time (10 attempts, first cold-cache run excluded):''' 116.521ms 267 251 268 252 There 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. … … 342 326 Execution without indexes: 343 327 {{{ 344 Limit (cost=1 54247.04..154247.06 rows=10 width=176) (actual time=2600.384..2611.703 rows=4loops=1)345 Buffers: shared hit=7 35904 read=1328 Limit (cost=164076.47..164076.50 rows=10 width=808) (actual time=2449.436..2457.032 rows=3 loops=1) 329 Buffers: shared hit=752817 346 330 CTE params 347 -> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.01 2..0.013rows=1 loops=1)348 -> Sort (cost=1 54247.02..154247.59 rows=230 width=176) (actual time=2544.398..2555.714 rows=4loops=1)331 -> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1) 332 -> Sort (cost=164076.45..164077.03 rows=230 width=808) (actual time=2398.618..2406.213 rows=3 loops=1) 349 333 Sort Key: (dense_rank() OVER (?)) 350 Sort Method: quicksort Memory: 2 6kB351 Buffers: shared hit=7 35904 read=1352 -> WindowAgg (cost=1 54232.29..154242.05 rows=230 width=176) (actual time=2544.338..2555.668 rows=4loops=1)353 Buffers: shared hit=7 35901 read=1354 -> Sort (cost=1 54232.27..154232.85 rows=230 width=152) (actual time=2544.310..2555.625 rows=4loops=1)334 Sort Method: quicksort Memory: 25kB 335 Buffers: shared hit=752817 336 -> WindowAgg (cost=164061.72..164071.48 rows=230 width=808) (actual time=2398.587..2406.195 rows=3 loops=1) 337 Buffers: shared hit=752817 338 -> Sort (cost=164061.71..164062.28 rows=230 width=784) (actual time=2398.570..2406.164 rows=3 loops=1) 355 339 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 356 340 Sort Method: quicksort Memory: 25kB 357 Buffers: shared hit=735901 read=1 358 -> Hash Join (cost=154197.60..154223.25 rows=230 width=152) (actual time=2544.282..2555.608 rows=4 loops=1) 359 Hash Cond: ((u.user_id)::text = (ps.user_id)::text) 360 Buffers: shared hit=735898 read=1 361 -> Seq Scan on users u (cost=0.00..15.80 rows=580 width=64) (actual time=0.041..0.045 rows=12 loops=1) 362 Buffers: shared read=1 363 -> Hash (cost=154194.72..154194.72 rows=230 width=146) (actual time=2544.166..2555.480 rows=4 loops=1) 364 Buckets: 1024 Batches: 1 Memory Usage: 9kB 365 Buffers: shared hit=735898 366 -> Merge Left Join (cost=154190.07..154194.72 rows=230 width=146) (actual time=2544.131..2555.465 rows=4 loops=1) 367 Merge Cond: ((ps.user_id)::text = (sr.sitter_id)::text) 368 Buffers: shared hit=735898 369 -> Merge Left Join (cost=109447.58..109451.64 rows=230 width=122) (actual time=2328.303..2328.329 rows=4 loops=1) 370 Merge Cond: ((ps.user_id)::text = (sf.sitter_id)::text) 371 Buffers: shared hit=711107 372 -> Merge Left Join (cost=48939.75..48943.22 rows=230 width=114) (actual time=724.264..724.279 rows=4 loops=1) 373 Merge Cond: ((ps.user_id)::text = (ss.sitter_id)::text) 374 Filter: (COALESCE(ss.total_bookings, '0'::bigint) > 0) 375 Buffers: shared hit=22224 376 -> Sort (cost=49.44..51.16 rows=690 width=90) (actual time=0.018..0.021 rows=4 loops=1) 377 Sort Key: ps.user_id 378 Sort Method: quicksort Memory: 25kB 341 Buffers: shared hit=752817 342 -> Hash Join (cost=164041.68..164052.68 rows=230 width=784) (actual time=2398.538..2406.155 rows=3 loops=1) 343 Hash Cond: ((ps.user_id)::text = (u.user_id)::text) 344 Buffers: shared hit=752817 345 -> Merge Left Join (cost=164030.78..164035.43 rows=230 width=146) (actual time=2398.463..2406.072 rows=3 loops=1) 346 Merge Cond: ((ps.user_id)::text = (sr.sitter_id)::text) 347 Buffers: shared hit=752816 348 -> Merge Left Join (cost=114472.54..114476.60 rows=230 width=122) (actual time=2187.057..2187.080 rows=3 loops=1) 349 Merge Cond: ((ps.user_id)::text = (sf.sitter_id)::text) 350 Buffers: shared hit=723219 351 -> Merge Left Join (cost=53743.37..53746.84 rows=230 width=114) (actual time=689.450..689.463 rows=3 loops=1) 352 Merge Cond: ((ps.user_id)::text = (ss.sitter_id)::text) 353 Filter: (COALESCE(ss.total_bookings, '0'::bigint) > 0) 354 Rows Removed by Filter: 3 355 Buffers: shared hit=27028 356 -> Sort (cost=49.44..51.16 rows=690 width=90) (actual time=0.017..0.021 rows=6 loops=1) 357 Sort Key: ps.user_id 358 Sort Method: quicksort Memory: 25kB 359 Buffers: shared hit=1 360 -> Seq Scan on pet_sitters ps (cost=0.00..16.90 rows=690 width=90) (actual time=0.007..0.008 rows=6 loops=1) 379 361 Buffers: shared hit=1 380 -> 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) 381 Buffers: shared hit=1 382 -> Sort (cost=48890.31..48890.32 rows=1 width=61) (actual time=724.208..724.212 rows=4 loops=1) 383 Sort Key: ss.sitter_id 384 Sort Method: quicksort Memory: 25kB 385 Buffers: shared hit=22223 386 -> Subquery Scan on ss (cost=48890.28..48890.30 rows=1 width=61) (actual time=724.190..724.196 rows=4 loops=1) 387 Buffers: shared hit=22223 388 -> HashAggregate (cost=48890.28..48890.29 rows=1 width=61) (actual time=724.182..724.187 rows=4 loops=1) 389 Group Key: b.sitter_id 390 Batches: 1 Memory Usage: 24kB 391 Buffers: shared hit=22223 392 -> Nested Loop (cost=0.00..47223.57 rows=111114 width=83) (actual time=0.044..359.696 rows=1000017 loops=1) 393 Join Filter: ((b.date_from >= p.start_date) AND (b.date_from < p.end_date)) 394 Rows Removed by Join Filter: 5 395 Buffers: shared hit=22223 396 -> CTE Scan on params p (cost=0.00..0.02 rows=1 width=8) (actual time=0.015..0.017 rows=1 loops=1) 397 -> Seq Scan on bookings b (cost=0.00..32223.22 rows=1000022 width=87) (actual time=0.013..106.436 rows=1000022 loops=1) 398 Buffers: shared hit=22223 399 -> Sort (cost=60507.84..60507.84 rows=1 width=45) (actual time=1604.016..1604.022 rows=2 loops=1) 400 Sort Key: sf.sitter_id 362 -> Sort (cost=53693.93..53693.94 rows=1 width=61) (actual time=689.401..689.404 rows=3 loops=1) 363 Sort Key: ss.sitter_id 401 364 Sort Method: quicksort Memory: 25kB 402 Buffers: shared hit= 688883403 -> Subquery Scan on s f (cost=60507.81..60507.83 rows=1 width=45) (actual time=1603.989..1603.995 rows=2loops=1)404 Buffers: shared hit= 688883405 -> HashAggregate (cost= 60507.81..60507.82 rows=1 width=45) (actual time=1603.982..1603.986 rows=2loops=1)406 Group Key: b _1.sitter_id365 Buffers: shared hit=27027 366 -> Subquery Scan on ss (cost=53693.90..53693.92 rows=1 width=61) (actual time=689.386..689.391 rows=3 loops=1) 367 Buffers: shared hit=27027 368 -> HashAggregate (cost=53693.90..53693.91 rows=1 width=61) (actual time=689.380..689.384 rows=3 loops=1) 369 Group Key: b.sitter_id 407 370 Batches: 1 Memory Usage: 24kB 408 Buffers: shared hit=688883 409 -> Nested Loop (cost=0.42..60413.23 rows=18915 width=41) (actual time=0.129..1550.156 rows=166665 loops=1) 410 Buffers: shared hit=688883 411 -> Nested Loop (cost=0.00..37276.85 rows=18915 width=74) (actual time=0.043..232.987 rows=166665 loops=1) 412 Join Filter: ((b_1.date_from >= p_1.start_date) AND (b_1.date_from < p_1.end_date)) 413 Rows Removed by Join Filter: 3 414 Buffers: shared hit=22223 415 -> 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) 416 -> 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) 417 Filter: ((status)::text = 'Completed'::text) 418 Rows Removed by Filter: 833354 419 Buffers: shared hit=22223 420 -> 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) 421 Index Cond: ((booking_id)::text = (b_1.booking_id)::text) 422 Buffers: shared hit=666660 423 -> Sort (cost=44742.49..44742.49 rows=1 width=61) (actual time=215.803..227.106 rows=2 loops=1) 424 Sort Key: sr.sitter_id 371 Buffers: shared hit=27027 372 -> Nested Loop (cost=0.00..52027.22 rows=111112 width=82) (actual time=0.035..322.377 rows=1000004 loops=1) 373 Join Filter: ((b.date_from >= p.start_date) AND (b.date_from < p.end_date)) 374 Rows Removed by Join Filter: 4 375 Buffers: shared hit=27027 376 -> CTE Scan on params p (cost=0.00..0.02 rows=1 width=8) (actual time=0.013..0.016 rows=1 loops=1) 377 -> Seq Scan on bookings b (cost=0.00..37027.08 rows=1000008 width=86) (actual time=0.008..87.323 rows=1000008 loops=1) 378 Buffers: shared hit=27027 379 -> Sort (cost=60729.17..60729.18 rows=1 width=45) (actual time=1497.586..1497.591 rows=3 loops=1) 380 Sort Key: sf.sitter_id 425 381 Sort Method: quicksort Memory: 25kB 426 Buffers: shared hit= 24791427 -> Subquery Scan on s r (cost=44742.45..44742.48 rows=1 width=61) (actual time=215.773..227.079 rows=2loops=1)428 Buffers: shared hit= 24791429 -> HashAggregate (cost= 44742.45..44742.47 rows=1 width=61) (actual time=215.766..227.070 rows=2loops=1)430 Group Key: b_ 2.sitter_id382 Buffers: shared hit=696191 383 -> Subquery Scan on sf (cost=60729.14..60729.16 rows=1 width=45) (actual time=1497.566..1497.572 rows=3 loops=1) 384 Buffers: shared hit=696191 385 -> HashAggregate (cost=60729.14..60729.15 rows=1 width=45) (actual time=1497.560..1497.564 rows=3 loops=1) 386 Group Key: b_1.sitter_id 431 387 Batches: 1 Memory Usage: 24kB 432 Buffers: shared hit=24791 433 -> Nested Loop (cost=4926.81..44645.18 rows=12969 width=78) (actual time=68.121..195.697 rows=116717 loops=1) 434 Join Filter: ((b_2.date_from >= p_2.start_date) AND (b_2.date_from < p_2.end_date)) 435 Rows Removed by Join Filter: 2 436 Buffers: shared hit=24791 437 -> 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) 438 -> Gather (cost=4926.81..42894.38 rows=116719 width=82) (actual time=68.087..176.699 rows=116719 loops=1) 439 Workers Planned: 3 440 Workers Launched: 3 441 Buffers: shared hit=24791 442 -> Parallel Hash Join (cost=3926.81..30222.48 rows=37651 width=82) (actual time=36.517..159.380 rows=29180 loops=4) 443 Hash Cond: ((b_2.booking_id)::text = (r.booking_id)::text) 444 Buffers: shared hit=24791 445 -> 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) 446 Buffers: shared hit=22223 447 -> Parallel Hash (cost=3068.58..3068.58 rows=68658 width=78) (actual time=35.713..35.714 rows=29180 loops=4) 448 Buckets: 131072 Batches: 1 Memory Usage: 13856kB 449 Buffers: shared hit=2382 450 -> 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) 451 Buffers: shared hit=2382 388 Buffers: shared hit=696191 389 -> Nested Loop (cost=0.42..60638.55 rows=18119 width=41) (actual time=0.105..1448.561 rows=167291 loops=1) 390 Buffers: shared hit=696191 391 -> Nested Loop (cost=0.00..41973.14 rows=18119 width=74) (actual time=0.037..226.674 rows=167291 loops=1) 392 Join Filter: ((b_1.date_from >= p_1.start_date) AND (b_1.date_from < p_1.end_date)) 393 Buffers: shared hit=27027 394 -> CTE Scan on params p_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1) 395 -> Seq Scan on bookings b_1 (cost=0.00..39527.10 rows=163068 width=78) (actual time=0.023..187.662 rows=167291 loops=1) 396 Filter: ((status)::text = 'Completed'::text) 397 Rows Removed by Filter: 832717 398 Buffers: shared hit=27027 399 -> Index Scan using payments_booking_id_key on payments pay (cost=0.42..1.03 rows=1 width=41) (actual time=0.007..0.007 rows=1 loops=167291) 400 Index Cond: ((booking_id)::text = (b_1.booking_id)::text) 401 Buffers: shared hit=669164 402 -> Sort (cost=49558.24..49558.24 rows=1 width=61) (actual time=211.385..218.968 rows=3 loops=1) 403 Sort Key: sr.sitter_id 404 Sort Method: quicksort Memory: 25kB 405 Buffers: shared hit=29597 406 -> Subquery Scan on sr (cost=49558.20..49558.23 rows=1 width=61) (actual time=211.362..218.949 rows=3 loops=1) 407 Buffers: shared hit=29597 408 -> HashAggregate (cost=49558.20..49558.22 rows=1 width=61) (actual time=211.355..218.941 rows=3 loops=1) 409 Group Key: b_2.sitter_id 410 Batches: 1 Memory Usage: 24kB 411 Buffers: shared hit=29597 412 -> Nested Loop (cost=4929.82..49460.87 rows=12977 width=78) (actual time=64.080..186.743 rows=116794 loops=1) 413 Join Filter: ((b_2.date_from >= p_2.start_date) AND (b_2.date_from < p_2.end_date)) 414 Rows Removed by Join Filter: 1 415 Buffers: shared hit=29597 416 -> 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) 417 -> Gather (cost=4929.82..47708.93 rows=116795 width=82) (actual time=64.066..167.308 rows=116795 loops=1) 418 Workers Planned: 3 419 Workers Launched: 3 420 Buffers: shared hit=29597 421 -> Parallel Hash Join (cost=3929.82..35029.43 rows=37676 width=82) (actual time=35.327..157.583 rows=29199 loops=4) 422 Hash Cond: ((b_2.booking_id)::text = (r.booking_id)::text) 423 Buffers: shared hit=29597 424 -> Parallel Seq Scan on bookings b_2 (cost=0.00..30252.83 rows=322583 width=78) (actual time=0.013..32.216 rows=250002 loops=4) 425 Buffers: shared hit=27027 426 -> Parallel Hash (cost=3071.03..3071.03 rows=68703 width=78) (actual time=34.538..34.539 rows=29199 loops=4) 427 Buckets: 131072 Batches: 1 Memory Usage: 13888kB 428 Buffers: shared hit=2384 429 -> Parallel Seq Scan on reviews r (cost=0.00..3071.03 rows=68703 width=78) (actual time=8.890..15.815 rows=29199 loops=4) 430 Buffers: shared hit=2384 431 -> Hash (cost=10.40..10.40 rows=40 width=696) (actual time=0.044..0.045 rows=14 loops=1) 432 Buckets: 1024 Batches: 1 Memory Usage: 10kB 433 Buffers: shared hit=1 434 -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=696) (actual time=0.027..0.031 rows=14 loops=1) 435 Buffers: shared hit=1 452 436 Planning: 453 Buffers: shared hit= 227 read=5454 Planning Time: 3.010 ms437 Buffers: shared hit=32 438 Planning Time: 1.520 ms 455 439 JIT: 456 Functions: 11 5440 Functions: 114 457 441 Options: Inlining false, Optimization false, Expressions true, Deforming true 458 Timing: Generation 9.518 ms (Deform 4.386 ms), Inlining 0.000 ms, Optimization 4.262 ms, Emission 89.334 ms, Total 103.115ms459 Execution Time: 2 619.146ms460 }}} 461 462 '''Execution time:''' 2 619.146ms442 Timing: Generation 7.398 ms (Deform 3.509 ms), Inlining 0.000 ms, Optimization 3.331 ms, Emission 83.328 ms, Total 94.056 ms 443 Execution Time: 2461.823 ms 444 }}} 445 446 '''Execution time:''' 2461.823 ms 463 447 464 448 We attempt to optimize this analytical query by introducing multiple foreign key indexes: … … 468 452 CREATE INDEX idx_bookings_date_owner ON project.bookings (date_from, owner_id); 469 453 CREATE INDEX idx_payments_booking_id ON project.payments (booking_id); 470 CREATE INDEX idx_booking _services_booking_id ON project.booking_services (booking_id);454 CREATE INDEX idx_bookings_service_id ON project.bookings (service_id); 471 455 CREATE INDEX idx_pets_owner_id ON project.pets (owner_id); 472 456 }}} … … 474 458 Execution with indexes: 475 459 {{{ 476 Limit (cost=1 10101.62..110101.65 rows=10 width=176) (actual time=3500.078..3500.100 rows=4loops=1)477 Buffers: shared hit=7 38392, temp read=6439 written=6439460 Limit (cost=124470.23..124470.26 rows=10 width=808) (actual time=3543.349..3543.370 rows=3 loops=1) 461 Buffers: shared hit=747209 read=8095, temp read=6446 written=6446 478 462 CTE params 479 -> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.0 27..0.028rows=1 loops=1)480 -> Sort (cost=1 10101.60..110102.18 rows=230 width=176) (actual time=3442.513..3442.532 rows=4loops=1)463 -> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1) 464 -> Sort (cost=124470.21..124470.79 rows=230 width=808) (actual time=3493.039..3493.057 rows=3 loops=1) 481 465 Sort Key: (dense_rank() OVER (?)) 482 Sort Method: quicksort Memory: 2 6kB483 Buffers: shared hit=7 38392, temp read=6439 written=6439484 -> WindowAgg (cost=1 10086.87..110096.63 rows=230 width=176) (actual time=3442.451..3442.485 rows=4loops=1)485 Buffers: shared hit=7 38389, temp read=6439 written=6439486 -> Sort (cost=1 10086.86..110087.43 rows=230 width=152) (actual time=3442.434..3442.453 rows=4loops=1)466 Sort Method: quicksort Memory: 25kB 467 Buffers: shared hit=747209 read=8095, temp read=6446 written=6446 468 -> WindowAgg (cost=124455.49..124465.24 rows=230 width=808) (actual time=3493.003..3493.034 rows=3 loops=1) 469 Buffers: shared hit=747209 read=8095, temp read=6446 written=6446 470 -> Sort (cost=124455.47..124456.04 rows=230 width=784) (actual time=3492.986..3493.004 rows=3 loops=1) 487 471 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 488 472 Sort Method: quicksort Memory: 25kB 489 Buffers: shared hit=738389, temp read=6439 written=6439 490 -> Hash Join (cost=110052.18..110077.83 rows=230 width=152) (actual time=3442.406..3442.435 rows=4 loops=1) 491 Hash Cond: ((u.user_id)::text = (ps.user_id)::text) 492 Buffers: shared hit=738386, temp read=6439 written=6439 493 -> Seq Scan on users u (cost=0.00..15.80 rows=580 width=64) (actual time=0.015..0.020 rows=12 loops=1) 473 Buffers: shared hit=747209 read=8095, temp read=6446 written=6446 474 -> Hash Join (cost=124435.44..124446.45 rows=230 width=784) (actual time=3492.954..3492.993 rows=3 loops=1) 475 Hash Cond: ((ps.user_id)::text = (u.user_id)::text) 476 Buffers: shared hit=747209 read=8095, temp read=6446 written=6446 477 -> Merge Left Join (cost=124424.54..124429.19 rows=230 width=146) (actual time=3492.879..3492.911 rows=3 loops=1) 478 Merge Cond: ((ps.user_id)::text = (sr.sitter_id)::text) 479 Buffers: shared hit=747208 read=8095, temp read=6446 written=6446 480 -> Merge Left Join (cost=81887.48..81891.54 rows=230 width=122) (actual time=2431.288..2431.313 rows=3 loops=1) 481 Merge Cond: ((ps.user_id)::text = (sf.sitter_id)::text) 482 Buffers: shared hit=716906 read=8095 483 -> Merge Left Join (cost=33064.29..33067.76 rows=230 width=114) (actual time=773.072..773.086 rows=3 loops=1) 484 Merge Cond: ((ps.user_id)::text = (ss.sitter_id)::text) 485 Filter: (COALESCE(ss.total_bookings, '0'::bigint) > 0) 486 Rows Removed by Filter: 3 487 Buffers: shared hit=27028 read=891 488 -> Sort (cost=49.44..51.16 rows=690 width=90) (actual time=0.018..0.022 rows=6 loops=1) 489 Sort Key: ps.user_id 490 Sort Method: quicksort Memory: 25kB 491 Buffers: shared hit=1 492 -> Seq Scan on pet_sitters ps (cost=0.00..16.90 rows=690 width=90) (actual time=0.007..0.008 rows=6 loops=1) 493 Buffers: shared hit=1 494 -> Sort (cost=33014.85..33014.86 rows=1 width=61) (actual time=773.022..773.026 rows=3 loops=1) 495 Sort Key: ss.sitter_id 496 Sort Method: quicksort Memory: 25kB 497 Buffers: shared hit=27027 read=891 498 -> Subquery Scan on ss (cost=33014.82..33014.84 rows=1 width=61) (actual time=773.007..773.012 rows=3 loops=1) 499 Buffers: shared hit=27027 read=891 500 -> HashAggregate (cost=33014.82..33014.83 rows=1 width=61) (actual time=773.000..773.004 rows=3 loops=1) 501 Group Key: b.sitter_id 502 Batches: 1 Memory Usage: 24kB 503 Buffers: shared hit=27027 read=891 504 -> Nested Loop (cost=1543.32..31348.14 rows=111112 width=83) (actual time=51.171..407.886 rows=1000004 loops=1) 505 Buffers: shared hit=27027 read=891 506 -> CTE Scan on params p (cost=0.00..0.02 rows=1 width=8) (actual time=0.013..0.015 rows=1 loops=1) 507 -> Bitmap Heap Scan on bookings b (cost=1543.32..30237.00 rows=111112 width=87) (actual time=51.137..216.994 rows=1000004 loops=1) 508 Recheck Cond: ((date_from >= p.start_date) AND (date_from < p.end_date)) 509 Heap Blocks: exact=27027 510 Buffers: shared hit=27027 read=891 511 -> Bitmap Index Scan on idx_bookings_date_owner (cost=0.00..1515.55 rows=111112 width=0) (actual time=45.474..45.474 rows=1000004 loops=1) 512 Index Cond: ((date_from >= p.start_date) AND (date_from < p.end_date)) 513 Buffers: shared read=891 514 -> Sort (cost=48823.19..48823.19 rows=1 width=45) (actual time=1658.196..1658.201 rows=3 loops=1) 515 Sort Key: sf.sitter_id 516 Sort Method: quicksort Memory: 25kB 517 Buffers: shared hit=689878 read=7204 518 -> Subquery Scan on sf (cost=48823.16..48823.18 rows=1 width=45) (actual time=1658.175..1658.182 rows=3 loops=1) 519 Buffers: shared hit=689878 read=7204 520 -> HashAggregate (cost=48823.16..48823.17 rows=1 width=45) (actual time=1658.168..1658.173 rows=3 loops=1) 521 Group Key: b_1.sitter_id 522 Batches: 1 Memory Usage: 24kB 523 Buffers: shared hit=689878 read=7204 524 -> Nested Loop (cost=1520.68..48728.99 rows=18833 width=41) (actual time=42.283..1607.525 rows=167291 loops=1) 525 Buffers: shared hit=689878 read=7204 526 -> Nested Loop (cost=1520.25..30680.06 rows=18833 width=74) (actual time=42.160..304.743 rows=167291 loops=1) 527 Buffers: shared hit=27918 528 -> 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) 529 -> Bitmap Heap Scan on bookings b_1 (cost=1520.25..30491.71 rows=18833 width=78) (actual time=42.148..272.927 rows=167291 loops=1) 530 Recheck Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date)) 531 Filter: ((status)::text = 'Completed'::text) 532 Rows Removed by Filter: 832713 533 Heap Blocks: exact=27027 534 Buffers: shared hit=27918 535 -> Bitmap Index Scan on idx_bookings_date_owner (cost=0.00..1515.55 rows=111112 width=0) (actual time=36.497..36.497 rows=1000004 loops=1) 536 Index Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date)) 537 Buffers: shared hit=891 538 -> 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=167291) 539 Index Cond: ((booking_id)::text = (b_1.booking_id)::text) 540 Buffers: shared hit=661960 read=7204 541 -> Sort (cost=42537.06..42537.07 rows=1 width=61) (actual time=1061.568..1061.573 rows=3 loops=1) 542 Sort Key: sr.sitter_id 543 Sort Method: quicksort Memory: 25kB 544 Buffers: shared hit=30302, temp read=6446 written=6446 545 -> Subquery Scan on sr (cost=42537.03..42537.05 rows=1 width=61) (actual time=1061.543..1061.552 rows=3 loops=1) 546 Buffers: shared hit=30302, temp read=6446 written=6446 547 -> HashAggregate (cost=42537.03..42537.04 rows=1 width=61) (actual time=1061.536..1061.544 rows=3 loops=1) 548 Group Key: b_2.sitter_id 549 Batches: 1 Memory Usage: 24kB 550 Buffers: shared hit=30302, temp read=6446 written=6446 551 -> Hash Join (cost=8038.21..42439.70 rows=12977 width=78) (actual time=128.073..1025.179 rows=116794 loops=1) 552 Hash Cond: ((b_2.booking_id)::text = (r.booking_id)::text) 553 Buffers: shared hit=30302, temp read=6446 written=6446 554 -> Nested Loop (cost=1543.32..31348.14 rows=111112 width=74) (actual time=42.544..438.183 rows=1000004 loops=1) 555 Buffers: shared hit=27918 556 -> 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) 557 -> Bitmap Heap Scan on bookings b_2 (cost=1543.32..30237.00 rows=111112 width=78) (actual time=42.521..231.784 rows=1000004 loops=1) 558 Recheck Cond: ((date_from >= p_2.start_date) AND (date_from < p_2.end_date)) 559 Heap Blocks: exact=27027 560 Buffers: shared hit=27918 561 -> Bitmap Index Scan on idx_bookings_date_owner (cost=0.00..1515.55 rows=111112 width=0) (actual time=36.900..36.900 rows=1000004 loops=1) 562 Index Cond: ((date_from >= p_2.start_date) AND (date_from < p_2.end_date)) 563 Buffers: shared hit=891 564 -> Hash (cost=3551.95..3551.95 rows=116795 width=78) (actual time=85.342..85.343 rows=116795 loops=1) 565 Buckets: 131072 Batches: 2 Memory Usage: 7256kB 566 Buffers: shared hit=2384, temp written=703 567 -> Seq Scan on reviews r (cost=0.00..3551.95 rows=116795 width=78) (actual time=0.028..31.128 rows=116795 loops=1) 568 Buffers: shared hit=2384 569 -> Hash (cost=10.40..10.40 rows=40 width=696) (actual time=0.044..0.045 rows=14 loops=1) 570 Buckets: 1024 Batches: 1 Memory Usage: 10kB 494 571 Buffers: shared hit=1 495 -> Hash (cost=110049.30..110049.30 rows=230 width=146) (actual time=3442.349..3442.367 rows=4 loops=1) 496 Buckets: 1024 Batches: 1 Memory Usage: 9kB 497 Buffers: shared hit=738385, temp read=6439 written=6439 498 -> Merge Left Join (cost=110044.65..110049.30 rows=230 width=146) (actual time=3442.306..3442.342 rows=4 loops=1) 499 Merge Cond: ((ps.user_id)::text = (sr.sitter_id)::text) 500 Buffers: shared hit=738385, temp read=6439 written=6439 501 -> Merge Left Join (cost=72317.28..72321.34 rows=230 width=122) (actual time=2386.271..2386.298 rows=4 loops=1) 502 Merge Cond: ((ps.user_id)::text = (sf.sitter_id)::text) 503 Buffers: shared hit=712889 504 -> Merge Left Join (cost=28260.39..28263.86 rows=230 width=114) (actual time=797.385..797.401 rows=4 loops=1) 505 Merge Cond: ((ps.user_id)::text = (ss.sitter_id)::text) 506 Filter: (COALESCE(ss.total_bookings, '0'::bigint) > 0) 507 Buffers: shared hit=23115 508 -> Sort (cost=49.44..51.16 rows=690 width=90) (actual time=0.024..0.026 rows=4 loops=1) 509 Sort Key: ps.user_id 510 Sort Method: quicksort Memory: 25kB 511 Buffers: shared hit=1 512 -> 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) 513 Buffers: shared hit=1 514 -> Sort (cost=28210.95..28210.96 rows=1 width=61) (actual time=797.322..797.327 rows=4 loops=1) 515 Sort Key: ss.sitter_id 516 Sort Method: quicksort Memory: 25kB 517 Buffers: shared hit=23114 518 -> Subquery Scan on ss (cost=28210.92..28210.94 rows=1 width=61) (actual time=797.304..797.311 rows=4 loops=1) 519 Buffers: shared hit=23114 520 -> HashAggregate (cost=28210.92..28210.93 rows=1 width=61) (actual time=797.297..797.302 rows=4 loops=1) 521 Group Key: b.sitter_id 522 Batches: 1 Memory Usage: 24kB 523 Buffers: shared hit=23114 524 -> Nested Loop (cost=1543.34..26544.21 rows=111114 width=83) (actual time=43.375..439.128 rows=1000017 loops=1) 525 Buffers: shared hit=23114 526 -> CTE Scan on params p (cost=0.00..0.02 rows=1 width=8) (actual time=0.030..0.033 rows=1 loops=1) 527 -> 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) 528 Recheck Cond: ((date_from >= p.start_date) AND (date_from < p.end_date)) 529 Heap Blocks: exact=22223 530 Buffers: shared hit=23114 531 -> 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) 532 Index Cond: ((date_from >= p.start_date) AND (date_from < p.end_date)) 533 Buffers: shared hit=891 534 -> Sort (cost=44056.89..44056.90 rows=1 width=45) (actual time=1588.861..1588.866 rows=2 loops=1) 535 Sort Key: sf.sitter_id 536 Sort Method: quicksort Memory: 25kB 537 Buffers: shared hit=689774 538 -> Subquery Scan on sf (cost=44056.86..44056.88 rows=1 width=45) (actual time=1588.837..1588.844 rows=2 loops=1) 539 Buffers: shared hit=689774 540 -> HashAggregate (cost=44056.86..44056.87 rows=1 width=45) (actual time=1588.829..1588.834 rows=2 loops=1) 541 Group Key: b_1.sitter_id 542 Batches: 1 Memory Usage: 24kB 543 Buffers: shared hit=689774 544 -> Nested Loop (cost=1520.72..43962.29 rows=18915 width=41) (actual time=41.086..1537.591 rows=166665 loops=1) 545 Buffers: shared hit=689774 546 -> Nested Loop (cost=1520.29..25876.96 rows=18915 width=74) (actual time=41.016..285.706 rows=166665 loops=1) 547 Buffers: shared hit=23114 548 -> 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) 549 -> 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) 550 Recheck Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date)) 551 Filter: ((status)::text = 'Completed'::text) 552 Rows Removed by Filter: 833352 553 Heap Blocks: exact=22223 554 Buffers: shared hit=23114 555 -> 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) 556 Index Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date)) 557 Buffers: shared hit=891 558 -> 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) 559 Index Cond: ((booking_id)::text = (b_1.booking_id)::text) 560 Buffers: shared hit=666660 561 -> Sort (cost=37727.37..37727.37 rows=1 width=61) (actual time=1056.010..1056.015 rows=2 loops=1) 562 Sort Key: sr.sitter_id 563 Sort Method: quicksort Memory: 25kB 564 Buffers: shared hit=25496, temp read=6439 written=6439 565 -> Subquery Scan on sr (cost=37727.33..37727.36 rows=1 width=61) (actual time=1055.985..1055.994 rows=2 loops=1) 566 Buffers: shared hit=25496, temp read=6439 written=6439 567 -> HashAggregate (cost=37727.33..37727.35 rows=1 width=61) (actual time=1055.977..1055.984 rows=2 loops=1) 568 Group Key: b_2.sitter_id 569 Batches: 1 Memory Usage: 24kB 570 Buffers: shared hit=25496, temp read=6439 written=6439 571 -> Hash Join (cost=8033.52..37630.07 rows=12969 width=78) (actual time=121.902..1017.507 rows=116717 loops=1) 572 Hash Cond: ((b_2.booking_id)::text = (r.booking_id)::text) 573 Buffers: shared hit=25496, temp read=6439 written=6439 574 -> Nested Loop (cost=1543.34..26544.21 rows=111114 width=74) (actual time=40.177..418.927 rows=1000017 loops=1) 575 Buffers: shared hit=23114 576 -> 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) 577 -> 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) 578 Recheck Cond: ((date_from >= p_2.start_date) AND (date_from < p_2.end_date)) 579 Heap Blocks: exact=22223 580 Buffers: shared hit=23114 581 -> 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) 582 Index Cond: ((date_from >= p_2.start_date) AND (date_from < p_2.end_date)) 583 Buffers: shared hit=891 584 -> Hash (cost=3549.19..3549.19 rows=116719 width=78) (actual time=81.514..81.515 rows=116719 loops=1) 585 Buckets: 131072 Batches: 2 Memory Usage: 7251kB 586 Buffers: shared hit=2382, temp written=702 587 -> Seq Scan on reviews r (cost=0.00..3549.19 rows=116719 width=78) (actual time=0.025..30.472 rows=116719 loops=1) 588 Buffers: shared hit=2382 572 -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=696) (actual time=0.027..0.030 rows=14 loops=1) 573 Buffers: shared hit=1 589 574 Planning: 590 Buffers: shared hit= 668591 Planning Time: 5.315ms575 Buffers: shared hit=148 read=9 576 Planning Time: 2.643 ms 592 577 JIT: 593 Functions: 8 1578 Functions: 80 594 579 Options: Inlining false, Optimization false, Expressions true, Deforming true 595 Timing: Generation 6.369 ms (Deform 2.765 ms), Inlining 0.000 ms, Optimization 2.281 ms, Emission 55.664 ms, Total 64.314ms596 Execution Time: 354 0.806ms597 }}} 598 599 '''Execution time:''' 354 0.806ms600 601 Instead of improving performance, adding these indexes actually '''increased''' the execution time by nearly 1 second. The indexes are also used in Scenario 4 below.580 Timing: Generation 5.241 ms (Deform 2.016 ms), Inlining 0.000 ms, Optimization 1.677 ms, Emission 48.982 ms, Total 55.900 ms 581 Execution Time: 3549.793 ms 582 }}} 583 584 '''Execution time:''' 3549.793 ms 585 586 Instead of improving performance, adding these indexes actually '''increased''' the execution time by more than 1 second. The indexes are also evaluated in Scenario 4 below. 602 587 603 588 === 4. Highest Paying Customers Analytics (Phase 6 Query) === … … 627 612 b.owner_id, 628 613 s.type AS service_type, 629 COUNT(b s.service_id) AS times_booked,630 ROW_NUMBER() OVER(PARTITION BY b.owner_id ORDER BY COUNT(b s.service_id) DESC) as rank_num614 COUNT(b.service_id) AS times_booked, 615 ROW_NUMBER() OVER(PARTITION BY b.owner_id ORDER BY COUNT(b.service_id) DESC) as rank_num 631 616 FROM project.bookings b 632 JOIN project.booking_services bs ON b.booking_id = bs.booking_id 633 JOIN project.services s ON bs.service_id = s.service_id 617 JOIN project.services s ON b.service_id = s.service_id 634 618 JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date 635 619 GROUP BY b.owner_id, s.type … … 668 652 Execution without indexes: 669 653 {{{ 670 Limit (cost=1 70067.13..170067.15 rows=10 width=115) (actual time=5322.701..5322.720 rows=2loops=1)671 Buffers: shared hit=72 6269, temp read=26853 written=26858654 Limit (cost=115376.54..115376.56 rows=10 width=702) (actual time=3272.796..3272.811 rows=3 loops=1) 655 Buffers: shared hit=723223, temp read=1865 written=1870 672 656 CTE params 673 -> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.01 3..0.014rows=1 loops=1)674 -> Sort (cost=1 70067.11..170067.68 rows=230 width=115) (actual time=5264.981..5264.999 rows=2loops=1)657 -> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1) 658 -> Sort (cost=115376.52..115377.09 rows=230 width=702) (actual time=3225.301..3225.314 rows=3 loops=1) 675 659 Sort Key: (dense_rank() OVER (?)) 676 660 Sort Method: quicksort Memory: 25kB 677 Buffers: shared hit=72 6269, temp read=26853 written=26858678 -> WindowAgg (cost=1 70057.56..170062.14 rows=230 width=115) (actual time=5264.923..5264.951 rows=2loops=1)679 Buffers: shared hit=72 6266, temp read=26853 written=26858680 -> Sort (cost=1 70057.54..170058.11 rows=230 width=349) (actual time=5264.888..5264.905 rows=2loops=1)661 Buffers: shared hit=723223, temp read=1865 written=1870 662 -> WindowAgg (cost=115366.97..115371.55 rows=230 width=702) (actual time=3225.271..3225.294 rows=3 loops=1) 663 Buffers: shared hit=723223, temp read=1865 written=1870 664 -> Sort (cost=115366.95..115367.52 rows=230 width=936) (actual time=3225.246..3225.259 rows=3 loops=1) 681 665 Sort Key: (COALESCE((sum(pay.amount)), '0'::bigint)) DESC, (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint)) DESC 682 666 Sort Method: quicksort Memory: 25kB 683 Buffers: shared hit=72 6266, temp read=26853 written=26858684 -> Hash Join (cost=1 69840.97..170048.52 rows=230 width=349) (actual time=5049.927..5264.888 rows=2loops=1)667 Buffers: shared hit=723223, temp read=1865 written=1870 668 -> Hash Join (cost=115165.28..115357.93 rows=230 width=936) (actual time=3014.108..3225.250 rows=3 loops=1) 685 669 Hash Cond: ((po.user_id)::text = (u.user_id)::text) 686 Buffers: shared hit=72 6266, temp read=26853 written=26858687 -> Merge Left Join (cost=1 69817.92..170024.86 rows=230 width=388) (actual time=5049.843..5264.798 rows=2loops=1)688 Merge Cond: ((po.user_id)::text = (p ets.owner_id)::text)689 Buffers: shared hit=72 6265, temp read=26853 written=26858690 -> Merge Left Join (cost=1 69816.17..170021.77 rows=230 width=380) (actual time=5049.710..5264.655 rows=2loops=1)670 Buffers: shared hit=723223, temp read=1865 written=1870 671 -> Merge Left Join (cost=115154.38..115346.42 rows=230 width=388) (actual time=3014.042..3225.179 rows=3 loops=1) 672 Merge Cond: ((po.user_id)::text = (pp.owner_id)::text) 673 Buffers: shared hit=723222, temp read=1865 written=1870 674 -> Merge Left Join (cost=115151.59..115343.02 rows=230 width=380) (actual time=3013.930..3225.062 rows=3 loops=1) 691 675 Merge Cond: ((po.user_id)::text = (service_counts.owner_id)::text) 692 Buffers: shared hit=72 6264, temp read=26853 written=26858693 -> Merge Left Join (cost=61 806.32..61998.99 rows=230 width=106) (actual time=2266.934..2481.844 rows=2loops=1)676 Buffers: shared hit=723220, temp read=1865 written=1870 677 -> Merge Left Join (cost=61969.47..62154.15 rows=230 width=106) (actual time=2010.978..2222.079 rows=3 loops=1) 694 678 Merge Cond: ((po.user_id)::text = (b.owner_id)::text) 695 679 Filter: (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint) > 0) 696 Rows Removed by Filter: 5697 Buffers: shared hit=6 88884, temp read=1858 written=1863698 -> Sort (cost=49.44..51.16 rows=690 width=90) (actual time=0.0 40..0.044 rows=7loops=1)680 Rows Removed by Filter: 3 681 Buffers: shared hit=696192, temp read=1865 written=1870 682 -> Sort (cost=49.44..51.16 rows=690 width=90) (actual time=0.018..0.021 rows=6 loops=1) 699 683 Sort Key: po.user_id 700 684 Sort Method: quicksort Memory: 25kB 701 685 Buffers: shared hit=1 702 -> Seq Scan on pet_owners po (cost=0.00..16.90 rows=690 width=90) (actual time=0.0 27..0.028 rows=7loops=1)686 -> Seq Scan on pet_owners po (cost=0.00..16.90 rows=690 width=90) (actual time=0.007..0.008 rows=6 loops=1) 703 687 Buffers: shared hit=1 704 -> Materialize (cost=61 756.88..61946.08 rows=2 width=53) (actual time=2266.857..2481.755 rows=2loops=1)705 Buffers: shared hit=6 88883, temp read=1858 written=1863706 -> GroupAggregate (cost=61 756.88..61946.05 rows=2 width=53) (actual time=2266.852..2481.746 rows=2loops=1)688 -> Materialize (cost=61920.03..62101.25 rows=1 width=53) (actual time=2010.929..2222.022 rows=3 loops=1) 689 Buffers: shared hit=696191, temp read=1865 written=1870 690 -> GroupAggregate (cost=61920.03..62101.23 rows=1 width=53) (actual time=2010.925..2222.015 rows=3 loops=1) 707 691 Group Key: b.owner_id 708 Buffers: shared hit=6 88883, temp read=1858 written=1863709 -> Sort (cost=61 756.88..61804.17 rows=18915 width=78) (actual time=2266.784..2442.950 rows=166665loops=1)692 Buffers: shared hit=696191, temp read=1865 written=1870 693 -> Sort (cost=61920.03..61965.33 rows=18119 width=78) (actual time=2010.866..2182.374 rows=167291 loops=1) 710 694 Sort Key: b.owner_id, b.booking_id 711 Sort Method: external merge Disk: 14 864kB712 Buffers: shared hit=6 88883, temp read=1858 written=1863713 -> Nested Loop (cost=0.42..60 413.23 rows=18915 width=78) (actual time=0.185..1616.303 rows=166665loops=1)714 Buffers: shared hit=6 88883715 -> Nested Loop (cost=0.00.. 37276.85 rows=18915 width=74) (actual time=0.057..274.855 rows=166665loops=1)695 Sort Method: external merge Disk: 14920kB 696 Buffers: shared hit=696191, temp read=1865 written=1870 697 -> Nested Loop (cost=0.42..60638.55 rows=18119 width=78) (actual time=0.072..1416.845 rows=167291 loops=1) 698 Buffers: shared hit=696191 699 -> Nested Loop (cost=0.00..41973.14 rows=18119 width=74) (actual time=0.039..218.948 rows=167291 loops=1) 716 700 Join Filter: ((b.date_from >= p.start_date) AND (b.date_from < p.end_date)) 717 Rows Removed by Join Filter: 3 718 Buffers: shared hit=22223 719 -> CTE Scan on params p (cost=0.00..0.02 rows=1 width=8) (actual time=0.015..0.017 rows=1 loops=1) 720 -> Seq Scan on bookings b (cost=0.00..34723.28 rows=170237 width=78) (actual time=0.026..235.609 rows=166668 loops=1) 701 Buffers: shared hit=27027 702 -> CTE Scan on params p (cost=0.00..0.02 rows=1 width=8) (actual time=0.013..0.015 rows=1 loops=1) 703 -> Seq Scan on bookings b (cost=0.00..39527.10 rows=163068 width=78) (actual time=0.015..183.349 rows=167291 loops=1) 721 704 Filter: ((status)::text = 'Completed'::text) 722 Rows Removed by Filter: 83 3354723 Buffers: shared hit=2 2223724 -> 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)705 Rows Removed by Filter: 832717 706 Buffers: shared hit=27027 707 -> Index Scan using payments_booking_id_key on payments pay (cost=0.42..1.03 rows=1 width=41) (actual time=0.007..0.007 rows=1 loops=167291) 725 708 Index Cond: ((booking_id)::text = (b.booking_id)::text) 726 Buffers: shared hit=66 6660727 -> Materialize (cost= 108009.85..108022.19 rows=2 width=311) (actual time=2782.752..2782.780 rows=2loops=1)728 Buffers: shared hit= 37380, temp read=24995 written=24995729 -> Subquery Scan on service_counts (cost= 108009.85..108022.18 rows=2 width=311) (actual time=2782.740..2782.766 rows=2loops=1)709 Buffers: shared hit=669164 710 -> Materialize (cost=53182.12..53188.28 rows=1 width=311) (actual time=1002.931..1002.956 rows=3 loops=1) 711 Buffers: shared hit=27028 712 -> Subquery Scan on service_counts (cost=53182.12..53188.28 rows=1 width=311) (actual time=1002.926..1002.947 rows=3 loops=1) 730 713 Filter: (service_counts.rank_num = 1) 731 Buffers: shared hit= 37380, temp read=24995 written=24995732 -> WindowAgg (cost= 108009.85..108017.43 rows=380 width=327) (actual time=2782.732..2782.756 rows=2loops=1)714 Buffers: shared hit=27028 715 -> WindowAgg (cost=53182.12..53185.90 rows=190 width=327) (actual time=1002.919..1002.938 rows=3 loops=1) 733 716 Run Condition: (row_number() OVER (?) <= 1) 734 Buffers: shared hit= 37380, temp read=24995 written=24995735 -> Sort (cost= 108009.83..108010.78 rows=380 width=319) (actual time=2782.709..2782.717 rows=5loops=1)736 Sort Key: b_1.owner_id, (count(b s.service_id)) DESC717 Buffers: shared hit=27028 718 -> Sort (cost=53182.10..53182.58 rows=190 width=319) (actual time=1002.896..1002.900 rows=4 loops=1) 719 Sort Key: b_1.owner_id, (count(b_1.service_id)) DESC 737 720 Sort Method: quicksort Memory: 25kB 738 Buffers: shared hit= 37380, temp read=24995 written=24995739 -> HashAggregate (cost= 107989.75..107993.55 rows=380 width=319) (actual time=2782.681..2782.692 rows=5loops=1)721 Buffers: shared hit=27028 722 -> HashAggregate (cost=53173.01..53174.91 rows=190 width=319) (actual time=1002.872..1002.878 rows=4 loops=1) 740 723 Group Key: b_1.owner_id, s.type 741 Batches: 1 Memory Usage: 37kB 742 Buffers: shared hit=37380, temp read=24995 written=24995 743 -> Hash Join (cost=50037.77..107156.39 rows=111114 width=348) (actual time=792.873..2447.981 rows=1000017 loops=1) 744 Hash Cond: ((bs.service_id)::text = (s.service_id)::text) 745 Buffers: shared hit=37380, temp read=24995 written=24995 746 -> Hash Join (cost=50023.50..106843.94 rows=111114 width=74) (actual time=792.796..2158.768 rows=1000017 loops=1) 747 Hash Cond: ((bs.booking_id)::text = (b_1.booking_id)::text) 748 Buffers: shared hit=37379, temp read=24995 written=24995 749 -> 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) 750 Buffers: shared hit=15156 751 -> Hash (cost=47223.57..47223.57 rows=111114 width=74) (actual time=789.943..789.945 rows=1000017 loops=1) 752 Buckets: 131072 (originally 131072) Batches: 16 (originally 2) Memory Usage: 7487kB 753 Buffers: shared hit=22223, temp written=10750 754 -> Nested Loop (cost=0.00..47223.57 rows=111114 width=74) (actual time=0.034..347.150 rows=1000017 loops=1) 755 Join Filter: ((b_1.date_from >= p_1.start_date) AND (b_1.date_from < p_1.end_date)) 756 Rows Removed by Join Filter: 5 757 Buffers: shared hit=22223 758 -> 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) 759 -> 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) 760 Buffers: shared hit=22223 761 -> Hash (cost=11.90..11.90 rows=190 width=364) (actual time=0.044..0.045 rows=4 loops=1) 724 Batches: 1 Memory Usage: 40kB 725 Buffers: shared hit=27028 726 -> Hash Join (cost=14.28..52339.67 rows=111112 width=348) (actual time=0.092..643.148 rows=1000004 loops=1) 727 Hash Cond: ((b_1.service_id)::text = (s.service_id)::text) 728 Buffers: shared hit=27028 729 -> Nested Loop (cost=0.00..52027.22 rows=111112 width=74) (actual time=0.034..342.830 rows=1000004 loops=1) 730 Join Filter: ((b_1.date_from >= p_1.start_date) AND (b_1.date_from < p_1.end_date)) 731 Rows Removed by Join Filter: 4 732 Buffers: shared hit=27027 733 -> 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) 734 -> Seq Scan on bookings b_1 (cost=0.00..37027.08 rows=1000008 width=78) (actual time=0.014..88.185 rows=1000008 loops=1) 735 Buffers: shared hit=27027 736 -> Hash (cost=11.90..11.90 rows=190 width=364) (actual time=0.029..0.030 rows=4 loops=1) 762 737 Buckets: 1024 Batches: 1 Memory Usage: 9kB 763 738 Buffers: shared hit=1 764 -> Seq Scan on services s (cost=0.00..11.90 rows=190 width=364) (actual time=0.0 31..0.033rows=4 loops=1)739 -> Seq Scan on services s (cost=0.00..11.90 rows=190 width=364) (actual time=0.017..0.019 rows=4 loops=1) 765 740 Buffers: shared hit=1 766 -> GroupAggregate (cost=1.75..2.15 rows=23 width=98) (actual time=0.093..0.104 rows=6 loops=1) 767 Group Key: pets.owner_id 768 Buffers: shared hit=1 769 -> Sort (cost=1.75..1.81 rows=23 width=180) (actual time=0.068..0.071 rows=16 loops=1) 770 Sort Key: pets.owner_id 771 Sort Method: quicksort Memory: 26kB 772 Buffers: shared hit=1 773 -> Seq Scan on pets (cost=0.00..1.23 rows=23 width=180) (actual time=0.030..0.034 rows=16 loops=1) 774 Buffers: shared hit=1 775 -> Hash (cost=15.80..15.80 rows=580 width=51) (actual time=0.057..0.058 rows=12 loops=1) 776 Buckets: 1024 Batches: 1 Memory Usage: 9kB 741 -> Sort (cost=2.78..2.80 rows=6 width=45) (actual time=0.091..0.093 rows=6 loops=1) 742 Sort Key: pp.owner_id 743 Sort Method: quicksort Memory: 25kB 744 Buffers: shared hit=2 745 -> Subquery Scan on pp (cost=2.59..2.71 rows=6 width=45) (actual time=0.071..0.074 rows=6 loops=1) 746 Buffers: shared hit=2 747 -> HashAggregate (cost=2.59..2.65 rows=6 width=45) (actual time=0.066..0.067 rows=6 loops=1) 748 Group Key: pets.owner_id 749 Batches: 1 Memory Usage: 24kB 750 Buffers: shared hit=2 751 -> Seq Scan on pets (cost=0.00..2.39 rows=39 width=74) (actual time=0.018..0.023 rows=39 loops=1) 752 Buffers: shared hit=2 753 -> Hash (cost=10.40..10.40 rows=40 width=638) (actual time=0.043..0.044 rows=14 loops=1) 754 Buckets: 1024 Batches: 1 Memory Usage: 10kB 777 755 Buffers: shared hit=1 778 -> Seq Scan on users u (cost=0.00..1 5.80 rows=580 width=51) (actual time=0.033..0.036 rows=12loops=1)756 -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=638) (actual time=0.027..0.030 rows=14 loops=1) 779 757 Buffers: shared hit=1 780 758 Planning: 781 Buffers: shared hit= 570782 Planning Time: 4.934 ms759 Buffers: shared hit=16 760 Planning Time: 1.344 ms 783 761 JIT: 784 Functions: 85762 Functions: 79 785 763 Options: Inlining false, Optimization false, Expressions true, Deforming true 786 Timing: Generation 6.167 ms (Deform 2.815 ms), Inlining 0.000 ms, Optimization 2.209 ms, Emission 55.891 ms, Total 64.267ms787 Execution Time: 5367.798ms788 }}} 789 790 '''Execution time:''' 5367.798ms764 Timing: Generation 4.177 ms (Deform 1.933 ms), Inlining 0.000 ms, Optimization 1.555 ms, Emission 46.200 ms, Total 51.932 ms 765 Execution Time: 3280.637 ms 766 }}} 767 768 '''Execution time:''' 3280.637 ms 791 769 792 770 Execution with the indexes we created in Scenario 3: 793 771 {{{ 794 Limit (cost= 133535.97..133535.99 rows=10 width=115) (actual time=11115.289..11115.307 rows=2loops=1)795 Buffers: shared hit= 4705758 read=7205, temp read=1858 written=1863772 Limit (cost=82850.79..82850.82 rows=10 width=702) (actual time=3399.874..3399.891 rows=3 loops=1) 773 Buffers: shared hit=725005, temp read=1865 written=1870 796 774 CTE params 797 -> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.0 13..0.014rows=1 loops=1)798 -> Sort (cost= 133535.95..133536.52 rows=230 width=115) (actual time=11060.413..11060.429 rows=2loops=1)775 -> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1) 776 -> Sort (cost=82850.77..82851.35 rows=230 width=702) (actual time=3399.872..3399.888 rows=3 loops=1) 799 777 Sort Key: (dense_rank() OVER (?)) 800 778 Sort Method: quicksort Memory: 25kB 801 Buffers: shared hit= 4705758 read=7205, temp read=1858 written=1863802 -> WindowAgg (cost= 133526.40..133530.98 rows=230 width=115) (actual time=11060.340..11060.367 rows=2loops=1)803 Buffers: shared hit= 4705755 read=7205, temp read=1858 written=1863804 -> Sort (cost= 133526.38..133526.95 rows=230 width=349) (actual time=11060.310..11060.326 rows=2loops=1)779 Buffers: shared hit=725005, temp read=1865 written=1870 780 -> WindowAgg (cost=82841.22..82845.80 rows=230 width=702) (actual time=3399.860..3399.880 rows=3 loops=1) 781 Buffers: shared hit=725005, temp read=1865 written=1870 782 -> Sort (cost=82841.20..82841.78 rows=230 width=936) (actual time=3399.854..3399.869 rows=3 loops=1) 805 783 Sort Key: (COALESCE((sum(pay.amount)), '0'::bigint)) DESC, (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint)) DESC 806 784 Sort Method: quicksort Memory: 25kB 807 Buffers: shared hit= 4705755 read=7205, temp read=1858 written=1863808 -> Hash Join (cost= 133310.05..133517.35 rows=230 width=349) (actual time=10845.768..11060.317 rows=2loops=1)785 Buffers: shared hit=725005, temp read=1865 written=1870 786 -> Hash Join (cost=82632.39..82832.18 rows=230 width=936) (actual time=3190.126..3399.860 rows=3 loops=1) 809 787 Hash Cond: ((po.user_id)::text = (u.user_id)::text) 810 Buffers: shared hit= 4705755 read=7205, temp read=1858 written=1863811 -> Merge Left Join (cost= 133287.00..133493.70 rows=230 width=388) (actual time=10845.686..11060.231 rows=2loops=1)812 Merge Cond: ((po.user_id)::text = (p ets.owner_id)::text)813 Buffers: shared hit= 4705754 read=7205, temp read=1858 written=1863814 -> Merge Left Join (cost= 133285.52..133491.12 rows=230 width=380) (actual time=10845.563..11060.100 rows=2loops=1)788 Buffers: shared hit=725005, temp read=1865 written=1870 789 -> Merge Left Join (cost=82621.49..82820.68 rows=230 width=388) (actual time=3190.090..3399.819 rows=3 loops=1) 790 Merge Cond: ((po.user_id)::text = (pp.owner_id)::text) 791 Buffers: shared hit=725004, temp read=1865 written=1870 792 -> Merge Left Join (cost=82618.71..82817.27 rows=230 width=380) (actual time=3190.017..3399.739 rows=3 loops=1) 815 793 Merge Cond: ((po.user_id)::text = (service_counts.owner_id)::text) 816 Buffers: shared hit= 4705753 read=7205, temp read=1858 written=1863817 -> Merge Left Join (cost= 45355.37..45548.05 rows=230 width=106) (actual time=2279.274..2493.782 rows=2loops=1)794 Buffers: shared hit=725002, temp read=1865 written=1870 795 -> Merge Left Join (cost=50115.66..50307.48 rows=230 width=106) (actual time=2121.267..2330.955 rows=3 loops=1) 818 796 Merge Cond: ((po.user_id)::text = (b.owner_id)::text) 819 797 Filter: (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint) > 0) 820 Rows Removed by Filter: 5821 Buffers: shared hit=6 89775, temp read=1858 written=1863822 -> Sort (cost=49.44..51.16 rows=690 width=90) (actual time=0.0 48..0.053 rows=7loops=1)798 Rows Removed by Filter: 3 799 Buffers: shared hit=697083, temp read=1865 written=1870 800 -> Sort (cost=49.44..51.16 rows=690 width=90) (actual time=0.019..0.022 rows=6 loops=1) 823 801 Sort Key: po.user_id 824 802 Sort Method: quicksort Memory: 25kB 825 803 Buffers: shared hit=1 826 -> Seq Scan on pet_owners po (cost=0.00..16.90 rows=690 width=90) (actual time=0.0 36..0.038 rows=7loops=1)804 -> Seq Scan on pet_owners po (cost=0.00..16.90 rows=690 width=90) (actual time=0.007..0.008 rows=6 loops=1) 827 805 Buffers: shared hit=1 828 -> Materialize (cost= 45305.94..45495.13 rows=2 width=53) (actual time=2279.186..2493.682 rows=2loops=1)829 Buffers: shared hit=6 89774, temp read=1858 written=1863830 -> GroupAggregate (cost= 45305.94..45495.11 rows=2 width=53) (actual time=2279.181..2493.674 rows=2loops=1)806 -> Materialize (cost=50066.23..50254.58 rows=1 width=53) (actual time=2121.239..2330.916 rows=3 loops=1) 807 Buffers: shared hit=697082, temp read=1865 written=1870 808 -> GroupAggregate (cost=50066.23..50254.57 rows=1 width=53) (actual time=2121.236..2330.908 rows=3 loops=1) 831 809 Group Key: b.owner_id 832 Buffers: shared hit=6 89774, temp read=1858 written=1863833 -> Sort (cost= 45305.94..45353.23 rows=18915 width=78) (actual time=2279.104..2454.786 rows=166665loops=1)810 Buffers: shared hit=697082, temp read=1865 written=1870 811 -> Sort (cost=50066.23..50113.31 rows=18833 width=78) (actual time=2121.221..2290.350 rows=167291 loops=1) 834 812 Sort Key: b.owner_id, b.booking_id 835 Sort Method: external merge Disk: 14 864kB836 Buffers: shared hit=6 89774, temp read=1858 written=1863837 -> Nested Loop (cost=1520. 72..43962.29 rows=18915 width=78) (actual time=43.262..1631.393 rows=166665loops=1)838 Buffers: shared hit=6 89774839 -> Nested Loop (cost=1520.2 9..25876.96 rows=18915 width=74) (actual time=43.200..340.897 rows=166665loops=1)840 Buffers: shared hit=2 3114841 -> CTE Scan on params p (cost=0.00..0.02 rows=1 width=8) (actual time=0.0 20..0.022rows=1 loops=1)842 -> Bitmap Heap Scan on bookings b (cost=1520.2 9..25687.79 rows=18915 width=78) (actual time=43.171..311.549 rows=166665loops=1)813 Sort Method: external merge Disk: 14920kB 814 Buffers: shared hit=697082, temp read=1865 written=1870 815 -> Nested Loop (cost=1520.68..48728.99 rows=18833 width=78) (actual time=41.189..1516.673 rows=167291 loops=1) 816 Buffers: shared hit=697082 817 -> Nested Loop (cost=1520.25..30680.06 rows=18833 width=74) (actual time=41.169..319.669 rows=167291 loops=1) 818 Buffers: shared hit=27918 819 -> CTE Scan on params p (cost=0.00..0.02 rows=1 width=8) (actual time=0.008..0.010 rows=1 loops=1) 820 -> Bitmap Heap Scan on bookings b (cost=1520.25..30491.71 rows=18833 width=78) (actual time=41.158..291.072 rows=167291 loops=1) 843 821 Recheck Cond: ((date_from >= p.start_date) AND (date_from < p.end_date)) 844 822 Filter: ((status)::text = 'Completed'::text) 845 Rows Removed by Filter: 83 3352846 Heap Blocks: exact=2 2223847 Buffers: shared hit=2 3114848 -> Bitmap Index Scan on idx_bookings_date_owner (cost=0.00..1515.5 7 rows=111114 width=0) (actual time=38.503..38.504 rows=1000017loops=1)823 Rows Removed by Filter: 832713 824 Heap Blocks: exact=27027 825 Buffers: shared hit=27918 826 -> Bitmap Index Scan on idx_bookings_date_owner (cost=0.00..1515.55 rows=111112 width=0) (actual time=35.562..35.562 rows=1000004 loops=1) 849 827 Index Cond: ((date_from >= p.start_date) AND (date_from < p.end_date)) 850 828 Buffers: shared hit=891 851 -> 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=16 6665)829 -> 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=167291) 852 830 Index Cond: ((booking_id)::text = (b.booking_id)::text) 853 Buffers: shared hit=66 6660854 -> Materialize (cost= 87930.14..87942.48 rows=2 width=311) (actual time=8566.266..8566.290 rows=2loops=1)855 Buffers: shared hit= 4015978 read=7205856 -> Subquery Scan on service_counts (cost= 87930.14..87942.47 rows=2 width=311) (actual time=8566.261..8566.283 rows=2loops=1)831 Buffers: shared hit=669164 832 -> Materialize (cost=32503.05..32509.20 rows=1 width=311) (actual time=1068.745..1068.772 rows=3 loops=1) 833 Buffers: shared hit=27919 834 -> Subquery Scan on service_counts (cost=32503.05..32509.20 rows=1 width=311) (actual time=1068.741..1068.766 rows=3 loops=1) 857 835 Filter: (service_counts.rank_num = 1) 858 Buffers: shared hit= 4015978 read=7205859 -> WindowAgg (cost= 87930.14..87937.72 rows=380 width=327) (actual time=8566.254..8566.274 rows=2loops=1)836 Buffers: shared hit=27919 837 -> WindowAgg (cost=32503.05..32506.83 rows=190 width=327) (actual time=1068.740..1068.762 rows=3 loops=1) 860 838 Run Condition: (row_number() OVER (?) <= 1) 861 Buffers: shared hit= 4015978 read=7205862 -> Sort (cost= 87930.12..87931.07 rows=380 width=319) (actual time=8566.230..8566.236 rows=5loops=1)863 Sort Key: b_1.owner_id, (count(b s.service_id)) DESC839 Buffers: shared hit=27919 840 -> Sort (cost=32503.03..32503.50 rows=190 width=319) (actual time=1068.729..1068.735 rows=4 loops=1) 841 Sort Key: b_1.owner_id, (count(b_1.service_id)) DESC 864 842 Sort Method: quicksort Memory: 25kB 865 Buffers: shared hit= 4015978 read=7205866 -> HashAggregate (cost= 87910.04..87913.84 rows=380 width=319) (actual time=8566.200..8566.209 rows=5loops=1)843 Buffers: shared hit=27919 844 -> HashAggregate (cost=32493.94..32495.84 rows=190 width=319) (actual time=1068.705..1068.712 rows=4 loops=1) 867 845 Group Key: b_1.owner_id, s.type 868 Batches: 1 Memory Usage: 37kB 869 Buffers: shared hit=4015978 read=7205 870 -> Hash Join (cost=1558.04..87076.68 rows=111114 width=348) (actual time=41.196..8143.541 rows=1000017 loops=1) 871 Hash Cond: ((bs.service_id)::text = (s.service_id)::text) 872 Buffers: shared hit=4015978 read=7205 873 -> Nested Loop (cost=1543.77..86764.23 rows=111114 width=74) (actual time=41.122..7819.032 rows=1000017 loops=1) 874 Buffers: shared hit=4015977 read=7205 875 -> Nested Loop (cost=1543.34..26544.21 rows=111114 width=74) (actual time=41.009..461.885 rows=1000017 loops=1) 876 Buffers: shared hit=23114 877 -> 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) 878 -> 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) 879 Recheck Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date)) 880 Heap Blocks: exact=22223 881 Buffers: shared hit=23114 882 -> Bitmap Index Scan on idx_bookings_date_owner (cost=0.00..1515.57 rows=111114 width=0) (actual time=35.914..35.914 rows=1000017 loops=1) 883 Index Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date)) 884 Buffers: shared hit=891 885 -> Index Scan using idx_booking_services_booking_id on booking_services bs (cost=0.42..0.53 rows=1 width=74) (actual time=0.007..0.007 rows=1 loops=1000017) 886 Index Cond: ((booking_id)::text = (b_1.booking_id)::text) 887 Buffers: shared hit=3992863 read=7205 888 -> Hash (cost=11.90..11.90 rows=190 width=364) (actual time=0.042..0.043 rows=4 loops=1) 846 Batches: 1 Memory Usage: 40kB 847 Buffers: shared hit=27919 848 -> Hash Join (cost=1557.60..31660.60 rows=111112 width=348) (actual time=42.429..729.260 rows=1000004 loops=1) 849 Hash Cond: ((b_1.service_id)::text = (s.service_id)::text) 850 Buffers: shared hit=27919 851 -> Nested Loop (cost=1543.32..31348.14 rows=111112 width=74) (actual time=42.390..408.282 rows=1000004 loops=1) 852 Buffers: shared hit=27918 853 -> 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) 854 -> Bitmap Heap Scan on bookings b_1 (cost=1543.32..30237.00 rows=111112 width=78) (actual time=42.384..209.378 rows=1000004 loops=1) 855 Recheck Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date)) 856 Heap Blocks: exact=27027 857 Buffers: shared hit=27918 858 -> Bitmap Index Scan on idx_bookings_date_owner (cost=0.00..1515.55 rows=111112 width=0) (actual time=36.836..36.836 rows=1000004 loops=1) 859 Index Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date)) 860 Buffers: shared hit=891 861 -> Hash (cost=11.90..11.90 rows=190 width=364) (actual time=0.025..0.026 rows=4 loops=1) 889 862 Buckets: 1024 Batches: 1 Memory Usage: 9kB 890 863 Buffers: shared hit=1 891 -> Seq Scan on services s (cost=0.00..11.90 rows=190 width=364) (actual time=0.0 30..0.031rows=4 loops=1)864 -> Seq Scan on services s (cost=0.00..11.90 rows=190 width=364) (actual time=0.019..0.020 rows=4 loops=1) 892 865 Buffers: shared hit=1 893 -> GroupAggregate (cost=1.48..1.76 rows=16 width=98) (actual time=0.096..0.105 rows=6 loops=1) 894 Group Key: pets.owner_id 895 Buffers: shared hit=1 896 -> Sort (cost=1.48..1.52 rows=16 width=180) (actual time=0.060..0.062 rows=16 loops=1) 897 Sort Key: pets.owner_id 898 Sort Method: quicksort Memory: 26kB 899 Buffers: shared hit=1 900 -> Seq Scan on pets (cost=0.00..1.16 rows=16 width=180) (actual time=0.029..0.034 rows=16 loops=1) 901 Buffers: shared hit=1 902 -> Hash (cost=15.80..15.80 rows=580 width=51) (actual time=0.046..0.047 rows=12 loops=1) 903 Buckets: 1024 Batches: 1 Memory Usage: 9kB 866 -> Sort (cost=2.78..2.80 rows=6 width=45) (actual time=0.068..0.071 rows=6 loops=1) 867 Sort Key: pp.owner_id 868 Sort Method: quicksort Memory: 25kB 869 Buffers: shared hit=2 870 -> Subquery Scan on pp (cost=2.59..2.71 rows=6 width=45) (actual time=0.049..0.053 rows=6 loops=1) 871 Buffers: shared hit=2 872 -> HashAggregate (cost=2.59..2.65 rows=6 width=45) (actual time=0.049..0.051 rows=6 loops=1) 873 Group Key: pets.owner_id 874 Batches: 1 Memory Usage: 24kB 875 Buffers: shared hit=2 876 -> Seq Scan on pets (cost=0.00..2.39 rows=39 width=74) (actual time=0.021..0.025 rows=39 loops=1) 877 Buffers: shared hit=2 878 -> Hash (cost=10.40..10.40 rows=40 width=638) (actual time=0.026..0.026 rows=14 loops=1) 879 Buckets: 1024 Batches: 1 Memory Usage: 10kB 904 880 Buffers: shared hit=1 905 -> Seq Scan on users u (cost=0.00..1 5.80 rows=580 width=51) (actual time=0.030..0.033 rows=12loops=1)881 -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=638) (actual time=0.013..0.017 rows=14 loops=1) 906 882 Buffers: shared hit=1 907 883 Planning: 908 Buffers: shared hit=706 read=6 dirtied=2 909 Planning Time: 5.877 ms 910 JIT: 911 Functions: 84 912 Options: Inlining false, Optimization false, Expressions true, Deforming true 913 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 914 Execution Time: 11158.152 ms 915 }}} 916 917 '''Execution time:''' 11158.152 ms 918 919 '''Conclusion:''' The execution time increased from 5.3 seconds to 11.1 seconds. Creating foreign key indexes actually decreased the query performance. 884 Buffers: shared hit=44 885 Planning Time: 1.735 ms 886 Execution Time: 3403.299 ms 887 }}} 888 889 '''Execution time:''' 3403.299 ms 890 891 '''Conclusion:''' The execution time did not change much (3280.637 ms vs 3403.299 ms) Creating foreign key indexes did not have a significant effect on the query performance. 920 892 921 893 Because 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: 922 894 {{{ 923 895 #!sql 924 DROP INDEX project.idx_booking _services_booking_id;896 DROP INDEX project.idx_bookings_service_id; 925 897 DROP INDEX project.idx_payments_booking_id; 926 898 DROP INDEX project.idx_bookings_date_sitter; 927 899 DROP INDEX project.idx_bookings_date_owner; 900 DROP INDEX project.idx_pets_owner_id; 928 901 }}} 929 902 … … 1004 977 1005 978 Because our frontend and backend are served together from the exact same Spring Boot instance, they share the exact same origin. Therefore, cross origin requests do not occur, making CORS configuration unnecessary for our application. 979 980 == Version history == 981 982 [https://develop.finki.ukim.mk/projects/petsitter/wiki/Performance_v01 v01] 983 * Initial version 984 985 [https://develop.finki.ukim.mk/projects/petsitter/wiki/Performance v02] 986 * Current version, based on version [https://develop.finki.ukim.mk/projects/petsitter/wiki/ERModel v03] of the ER model 987 * The mock data assigns the {{{service_id}}} directly inside the {{{INSERT}}} into {{{bookings}}}; the last/fifth step of the mock data that used the removed {{{booking_services}}} table is deleted 988 * All {{{EXPLAIN (ANALYZE, BUFFERS)}}} queries were executed again on the corrected schema, and every output, measured time and average was updated 989 * The {{{idx_booking_services_booking_id}}} index is replaced by {{{idx_bookings_service_id}}}, because the junction table it indexed does not exist 990 * In Scenario 4 the benchmark query joins {{{services}}} directly on {{{b.service_id}}}, correlating with the updated report in [https://develop.finki.ukim.mk/projects/petsitter/wiki/AdvancedReports AdvancedReports]
