explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TbMP : Optimization for: plan #ruZU

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.046 2.568 ↓ 352.0 704 1

Append (cost=5,010.36..5,144.62 rows=2 width=68) (actual time=1.920..2.568 rows=704 loops=1)

2.          

CTE client_days_product

3. 0.017 0.044 ↑ 1,666.7 60 1

Nested Loop (cost=0.00..1,286.79 rows=100,000 width=8) (actual time=0.020..0.044 rows=60 loops=1)

4. 0.006 0.027 ↑ 33.3 30 1

Result (cost=0.00..25.02 rows=1,000 width=4) (actual time=0.016..0.027 rows=30 loops=1)

5. 0.020 0.021 ↑ 33.3 30 1

ProjectSet (cost=0.00..5.02 rows=1,000 width=8) (actual time=0.015..0.021 rows=30 loops=1)

6. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)

7. 0.000 0.000 ↑ 50.0 2 30

Materialize (cost=0.00..2.02 rows=100 width=4) (actual time=0.000..0.000 rows=2 loops=30)

8. 0.002 0.002 ↑ 50.0 2 1

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual time=0.001..0.002 rows=2 loops=1)

9. 0.000 0.000 ↑ 1.0 1 1

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

10.          

CTE rrule_week_start

11. 0.054 0.293 ↑ 4.0 1 1

Nested Loop Left Join (cost=9.82..39.30 rows=4 width=32) (actual time=0.162..0.293 rows=1 loops=1)

  • Join Filter: ((s2.category)::text = (s1.category)::text)
12. 0.012 0.201 ↑ 4.0 1 1

Hash Right Join (cost=9.54..23.47 rows=4 width=26) (actual time=0.078..0.201 rows=1 loops=1)

  • Hash Cond: (sc1.setting_id = s1.setting_id)
13. 0.120 0.143 ↑ 1.0 16 1

Bitmap Heap Scan on settings_columns sc1 (cost=4.40..18.29 rows=16 width=12) (actual time=0.027..0.143 rows=16 loops=1)

  • Recheck Cond: ((name)::text = 'start_week'::text)
  • Heap Blocks: exact=11
14. 0.023 0.023 ↓ 1.4 23 1

Bitmap Index Scan on settings_columns_setting_name_idx (cost=0.00..4.40 rows=16 width=0) (actual time=0.023..0.023 rows=23 loops=1)

  • Index Cond: ((name)::text = 'start_week'::text)
15. 0.001 0.046 ↑ 4.0 1 1

Hash (cost=5.09..5.09 rows=4 width=22) (actual time=0.046..0.046 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.045 0.045 ↑ 4.0 1 1

Seq Scan on settings s1 (cost=0.00..5.09 rows=4 width=22) (actual time=0.016..0.045 rows=1 loops=1)

  • Filter: ((group_id IS NULL) AND (client_alias_id IS NULL) AND ((category)::text = 'diary.general'::text))
  • Rows Removed by Filter: 166
17. 0.002 0.038 ↑ 1.0 1 1

Materialize (cost=0.28..14.75 rows=1 width=26) (actual time=0.030..0.038 rows=1 loops=1)

18. 0.001 0.036 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.28..14.75 rows=1 width=26) (actual time=0.028..0.036 rows=1 loops=1)

19. 0.023 0.023 ↑ 1.0 1 1

Seq Scan on settings s2 (cost=0.00..5.50 rows=1 width=22) (actual time=0.015..0.023 rows=1 loops=1)

  • Filter: ((is_active IS TRUE) AND ((category)::text = 'diary.general'::text) AND (group_id = 1036))
  • Rows Removed by Filter: 166
20. 0.012 0.012 ↑ 1.0 1 1

Index Scan using settings_columns_unique on settings_columns sc2 (cost=0.28..8.30 rows=1 width=12) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: ((s2.setting_id = setting_id) AND ((name)::text = 'start_week'::text))
21.          

CTE relevant_schedules

22. 0.015 0.380 ↓ 15.0 30 1

Subquery Scan on schedule_days (cost=3,356.64..3,373.30 rows=2 width=32) (actual time=0.285..0.380 rows=30 loops=1)

  • Filter: (schedule_days.schedule_priority = 1)
  • Rows Removed by Filter: 90
23. 0.085 0.365 ↑ 3.7 120 1

WindowAgg (cost=3,356.64..3,367.74 rows=444 width=165) (actual time=0.282..0.365 rows=120 loops=1)

24. 0.034 0.280 ↑ 3.7 120 1

Sort (cost=3,356.64..3,357.75 rows=444 width=24) (actual time=0.275..0.280 rows=120 loops=1)

  • Sort Key: cdp.relevant_day, cdp.relevant_client_alias, ds_1.date_to, ds_1.date_from DESC
  • Sort Method: quicksort Memory: 34kB
25. 0.046 0.246 ↑ 3.7 120 1

Hash Join (cost=27.12..3,337.12 rows=444 width=24) (actual time=0.170..0.246 rows=120 loops=1)

  • Hash Cond: (cdp.relevant_client_alias = ds_1.client_alias_id)
  • Join Filter: ((ds_1.date_from <= cdp.relevant_day) AND (COALESCE(ds_1.date_to, '2099-01-01'::date) >= cdp.relevant_day))
  • Rows Removed by Join Filter: 180
26. 0.056 0.056 ↑ 1,666.7 60 1

CTE Scan on client_days_product cdp (cost=0.00..2,000.00 rows=100,000 width=8) (actual time=0.020..0.056 rows=60 loops=1)

27. 0.003 0.144 ↓ 1.2 10 1

Hash (cost=27.02..27.02 rows=8 width=20) (actual time=0.144..0.144 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 0.141 0.141 ↓ 1.2 10 1

Seq Scan on diary_schedules ds_1 (cost=0.00..27.02 rows=8 width=20) (actual time=0.007..0.141 rows=10 loops=1)

  • Filter: ((client_alias_id = ANY ('{1,25}'::integer[])) AND (allocation_type = 'COLLEAGUE'::diary_allocation_type))
  • Rows Removed by Filter: 58
29.          

CTE schedule_week_count

30. 0.003 0.121 ↑ 2.0 1 1

Nested Loop (cost=0.19..16.85 rows=2 width=8) (actual time=0.117..0.121 rows=1 loops=1)

31. 0.002 0.113 ↑ 2.0 1 1

Unique (cost=0.05..0.06 rows=2 width=4) (actual time=0.109..0.113 rows=1 loops=1)

32. 0.006 0.111 ↓ 15.0 30 1

Sort (cost=0.05..0.06 rows=2 width=4) (actual time=0.109..0.111 rows=30 loops=1)

  • Sort Key: relevant_schedules.schedule_id
  • Sort Method: quicksort Memory: 26kB
33. 0.105 0.105 ↓ 15.0 30 1

CTE Scan on relevant_schedules (cost=0.00..0.04 rows=2 width=4) (actual time=0.000..0.105 rows=30 loops=1)

34. 0.005 0.005 ↑ 1.0 1 1

Index Scan using diary_schedules_pkey on diary_schedules (cost=0.14..8.16 rows=1 width=1,027) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (schedule_id = relevant_schedules.schedule_id)
35.          

CTE schedule_weeks

36. 0.036 0.292 ↑ 3.0 1 1

Group (cost=31.20..31.23 rows=3 width=16) (actual time=0.248..0.292 rows=1 loops=1)

  • Group Key: ds_2.schedule_id, dss_1.week_nr, swc.week_count
37. 0.030 0.256 ↓ 53.3 160 1

Sort (cost=31.20..31.21 rows=3 width=16) (actual time=0.247..0.256 rows=160 loops=1)

  • Sort Key: ds_2.schedule_id, dss_1.week_nr, swc.week_count
  • Sort Method: quicksort Memory: 32kB
38. 0.018 0.226 ↓ 53.3 160 1

Nested Loop (cost=0.51..31.18 rows=3 width=16) (actual time=0.171..0.226 rows=160 loops=1)

39. 0.002 0.143 ↑ 1.0 1 1

Nested Loop (cost=0.23..16.50 rows=1 width=20) (actual time=0.139..0.143 rows=1 loops=1)

  • Join Filter: (swc.schedule_id = ds_2.schedule_id)
40. 0.003 0.139 ↑ 2.0 1 1

Hash Join (cost=0.08..0.15 rows=2 width=12) (actual time=0.135..0.139 rows=1 loops=1)

  • Hash Cond: (swc.schedule_id = relevant_schedules_1.schedule_id)
41. 0.121 0.121 ↑ 2.0 1 1

CTE Scan on schedule_week_count swc (cost=0.00..0.04 rows=2 width=8) (actual time=0.117..0.121 rows=1 loops=1)

42. 0.002 0.015 ↑ 2.0 1 1

Hash (cost=0.06..0.06 rows=2 width=4) (actual time=0.014..0.015 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.002 0.013 ↑ 2.0 1 1

Unique (cost=0.05..0.06 rows=2 width=4) (actual time=0.009..0.013 rows=1 loops=1)

44. 0.006 0.011 ↓ 15.0 30 1

Sort (cost=0.05..0.06 rows=2 width=4) (actual time=0.009..0.011 rows=30 loops=1)

  • Sort Key: relevant_schedules_1.schedule_id
  • Sort Method: quicksort Memory: 26kB
45. 0.005 0.005 ↓ 15.0 30 1

CTE Scan on relevant_schedules relevant_schedules_1 (cost=0.00..0.04 rows=2 width=4) (actual time=0.001..0.005 rows=30 loops=1)

46. 0.002 0.002 ↑ 1.0 1 1

Index Scan using diary_schedules_pkey on diary_schedules ds_2 (cost=0.14..8.16 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)

  • Index Cond: (schedule_id = relevant_schedules_1.schedule_id)
47. 0.065 0.065 ↑ 1.7 160 1

Index Scan using diary_schedules_slots_id_idx on diary_schedules_slots dss_1 (cost=0.28..11.99 rows=269 width=8) (actual time=0.030..0.065 rows=160 loops=1)

  • Index Cond: (schedule_id = ds_2.schedule_id)
48.          

CTE schedules_occurrences

49. 0.022 1.154 ↑ 2.0 1 1

GroupAggregate (cost=0.17..262.66 rows=2 width=40) (actual time=1.154..1.154 rows=1 loops=1)

  • Group Key: rs.schedule_id, sw.week_nr
50. 0.009 1.132 ↑ 102.6 78 1

Nested Loop (cost=0.17..202.64 rows=8,000 width=12) (actual time=0.964..1.132 rows=78 loops=1)

51. 0.011 0.332 ↑ 2.0 1 1

GroupAggregate (cost=0.17..0.22 rows=2 width=20) (actual time=0.332..0.332 rows=1 loops=1)

  • Group Key: rs.schedule_id, sw.week_nr, sw.week_count
52. 0.009 0.321 ↓ 15.0 30 1

Sort (cost=0.17..0.17 rows=2 width=20) (actual time=0.319..0.321 rows=30 loops=1)

  • Sort Key: rs.schedule_id, sw.week_nr, sw.week_count
  • Sort Method: quicksort Memory: 26kB
53. 0.009 0.312 ↓ 15.0 30 1

Hash Join (cost=0.07..0.16 rows=2 width=20) (actual time=0.263..0.312 rows=30 loops=1)

  • Hash Cond: (sw.schedule_id = rs.schedule_id)
54. 0.293 0.293 ↑ 3.0 1 1

CTE Scan on schedule_weeks sw (cost=0.00..0.06 rows=3 width=12) (actual time=0.249..0.293 rows=1 loops=1)

55. 0.004 0.010 ↓ 15.0 30 1

Hash (cost=0.04..0.04 rows=2 width=12) (actual time=0.010..0.010 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
56. 0.006 0.006 ↓ 15.0 30 1

CTE Scan on relevant_schedules rs (cost=0.00..0.04 rows=2 width=12) (actual time=0.001..0.006 rows=30 loops=1)

57. 0.497 0.791 ↑ 51.3 78 1

ProjectSet (cost=0.00..21.20 rows=4,000 width=4) (actual time=0.630..0.791 rows=78 loops=1)

58. 0.294 0.294 ↑ 4.0 1 1

CTE Scan on rrule_week_start (cost=0.00..0.08 rows=4 width=32) (actual time=0.163..0.294 rows=1 loops=1)

59.          

CTE schedules

60. 0.019 1.466 ↓ 15.0 30 1

Hash Left Join (cost=0.07..0.16 rows=2 width=28) (actual time=1.449..1.466 rows=30 loops=1)

  • Hash Cond: (rs_1.schedule_id = so.schedule_id)
  • Join Filter: (rs_1.schedule_date = ANY (so.occurrences))
61. 0.289 0.289 ↓ 15.0 30 1

CTE Scan on relevant_schedules rs_1 (cost=0.00..0.04 rows=2 width=24) (actual time=0.286..0.289 rows=30 loops=1)

62. 0.002 1.158 ↑ 2.0 1 1

Hash (cost=0.04..0.04 rows=2 width=40) (actual time=1.158..1.158 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
63. 1.156 1.156 ↑ 2.0 1 1

CTE Scan on schedules_occurrences so (cost=0.00..0.04 rows=2 width=40) (actual time=1.156..1.156 rows=1 loops=1)

64. 0.561 2.519 ↓ 704.0 704 1

Hash Join (cost=0.08..121.63 rows=1 width=68) (actual time=1.920..2.519 rows=704 loops=1)

  • Hash Cond: ((dss.schedule_id = ds.schedule_id) AND (dss.week_nr = ds.week_nr) AND ((dss.weekday)::double precision = ds.weekday))
65. 0.474 0.474 ↑ 1.0 3,224 1

Seq Scan on diary_schedules_slots dss (cost=0.00..81.24 rows=3,224 width=58) (actual time=0.026..0.474 rows=3,224 loops=1)

66. 0.007 1.484 ↓ 15.0 30 1

Hash (cost=0.04..0.04 rows=2 width=28) (actual time=1.483..1.484 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
67. 1.477 1.477 ↓ 15.0 30 1

CTE Scan on schedules ds (cost=0.00..0.04 rows=2 width=28) (actual time=1.450..1.477 rows=30 loops=1)

68. 0.000 0.003 ↓ 0.0 0 1

Bitmap Heap Scan on diary_custom_slots dcs (cost=4.19..12.68 rows=1 width=68) (actual time=0.003..0.003 rows=0 loops=1)

  • Recheck Cond: ((slot_date >= '2020-01-01'::date) AND (slot_date <= '2020-01-30'::date))
  • Filter: ((deleted IS FALSE) AND (client_alias_id = ANY ('{1,25}'::integer[])) AND (allocation_type = 'COLLEAGUE'::diary_allocation_type))
69. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on diary_custom_slots_slot_date_idx (cost=0.00..4.19 rows=4 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((slot_date >= '2020-01-01'::date) AND (slot_date <= '2020-01-30'::date))
Planning time : 1.412 ms
Execution time : 2.788 ms