| | 1 | = Other Topics |
| | 2 | == SQL Performance, adding Indexes |
| | 3 | To analyze the performance of our queries, we consider several different use cases. |
| | 4 | |
| | 5 | The testing approach is as follows: |
| | 6 | |
| | 7 | We first insert a large number of new records into the tables (around 1~2 million rows) so that using an index becomes meaningful and beneficial when applied correctly. It is clear that for a table with only a small number of records, indexing would not provide a significant advantage. |
| | 8 | |
| | 9 | Before creating the index, we execute the queries 10 times using {{{EXPLAIN ANALYZE}}}. We then calculate the average Execution Time and record the query plan, so we can later compare it with the new plan after the index is introduced. |
| | 10 | |
| | 11 | After adding the index, we run the same query again 10 times and compare the results in terms of execution time and query plan. |
| | 12 | === Scenario 1 |
| | 13 | ==== Making appointments table have 1milion rows |
| | 14 | {{{ |
| | 15 | ALTER TABLE appointments DISABLE TRIGGER trg_appointments_no_overlap; |
| | 16 | |
| | 17 | INSERT INTO appointments (clinic_id, animal_id, responsible_owner_id, status, date_time, notes) |
| | 18 | SELECT |
| | 19 | c.clinic_id, |
| | 20 | a.animal_id, |
| | 21 | a.owner_id, |
| | 22 | CASE |
| | 23 | WHEN dt < now() THEN |
| | 24 | (ARRAY['DONE','CANCELLED'])[floor(random()*2)+1] |
| | 25 | ELSE |
| | 26 | 'CONFIRMED' |
| | 27 | END, |
| | 28 | dt, |
| | 29 | 'Auto generated' |
| | 30 | FROM generate_series(1, 1000000) gs |
| | 31 | JOIN LATERAL ( |
| | 32 | SELECT clinic_id FROM vet_clinics ORDER BY random() LIMIT 1 |
| | 33 | ) c ON true |
| | 34 | JOIN LATERAL ( |
| | 35 | SELECT animal_id, owner_id FROM animals ORDER BY random() LIMIT 1 |
| | 36 | ) a ON true |
| | 37 | JOIN LATERAL ( |
| | 38 | SELECT NOW() - (random() * INTERVAL '90 days') AS dt |
| | 39 | ) t ON true; |
| | 40 | |
| | 41 | ALTER TABLE appointments ENABLE TRIGGER trg_appointments_no_overlap; |
| | 42 | }}} |
| | 43 | * I disabled the overlap trigger for easier testing, after I have inserted all the rows I enabled it again |
| | 44 | ==== Executing the query 10 times to see average execution time |
| | 45 | {{{ |
| | 46 | EXPLAIN ANALYZE |
| | 47 | SELECT * |
| | 48 | FROM appointments |
| | 49 | WHERE status = 'CONFIRMED' |
| | 50 | AND date_time < now() - interval '45 minutes'; |
| | 51 | }}} |
| | 52 | After the first execution |
| | 53 | {{{ |
| | 54 | Gather (cost=1000.00..21156.46 rows=1 width=62) (actual time=0.646..81.896 rows=1 loops=1) |
| | 55 | Workers Planned: 2 |
| | 56 | Workers Launched: 2 |
| | 57 | -> Parallel Seq Scan on appointments (cost=0.00..20156.36 rows=1 width=62) (actual time=44.874..69.423 rows=0 loops=3) |
| | 58 | Filter: (((status)::text = 'CONFIRMED'::text) AND (date_time < (now() - '00:45:00'::interval))) |
| | 59 | Rows Removed by Filter: 333334 |
| | 60 | Planning Time: 0.726 ms |
| | 61 | Execution Time: 81.930 ms |
| | 62 | }}} |
| | 63 | * Average execution time WITHOUT indexes |
| | 64 | {{{61.1925ms}}} |
| | 65 | {{{ |
| | 66 | Index Scan using idx_appointments_status_date_time on appointments (cost=0.43..7.88 rows=1 width=62) (actual time=0.089..0.090 rows=1 loops=1) |
| | 67 | Index Cond: (((status)::text = 'CONFIRMED'::text) AND (date_time < (now() - '00:45:00'::interval))) |
| | 68 | Planning Time: 1.020 ms |
| | 69 | Execution Time: 0.122 ms |
| | 70 | }}} |
| | 71 | * Average execution time WITH indexes |
| | 72 | {{{0.093ms}}} |
| | 73 | Because the execution time has been optimized with the use of index we will keep the index. |
| | 74 | === Scenario 2 |
| | 75 | ==== Making listings table have 2milion rows |
| | 76 | {{{ |
| | 77 | INSERT INTO listings (animal_id, owner_id, status, price, created_at) |
| | 78 | SELECT |
| | 79 | a.animal_id, |
| | 80 | a.owner_id, |
| | 81 | CASE |
| | 82 | WHEN random() < 0.7 THEN 'DRAFT' |
| | 83 | WHEN random() < 0.9 THEN 'ARCHIVED' |
| | 84 | ELSE 'SOLD' |
| | 85 | END, |
| | 86 | (random() * 1000 + 50)::numeric(10,2), |
| | 87 | NOW() - (random() * INTERVAL '120 days') |
| | 88 | FROM generate_series(1, 2000000) gs |
| | 89 | JOIN LATERAL ( |
| | 90 | SELECT animal_id, owner_id |
| | 91 | FROM animals |
| | 92 | ORDER BY random() |
| | 93 | LIMIT 1 |
| | 94 | ) a ON true; |
| | 95 | }}} |
| | 96 | ==== Executing the query 10 times to see average execution time |
| | 97 | {{{ |
| | 98 | EXPLAIN ANALYZE |
| | 99 | SELECT * |
| | 100 | FROM listings |
| | 101 | WHERE status = 'DRAFT' |
| | 102 | AND created_at < now() - interval '30 days'; |
| | 103 | }}} |
| | 104 | After the first execution |
| | 105 | {{{ |
| | 106 | Seq Scan on listings (cost=0.00..60619.18 rows=1058297 width=576) (actual time=0.014..453.562 rows=1051498 loops=1) |
| | 107 | Filter: (((status)::text = 'DRAFT'::text) AND (created_at < (now() - '30 days'::interval))) |
| | 108 | Rows Removed by Filter: 948511 |
| | 109 | Planning Time: 0.544 ms |
| | 110 | Execution Time: 487.292 ms |
| | 111 | }}} |
| | 112 | * Average execution time WITHOUT indexes |
| | 113 | {{{494.8984ms}}} |
| | 114 | {{{ |
| | 115 | Seq Scan on listings (cost=0.00..60619.18 rows=1058332 width=576) (actual time=0.044..473.306 rows=1051534 loops=1) |
| | 116 | Filter: (((status)::text = 'DRAFT'::text) AND (created_at < (now() - '30 days'::interval))) |
| | 117 | Rows Removed by Filter: 948475 |
| | 118 | Planning Time: 0.092 ms |
| | 119 | Execution Time: 507.535 ms |
| | 120 | |
| | 121 | }}} |
| | 122 | * Average execution time WITH indexes |
| | 123 | {{{500.327ms}}} |
| | 124 | There isn't a change in the execution time, the query doesn't even use the index because its's cheaper to just use seq scan, we won't be keeping this index. |
| | 125 | === Scenario 3 |
| | 126 | Executing with EXPLAIN ANALYZE |
| | 127 | {{{ |
| | 128 | EXPLAIN ANALYZE |
| | 129 | WITH params AS ( |
| | 130 | SELECT |
| | 131 | TIMESTAMP '2025-01-01 00:00:00' AS start_ts, |
| | 132 | TIMESTAMP '2026-01-01 00:00:00' AS end_ts |
| | 133 | ), |
| | 134 | listings_by_user AS ( |
| | 135 | SELECT l.owner_id AS user_id, COUNT(*) AS listings_created |
| | 136 | FROM listings l |
| | 137 | JOIN params p ON l.created_at >= p.start_ts AND l.created_at < p.end_ts |
| | 138 | GROUP BY l.owner_id |
| | 139 | ), |
| | 140 | reviews_by_user AS ( |
| | 141 | SELECT r.reviewer_id AS user_id, |
| | 142 | COUNT(*) AS reviews_left, |
| | 143 | AVG(r.rating)::numeric(10,2) AS avg_rating_left |
| | 144 | FROM reviews r |
| | 145 | JOIN params p ON r.created_at >= p.start_ts AND r.created_at < p.end_ts |
| | 146 | GROUP BY r.reviewer_id |
| | 147 | ), |
| | 148 | appointments_by_user AS ( |
| | 149 | SELECT a.responsible_owner_id AS user_id, |
| | 150 | COUNT(*) AS appointments_total, |
| | 151 | COUNT(*) FILTER (WHERE a.status = 'DONE') AS appointments_done, |
| | 152 | COUNT(*) FILTER (WHERE a.status = 'NO_SHOW') AS appointments_no_show, |
| | 153 | COUNT(*) FILTER (WHERE a.status = 'CANCELLED') AS appointments_cancelled |
| | 154 | FROM appointments a |
| | 155 | JOIN params p ON a.date_time >= p.start_ts AND a.date_time < p.end_ts |
| | 156 | GROUP BY a.responsible_owner_id |
| | 157 | ), |
| | 158 | favorites_by_user AS ( |
| | 159 | SELECT f.client_id AS user_id, COUNT(*) AS favorites_saved_all_time |
| | 160 | FROM favorite_listings f |
| | 161 | GROUP BY f.client_id |
| | 162 | ) |
| | 163 | SELECT |
| | 164 | u.user_id, u.username, u.email, u.name, u.surname, |
| | 165 | COALESCE(l.listings_created, 0) AS listings_created, |
| | 166 | COALESCE(rv.reviews_left, 0) AS reviews_left, |
| | 167 | COALESCE(rv.avg_rating_left, 0) AS avg_rating_left, |
| | 168 | COALESCE(ap.appointments_total, 0) AS appointments_total, |
| | 169 | COALESCE(ap.appointments_done, 0) AS appointments_done, |
| | 170 | COALESCE(ap.appointments_no_show, 0) AS appointments_no_show, |
| | 171 | COALESCE(ap.appointments_cancelled, 0) AS appointments_cancelled, |
| | 172 | COALESCE(fv.favorites_saved_all_time, 0) AS favorites_saved_all_time, |
| | 173 | ( |
| | 174 | COALESCE(l.listings_created, 0) * 5 |
| | 175 | + COALESCE(rv.reviews_left, 0) * 3 |
| | 176 | + COALESCE(ap.appointments_done, 0) * 2 |
| | 177 | + COALESCE(fv.favorites_saved_all_time, 0) |
| | 178 | - COALESCE(ap.appointments_no_show, 0) * 2 |
| | 179 | ) AS activity_score, |
| | 180 | DENSE_RANK() OVER ( |
| | 181 | ORDER BY |
| | 182 | ( |
| | 183 | COALESCE(l.listings_created, 0) * 5 |
| | 184 | + COALESCE(rv.reviews_left, 0) * 3 |
| | 185 | + COALESCE(ap.appointments_done, 0) * 2 |
| | 186 | + COALESCE(fv.favorites_saved_all_time, 0) |
| | 187 | - COALESCE(ap.appointments_no_show, 0) * 2 |
| | 188 | ) DESC, |
| | 189 | COALESCE(l.listings_created, 0) DESC, |
| | 190 | COALESCE(rv.reviews_left, 0) DESC |
| | 191 | ) AS activity_rank |
| | 192 | FROM users u |
| | 193 | LEFT JOIN listings_by_user l ON l.user_id = u.user_id |
| | 194 | LEFT JOIN reviews_by_user rv ON rv.user_id = u.user_id |
| | 195 | LEFT JOIN appointments_by_user ap ON ap.user_id = u.user_id |
| | 196 | LEFT JOIN favorites_by_user fv ON fv.user_id = u.user_id |
| | 197 | WHERE COALESCE(l.listings_created, 0) |
| | 198 | + COALESCE(rv.reviews_left, 0) |
| | 199 | + COALESCE(ap.appointments_total, 0) |
| | 200 | + COALESCE(fv.favorites_saved_all_time, 0) > 0 |
| | 201 | ORDER BY activity_rank |
| | 202 | LIMIT 10; |
| | 203 | }}} |
| | 204 | Without indexes |
| | 205 | {{{ |
| | 206 | |
| | 207 | Limit (cost=86391.14..86391.14 rows=2 width=2176) (actual time=538.362..538.369 rows=3 loops=1) |
| | 208 | CTE params |
| | 209 | -> Result (cost=0.00..0.01 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=1) |
| | 210 | -> Sort (cost=86391.13..86391.13 rows=2 width=2176) (actual time=538.361..538.366 rows=3 loops=1) |
| | 211 | Sort Key: (dense_rank() OVER (?)) |
| | 212 | Sort Method: quicksort Memory: 25kB |
| | 213 | -> WindowAgg (cost=86391.03..86391.12 rows=2 width=2176) (actual time=538.326..538.334 rows=3 loops=1) |
| | 214 | -> Sort (cost=86391.03..86391.04 rows=2 width=2152) (actual time=538.313..538.318 rows=3 loops=1) |
| | 215 | " Sort Key: ((((((COALESCE(l.listings_created, '0'::bigint) * 5) + (COALESCE((count(*)), '0'::bigint) * 3)) + (COALESCE((count(*) FILTER (WHERE ((a.status)::text = 'DONE'::text))), '0'::bigint) * 2)) + COALESCE((count(*)), '0'::bigint)) - (COALESCE((count(*) FILTER (WHERE ((a.status)::text = 'NO_SHOW'::text))), '0'::bigint) * 2))) DESC, (COALESCE(l.listings_created, '0'::bigint)) DESC, (COALESCE((count(*)), '0'::bigint)) DESC" |
| | 216 | Sort Method: quicksort Memory: 25kB |
| | 217 | -> Merge Left Join (cost=86390.69..86391.02 rows=2 width=2152) (actual time=538.278..538.294 rows=3 loops=1) |
| | 218 | Merge Cond: (u.user_id = a.responsible_owner_id) |
| | 219 | " Filter: ((((COALESCE(l.listings_created, '0'::bigint) + COALESCE((count(*)), '0'::bigint)) + COALESCE((count(*)), '0'::bigint)) + COALESCE((count(*)), '0'::bigint)) > 0)" |
| | 220 | Rows Removed by Filter: 7 |
| | 221 | -> Merge Left Join (cost=82934.17..82934.38 rows=5 width=2112) (actual time=538.223..538.236 rows=10 loops=1) |
| | 222 | Merge Cond: (u.user_id = f.client_id) |
| | 223 | -> Merge Left Join (cost=82933.14..82933.26 rows=5 width=2104) (actual time=538.151..538.161 rows=10 loops=1) |
| | 224 | Merge Cond: (u.user_id = r.reviewer_id) |
| | 225 | -> Merge Left Join (cost=82931.79..82931.84 rows=5 width=2080) (actual time=538.097..538.104 rows=10 loops=1) |
| | 226 | Merge Cond: (u.user_id = l.user_id) |
| | 227 | -> Sort (cost=1.11..1.12 rows=5 width=2072) (actual time=0.025..0.027 rows=10 loops=1) |
| | 228 | Sort Key: u.user_id |
| | 229 | Sort Method: quicksort Memory: 25kB |
| | 230 | -> Seq Scan on users u (cost=0.00..1.05 rows=5 width=2072) (actual time=0.012..0.014 rows=10 loops=1) |
| | 231 | -> Sort (cost=82930.68..82930.69 rows=2 width=16) (actual time=538.069..538.070 rows=1 loops=1) |
| | 232 | Sort Key: l.user_id |
| | 233 | Sort Method: quicksort Memory: 25kB |
| | 234 | -> Subquery Scan on l (cost=82930.63..82930.67 rows=2 width=16) (actual time=538.058..538.060 rows=1 loops=1) |
| | 235 | -> HashAggregate (cost=82930.63..82930.65 rows=2 width=16) (actual time=538.057..538.058 rows=1 loops=1) |
| | 236 | Group Key: l_1.owner_id |
| | 237 | Batches: 1 Memory Usage: 24kB |
| | 238 | -> Nested Loop (cost=0.00..81819.60 rows=222207 width=8) (actual time=0.094..529.559 rows=41024 loops=1) |
| | 239 | Join Filter: ((l_1.created_at >= p.start_ts) AND (l_1.created_at < p.end_ts)) |
| | 240 | Rows Removed by Join Filter: 1958985 |
| | 241 | -> CTE Scan on params p (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.003 rows=1 loops=1) |
| | 242 | -> Seq Scan on listings l_1 (cost=0.00..51821.63 rows=1999863 width=16) (actual time=0.075..395.717 rows=2000009 loops=1) |
| | 243 | -> GroupAggregate (cost=1.35..1.38 rows=1 width=32) (actual time=0.052..0.052 rows=0 loops=1) |
| | 244 | Group Key: r.reviewer_id |
| | 245 | -> Sort (cost=1.35..1.36 rows=1 width=12) (actual time=0.050..0.051 rows=0 loops=1) |
| | 246 | Sort Key: r.reviewer_id |
| | 247 | Sort Method: quicksort Memory: 25kB |
| | 248 | -> Nested Loop (cost=0.00..1.34 rows=1 width=12) (actual time=0.016..0.016 rows=0 loops=1) |
| | 249 | Join Filter: ((r.created_at >= p_1.start_ts) AND (r.created_at < p_1.end_ts)) |
| | 250 | Rows Removed by Join Filter: 14 |
| | 251 | -> CTE Scan on params p_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=1) |
| | 252 | -> Seq Scan on reviews r (cost=0.00..1.13 rows=13 width=20) (actual time=0.011..0.011 rows=14 loops=1) |
| | 253 | -> GroupAggregate (cost=1.03..1.06 rows=2 width=16) (actual time=0.068..0.070 rows=2 loops=1) |
| | 254 | Group Key: f.client_id |
| | 255 | -> Sort (cost=1.03..1.03 rows=2 width=8) (actual time=0.064..0.065 rows=2 loops=1) |
| | 256 | Sort Key: f.client_id |
| | 257 | Sort Method: quicksort Memory: 25kB |
| | 258 | -> Seq Scan on favorite_listings f (cost=0.00..1.02 rows=2 width=8) (actual time=0.010..0.010 rows=2 loops=1) |
| | 259 | -> Materialize (cost=3456.52..3456.57 rows=1 width=40) (actual time=0.049..0.050 rows=0 loops=1) |
| | 260 | -> GroupAggregate (cost=3456.52..3456.55 rows=1 width=40) (actual time=0.044..0.045 rows=0 loops=1) |
| | 261 | Group Key: a.responsible_owner_id |
| | 262 | -> Sort (cost=3456.52..3456.52 rows=1 width=14) (actual time=0.043..0.044 rows=0 loops=1) |
| | 263 | Sort Key: a.responsible_owner_id |
| | 264 | Sort Method: quicksort Memory: 25kB |
| | 265 | -> Nested Loop (cost=3452.46..3456.51 rows=1 width=14) (actual time=0.034..0.035 rows=0 loops=1) |
| | 266 | -> CTE Scan on params p_2 (cost=0.00..0.02 rows=1 width=16) (actual time=0.000..0.001 rows=1 loops=1) |
| | 267 | -> Bitmap Heap Scan on appointments a (cost=3452.46..3456.48 rows=1 width=22) (actual time=0.030..0.030 rows=0 loops=1) |
| | 268 | Recheck Cond: ((date_time >= p_2.start_ts) AND (date_time < p_2.end_ts)) |
| | 269 | -> Bitmap Index Scan on idx_appointments_clinic_date_time (cost=0.00..3452.46 rows=1 width=0) (actual time=0.026..0.026 rows=0 loops=1) |
| | 270 | Index Cond: ((date_time >= p_2.start_ts) AND (date_time < p_2.end_ts)) |
| | 271 | Planning Time: 0.777 ms |
| | 272 | Execution Time: 538.700 ms |
| | 273 | }}} |
| | 274 | * This is an acceptable time so we don't need to optimize it. |
| | 275 | === Scenario 4 |
| | 276 | {{{ |
| | 277 | EXPLAIN ANALYZE |
| | 278 | WITH |
| | 279 | my_likes AS ( |
| | 280 | SELECT fl.listing_id |
| | 281 | FROM favorite_listings fl |
| | 282 | WHERE fl.client_id = 1 |
| | 283 | ), |
| | 284 | |
| | 285 | my_recent_likes AS ( |
| | 286 | SELECT fl.listing_id |
| | 287 | FROM favorite_listings fl |
| | 288 | JOIN listings l ON l.listing_id = fl.listing_id |
| | 289 | WHERE fl.client_id = 1 |
| | 290 | ORDER BY l.created_at DESC |
| | 291 | LIMIT 10 |
| | 292 | ), |
| | 293 | |
| | 294 | similar_users AS ( |
| | 295 | SELECT |
| | 296 | fl2.client_id AS other_user_id, |
| | 297 | COUNT(*) AS overlap_likes |
| | 298 | FROM favorite_listings fl2 |
| | 299 | JOIN my_likes ml ON ml.listing_id = fl2.listing_id |
| | 300 | WHERE fl2.client_id <> 1 |
| | 301 | GROUP BY fl2.client_id |
| | 302 | HAVING COUNT(*) > 0 |
| | 303 | ), |
| | 304 | |
| | 305 | cf_candidates AS ( |
| | 306 | SELECT |
| | 307 | fl.listing_id, |
| | 308 | SUM(su.overlap_likes) AS cf_score, |
| | 309 | COUNT(DISTINCT su.other_user_id) AS liked_by_similar_users |
| | 310 | FROM similar_users su |
| | 311 | JOIN favorite_listings fl ON fl.client_id = su.other_user_id |
| | 312 | LEFT JOIN my_likes ml ON ml.listing_id = fl.listing_id |
| | 313 | WHERE ml.listing_id IS NULL |
| | 314 | GROUP BY fl.listing_id |
| | 315 | ), |
| | 316 | |
| | 317 | content_candidates AS ( |
| | 318 | SELECT |
| | 319 | l2.listing_id, |
| | 320 | COUNT(*) AS content_score |
| | 321 | FROM my_recent_likes r |
| | 322 | JOIN listings l1 ON l1.listing_id = r.listing_id |
| | 323 | JOIN animals a1 ON a1.animal_id = l1.animal_id |
| | 324 | |
| | 325 | JOIN listings l2 ON l2.listing_id <> l1.listing_id |
| | 326 | JOIN animals a2 ON a2.animal_id = l2.animal_id |
| | 327 | |
| | 328 | LEFT JOIN my_likes ml ON ml.listing_id = l2.listing_id |
| | 329 | WHERE ml.listing_id IS NULL |
| | 330 | AND ( |
| | 331 | a2.species = a1.species |
| | 332 | OR a2.breed = a1.breed |
| | 333 | OR a2.located_name = a1.located_name |
| | 334 | ) |
| | 335 | GROUP BY l2.listing_id |
| | 336 | ), |
| | 337 | |
| | 338 | merged AS ( |
| | 339 | SELECT |
| | 340 | COALESCE(cf.listing_id, cc.listing_id) AS listing_id, |
| | 341 | COALESCE(cf.cf_score, 0) AS cf_score, |
| | 342 | COALESCE(cf.liked_by_similar_users, 0) AS liked_by_similar_users, |
| | 343 | COALESCE(cc.content_score, 0) AS content_score |
| | 344 | FROM cf_candidates cf |
| | 345 | FULL OUTER JOIN content_candidates cc |
| | 346 | ON cc.listing_id = cf.listing_id |
| | 347 | ) |
| | 348 | |
| | 349 | SELECT |
| | 350 | l.listing_id, |
| | 351 | a.name AS title, |
| | 352 | a.species, |
| | 353 | a.breed, |
| | 354 | a.located_name AS location, |
| | 355 | l.created_at, |
| | 356 | m.cf_score, |
| | 357 | m.liked_by_similar_users, |
| | 358 | m.content_score, |
| | 359 | (m.cf_score * 3 + m.content_score * 2) AS final_score |
| | 360 | FROM merged m |
| | 361 | JOIN listings l ON l.listing_id = m.listing_id |
| | 362 | JOIN animals a ON a.animal_id = l.animal_id |
| | 363 | WHERE l.status = 'ACTIVE' |
| | 364 | AND l.owner_id <> 1 |
| | 365 | ORDER BY final_score DESC, l.created_at DESC |
| | 366 | LIMIT 20; |
| | 367 | }}} |
| | 368 | Without indexes |
| | 369 | {{{ |
| | 370 | Limit (cost=264852.48..264852.48 rows=1 width=2160) (actual time=7249.269..7249.523 rows=3 loops=1) |
| | 371 | CTE my_likes |
| | 372 | -> Seq Scan on favorite_listings fl_2 (cost=0.00..1.02 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=1) |
| | 373 | Filter: (client_id = 1) |
| | 374 | Rows Removed by Filter: 1 |
| | 375 | -> Sort (cost=264851.45..264851.46 rows=1 width=2160) (actual time=7008.341..7008.593 rows=3 loops=1) |
| | 376 | " Sort Key: (((COALESCE(cf.cf_score, '0'::numeric) * '3'::numeric) + ((COALESCE((count(*)), '0'::bigint) * 2))::numeric)) DESC, l.created_at DESC" |
| | 377 | Sort Method: quicksort Memory: 25kB |
| | 378 | -> Nested Loop (cost=218242.91..264851.44 rows=1 width=2160) (actual time=4594.816..7007.250 rows=3 loops=1) |
| | 379 | Join Filter: (l.animal_id = a.animal_id) |
| | 380 | Rows Removed by Join Filter: 9 |
| | 381 | -> Hash Join (cost=218242.91..264850.37 rows=1 width=72) (actual time=4594.763..7007.059 rows=3 loops=1) |
| | 382 | " Hash Cond: (COALESCE(cf.listing_id, l2.listing_id) = l.listing_id)" |
| | 383 | -> Hash Full Join (cost=172920.66..215833.91 rows=1407309 width=64) (actual time=2751.983..5316.014 rows=2000008 loops=1) |
| | 384 | Hash Cond: (l2.listing_id = cf.listing_id) |
| | 385 | -> HashAggregate (cost=172918.43..197986.12 rows=1407309 width=16) (actual time=2751.308..4818.192 rows=2000008 loops=1) |
| | 386 | Group Key: l2.listing_id |
| | 387 | Planned Partitions: 32 Batches: 33 Memory Usage: 8209kB Disk Usage: 63168kB |
| | 388 | -> Hash Anti Join (cost=3.38..93757.30 rows=1407309 width=8) (actual time=1.187..1532.095 rows=2000008 loops=1) |
| | 389 | Hash Cond: (l2.listing_id = ml.listing_id) |
| | 390 | -> Hash Join (cost=3.35..75989.99 rows=1407310 width=8) (actual time=1.125..1117.188 rows=2000008 loops=1) |
| | 391 | Hash Cond: (l2.animal_id = a2.animal_id) |
| | 392 | Join Filter: (l2.listing_id <> l1.listing_id) |
| | 393 | Rows Removed by Join Filter: 1 |
| | 394 | -> Seq Scan on listings l2 (cost=0.00..51821.63 rows=1999863 width=16) (actual time=0.146..543.935 rows=2000009 loops=1) |
| | 395 | -> Hash (cost=3.32..3.32 rows=2 width=16) (actual time=0.831..0.857 rows=8 loops=1) |
| | 396 | Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| | 397 | -> Merge Join (cost=2.50..3.32 rows=2 width=16) (actual time=0.779..0.832 rows=8 loops=1) |
| | 398 | Merge Cond: (l1.listing_id = r.listing_id) |
| | 399 | -> Nested Loop (cost=0.57..364472.04 rows=4221933 width=16) (actual time=0.365..0.400 rows=9 loops=1) |
| | 400 | Join Filter: (((a2.species)::text = (a1.species)::text) OR ((a2.breed)::text = (a1.breed)::text) OR ((a2.located_name)::text = (a1.located_name)::text)) |
| | 401 | Rows Removed by Join Filter: 1 |
| | 402 | -> Nested Loop (cost=0.57..244479.22 rows=1999863 width=1556) (actual time=0.330..0.346 rows=2 loops=1) |
| | 403 | -> Index Scan using listings_pk on listings l1 (cost=0.43..199481.87 rows=1999863 width=16) (actual time=0.064..0.065 rows=2 loops=1) |
| | 404 | -> Memoize (cost=0.14..0.16 rows=1 width=1556) (actual time=0.133..0.133 rows=1 loops=2) |
| | 405 | Cache Key: l1.animal_id |
| | 406 | Cache Mode: logical |
| | 407 | Hits: 0 Misses: 2 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| | 408 | -> Index Scan using animals_pk on animals a1 (cost=0.13..0.15 rows=1 width=1556) (actual time=0.124..0.125 rows=1 loops=2) |
| | 409 | Index Cond: (animal_id = l1.animal_id) |
| | 410 | -> Materialize (cost=0.00..1.04 rows=3 width=1556) (actual time=0.012..0.018 rows=5 loops=2) |
| | 411 | -> Seq Scan on animals a2 (cost=0.00..1.03 rows=3 width=1556) (actual time=0.014..0.018 rows=9 loops=1) |
| | 412 | -> Sort (cost=1.92..1.93 rows=1 width=8) (actual time=0.401..0.411 rows=1 loops=1) |
| | 413 | Sort Key: r.listing_id |
| | 414 | Sort Method: quicksort Memory: 25kB |
| | 415 | -> Subquery Scan on r (cost=1.90..1.91 rows=1 width=8) (actual time=0.271..0.281 rows=1 loops=1) |
| | 416 | -> Limit (cost=1.90..1.90 rows=1 width=16) (actual time=0.266..0.274 rows=1 loops=1) |
| | 417 | -> Sort (cost=1.90..1.90 rows=1 width=16) (actual time=0.252..0.259 rows=1 loops=1) |
| | 418 | Sort Key: l_1.created_at DESC |
| | 419 | Sort Method: quicksort Memory: 25kB |
| | 420 | -> Merge Join (cost=1.47..1.89 rows=1 width=16) (actual time=0.218..0.226 rows=1 loops=1) |
| | 421 | Merge Cond: (l_1.listing_id = fl.listing_id) |
| | 422 | -> Index Scan using listings_pk on listings l_1 (cost=0.43..199481.87 rows=1999863 width=16) (actual time=0.035..0.037 rows=2 loops=1) |
| | 423 | -> Sort (cost=1.03..1.04 rows=1 width=8) (actual time=0.164..0.167 rows=1 loops=1) |
| | 424 | Sort Key: fl.listing_id |
| | 425 | Sort Method: quicksort Memory: 25kB |
| | 426 | -> Seq Scan on favorite_listings fl (cost=0.00..1.02 rows=1 width=8) (actual time=0.027..0.029 rows=1 loops=1) |
| | 427 | Filter: (client_id = 1) |
| | 428 | Rows Removed by Filter: 1 |
| | 429 | -> Hash (cost=0.02..0.02 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=1) |
| | 430 | Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| | 431 | -> CTE Scan on my_likes ml (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=1) |
| | 432 | -> Hash (cost=2.21..2.21 rows=1 width=48) (actual time=0.611..0.621 rows=0 loops=1) |
| | 433 | Buckets: 1024 Batches: 1 Memory Usage: 8kB |
| | 434 | -> Subquery Scan on cf (cost=2.12..2.21 rows=1 width=48) (actual time=0.610..0.619 rows=0 loops=1) |
| | 435 | -> GroupAggregate (cost=2.12..2.20 rows=1 width=48) (actual time=0.609..0.617 rows=0 loops=1) |
| | 436 | Group Key: fl_1.listing_id |
| | 437 | -> Nested Loop (cost=2.12..2.18 rows=1 width=24) (actual time=0.607..0.615 rows=0 loops=1) |
| | 438 | Join Filter: (fl_1.client_id = fl2.client_id) |
| | 439 | -> Merge Anti Join (cost=1.06..1.09 rows=1 width=16) (actual time=0.452..0.457 rows=1 loops=1) |
| | 440 | Merge Cond: (fl_1.listing_id = ml_1.listing_id) |
| | 441 | -> Sort (cost=1.03..1.03 rows=2 width=16) (actual time=0.346..0.348 rows=2 loops=1) |
| | 442 | Sort Key: fl_1.listing_id |
| | 443 | Sort Method: quicksort Memory: 25kB |
| | 444 | -> Seq Scan on favorite_listings fl_1 (cost=0.00..1.02 rows=2 width=16) (actual time=0.065..0.068 rows=2 loops=1) |
| | 445 | -> Sort (cost=0.03..0.04 rows=1 width=8) (actual time=0.066..0.067 rows=1 loops=1) |
| | 446 | Sort Key: ml_1.listing_id |
| | 447 | Sort Method: quicksort Memory: 25kB |
| | 448 | -> CTE Scan on my_likes ml_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.026..0.028 rows=1 loops=1) |
| | 449 | -> HashAggregate (cost=1.06..1.07 rows=1 width=16) (actual time=0.150..0.152 rows=0 loops=1) |
| | 450 | Group Key: fl2.client_id |
| | 451 | Filter: (count(*) > 0) |
| | 452 | Batches: 1 Memory Usage: 24kB |
| | 453 | -> Nested Loop (cost=0.00..1.06 rows=1 width=8) (actual time=0.079..0.081 rows=0 loops=1) |
| | 454 | Join Filter: (fl2.listing_id = ml_2.listing_id) |
| | 455 | Rows Removed by Join Filter: 1 |
| | 456 | -> Seq Scan on favorite_listings fl2 (cost=0.00..1.02 rows=1 width=16) (actual time=0.012..0.017 rows=1 loops=1) |
| | 457 | Filter: (client_id <> 1) |
| | 458 | Rows Removed by Filter: 1 |
| | 459 | -> CTE Scan on my_likes ml_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1) |
| | 460 | -> Hash (cost=45322.24..45322.24 rows=1 width=24) (actual time=1436.407..1436.608 rows=4 loops=1) |
| | 461 | Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| | 462 | -> Gather (cost=1000.00..45322.24 rows=1 width=24) (actual time=1420.646..1436.431 rows=4 loops=1) |
| | 463 | Workers Planned: 2 |
| | 464 | Workers Launched: 2 |
| | 465 | -> Parallel Seq Scan on listings l (cost=0.00..44322.14 rows=1 width=24) (actual time=1316.362..1367.278 rows=1 loops=3) |
| | 466 | Filter: ((owner_id <> 1) AND ((status)::text = 'ACTIVE'::text)) |
| | 467 | Rows Removed by Filter: 666668 |
| | 468 | -> Seq Scan on animals a (cost=0.00..1.03 rows=3 width=2072) (actual time=0.019..0.019 rows=4 loops=3) |
| | 469 | Planning Time: 11.722 ms |
| | 470 | JIT: |
| | 471 | Functions: 124 |
| | 472 | " Options: Inlining false, Optimization false, Expressions true, Deforming true" |
| | 473 | " Timing: Generation 22.000 ms, Inlining 0.000 ms, Optimization 32.272 ms, Emission 243.487 ms, Total 297.758 ms" |
| | 474 | Execution Time: 7756.372 ms |
| | 475 | |
| | 476 | }}} |
| | 477 | This is not an acceptable execution time so we will optimze it with indexes. |
| | 478 | * We add these indexes |
| | 479 | {{{ |
| | 480 | CREATE INDEX idx_favorite_listings_client_listing |
| | 481 | ON favorite_listings (client_id, listing_id); |
| | 482 | |
| | 483 | CREATE INDEX idx_favorite_listings_listing_client |
| | 484 | ON favorite_listings (listing_id, client_id); |
| | 485 | |
| | 486 | CREATE INDEX idx_listings_status_owner_created |
| | 487 | ON listings (status, owner_id, created_at DESC); |
| | 488 | |
| | 489 | CREATE INDEX idx_listings_animal |
| | 490 | ON listings (animal_id); |
| | 491 | |
| | 492 | CREATE INDEX idx_animals_species |
| | 493 | ON animals (species); |
| | 494 | |
| | 495 | CREATE INDEX idx_animals_breed |
| | 496 | ON animals (breed); |
| | 497 | |
| | 498 | CREATE INDEX idx_animals_located_name |
| | 499 | ON animals (located_name); |
| | 500 | }}} |
| | 501 | With indexes |
| | 502 | {{{ |
| | 503 | Limit (cost=24.22..24.23 rows=1 width=121) (actual time=0.419..0.427 rows=3 loops=1) |
| | 504 | CTE my_likes |
| | 505 | -> Seq Scan on favorite_listings fl_2 (cost=0.00..1.02 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=1) |
| | 506 | Filter: (client_id = 1) |
| | 507 | Rows Removed by Filter: 1 |
| | 508 | -> Sort (cost=23.20..23.20 rows=1 width=121) (actual time=0.418..0.425 rows=3 loops=1) |
| | 509 | " Sort Key: (((COALESCE((sum((count(*)))), '0'::numeric) * '3'::numeric) + ((COALESCE((count(*)), '0'::bigint) * 2))::numeric)) DESC, l.created_at DESC" |
| | 510 | Sort Method: quicksort Memory: 25kB |
| | 511 | -> Nested Loop (cost=7.22..23.19 rows=1 width=121) (actual time=0.381..0.405 rows=3 loops=1) |
| | 512 | -> Nested Loop (cost=7.08..23.02 rows=1 width=72) (actual time=0.357..0.374 rows=3 loops=1) |
| | 513 | -> Merge Full Join (cost=6.65..14.58 rows=1 width=64) (actual time=0.338..0.348 rows=3 loops=1) |
| | 514 | Merge Cond: (fl.listing_id = l2.listing_id) |
| | 515 | -> GroupAggregate (cost=2.55..10.42 rows=1 width=48) (actual time=0.071..0.074 rows=0 loops=1) |
| | 516 | Group Key: fl.listing_id |
| | 517 | -> Nested Loop (cost=2.55..10.40 rows=1 width=24) (actual time=0.070..0.072 rows=0 loops=1) |
| | 518 | Join Filter: (fl.listing_id = l_1.listing_id) |
| | 519 | -> Nested Loop (cost=2.12..2.18 rows=1 width=24) (actual time=0.070..0.072 rows=0 loops=1) |
| | 520 | Join Filter: (fl.client_id = fl2.client_id) |
| | 521 | -> Merge Anti Join (cost=1.06..1.09 rows=1 width=16) (actual time=0.061..0.062 rows=1 loops=1) |
| | 522 | Merge Cond: (fl.listing_id = ml.listing_id) |
| | 523 | -> Sort (cost=1.03..1.03 rows=2 width=16) (actual time=0.034..0.034 rows=2 loops=1) |
| | 524 | Sort Key: fl.listing_id |
| | 525 | Sort Method: quicksort Memory: 25kB |
| | 526 | -> Seq Scan on favorite_listings fl (cost=0.00..1.02 rows=2 width=16) (actual time=0.014..0.015 rows=2 loops=1) |
| | 527 | -> Sort (cost=0.03..0.04 rows=1 width=8) (actual time=0.023..0.024 rows=1 loops=1) |
| | 528 | Sort Key: ml.listing_id |
| | 529 | Sort Method: quicksort Memory: 25kB |
| | 530 | -> CTE Scan on my_likes ml (cost=0.00..0.02 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=1) |
| | 531 | -> HashAggregate (cost=1.06..1.07 rows=1 width=16) (actual time=0.007..0.008 rows=0 loops=1) |
| | 532 | Group Key: fl2.client_id |
| | 533 | Batches: 1 Memory Usage: 24kB |
| | 534 | -> Nested Loop (cost=0.00..1.06 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=1) |
| | 535 | Join Filter: (fl2.listing_id = ml_1.listing_id) |
| | 536 | Rows Removed by Join Filter: 1 |
| | 537 | -> Seq Scan on favorite_listings fl2 (cost=0.00..1.02 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=1) |
| | 538 | Filter: (client_id <> 1) |
| | 539 | Rows Removed by Filter: 1 |
| | 540 | -> CTE Scan on my_likes ml_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=1) |
| | 541 | -> Index Scan using idx_listings_status_owner_created on listings l_1 (cost=0.43..8.20 rows=1 width=8) (never executed) |
| | 542 | Index Cond: ((status)::text = 'ACTIVE'::text) |
| | 543 | Filter: (owner_id <> 1) |
| | 544 | -> GroupAggregate (cost=4.10..4.12 rows=1 width=16) (actual time=0.266..0.272 rows=3 loops=1) |
| | 545 | Group Key: l2.listing_id |
| | 546 | -> Sort (cost=4.10..4.11 rows=1 width=8) (actual time=0.262..0.265 rows=3 loops=1) |
| | 547 | Sort Key: l2.listing_id |
| | 548 | Sort Method: quicksort Memory: 25kB |
| | 549 | -> Nested Loop (cost=3.23..4.09 rows=1 width=8) (actual time=0.214..0.246 rows=3 loops=1) |
| | 550 | Join Filter: (((a2.species)::text = (a1.species)::text) OR ((a2.breed)::text = (a1.breed)::text) OR ((a2.located_name)::text = (a1.located_name)::text)) |
| | 551 | -> Merge Join (cost=3.09..3.92 rows=1 width=36) (actual time=0.188..0.214 rows=3 loops=1) |
| | 552 | Merge Cond: (l1.listing_id = r.listing_id) |
| | 553 | -> Nested Loop (cost=0.85..229633.64 rows=2000898 width=44) (actual time=0.118..0.140 rows=4 loops=1) |
| | 554 | Join Filter: (l2.listing_id <> l1.listing_id) |
| | 555 | -> Index Scan using listings_pk on listings l1 (cost=0.43..199610.69 rows=2000900 width=16) (actual time=0.018..0.019 rows=2 loops=1) |
| | 556 | -> Materialize (cost=0.43..9.44 rows=1 width=28) (actual time=0.049..0.058 rows=2 loops=2) |
| | 557 | -> Nested Loop Anti Join (cost=0.43..9.44 rows=1 width=28) (actual time=0.090..0.107 rows=3 loops=1) |
| | 558 | Join Filter: (ml_2.listing_id = l2.listing_id) |
| | 559 | Rows Removed by Join Filter: 3 |
| | 560 | -> Nested Loop (cost=0.43..9.41 rows=1 width=28) (actual time=0.088..0.102 rows=4 loops=1) |
| | 561 | Join Filter: (a2.animal_id = l2.animal_id) |
| | 562 | Rows Removed by Join Filter: 9 |
| | 563 | -> Index Scan using idx_listings_status_owner_created on listings l2 (cost=0.43..8.20 rows=1 width=16) (actual time=0.082..0.085 rows=4 loops=1) |
| | 564 | Index Cond: ((status)::text = 'ACTIVE'::text) |
| | 565 | Filter: (owner_id <> 1) |
| | 566 | Rows Removed by Filter: 1 |
| | 567 | -> Seq Scan on animals a2 (cost=0.00..1.09 rows=9 width=28) (actual time=0.002..0.002 rows=3 loops=4) |
| | 568 | -> CTE Scan on my_likes ml_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=4) |
| | 569 | -> Sort (cost=2.23..2.24 rows=1 width=8) (actual time=0.068..0.070 rows=1 loops=1) |
| | 570 | Sort Key: r.listing_id |
| | 571 | Sort Method: quicksort Memory: 25kB |
| | 572 | -> Subquery Scan on r (cost=2.21..2.22 rows=1 width=8) (actual time=0.053..0.054 rows=1 loops=1) |
| | 573 | -> Limit (cost=2.21..2.21 rows=1 width=16) (actual time=0.052..0.053 rows=1 loops=1) |
| | 574 | -> Sort (cost=2.21..2.21 rows=1 width=16) (actual time=0.051..0.052 rows=1 loops=1) |
| | 575 | Sort Key: l_2.created_at DESC |
| | 576 | Sort Method: quicksort Memory: 25kB |
| | 577 | -> Merge Join (cost=1.46..2.20 rows=1 width=16) (actual time=0.035..0.038 rows=1 loops=1) |
| | 578 | Merge Cond: (l_2.listing_id = fl_1.listing_id) |
| | 579 | -> Index Scan using listings_pk on listings l_2 (cost=0.43..199610.69 rows=2000900 width=16) (actual time=0.019..0.020 rows=2 loops=1) |
| | 580 | -> Sort (cost=1.03..1.04 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=1) |
| | 581 | Sort Key: fl_1.listing_id |
| | 582 | Sort Method: quicksort Memory: 25kB |
| | 583 | -> Seq Scan on favorite_listings fl_1 (cost=0.00..1.02 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1) |
| | 584 | Filter: (client_id = 1) |
| | 585 | Rows Removed by Filter: 1 |
| | 586 | -> Index Scan using animals_pk on animals a1 (cost=0.14..0.15 rows=1 width=28) (actual time=0.008..0.008 rows=1 loops=3) |
| | 587 | Index Cond: (animal_id = l1.animal_id) |
| | 588 | -> Index Scan using listings_pk on listings l (cost=0.43..8.45 rows=1 width=24) (actual time=0.007..0.007 rows=1 loops=3) |
| | 589 | " Index Cond: (listing_id = COALESCE(fl.listing_id, l2.listing_id))" |
| | 590 | -> Index Scan using animals_pk on animals a (cost=0.14..0.15 rows=1 width=33) (actual time=0.005..0.005 rows=1 loops=3) |
| | 591 | Index Cond: (animal_id = l.animal_id) |
| | 592 | Planning Time: 7.688 ms |
| | 593 | Execution Time: 0.904 ms |
| | 594 | }}} |
| | 595 | * The indexes worked, now the execution time is accepatable. |
| | 596 | == Security measures |
| | 597 | === SQL injection prevention |
| | 598 | {{{ |
| | 599 | @Query(value = """ |
| | 600 | SELECT * |
| | 601 | FROM get_top_active_users(:startTs, :endTs) |
| | 602 | """, nativeQuery = true) |
| | 603 | List<UserActivityRankingProjection> getTopActiveUsers( |
| | 604 | @Param("startTs") LocalDateTime startTs, |
| | 605 | @Param("endTs") LocalDateTime endTs |
| | 606 | ); |
| | 607 | }}} |
| | 608 | * Native queries are executed using named parameters such as :startTs and :endTs, which are bound by Spring Data JPA. This prevents SQL injection because input values are treated as parameters, not as executable SQL. |
| | 609 | === Keeping passwords |
| | 610 | *Password security is implemented using {{{BCryptPasswordEncoder}}}, which hashes user passwords before storing them in the database. This ensures that passwords are not stored in plain text and provides protection against brute-force and dictionary attacks. |
| | 611 | {{{ |
| | 612 | |
| | 613 | @Bean |
| | 614 | public PasswordEncoder passwordEncoder() { |
| | 615 | return new BCryptPasswordEncoder(); |
| | 616 | } |
| | 617 | }}} |
| | 618 | === CORS |
| | 619 | * The application uses a CORS configuration to control which frontend clients are allowed to communicate with the backend API. In development, requests are allowed only from local frontend origins such as [Acronym:http://localhost:*] (this will be changed to the correct URL of the hosted site). The configuration defines the permitted HTTP methods, including GET, POST, PUT, PATCH, DELETE, and OPTIONS. |
| | 620 | {{{ |
| | 621 | @Bean |
| | 622 | public CorsConfigurationSource corsConfigurationSource() { |
| | 623 | CorsConfiguration configuration = new CorsConfiguration(); |
| | 624 | configuration.setAllowedOriginPatterns(List.of("http://localhost:*")); |
| | 625 | configuration.setAllowedMethods(Arrays.asList("GET", "POST", "PUT","PATCH", "DELETE", "OPTIONS")); |
| | 626 | configuration.setAllowedHeaders(Arrays.asList("*")); |
| | 627 | configuration.setAllowCredentials(true); |
| | 628 | |
| | 629 | UrlBasedCorsConfigurationSource source = new UrlBasedCorsConfigurationSource(); |
| | 630 | source.registerCorsConfiguration("/**", configuration); |
| | 631 | return source; |
| | 632 | } |
| | 633 | }}} |
| | 634 | |
| | 635 | == Other developments |
