explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ruZU

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.047 3.101 ↓ 352.0 704 1

Append (cost=5,013.39..5,247.74 rows=2 width=68) (actual time=1.867..3.101 rows=704 loops=1)

2.          

CTE client_days_product

3. 0.018 0.034 ↑ 1,666.7 60 1

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

4. 0.005 0.016 ↑ 33.3 30 1

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

5. 0.010 0.011 ↑ 33.3 30 1

ProjectSet (cost=0.00..5.02 rows=1,000 width=8) (actual time=0.004..0.011 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.056 0.154 ↑ 4.0 1 1

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

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

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

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

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

  • Recheck Cond: ((name)::text = 'start_week'::text)
  • Heap Blocks: exact=11
14. 0.011 0.011 ↓ 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.011..0.011 rows=23 loops=1)

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

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

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

Seq Scan on settings s1 (cost=0.00..5.09 rows=4 width=22) (actual time=0.007..0.024 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.001 0.033 ↑ 1.0 1 1

Materialize (cost=0.28..14.75 rows=1 width=26) (actual time=0.022..0.033 rows=1 loops=1)

18. 0.002 0.032 ↑ 1.0 1 1

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

19. 0.025 0.025 ↑ 1.0 1 1

Seq Scan on settings s2 (cost=0.00..5.50 rows=1 width=22) (actual time=0.015..0.025 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.005 0.005 ↑ 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.005..0.005 rows=1 loops=1)

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

CTE relevant_schedules

22. 0.019 0.443 ↓ 15.0 30 1

Subquery Scan on schedule_days (cost=3,356.66..3,373.32 rows=2 width=32) (actual time=0.335..0.443 rows=30 loops=1)

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

WindowAgg (cost=3,356.66..3,367.76 rows=444 width=165) (actual time=0.331..0.424 rows=120 loops=1)

24. 0.038 0.330 ↑ 3.7 120 1

Sort (cost=3,356.66..3,357.77 rows=444 width=24) (actual time=0.324..0.330 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.061 0.292 ↑ 3.7 120 1

Hash Join (cost=27.14..3,337.14 rows=444 width=24) (actual time=0.199..0.292 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: 390
26. 0.046 0.046 ↑ 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.009..0.046 rows=60 loops=1)

27. 0.005 0.185 ↓ 2.1 17 1

Hash (cost=27.04..27.04 rows=8 width=20) (actual time=0.185..0.185 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 0.180 0.180 ↓ 2.1 17 1

Seq Scan on diary_schedules ds_1 (cost=0.00..27.04 rows=8 width=20) (actual time=0.020..0.180 rows=17 loops=1)

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

CTE schedule_week_count

30. 0.002 0.134 ↑ 2.0 1 1

Nested Loop (cost=0.19..16.84 rows=2 width=8) (actual time=0.131..0.134 rows=1 loops=1)

31. 0.003 0.127 ↑ 2.0 1 1

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

32. 0.006 0.124 ↓ 15.0 30 1

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

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

CTE Scan on relevant_schedules (cost=0.00..0.04 rows=2 width=4) (actual time=0.001..0.118 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,034) (actual time=0.005..0.005 rows=1 loops=1)

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

CTE schedule_weeks

36. 0.014 0.274 ↑ 5.0 1 1

Group (cost=34.15..34.20 rows=5 width=16) (actual time=0.254..0.274 rows=1 loops=1)

  • Group Key: ds_2.schedule_id, dss_1.week_nr, swc.week_count
37. 0.028 0.260 ↓ 32.0 160 1

Sort (cost=34.15..34.16 rows=5 width=16) (actual time=0.253..0.260 rows=160 loops=1)

  • Sort Key: ds_2.schedule_id, dss_1.week_nr, swc.week_count
  • Sort Method: quicksort Memory: 32kB
38. 0.017 0.232 ↓ 32.0 160 1

Nested Loop (cost=0.51..34.09 rows=5 width=16) (actual time=0.180..0.232 rows=160 loops=1)

39. 0.003 0.157 ↑ 1.0 1 1

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

  • Join Filter: (swc.schedule_id = ds_2.schedule_id)
40. 0.004 0.152 ↑ 2.0 1 1

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

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

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

42. 0.001 0.013 ↑ 2.0 1 1

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

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

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

44. 0.006 0.010 ↓ 15.0 30 1

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

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

CTE Scan on relevant_schedules relevant_schedules_1 (cost=0.00..0.04 rows=2 width=4) (actual time=0.000..0.004 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.058 0.058 ↑ 1.8 160 1

Index Scan using diary_schedules_slots_id_idx on diary_schedules_slots dss_1 (cost=0.28..14.74 rows=286 width=8) (actual time=0.027..0.058 rows=160 loops=1)

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

CTE schedules_occurrences

49. 0.012 0.974 ↑ 2.0 1 1

GroupAggregate (cost=0.21..262.71 rows=2 width=40) (actual time=0.974..0.974 rows=1 loops=1)

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

Nested Loop (cost=0.21..202.68 rows=8,000 width=12) (actual time=0.892..0.962 rows=78 loops=1)

51. 0.007 0.306 ↑ 2.0 1 1

GroupAggregate (cost=0.21..0.26 rows=2 width=20) (actual time=0.306..0.306 rows=1 loops=1)

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

Sort (cost=0.21..0.22 rows=2 width=20) (actual time=0.298..0.299 rows=30 loops=1)

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

Hash Join (cost=0.07..0.20 rows=2 width=20) (actual time=0.268..0.291 rows=30 loops=1)

  • Hash Cond: (sw.schedule_id = rs.schedule_id)
54. 0.274 0.274 ↑ 5.0 1 1

CTE Scan on schedule_weeks sw (cost=0.00..0.10 rows=5 width=12) (actual time=0.255..0.274 rows=1 loops=1)

55. 0.004 0.009 ↓ 15.0 30 1

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

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

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

57. 0.492 0.647 ↑ 51.3 78 1

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

58. 0.155 0.155 ↑ 4.0 1 1

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

59.          

CTE schedules

60. 0.021 1.339 ↓ 15.0 30 1

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

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

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

62. 0.001 0.977 ↑ 2.0 1 1

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

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

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

64. 0.847 3.050 ↓ 704.0 704 1

Hash Join (cost=0.08..221.72 rows=1 width=68) (actual time=1.866..3.050 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.846 0.846 ↑ 1.0 5,717 1

Seq Scan on diary_schedules_slots dss (cost=0.00..150.17 rows=5,717 width=58) (actual time=0.013..0.846 rows=5,717 loops=1)

66. 0.007 1.357 ↓ 15.0 30 1

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

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

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

68. 0.001 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on diary_custom_slots dcs (cost=4.19..12.68 rows=1 width=68) (actual time=0.004..0.004 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.356 ms
Execution time : 3.316 ms