#	exclusive	inclusive	rows x	node
1.	0.000	0.000	↓ 0.0	→ Unique (cost=4639.82..4639.85 rows=1 width=112) (actual time=.. rows= loops=)
2.				CTE user_scores
3.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=12.41..4638.44 rows=4 width=103) (actual time=.. rows= loops=)
4.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=12.41..4637.47 rows=1 width=107) (actual time=.. rows= loops=)
5.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=12.41..4635.88 rows=1 width=69) (actual time=.. rows= loops=)
6.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=12.41..4633.61 rows=1 width=65) (actual time=.. rows= loops=)
7.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=12.41..4627.34 rows=1 width=46) (actual time=.. rows= loops=)
8.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=12.41..4624.86 rows=7 width=22) (actual time=.. rows= loops=)
9.	0.000	0.000	↓ 0.0	→ Index Scan using questions_competencyid_idx on questions q (cost=0.00..24.21 rows=4 width=10) (actual time=.. rows= loops=) Index Cond: (competencyid = 62) Filter: (checking = 0)
10.	0.000	0.000	↓ 0.0	→ Bitmap Heap Scan on useranswers ua (cost=12.41..1143.29 rows=550 width=16) (actual time=.. rows= loops=) Recheck Cond: (questionid = q.id) Filter: (count_user_questions_left(userid) = 0)
11.	0.000	0.000	↓ 0.0	→ Bitmap Index Scan on useranswers_questionid_idx (cost=0.00..12.41 rows=550 width=0) (actual time=.. rows= loops=) Index Cond: (questionid = q.id)
12.	0.000	0.000	↓ 0.0	→ Index Scan using users_pkey on users u (cost=0.00..0.34 rows=1 width=24) (actual time=.. rows= loops=) Index Cond: (id = ua.userid) Filter: (groupid = 21)
13.	0.000	0.000	↓ 0.0	→ Seq Scan on competencies c (cost=0.00..6.26 rows=1 width=23) (actual time=.. rows= loops=) Filter: (id = 62)
14.	0.000	0.000	↓ 0.0	→ Seq Scan on test2competency t2c (cost=0.00..2.26 rows=1 width=8) (actual time=.. rows= loops=) Filter: (competencyid = 62)
15.	0.000	0.000	↓ 0.0	→ Seq Scan on usergroups ug (cost=0.00..1.58 rows=1 width=42) (actual time=.. rows= loops=) Filter: (id = 21)
16.	0.000	0.000	↓ 0.0	→ Index Scan using user2test_1_idx on user2test u2t (cost=0.00..0.76 rows=3 width=4) (actual time=.. rows= loops=) Index Cond: (userid = ua.userid)
17.				CTE user_averages
18.	0.000	0.000	↓ 0.0	→ HashAggregate (cost=0.29..0.33 rows=4 width=790) (actual time=.. rows= loops=)
19.	0.000	0.000	↓ 0.0	→ HashAggregate (cost=0.16..0.21 rows=4 width=790) (actual time=.. rows= loops=)
20.	0.000	0.000	↓ 0.0	→ CTE Scan on user_scores (cost=0.00..0.08 rows=4 width=790) (actual time=.. rows= loops=)
21.				CTE gc_avg_med
22.	0.000	0.000	↓ 0.0	→ HashAggregate (cost=0.15..0.21 rows=4 width=84) (actual time=.. rows= loops=)
23.	0.000	0.000	↓ 0.0	→ CTE Scan on user_averages ua (cost=0.00..0.08 rows=4 width=84) (actual time=.. rows= loops=)
24.				CTE gc_quartiles
25.	0.000	0.000	↓ 0.0	→ WindowAgg (cost=0.12..0.20 rows=4 width=116) (actual time=.. rows= loops=)
26.	0.000	0.000	↓ 0.0	→ Sort (cost=0.12..0.13 rows=4 width=116) (actual time=.. rows= loops=) Sort Key: ua.part_group_competency, ua.competency_avg
27.	0.000	0.000	↓ 0.0	→ CTE Scan on user_averages ua (cost=0.00..0.08 rows=4 width=116) (actual time=.. rows= loops=)
28.				CTE gc_quartiles_bounds
29.	0.000	0.000	↓ 0.0	→ HashAggregate (cost=0.15..0.19 rows=4 width=56) (actual time=.. rows= loops=)
30.	0.000	0.000	↓ 0.0	→ CTE Scan on gc_quartiles (cost=0.00..0.08 rows=4 width=56) (actual time=.. rows= loops=)
31.	0.000	0.000	↓ 0.0	→ Sort (cost=0.46..0.46 rows=1 width=112) (actual time=.. rows= loops=) Sort Key: gam.testid, gam.groupid, gam.groupname, gam.competencyid, gcq.quartile, gam.competencytitle, gam.group_avg, gam.group_median, gcqb.quartile_min, gcqb.quartile_max
32.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=0.15..0.45 rows=1 width=112) (actual time=.. rows= loops=) Join Filter: ((gam.testid = gcqb.testid) AND (gam.groupid = gcqb.groupid) AND (gam.competencyid = gcqb.competencyid) AND (gcq.quartile = gcqb.quartile))
33.	0.000	0.000	↓ 0.0	→ Hash Join (cost=0.15..0.29 rows=1 width=108) (actual time=.. rows= loops=) Hash Cond: ((gam.testid = gcq.testid) AND (gam.groupid = gcq.groupid) AND (gam.competencyid = gcq.competencyid))
34.	0.000	0.000	↓ 0.0	→ CTE Scan on gc_avg_med gam (cost=0.00..0.08 rows=4 width=92) (actual time=.. rows= loops=)
35.	0.000	0.000	↓ 0.0	→ Hash (cost=0.08..0.08 rows=4 width=16) (actual time=.. rows= loops=)
36.	0.000	0.000	↓ 0.0	→ CTE Scan on gc_quartiles gcq (cost=0.00..0.08 rows=4 width=16) (actual time=.. rows= loops=)
37.	0.000	0.000	↓ 0.0	→ CTE Scan on gc_quartiles_bounds gcqb (cost=0.00..0.08 rows=4 width=32) (actual time=.. rows= loops=)

Unique  (cost=4639.82..4639.85 rows=1 width=112)
   CTE user_scores
     ->  Nested Loop  (cost=12.41..4638.44 rows=4 width=103)
           ->  Nested Loop  (cost=12.41..4637.47 rows=1 width=107)
                 ->  Nested Loop  (cost=12.41..4635.88 rows=1 width=69)
                       ->  Nested Loop  (cost=12.41..4633.61 rows=1 width=65)
                             ->  Nested Loop  (cost=12.41..4627.34 rows=1 width=46)
                                   ->  Nested Loop  (cost=12.41..4624.86 rows=7 width=22)
                                         ->  Index Scan using questions_competencyid_idx on questions q  (cost=0.00..24.21 rows=4 width=10)
                                               Index Cond: (competencyid = 62)
                                               Filter: (checking = 0)
                                         ->  Bitmap Heap Scan on useranswers ua  (cost=12.41..1143.29 rows=550 width=16)
                                               Recheck Cond: (questionid = q.id)
                                               Filter: (count_user_questions_left(userid) = 0)
                                               ->  Bitmap Index Scan on useranswers_questionid_idx  (cost=0.00..12.41 rows=550 width=0)
                                                     Index Cond: (questionid = q.id)
                                   ->  Index Scan using users_pkey on users u  (cost=0.00..0.34 rows=1 width=24)
                                         Index Cond: (id = ua.userid)
                                         Filter: (groupid = 21)
                             ->  Seq Scan on competencies c  (cost=0.00..6.26 rows=1 width=23)
                                   Filter: (id = 62)
                       ->  Seq Scan on test2competency t2c  (cost=0.00..2.26 rows=1 width=8)
                             Filter: (competencyid = 62)
                 ->  Seq Scan on usergroups ug  (cost=0.00..1.58 rows=1 width=42)
                       Filter: (id = 21)
           ->  Index Scan using user2test_1_idx on user2test u2t  (cost=0.00..0.76 rows=3 width=4)
                 Index Cond: (userid = ua.userid)
   CTE user_averages
     ->  HashAggregate  (cost=0.29..0.33 rows=4 width=790)
           ->  HashAggregate  (cost=0.16..0.21 rows=4 width=790)
                 ->  CTE Scan on user_scores  (cost=0.00..0.08 rows=4 width=790)
   CTE gc_avg_med
     ->  HashAggregate  (cost=0.15..0.21 rows=4 width=84)
           ->  CTE Scan on user_averages ua  (cost=0.00..0.08 rows=4 width=84)
   CTE gc_quartiles
     ->  WindowAgg  (cost=0.12..0.20 rows=4 width=116)
           ->  Sort  (cost=0.12..0.13 rows=4 width=116)
                 Sort Key: ua.part_group_competency, ua.competency_avg
                 ->  CTE Scan on user_averages ua  (cost=0.00..0.08 rows=4 width=116)
   CTE gc_quartiles_bounds
     ->  HashAggregate  (cost=0.15..0.19 rows=4 width=56)
           ->  CTE Scan on gc_quartiles  (cost=0.00..0.08 rows=4 width=56)
   ->  Sort  (cost=0.46..0.46 rows=1 width=112)
         Sort Key: gam.testid, gam.groupid, gam.groupname, gam.competencyid, gcq.quartile, gam.competencytitle, gam.group_avg, gam.group_median, gcqb.quartile_min, gcqb.quartile_max
         ->  Nested Loop  (cost=0.15..0.45 rows=1 width=112)
               Join Filter: ((gam.testid = gcqb.testid) AND (gam.groupid = gcqb.groupid) AND (gam.competencyid = gcqb.competencyid) AND (gcq.quartile = gcqb.quartile))
               ->  Hash Join  (cost=0.15..0.29 rows=1 width=108)
                     Hash Cond: ((gam.testid = gcq.testid) AND (gam.groupid = gcq.groupid) AND (gam.competencyid = gcq.competencyid))
                     ->  CTE Scan on gc_avg_med gam  (cost=0.00..0.08 rows=4 width=92)
                     ->  Hash  (cost=0.08..0.08 rows=4 width=16)
                           ->  CTE Scan on gc_quartiles gcq  (cost=0.00..0.08 rows=4 width=16)
               ->  CTE Scan on gc_quartiles_bounds gcqb  (cost=0.00..0.08 rows=4 width=32)

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	0.000 ms	0.0 %
Bitmap Index Scan	1	0.000 ms	0.0 %
CTE Scan	7	0.000 ms	0.0 %
Hash	1	0.000 ms	0.0 %
Hash Join	1	0.000 ms	0.0 %
HashAggregate	4	0.000 ms	0.0 %
Index Scan	3	0.000 ms	0.0 %
Nested Loop	7	0.000 ms	0.0 %
Seq Scan	3	0.000 ms	0.0 %
Sort	2	0.000 ms	0.0 %
Unique	1	0.000 ms	0.0 %
WindowAgg	1	0.000 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
competencies	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
questions	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
test2competency	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
user2test	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
useranswers	1	0.000 ms	0.0 %
Bitmap Heap Scan	1	0.000 ms	0.0 %
usergroups	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
users	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: o2K

Colorize:

Columns visibility:

Per node type stats

Per table stats