explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8coa

Settings
# exclusive inclusive rows x rows loops node
1. 0.281 87.053 ↑ 80.0 5 1

Nested Loop (cost=13.90..854,247.45 rows=400 width=152) (actual time=16.412..87.053 rows=5 loops=1)

2.          

CTE c

3. 0.045 0.058 ↑ 20.0 5 1

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual time=0.015..0.058 rows=5 loops=1)

4. 0.013 0.013 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.004..0.013 rows=1 loops=1)

5. 0.064 0.307 ↑ 20.0 5 1

Hash Left Join (cost=9.21..20.98 rows=100 width=32) (actual time=0.105..0.307 rows=5 loops=1)

  • Hash Cond: ((r.routine_id = wr.routine_id) AND (c.child = wr.child_id))
6. 0.058 0.197 ↑ 20.0 5 1

Nested Loop (cost=0.15..11.17 rows=100 width=28) (actual time=0.048..0.197 rows=5 loops=1)

7. 0.021 0.021 ↑ 1.0 1 1

Index Scan using routine_pkey on routine r (cost=0.15..8.17 rows=1 width=24) (actual time=0.010..0.021 rows=1 loops=1)

  • Index Cond: (routine_id = 1)
8. 0.118 0.118 ↑ 20.0 5 1

CTE Scan on c (cost=0.00..2.00 rows=100 width=4) (actual time=0.024..0.118 rows=5 loops=1)

9. 0.010 0.046 ↓ 0.0 0 1

Hash (cost=9.04..9.04 rows=1 width=12) (actual time=0.042..0.046 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
10. 0.036 0.036 ↓ 0.0 0 1

Index Scan using weekly_routine_date_class_idx on weekly_routine wr (cost=0.30..9.04 rows=1 width=12) (actual time=0.033..0.036 rows=0 loops=1)

  • Index Cond: ((routine_date >= (date_trunc('week'::text, ('2019-05-27'::date)::timestamp with time zone) - '1 day'::interval)) AND (routine_date < (date_trunc('week'::text, ('2019-05-27'::date)::timestamp with time zone) + '4 days'::int
  • Filter: (routine_id = 1)
11. 0.064 0.095 ↑ 4.0 1 5

Materialize (cost=4.18..12.66 rows=4 width=4) (actual time=0.012..0.019 rows=1 loops=5)

12. 0.017 0.031 ↑ 4.0 1 1

Bitmap Heap Scan on lang l (cost=4.18..12.64 rows=4 width=4) (actual time=0.022..0.031 rows=1 loops=1)

  • Recheck Cond: (locale = 'en_US'::text)
  • Heap Blocks: exact=1
13. 0.014 0.014 ↑ 4.0 1 1

Bitmap Index Scan on lang_locale_variant_key (cost=0.00..4.18 rows=4 width=0) (actual time=0.010..0.014 rows=1 loops=1)

  • Index Cond: (locale = 'en_US'::text)
14.          

SubPlan (for Nested Loop)

15. 0.090 58.085 ↑ 1.0 1 5

Aggregate (cost=1,533.22..1,533.23 rows=1 width=32) (actual time=11.612..11.617 rows=1 loops=5)

16. 1.485 57.995 ↓ 0.0 0 5

Nested Loop (cost=0.00..1,533.12 rows=8 width=29) (actual time=11.598..11.599 rows=0 loops=5)

  • Join Filter: (wrca.careplan_id = rc.careplan_id)
17. 0.825 0.825 ↑ 1.0 37 5

Seq Scan on routine_careplan rc (cost=0.00..1.48 rows=37 width=21) (actual time=0.009..0.165 rows=37 loops=5)

  • Filter: (title <> 'ssl_nap'::text)
  • Rows Removed by Filter: 1
18. 0.850 55.685 ↓ 0.0 0 185

Materialize (cost=0.00..1,527.22 rows=8 width=16) (actual time=0.301..0.301 rows=0 loops=185)

19. 54.835 54.835 ↓ 0.0 0 5

Seq Scan on weekly_routine_careplan_answer wrca (cost=0.00..1,527.18 rows=8 width=16) (actual time=10.966..10.967 rows=0 loops=5)

  • Filter: ((answer <> '""'::jsonb) AND (wr.weekly_routine_id = weekly_routine_id))
  • Rows Removed by Filter: 68479
20. 0.070 3.020 ↑ 1.0 1 5

Aggregate (cost=170.53..170.54 rows=1 width=32) (actual time=0.599..0.604 rows=1 loops=5)

21. 0.055 2.950 ↓ 0.0 0 5

Nested Loop (cost=160.24..170.51 rows=2 width=216) (actual time=0.589..0.590 rows=0 loops=5)

  • Join Filter: (wrs.routine_strategy_id = rs.routine_strategy_id)
22. 0.672 2.895 ↓ 0.0 0 5

Hash Join (cost=160.10..169.91 rows=2 width=220) (actual time=0.578..0.579 rows=0 loops=5)

  • Hash Cond: (rsl.routine_strategy_id = wrs.routine_strategy_id)
23. 0.018 0.018 ↑ 93.0 1 1

Seq Scan on routine_strategy_lang rsl (cost=0.00..9.32 rows=93 width=216) (actual time=0.015..0.018 rows=1 loops=1)

  • Filter: (lang_id = l.lang_id)
24. 0.070 2.205 ↓ 0.0 0 5

Hash (cost=160.07..160.07 rows=2 width=4) (actual time=0.440..0.441 rows=0 loops=5)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
25. 2.135 2.135 ↓ 0.0 0 5

Seq Scan on weekly_routine_strategy wrs (cost=0.00..160.07 rows=2 width=4) (actual time=0.427..0.427 rows=0 loops=5)

  • Filter: (wr.weekly_routine_id = weekly_routine_id)
  • Rows Removed by Filter: 7686
26. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (routine_strategy_id = rsl.routine_strategy_id)
  • Heap Fetches: 0
27. 0.070 2.205 ↑ 1.0 1 5

Aggregate (cost=218.69..218.70 rows=1 width=32) (actual time=0.436..0.441 rows=1 loops=5)

28. 0.040 2.135 ↓ 0.0 0 5

Nested Loop (cost=0.84..218.68 rows=1 width=64) (actual time=0.426..0.427 rows=0 loops=5)

  • Join Filter: (ra.curriculum_asset_id = ca.curriculum_asset_id)
29. 0.050 2.095 ↓ 0.0 0 5

Nested Loop (cost=0.55..218.25 rows=1 width=68) (actual time=0.418..0.419 rows=0 loops=5)

30. 0.055 2.045 ↓ 0.0 0 5

Nested Loop (cost=0.27..217.27 rows=1 width=8) (actual time=0.409..0.409 rows=0 loops=5)

31. 1.990 1.990 ↓ 0.0 0 5

Seq Scan on weekly_routine_asset wra (cost=0.00..208.95 rows=1 width=4) (actual time=0.397..0.398 rows=0 loops=5)

  • Filter: (wr.weekly_routine_id = weekly_routine_id)
  • Rows Removed by Filter: 6956
32. 0.000 0.000 ↓ 0.0 0

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

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

Index Scan using asset_content_curriculum_asset_id on asset_content ac (cost=0.28..0.97 rows=1 width=60) (never executed)

  • Index Cond: (curriculum_asset_id = ra.curriculum_asset_id)
  • Filter: (lang_id = l.lang_id)
34. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (curriculum_asset_id = ac.curriculum_asset_id)
  • Filter: (odl_version_id = 1)
35. 0.055 23.060 ↓ 0.0 0 5

Limit (cost=0.00..213.05 rows=1 width=32) (actual time=4.611..4.612 rows=0 loops=5)

36. 1.800 23.005 ↓ 0.0 0 5

Nested Loop (cost=0.00..1,704.36 rows=8 width=32) (actual time=4.600..4.601 rows=0 loops=5)

  • Join Filter: (wrca_1.careplan_id = rc_1.careplan_id)
37. 0.875 0.875 ↑ 1.0 38 5

Seq Scan on routine_careplan rc_1 (cost=0.00..1.38 rows=38 width=4) (actual time=0.009..0.175 rows=38 loops=5)

38. 0.960 20.330 ↓ 0.0 0 190

Materialize (cost=0.00..1,698.42 rows=8 width=16) (actual time=0.107..0.107 rows=0 loops=190)

39. 19.370 19.370 ↓ 0.0 0 5

Seq Scan on weekly_routine_careplan_answer wrca_1 (cost=0.00..1,698.38 rows=8 width=16) (actual time=3.873..3.874 rows=0 loops=5)

  • Filter: ((wr.weekly_routine_id = weekly_routine_id) AND ((answer -> 'naps'::text) <> '{}'::jsonb))
  • Rows Removed by Filter: 68479