#	exclusive	inclusive	rows x	rows	loops	node
1.	16.166	3282.245	↓ 156.8	31351	1	→ HashAggregate (cost=993409.91..993412.91 rows=200 width=32) (actual time=3276.080..3282.245 rows=31351 loops=1)
2.	77.641	3266.079	↑ 986.7	31351	1	→ Merge Join (cost=297220.97..916075.75 rows=30933664 width=32) (actual time=3111.085..3266.079 rows=31351 loops=1) Merge Cond: ((workareas.aspect)::integer = (grouprights.aspect)::integer)
3.	30.699	140.459	↓ 1.4	50809	1	→ Sort (cost=9373.31..9463.86 rows=36220 width=36) (actual time=137.391..140.459 rows=50809 loops=1) Sort Key: workareas.aspect Sort Method: quicksort Memory: 8094kB
4.	63.882	109.760	↓ 1.4	50823	1	→ Hash Anti Join (cost=3863.07..6630.64 rows=36220 width=36) (actual time=36.458..109.760 rows=50823 loops=1) Hash Cond: ((workareas.aspect)::integer = (aspectinactive.id)::integer)
5.	9.461	9.461	↑ 1.0	72440	1	→ Seq Scan on workareas (cost=0.00..1916.40 rows=72440 width=36) (actual time=0.007..9.461 rows=72440 loops=1)
6.	23.468	36.417	↑ 1.0	138403	1	→ Hash (cost=2133.03..2133.03 rows=138403 width=4) (actual time=36.417..36.417 rows=138403 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 5948kB
7.	12.949	12.949	↑ 1.0	138403	1	→ Seq Scan on aspectinactive (cost=0.00..2133.03 rows=138403 width=4) (actual time=0.006..12.949 rows=138403 loops=1)
8.	1071.361	3047.979	↓ 4.7	800592	1	→ Sort (cost=287847.66..288274.69 rows=170810 width=4) (actual time=2949.319..3047.979 rows=800592 loops=1) Sort Key: grouprights.aspect Sort Method: external sort Disk: 14096kB
9.	416.249	1976.618	↓ 4.7	800592	1	→ Hash Join (cost=10005.97..273002.54 rows=170810 width=4) (actual time=1337.832..1976.618 rows=800592 loops=1) Hash Cond: ((grouprights."group")::integer = (groups.id)::integer)
10.	1504.766	1504.766	↑ 1.6	3867485	1	→ Seq Scan on grouprights (cost=0.00..206182.80 rows=6297790 width=8) (actual time=0.034..1504.766 rows=3867485 loops=1) Filter: visit
11.	0.004	55.603	↑ 2476.5	2	1	→ Hash (cost=9944.06..9944.06 rows=4953 width=8) (actual time=55.603..55.603 rows=2 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB
12.	0.075	55.599	↑ 2476.5	2	1	→ Hash Join (cost=5191.65..9944.06 rows=4953 width=8) (actual time=55.593..55.599 rows=2 loops=1) Hash Cond: ((u2g."group")::integer = (groups.id)::integer)
13.	0.010	0.028	↑ 2476.5	2	1	→ Bitmap Heap Scan on user2group u2g (cost=94.74..4748.10 rows=4953 width=4) (actual time=0.023..0.028 rows=2 loops=1) Recheck Cond: (("user")::integer = 234393)
14.	0.018	0.018	↑ 2476.5	2	1	→ Bitmap Index Scan on "fki_user2group_FK1" (cost=0.00..93.51 rows=4953 width=0) (actual time=0.018..0.018 rows=2 loops=1) Index Cond: (("user")::integer = 234393)
15.	33.191	55.496	↑ 1.0	182618	1	→ Hash (cost=2814.18..2814.18 rows=182618 width=4) (actual time=55.496..55.496 rows=182618 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 6421kB
16.	22.305	22.305	↑ 1.0	182618	1	→ Seq Scan on groups (cost=0.00..2814.18 rows=182618 width=4) (actual time=0.007..22.305 rows=182618 loops=1)

"HashAggregate  (cost=993409.91..993412.91 rows=200 width=32) (actual time=3276.080..3282.245 rows=31351 loops=1)"
"  ->  Merge Join  (cost=297220.97..916075.75 rows=30933664 width=32) (actual time=3111.085..3266.079 rows=31351 loops=1)"
"        Merge Cond: ((workareas.aspect)::integer = (grouprights.aspect)::integer)"
"        ->  Sort  (cost=9373.31..9463.86 rows=36220 width=36) (actual time=137.391..140.459 rows=50809 loops=1)"
"              Sort Key: workareas.aspect"
"              Sort Method: quicksort  Memory: 8094kB"
"              ->  Hash Anti Join  (cost=3863.07..6630.64 rows=36220 width=36) (actual time=36.458..109.760 rows=50823 loops=1)"
"                    Hash Cond: ((workareas.aspect)::integer = (aspectinactive.id)::integer)"
"                    ->  Seq Scan on workareas  (cost=0.00..1916.40 rows=72440 width=36) (actual time=0.007..9.461 rows=72440 loops=1)"
"                    ->  Hash  (cost=2133.03..2133.03 rows=138403 width=4) (actual time=36.417..36.417 rows=138403 loops=1)"
"                          Buckets: 16384  Batches: 1  Memory Usage: 5948kB"
"                          ->  Seq Scan on aspectinactive  (cost=0.00..2133.03 rows=138403 width=4) (actual time=0.006..12.949 rows=138403 loops=1)"
"        ->  Sort  (cost=287847.66..288274.69 rows=170810 width=4) (actual time=2949.319..3047.979 rows=800592 loops=1)"
"              Sort Key: grouprights.aspect"
"              Sort Method: external sort  Disk: 14096kB"
"              ->  Hash Join  (cost=10005.97..273002.54 rows=170810 width=4) (actual time=1337.832..1976.618 rows=800592 loops=1)"
"                    Hash Cond: ((grouprights."group")::integer = (groups.id)::integer)"
"                    ->  Seq Scan on grouprights  (cost=0.00..206182.80 rows=6297790 width=8) (actual time=0.034..1504.766 rows=3867485 loops=1)"
"                          Filter: visit"
"                    ->  Hash  (cost=9944.06..9944.06 rows=4953 width=8) (actual time=55.603..55.603 rows=2 loops=1)"
"                          Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"                          ->  Hash Join  (cost=5191.65..9944.06 rows=4953 width=8) (actual time=55.593..55.599 rows=2 loops=1)"
"                                Hash Cond: ((u2g."group")::integer = (groups.id)::integer)"
"                                ->  Bitmap Heap Scan on user2group u2g  (cost=94.74..4748.10 rows=4953 width=4) (actual time=0.023..0.028 rows=2 loops=1)"
"                                      Recheck Cond: (("user")::integer = 234393)"
"                                      ->  Bitmap Index Scan on "fki_user2group_FK1"  (cost=0.00..93.51 rows=4953 width=0) (actual time=0.018..0.018 rows=2 loops=1)"
"                                            Index Cond: (("user")::integer = 234393)"
"                                ->  Hash  (cost=2814.18..2814.18 rows=182618 width=4) (actual time=55.496..55.496 rows=182618 loops=1)"
"                                      Buckets: 32768  Batches: 1  Memory Usage: 6421kB"
"                                      ->  Seq Scan on groups  (cost=0.00..2814.18 rows=182618 width=4) (actual time=0.007..22.305 rows=182618 loops=1)"
"Total runtime: 3289.234 ms"

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	0.010 ms	0.0 %
Bitmap Index Scan	1	0.018 ms	0.0 %
Hash	3	56.663 ms	1.7 %
Hash Anti Join	1	63.882 ms	1.9 %
Hash Join	2	416.324 ms	12.7 %
HashAggregate	1	16.166 ms	0.5 %
Merge Join	1	77.641 ms	2.4 %
Seq Scan	4	1549.481 ms	47.2 %
Sort	2	1102.060 ms	33.6 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
aspectinactive	1	12.949 ms	0.4 %
Seq Scan	1	12.949 ms	100.0 %
grouprights	1	1504.766 ms	45.8 %
Seq Scan	1	1504.766 ms	100.0 %
groups	1	22.305 ms	0.7 %
Seq Scan	1	22.305 ms	100.0 %
user2group	1	0.010 ms	0.0 %
Bitmap Heap Scan	1	0.010 ms	100.0 %
workareas	1	9.461 ms	0.3 %
Seq Scan	1	9.461 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: YVP

Colorize:

Columns visibility:

Per node type stats

Per table stats