#	exclusive	inclusive	rows x	rows	loops	node
1.	31.736	3330.558	↑ 1.0	1	1	→ Aggregate (cost=74247.68..74247.69 rows=1 width=0) (actual time=3330.558..3330.558 rows=1 loops=1) Buffers: shared hit=16771 read=10736
2.	90.280	3298.822	↓ 1.0	275428	1	→ Hash Join (cost=53292.23..73585.59 rows=264833 width=0) (actual time=2184.223..3298.822 rows=275428 loops=1) Hash Cond: (cp.contestid = c.id) Buffers: shared hit=16771 read=10736
3.	780.810	3205.449	↓ 1.0	275428	1	→ Hash Right Join (cost=53205.86..69857.77 rows=264833 width=8) (actual time=2181.089..3205.449 rows=275428 loops=1) Hash Cond: (tm.contestparticipantid = cp.id) Buffers: shared hit=16769 read=10676
4.	244.206	244.206	↑ 1.0	479481	1	→ Seq Scan on teammember tm (cost=0.00..10428.81 rows=479481 width=8) (actual time=0.031..244.206 rows=479481 loops=1) Buffers: shared hit=2 read=5632
5.	110.190	2180.433	↓ 1.0	267278	1	→ Hash (cost=49895.45..49895.45 rows=264833 width=16) (actual time=2180.433..2180.433 rows=267278 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 12529kB Buffers: shared hit=16767 read=5044
6.	123.887	2070.243	↓ 1.0	267278	1	→ Hash Right Join (cost=49201.74..49895.45 rows=264833 width=16) (actual time=1934.848..2070.243 rows=267278 loops=1) Hash Cond: (sm.contestparticipantid = cp.id) Buffers: shared hit=16767 read=5044
7.	12.161	12.161	↑ 1.0	16742	1	→ Seq Scan on staffmember sm (cost=0.00..476.42 rows=16742 width=8) (actual time=0.011..12.161 rows=16742 loops=1) Buffers: shared hit=2 read=307
8.	131.415	1934.195	↓ 1.0	267258	1	→ Hash (cost=45891.32..45891.32 rows=264833 width=16) (actual time=1934.195..1934.195 rows=267258 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 12528kB Buffers: shared hit=16765 read=4737
9.	768.713	1802.780	↓ 1.0	267258	1	→ Hash Join (cost=27625.03..45891.32 rows=264833 width=16) (actual time=812.538..1802.780 rows=267258 loops=1) Hash Cond: (cp.personinfoid = pi.id) Buffers: shared hit=16765 read=4737
10.	222.216	222.216	↑ 1.0	483376	1	→ Seq Scan on contestparticipant cp (cost=0.00..9575.76 rows=483376 width=32) (actual time=0.013..222.216 rows=483376 loops=1) Buffers: shared hit=5 read=4737
11.	134.340	811.851	↓ 1.0	278282	1	→ Hash (cost=24219.33..24219.33 rows=272456 width=8) (actual time=811.851..811.851 rows=278282 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 10871kB Buffers: shared hit=16760
12.	677.511	677.511	↓ 1.0	278282	1	→ Seq Scan on personinfo pi (cost=0.00..24219.33 rows=272456 width=8) (actual time=0.037..677.511 rows=278282 loops=1) Filter: (((lastname)::text ~~ '%b%'::text) OR ((firstname)::text ~~ '%a%'::text)) Rows Removed by Filter: 219007 Buffers: shared hit=16760
13.	0.504	3.093	↑ 1.0	1083	1	→ Hash (cost=72.83..72.83 rows=1083 width=8) (actual time=3.093..3.093 rows=1083 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 43kB Buffers: shared hit=2 read=60
14.	2.589	2.589	↑ 1.0	1083	1	→ Seq Scan on contest c (cost=0.00..72.83 rows=1083 width=8) (actual time=0.013..2.589 rows=1083 loops=1) Buffers: shared hit=2 read=60

                                                                               QUERY PLAN                                                                                
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=74247.68..74247.69 rows=1 width=0) (actual time=3330.558..3330.558 rows=1 loops=1)
   Buffers: shared hit=16771 read=10736
   ->  Hash Join  (cost=53292.23..73585.59 rows=264833 width=0) (actual time=2184.223..3298.822 rows=275428 loops=1)
         Hash Cond: (cp.contestid = c.id)
         Buffers: shared hit=16771 read=10736
         ->  Hash Right Join  (cost=53205.86..69857.77 rows=264833 width=8) (actual time=2181.089..3205.449 rows=275428 loops=1)
               Hash Cond: (tm.contestparticipantid = cp.id)
               Buffers: shared hit=16769 read=10676
               ->  Seq Scan on teammember tm  (cost=0.00..10428.81 rows=479481 width=8) (actual time=0.031..244.206 rows=479481 loops=1)
                     Buffers: shared hit=2 read=5632
               ->  Hash  (cost=49895.45..49895.45 rows=264833 width=16) (actual time=2180.433..2180.433 rows=267278 loops=1)
                     Buckets: 32768  Batches: 1  Memory Usage: 12529kB
                     Buffers: shared hit=16767 read=5044
                     ->  Hash Right Join  (cost=49201.74..49895.45 rows=264833 width=16) (actual time=1934.848..2070.243 rows=267278 loops=1)
                           Hash Cond: (sm.contestparticipantid = cp.id)
                           Buffers: shared hit=16767 read=5044
                           ->  Seq Scan on staffmember sm  (cost=0.00..476.42 rows=16742 width=8) (actual time=0.011..12.161 rows=16742 loops=1)
                                 Buffers: shared hit=2 read=307
                           ->  Hash  (cost=45891.32..45891.32 rows=264833 width=16) (actual time=1934.195..1934.195 rows=267258 loops=1)
                                 Buckets: 32768  Batches: 1  Memory Usage: 12528kB
                                 Buffers: shared hit=16765 read=4737
                                 ->  Hash Join  (cost=27625.03..45891.32 rows=264833 width=16) (actual time=812.538..1802.780 rows=267258 loops=1)
                                       Hash Cond: (cp.personinfoid = pi.id)
                                       Buffers: shared hit=16765 read=4737
                                       ->  Seq Scan on contestparticipant cp  (cost=0.00..9575.76 rows=483376 width=32) (actual time=0.013..222.216 rows=483376 loops=1)
                                             Buffers: shared hit=5 read=4737
                                       ->  Hash  (cost=24219.33..24219.33 rows=272456 width=8) (actual time=811.851..811.851 rows=278282 loops=1)
                                             Buckets: 32768  Batches: 1  Memory Usage: 10871kB
                                             Buffers: shared hit=16760
                                             ->  Seq Scan on personinfo pi  (cost=0.00..24219.33 rows=272456 width=8) (actual time=0.037..677.511 rows=278282 loops=1)
                                                   Filter: (((lastname)::text ~~ '%b%'::text) OR ((firstname)::text ~~ '%a%'::text))
                                                   Rows Removed by Filter: 219007
                                                   Buffers: shared hit=16760
         ->  Hash  (cost=72.83..72.83 rows=1083 width=8) (actual time=3.093..3.093 rows=1083 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 43kB
               Buffers: shared hit=2 read=60
               ->  Seq Scan on contest c  (cost=0.00..72.83 rows=1083 width=8) (actual time=0.013..2.589 rows=1083 loops=1)
                     Buffers: shared hit=2 read=60
 Total runtime: 3331.106 ms
(39 rows)

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	31.736 ms	1.0 %
Hash	4	376.449 ms	11.3 %
Hash Join	2	858.993 ms	25.8 %
Hash Right Join	2	904.697 ms	27.2 %
Seq Scan	5	1158.683 ms	34.8 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
contest	1	2.589 ms	0.1 %
Seq Scan	1	2.589 ms	100.0 %
contestparticipant	1	222.216 ms	6.7 %
Seq Scan	1	222.216 ms	100.0 %
personinfo	1	677.511 ms	20.3 %
Seq Scan	1	677.511 ms	100.0 %
staffmember	1	12.161 ms	0.4 %
Seq Scan	1	12.161 ms	100.0 %
teammember	1	244.206 ms	7.3 %
Seq Scan	1	244.206 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: Plk

Colorize:

Columns visibility:

Per node type stats

Per table stats