#	exclusive	inclusive	rows x	rows	loops	node
1.	0.078	3.139	↓ 21.0	21	1	→ Hash Join (cost=5.49..30.25 rows=1 width=245) (actual time=2.945..3.139 rows=21 loops=1) Hash Cond: ((frm.assessmentformid = asmt.assessmentformid) AND (frm.assessmentlevelid = lvl.assessmentlevelid))
2.	0.143	0.143	↑ 1.0	587	1	→ Seq Scan on dim_assessmentform frm (cost=0.00..20.34 rows=587 width=16) (actual time=0.009..0.143 rows=587 loops=1) Filter: (administrationid = 593218)
3.	1.141	2.918	↓ 21.0	3,192	1	→ Hash (cost=3.21..3.21 rows=152 width=241) (actual time=2.918..2.918 rows=3,192 loops=1) Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 431kB
4.	0.589	1.777	↓ 21.0	3,192	1	→ Merge Join (cost=2.57..3.21 rows=152 width=241) (actual time=0.085..1.777 rows=3,192 loops=1) Merge Cond: (lvl.subtestid = tst.subtestid)
5.	0.813	1.166	↓ 21.0	3,192	1	→ Nested Loop (cost=1.55..97.61 rows=152 width=249) (actual time=0.061..1.166 rows=3,192 loops=1)
6.	0.049	0.049	↑ 1.0	152	1	→ Index Scan using udx_dim_assessmentlevel on dim_assessmentlevel lvl (cost=0.14..29.12 rows=152 width=20) (actual time=0.012..0.049 rows=152 loops=1) Index Cond: (administrationid = 593218)
7.	0.159	0.304	↓ 21.0	21	152	→ Materialize (cost=1.41..66.60 rows=1 width=233) (actual time=0.000..0.002 rows=21 loops=152)
8.	0.017	0.145	↓ 21.0	21	1	→ Nested Loop (cost=1.41..66.59 rows=1 width=233) (actual time=0.044..0.145 rows=21 loops=1)
9.	0.021	0.107	↓ 21.0	21	1	→ Nested Loop (cost=1.12..66.10 rows=1 width=233) (actual time=0.037..0.107 rows=21 loops=1)
10.	0.011	0.065	↓ 21.0	21	1	→ Nested Loop (cost=0.84..57.79 rows=1 width=233) (actual time=0.029..0.065 rows=21 loops=1)
11.	0.019	0.019	↑ 1.0	5	1	→ Index Scan using idx_dim_assessmentparticipant_2 on dim_assessmentparticipant asmt (cost=0.42..15.52 rows=5 width=48) (actual time=0.013..0.019 rows=5 loops=1) Index Cond: ((administrationid = 593218) AND (participantid = 13))
12.	0.035	0.035	↓ 4.0	4	5	→ Index Scan using idx_stg_subtestobjective_score_1 on stg_subtestobjective_score x (cost=0.42..8.44 rows=1 width=217) (actual time=0.005..0.007 rows=4 loops=5) Index Cond: ((administrationid = 593218) AND (documentidcode = asmt.documentidcode) AND (testeventidcode = asmt.testeventidcode))
13.	0.021	0.021	↑ 1.0	1	21	→ Index Scan using pk_dim_assessmentsession on dim_assessmentsession ses (cost=0.28..8.30 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=21) Index Cond: (assessmentsessionid = asmt.assessmentsessionid) Filter: (administrationid = 593218)
14.	0.021	0.021	↑ 1.0	1	21	→ Index Scan using pk_dim_organizations on dim_organizations org (cost=0.29..0.50 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=21) Index Cond: (organizationid = ses.organizationid) Filter: ((administrationid = 593218) AND (orglevel = 2))
15.	0.013	0.022	↓ 17.0	17	1	→ Sort (cost=1.01..1.01 rows=1 width=12) (actual time=0.021..0.022 rows=17 loops=1) Sort Key: tst.subtestid Sort Method: quicksort Memory: 25kB
16.	0.009	0.009	↓ 17.0	17	1	→ Seq Scan on dim_subtest tst (cost=0.00..1.00 rows=1 width=12) (actual time=0.005..0.009 rows=17 loops=1) Filter: (administrationid = 593218)

"Hash Join  (cost=5.49..30.25 rows=1 width=245) (actual time=2.945..3.139 rows=21 loops=1)"
"  Hash Cond: ((frm.assessmentformid = asmt.assessmentformid) AND (frm.assessmentlevelid = lvl.assessmentlevelid))"
"  ->  Seq Scan on dim_assessmentform frm  (cost=0.00..20.34 rows=587 width=16) (actual time=0.009..0.143 rows=587 loops=1)"
"        Filter: (administrationid = 593218)"
"  ->  Hash  (cost=3.21..3.21 rows=152 width=241) (actual time=2.918..2.918 rows=3192 loops=1)"
"        Buckets: 4096 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 431kB"
"        ->  Merge Join  (cost=2.57..3.21 rows=152 width=241) (actual time=0.085..1.777 rows=3192 loops=1)"
"              Merge Cond: (lvl.subtestid = tst.subtestid)"
"              ->  Nested Loop  (cost=1.55..97.61 rows=152 width=249) (actual time=0.061..1.166 rows=3192 loops=1)"
"                    ->  Index Scan using udx_dim_assessmentlevel on dim_assessmentlevel lvl  (cost=0.14..29.12 rows=152 width=20) (actual time=0.012..0.049 rows=152 loops=1)"
"                          Index Cond: (administrationid = 593218)"
"                    ->  Materialize  (cost=1.41..66.60 rows=1 width=233) (actual time=0.000..0.002 rows=21 loops=152)"
"                          ->  Nested Loop  (cost=1.41..66.59 rows=1 width=233) (actual time=0.044..0.145 rows=21 loops=1)"
"                                ->  Nested Loop  (cost=1.12..66.10 rows=1 width=233) (actual time=0.037..0.107 rows=21 loops=1)"
"                                      ->  Nested Loop  (cost=0.84..57.79 rows=1 width=233) (actual time=0.029..0.065 rows=21 loops=1)"
"                                            ->  Index Scan using idx_dim_assessmentparticipant_2 on dim_assessmentparticipant asmt  (cost=0.42..15.52 rows=5 width=48) (actual time=0.013..0.019 rows=5 loops=1)"
"                                                  Index Cond: ((administrationid = 593218) AND (participantid = 13))"
"                                            ->  Index Scan using idx_stg_subtestobjective_score_1 on stg_subtestobjective_score x  (cost=0.42..8.44 rows=1 width=217) (actual time=0.005..0.007 rows=4 loops=5)"
"                                                  Index Cond: ((administrationid = 593218) AND (documentidcode = asmt.documentidcode) AND (testeventidcode = asmt.testeventidcode))"
"                                      ->  Index Scan using pk_dim_assessmentsession on dim_assessmentsession ses  (cost=0.28..8.30 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=21)"
"                                            Index Cond: (assessmentsessionid = asmt.assessmentsessionid)"
"                                            Filter: (administrationid = 593218)"
"                                ->  Index Scan using pk_dim_organizations on dim_organizations org  (cost=0.29..0.50 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=21)"
"                                      Index Cond: (organizationid = ses.organizationid)"
"                                      Filter: ((administrationid = 593218) AND (orglevel = 2))"
"              ->  Sort  (cost=1.01..1.01 rows=1 width=12) (actual time=0.021..0.022 rows=17 loops=1)"
"                    Sort Key: tst.subtestid"
"                    Sort Method: quicksort  Memory: 25kB"
"                    ->  Seq Scan on dim_subtest tst  (cost=0.00..1.00 rows=1 width=12) (actual time=0.005..0.009 rows=17 loops=1)"
"                          Filter: (administrationid = 593218)"
"Planning time: 11.546 ms"
"Execution time: 3.299 ms"

Per node type stats

node type	count	sum of times	% of query
Hash	1	1.141 ms	36.3 %
Hash Join	1	0.078 ms	2.5 %
Index Scan	5	0.145 ms	4.6 %
Materialize	1	0.159 ms	5.1 %
Merge Join	1	0.589 ms	18.8 %
Nested Loop	4	0.862 ms	27.5 %
Seq Scan	2	0.152 ms	4.8 %
Sort	1	0.013 ms	0.4 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
dim_assessmentform	1	0.143 ms	4.6 %
Seq Scan	1	0.143 ms	100.0 %
dim_assessmentlevel	1	0.049 ms	1.6 %
Index Scan	1	0.049 ms	100.0 %
dim_assessmentparticipant	1	0.019 ms	0.6 %
Index Scan	1	0.019 ms	100.0 %
dim_assessmentsession	1	0.021 ms	0.7 %
Index Scan	1	0.021 ms	100.0 %
dim_organizations	1	0.021 ms	0.7 %
Index Scan	1	0.021 ms	100.0 %
dim_subtest	1	0.009 ms	0.3 %
Seq Scan	1	0.009 ms	100.0 %
stg_subtestobjective_score	1	0.035 ms	1.1 %
Index Scan	1	0.035 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v0Z1

Color mode:

Visible columns:

Per node type stats

Per table stats