#	exclusive	inclusive	rows x	node
1.	0.000	0.000	↓ 0.0	→ HashAggregate (cost=188.98..188.99 rows=1 width=89) (actual time=.. rows= loops=)
2.	0.000	0.000	↓ 0.0	→ Hash Right Join (cost=187.85..188.96 rows=1 width=89) (actual time=.. rows= loops=) Hash Cond: (b.parent_id = a.child_id)
3.	0.000	0.000	↓ 0.0	→ Seq Scan on cms_container_containers b (cost=0.00..1.07 rows=7 width=8) (actual time=.. rows= loops=)
4.	0.000	0.000	↓ 0.0	→ Hash (cost=187.84..187.84 rows=1 width=85) (actual time=.. rows= loops=)
5.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=186.69..187.84 rows=1 width=85) (actual time=.. rows= loops=) Join Filter: (a.child_id = d.id)
6.	0.000	0.000	↓ 0.0	→ Merge Join (cost=186.69..186.72 rows=1 width=48) (actual time=.. rows= loops=) Merge Cond: (a.child_id = c.id)
7.	0.000	0.000	↓ 0.0	→ Sort (cost=1.11..1.12 rows=3 width=12) (actual time=.. rows= loops=) Sort Key: a.child_id
8.	0.000	0.000	↓ 0.0	→ Seq Scan on cms_container_containers a (cost=0.00..1.09 rows=3 width=12) (actual time=.. rows= loops=) Filter: (parent_id = 1)
9.	0.000	0.000	↓ 0.0	→ Sort (cost=185.58..185.58 rows=1 width=36) (actual time=.. rows= loops=) Sort Key: c.id
10.	0.000	0.000	↓ 0.0	→ Subquery Scan on c (cost=185.55..185.57 rows=1 width=36) (actual time=.. rows= loops=)
11.	0.000	0.000	↓ 0.0	→ HashAggregate (cost=185.55..185.56 rows=1 width=12) (actual time=.. rows= loops=)
12.				CTE subcontainer
13.	0.000	0.000	↓ 0.0	→ Recursive Union (cost=0.00..177.17 rows=71 width=48) (actual time=.. rows= loops=)
14.	0.000	0.000	↓ 0.0	→ Nested Loop Left Join (cost=0.00..11.74 rows=1 width=16) (actual time=.. rows= loops=) Join Filter: (cs.container_id = c1.id)
15.	0.000	0.000	↓ 0.0	→ Nested Loop Left Join (cost=0.00..2.19 rows=1 width=8) (actual time=.. rows= loops=) Join Filter: (c1.id = cc.child_id)
16.	0.000	0.000	↓ 0.0	→ Seq Scan on cms_containers c1 (cost=0.00..1.09 rows=1 width=4) (actual time=.. rows= loops=) Filter: (id = 1)
17.	0.000	0.000	↓ 0.0	→ Seq Scan on cms_container_containers cc (cost=0.00..1.09 rows=1 width=8) (actual time=.. rows= loops=) Filter: (child_id = 1)
18.	0.000	0.000	↓ 0.0	→ GroupAggregate (cost=0.00..9.52 rows=1 width=8) (actual time=.. rows= loops=)
19.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=0.00..9.51 rows=1 width=8) (actual time=.. rows= loops=)
20.	0.000	0.000	↓ 0.0	→ Seq Scan on cms_container_schedule cs (cost=0.00..1.21 rows=1 width=8) (actual time=.. rows= loops=) Filter: (container_id = 1)
21.	0.000	0.000	↓ 0.0	→ Index Scan using ix_cms_entitlements_asset_id on cms_entitlements e (cost=0.00..8.28 rows=1 width=4) (actual time=.. rows= loops=) Index Cond: (asset_id = cs.asset_id) Filter: (((offer_stop IS NULL) OR (offer_stop > now())) AND (offer_start <= (now() + '24:00:00'::interval)))
22.	0.000	0.000	↓ 0.0	→ Hash Join (cost=15.02..16.40 rows=7 width=48) (actual time=.. rows= loops=) Hash Cond: (dd.parent_id = sd.id)
23.	0.000	0.000	↓ 0.0	→ Hash Left Join (cost=14.70..15.91 rows=7 width=16) (actual time=.. rows= loops=) Hash Cond: (c2.id = t.container_id)
24.	0.000	0.000	↓ 0.0	→ Hash Join (cost=1.16..2.32 rows=7 width=8) (actual time=.. rows= loops=) Hash Cond: (c2.id = dd.child_id)
25.	0.000	0.000	↓ 0.0	→ Seq Scan on cms_containers c2 (cost=0.00..1.07 rows=7 width=4) (actual time=.. rows= loops=)
26.	0.000	0.000	↓ 0.0	→ Hash (cost=1.07..1.07 rows=7 width=8) (actual time=.. rows= loops=)
27.	0.000	0.000	↓ 0.0	→ Seq Scan on cms_container_containers dd (cost=0.00..1.07 rows=7 width=8) (actual time=.. rows= loops=)
28.	0.000	0.000	↓ 0.0	→ Hash (cost=13.52..13.52 rows=2 width=12) (actual time=.. rows= loops=)
29.	0.000	0.000	↓ 0.0	→ Subquery Scan on t (cost=13.48..13.52 rows=2 width=12) (actual time=.. rows= loops=)
30.	0.000	0.000	↓ 0.0	→ HashAggregate (cost=13.48..13.50 rows=2 width=8) (actual time=.. rows= loops=)
31.	0.000	0.000	↓ 0.0	→ Hash Join (cost=1.38..13.46 rows=2 width=8) (actual time=.. rows= loops=) Hash Cond: (e.asset_id = cs.asset_id)
32.	0.000	0.000	↓ 0.0	→ Seq Scan on cms_entitlements e (cost=0.00..12.03 rows=10 width=4) (actual time=.. rows= loops=) Filter: (((offer_stop IS NULL) OR (offer_stop > now())) AND (offer_start <= (now() + '24:00:00'::interval)))
33.	0.000	0.000	↓ 0.0	→ Hash (cost=1.17..1.17 rows=17 width=8) (actual time=.. rows= loops=)
34.	0.000	0.000	↓ 0.0	→ Seq Scan on cms_container_schedule cs (cost=0.00..1.17 rows=17 width=8) (actual time=.. rows= loops=)
35.	0.000	0.000	↓ 0.0	→ Hash (cost=0.20..0.20 rows=10 width=36) (actual time=.. rows= loops=)
36.	0.000	0.000	↓ 0.0	→ WorkTable Scan on subcontainer sd (cost=0.00..0.20 rows=10 width=36) (actual time=.. rows= loops=)
37.	0.000	0.000	↓ 0.0	→ Nested Loop (cost=0.00..8.37 rows=1 width=12) (actual time=.. rows= loops=) Join Filter: (b.path ~ similar_escape((('1\.'::text \|\| (cms_container_containers.child_id)::text) \|\| '(\.%%){0,1}'::text), NULL::text))
38.	0.000	0.000	↓ 0.0	→ CTE Scan on subcontainer b (cost=0.00..1.42 rows=71 width=40) (actual time=.. rows= loops=)
39.	0.000	0.000	↓ 0.0	→ Materialize (cost=0.00..1.10 rows=3 width=4) (actual time=.. rows= loops=)
40.	0.000	0.000	↓ 0.0	→ Seq Scan on cms_container_containers (cost=0.00..1.09 rows=3 width=4) (actual time=.. rows= loops=) Filter: (parent_id = 1)
41.	0.000	0.000	↓ 0.0	→ Seq Scan on cms_containers d (cost=0.00..1.07 rows=4 width=45) (actual time=.. rows= loops=) Filter: visible

"HashAggregate  (cost=188.98..188.99 rows=1 width=89)"
"  ->  Hash Right Join  (cost=187.85..188.96 rows=1 width=89)"
"        Hash Cond: (b.parent_id = a.child_id)"
"        ->  Seq Scan on cms_container_containers b  (cost=0.00..1.07 rows=7 width=8)"
"        ->  Hash  (cost=187.84..187.84 rows=1 width=85)"
"              ->  Nested Loop  (cost=186.69..187.84 rows=1 width=85)"
"                    Join Filter: (a.child_id = d.id)"
"                    ->  Merge Join  (cost=186.69..186.72 rows=1 width=48)"
"                          Merge Cond: (a.child_id = c.id)"
"                          ->  Sort  (cost=1.11..1.12 rows=3 width=12)"
"                                Sort Key: a.child_id"
"                                ->  Seq Scan on cms_container_containers a  (cost=0.00..1.09 rows=3 width=12)"
"                                      Filter: (parent_id = 1)"
"                          ->  Sort  (cost=185.58..185.58 rows=1 width=36)"
"                                Sort Key: c.id"
"                                ->  Subquery Scan on c  (cost=185.55..185.57 rows=1 width=36)"
"                                      ->  HashAggregate  (cost=185.55..185.56 rows=1 width=12)"
"                                            CTE subcontainer"
"                                              ->  Recursive Union  (cost=0.00..177.17 rows=71 width=48)"
"                                                    ->  Nested Loop Left Join  (cost=0.00..11.74 rows=1 width=16)"
"                                                          Join Filter: (cs.container_id = c1.id)"
"                                                          ->  Nested Loop Left Join  (cost=0.00..2.19 rows=1 width=8)"
"                                                                Join Filter: (c1.id = cc.child_id)"
"                                                                ->  Seq Scan on cms_containers c1  (cost=0.00..1.09 rows=1 width=4)"
"                                                                      Filter: (id = 1)"
"                                                                ->  Seq Scan on cms_container_containers cc  (cost=0.00..1.09 rows=1 width=8)"
"                                                                      Filter: (child_id = 1)"
"                                                          ->  GroupAggregate  (cost=0.00..9.52 rows=1 width=8)"
"                                                                ->  Nested Loop  (cost=0.00..9.51 rows=1 width=8)"
"                                                                      ->  Seq Scan on cms_container_schedule cs  (cost=0.00..1.21 rows=1 width=8)"
"                                                                            Filter: (container_id = 1)"
"                                                                      ->  Index Scan using ix_cms_entitlements_asset_id on cms_entitlements e  (cost=0.00..8.28 rows=1 width=4)"
"                                                                            Index Cond: (asset_id = cs.asset_id)"
"                                                                            Filter: (((offer_stop IS NULL) OR (offer_stop > now())) AND (offer_start <= (now() + '24:00:00'::interval)))"
"                                                    ->  Hash Join  (cost=15.02..16.40 rows=7 width=48)"
"                                                          Hash Cond: (dd.parent_id = sd.id)"
"                                                          ->  Hash Left Join  (cost=14.70..15.91 rows=7 width=16)"
"                                                                Hash Cond: (c2.id = t.container_id)"
"                                                                ->  Hash Join  (cost=1.16..2.32 rows=7 width=8)"
"                                                                      Hash Cond: (c2.id = dd.child_id)"
"                                                                      ->  Seq Scan on cms_containers c2  (cost=0.00..1.07 rows=7 width=4)"
"                                                                      ->  Hash  (cost=1.07..1.07 rows=7 width=8)"
"                                                                            ->  Seq Scan on cms_container_containers dd  (cost=0.00..1.07 rows=7 width=8)"
"                                                                ->  Hash  (cost=13.52..13.52 rows=2 width=12)"
"                                                                      ->  Subquery Scan on t  (cost=13.48..13.52 rows=2 width=12)"
"                                                                            ->  HashAggregate  (cost=13.48..13.50 rows=2 width=8)"
"                                                                                  ->  Hash Join  (cost=1.38..13.46 rows=2 width=8)"
"                                                                                        Hash Cond: (e.asset_id = cs.asset_id)"
"                                                                                        ->  Seq Scan on cms_entitlements e  (cost=0.00..12.03 rows=10 width=4)"
"                                                                                              Filter: (((offer_stop IS NULL) OR (offer_stop > now())) AND (offer_start <= (now() + '24:00:00'::interval)))"
"                                                                                        ->  Hash  (cost=1.17..1.17 rows=17 width=8)"
"                                                                                              ->  Seq Scan on cms_container_schedule cs  (cost=0.00..1.17 rows=17 width=8)"
"                                                          ->  Hash  (cost=0.20..0.20 rows=10 width=36)"
"                                                                ->  WorkTable Scan on subcontainer sd  (cost=0.00..0.20 rows=10 width=36)"
"                                            ->  Nested Loop  (cost=0.00..8.37 rows=1 width=12)"
"                                                  Join Filter: (b.path ~ similar_escape((('1\.'::text || (cms_container_containers.child_id)::text) || '(\.%%){0,1}'::text), NULL::text))"
"                                                  ->  CTE Scan on subcontainer b  (cost=0.00..1.42 rows=71 width=40)"
"                                                  ->  Materialize  (cost=0.00..1.10 rows=3 width=4)"
"                                                        ->  Seq Scan on cms_container_containers  (cost=0.00..1.09 rows=3 width=4)"
"                                                              Filter: (parent_id = 1)"
"                    ->  Seq Scan on cms_containers d  (cost=0.00..1.07 rows=4 width=45)"
"                          Filter: visible"

Per node type stats

node type	count	sum of times	% of query
CTE Scan	1	0.000 ms	0.0 %
GroupAggregate	1	0.000 ms	0.0 %
Hash	5	0.000 ms	0.0 %
Hash Join	3	0.000 ms	0.0 %
Hash Left Join	1	0.000 ms	0.0 %
Hash Right Join	1	0.000 ms	0.0 %
HashAggregate	3	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
Materialize	1	0.000 ms	0.0 %
Merge Join	1	0.000 ms	0.0 %
Nested Loop	3	0.000 ms	0.0 %
Nested Loop Left Join	2	0.000 ms	0.0 %
Recursive Union	1	0.000 ms	0.0 %
Seq Scan	11	0.000 ms	0.0 %
Sort	2	0.000 ms	0.0 %
Subquery Scan on c	1	0.000 ms	0.0 %
Subquery Scan on t	1	0.000 ms	0.0 %
WorkTable Scan on subcontainer sd	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
cms_container_containers	5	0.000 ms	0.0 %
Seq Scan	5	0.000 ms	0.0 %
cms_container_schedule	2	0.000 ms	0.0 %
Seq Scan	2	0.000 ms	0.0 %
cms_containers	3	0.000 ms	0.0 %
Seq Scan	3	0.000 ms	0.0 %
cms_entitlements	2	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: bv8 : CTE thing

Colorize:

Columns visibility:

Per node type stats

Per table stats