explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cRYF

Settings
# exclusive inclusive rows x rows loops node
1. 0.742 893.177 ↓ 1.6 49 1

Nested Loop (cost=344.83..459,483.51 rows=31 width=204) (actual time=36.602..893.177 rows=49 loops=1)

  • Join Filter: (((state_changes.ts_end IS NULL) AND (state_changes.ts_start < (LEAST(timezone((work_shift.time_zone)::text, (("*VALUES*".column1 + work_shift.start_time) + (work_shift.duration)::interval)), (((SubPlan 6)))::timestamp with time zone))::timestamp without time zone)) OR (GREATEST((state_changes.ts_start)::timestamp with time zone, GREATEST(timezone((work_shift.time_zone)::text, ("*VALUES*".column1 + work_shift.start_time)), ((NULL::timestamp without time zone))::timestamp with time zone)) < LEAST((state_changes.ts_end)::timestamp with time zone, LEAST(timezone((work_shift.time_zone)::text, (("*VALUES*".column1 + work_shift.start_time) + (work_shift.duration)::interval)), (((SubPlan 6)))::timestamp with time zone))))
  • Rows Removed by Join Filter: 107
2. 0.007 0.330 ↓ 3.0 3 1

Nested Loop Left Join (cost=344.83..361.46 rows=1 width=606) (actual time=0.292..0.330 rows=3 loops=1)

3. 0.005 0.302 ↓ 3.0 3 1

Nested Loop (cost=344.69..357.29 rows=1 width=548) (actual time=0.275..0.302 rows=3 loops=1)

  • Join Filter: ((((NULL::timestamp without time zone) IS NULL) AND (((SubPlan 6)) IS NULL)) OR (((NULL::timestamp without time zone) IS NULL) AND (((SubPlan 6)) > (timezone((work_shift.time_zone)::text, ("*VALUES*".column1 + work_shift.start_time)))::timestamp without time zone)) OR (((NULL::timestamp without time zone) < (timezone((work_shift.time_zone)::text, (("*VALUES*".column1 + work_shift.start_time) + (work_shift.duration)::interval)))::timestamp without time zone) AND (((SubPlan 6)) IS NULL)) OR (((NULL::timestamp without time zone) < (timezone((work_shift.time_zone)::text, (("*VALUES*".column1 + work_shift.start_time) + (work_shift.duration)::interval)))::timestamp without time zone) AND (((SubPlan 6)) > (timezone((work_shift.time_zone)::text, ("*VALUES*".column1 + work_shift.start_time)))::timestamp without time zone)))
4. 0.023 0.108 ↓ 3.0 3 1

Nested Loop (cost=12.01..24.03 rows=1 width=496) (actual time=0.087..0.108 rows=3 loops=1)

  • Join Filter: ((timezone((work_shift.time_zone)::text, ("*VALUES*".column1 + work_shift.start_time)) < '2019-08-12 00:00:00+00'::timestamp with time zone) AND (timezone((work_shift.time_zone)::text, (("*VALUES*".column1 + work_shift.start_time) + (work_shift.duration)::interval)) > '2019-08-11 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Join Filter: 3
5. 0.032 0.073 ↓ 3.0 3 1

Hash Join (cost=12.01..23.95 rows=1 width=492) (actual time=0.065..0.073 rows=3 loops=1)

  • Hash Cond: ((work_shift.workshift_plan_code)::text = (work_shift_to_plant.workshift_plan_code)::text)
6. 0.021 0.021 ↑ 46.7 3 1

Seq Scan on work_shift (cost=0.00..11.40 rows=140 width=550) (actual time=0.020..0.021 rows=3 loops=1)

7. 0.011 0.020 ↑ 1.0 1 1

Hash (cost=12.00..12.00 rows=1 width=58) (actual time=0.020..0.020 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
8. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on work_shift_to_plant (cost=0.00..12.00 rows=1 width=58) (actual time=0.008..0.009 rows=1 loops=1)

  • Filter: ((plant_code)::text = 'DEFAULT_PLANT'::text)
9. 0.012 0.012 ↑ 1.0 2 3

Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=4) (actual time=0.002..0.004 rows=2 loops=3)

10. 0.023 0.189 ↑ 8.0 1 3

Sort (cost=332.68..332.70 rows=8 width=56) (actual time=0.062..0.063 rows=1 loops=3)

  • Sort Key: ((n.id * 1000000))
  • Sort Method: quicksort Memory: 25kB
11. 0.007 0.166 ↑ 8.0 1 1

HashAggregate (cost=332.48..332.56 rows=8 width=56) (actual time=0.166..0.166 rows=1 loops=1)

  • Group Key: n.id, ((n.id * 1000000)), (NULL::timestamp without time zone), ((SubPlan 6)), (string_agg((((ncd.code)::text || '='::text) || (ncd.default_value)::text), ';'::text))
12. 0.002 0.159 ↑ 8.0 1 1

Append (cost=8.17..332.38 rows=8 width=56) (actual time=0.133..0.159 rows=1 loops=1)

13. 0.045 0.132 ↑ 1.0 1 1

GroupAggregate (cost=8.17..23.28 rows=1 width=56) (actual time=0.132..0.132 rows=1 loops=1)

  • Group Key: n.id
14. 0.037 0.067 ↓ 5.0 5 1

Hash Right Join (cost=8.17..19.06 rows=1 width=130) (actual time=0.055..0.067 rows=5 loops=1)

  • Hash Cond: ((ncd.node_type_code)::text = (n.node_type_code)::text)
15. 0.006 0.006 ↑ 14.0 5 1

Seq Scan on node_configuration_domain ncd (cost=0.00..10.70 rows=70 width=184) (actual time=0.004..0.006 rows=5 loops=1)

16. 0.006 0.024 ↑ 1.0 1 1

Hash (cost=8.16..8.16 rows=1 width=62) (actual time=0.024..0.024 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.018 0.018 ↑ 1.0 1 1

Index Scan using nodes_pkey on nodes n (cost=0.14..8.16 rows=1 width=62) (actual time=0.017..0.018 rows=1 loops=1)

  • Index Cond: (id = 3)
18.          

SubPlan (forGroupAggregate)

19. 0.003 0.020 ↑ 1.0 1 1

Result (cost=4.17..4.18 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1)

20.          

Initplan (forResult)

21. 0.001 0.017 ↓ 0.0 0 1

Limit (cost=0.15..4.17 rows=1 width=8) (actual time=0.017..0.017 rows=0 loops=1)

22. 0.016 0.016 ↓ 0.0 0 1

Index Only Scan using node_configuration_node_id_ts_start_validity_field_id_key on node_configuration nc_1 (cost=0.15..28.29 rows=7 width=8) (actual time=0.016..0.016 rows=0 loops=1)

  • Index Cond: ((node_id = n.id) AND (ts_start_validity IS NOT NULL))
  • Heap Fetches: 0
23. 0.005 0.025 ↓ 0.0 0 1

Nested Loop (cost=4.35..309.02 rows=7 width=56) (actual time=0.025..0.025 rows=0 loops=1)

24. 0.017 0.017 ↑ 1.0 1 1

Index Only Scan using nodes_pkey on nodes n_1 (cost=0.14..8.16 rows=1 width=4) (actual time=0.016..0.017 rows=1 loops=1)

  • Index Cond: (id = 3)
  • Heap Fetches: 1
25. 0.001 0.003 ↓ 0.0 0 1

Bitmap Heap Scan on node_configuration nc (cost=4.21..14.35 rows=7 width=20) (actual time=0.003..0.003 rows=0 loops=1)

  • Recheck Cond: (node_id = 3)
26. 0.002 0.002 ↓ 0.0 0 1

Bitmap Index Scan on node_configuration_node_id_ts_start_validity_field_id_key (cost=0.00..4.21 rows=7 width=0) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (node_id = 3)
27.          

SubPlan (forNested Loop)

28. 0.000 0.000 ↓ 0.0 0

Result (cost=6.17..6.18 rows=1 width=8) (never executed)

29.          

Initplan (forResult)

30. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.15..6.17 rows=1 width=8) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Index Only Scan using node_configuration_node_id_ts_start_validity_field_id_key on node_configuration nc_next (cost=0.15..12.20 rows=2 width=8) (never executed)

  • Index Cond: ((node_id = n_1.id) AND (ts_start_validity IS NOT NULL) AND (ts_start_validity > nc.ts_start_validity))
  • Heap Fetches: 0
32. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=34.72..34.73 rows=1 width=32) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=22.77..34.71 rows=1 width=134) (never executed)

  • Join Filter: ((sub_nc.node_id = sub_n.id) AND (sub_nc.ts_start_validity = (max(subsub_nc.ts_start_validity))))
34. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=22.62..33.83 rows=1 width=142) (never executed)

  • Join Filter: ((subsub_nc.node_id = sub_n.id) AND (subsub_nc.field_id = sub_ncd.id))
35. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.17..19.06 rows=1 width=134) (never executed)

  • Hash Cond: ((sub_ncd.node_type_code)::text = (sub_n.node_type_code)::text)
36. 0.000 0.000 ↓ 0.0 0

Seq Scan on node_configuration_domain sub_ncd (cost=0.00..10.70 rows=70 width=188) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.16..8.16 rows=1 width=62) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Index Scan using nodes_pkey on nodes sub_n (cost=0.14..8.16 rows=1 width=62) (never executed)

  • Index Cond: (id = n_1.id)
39. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=14.45..14.59 rows=7 width=16) (never executed)

  • Group Key: subsub_nc.node_id, subsub_nc.field_id
40. 0.000 0.000 ↓ 0.0 0

Sort (cost=14.45..14.47 rows=7 width=16) (never executed)

  • Sort Key: subsub_nc.field_id
41. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on node_configuration subsub_nc (cost=4.21..14.35 rows=7 width=16) (never executed)

  • Recheck Cond: (node_id = n_1.id)
42. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on node_configuration_node_id_ts_start_validity_field_id_key (cost=0.00..4.21 rows=7 width=0) (never executed)

  • Index Cond: (node_id = n_1.id)
43. 0.000 0.000 ↓ 0.0 0

Index Scan using node_configuration_node_id_ts_start_validity_field_id_key on node_configuration sub_nc (cost=0.15..0.86 rows=1 width=24) (never executed)

  • Index Cond: ((node_id = n_1.id) AND (field_id = sub_ncd.id))
44. 0.021 0.021 ↑ 1.0 1 3

Index Scan using nodes_pkey on nodes (cost=0.14..4.16 rows=1 width=62) (actual time=0.007..0.007 rows=1 loops=3)

  • Index Cond: ((id = n.id) AND (id = 3))
45. 0.648 0.648 ↑ 1.8 52 3

Seq Scan on state_changes (cost=0.00..91.12 rows=93 width=24) (actual time=0.015..0.216 rows=52 loops=3)

  • Filter: ((node_id = 3) AND (((ts_end IS NULL) AND (ts_start < '2019-08-12 00:00:00'::timestamp without time zone)) OR (GREATEST(ts_start, '2019-08-11 00:00:00'::timestamp without time zone) < LEAST(ts_end, '2019-08-12 00:00:00'::timestamp without time zone))))
  • Rows Removed by Filter: 2798
46.          

SubPlan (forNested Loop)

47. 470.008 891.457 ↑ 1.0 2 49

HashAggregate (cost=14,807.19..14,807.21 rows=2 width=12) (actual time=18.193..18.193 rows=2 loops=49)

  • Group Key: readings.read_type_id
48. 261.562 421.449 ↓ 2.1 61,966 49

Bitmap Heap Scan on readings (cost=2,612.61..14,661.71 rows=29,096 width=12) (actual time=3.331..8.601 rows=61,966 loops=49)

  • Recheck Cond: ((node_id = n.id) AND (ts_end <= (LEAST(LEAST(timezone((work_shift.time_zone)::text, (("*VALUES*".column1 + work_shift.start_time) + (work_shift.duration)::interval)), (((SubPlan 6)))::timestamp with time zone), (state_changes.ts_end)::timestamp with time zone))::timestamp without time zone))
  • Heap Blocks: exact=34604
49. 159.887 159.887 ↓ 2.1 61,966 49

Bitmap Index Scan on readings_composite_node_id_read_type_id_sampling_level_ts_end_i (cost=0.00..2,605.34 rows=29,096 width=0) (actual time=3.263..3.263 rows=61,966 loops=49)

  • Index Cond: ((node_id = n.id) AND (ts_end <= (LEAST(LEAST(timezone((work_shift.time_zone)::text, (("*VALUES*".column1 + work_shift.start_time) + (work_shift.duration)::interval)), (((SubPlan 6)))::timestamp with time zone), (state_changes.ts_end)::timestamp with time zone))::timestamp without time zone))
Planning time : 5.002 ms
Execution time : 894.082 ms