explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Smpg

Settings
# exclusive inclusive rows x rows loops node
1. 22.094 202.022 ↓ 41.7 1,920 1

Nested Loop Left Join (cost=33,215.20..41,308.95 rows=46 width=184) (actual time=145.235..202.022 rows=1,920 loops=1)

  • Join Filter: (cmp_readings_start.read_type_id = cmp_readings_end.read_type_id)
  • Rows Removed by Join Filter: 1920
2. 12.683 166.488 ↓ 41.7 1,920 1

Nested Loop Left Join (cost=33,210.83..37,257.28 rows=46 width=642) (actual time=145.191..166.488 rows=1,920 loops=1)

3. 0.671 146.125 ↓ 20.9 960 1

Merge Left Join (cost=33,206.45..33,207.33 rows=46 width=630) (actual time=145.132..146.125 rows=960 loops=1)

  • Merge Cond: (n.id = readings.node_id)
4. 0.770 6.576 ↓ 10.4 480 1

Sort (cost=10,216.29..10,216.40 rows=46 width=626) (actual time=6.468..6.576 rows=480 loops=1)

  • Sort Key: n.id
  • Sort Method: quicksort Memory: 152kB
5. 0.343 5.806 ↓ 10.4 480 1

Hash Left Join (cost=10,079.78..10,215.02 rows=46 width=626) (actual time=0.738..5.806 rows=480 loops=1)

  • Hash Cond: (n.id = nodes.id)
6. 3.812 5.436 ↓ 10.4 480 1

Hash Join (cost=10,068.43..10,203.54 rows=46 width=568) (actual time=0.685..5.436 rows=480 loops=1)

  • Hash Cond: (state_changes.node_id = n.id)
  • 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 9)))::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 9)))::timestamp with time zone))))
  • Rows Removed by Join Filter: 1080
7. 1.077 1.077 ↑ 1.8 520 1

Seq Scan on state_changes (cost=0.00..119.76 rows=956 width=24) (actual time=0.054..1.077 rows=520 loops=1)

  • Filter: ((((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))) AND (node_id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[])))
  • Rows Removed by Filter: 2334
8. 0.023 0.547 ↓ 1.0 30 1

Hash (cost=10,068.07..10,068.07 rows=29 width=548) (actual time=0.547..0.547 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
9. 0.018 0.524 ↓ 1.0 30 1

Nested Loop (cost=10,037.78..10,068.07 rows=29 width=548) (actual time=0.482..0.524 rows=30 loops=1)

  • Join Filter: ((((NULL::timestamp without time zone) IS NULL) AND (((SubPlan 9)) IS NULL)) OR (((NULL::timestamp without time zone) IS NULL) AND (((SubPlan 9)) > (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 9)) 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 9)) > (timezone((work_shift.time_zone)::text, ("*VALUES*".column1 + work_shift.start_time)))::timestamp without time zone)))
10. 0.031 0.098 ↓ 3.0 3 1

Nested Loop (cost=12.01..24.03 rows=1 width=496) (actual time=0.075..0.098 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
11. 0.032 0.055 ↓ 3.0 3 1

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

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

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

13. 0.007 0.016 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 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)
15. 0.012 0.012 ↑ 1.0 2 3

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

16. 0.032 0.408 ↑ 25.2 10 3

Sort (cost=10,025.77..10,026.40 rows=252 width=56) (actual time=0.135..0.136 rows=10 loops=3)

  • Sort Key: ((n.id * 1000000))
  • Sort Method: quicksort Memory: 25kB
17. 0.030 0.376 ↑ 25.2 10 1

HashAggregate (cost=10,013.20..10,015.72 rows=252 width=56) (actual time=0.369..0.376 rows=10 loops=1)

  • Group Key: n.id, ((n.id * 1000000)), (NULL::timestamp without time zone), ((SubPlan 9)), (string_agg((((ncd.code)::text || '='::text) || (ncd.default_value)::text), ';'::text))
18. 0.003 0.346 ↑ 25.2 10 1

Append (cost=22.70..10,010.05 rows=252 width=56) (actual time=0.240..0.346 rows=10 loops=1)

19. 0.121 0.338 ↑ 1.0 10 1

GroupAggregate (cost=22.70..64.85 rows=10 width=56) (actual time=0.239..0.338 rows=10 loops=1)

  • Group Key: n.id
20. 0.063 0.157 ↓ 5.0 50 1

Sort (cost=22.70..22.73 rows=10 width=130) (actual time=0.151..0.157 rows=50 loops=1)

  • Sort Key: n.id
  • Sort Method: quicksort Memory: 27kB
21. 0.050 0.094 ↓ 5.0 50 1

Hash Right Join (cost=11.47..22.54 rows=10 width=130) (actual time=0.070..0.094 rows=50 loops=1)

  • Hash Cond: ((ncd.node_type_code)::text = (n.node_type_code)::text)
22. 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.005..0.006 rows=5 loops=1)

23. 0.016 0.038 ↑ 1.0 10 1

Hash (cost=11.35..11.35 rows=10 width=62) (actual time=0.038..0.038 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.022 0.022 ↑ 1.0 10 1

Seq Scan on nodes n (cost=0.00..11.35 rows=10 width=62) (actual time=0.017..0.022 rows=10 loops=1)

  • Filter: (id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))
25.          

SubPlan (forGroupAggregate)

26. 0.010 0.060 ↑ 1.0 1 10

Result (cost=4.17..4.18 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=10)

27.          

Initplan (forResult)

28. 0.010 0.050 ↓ 0.0 0 10

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

29. 0.040 0.040 ↓ 0.0 0 10

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.004..0.004 rows=0 loops=10)

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

Hash Join (cost=11.47..9,942.67 rows=242 width=56) (actual time=0.005..0.005 rows=0 loops=1)

  • Hash Cond: (nc.node_id = n_1.id)
31. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on node_configuration nc (cost=0.00..24.50 rows=1,450 width=20) (actual time=0.004..0.004 rows=0 loops=1)

32. 0.000 0.000 ↓ 0.0 0

Hash (cost=11.35..11.35 rows=10 width=4) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Seq Scan on nodes n_1 (cost=0.00..11.35 rows=10 width=4) (never executed)

  • Filter: (id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))
34.          

SubPlan (forHash Join)

35. 0.000 0.000 ↓ 0.0 0

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

36.          

Initplan (forResult)

37. 0.000 0.000 ↓ 0.0 0

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

38. 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
39. 0.000 0.000 ↓ 0.0 0

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

40. 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))))
41. 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))
42. 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)
43. 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)

44. 0.000 0.000 ↓ 0.0 0

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

45. 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)
46. 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
47. 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
48. 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)
49. 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)
50. 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))
51. 0.012 0.027 ↑ 6.0 10 1

Hash (cost=10.60..10.60 rows=60 width=62) (actual time=0.027..0.027 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 0.015 0.015 ↑ 6.0 10 1

Seq Scan on nodes (cost=0.00..10.60 rows=60 width=62) (actual time=0.011..0.015 rows=10 loops=1)

53. 0.193 138.878 ↓ 45.5 911 1

Materialize (cost=22,990.17..22,990.72 rows=20 width=8) (actual time=138.657..138.878 rows=911 loops=1)

54. 0.020 138.685 ↑ 1.0 20 1

Group (cost=22,990.17..22,990.47 rows=20 width=8) (actual time=138.650..138.685 rows=20 loops=1)

  • Group Key: readings.node_id, readings.read_type_id
55. 0.000 138.665 ↓ 1.5 60 1

Sort (cost=22,990.17..22,990.27 rows=40 width=8) (actual time=138.649..138.665 rows=60 loops=1)

  • Sort Key: readings.node_id, readings.read_type_id
  • Sort Method: quicksort Memory: 27kB
56. 4.205 138.675 ↓ 1.5 60 1

Gather (cost=22,984.90..22,989.10 rows=40 width=8) (actual time=138.556..138.675 rows=60 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
57. 61.400 134.470 ↑ 1.0 20 3

Partial HashAggregate (cost=21,984.90..21,985.10 rows=20 width=8) (actual time=134.466..134.470 rows=20 loops=3)

  • Group Key: readings.node_id, readings.read_type_id
58. 73.070 73.070 ↑ 1.2 321,103 3

Parallel Seq Scan on readings (cost=0.00..19,978.01 rows=401,378 width=8) (actual time=0.019..73.070 rows=321,103 loops=3)

  • Filter: (node_id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))
59. 0.000 7.680 ↑ 1.0 2 960

Index Scan using ts_end_idx on readings cmp_readings_end (cost=4.38..88.02 rows=2 width=24) (actual time=0.005..0.008 rows=2 loops=960)

  • Index Cond: (ts_end = (SubPlan 2))
  • Filter: (node_id = n.id)
  • Rows Removed by Filter: 18
60.          

SubPlan (forIndex Scan)

61. 0.000 10.560 ↑ 1.0 1 960

Result (cost=3.94..3.95 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=960)

62.          

Initplan (forResult)

63. 1.920 10.560 ↑ 1.0 1 960

Limit (cost=0.45..3.94 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=960)

64. 8.640 8.640 ↑ 16,055.0 1 960

Index Scan Backward using ts_end_idx on readings readings_1 (cost=0.45..56,120.92 rows=16,055 width=8) (actual time=0.009..0.009 rows=1 loops=960)

  • Index Cond: ((ts_end IS NOT NULL) AND (ts_end <= (LEAST(LEAST(timezone((work_shift.time_zone)::text, (("*VALUES*".column1 + work_shift.start_time) + (work_shift.duration)::interval)), (((SubPlan 9)))::timestamp with time zone), (state_changes.ts_end)::timestamp with time zone))::timestamp without time zone))
  • Filter: ((node_id = n.id) AND (read_type_id = readings.read_type_id))
  • Rows Removed by Filter: 10
65. 0.000 13.440 ↑ 1.0 2 1,920

Index Scan using ts_start_idx on readings cmp_readings_start (cost=4.37..88.02 rows=2 width=24) (actual time=0.004..0.007 rows=2 loops=1,920)

  • Index Cond: (ts_start = (SubPlan 4))
  • Filter: (node_id = n.id)
  • Rows Removed by Filter: 18
66.          

SubPlan (forIndex Scan)

67. 0.000 17.280 ↑ 1.0 1 1,920

Result (cost=3.94..3.95 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1,920)

68.          

Initplan (forResult)

69. 1.920 17.280 ↑ 1.0 1 1,920

Limit (cost=0.44..3.94 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1,920)

70. 15.360 15.360 ↑ 16,055.0 1 1,920

Index Scan using ts_start_idx on readings readings_2 (cost=0.44..56,120.92 rows=16,055 width=8) (actual time=0.008..0.008 rows=1 loops=1,920)

  • Index Cond: ((ts_start IS NOT NULL) AND (ts_start >= (GREATEST(GREATEST(timezone((work_shift.time_zone)::text, ("*VALUES*".column1 + work_shift.start_time)), ((NULL::timestamp without time zone))::timestamp with time zone), (state_changes.ts_start)::timestamp with time zone))::timestamp without time zone))
  • Filter: ((node_id = n.id) AND (read_type_id = readings.read_type_id))
  • Rows Removed by Filter: 9
Planning time : 10.874 ms
Execution time : 203.450 ms