explain.depesz.com

A tool for finding a real cause for slow queries.

Result: bv8 : CTE thing

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops 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