| Version 10 (modified by , 5 days ago) ( diff ) |
|---|
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.
-- 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;
-- 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
);
-- 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
);
-- 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:
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:
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)
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:
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:
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.
DROP INDEX IF EXISTS project.idx_reviews_booking_id;
3. Sitter Performance Analytics (Phase 6 Query)
Benchmark query:
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:
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:
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:
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:
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:
@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:
@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<User> 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
- Initial version
- Current version, based on version v03 of the ER model
- The mock data assigns the
service_iddirectly inside theINSERTintobookings; the last/fifth step of the mock data that used the removedbooking_servicestable 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_idindex is replaced byidx_bookings_service_id, because the junction table it indexed does not exist - In Scenario 4 the benchmark query joins
servicesdirectly onb.service_id, correlating with the updated report in AdvancedReports
Note:
See TracWiki
for help on using the wiki.
