| | 1 | == Performance == |
| | 2 | |
| | 3 | === 1. Adding mock data for bookings === |
| | 4 | |
| | 5 | To properly benchmark our database, we first generate a realistic dataset of 1000000 bookings and related entities which. |
| | 6 | |
| | 7 | {{{ |
| | 8 | #!sql |
| | 9 | -- 1. Insert 1,000,000 mock bookings |
| | 10 | ALTER TABLE project.bookings DISABLE TRIGGER trg_booking_validation; |
| | 11 | |
| | 12 | INSERT INTO project.bookings (status, date_from, date_to, address, owner_id, sitter_id) |
| | 13 | SELECT |
| | 14 | (ARRAY['Pending', 'Confirmed', 'Completed', 'Canceled', 'Expired', 'Reviewed'])[floor(random()*6)+1] AS status, |
| | 15 | t.dt AS date_from, |
| | 16 | t.dt + (random() * INTERVAL '7 days') AS date_to, |
| | 17 | 'Mock Address ' || gs AS address, |
| | 18 | o.user_id AS owner_id, |
| | 19 | s.user_id AS sitter_id |
| | 20 | FROM generate_series(1, 1000000) gs |
| | 21 | JOIN LATERAL ( |
| | 22 | SELECT user_id FROM project.pet_owners ORDER BY random() LIMIT 1 |
| | 23 | ) o ON true |
| | 24 | JOIN LATERAL ( |
| | 25 | SELECT user_id FROM project.pet_sitters ORDER BY random() LIMIT 1 |
| | 26 | ) s ON true |
| | 27 | JOIN LATERAL ( |
| | 28 | SELECT CURRENT_DATE - (random() * INTERVAL '365 days') AS dt |
| | 29 | ) t ON true; |
| | 30 | |
| | 31 | ALTER TABLE project.bookings ENABLE TRIGGER trg_booking_validation; |
| | 32 | }}} |
| | 33 | |
| | 34 | {{{ |
| | 35 | #!sql |
| | 36 | -- 2. Insert relevant mock payments |
| | 37 | INSERT INTO project.payments (booking_id, amount, payment_type) |
| | 38 | SELECT |
| | 39 | b.booking_id, |
| | 40 | floor(random() * 100 + 20)::int AS amount, |
| | 41 | (ARRAY['Card', 'Cash', 'Bank Transfer'])[floor(random()*3)+1] AS payment_type |
| | 42 | FROM project.bookings b |
| | 43 | WHERE NOT EXISTS ( |
| | 44 | SELECT 1 FROM project.payments p WHERE p.booking_id = b.booking_id |
| | 45 | ); |
| | 46 | }}} |
| | 47 | |
| | 48 | {{{ |
| | 49 | #!sql |
| | 50 | -- 3. Insert mock reviews for completed bookings |
| | 51 | INSERT INTO project.reviews (booking_id, rating, comment) |
| | 52 | SELECT |
| | 53 | b.booking_id, |
| | 54 | floor(random()*5)+1 AS rating, |
| | 55 | 'Mock Review ' || b.booking_id AS comment |
| | 56 | FROM project.bookings b |
| | 57 | WHERE b.status = 'Completed' |
| | 58 | AND random() > 0.3 |
| | 59 | AND NOT EXISTS ( |
| | 60 | SELECT 1 FROM project.reviews r WHERE r.booking_id = b.booking_id |
| | 61 | ); |
| | 62 | }}} |
| | 63 | |
| | 64 | {{{ |
| | 65 | #!sql |
| | 66 | -- 4. Insert mock pets |
| | 67 | INSERT INTO project.pets (owner_id, name, age, pettype_id) |
| | 68 | SELECT |
| | 69 | po.user_id, |
| | 70 | 'Mock Pet ' || po.user_id || '-' || gs AS name, |
| | 71 | floor(random()*15)+1 AS age, |
| | 72 | pt.pettype_id |
| | 73 | FROM project.pet_owners po |
| | 74 | CROSS JOIN LATERAL generate_series(1, floor(random()*3 + 1)::int) gs |
| | 75 | JOIN LATERAL ( |
| | 76 | SELECT pettype_id FROM project.pet_types ORDER BY random() LIMIT 1 |
| | 77 | ) pt ON true; |
| | 78 | }}} |
| | 79 | |
| | 80 | {{{ |
| | 81 | #!sql |
| | 82 | -- 5. Link random services to bookings |
| | 83 | INSERT INTO project.booking_services (booking_id, service_id) |
| | 84 | SELECT |
| | 85 | b.booking_id, |
| | 86 | s.service_id |
| | 87 | FROM project.bookings b |
| | 88 | JOIN LATERAL ( |
| | 89 | SELECT service_id FROM project.services ORDER BY random() LIMIT 1 |
| | 90 | ) s ON true |
| | 91 | WHERE NOT EXISTS ( |
| | 92 | SELECT 1 FROM project.booking_services bs WHERE bs.booking_id = b.booking_id |
| | 93 | ); |
| | 94 | }}} |
| | 95 | |
| | 96 | Benchmark query: |
| | 97 | {{{ |
| | 98 | #!sql |
| | 99 | EXPLAIN (ANALYZE, BUFFERS) |
| | 100 | SELECT * FROM project.bookings |
| | 101 | WHERE sitter_id = (SELECT user_id FROM project.pet_sitters LIMIT 1) |
| | 102 | ORDER BY date_from DESC; |
| | 103 | }}} |
| | 104 | |
| | 105 | Execution without indexes: |
| | 106 | |
| | 107 | {{{ |
| | 108 | Gather Merge (cost=81029.64..195548.38 rows=967764 width=147) (actual time=102.092..113.433 rows=2 loops=1) |
| | 109 | Workers Planned: 3 |
| | 110 | Workers Launched: 3 |
| | 111 | Buffers: shared hit=22335 |
| | 112 | InitPlan 1 |
| | 113 | -> Limit (cost=0.00..0.02 rows=1 width=90) (actual time=6.105..6.106 rows=1 loops=1) |
| | 114 | Buffers: shared hit=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) |
| | 116 | Buffers: shared hit=1 |
| | 117 | -> Sort (cost=80029.57..80836.04 rows=322588 width=147) (actual time=65.204..65.206 rows=0 loops=4) |
| | 118 | Sort Key: bookings.date_from DESC |
| | 119 | Sort Method: quicksort Memory: 25kB |
| | 120 | Buffers: shared hit=22334 |
| | 121 | Worker 0: Sort Method: quicksort Memory: 25kB |
| | 122 | Worker 1: Sort Method: quicksort Memory: 25kB |
| | 123 | 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.056 rows=0 loops=4) |
| | 125 | 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 |
| | 131 | JIT: |
| | 132 | Functions: 12 |
| | 133 | 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.788 ms |
| | 135 | Execution Time: 148.520 ms |
| | 136 | }}} |
| | 137 | |
| | 138 | '''Average execution time (10 attempts):''' 145.812ms |
| | 139 | |
| | 140 | Next, we add this index: |
| | 141 | {{{ |
| | 142 | #!sql |
| | 143 | CREATE INDEX idx_bookings_sitter_date |
| | 144 | ON project.bookings (sitter_id, date_from DESC); |
| | 145 | }}} |
| | 146 | |
| | 147 | (Note, for the exact same performance reasons, we also created this index to optimize the queries when Pet Owners view their own bookings) |
| | 148 | {{{ |
| | 149 | #!sql |
| | 150 | CREATE INDEX idx_bookings_owner_date |
| | 151 | ON project.bookings (owner_id, date_from DESC); |
| | 152 | }}} |
| | 153 | |
| | 154 | Execution with indexes: |
| | 155 | |
| | 156 | {{{ |
| | 157 | Index 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) |
| | 158 | Index Cond: ((sitter_id)::text = ((InitPlan 1).col1)::text) |
| | 159 | Buffers: shared hit=2 read=3 |
| | 160 | InitPlan 1 |
| | 161 | -> Limit (cost=0.00..0.02 rows=1 width=90) (actual time=0.040..0.040 rows=1 loops=1) |
| | 162 | Buffers: shared hit=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) |
| | 164 | 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 |
| | 172 | |
| | 173 | Because the execution time has been massively lowered by bypassing the expensive sequential scan and memory sort, we '''keep''' this index. |
| | 174 | |
| | 175 | === 2. Calculate average sitter rating === |
| | 176 | |
| | 177 | Benchmark query: |
| | 178 | |
| | 179 | {{{ |
| | 180 | #!sql |
| | 181 | EXPLAIN (ANALYZE, BUFFERS) |
| | 182 | SELECT AVG(r.rating) |
| | 183 | FROM project.reviews r |
| | 184 | JOIN project.bookings b ON r.booking_id = b.booking_id |
| | 185 | WHERE b.sitter_id = (SELECT user_id FROM project.pet_sitters LIMIT 1); |
| | 186 | }}} |
| | 187 | |
| | 188 | Execution without indexes: |
| | 189 | |
| | 190 | {{{ |
| | 191 | Finalize 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 |
| | 193 | InitPlan 1 |
| | 194 | -> Limit (cost=0.00..0.02 rows=1 width=90) (actual time=0.013..0.014 rows=1 loops=1) |
| | 195 | Buffers: shared hit=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) |
| | 197 | Buffers: shared hit=1 |
| | 198 | -> Gather (cost=32123.08..32123.39 rows=3 width=32) (actual time=104.969..116.470 rows=4 loops=1) |
| | 199 | Workers Planned: 3 |
| | 200 | Workers Launched: 3 |
| | 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) |
| | 205 | Hash Cond: ((b.booking_id)::text = (r.booking_id)::text) |
| | 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) |
| | 208 | Filter: ((sitter_id)::text = ((InitPlan 1).col1)::text) |
| | 209 | Rows Removed by Filter: 250005 |
| | 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 |
| | 216 | Planning: |
| | 217 | Buffers: shared hit=165 read=2 |
| | 218 | Planning Time: 1.200 ms |
| | 219 | Execution Time: 116.627 ms |
| | 220 | }}} |
| | 221 | |
| | 222 | '''Average execution time (10 attempts):''' 115.143ms |
| | 223 | |
| | 224 | We add this index: |
| | 225 | |
| | 226 | {{{ |
| | 227 | #!sql |
| | 228 | CREATE INDEX idx_reviews_booking_id |
| | 229 | ON project.reviews (booking_id); |
| | 230 | }}} |
| | 231 | |
| | 232 | Execution with indexes: |
| | 233 | |
| | 234 | {{{ |
| | 235 | Finalize 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 |
| | 260 | Planning: |
| | 261 | Buffers: shared hit=78 read=6 |
| | 262 | Planning Time: 1.082 ms |
| | 263 | Execution Time: 113.336 ms |
| | 264 | }}} |
| | 265 | |
| | 266 | '''Average execution time (10 attempts):''' 114.288ms |
| | 267 | |
| | 268 | 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. |
| | 269 | |
| | 270 | {{{ |
| | 271 | #!sql |
| | 272 | DROP INDEX IF EXISTS project.idx_reviews_booking_id; |
| | 273 | }}} |
| | 274 | |
| | 275 | === 3. Sitter Performance Analytics (Phase 6 Query) === |
| | 276 | |
| | 277 | Benchmark query: |
| | 278 | {{{ |
| | 279 | #!sql |
| | 280 | EXPLAIN (ANALYZE, BUFFERS) |
| | 281 | WITH params AS ( |
| | 282 | SELECT |
| | 283 | (CURRENT_DATE - INTERVAL '1 year')::DATE AS start_date, |
| | 284 | CURRENT_DATE::DATE AS end_date |
| | 285 | ), |
| | 286 | sitter_stats AS ( |
| | 287 | SELECT |
| | 288 | b.sitter_id, |
| | 289 | COUNT(b.booking_id) AS total_bookings, |
| | 290 | COUNT(b.booking_id) FILTER (WHERE b.status = 'Completed') AS completed_bookings, |
| | 291 | COUNT(b.booking_id) FILTER (WHERE b.status IN ('Canceled', 'Rejected')) AS missed_bookings |
| | 292 | FROM project.bookings b |
| | 293 | JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date |
| | 294 | GROUP BY b.sitter_id |
| | 295 | ), |
| | 296 | sitter_reports AS ( |
| | 297 | SELECT |
| | 298 | b.sitter_id, |
| | 299 | SUM(pay.amount) AS total_revenue |
| | 300 | FROM project.bookings b |
| | 301 | JOIN project.payments pay ON b.booking_id = pay.booking_id |
| | 302 | JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date |
| | 303 | WHERE b.status = 'Completed' |
| | 304 | GROUP BY b.sitter_id |
| | 305 | ), |
| | 306 | sitter_ratings AS ( |
| | 307 | SELECT |
| | 308 | b.sitter_id, |
| | 309 | AVG(r.rating)::numeric(10,2) AS avg_rating, |
| | 310 | COUNT(r.review_id) AS total_reviews |
| | 311 | FROM project.bookings b |
| | 312 | JOIN project.reviews r ON b.booking_id = r.booking_id |
| | 313 | JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date |
| | 314 | GROUP BY b.sitter_id |
| | 315 | ) |
| | 316 | SELECT |
| | 317 | u.user_id, u.username, u.first_name, u.last_name, |
| | 318 | COALESCE(ss.total_bookings, 0) AS total_bookings, |
| | 319 | COALESCE(ss.completed_bookings, 0) AS completed_bookings, |
| | 320 | COALESCE(ss.missed_bookings, 0) AS missed_bookings, |
| | 321 | COALESCE(sr.avg_rating, 0) AS avg_rating, |
| | 322 | COALESCE(sr.total_reviews, 0) AS total_reviews, |
| | 323 | COALESCE(sf.total_revenue, 0) AS total_revenue, |
| | 324 | DENSE_RANK() OVER ( |
| | 325 | ORDER BY ( |
| | 326 | COALESCE(sf.total_revenue, 0) * 0.5 |
| | 327 | + COALESCE(ss.completed_bookings, 0) * 10 |
| | 328 | + COALESCE(sr.avg_rating, 0) * 15 |
| | 329 | - COALESCE(ss.missed_bookings, 0) * 5 |
| | 330 | ) DESC |
| | 331 | ) AS sitter_rank |
| | 332 | FROM project.users u |
| | 333 | JOIN project.pet_sitters ps ON u.user_id = ps.user_id |
| | 334 | LEFT JOIN sitter_stats ss ON ss.sitter_id = ps.user_id |
| | 335 | LEFT JOIN sitter_reports sf ON sf.sitter_id = ps.user_id |
| | 336 | LEFT JOIN sitter_ratings sr ON sr.sitter_id = ps.user_id |
| | 337 | WHERE COALESCE(ss.total_bookings, 0) > 0 |
| | 338 | ORDER BY sitter_rank |
| | 339 | LIMIT 10; |
| | 340 | }}} |
| | 341 | |
| | 342 | Execution without indexes: |
| | 343 | {{{ |
| | 344 | Limit (cost=154247.04..154247.06 rows=10 width=176) (actual time=2600.384..2611.703 rows=4 loops=1) |
| | 345 | Buffers: shared hit=735904 read=1 |
| | 346 | CTE params |
| | 347 | -> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=1) |
| | 348 | -> Sort (cost=154247.02..154247.59 rows=230 width=176) (actual time=2544.398..2555.714 rows=4 loops=1) |
| | 349 | Sort Key: (dense_rank() OVER (?)) |
| | 350 | Sort Method: quicksort Memory: 26kB |
| | 351 | Buffers: shared hit=735904 read=1 |
| | 352 | -> WindowAgg (cost=154232.29..154242.05 rows=230 width=176) (actual time=2544.338..2555.668 rows=4 loops=1) |
| | 353 | Buffers: shared hit=735901 read=1 |
| | 354 | -> Sort (cost=154232.27..154232.85 rows=230 width=152) (actual time=2544.310..2555.625 rows=4 loops=1) |
| | 355 | 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 | 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 |
| | 379 | 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 |
| | 401 | Sort Method: quicksort Memory: 25kB |
| | 402 | Buffers: shared hit=688883 |
| | 403 | -> Subquery Scan on sf (cost=60507.81..60507.83 rows=1 width=45) (actual time=1603.989..1603.995 rows=2 loops=1) |
| | 404 | Buffers: shared hit=688883 |
| | 405 | -> HashAggregate (cost=60507.81..60507.82 rows=1 width=45) (actual time=1603.982..1603.986 rows=2 loops=1) |
| | 406 | Group Key: b_1.sitter_id |
| | 407 | 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 |
| | 425 | Sort Method: quicksort Memory: 25kB |
| | 426 | Buffers: shared hit=24791 |
| | 427 | -> Subquery Scan on sr (cost=44742.45..44742.48 rows=1 width=61) (actual time=215.773..227.079 rows=2 loops=1) |
| | 428 | Buffers: shared hit=24791 |
| | 429 | -> HashAggregate (cost=44742.45..44742.47 rows=1 width=61) (actual time=215.766..227.070 rows=2 loops=1) |
| | 430 | Group Key: b_2.sitter_id |
| | 431 | 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 |
| | 452 | Planning: |
| | 453 | Buffers: shared hit=227 read=5 |
| | 454 | Planning Time: 3.010 ms |
| | 455 | JIT: |
| | 456 | Functions: 115 |
| | 457 | 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.115 ms |
| | 459 | Execution Time: 2619.146 ms |
| | 460 | }}} |
| | 461 | |
| | 462 | '''Execution time:''' 2619.146 ms |
| | 463 | |
| | 464 | We attempt to optimize this analytical query by introducing multiple foreign key indexes: |
| | 465 | {{{ |
| | 466 | #!sql |
| | 467 | CREATE INDEX idx_bookings_date_sitter ON project.bookings (date_from, sitter_id); |
| | 468 | CREATE INDEX idx_bookings_date_owner ON project.bookings (date_from, owner_id); |
| | 469 | 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); |
| | 471 | CREATE INDEX idx_pets_owner_id ON project.pets (owner_id); |
| | 472 | }}} |
| | 473 | |
| | 474 | Execution with indexes: |
| | 475 | {{{ |
| | 476 | Limit (cost=110101.62..110101.65 rows=10 width=176) (actual time=3500.078..3500.100 rows=4 loops=1) |
| | 477 | Buffers: shared hit=738392, temp read=6439 written=6439 |
| | 478 | CTE params |
| | 479 | -> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.027..0.028 rows=1 loops=1) |
| | 480 | -> Sort (cost=110101.60..110102.18 rows=230 width=176) (actual time=3442.513..3442.532 rows=4 loops=1) |
| | 481 | Sort Key: (dense_rank() OVER (?)) |
| | 482 | Sort Method: quicksort Memory: 26kB |
| | 483 | Buffers: shared hit=738392, temp read=6439 written=6439 |
| | 484 | -> WindowAgg (cost=110086.87..110096.63 rows=230 width=176) (actual time=3442.451..3442.485 rows=4 loops=1) |
| | 485 | Buffers: shared hit=738389, temp read=6439 written=6439 |
| | 486 | -> Sort (cost=110086.86..110087.43 rows=230 width=152) (actual time=3442.434..3442.453 rows=4 loops=1) |
| | 487 | 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 | 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) |
| | 494 | 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 |
| | 589 | Planning: |
| | 590 | Buffers: shared hit=668 |
| | 591 | Planning Time: 5.315 ms |
| | 592 | JIT: |
| | 593 | Functions: 81 |
| | 594 | 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.314 ms |
| | 596 | Execution Time: 3540.806 ms |
| | 597 | }}} |
| | 598 | |
| | 599 | '''Execution time:''' 3540.806 ms |
| | 600 | |
| | 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. |
| | 602 | |
| | 603 | === 4. Highest Paying Customers Analytics (Phase 6 Query) === |
| | 604 | |
| | 605 | Benchmark query: |
| | 606 | {{{ |
| | 607 | #!sql |
| | 608 | EXPLAIN (ANALYZE, BUFFERS) |
| | 609 | WITH params AS ( |
| | 610 | SELECT |
| | 611 | (CURRENT_DATE - INTERVAL '1 year')::DATE AS start_date, |
| | 612 | CURRENT_DATE::DATE AS end_date |
| | 613 | ), |
| | 614 | owner_reports AS ( |
| | 615 | SELECT |
| | 616 | b.owner_id, |
| | 617 | SUM(pay.amount) AS total_profit_generated, |
| | 618 | COUNT(DISTINCT b.booking_id) AS successful_bookings |
| | 619 | FROM project.bookings b |
| | 620 | JOIN project.payments pay ON b.booking_id = pay.booking_id |
| | 621 | JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date |
| | 622 | WHERE b.status = 'Completed' |
| | 623 | GROUP BY b.owner_id |
| | 624 | ), |
| | 625 | service_counts AS ( |
| | 626 | SELECT |
| | 627 | b.owner_id, |
| | 628 | s.type AS service_type, |
| | 629 | COUNT(bs.service_id) AS times_booked, |
| | 630 | ROW_NUMBER() OVER(PARTITION BY b.owner_id ORDER BY COUNT(bs.service_id) DESC) as rank_num |
| | 631 | 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 |
| | 634 | JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date |
| | 635 | GROUP BY b.owner_id, s.type |
| | 636 | ), |
| | 637 | favorite_service AS ( |
| | 638 | SELECT owner_id, service_type AS top_interest |
| | 639 | FROM service_counts |
| | 640 | WHERE rank_num = 1 |
| | 641 | ), |
| | 642 | pet_portfolio AS ( |
| | 643 | SELECT owner_id, COUNT(pet_id) AS registered_pets |
| | 644 | FROM project.pets |
| | 645 | GROUP BY owner_id |
| | 646 | ) |
| | 647 | SELECT |
| | 648 | u.user_id, u.first_name, u.last_name, |
| | 649 | COALESCE(ofin.successful_bookings, 0) AS successful_bookings, |
| | 650 | COALESCE(pp.registered_pets, 0) AS total_pets, |
| | 651 | COALESCE(fs.top_interest, 'Unknown') AS top_interest, |
| | 652 | COALESCE(ofin.total_profit_generated, 0) AS total_profit_generated, |
| | 653 | DENSE_RANK() OVER ( |
| | 654 | ORDER BY |
| | 655 | COALESCE(ofin.total_profit_generated, 0) DESC, |
| | 656 | COALESCE(ofin.successful_bookings, 0) DESC |
| | 657 | ) AS customer_rank |
| | 658 | FROM project.users u |
| | 659 | JOIN project.pet_owners po ON u.user_id = po.user_id |
| | 660 | LEFT JOIN owner_reports ofin ON po.user_id = ofin.owner_id |
| | 661 | LEFT JOIN favorite_service fs ON po.user_id = fs.owner_id |
| | 662 | LEFT JOIN pet_portfolio pp ON po.user_id = pp.owner_id |
| | 663 | WHERE COALESCE(ofin.successful_bookings, 0) > 0 |
| | 664 | ORDER BY customer_rank |
| | 665 | LIMIT 10; |
| | 666 | }}} |
| | 667 | |
| | 668 | Execution without indexes: |
| | 669 | {{{ |
| | 670 | Limit (cost=170067.13..170067.15 rows=10 width=115) (actual time=5322.701..5322.720 rows=2 loops=1) |
| | 671 | Buffers: shared hit=726269, temp read=26853 written=26858 |
| | 672 | CTE params |
| | 673 | -> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1) |
| | 674 | -> Sort (cost=170067.11..170067.68 rows=230 width=115) (actual time=5264.981..5264.999 rows=2 loops=1) |
| | 675 | Sort Key: (dense_rank() OVER (?)) |
| | 676 | Sort Method: quicksort Memory: 25kB |
| | 677 | Buffers: shared hit=726269, temp read=26853 written=26858 |
| | 678 | -> WindowAgg (cost=170057.56..170062.14 rows=230 width=115) (actual time=5264.923..5264.951 rows=2 loops=1) |
| | 679 | Buffers: shared hit=726266, temp read=26853 written=26858 |
| | 680 | -> Sort (cost=170057.54..170058.11 rows=230 width=349) (actual time=5264.888..5264.905 rows=2 loops=1) |
| | 681 | Sort Key: (COALESCE((sum(pay.amount)), '0'::bigint)) DESC, (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint)) DESC |
| | 682 | Sort Method: quicksort Memory: 25kB |
| | 683 | Buffers: shared hit=726266, temp read=26853 written=26858 |
| | 684 | -> Hash Join (cost=169840.97..170048.52 rows=230 width=349) (actual time=5049.927..5264.888 rows=2 loops=1) |
| | 685 | Hash Cond: ((po.user_id)::text = (u.user_id)::text) |
| | 686 | Buffers: shared hit=726266, temp read=26853 written=26858 |
| | 687 | -> Merge Left Join (cost=169817.92..170024.86 rows=230 width=388) (actual time=5049.843..5264.798 rows=2 loops=1) |
| | 688 | Merge Cond: ((po.user_id)::text = (pets.owner_id)::text) |
| | 689 | Buffers: shared hit=726265, temp read=26853 written=26858 |
| | 690 | -> Merge Left Join (cost=169816.17..170021.77 rows=230 width=380) (actual time=5049.710..5264.655 rows=2 loops=1) |
| | 691 | Merge Cond: ((po.user_id)::text = (service_counts.owner_id)::text) |
| | 692 | Buffers: shared hit=726264, temp read=26853 written=26858 |
| | 693 | -> Merge Left Join (cost=61806.32..61998.99 rows=230 width=106) (actual time=2266.934..2481.844 rows=2 loops=1) |
| | 694 | Merge Cond: ((po.user_id)::text = (b.owner_id)::text) |
| | 695 | Filter: (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint) > 0) |
| | 696 | Rows Removed by Filter: 5 |
| | 697 | Buffers: shared hit=688884, temp read=1858 written=1863 |
| | 698 | -> Sort (cost=49.44..51.16 rows=690 width=90) (actual time=0.040..0.044 rows=7 loops=1) |
| | 699 | Sort Key: po.user_id |
| | 700 | Sort Method: quicksort Memory: 25kB |
| | 701 | Buffers: shared hit=1 |
| | 702 | -> 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) |
| | 703 | Buffers: shared hit=1 |
| | 704 | -> Materialize (cost=61756.88..61946.08 rows=2 width=53) (actual time=2266.857..2481.755 rows=2 loops=1) |
| | 705 | Buffers: shared hit=688883, temp read=1858 written=1863 |
| | 706 | -> GroupAggregate (cost=61756.88..61946.05 rows=2 width=53) (actual time=2266.852..2481.746 rows=2 loops=1) |
| | 707 | Group Key: b.owner_id |
| | 708 | Buffers: shared hit=688883, temp read=1858 written=1863 |
| | 709 | -> Sort (cost=61756.88..61804.17 rows=18915 width=78) (actual time=2266.784..2442.950 rows=166665 loops=1) |
| | 710 | Sort Key: b.owner_id, b.booking_id |
| | 711 | Sort Method: external merge Disk: 14864kB |
| | 712 | Buffers: shared hit=688883, temp read=1858 written=1863 |
| | 713 | -> Nested Loop (cost=0.42..60413.23 rows=18915 width=78) (actual time=0.185..1616.303 rows=166665 loops=1) |
| | 714 | Buffers: shared hit=688883 |
| | 715 | -> Nested Loop (cost=0.00..37276.85 rows=18915 width=74) (actual time=0.057..274.855 rows=166665 loops=1) |
| | 716 | 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) |
| | 721 | Filter: ((status)::text = 'Completed'::text) |
| | 722 | Rows Removed by Filter: 833354 |
| | 723 | Buffers: shared hit=22223 |
| | 724 | -> 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) |
| | 725 | Index Cond: ((booking_id)::text = (b.booking_id)::text) |
| | 726 | Buffers: shared hit=666660 |
| | 727 | -> Materialize (cost=108009.85..108022.19 rows=2 width=311) (actual time=2782.752..2782.780 rows=2 loops=1) |
| | 728 | Buffers: shared hit=37380, temp read=24995 written=24995 |
| | 729 | -> Subquery Scan on service_counts (cost=108009.85..108022.18 rows=2 width=311) (actual time=2782.740..2782.766 rows=2 loops=1) |
| | 730 | Filter: (service_counts.rank_num = 1) |
| | 731 | Buffers: shared hit=37380, temp read=24995 written=24995 |
| | 732 | -> WindowAgg (cost=108009.85..108017.43 rows=380 width=327) (actual time=2782.732..2782.756 rows=2 loops=1) |
| | 733 | Run Condition: (row_number() OVER (?) <= 1) |
| | 734 | Buffers: shared hit=37380, temp read=24995 written=24995 |
| | 735 | -> Sort (cost=108009.83..108010.78 rows=380 width=319) (actual time=2782.709..2782.717 rows=5 loops=1) |
| | 736 | Sort Key: b_1.owner_id, (count(bs.service_id)) DESC |
| | 737 | Sort Method: quicksort Memory: 25kB |
| | 738 | Buffers: shared hit=37380, temp read=24995 written=24995 |
| | 739 | -> HashAggregate (cost=107989.75..107993.55 rows=380 width=319) (actual time=2782.681..2782.692 rows=5 loops=1) |
| | 740 | 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) |
| | 762 | Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| | 763 | Buffers: shared hit=1 |
| | 764 | -> Seq Scan on services s (cost=0.00..11.90 rows=190 width=364) (actual time=0.031..0.033 rows=4 loops=1) |
| | 765 | 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 |
| | 777 | Buffers: shared hit=1 |
| | 778 | -> Seq Scan on users u (cost=0.00..15.80 rows=580 width=51) (actual time=0.033..0.036 rows=12 loops=1) |
| | 779 | Buffers: shared hit=1 |
| | 780 | Planning: |
| | 781 | Buffers: shared hit=570 |
| | 782 | Planning Time: 4.934 ms |
| | 783 | JIT: |
| | 784 | Functions: 85 |
| | 785 | 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.267 ms |
| | 787 | Execution Time: 5367.798 ms |
| | 788 | }}} |
| | 789 | |
| | 790 | '''Execution time:''' 5367.798 ms |
| | 791 | |
| | 792 | Execution with the indexes we created in Scenario 3: |
| | 793 | {{{ |
| | 794 | Limit (cost=133535.97..133535.99 rows=10 width=115) (actual time=11115.289..11115.307 rows=2 loops=1) |
| | 795 | Buffers: shared hit=4705758 read=7205, temp read=1858 written=1863 |
| | 796 | CTE params |
| | 797 | -> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1) |
| | 798 | -> Sort (cost=133535.95..133536.52 rows=230 width=115) (actual time=11060.413..11060.429 rows=2 loops=1) |
| | 799 | Sort Key: (dense_rank() OVER (?)) |
| | 800 | Sort Method: quicksort Memory: 25kB |
| | 801 | Buffers: shared hit=4705758 read=7205, temp read=1858 written=1863 |
| | 802 | -> WindowAgg (cost=133526.40..133530.98 rows=230 width=115) (actual time=11060.340..11060.367 rows=2 loops=1) |
| | 803 | Buffers: shared hit=4705755 read=7205, temp read=1858 written=1863 |
| | 804 | -> Sort (cost=133526.38..133526.95 rows=230 width=349) (actual time=11060.310..11060.326 rows=2 loops=1) |
| | 805 | Sort Key: (COALESCE((sum(pay.amount)), '0'::bigint)) DESC, (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint)) DESC |
| | 806 | Sort Method: quicksort Memory: 25kB |
| | 807 | Buffers: shared hit=4705755 read=7205, temp read=1858 written=1863 |
| | 808 | -> Hash Join (cost=133310.05..133517.35 rows=230 width=349) (actual time=10845.768..11060.317 rows=2 loops=1) |
| | 809 | Hash Cond: ((po.user_id)::text = (u.user_id)::text) |
| | 810 | Buffers: shared hit=4705755 read=7205, temp read=1858 written=1863 |
| | 811 | -> Merge Left Join (cost=133287.00..133493.70 rows=230 width=388) (actual time=10845.686..11060.231 rows=2 loops=1) |
| | 812 | Merge Cond: ((po.user_id)::text = (pets.owner_id)::text) |
| | 813 | Buffers: shared hit=4705754 read=7205, temp read=1858 written=1863 |
| | 814 | -> Merge Left Join (cost=133285.52..133491.12 rows=230 width=380) (actual time=10845.563..11060.100 rows=2 loops=1) |
| | 815 | Merge Cond: ((po.user_id)::text = (service_counts.owner_id)::text) |
| | 816 | Buffers: shared hit=4705753 read=7205, temp read=1858 written=1863 |
| | 817 | -> Merge Left Join (cost=45355.37..45548.05 rows=230 width=106) (actual time=2279.274..2493.782 rows=2 loops=1) |
| | 818 | Merge Cond: ((po.user_id)::text = (b.owner_id)::text) |
| | 819 | Filter: (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint) > 0) |
| | 820 | Rows Removed by Filter: 5 |
| | 821 | Buffers: shared hit=689775, temp read=1858 written=1863 |
| | 822 | -> Sort (cost=49.44..51.16 rows=690 width=90) (actual time=0.048..0.053 rows=7 loops=1) |
| | 823 | Sort Key: po.user_id |
| | 824 | Sort Method: quicksort Memory: 25kB |
| | 825 | Buffers: shared hit=1 |
| | 826 | -> 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) |
| | 827 | Buffers: shared hit=1 |
| | 828 | -> Materialize (cost=45305.94..45495.13 rows=2 width=53) (actual time=2279.186..2493.682 rows=2 loops=1) |
| | 829 | Buffers: shared hit=689774, temp read=1858 written=1863 |
| | 830 | -> GroupAggregate (cost=45305.94..45495.11 rows=2 width=53) (actual time=2279.181..2493.674 rows=2 loops=1) |
| | 831 | Group Key: b.owner_id |
| | 832 | Buffers: shared hit=689774, temp read=1858 written=1863 |
| | 833 | -> Sort (cost=45305.94..45353.23 rows=18915 width=78) (actual time=2279.104..2454.786 rows=166665 loops=1) |
| | 834 | Sort Key: b.owner_id, b.booking_id |
| | 835 | Sort Method: external merge Disk: 14864kB |
| | 836 | Buffers: shared hit=689774, temp read=1858 written=1863 |
| | 837 | -> Nested Loop (cost=1520.72..43962.29 rows=18915 width=78) (actual time=43.262..1631.393 rows=166665 loops=1) |
| | 838 | Buffers: shared hit=689774 |
| | 839 | -> Nested Loop (cost=1520.29..25876.96 rows=18915 width=74) (actual time=43.200..340.897 rows=166665 loops=1) |
| | 840 | Buffers: shared hit=23114 |
| | 841 | -> CTE Scan on params p (cost=0.00..0.02 rows=1 width=8) (actual time=0.020..0.022 rows=1 loops=1) |
| | 842 | -> 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) |
| | 843 | Recheck Cond: ((date_from >= p.start_date) AND (date_from < p.end_date)) |
| | 844 | Filter: ((status)::text = 'Completed'::text) |
| | 845 | Rows Removed by Filter: 833352 |
| | 846 | Heap Blocks: exact=22223 |
| | 847 | Buffers: shared hit=23114 |
| | 848 | -> 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) |
| | 849 | Index Cond: ((date_from >= p.start_date) AND (date_from < p.end_date)) |
| | 850 | 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=166665) |
| | 852 | Index Cond: ((booking_id)::text = (b.booking_id)::text) |
| | 853 | Buffers: shared hit=666660 |
| | 854 | -> Materialize (cost=87930.14..87942.48 rows=2 width=311) (actual time=8566.266..8566.290 rows=2 loops=1) |
| | 855 | Buffers: shared hit=4015978 read=7205 |
| | 856 | -> Subquery Scan on service_counts (cost=87930.14..87942.47 rows=2 width=311) (actual time=8566.261..8566.283 rows=2 loops=1) |
| | 857 | Filter: (service_counts.rank_num = 1) |
| | 858 | Buffers: shared hit=4015978 read=7205 |
| | 859 | -> WindowAgg (cost=87930.14..87937.72 rows=380 width=327) (actual time=8566.254..8566.274 rows=2 loops=1) |
| | 860 | Run Condition: (row_number() OVER (?) <= 1) |
| | 861 | Buffers: shared hit=4015978 read=7205 |
| | 862 | -> Sort (cost=87930.12..87931.07 rows=380 width=319) (actual time=8566.230..8566.236 rows=5 loops=1) |
| | 863 | Sort Key: b_1.owner_id, (count(bs.service_id)) DESC |
| | 864 | Sort Method: quicksort Memory: 25kB |
| | 865 | Buffers: shared hit=4015978 read=7205 |
| | 866 | -> HashAggregate (cost=87910.04..87913.84 rows=380 width=319) (actual time=8566.200..8566.209 rows=5 loops=1) |
| | 867 | 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) |
| | 889 | Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| | 890 | Buffers: shared hit=1 |
| | 891 | -> Seq Scan on services s (cost=0.00..11.90 rows=190 width=364) (actual time=0.030..0.031 rows=4 loops=1) |
| | 892 | 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 |
| | 904 | Buffers: shared hit=1 |
| | 905 | -> Seq Scan on users u (cost=0.00..15.80 rows=580 width=51) (actual time=0.030..0.033 rows=12 loops=1) |
| | 906 | Buffers: shared hit=1 |
| | 907 | 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. |
| | 920 | |
| | 921 | 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 | {{{ |
| | 923 | #!sql |
| | 924 | DROP INDEX project.idx_booking_services_booking_id; |
| | 925 | DROP INDEX project.idx_payments_booking_id; |
| | 926 | DROP INDEX project.idx_bookings_date_sitter; |
| | 927 | DROP INDEX project.idx_bookings_date_owner; |
| | 928 | }}} |
| | 929 | |
| | 930 | |
| | 931 | |
| | 932 | == Security == |
| | 933 | |
| | 934 | === 1. Password Security (BCrypt) === |
| | 935 | User passwords are encrypted before being stored in the database using {{{BCryptPasswordEncoder}}}. This ensures that even in the event of a database breach, plain-text passwords remain unreadable and are protected against brute-force and dictionary attacks. Authentication is securely handled by verifying the raw input against the stored BCrypt hash within the {{{UserService}}}. |
| | 936 | |
| | 937 | Implementation in {{{UserService.java}}}: |
| | 938 | {{{#!java |
| | 939 | private final BCryptPasswordEncoder passwordEncoder = new BCryptPasswordEncoder(); |
| | 940 | |
| | 941 | @Transactional(readOnly = true) |
| | 942 | public User authenticate(String username, String password) { |
| | 943 | User user = userRepository.findByUsername(username).orElse(null); |
| | 944 | if (user != null && passwordEncoder.matches(password, user.getPassword())) { |
| | 945 | return user; |
| | 946 | } |
| | 947 | return null; |
| | 948 | } |
| | 949 | |
| | 950 | @Transactional |
| | 951 | public User registerUser(String username, String password, String firstName, String lastName, String email, String role) { |
| | 952 | // ... |
| | 953 | newUser.setUsername(username); |
| | 954 | newUser.setPassword(passwordEncoder.encode(password)); |
| | 955 | newUser.setFirstName(firstName); |
| | 956 | // ... |
| | 957 | } |
| | 958 | }}} |
| | 959 | |
| | 960 | === 2. SQL Injection Prevention === |
| | 961 | The application utilizes '''Spring Data JPA''' for all database interactions. By relying on JPA's built in repository methods, all user inputs are automatically treated as parameters rather than executable SQL strings. This protects the application from SQL Injection attacks. |
| | 962 | |
| | 963 | Example of safe repository usage in {{{UserService.java}}}: |
| | 964 | {{{#!java |
| | 965 | @Transactional(readOnly = true) |
| | 966 | public User authenticate(String username, String password) { |
| | 967 | User user = userRepository.findByUsername(username).orElse(null); |
| | 968 | // ... |
| | 969 | } |
| | 970 | }}} |
| | 971 | |
| | 972 | === 3. Role based access control (RBAC) === |
| | 973 | The application implements strict endpoint protection based on user roles (Admin, PetOwner, PetSitter). Controller routes verify the identity and specific subtype of the authenticated user via the {{{HttpSession}}}. If an unauthorized role attempts to access restricted areas, they are immediately redirected, preventing privilege escalation. |
| | 974 | |
| | 975 | Implementation example in {{{AdminController.java}}}: |
| | 976 | {{{#!java |
| | 977 | @GetMapping("/admin/users") |
| | 978 | public String showAllUsers(HttpSession session, Model model) { |
| | 979 | User user = (User) session.getAttribute("loggedInUser"); |
| | 980 | |
| | 981 | if (user == null || user instanceof PetOwner || user instanceof PetSitter) { |
| | 982 | return "redirect:/dashboard"; |
| | 983 | } |
| | 984 | |
| | 985 | List<User> users = userRepository.findAll(); |
| | 986 | model.addAttribute("users", users); |
| | 987 | // ... |
| | 988 | return "admin-users"; |
| | 989 | } |
| | 990 | }}} |
| | 991 | |
| | 992 | ---- |
| | 993 | |
| | 994 | == Not applicable == |
| | 995 | |
| | 996 | During the security analysis phase, two common web security measures were evaluated but found to be inapplicable for our specific architecture: |
| | 997 | |
| | 998 | === JWTs === |
| | 999 | '''JSON Web Tokens''' are designed for Stateless REST APIs (for example, when using a completely separate React/Vue frontend). |
| | 1000 | |
| | 1001 | Because our application uses a '''Server Side Rendering - SSR ''' architecture returning HTML views directly from controllers, we manage state using the built-in {{{HttpSession}}}. Session based authentication is the standard for this specific architecture and provides excellent security. Implementing JWTs would require an unnecessary and highly complex rewrite of our entire authentication system, moving away from SSR best practices. |
| | 1002 | |
| | 1003 | === CORS === |
| | 1004 | |
| | 1005 | 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. |
