explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bazr

Settings
# exclusive inclusive rows x rows loops node
1. 0.176 1.381 ↑ 254.0 5 1

Seq Scan on routine r (cost=1,858.48..2,582.86 rows=1,270 width=164) (actual time=0.930..1.381 rows=5 loops=1)

2.          

CTE wr

3. 0.024 0.024 ↓ 0.0 0 1

Index Scan using weekly_routine_date_class_idx on weekly_routine wr (cost=0.30..8.35 rows=1 width=8) (actual time=0.020..0.024 rows=0 loops=1)

  • Index Cond: ((routine_date >= (date_trunc('week'::text, ('2019-01-27'::date)::timestamp with time zone) - '1 day'::interval)) AND (routine_date < (date_trunc('week'::text, ('2019-01-27'::date)::timestamp with time zone) + '4 days'::interval)) A
  • Filter: (child_id = 14276294)
4.          

CTE wrca

5. 0.293 0.684 ↓ 0.0 0 1

Nested Loop (cost=0.03..1,447.66 rows=8 width=37) (actual time=0.680..0.684 rows=0 loops=1)

  • Join Filter: (wrca.careplan_id = rc.careplan_id)
6. 0.163 0.163 ↑ 1.0 38 1

Seq Scan on routine_careplan rc (cost=0.00..1.38 rows=38 width=21) (actual time=0.007..0.163 rows=38 loops=1)

7. 0.165 0.228 ↓ 0.0 0 38

Materialize (cost=0.03..1,441.74 rows=8 width=24) (actual time=0.006..0.006 rows=0 loops=38)

8. 0.010 0.063 ↓ 0.0 0 1

Hash Join (cost=0.03..1,441.70 rows=8 width=24) (actual time=0.059..0.063 rows=0 loops=1)

  • Hash Cond: (wrca.weekly_routine_id = wr_1.weekly_routine_id)
9. 0.012 0.012 ↑ 68,479.0 1 1

Seq Scan on weekly_routine_careplan_answer wrca (cost=0.00..1,184.79 rows=68,479 width=20) (actual time=0.008..0.012 rows=1 loops=1)

10. 0.009 0.041 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=8) (actual time=0.036..0.041 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
11. 0.032 0.032 ↓ 0.0 0 1

CTE Scan on wr wr_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.028..0.032 rows=0 loops=1)

12.          

CTE wrs

13. 0.007 0.065 ↓ 0.0 0 1

Nested Loop (cost=11.51..182.96 rows=1 width=220) (actual time=0.061..0.065 rows=0 loops=1)

14. 0.008 0.058 ↓ 0.0 0 1

Nested Loop (cost=11.37..182.79 rows=1 width=224) (actual time=0.054..0.058 rows=0 loops=1)

15. 0.009 0.050 ↓ 0.0 0 1

Hash Join (cost=11.22..180.97 rows=4 width=226) (actual time=0.046..0.050 rows=0 loops=1)

  • Hash Cond: (wrs.routine_strategy_id = rsl.routine_strategy_id)
16. 0.011 0.041 ↓ 0.0 0 1

Hash Join (cost=0.03..169.74 rows=2 width=8) (actual time=0.037..0.041 rows=0 loops=1)

  • Hash Cond: (wrs.weekly_routine_id = wr_2.weekly_routine_id)
17. 0.014 0.014 ↑ 7,686.0 1 1

Seq Scan on weekly_routine_strategy wrs (cost=0.00..140.86 rows=7,686 width=8) (actual time=0.010..0.014 rows=1 loops=1)

18. 0.008 0.016 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=8) (actual time=0.012..0.016 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
19. 0.008 0.008 ↓ 0.0 0 1

CTE Scan on wr wr_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.004..0.008 rows=0 loops=1)

20. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.86..8.86 rows=186 width=218) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Seq Scan on routine_strategy_lang rsl (cost=0.00..8.86 rows=186 width=218) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Index Scan using lang_pkey on lang l (cost=0.15..0.43 rows=1 width=4) (never executed)

  • Index Cond: (lang_id = rsl.lang_id)
  • Filter: (locale = 'en_US'::text)
23. 0.000 0.000 ↓ 0.0 0

Index Only Scan using routine_strategy_pkey on routine_strategy rs (cost=0.14..0.16 rows=1 width=4) (never executed)

  • Index Cond: (routine_strategy_id = wrs.routine_strategy_id)
  • Heap Fetches: 0
24.          

CTE assets

25. 0.008 0.071 ↓ 0.0 0 1

Nested Loop (cost=1.02..219.52 rows=1 width=67) (actual time=0.067..0.071 rows=0 loops=1)

26. 0.008 0.063 ↓ 0.0 0 1

Nested Loop (cost=0.87..219.32 rows=1 width=70) (actual time=0.059..0.063 rows=0 loops=1)

  • Join Filter: (ra.curriculum_asset_id = ac.curriculum_asset_id)
27. 0.008 0.055 ↓ 0.0 0 1

Nested Loop (cost=0.59..218.79 rows=1 width=16) (actual time=0.051..0.055 rows=0 loops=1)

28. 0.008 0.047 ↓ 0.0 0 1

Nested Loop (cost=0.31..217.98 rows=1 width=12) (actual time=0.043..0.047 rows=0 loops=1)

29. 0.010 0.039 ↓ 0.0 0 1

Hash Join (cost=0.03..217.69 rows=1 width=8) (actual time=0.035..0.039 rows=0 loops=1)

  • Hash Cond: (wra.weekly_routine_id = wr_3.weekly_routine_id)
30. 0.013 0.013 ↑ 6,956.0 1 1

Seq Scan on weekly_routine_asset wra (cost=0.00..191.56 rows=6,956 width=8) (actual time=0.009..0.013 rows=1 loops=1)

31. 0.008 0.016 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=8) (actual time=0.012..0.016 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
32. 0.008 0.008 ↓ 0.0 0 1

CTE Scan on wr wr_3 (cost=0.00..0.02 rows=1 width=8) (actual time=0.004..0.008 rows=0 loops=1)

33. 0.000 0.000 ↓ 0.0 0

Index Scan using routine_asset_pkey on routine_asset ra (cost=0.27..0.29 rows=1 width=8) (never executed)

  • Index Cond: (routine_asset_id = wra.routine_asset_id)
34. 0.000 0.000 ↓ 0.0 0

Index Scan using curriculum_asset_pkey on curriculum_asset ca (cost=0.28..0.80 rows=1 width=4) (never executed)

  • Index Cond: (curriculum_asset_id = ra.curriculum_asset_id)
  • Filter: (odl_version_id = 2)
35. 0.000 0.000 ↓ 0.0 0

Index Scan using asset_content_curriculum_asset_id on asset_content ac (cost=0.28..0.51 rows=2 width=62) (never executed)

  • Index Cond: (curriculum_asset_id = ca.curriculum_asset_id)
36. 0.000 0.000 ↓ 0.0 0

Index Scan using lang_pkey on lang l_1 (cost=0.15..0.18 rows=1 width=4) (never executed)

  • Index Cond: (lang_id = ac.lang_id)
  • Filter: (locale = 'en_US'::text)
37.          

SubPlan (for Seq Scan)

38. 0.065 0.775 ↑ 1.0 1 5

Aggregate (cost=0.24..0.25 rows=1 width=32) (actual time=0.150..0.155 rows=1 loops=5)

39. 0.710 0.710 ↓ 0.0 0 5

CTE Scan on wrca wrca_1 (cost=0.00..0.22 rows=1 width=64) (actual time=0.141..0.142 rows=0 loops=5)

  • Filter: ((answer <> '""'::jsonb) AND (title <> 'ssl_nap'::text) AND (routine_id = r.routine_id))
40. 0.060 0.150 ↑ 1.0 1 5

Aggregate (cost=0.03..0.04 rows=1 width=32) (actual time=0.026..0.030 rows=1 loops=5)

41. 0.090 0.090 ↓ 0.0 0 5

CTE Scan on wrs wrs_1 (cost=0.00..0.02 rows=1 width=72) (actual time=0.017..0.018 rows=0 loops=5)

  • Filter: (routine_id = r.routine_id)
42. 0.120 0.215 ↑ 1.0 1 5

Aggregate (cost=0.03..0.04 rows=1 width=32) (actual time=0.039..0.043 rows=1 loops=5)

43. 0.095 0.095 ↓ 0.0 0 5

CTE Scan on assets a (cost=0.00..0.02 rows=1 width=104) (actual time=0.018..0.019 rows=0 loops=5)

  • Filter: (routine_id = r.routine_id)
44. 0.040 0.065 ↓ 0.0 0 5

Limit (cost=0.00..0.22 rows=1 width=32) (actual time=0.012..0.013 rows=0 loops=5)

45. 0.025 0.025 ↓ 0.0 0 5

CTE Scan on wrca wrca_2 (cost=0.00..0.22 rows=1 width=32) (actual time=0.004..0.005 rows=0 loops=5)

  • Filter: ((routine_id = r.routine_id) AND ((answer -> 'naps'::text) <> '{}'::jsonb))