#	exclusive	inclusive	rows x	rows	loops	node
1.	31.187	2092.669	↑ 1.0	1	1	→ Aggregate (cost=89582.94..89582.95 rows=1 width=0) (actual time=2092.669..2092.669 rows=1 loops=1) Buffers: shared hit=201 read=27306, temp read=7667 written=7577
2.	86.240	2061.482	↓ 1.0	275428	1	→ Hash Join (cost=63587.50..88920.86 rows=264833 width=0) (actual time=1469.199..2061.482 rows=275428 loops=1) Hash Cond: (cp.contestid = c.id) Buffers: shared hit=201 read=27306, temp read=7667 written=7577
3.	337.563	1974.463	↓ 1.0	275428	1	→ Hash Right Join (cost=63501.13..85193.04 rows=264833 width=8) (actual time=1468.402..1974.463 rows=275428 loops=1) Hash Cond: (tm.contestparticipantid = cp.id) Buffers: shared hit=139 read=27306, temp read=7667 written=7577
4.	168.712	168.712	↑ 1.0	479481	1	→ Seq Scan on teammember tm (cost=0.00..10428.81 rows=479481 width=8) (actual time=0.056..168.712 rows=479481 loops=1) Buffers: shared hit=34 read=5600
5.	67.111	1468.188	↓ 1.0	267278	1	→ Hash (cost=58896.72..58896.72 rows=264833 width=16) (actual time=1468.188..1468.188 rows=267278 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 796kB Buffers: shared hit=105 read=21706, temp read=4984 written=6018
6.	107.168	1401.077	↓ 1.0	267278	1	→ Hash Right Join (cost=56818.86..58896.72 rows=264833 width=16) (actual time=1282.725..1401.077 rows=267278 loops=1) Hash Cond: (sm.contestparticipantid = cp.id) Buffers: shared hit=105 read=21706, temp read=4984 written=4924
7.	11.419	11.419	↑ 1.0	16742	1	→ Seq Scan on staffmember sm (cost=0.00..476.42 rows=16742 width=8) (actual time=0.047..11.419 rows=16742 loops=1) Buffers: shared hit=34 read=275
8.	83.284	1282.490	↓ 1.0	267258	1	→ Hash (cost=52214.45..52214.45 rows=264833 width=16) (actual time=1282.490..1282.490 rows=267258 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 796kB Buffers: shared hit=71 read=21431, temp read=3786 written=4849
9.	441.616	1199.206	↓ 1.0	267258	1	→ Hash Join (cost=28690.03..52214.45 rows=264833 width=16) (actual time=606.796..1199.206 rows=267258 loops=1) Hash Cond: (cp.personinfoid = pi.id) Buffers: shared hit=71 read=21431, temp read=3786 written=3756
10.	151.736	151.736	↑ 1.0	483376	1	→ Seq Scan on contestparticipant cp (cost=0.00..9575.76 rows=483376 width=32) (actual time=0.015..151.736 rows=483376 loops=1) Buffers: shared hit=37 read=4705
11.	100.682	605.854	↓ 1.0	278282	1	→ Hash (cost=24219.33..24219.33 rows=272456 width=8) (actual time=605.854..605.854 rows=278282 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 689kB Buffers: shared hit=34 read=16726, temp written=883
12.	505.172	505.172	↓ 1.0	278282	1	→ Seq Scan on personinfo pi (cost=0.00..24219.33 rows=272456 width=8) (actual time=0.010..505.172 rows=278282 loops=1) Filter: (((lastname)::text ~~ '%b%'::text) OR ((firstname)::text ~~ '%a%'::text)) Rows Removed by Filter: 219007 Buffers: shared hit=34 read=16726
13.	0.411	0.779	↑ 1.0	1083	1	→ Hash (cost=72.83..72.83 rows=1083 width=8) (actual time=0.779..0.779 rows=1083 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 43kB Buffers: shared hit=62
14.	0.368	0.368	↑ 1.0	1083	1	→ Seq Scan on contest c (cost=0.00..72.83 rows=1083 width=8) (actual time=0.007..0.368 rows=1083 loops=1) Buffers: shared hit=62

                                                                               QUERY PLAN                                                                                
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=89582.94..89582.95 rows=1 width=0) (actual time=2092.669..2092.669 rows=1 loops=1)
   Buffers: shared hit=201 read=27306, temp read=7667 written=7577
   ->  Hash Join  (cost=63587.50..88920.86 rows=264833 width=0) (actual time=1469.199..2061.482 rows=275428 loops=1)
         Hash Cond: (cp.contestid = c.id)
         Buffers: shared hit=201 read=27306, temp read=7667 written=7577
         ->  Hash Right Join  (cost=63501.13..85193.04 rows=264833 width=8) (actual time=1468.402..1974.463 rows=275428 loops=1)
               Hash Cond: (tm.contestparticipantid = cp.id)
               Buffers: shared hit=139 read=27306, temp read=7667 written=7577
               ->  Seq Scan on teammember tm  (cost=0.00..10428.81 rows=479481 width=8) (actual time=0.056..168.712 rows=479481 loops=1)
                     Buffers: shared hit=34 read=5600
               ->  Hash  (cost=58896.72..58896.72 rows=264833 width=16) (actual time=1468.188..1468.188 rows=267278 loops=1)
                     Buckets: 4096  Batches: 16  Memory Usage: 796kB
                     Buffers: shared hit=105 read=21706, temp read=4984 written=6018
                     ->  Hash Right Join  (cost=56818.86..58896.72 rows=264833 width=16) (actual time=1282.725..1401.077 rows=267278 loops=1)
                           Hash Cond: (sm.contestparticipantid = cp.id)
                           Buffers: shared hit=105 read=21706, temp read=4984 written=4924
                           ->  Seq Scan on staffmember sm  (cost=0.00..476.42 rows=16742 width=8) (actual time=0.047..11.419 rows=16742 loops=1)
                                 Buffers: shared hit=34 read=275
                           ->  Hash  (cost=52214.45..52214.45 rows=264833 width=16) (actual time=1282.490..1282.490 rows=267258 loops=1)
                                 Buckets: 4096  Batches: 16  Memory Usage: 796kB
                                 Buffers: shared hit=71 read=21431, temp read=3786 written=4849
                                 ->  Hash Join  (cost=28690.03..52214.45 rows=264833 width=16) (actual time=606.796..1199.206 rows=267258 loops=1)
                                       Hash Cond: (cp.personinfoid = pi.id)
                                       Buffers: shared hit=71 read=21431, temp read=3786 written=3756
                                       ->  Seq Scan on contestparticipant cp  (cost=0.00..9575.76 rows=483376 width=32) (actual time=0.015..151.736 rows=483376 loops=1)
                                             Buffers: shared hit=37 read=4705
                                       ->  Hash  (cost=24219.33..24219.33 rows=272456 width=8) (actual time=605.854..605.854 rows=278282 loops=1)
                                             Buckets: 4096  Batches: 16  Memory Usage: 689kB
                                             Buffers: shared hit=34 read=16726, temp written=883
                                             ->  Seq Scan on personinfo pi  (cost=0.00..24219.33 rows=272456 width=8) (actual time=0.010..505.172 rows=278282 loops=1)
                                                   Filter: (((lastname)::text ~~ '%b%'::text) OR ((firstname)::text ~~ '%a%'::text))
                                                   Rows Removed by Filter: 219007
                                                   Buffers: shared hit=34 read=16726
         ->  Hash  (cost=72.83..72.83 rows=1083 width=8) (actual time=0.779..0.779 rows=1083 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 43kB
               Buffers: shared hit=62
               ->  Seq Scan on contest c  (cost=0.00..72.83 rows=1083 width=8) (actual time=0.007..0.368 rows=1083 loops=1)
                     Buffers: shared hit=62
 Total runtime: 2093.709 ms
(39 rows)

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	31.187 ms	1.5 %
Hash	4	251.488 ms	12.0 %
Hash Join	2	527.856 ms	25.2 %
Hash Right Join	2	444.731 ms	21.3 %
Seq Scan	5	837.407 ms	40.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
contest	1	0.368 ms	0.0 %
Seq Scan	1	0.368 ms	100.0 %
contestparticipant	1	151.736 ms	7.3 %
Seq Scan	1	151.736 ms	100.0 %
personinfo	1	505.172 ms	24.1 %
Seq Scan	1	505.172 ms	100.0 %
staffmember	1	11.419 ms	0.5 %
Seq Scan	1	11.419 ms	100.0 %
teammember	1	168.712 ms	8.1 %
Seq Scan	1	168.712 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: xaHJ

Colorize:

Columns visibility:

Per node type stats

Per table stats