explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aq5s : Optimization for: Optimization for: plan #ruZU; plan #TbMP

Settings

Optimization path:

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

Append (cost=5,013.11..5,135.80 rows=2 width=68) (actual time=1.311..2.216 rows=704 loops=1)

2.          

CTE client_days_product

3. 0.016 0.032 ↑ 1,666.7 60 1

Nested Loop (cost=0.00..1,286.79 rows=100,000 width=8) (actual time=0.009..0.032 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.006..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.005..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.001..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.065 0.197 ↑ 4.0 1 1

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

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

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

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

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

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

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

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

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

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

Materialize (cost=0.28..14.75 rows=1 width=26) (actual time=0.024..0.041 rows=1 loops=1)

18. 0.001 0.040 ↑ 1.0 1 1

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

19. 0.032 0.032 ↑ 1.0 1 1

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

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

CTE relevant_schedules

22. 0.016 0.388 ↓ 15.0 30 1

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

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

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

24. 0.038 0.300 ↑ 3.7 120 1

Sort (cost=3,356.64..3,357.75 rows=444 width=24) (actual time=0.295..0.300 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.053 0.262 ↑ 3.7 120 1

Hash Join (cost=27.12..3,337.12 rows=444 width=24) (actual time=0.180..0.262 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.045 0.045 ↑ 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.045 rows=60 loops=1)

27. 0.003 0.164 ↓ 1.2 10 1

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

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

Seq Scan on diary_schedules ds_1 (cost=0.00..27.02 rows=8 width=20) (actual time=0.023..0.161 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.002 0.108 ↑ 2.0 1 1

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

31. 0.003 0.101 ↑ 2.0 1 1

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

32. 0.005 0.098 ↓ 15.0 30 1

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

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

CTE Scan on relevant_schedules (cost=0.00..0.04 rows=2 width=4) (actual time=0.000..0.093 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.014 0.230 ↑ 3.0 1 1

Group (cost=27.73..27.76 rows=3 width=16) (actual time=0.211..0.230 rows=1 loops=1)

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

Sort (cost=27.73..27.74 rows=3 width=16) (actual time=0.209..0.216 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.188 ↓ 53.3 160 1

Nested Loop (cost=0.51..27.70 rows=3 width=16) (actual time=0.143..0.188 rows=160 loops=1)

39. 0.002 0.130 ↑ 1.0 1 1

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

  • Join Filter: (swc.schedule_id = ds_2.schedule_id)
40. 0.005 0.126 ↑ 2.0 1 1

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

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

CTE Scan on schedule_week_count swc (cost=0.00..0.04 rows=2 width=8) (actual time=0.105..0.108 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.008..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.041 0.041 ↑ 1.7 160 1

Index Scan using diary_schedules_slots_id_idx on diary_schedules_slots dss_1 (cost=0.28..8.52 rows=269 width=8) (actual time=0.016..0.041 rows=160 loops=1)

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

CTE schedules_occurrences

49. 0.013 0.964 ↑ 2.0 1 1

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

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

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

51. 0.007 0.263 ↑ 2.0 1 1

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

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

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

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

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

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

CTE Scan on schedule_weeks sw (cost=0.00..0.06 rows=3 width=12) (actual time=0.211..0.231 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.481 0.679 ↑ 51.3 78 1

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

58. 0.198 0.198 ↑ 4.0 1 1

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

59.          

CTE schedules

60. 0.022 1.299 ↓ 15.0 30 1

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

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

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

62. 0.002 0.967 ↑ 2.0 1 1

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

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

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

64. 0.136 2.163 ↓ 704.0 704 1

Nested Loop (cost=6.30..116.27 rows=1 width=68) (actual time=1.310..2.163 rows=704 loops=1)

65. 1.307 1.307 ↓ 15.0 30 1

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

66. 0.570 0.720 ↓ 23.0 23 30

Bitmap Heap Scan on diary_schedules_slots dss (cost=6.30..58.11 rows=1 width=58) (actual time=0.017..0.024 rows=23 loops=30)

  • Recheck Cond: (schedule_id = ds.schedule_id)
  • Filter: ((ds.week_nr = week_nr) AND (ds.weekday = (weekday)::double precision))
  • Rows Removed by Filter: 137
  • Heap Blocks: exact=90
67. 0.150 0.150 ↑ 1.7 160 30

Bitmap Index Scan on diary_schedules_slots_id_idx (cost=0.00..6.30 rows=269 width=0) (actual time=0.005..0.005 rows=160 loops=30)

  • Index Cond: (schedule_id = ds.schedule_id)
68. 0.001 0.007 ↓ 0.0 0 1

Bitmap Heap Scan on diary_custom_slots dcs (cost=4.19..12.68 rows=1 width=68) (actual time=0.007..0.007 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.006 0.006 ↓ 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.006..0.006 rows=0 loops=1)

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