== Performance == === 1. Adding mock data for bookings === To properly benchmark our database, we first generate a realistic dataset of 1000000 bookings and related entities which. {{{ #!sql -- 1. Insert 1,000,000 mock bookings ALTER TABLE project.bookings DISABLE TRIGGER trg_booking_validation; INSERT INTO project.bookings (status, date_from, date_to, address, owner_id, sitter_id, service_id) SELECT (ARRAY['Pending', 'Confirmed', 'Completed', 'Canceled', 'Expired', 'Reviewed'])[floor(random()*6)+1] AS status, t.dt AS date_from, t.dt + (random() * INTERVAL '7 days') AS date_to, 'Mock Address ' || gs AS address, o.user_id AS owner_id, s.user_id AS sitter_id, sv.service_id AS service_id FROM generate_series(1, 1000000) gs JOIN LATERAL ( SELECT user_id FROM project.pet_owners ORDER BY random() LIMIT 1 ) o ON true JOIN LATERAL ( SELECT user_id FROM project.pet_sitters ORDER BY random() LIMIT 1 ) s ON true JOIN LATERAL ( SELECT service_id FROM project.services ORDER BY random() LIMIT 1 ) sv ON true JOIN LATERAL ( SELECT CURRENT_DATE - (random() * INTERVAL '365 days') AS dt ) t ON true; ALTER TABLE project.bookings ENABLE TRIGGER trg_booking_validation; }}} {{{ #!sql -- 2. Insert relevant mock payments INSERT INTO project.payments (booking_id, amount, payment_type) SELECT b.booking_id, floor(random() * 100 + 20)::int AS amount, (ARRAY['Card', 'Cash', 'Bank Transfer'])[floor(random()*3)+1] AS payment_type FROM project.bookings b WHERE NOT EXISTS ( SELECT 1 FROM project.payments p WHERE p.booking_id = b.booking_id ); }}} {{{ #!sql -- 3. Insert mock reviews for completed bookings INSERT INTO project.reviews (booking_id, rating, comment) SELECT b.booking_id, floor(random()*5)+1 AS rating, 'Mock Review ' || b.booking_id AS comment FROM project.bookings b WHERE b.status = 'Completed' AND random() > 0.3 AND NOT EXISTS ( SELECT 1 FROM project.reviews r WHERE r.booking_id = b.booking_id ); }}} {{{ #!sql -- 4. Insert mock pets INSERT INTO project.pets (owner_id, name, age, pettype_id) SELECT po.user_id, 'Mock Pet ' || po.user_id || '-' || gs AS name, floor(random()*15)+1 AS age, pt.pettype_id FROM project.pet_owners po CROSS JOIN LATERAL generate_series(1, floor(random()*3 + 1)::int) gs JOIN LATERAL ( SELECT pettype_id FROM project.pet_types ORDER BY random() LIMIT 1 ) pt ON true; }}} Benchmark query: {{{ #!sql EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM project.bookings WHERE sitter_id = (SELECT user_id FROM project.pet_sitters LIMIT 1) ORDER BY date_from DESC; }}} Execution without indexes: {{{ Gather Merge (cost=118911.59..233428.55 rows=967749 width=183) (actual time=85.181..92.703 rows=2 loops=1) Workers Planned: 3 Workers Launched: 3 Buffers: shared hit=27139 InitPlan 1 -> Limit (cost=0.00..0.02 rows=1 width=90) (actual time=3.311..3.312 rows=1 loops=1) Buffers: shared hit=1 -> Seq Scan on pet_sitters (cost=0.00..16.90 rows=690 width=90) (actual time=0.018..0.019 rows=1 loops=1) Buffers: shared hit=1 -> Sort (cost=117911.52..118717.98 rows=322583 width=183) (actual time=54.026..54.027 rows=0 loops=4) Sort Key: bookings.date_from DESC Sort Method: quicksort Memory: 25kB Buffers: shared hit=27138 Worker 0: Sort Method: quicksort Memory: 25kB Worker 1: Sort Method: quicksort Memory: 25kB Worker 2: Sort Method: quicksort Memory: 25kB -> Parallel Seq Scan on bookings (cost=0.00..31059.29 rows=322583 width=183) (actual time=33.675..53.902 rows=0 loops=4) Filter: ((sitter_id)::text = ((InitPlan 1).col1)::text) Rows Removed by Filter: 250002 Buffers: shared hit=27027 Planning Time: 0.168 ms JIT: Functions: 12 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 1.400 ms (Deform 0.651 ms), Inlining 0.000 ms, Optimization 1.233 ms, Emission 18.598 ms, Total 21.231 ms Execution Time: 93.143 ms }}} '''Average execution time (10 attempts):''' 93.093ms Next, we add this index: {{{ #!sql CREATE INDEX idx_bookings_sitter_date ON project.bookings (sitter_id, date_from DESC); }}} (Note, for the exact same performance reasons, we also created this index to optimize the queries when Pet Owners view their own bookings) {{{ #!sql CREATE INDEX idx_bookings_owner_date ON project.bookings (owner_id, date_from DESC); }}} Execution with indexes: {{{ Index Scan using idx_bookings_sitter_date on bookings (cost=0.45..83614.21 rows=1000008 width=183) (actual time=0.051..0.053 rows=2 loops=1) Index Cond: ((sitter_id)::text = ((InitPlan 1).col1)::text) Buffers: shared hit=5 InitPlan 1 -> Limit (cost=0.00..0.02 rows=1 width=90) (actual time=0.018..0.019 rows=1 loops=1) Buffers: shared hit=1 -> Seq Scan on pet_sitters (cost=0.00..16.90 rows=690 width=90) (actual time=0.017..0.017 rows=1 loops=1) Buffers: shared hit=1 Planning Time: 0.179 ms Execution Time: 0.081 ms }}} '''Average execution time (10 attempts):''' 0.082ms Because the execution time has been massively lowered by bypassing the expensive sequential scan and memory sort, we '''keep''' this index. === 2. Calculate average sitter rating === Benchmark query: {{{ #!sql EXPLAIN (ANALYZE, BUFFERS) SELECT AVG(r.rating) FROM project.reviews r JOIN project.bookings b ON r.booking_id = b.booking_id WHERE b.sitter_id = (SELECT user_id FROM project.pet_sitters LIMIT 1); }}} Execution without indexes: {{{ Finalize Aggregate (cost=36930.42..36930.43 rows=1 width=32) (actual time=114.078..121.145 rows=1 loops=1) Buffers: shared hit=29598 InitPlan 1 -> Limit (cost=0.00..0.02 rows=1 width=90) (actual time=0.014..0.015 rows=1 loops=1) Buffers: shared hit=1 -> Seq Scan on pet_sitters (cost=0.00..16.90 rows=690 width=90) (actual time=0.012..0.013 rows=1 loops=1) Buffers: shared hit=1 -> Gather (cost=36930.08..36930.39 rows=3 width=32) (actual time=112.766..121.113 rows=4 loops=1) Workers Planned: 3 Workers Launched: 3 Buffers: shared hit=29598 -> Partial Aggregate (cost=35930.08..35930.09 rows=1 width=32) (actual time=86.975..86.979 rows=1 loops=4) Buffers: shared hit=29597 -> Parallel Hash Join (cost=3929.82..35835.89 rows=37676 width=4) (actual time=71.573..86.969 rows=0 loops=4) Hash Cond: ((b.booking_id)::text = (r.booking_id)::text) Buffers: shared hit=29597 -> Parallel Seq Scan on bookings b (cost=0.00..31059.29 rows=322583 width=37) (actual time=46.200..61.590 rows=0 loops=4) Filter: ((sitter_id)::text = ((InitPlan 1).col1)::text) Rows Removed by Filter: 250002 Buffers: shared hit=27027 -> Parallel Hash (cost=3071.03..3071.03 rows=68703 width=41) (actual time=24.567..24.568 rows=29199 loops=4) Buckets: 131072 Batches: 1 Memory Usage: 10240kB Buffers: shared hit=2384 -> Parallel Seq Scan on reviews r (cost=0.00..3071.03 rows=68703 width=41) (actual time=0.027..8.694 rows=29199 loops=4) Buffers: shared hit=2384 Planning: Buffers: shared hit=170 dirtied=1 Planning Time: 1.219 ms Execution Time: 121.247 ms }}} '''Average execution time (10 attempts):''' 116.413ms We add this index: {{{ #!sql CREATE INDEX idx_reviews_booking_id ON project.reviews (booking_id); }}} Execution with indexes: {{{ Finalize Aggregate (cost=36930.42..36930.43 rows=1 width=32) (actual time=114.523..121.209 rows=1 loops=1) Buffers: shared hit=29598 InitPlan 1 -> Limit (cost=0.00..0.02 rows=1 width=90) (actual time=0.014..0.016 rows=1 loops=1) Buffers: shared hit=1 -> Seq Scan on pet_sitters (cost=0.00..16.90 rows=690 width=90) (actual time=0.013..0.013 rows=1 loops=1) Buffers: shared hit=1 -> Gather (cost=36930.08..36930.39 rows=3 width=32) (actual time=113.185..121.193 rows=4 loops=1) Workers Planned: 3 Workers Launched: 3 Buffers: shared hit=29598 -> Partial Aggregate (cost=35930.08..35930.09 rows=1 width=32) (actual time=83.754..83.757 rows=1 loops=4) Buffers: shared hit=29597 -> Parallel Hash Join (cost=3929.82..35835.89 rows=37676 width=4) (actual time=68.170..83.746 rows=0 loops=4) Hash Cond: ((b.booking_id)::text = (r.booking_id)::text) Buffers: shared hit=29597 -> Parallel Seq Scan on bookings b (cost=0.00..31059.29 rows=322583 width=37) (actual time=46.730..62.303 rows=0 loops=4) Filter: ((sitter_id)::text = ((InitPlan 1).col1)::text) Rows Removed by Filter: 250002 Buffers: shared hit=27027 -> Parallel Hash (cost=3071.03..3071.03 rows=68703 width=41) (actual time=20.662..20.663 rows=29199 loops=4) Buckets: 131072 Batches: 1 Memory Usage: 10240kB Buffers: shared hit=2384 -> Parallel Seq Scan on reviews r (cost=0.00..3071.03 rows=68703 width=41) (actual time=0.023..7.095 rows=29199 loops=4) Buffers: shared hit=2384 Planning: Buffers: shared hit=35 read=6 Planning Time: 0.815 ms Execution Time: 121.271 ms }}} '''Average execution time (10 attempts, first cold-cache run excluded):''' 116.521ms 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. {{{ #!sql DROP INDEX IF EXISTS project.idx_reviews_booking_id; }}} === 3. Sitter Performance Analytics (Phase 6 Query) === Benchmark query: {{{ #!sql EXPLAIN (ANALYZE, BUFFERS) WITH params AS ( SELECT (CURRENT_DATE - INTERVAL '1 year')::DATE AS start_date, CURRENT_DATE::DATE AS end_date ), sitter_stats AS ( SELECT b.sitter_id, COUNT(b.booking_id) AS total_bookings, COUNT(b.booking_id) FILTER (WHERE b.status = 'Completed') AS completed_bookings, COUNT(b.booking_id) FILTER (WHERE b.status IN ('Canceled', 'Rejected')) AS missed_bookings FROM project.bookings b JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date GROUP BY b.sitter_id ), sitter_reports AS ( SELECT b.sitter_id, SUM(pay.amount) AS total_revenue FROM project.bookings b JOIN project.payments pay ON b.booking_id = pay.booking_id JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date WHERE b.status = 'Completed' GROUP BY b.sitter_id ), sitter_ratings AS ( SELECT b.sitter_id, AVG(r.rating)::numeric(10,2) AS avg_rating, COUNT(r.review_id) AS total_reviews FROM project.bookings b JOIN project.reviews r ON b.booking_id = r.booking_id JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date GROUP BY b.sitter_id ) SELECT u.user_id, u.username, u.first_name, u.last_name, COALESCE(ss.total_bookings, 0) AS total_bookings, COALESCE(ss.completed_bookings, 0) AS completed_bookings, COALESCE(ss.missed_bookings, 0) AS missed_bookings, COALESCE(sr.avg_rating, 0) AS avg_rating, COALESCE(sr.total_reviews, 0) AS total_reviews, COALESCE(sf.total_revenue, 0) AS total_revenue, DENSE_RANK() OVER ( ORDER BY ( COALESCE(sf.total_revenue, 0) * 0.5 + COALESCE(ss.completed_bookings, 0) * 10 + COALESCE(sr.avg_rating, 0) * 15 - COALESCE(ss.missed_bookings, 0) * 5 ) DESC ) AS sitter_rank FROM project.users u JOIN project.pet_sitters ps ON u.user_id = ps.user_id LEFT JOIN sitter_stats ss ON ss.sitter_id = ps.user_id LEFT JOIN sitter_reports sf ON sf.sitter_id = ps.user_id LEFT JOIN sitter_ratings sr ON sr.sitter_id = ps.user_id WHERE COALESCE(ss.total_bookings, 0) > 0 ORDER BY sitter_rank LIMIT 10; }}} Execution without indexes: {{{ Limit (cost=164076.47..164076.50 rows=10 width=808) (actual time=2449.436..2457.032 rows=3 loops=1) Buffers: shared hit=752817 CTE params -> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1) -> Sort (cost=164076.45..164077.03 rows=230 width=808) (actual time=2398.618..2406.213 rows=3 loops=1) Sort Key: (dense_rank() OVER (?)) Sort Method: quicksort Memory: 25kB Buffers: shared hit=752817 -> WindowAgg (cost=164061.72..164071.48 rows=230 width=808) (actual time=2398.587..2406.195 rows=3 loops=1) Buffers: shared hit=752817 -> Sort (cost=164061.71..164062.28 rows=230 width=784) (actual time=2398.570..2406.164 rows=3 loops=1) 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 Sort Method: quicksort Memory: 25kB Buffers: shared hit=752817 -> Hash Join (cost=164041.68..164052.68 rows=230 width=784) (actual time=2398.538..2406.155 rows=3 loops=1) Hash Cond: ((ps.user_id)::text = (u.user_id)::text) Buffers: shared hit=752817 -> Merge Left Join (cost=164030.78..164035.43 rows=230 width=146) (actual time=2398.463..2406.072 rows=3 loops=1) Merge Cond: ((ps.user_id)::text = (sr.sitter_id)::text) Buffers: shared hit=752816 -> Merge Left Join (cost=114472.54..114476.60 rows=230 width=122) (actual time=2187.057..2187.080 rows=3 loops=1) Merge Cond: ((ps.user_id)::text = (sf.sitter_id)::text) Buffers: shared hit=723219 -> Merge Left Join (cost=53743.37..53746.84 rows=230 width=114) (actual time=689.450..689.463 rows=3 loops=1) Merge Cond: ((ps.user_id)::text = (ss.sitter_id)::text) Filter: (COALESCE(ss.total_bookings, '0'::bigint) > 0) Rows Removed by Filter: 3 Buffers: shared hit=27028 -> Sort (cost=49.44..51.16 rows=690 width=90) (actual time=0.017..0.021 rows=6 loops=1) Sort Key: ps.user_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=1 -> Seq Scan on pet_sitters ps (cost=0.00..16.90 rows=690 width=90) (actual time=0.007..0.008 rows=6 loops=1) Buffers: shared hit=1 -> Sort (cost=53693.93..53693.94 rows=1 width=61) (actual time=689.401..689.404 rows=3 loops=1) Sort Key: ss.sitter_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=27027 -> Subquery Scan on ss (cost=53693.90..53693.92 rows=1 width=61) (actual time=689.386..689.391 rows=3 loops=1) Buffers: shared hit=27027 -> HashAggregate (cost=53693.90..53693.91 rows=1 width=61) (actual time=689.380..689.384 rows=3 loops=1) Group Key: b.sitter_id Batches: 1 Memory Usage: 24kB Buffers: shared hit=27027 -> Nested Loop (cost=0.00..52027.22 rows=111112 width=82) (actual time=0.035..322.377 rows=1000004 loops=1) Join Filter: ((b.date_from >= p.start_date) AND (b.date_from < p.end_date)) Rows Removed by Join Filter: 4 Buffers: shared hit=27027 -> CTE Scan on params p (cost=0.00..0.02 rows=1 width=8) (actual time=0.013..0.016 rows=1 loops=1) -> Seq Scan on bookings b (cost=0.00..37027.08 rows=1000008 width=86) (actual time=0.008..87.323 rows=1000008 loops=1) Buffers: shared hit=27027 -> Sort (cost=60729.17..60729.18 rows=1 width=45) (actual time=1497.586..1497.591 rows=3 loops=1) Sort Key: sf.sitter_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=696191 -> Subquery Scan on sf (cost=60729.14..60729.16 rows=1 width=45) (actual time=1497.566..1497.572 rows=3 loops=1) Buffers: shared hit=696191 -> HashAggregate (cost=60729.14..60729.15 rows=1 width=45) (actual time=1497.560..1497.564 rows=3 loops=1) Group Key: b_1.sitter_id Batches: 1 Memory Usage: 24kB Buffers: shared hit=696191 -> Nested Loop (cost=0.42..60638.55 rows=18119 width=41) (actual time=0.105..1448.561 rows=167291 loops=1) Buffers: shared hit=696191 -> Nested Loop (cost=0.00..41973.14 rows=18119 width=74) (actual time=0.037..226.674 rows=167291 loops=1) Join Filter: ((b_1.date_from >= p_1.start_date) AND (b_1.date_from < p_1.end_date)) Buffers: shared hit=27027 -> CTE Scan on params p_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1) -> Seq Scan on bookings b_1 (cost=0.00..39527.10 rows=163068 width=78) (actual time=0.023..187.662 rows=167291 loops=1) Filter: ((status)::text = 'Completed'::text) Rows Removed by Filter: 832717 Buffers: shared hit=27027 -> Index Scan using payments_booking_id_key on payments pay (cost=0.42..1.03 rows=1 width=41) (actual time=0.007..0.007 rows=1 loops=167291) Index Cond: ((booking_id)::text = (b_1.booking_id)::text) Buffers: shared hit=669164 -> Sort (cost=49558.24..49558.24 rows=1 width=61) (actual time=211.385..218.968 rows=3 loops=1) Sort Key: sr.sitter_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=29597 -> Subquery Scan on sr (cost=49558.20..49558.23 rows=1 width=61) (actual time=211.362..218.949 rows=3 loops=1) Buffers: shared hit=29597 -> HashAggregate (cost=49558.20..49558.22 rows=1 width=61) (actual time=211.355..218.941 rows=3 loops=1) Group Key: b_2.sitter_id Batches: 1 Memory Usage: 24kB Buffers: shared hit=29597 -> Nested Loop (cost=4929.82..49460.87 rows=12977 width=78) (actual time=64.080..186.743 rows=116794 loops=1) Join Filter: ((b_2.date_from >= p_2.start_date) AND (b_2.date_from < p_2.end_date)) Rows Removed by Join Filter: 1 Buffers: shared hit=29597 -> CTE Scan on params p_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1) -> Gather (cost=4929.82..47708.93 rows=116795 width=82) (actual time=64.066..167.308 rows=116795 loops=1) Workers Planned: 3 Workers Launched: 3 Buffers: shared hit=29597 -> Parallel Hash Join (cost=3929.82..35029.43 rows=37676 width=82) (actual time=35.327..157.583 rows=29199 loops=4) Hash Cond: ((b_2.booking_id)::text = (r.booking_id)::text) Buffers: shared hit=29597 -> Parallel Seq Scan on bookings b_2 (cost=0.00..30252.83 rows=322583 width=78) (actual time=0.013..32.216 rows=250002 loops=4) Buffers: shared hit=27027 -> Parallel Hash (cost=3071.03..3071.03 rows=68703 width=78) (actual time=34.538..34.539 rows=29199 loops=4) Buckets: 131072 Batches: 1 Memory Usage: 13888kB Buffers: shared hit=2384 -> Parallel Seq Scan on reviews r (cost=0.00..3071.03 rows=68703 width=78) (actual time=8.890..15.815 rows=29199 loops=4) Buffers: shared hit=2384 -> Hash (cost=10.40..10.40 rows=40 width=696) (actual time=0.044..0.045 rows=14 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB Buffers: shared hit=1 -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=696) (actual time=0.027..0.031 rows=14 loops=1) Buffers: shared hit=1 Planning: Buffers: shared hit=32 Planning Time: 1.520 ms JIT: Functions: 114 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 7.398 ms (Deform 3.509 ms), Inlining 0.000 ms, Optimization 3.331 ms, Emission 83.328 ms, Total 94.056 ms Execution Time: 2461.823 ms }}} '''Execution time:''' 2461.823 ms We attempt to optimize this analytical query by introducing multiple foreign key indexes: {{{ #!sql CREATE INDEX idx_bookings_date_sitter ON project.bookings (date_from, sitter_id); CREATE INDEX idx_bookings_date_owner ON project.bookings (date_from, owner_id); CREATE INDEX idx_payments_booking_id ON project.payments (booking_id); CREATE INDEX idx_bookings_service_id ON project.bookings (service_id); CREATE INDEX idx_pets_owner_id ON project.pets (owner_id); }}} Execution with indexes: {{{ Limit (cost=124470.23..124470.26 rows=10 width=808) (actual time=3543.349..3543.370 rows=3 loops=1) Buffers: shared hit=747209 read=8095, temp read=6446 written=6446 CTE params -> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1) -> Sort (cost=124470.21..124470.79 rows=230 width=808) (actual time=3493.039..3493.057 rows=3 loops=1) Sort Key: (dense_rank() OVER (?)) Sort Method: quicksort Memory: 25kB Buffers: shared hit=747209 read=8095, temp read=6446 written=6446 -> WindowAgg (cost=124455.49..124465.24 rows=230 width=808) (actual time=3493.003..3493.034 rows=3 loops=1) Buffers: shared hit=747209 read=8095, temp read=6446 written=6446 -> Sort (cost=124455.47..124456.04 rows=230 width=784) (actual time=3492.986..3493.004 rows=3 loops=1) 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 Sort Method: quicksort Memory: 25kB Buffers: shared hit=747209 read=8095, temp read=6446 written=6446 -> Hash Join (cost=124435.44..124446.45 rows=230 width=784) (actual time=3492.954..3492.993 rows=3 loops=1) Hash Cond: ((ps.user_id)::text = (u.user_id)::text) Buffers: shared hit=747209 read=8095, temp read=6446 written=6446 -> Merge Left Join (cost=124424.54..124429.19 rows=230 width=146) (actual time=3492.879..3492.911 rows=3 loops=1) Merge Cond: ((ps.user_id)::text = (sr.sitter_id)::text) Buffers: shared hit=747208 read=8095, temp read=6446 written=6446 -> Merge Left Join (cost=81887.48..81891.54 rows=230 width=122) (actual time=2431.288..2431.313 rows=3 loops=1) Merge Cond: ((ps.user_id)::text = (sf.sitter_id)::text) Buffers: shared hit=716906 read=8095 -> Merge Left Join (cost=33064.29..33067.76 rows=230 width=114) (actual time=773.072..773.086 rows=3 loops=1) Merge Cond: ((ps.user_id)::text = (ss.sitter_id)::text) Filter: (COALESCE(ss.total_bookings, '0'::bigint) > 0) Rows Removed by Filter: 3 Buffers: shared hit=27028 read=891 -> Sort (cost=49.44..51.16 rows=690 width=90) (actual time=0.018..0.022 rows=6 loops=1) Sort Key: ps.user_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=1 -> Seq Scan on pet_sitters ps (cost=0.00..16.90 rows=690 width=90) (actual time=0.007..0.008 rows=6 loops=1) Buffers: shared hit=1 -> Sort (cost=33014.85..33014.86 rows=1 width=61) (actual time=773.022..773.026 rows=3 loops=1) Sort Key: ss.sitter_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=27027 read=891 -> Subquery Scan on ss (cost=33014.82..33014.84 rows=1 width=61) (actual time=773.007..773.012 rows=3 loops=1) Buffers: shared hit=27027 read=891 -> HashAggregate (cost=33014.82..33014.83 rows=1 width=61) (actual time=773.000..773.004 rows=3 loops=1) Group Key: b.sitter_id Batches: 1 Memory Usage: 24kB Buffers: shared hit=27027 read=891 -> Nested Loop (cost=1543.32..31348.14 rows=111112 width=83) (actual time=51.171..407.886 rows=1000004 loops=1) Buffers: shared hit=27027 read=891 -> CTE Scan on params p (cost=0.00..0.02 rows=1 width=8) (actual time=0.013..0.015 rows=1 loops=1) -> Bitmap Heap Scan on bookings b (cost=1543.32..30237.00 rows=111112 width=87) (actual time=51.137..216.994 rows=1000004 loops=1) Recheck Cond: ((date_from >= p.start_date) AND (date_from < p.end_date)) Heap Blocks: exact=27027 Buffers: shared hit=27027 read=891 -> Bitmap Index Scan on idx_bookings_date_owner (cost=0.00..1515.55 rows=111112 width=0) (actual time=45.474..45.474 rows=1000004 loops=1) Index Cond: ((date_from >= p.start_date) AND (date_from < p.end_date)) Buffers: shared read=891 -> Sort (cost=48823.19..48823.19 rows=1 width=45) (actual time=1658.196..1658.201 rows=3 loops=1) Sort Key: sf.sitter_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=689878 read=7204 -> Subquery Scan on sf (cost=48823.16..48823.18 rows=1 width=45) (actual time=1658.175..1658.182 rows=3 loops=1) Buffers: shared hit=689878 read=7204 -> HashAggregate (cost=48823.16..48823.17 rows=1 width=45) (actual time=1658.168..1658.173 rows=3 loops=1) Group Key: b_1.sitter_id Batches: 1 Memory Usage: 24kB Buffers: shared hit=689878 read=7204 -> Nested Loop (cost=1520.68..48728.99 rows=18833 width=41) (actual time=42.283..1607.525 rows=167291 loops=1) Buffers: shared hit=689878 read=7204 -> Nested Loop (cost=1520.25..30680.06 rows=18833 width=74) (actual time=42.160..304.743 rows=167291 loops=1) Buffers: shared hit=27918 -> 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) -> Bitmap Heap Scan on bookings b_1 (cost=1520.25..30491.71 rows=18833 width=78) (actual time=42.148..272.927 rows=167291 loops=1) Recheck Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date)) Filter: ((status)::text = 'Completed'::text) Rows Removed by Filter: 832713 Heap Blocks: exact=27027 Buffers: shared hit=27918 -> Bitmap Index Scan on idx_bookings_date_owner (cost=0.00..1515.55 rows=111112 width=0) (actual time=36.497..36.497 rows=1000004 loops=1) Index Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date)) Buffers: shared hit=891 -> Index Scan using idx_payments_booking_id on payments pay (cost=0.42..0.96 rows=1 width=41) (actual time=0.007..0.007 rows=1 loops=167291) Index Cond: ((booking_id)::text = (b_1.booking_id)::text) Buffers: shared hit=661960 read=7204 -> Sort (cost=42537.06..42537.07 rows=1 width=61) (actual time=1061.568..1061.573 rows=3 loops=1) Sort Key: sr.sitter_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=30302, temp read=6446 written=6446 -> Subquery Scan on sr (cost=42537.03..42537.05 rows=1 width=61) (actual time=1061.543..1061.552 rows=3 loops=1) Buffers: shared hit=30302, temp read=6446 written=6446 -> HashAggregate (cost=42537.03..42537.04 rows=1 width=61) (actual time=1061.536..1061.544 rows=3 loops=1) Group Key: b_2.sitter_id Batches: 1 Memory Usage: 24kB Buffers: shared hit=30302, temp read=6446 written=6446 -> Hash Join (cost=8038.21..42439.70 rows=12977 width=78) (actual time=128.073..1025.179 rows=116794 loops=1) Hash Cond: ((b_2.booking_id)::text = (r.booking_id)::text) Buffers: shared hit=30302, temp read=6446 written=6446 -> Nested Loop (cost=1543.32..31348.14 rows=111112 width=74) (actual time=42.544..438.183 rows=1000004 loops=1) Buffers: shared hit=27918 -> 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) -> Bitmap Heap Scan on bookings b_2 (cost=1543.32..30237.00 rows=111112 width=78) (actual time=42.521..231.784 rows=1000004 loops=1) Recheck Cond: ((date_from >= p_2.start_date) AND (date_from < p_2.end_date)) Heap Blocks: exact=27027 Buffers: shared hit=27918 -> Bitmap Index Scan on idx_bookings_date_owner (cost=0.00..1515.55 rows=111112 width=0) (actual time=36.900..36.900 rows=1000004 loops=1) Index Cond: ((date_from >= p_2.start_date) AND (date_from < p_2.end_date)) Buffers: shared hit=891 -> Hash (cost=3551.95..3551.95 rows=116795 width=78) (actual time=85.342..85.343 rows=116795 loops=1) Buckets: 131072 Batches: 2 Memory Usage: 7256kB Buffers: shared hit=2384, temp written=703 -> Seq Scan on reviews r (cost=0.00..3551.95 rows=116795 width=78) (actual time=0.028..31.128 rows=116795 loops=1) Buffers: shared hit=2384 -> Hash (cost=10.40..10.40 rows=40 width=696) (actual time=0.044..0.045 rows=14 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB Buffers: shared hit=1 -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=696) (actual time=0.027..0.030 rows=14 loops=1) Buffers: shared hit=1 Planning: Buffers: shared hit=148 read=9 Planning Time: 2.643 ms JIT: Functions: 80 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 5.241 ms (Deform 2.016 ms), Inlining 0.000 ms, Optimization 1.677 ms, Emission 48.982 ms, Total 55.900 ms Execution Time: 3549.793 ms }}} '''Execution time:''' 3549.793 ms Instead of improving performance, adding these indexes actually '''increased''' the execution time by more than 1 second. The indexes are also evaluated in Scenario 4 below. === 4. Highest Paying Customers Analytics (Phase 6 Query) === Benchmark query: {{{ #!sql EXPLAIN (ANALYZE, BUFFERS) WITH params AS ( SELECT (CURRENT_DATE - INTERVAL '1 year')::DATE AS start_date, CURRENT_DATE::DATE AS end_date ), owner_reports AS ( SELECT b.owner_id, SUM(pay.amount) AS total_profit_generated, COUNT(DISTINCT b.booking_id) AS successful_bookings FROM project.bookings b JOIN project.payments pay ON b.booking_id = pay.booking_id JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date WHERE b.status = 'Completed' GROUP BY b.owner_id ), service_counts AS ( SELECT b.owner_id, s.type AS service_type, COUNT(b.service_id) AS times_booked, ROW_NUMBER() OVER(PARTITION BY b.owner_id ORDER BY COUNT(b.service_id) DESC) as rank_num FROM project.bookings b JOIN project.services s ON b.service_id = s.service_id JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date GROUP BY b.owner_id, s.type ), favorite_service AS ( SELECT owner_id, service_type AS top_interest FROM service_counts WHERE rank_num = 1 ), pet_portfolio AS ( SELECT owner_id, COUNT(pet_id) AS registered_pets FROM project.pets GROUP BY owner_id ) SELECT u.user_id, u.first_name, u.last_name, COALESCE(ofin.successful_bookings, 0) AS successful_bookings, COALESCE(pp.registered_pets, 0) AS total_pets, COALESCE(fs.top_interest, 'Unknown') AS top_interest, COALESCE(ofin.total_profit_generated, 0) AS total_profit_generated, DENSE_RANK() OVER ( ORDER BY COALESCE(ofin.total_profit_generated, 0) DESC, COALESCE(ofin.successful_bookings, 0) DESC ) AS customer_rank FROM project.users u JOIN project.pet_owners po ON u.user_id = po.user_id LEFT JOIN owner_reports ofin ON po.user_id = ofin.owner_id LEFT JOIN favorite_service fs ON po.user_id = fs.owner_id LEFT JOIN pet_portfolio pp ON po.user_id = pp.owner_id WHERE COALESCE(ofin.successful_bookings, 0) > 0 ORDER BY customer_rank LIMIT 10; }}} Execution without indexes: {{{ Limit (cost=115376.54..115376.56 rows=10 width=702) (actual time=3272.796..3272.811 rows=3 loops=1) Buffers: shared hit=723223, temp read=1865 written=1870 CTE params -> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1) -> Sort (cost=115376.52..115377.09 rows=230 width=702) (actual time=3225.301..3225.314 rows=3 loops=1) Sort Key: (dense_rank() OVER (?)) Sort Method: quicksort Memory: 25kB Buffers: shared hit=723223, temp read=1865 written=1870 -> WindowAgg (cost=115366.97..115371.55 rows=230 width=702) (actual time=3225.271..3225.294 rows=3 loops=1) Buffers: shared hit=723223, temp read=1865 written=1870 -> Sort (cost=115366.95..115367.52 rows=230 width=936) (actual time=3225.246..3225.259 rows=3 loops=1) Sort Key: (COALESCE((sum(pay.amount)), '0'::bigint)) DESC, (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint)) DESC Sort Method: quicksort Memory: 25kB Buffers: shared hit=723223, temp read=1865 written=1870 -> Hash Join (cost=115165.28..115357.93 rows=230 width=936) (actual time=3014.108..3225.250 rows=3 loops=1) Hash Cond: ((po.user_id)::text = (u.user_id)::text) Buffers: shared hit=723223, temp read=1865 written=1870 -> Merge Left Join (cost=115154.38..115346.42 rows=230 width=388) (actual time=3014.042..3225.179 rows=3 loops=1) Merge Cond: ((po.user_id)::text = (pp.owner_id)::text) Buffers: shared hit=723222, temp read=1865 written=1870 -> Merge Left Join (cost=115151.59..115343.02 rows=230 width=380) (actual time=3013.930..3225.062 rows=3 loops=1) Merge Cond: ((po.user_id)::text = (service_counts.owner_id)::text) Buffers: shared hit=723220, temp read=1865 written=1870 -> Merge Left Join (cost=61969.47..62154.15 rows=230 width=106) (actual time=2010.978..2222.079 rows=3 loops=1) Merge Cond: ((po.user_id)::text = (b.owner_id)::text) Filter: (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint) > 0) Rows Removed by Filter: 3 Buffers: shared hit=696192, temp read=1865 written=1870 -> Sort (cost=49.44..51.16 rows=690 width=90) (actual time=0.018..0.021 rows=6 loops=1) Sort Key: po.user_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=1 -> Seq Scan on pet_owners po (cost=0.00..16.90 rows=690 width=90) (actual time=0.007..0.008 rows=6 loops=1) Buffers: shared hit=1 -> Materialize (cost=61920.03..62101.25 rows=1 width=53) (actual time=2010.929..2222.022 rows=3 loops=1) Buffers: shared hit=696191, temp read=1865 written=1870 -> GroupAggregate (cost=61920.03..62101.23 rows=1 width=53) (actual time=2010.925..2222.015 rows=3 loops=1) Group Key: b.owner_id Buffers: shared hit=696191, temp read=1865 written=1870 -> Sort (cost=61920.03..61965.33 rows=18119 width=78) (actual time=2010.866..2182.374 rows=167291 loops=1) Sort Key: b.owner_id, b.booking_id Sort Method: external merge Disk: 14920kB Buffers: shared hit=696191, temp read=1865 written=1870 -> Nested Loop (cost=0.42..60638.55 rows=18119 width=78) (actual time=0.072..1416.845 rows=167291 loops=1) Buffers: shared hit=696191 -> Nested Loop (cost=0.00..41973.14 rows=18119 width=74) (actual time=0.039..218.948 rows=167291 loops=1) Join Filter: ((b.date_from >= p.start_date) AND (b.date_from < p.end_date)) Buffers: shared hit=27027 -> CTE Scan on params p (cost=0.00..0.02 rows=1 width=8) (actual time=0.013..0.015 rows=1 loops=1) -> Seq Scan on bookings b (cost=0.00..39527.10 rows=163068 width=78) (actual time=0.015..183.349 rows=167291 loops=1) Filter: ((status)::text = 'Completed'::text) Rows Removed by Filter: 832717 Buffers: shared hit=27027 -> Index Scan using payments_booking_id_key on payments pay (cost=0.42..1.03 rows=1 width=41) (actual time=0.007..0.007 rows=1 loops=167291) Index Cond: ((booking_id)::text = (b.booking_id)::text) Buffers: shared hit=669164 -> Materialize (cost=53182.12..53188.28 rows=1 width=311) (actual time=1002.931..1002.956 rows=3 loops=1) Buffers: shared hit=27028 -> Subquery Scan on service_counts (cost=53182.12..53188.28 rows=1 width=311) (actual time=1002.926..1002.947 rows=3 loops=1) Filter: (service_counts.rank_num = 1) Buffers: shared hit=27028 -> WindowAgg (cost=53182.12..53185.90 rows=190 width=327) (actual time=1002.919..1002.938 rows=3 loops=1) Run Condition: (row_number() OVER (?) <= 1) Buffers: shared hit=27028 -> Sort (cost=53182.10..53182.58 rows=190 width=319) (actual time=1002.896..1002.900 rows=4 loops=1) Sort Key: b_1.owner_id, (count(b_1.service_id)) DESC Sort Method: quicksort Memory: 25kB Buffers: shared hit=27028 -> HashAggregate (cost=53173.01..53174.91 rows=190 width=319) (actual time=1002.872..1002.878 rows=4 loops=1) Group Key: b_1.owner_id, s.type Batches: 1 Memory Usage: 40kB Buffers: shared hit=27028 -> Hash Join (cost=14.28..52339.67 rows=111112 width=348) (actual time=0.092..643.148 rows=1000004 loops=1) Hash Cond: ((b_1.service_id)::text = (s.service_id)::text) Buffers: shared hit=27028 -> Nested Loop (cost=0.00..52027.22 rows=111112 width=74) (actual time=0.034..342.830 rows=1000004 loops=1) Join Filter: ((b_1.date_from >= p_1.start_date) AND (b_1.date_from < p_1.end_date)) Rows Removed by Join Filter: 4 Buffers: shared hit=27027 -> 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) -> Seq Scan on bookings b_1 (cost=0.00..37027.08 rows=1000008 width=78) (actual time=0.014..88.185 rows=1000008 loops=1) Buffers: shared hit=27027 -> Hash (cost=11.90..11.90 rows=190 width=364) (actual time=0.029..0.030 rows=4 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=1 -> Seq Scan on services s (cost=0.00..11.90 rows=190 width=364) (actual time=0.017..0.019 rows=4 loops=1) Buffers: shared hit=1 -> Sort (cost=2.78..2.80 rows=6 width=45) (actual time=0.091..0.093 rows=6 loops=1) Sort Key: pp.owner_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=2 -> Subquery Scan on pp (cost=2.59..2.71 rows=6 width=45) (actual time=0.071..0.074 rows=6 loops=1) Buffers: shared hit=2 -> HashAggregate (cost=2.59..2.65 rows=6 width=45) (actual time=0.066..0.067 rows=6 loops=1) Group Key: pets.owner_id Batches: 1 Memory Usage: 24kB Buffers: shared hit=2 -> Seq Scan on pets (cost=0.00..2.39 rows=39 width=74) (actual time=0.018..0.023 rows=39 loops=1) Buffers: shared hit=2 -> Hash (cost=10.40..10.40 rows=40 width=638) (actual time=0.043..0.044 rows=14 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB Buffers: shared hit=1 -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=638) (actual time=0.027..0.030 rows=14 loops=1) Buffers: shared hit=1 Planning: Buffers: shared hit=16 Planning Time: 1.344 ms JIT: Functions: 79 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 4.177 ms (Deform 1.933 ms), Inlining 0.000 ms, Optimization 1.555 ms, Emission 46.200 ms, Total 51.932 ms Execution Time: 3280.637 ms }}} '''Execution time:''' 3280.637 ms Execution with the indexes we created in Scenario 3: {{{ Limit (cost=82850.79..82850.82 rows=10 width=702) (actual time=3399.874..3399.891 rows=3 loops=1) Buffers: shared hit=725005, temp read=1865 written=1870 CTE params -> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1) -> Sort (cost=82850.77..82851.35 rows=230 width=702) (actual time=3399.872..3399.888 rows=3 loops=1) Sort Key: (dense_rank() OVER (?)) Sort Method: quicksort Memory: 25kB Buffers: shared hit=725005, temp read=1865 written=1870 -> WindowAgg (cost=82841.22..82845.80 rows=230 width=702) (actual time=3399.860..3399.880 rows=3 loops=1) Buffers: shared hit=725005, temp read=1865 written=1870 -> Sort (cost=82841.20..82841.78 rows=230 width=936) (actual time=3399.854..3399.869 rows=3 loops=1) Sort Key: (COALESCE((sum(pay.amount)), '0'::bigint)) DESC, (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint)) DESC Sort Method: quicksort Memory: 25kB Buffers: shared hit=725005, temp read=1865 written=1870 -> Hash Join (cost=82632.39..82832.18 rows=230 width=936) (actual time=3190.126..3399.860 rows=3 loops=1) Hash Cond: ((po.user_id)::text = (u.user_id)::text) Buffers: shared hit=725005, temp read=1865 written=1870 -> Merge Left Join (cost=82621.49..82820.68 rows=230 width=388) (actual time=3190.090..3399.819 rows=3 loops=1) Merge Cond: ((po.user_id)::text = (pp.owner_id)::text) Buffers: shared hit=725004, temp read=1865 written=1870 -> Merge Left Join (cost=82618.71..82817.27 rows=230 width=380) (actual time=3190.017..3399.739 rows=3 loops=1) Merge Cond: ((po.user_id)::text = (service_counts.owner_id)::text) Buffers: shared hit=725002, temp read=1865 written=1870 -> Merge Left Join (cost=50115.66..50307.48 rows=230 width=106) (actual time=2121.267..2330.955 rows=3 loops=1) Merge Cond: ((po.user_id)::text = (b.owner_id)::text) Filter: (COALESCE((count(DISTINCT b.booking_id)), '0'::bigint) > 0) Rows Removed by Filter: 3 Buffers: shared hit=697083, temp read=1865 written=1870 -> Sort (cost=49.44..51.16 rows=690 width=90) (actual time=0.019..0.022 rows=6 loops=1) Sort Key: po.user_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=1 -> Seq Scan on pet_owners po (cost=0.00..16.90 rows=690 width=90) (actual time=0.007..0.008 rows=6 loops=1) Buffers: shared hit=1 -> Materialize (cost=50066.23..50254.58 rows=1 width=53) (actual time=2121.239..2330.916 rows=3 loops=1) Buffers: shared hit=697082, temp read=1865 written=1870 -> GroupAggregate (cost=50066.23..50254.57 rows=1 width=53) (actual time=2121.236..2330.908 rows=3 loops=1) Group Key: b.owner_id Buffers: shared hit=697082, temp read=1865 written=1870 -> Sort (cost=50066.23..50113.31 rows=18833 width=78) (actual time=2121.221..2290.350 rows=167291 loops=1) Sort Key: b.owner_id, b.booking_id Sort Method: external merge Disk: 14920kB Buffers: shared hit=697082, temp read=1865 written=1870 -> Nested Loop (cost=1520.68..48728.99 rows=18833 width=78) (actual time=41.189..1516.673 rows=167291 loops=1) Buffers: shared hit=697082 -> Nested Loop (cost=1520.25..30680.06 rows=18833 width=74) (actual time=41.169..319.669 rows=167291 loops=1) Buffers: shared hit=27918 -> CTE Scan on params p (cost=0.00..0.02 rows=1 width=8) (actual time=0.008..0.010 rows=1 loops=1) -> Bitmap Heap Scan on bookings b (cost=1520.25..30491.71 rows=18833 width=78) (actual time=41.158..291.072 rows=167291 loops=1) Recheck Cond: ((date_from >= p.start_date) AND (date_from < p.end_date)) Filter: ((status)::text = 'Completed'::text) Rows Removed by Filter: 832713 Heap Blocks: exact=27027 Buffers: shared hit=27918 -> Bitmap Index Scan on idx_bookings_date_owner (cost=0.00..1515.55 rows=111112 width=0) (actual time=35.562..35.562 rows=1000004 loops=1) Index Cond: ((date_from >= p.start_date) AND (date_from < p.end_date)) Buffers: shared hit=891 -> Index Scan using idx_payments_booking_id on payments pay (cost=0.42..0.96 rows=1 width=41) (actual time=0.007..0.007 rows=1 loops=167291) Index Cond: ((booking_id)::text = (b.booking_id)::text) Buffers: shared hit=669164 -> Materialize (cost=32503.05..32509.20 rows=1 width=311) (actual time=1068.745..1068.772 rows=3 loops=1) Buffers: shared hit=27919 -> Subquery Scan on service_counts (cost=32503.05..32509.20 rows=1 width=311) (actual time=1068.741..1068.766 rows=3 loops=1) Filter: (service_counts.rank_num = 1) Buffers: shared hit=27919 -> WindowAgg (cost=32503.05..32506.83 rows=190 width=327) (actual time=1068.740..1068.762 rows=3 loops=1) Run Condition: (row_number() OVER (?) <= 1) Buffers: shared hit=27919 -> Sort (cost=32503.03..32503.50 rows=190 width=319) (actual time=1068.729..1068.735 rows=4 loops=1) Sort Key: b_1.owner_id, (count(b_1.service_id)) DESC Sort Method: quicksort Memory: 25kB Buffers: shared hit=27919 -> HashAggregate (cost=32493.94..32495.84 rows=190 width=319) (actual time=1068.705..1068.712 rows=4 loops=1) Group Key: b_1.owner_id, s.type Batches: 1 Memory Usage: 40kB Buffers: shared hit=27919 -> Hash Join (cost=1557.60..31660.60 rows=111112 width=348) (actual time=42.429..729.260 rows=1000004 loops=1) Hash Cond: ((b_1.service_id)::text = (s.service_id)::text) Buffers: shared hit=27919 -> Nested Loop (cost=1543.32..31348.14 rows=111112 width=74) (actual time=42.390..408.282 rows=1000004 loops=1) Buffers: shared hit=27918 -> 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) -> Bitmap Heap Scan on bookings b_1 (cost=1543.32..30237.00 rows=111112 width=78) (actual time=42.384..209.378 rows=1000004 loops=1) Recheck Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date)) Heap Blocks: exact=27027 Buffers: shared hit=27918 -> Bitmap Index Scan on idx_bookings_date_owner (cost=0.00..1515.55 rows=111112 width=0) (actual time=36.836..36.836 rows=1000004 loops=1) Index Cond: ((date_from >= p_1.start_date) AND (date_from < p_1.end_date)) Buffers: shared hit=891 -> Hash (cost=11.90..11.90 rows=190 width=364) (actual time=0.025..0.026 rows=4 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=1 -> Seq Scan on services s (cost=0.00..11.90 rows=190 width=364) (actual time=0.019..0.020 rows=4 loops=1) Buffers: shared hit=1 -> Sort (cost=2.78..2.80 rows=6 width=45) (actual time=0.068..0.071 rows=6 loops=1) Sort Key: pp.owner_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=2 -> Subquery Scan on pp (cost=2.59..2.71 rows=6 width=45) (actual time=0.049..0.053 rows=6 loops=1) Buffers: shared hit=2 -> HashAggregate (cost=2.59..2.65 rows=6 width=45) (actual time=0.049..0.051 rows=6 loops=1) Group Key: pets.owner_id Batches: 1 Memory Usage: 24kB Buffers: shared hit=2 -> Seq Scan on pets (cost=0.00..2.39 rows=39 width=74) (actual time=0.021..0.025 rows=39 loops=1) Buffers: shared hit=2 -> Hash (cost=10.40..10.40 rows=40 width=638) (actual time=0.026..0.026 rows=14 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB Buffers: shared hit=1 -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=638) (actual time=0.013..0.017 rows=14 loops=1) Buffers: shared hit=1 Planning: Buffers: shared hit=44 Planning Time: 1.735 ms Execution Time: 3403.299 ms }}} '''Execution time:''' 3403.299 ms '''Conclusion:''' The execution time did not change much (3280.637 ms vs 3403.299 ms) Creating foreign key indexes did not have a significant effect on the query performance. 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: {{{ #!sql DROP INDEX project.idx_bookings_service_id; DROP INDEX project.idx_payments_booking_id; DROP INDEX project.idx_bookings_date_sitter; DROP INDEX project.idx_bookings_date_owner; DROP INDEX project.idx_pets_owner_id; }}} == Security == === 1. Password Security (BCrypt) === 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}}}. Implementation in {{{UserService.java}}}: {{{#!java private final BCryptPasswordEncoder passwordEncoder = new BCryptPasswordEncoder(); @Transactional(readOnly = true) public User authenticate(String username, String password) { User user = userRepository.findByUsername(username).orElse(null); if (user != null && passwordEncoder.matches(password, user.getPassword())) { return user; } return null; } @Transactional public User registerUser(String username, String password, String firstName, String lastName, String email, String role) { // ... newUser.setUsername(username); newUser.setPassword(passwordEncoder.encode(password)); newUser.setFirstName(firstName); // ... } }}} === 2. SQL Injection Prevention === 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. Example of safe repository usage in {{{UserService.java}}}: {{{#!java @Transactional(readOnly = true) public User authenticate(String username, String password) { User user = userRepository.findByUsername(username).orElse(null); // ... } }}} === 3. Role based access control (RBAC) === 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. Implementation example in {{{AdminController.java}}}: {{{#!java @GetMapping("/admin/users") public String showAllUsers(HttpSession session, Model model) { User user = (User) session.getAttribute("loggedInUser"); if (user == null || user instanceof PetOwner || user instanceof PetSitter) { return "redirect:/dashboard"; } List users = userRepository.findAll(); model.addAttribute("users", users); // ... return "admin-users"; } }}} ---- == Not applicable == During the security analysis phase, two common web security measures were evaluated but found to be inapplicable for our specific architecture: === JWTs === '''JSON Web Tokens''' are designed for Stateless REST APIs (for example, when using a completely separate React/Vue frontend). 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. === CORS === 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. == Version history == [https://develop.finki.ukim.mk/projects/petsitter/wiki/Performance_v01 v01] * Initial version [https://develop.finki.ukim.mk/projects/petsitter/wiki/Performance v02] * Current version, based on version [https://develop.finki.ukim.mk/projects/petsitter/wiki/ERModel v03] of the ER model * The mock data assigns the {{{service_id}}} directly inside the {{{INSERT}}} into {{{bookings}}}; the last/fifth step of the mock data that used the removed {{{booking_services}}} table is deleted * All {{{EXPLAIN (ANALYZE, BUFFERS)}}} queries were executed again on the corrected schema, and every output, measured time and average was updated * The {{{idx_booking_services_booking_id}}} index is replaced by {{{idx_bookings_service_id}}}, because the junction table it indexed does not exist * In Scenario 4 the benchmark query joins {{{services}}} directly on {{{b.service_id}}}, correlating with the updated report in [https://develop.finki.ukim.mk/projects/petsitter/wiki/AdvancedReports AdvancedReports]