#	exclusive	inclusive	rows x	node
1.	0.000	0.000	↓ 0.0	→ Result (cost=686,927.56..686,932.02 rows=17 width=217) (actual rows= loops=)
2.	0.000	0.000	↓ 0.0	→ Sort (cost=686,927.56..686,927.60 rows=17 width=189) (actual rows= loops=) Sort Key: dcc2.pretty_name, dci.class_key, dcc_1.pretty_name, dcc_1.community_key
3.	0.000	0.000	↓ 0.0	→ Nested Loop Left Join (cost=495,066.09..686,927.21 rows=17 width=189) (actual rows= loops=) Join Filter: (dcc_1.community_id = members.community_id)
4.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=11,896.01..195,678.47 rows=2 width=181) (actual rows= loops=) Join Filter: (dcc_1.parent_community_id = dcc.community_id)
5.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=0.84..966.48 rows=1 width=451) (actual rows= loops=) Join Filter: (dcc.community_id = dla.community_id)
6.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=0.42..949.81 rows=1 width=33) (actual rows= loops=)
7.	0.000	0.000	↓ 0.0	→ Seq Scan on dotlrn_classes dc (cost=0.00..2.17 rows=117 width=4) (actual rows= loops=)
8.	0.000	0.000	↓ 0.0	→ Index Scan using dtlrn_com_all_com_par_id_idx on dotlrn_communities_core dcc (cost=0.42..8.10 rows=1 width=29) (actual rows= loops=) Index Cond: (community_id = dc.class_id) Filter: ((community_type)::text = 'dotlrn_class'::text)
9.	0.000	0.000	↓ 0.0	→ Index Only Scan using dlrn_atts_pk on dotlrn_learn_attributes dla (cost=0.42..7.96 rows=1 width=4) (actual rows= loops=) Index Cond: (community_id = dc.class_id)
10.				SubPlan (forNested Loop)
11.	0.000	0.000	↓ 0.0	→ Index Scan using site_nodes_object_id_idx on site_nodes site_nodes_1 (cost=0.43..8.70 rows=1 width=32) (actual rows= loops=) Index Cond: (object_id = dcc.package_id)
12.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=11,895.17..194,292.99 rows=18,622 width=648) (actual rows= loops=)
13.	0.000	0.000	↓ 0.0	→ Seq Scan on dotlrn_terms t (cost=0.00..1.24 rows=1 width=22) (actual rows= loops=) Filter: (term_id = 291558394)
14.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=11,895.17..32,140.68 rows=18,622 width=82) (actual rows= loops=)
15.	0.000	0.000	↓ 0.0	→ Hash Join (cost=11,894.75..21,674.96 rows=18,622 width=69) (actual rows= loops=) Hash Cond: (dcc_1.community_id = dci.class_instance_id)
16.	0.000	0.000	↓ 0.0	→ Seq Scan on dotlrn_communities_core dcc_1 (cost=0.00..8,787.75 rows=209,110 width=46) (actual rows= loops=) Filter: ((community_type)::text = 'dotlrn_class_instance'::text)
17.	0.000	0.000	↓ 0.0	→ Hash (cost=11,603.51..11,603.51 rows=23,299 width=35) (actual rows= loops=)
18.	0.000	0.000	↓ 0.0	→ Hash Join (cost=6,082.26..11,603.51 rows=23,299 width=35) (actual rows= loops=) Hash Cond: (g.group_id = dci.class_instance_id)
19.	0.000	0.000	↓ 0.0	→ Seq Scan on groups g (cost=0.00..4,411.10 rows=233,910 width=4) (actual rows= loops=)
20.	0.000	0.000	↓ 0.0	→ Hash (cost=5,791.02..5,791.02 rows=23,299 width=31) (actual rows= loops=)
21.	0.000	0.000	↓ 0.0	→ Hash Join (cost=1,657.24..5,791.02 rows=23,299 width=31) (actual rows= loops=) Hash Cond: (dla_1.community_id = dci.class_instance_id)
22.	0.000	0.000	↓ 0.0	→ Seq Scan on dotlrn_learn_attributes dla_1 (cost=0.00..3,587.19 rows=208,219 width=4) (actual rows= loops=)
23.	0.000	0.000	↓ 0.0	→ Hash (cost=1,364.75..1,364.75 rows=23,399 width=27) (actual rows= loops=)
24.	0.000	0.000	↓ 0.0	→ Index Scan using dotlrn_class_instances_term_id_idx on dotlrn_class_instances dci (cost=0.42..1,364.75 rows=23,399 width=27) (actual rows= loops=) Index Cond: (term_id = 291558394)
25.	0.000	0.000	↓ 0.0	→ Index Scan using dotlrn_communities_pk on dotlrn_communities_core dcc2 (cost=0.42..0.56 rows=1 width=17) (actual rows= loops=) Index Cond: (community_id = dcc_1.parent_community_id)
26.				SubPlan (forNested Loop)
27.	0.000	0.000	↓ 0.0	→ Index Scan using site_nodes_object_id_idx on site_nodes (cost=0.43..8.70 rows=1 width=32) (actual rows= loops=) Index Cond: (object_id = dcc_1.package_id)
28.	0.000	0.000	↓ 0.0	→ Materialize (cost=483,170.09..487,017.06 rows=153,879 width=12) (actual rows= loops=)
29.	0.000	0.000	↓ 0.0	→ Subquery Scan on members (cost=483,170.09..486,247.67 rows=153,879 width=12) (actual rows= loops=)
30.	0.000	0.000	↓ 0.0	→ HashAggregate (cost=483,170.09..484,708.88 rows=153,879 width=12) (actual rows= loops=) Group Key: acs_rels.object_id_one
31.	0.000	0.000	↓ 0.0	→ Hash Join (cost=270,598.72..462,818.30 rows=4,070,357 width=4) (actual rows= loops=) Hash Cond: (acs_rels.rel_id = dotlrn_member_rels.rel_id)
32.	0.000	0.000	↓ 0.0	→ Seq Scan on acs_rels (cost=0.00..107,798.72 rows=5,828,972 width=8) (actual rows= loops=)
33.	0.000	0.000	↓ 0.0	→ Hash (cost=219,719.26..219,719.26 rows=4,070,357 width=8) (actual rows= loops=)
34.	0.000	0.000	↓ 0.0	→ Hash Join (cost=114,116.50..219,719.26 rows=4,070,357 width=8) (actual rows= loops=) Hash Cond: (membership_rels.rel_id = dotlrn_member_rels.rel_id)
35.	0.000	0.000	↓ 0.0	→ Seq Scan on membership_rels (cost=0.00..92,758.98 rows=4,892,867 width=4) (actual rows= loops=) Filter: ((member_state)::text = 'approved'::text)
36.	0.000	0.000	↓ 0.0	→ Hash (cost=62,999.00..62,999.00 rows=4,089,400 width=4) (actual rows= loops=)
37.	0.000	0.000	↓ 0.0	→ Seq Scan on dotlrn_member_rels (cost=0.00..62,999.00 rows=4,089,400 width=4) (actual rows= loops=)

                                                                                             QUERY PLAN                                                                                              
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=686927.56..686932.02 rows=17 width=217)
   ->  Sort  (cost=686927.56..686927.60 rows=17 width=189)
         Sort Key: dcc2.pretty_name, dci.class_key, dcc_1.pretty_name, dcc_1.community_key
         ->  Nested Loop Left Join  (cost=495066.09..686927.21 rows=17 width=189)
               Join Filter: (dcc_1.community_id = members.community_id)
               ->  Nested Loop  (cost=11896.01..195678.47 rows=2 width=181)
                     Join Filter: (dcc_1.parent_community_id = dcc.community_id)
                     ->  Nested Loop  (cost=0.84..966.48 rows=1 width=451)
                           Join Filter: (dcc.community_id = dla.community_id)
                           ->  Nested Loop  (cost=0.42..949.81 rows=1 width=33)
                                 ->  Seq Scan on dotlrn_classes dc  (cost=0.00..2.17 rows=117 width=4)
                                 ->  Index Scan using dtlrn_com_all_com_par_id_idx on dotlrn_communities_core dcc  (cost=0.42..8.10 rows=1 width=29)
                                       Index Cond: (community_id = dc.class_id)
                                       Filter: ((community_type)::text = 'dotlrn_class'::text)
                           ->  Index Only Scan using dlrn_atts_pk on dotlrn_learn_attributes dla  (cost=0.42..7.96 rows=1 width=4)
                                 Index Cond: (community_id = dc.class_id)
                           SubPlan 2
                             ->  Index Scan using site_nodes_object_id_idx on site_nodes site_nodes_1  (cost=0.43..8.70 rows=1 width=32)
                                   Index Cond: (object_id = dcc.package_id)
                     ->  Nested Loop  (cost=11895.17..194292.99 rows=18622 width=648)
                           ->  Seq Scan on dotlrn_terms t  (cost=0.00..1.24 rows=1 width=22)
                                 Filter: (term_id = 291558394)
                           ->  Nested Loop  (cost=11895.17..32140.68 rows=18622 width=82)
                                 ->  Hash Join  (cost=11894.75..21674.96 rows=18622 width=69)
                                       Hash Cond: (dcc_1.community_id = dci.class_instance_id)
                                       ->  Seq Scan on dotlrn_communities_core dcc_1  (cost=0.00..8787.75 rows=209110 width=46)
                                             Filter: ((community_type)::text = 'dotlrn_class_instance'::text)
                                       ->  Hash  (cost=11603.51..11603.51 rows=23299 width=35)
                                             ->  Hash Join  (cost=6082.26..11603.51 rows=23299 width=35)
                                                   Hash Cond: (g.group_id = dci.class_instance_id)
                                                   ->  Seq Scan on groups g  (cost=0.00..4411.10 rows=233910 width=4)
                                                   ->  Hash  (cost=5791.02..5791.02 rows=23299 width=31)
                                                         ->  Hash Join  (cost=1657.24..5791.02 rows=23299 width=31)
                                                               Hash Cond: (dla_1.community_id = dci.class_instance_id)
                                                               ->  Seq Scan on dotlrn_learn_attributes dla_1  (cost=0.00..3587.19 rows=208219 width=4)
                                                               ->  Hash  (cost=1364.75..1364.75 rows=23399 width=27)
                                                                     ->  Index Scan using dotlrn_class_instances_term_id_idx on dotlrn_class_instances dci  (cost=0.42..1364.75 rows=23399 width=27)
                                                                           Index Cond: (term_id = 291558394)
                                 ->  Index Scan using dotlrn_communities_pk on dotlrn_communities_core dcc2  (cost=0.42..0.56 rows=1 width=17)
                                       Index Cond: (community_id = dcc_1.parent_community_id)
                           SubPlan 1
                             ->  Index Scan using site_nodes_object_id_idx on site_nodes  (cost=0.43..8.70 rows=1 width=32)
                                   Index Cond: (object_id = dcc_1.package_id)
               ->  Materialize  (cost=483170.09..487017.06 rows=153879 width=12)
                     ->  Subquery Scan on members  (cost=483170.09..486247.67 rows=153879 width=12)
                           ->  HashAggregate  (cost=483170.09..484708.88 rows=153879 width=12)
                                 Group Key: acs_rels.object_id_one
                                 ->  Hash Join  (cost=270598.72..462818.30 rows=4070357 width=4)
                                       Hash Cond: (acs_rels.rel_id = dotlrn_member_rels.rel_id)
                                       ->  Seq Scan on acs_rels  (cost=0.00..107798.72 rows=5828972 width=8)
                                       ->  Hash  (cost=219719.26..219719.26 rows=4070357 width=8)
                                             ->  Hash Join  (cost=114116.50..219719.26 rows=4070357 width=8)
                                                   Hash Cond: (membership_rels.rel_id = dotlrn_member_rels.rel_id)
                                                   ->  Seq Scan on membership_rels  (cost=0.00..92758.98 rows=4892867 width=4)
                                                         Filter: ((member_state)::text = 'approved'::text)
                                                   ->  Hash  (cost=62999.00..62999.00 rows=4089400 width=4)
                                                         ->  Seq Scan on dotlrn_member_rels  (cost=0.00..62999.00 rows=4089400 width=4)
(57 rows)

Per node type stats

node type	count	sum of times	% of query
Hash	5	0.000 ms	0.0 %
Hash Join	5	0.000 ms	0.0 %
HashAggregate	1	0.000 ms	0.0 %
Index Only Scan	1	0.000 ms	0.0 %
Index Scan	5	0.000 ms	0.0 %
Materialize	1	0.000 ms	0.0 %
Nested Loop	5	0.000 ms	0.0 %
Nested Loop Left Join	1	0.000 ms	0.0 %
Result	1	0.000 ms	0.0 %
Seq Scan	8	0.000 ms	0.0 %
Sort	1	0.000 ms	0.0 %
Subquery Scan	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
acs_rels	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
dotlrn_class_instances	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
dotlrn_classes	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
dotlrn_communities_core	3	0.000 ms	0.0 %
Index Scan	2	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
dotlrn_learn_attributes	2	0.000 ms	0.0 %
Index Only Scan	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
dotlrn_member_rels	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
dotlrn_terms	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
groups	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
membership_rels	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
site_nodes	2	0.000 ms	0.0 %
Index Scan	2	0.000 ms	0.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5PCR

Color mode:

Visible columns:

Per node type stats

Per table stats