explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7lB8

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 485.365 ↓ 1.9 29 1

Sort (cost=4,277.32..4,277.36 rows=15 width=75) (actual time=485.098..485.365 rows=29 loops=1)

  • Sort Key: codh.acquisition_time, codh.operation_data_id
  • Sort Method: quicksort Memory: 27kB
2.          

CTE target

3. 0.211 44.739 ↑ 1.0 6 1

HashAggregate (cost=450.16..450.22 rows=6 width=8) (actual time=44.682..44.739 rows=6 loops=1)

  • Group Key: memp.operation_data_id, ('KEYSWON'::text)
4. 0.177 44.528 ↑ 1.0 6 1

Append (cost=4.52..450.13 rows=6 width=8) (actual time=4.647..44.528 rows=6 loops=1)

5. 0.221 6.047 ↑ 1.0 1 1

Nested Loop (cost=4.52..59.81 rows=1 width=8) (actual time=4.629..6.047 rows=1 loops=1)

  • Join Filter: (cod.model_id = m.model_id)
  • Rows Removed by Join Filter: 13
6. 0.200 0.200 ↑ 1.0 1 1

Index Scan using cq_machines_index01 on cq_machines m (cost=0.14..8.16 rows=1 width=8) (actual time=0.190..0.200 rows=1 loops=1)

  • Index Cond: (id = 86)
7. 0.544 5.626 ↑ 1.0 14 1

Nested Loop (cost=4.37..51.47 rows=14 width=16) (actual time=1.325..5.626 rows=14 loops=1)

8. 0.310 2.856 ↑ 1.0 14 1

Nested Loop (cost=4.09..46.08 rows=14 width=8) (actual time=1.109..2.856 rows=14 loops=1)

9. 0.200 1.331 ↑ 1.0 1 1

Nested Loop (cost=0.00..26.05 rows=1 width=8) (actual time=0.769..1.331 rows=1 loops=1)

  • Join Filter: (cl.id = me.data_class_id)
  • Rows Removed by Join Filter: 12
10. 0.203 0.203 ↑ 1.0 1 1

Seq Scan on cq_data_class cl (cost=0.00..1.16 rows=1 width=8) (actual time=0.193..0.203 rows=1 loops=1)

  • Filter: ((data_class_no)::text = '1200'::text)
  • Rows Removed by Filter: 12
11. 0.928 0.928 ↑ 1.0 13 1

Seq Scan on cq_message_formats me (cost=0.00..24.73 rows=13 width=16) (actual time=0.047..0.928 rows=13 loops=1)

  • Filter: (data_no = 1)
  • Rows Removed by Filter: 765
12. 0.998 1.215 ↑ 1.0 14 1

Bitmap Heap Scan on cq_message_format_mappings memp (cost=4.09..19.89 rows=14 width=16) (actual time=0.300..1.215 rows=14 loops=1)

  • Recheck Cond: (message_format_id = me.id)
  • Heap Blocks: exact=14
13. 0.217 0.217 ↑ 1.0 14 1

Bitmap Index Scan on cq_message_format_mappings_index03 (cost=0.00..4.08 rows=14 width=0) (actual time=0.217..0.217 rows=14 loops=1)

  • Index Cond: (message_format_id = me.id)
14. 2.226 2.226 ↑ 1.0 1 14

Index Scan using cq_operation_data_index01 on cq_operation_data cod (cost=0.29..0.37 rows=1 width=16) (actual time=0.149..0.159 rows=1 loops=14)

  • Index Cond: (id = memp.operation_data_id)
15. 0.212 28.933 ↑ 1.0 1 1

Nested Loop (cost=4.82..78.05 rows=1 width=8) (actual time=27.908..28.933 rows=1 loops=1)

  • Join Filter: (cod_1.model_id = m_1.model_id)
  • Rows Removed by Join Filter: 13
16. 0.023 0.023 ↑ 1.0 1 1

Index Scan using cq_machines_index01 on cq_machines m_1 (cost=0.14..8.16 rows=1 width=8) (actual time=0.013..0.023 rows=1 loops=1)

  • Index Cond: (id = 86)
17. 0.570 28.698 ↑ 1.0 14 1

Nested Loop (cost=4.68..69.71 rows=14 width=16) (actual time=24.680..28.698 rows=14 loops=1)

18. 0.310 25.734 ↑ 1.0 14 1

Nested Loop (cost=4.39..64.33 rows=14 width=8) (actual time=24.561..25.734 rows=14 loops=1)

19. 0.127 0.487 ↑ 1.0 1 1

Nested Loop (cost=0.00..27.86 rows=1 width=8) (actual time=0.385..0.487 rows=1 loops=1)

  • Join Filter: (cl_1.id = me_1.data_class_id)
  • Rows Removed by Join Filter: 2
20. 0.044 0.044 ↑ 1.0 1 1

Seq Scan on cq_data_class cl_1 (cost=0.00..1.16 rows=1 width=8) (actual time=0.032..0.044 rows=1 loops=1)

  • Filter: ((data_class_no)::text = '0200'::text)
  • Rows Removed by Filter: 12
21. 0.316 0.316 ↓ 1.5 3 1

Seq Scan on cq_message_formats me_1 (cost=0.00..26.67 rows=2 width=16) (actual time=0.072..0.316 rows=3 loops=1)

  • Filter: ((byte_position = 1) AND (bit_position = 8))
  • Rows Removed by Filter: 775
22. 0.843 24.937 ↑ 1.0 14 1

Bitmap Heap Scan on cq_message_format_mappings memp_1 (cost=4.39..36.33 rows=14 width=16) (actual time=24.136..24.937 rows=14 loops=1)

  • Recheck Cond: (message_format_id = me_1.id)
  • Heap Blocks: exact=14
23. 24.094 24.094 ↑ 1.0 14 1

Bitmap Index Scan on cq_message_format_mappings_index03 (cost=0.00..4.39 rows=14 width=0) (actual time=24.094..24.094 rows=14 loops=1)

  • Index Cond: (message_format_id = me_1.id)
24. 2.394 2.394 ↑ 1.0 1 14

Index Scan using cq_operation_data_index01 on cq_operation_data cod_1 (cost=0.29..0.37 rows=1 width=16) (actual time=0.161..0.171 rows=1 loops=14)

  • Index Cond: (id = memp_1.operation_data_id)
25. 0.215 2.084 ↑ 1.0 1 1

Nested Loop (cost=4.82..78.05 rows=1 width=8) (actual time=1.549..2.084 rows=1 loops=1)

  • Join Filter: (cod_2.model_id = m_2.model_id)
  • Rows Removed by Join Filter: 13
26. 0.025 0.025 ↑ 1.0 1 1

Index Scan using cq_machines_index01 on cq_machines m_2 (cost=0.14..8.16 rows=1 width=8) (actual time=0.015..0.025 rows=1 loops=1)

  • Index Cond: (id = 86)
27. 0.573 1.844 ↑ 1.0 14 1

Nested Loop (cost=4.68..69.71 rows=14 width=16) (actual time=0.451..1.844 rows=14 loops=1)

28. 0.332 0.949 ↑ 1.0 14 1

Nested Loop (cost=4.39..64.33 rows=14 width=8) (actual time=0.393..0.949 rows=14 loops=1)

29. 0.106 0.364 ↑ 1.0 1 1

Nested Loop (cost=0.00..27.86 rows=1 width=8) (actual time=0.251..0.364 rows=1 loops=1)

  • Join Filter: (cl_2.id = me_2.data_class_id)
  • Rows Removed by Join Filter: 2
30. 0.046 0.046 ↑ 1.0 1 1

Seq Scan on cq_data_class cl_2 (cost=0.00..1.16 rows=1 width=8) (actual time=0.035..0.046 rows=1 loops=1)

  • Filter: ((data_class_no)::text = '0200'::text)
  • Rows Removed by Filter: 12
31. 0.212 0.212 ↓ 1.5 3 1

Seq Scan on cq_message_formats me_2 (cost=0.00..26.67 rows=2 width=16) (actual time=0.059..0.212 rows=3 loops=1)

  • Filter: ((byte_position = 1) AND (bit_position = 5))
  • Rows Removed by Filter: 775
32. 0.174 0.253 ↑ 1.0 14 1

Bitmap Heap Scan on cq_message_format_mappings memp_2 (cost=4.39..36.33 rows=14 width=16) (actual time=0.103..0.253 rows=14 loops=1)

  • Recheck Cond: (message_format_id = me_2.id)
  • Heap Blocks: exact=14
33. 0.079 0.079 ↑ 1.0 14 1

Bitmap Index Scan on cq_message_format_mappings_index03 (cost=0.00..4.39 rows=14 width=0) (actual time=0.079..0.079 rows=14 loops=1)

  • Index Cond: (message_format_id = me_2.id)
34. 0.322 0.322 ↑ 1.0 1 14

Index Scan using cq_operation_data_index01 on cq_operation_data cod_2 (cost=0.29..0.37 rows=1 width=16) (actual time=0.014..0.023 rows=1 loops=14)

  • Index Cond: (id = memp_2.operation_data_id)
35. 0.213 3.250 ↑ 1.0 1 1

Nested Loop (cost=4.82..78.05 rows=1 width=8) (actual time=2.719..3.250 rows=1 loops=1)

  • Join Filter: (cod_3.model_id = m_3.model_id)
  • Rows Removed by Join Filter: 13
36. 0.022 0.022 ↑ 1.0 1 1

Index Scan using cq_machines_index01 on cq_machines m_3 (cost=0.14..8.16 rows=1 width=8) (actual time=0.012..0.022 rows=1 loops=1)

  • Index Cond: (id = 86)
37. 0.555 3.015 ↑ 1.0 14 1

Nested Loop (cost=4.68..69.71 rows=14 width=16) (actual time=1.581..3.015 rows=14 loops=1)

38. 0.334 2.096 ↑ 1.0 14 1

Nested Loop (cost=4.39..64.33 rows=14 width=8) (actual time=1.539..2.096 rows=14 loops=1)

39. 0.106 0.458 ↑ 1.0 1 1

Nested Loop (cost=0.00..27.86 rows=1 width=8) (actual time=0.347..0.458 rows=1 loops=1)

  • Join Filter: (cl_3.id = me_3.data_class_id)
  • Rows Removed by Join Filter: 2
40. 0.043 0.043 ↑ 1.0 1 1

Seq Scan on cq_data_class cl_3 (cost=0.00..1.16 rows=1 width=8) (actual time=0.032..0.043 rows=1 loops=1)

  • Filter: ((data_class_no)::text = '0200'::text)
  • Rows Removed by Filter: 12
41. 0.309 0.309 ↓ 1.5 3 1

Seq Scan on cq_message_formats me_3 (cost=0.00..26.67 rows=2 width=16) (actual time=0.121..0.309 rows=3 loops=1)

  • Filter: ((byte_position = 1) AND (bit_position = 1))
  • Rows Removed by Filter: 775
42. 0.176 1.304 ↑ 1.0 14 1

Bitmap Heap Scan on cq_message_format_mappings memp_3 (cost=4.39..36.33 rows=14 width=16) (actual time=1.152..1.304 rows=14 loops=1)

  • Recheck Cond: (message_format_id = me_3.id)
  • Heap Blocks: exact=14
43. 1.128 1.128 ↑ 1.0 14 1

Bitmap Index Scan on cq_message_format_mappings_index03 (cost=0.00..4.39 rows=14 width=0) (actual time=1.128..1.128 rows=14 loops=1)

  • Index Cond: (message_format_id = me_3.id)
44. 0.364 0.364 ↑ 1.0 1 14

Index Scan using cq_operation_data_index01 on cq_operation_data cod_3 (cost=0.29..0.37 rows=1 width=16) (actual time=0.016..0.026 rows=1 loops=14)

  • Index Cond: (id = memp_3.operation_data_id)
45. 0.209 2.021 ↑ 1.0 1 1

Nested Loop (cost=4.82..78.05 rows=1 width=8) (actual time=1.525..2.021 rows=1 loops=1)

  • Join Filter: (cod_4.model_id = m_4.model_id)
  • Rows Removed by Join Filter: 13
46. 0.023 0.023 ↑ 1.0 1 1

Index Scan using cq_machines_index01 on cq_machines m_4 (cost=0.14..8.16 rows=1 width=8) (actual time=0.013..0.023 rows=1 loops=1)

  • Index Cond: (id = 86)
47. 0.547 1.789 ↑ 1.0 14 1

Nested Loop (cost=4.68..69.71 rows=14 width=16) (actual time=0.444..1.789 rows=14 loops=1)

48. 0.313 0.934 ↑ 1.0 14 1

Nested Loop (cost=4.39..64.33 rows=14 width=8) (actual time=0.404..0.934 rows=14 loops=1)

49. 0.122 0.369 ↑ 1.0 1 1

Nested Loop (cost=0.00..27.86 rows=1 width=8) (actual time=0.276..0.369 rows=1 loops=1)

  • Join Filter: (cl_4.id = me_4.data_class_id)
  • Rows Removed by Join Filter: 2
50. 0.044 0.044 ↑ 1.0 1 1

Seq Scan on cq_data_class cl_4 (cost=0.00..1.16 rows=1 width=8) (actual time=0.033..0.044 rows=1 loops=1)

  • Filter: ((data_class_no)::text = '0200'::text)
  • Rows Removed by Filter: 12
51. 0.203 0.203 ↓ 1.5 3 1

Seq Scan on cq_message_formats me_4 (cost=0.00..26.67 rows=2 width=16) (actual time=0.044..0.203 rows=3 loops=1)

  • Filter: ((byte_position = 1) AND (bit_position = 7))
  • Rows Removed by Filter: 775
52. 0.187 0.252 ↑ 1.0 14 1

Bitmap Heap Scan on cq_message_format_mappings memp_4 (cost=4.39..36.33 rows=14 width=16) (actual time=0.088..0.252 rows=14 loops=1)

  • Recheck Cond: (message_format_id = me_4.id)
  • Heap Blocks: exact=14
53. 0.065 0.065 ↑ 1.0 14 1

Bitmap Index Scan on cq_message_format_mappings_index03 (cost=0.00..4.39 rows=14 width=0) (actual time=0.065..0.065 rows=14 loops=1)

  • Index Cond: (message_format_id = me_4.id)
54. 0.308 0.308 ↑ 1.0 1 14

Index Scan using cq_operation_data_index01 on cq_operation_data cod_4 (cost=0.29..0.37 rows=1 width=16) (actual time=0.012..0.022 rows=1 loops=14)

  • Index Cond: (id = memp_4.operation_data_id)
55. 0.254 2.016 ↑ 1.0 1 1

Nested Loop (cost=4.82..78.05 rows=1 width=8) (actual time=1.484..2.016 rows=1 loops=1)

  • Join Filter: (cod_5.model_id = m_5.model_id)
  • Rows Removed by Join Filter: 13
56. 0.024 0.024 ↑ 1.0 1 1

Index Scan using cq_machines_index01 on cq_machines m_5 (cost=0.14..8.16 rows=1 width=8) (actual time=0.013..0.024 rows=1 loops=1)

  • Index Cond: (id = 86)
57. 0.549 1.738 ↑ 1.0 14 1

Nested Loop (cost=4.68..69.71 rows=14 width=16) (actual time=0.391..1.738 rows=14 loops=1)

58. 0.327 0.895 ↑ 1.0 14 1

Nested Loop (cost=4.39..64.33 rows=14 width=8) (actual time=0.350..0.895 rows=14 loops=1)

59. 0.108 0.353 ↑ 1.0 1 1

Nested Loop (cost=0.00..27.86 rows=1 width=8) (actual time=0.241..0.353 rows=1 loops=1)

  • Join Filter: (cl_5.id = me_5.data_class_id)
  • Rows Removed by Join Filter: 2
60. 0.041 0.041 ↑ 1.0 1 1

Seq Scan on cq_data_class cl_5 (cost=0.00..1.16 rows=1 width=8) (actual time=0.031..0.041 rows=1 loops=1)

  • Filter: ((data_class_no)::text = '0200'::text)
  • Rows Removed by Filter: 12
61. 0.204 0.204 ↓ 1.5 3 1

Seq Scan on cq_message_formats me_5 (cost=0.00..26.67 rows=2 width=16) (actual time=0.055..0.204 rows=3 loops=1)

  • Filter: ((byte_position = 1) AND (bit_position = 6))
  • Rows Removed by Filter: 775
62. 0.167 0.215 ↑ 1.0 14 1

Bitmap Heap Scan on cq_message_format_mappings memp_5 (cost=4.39..36.33 rows=14 width=16) (actual time=0.071..0.215 rows=14 loops=1)

  • Recheck Cond: (message_format_id = me_5.id)
  • Heap Blocks: exact=14
63. 0.048 0.048 ↑ 1.0 14 1

Bitmap Index Scan on cq_message_format_mappings_index03 (cost=0.00..4.39 rows=14 width=0) (actual time=0.048..0.048 rows=14 loops=1)

  • Index Cond: (message_format_id = me_5.id)
64. 0.294 0.294 ↑ 1.0 1 14

Index Scan using cq_operation_data_index01 on cq_operation_data cod_5 (cost=0.29..0.37 rows=1 width=16) (actual time=0.012..0.021 rows=1 loops=14)

  • Index Cond: (id = memp_5.operation_data_id)
65.          

CTE pretime

66. 0.043 413.823 ↑ 1.0 1 1

Limit (cost=1,978.92..1,978.92 rows=1 width=8) (actual time=413.814..413.823 rows=1 loops=1)

67. 16.533 413.780 ↑ 242.0 1 1

Sort (cost=1,978.92..1,979.53 rows=242 width=8) (actual time=413.780..413.780 rows=1 loops=1)

  • Sort Key: h.acquisition_time
  • Sort Method: top-N heapsort Memory: 25kB
68. 31.822 397.247 ↓ 6.8 1,652 1

Nested Loop (cost=13.58..1,977.71 rows=242 width=8) (actual time=25.468..397.247 rows=1,652 loops=1)

69. 0.162 0.162 ↑ 1.0 1 1

CTE Scan on target target_1 (cost=0.00..0.14 rows=1 width=8) (actual time=0.107..0.162 rows=1 loops=1)

  • Filter: (data_code = 'KEYON'::text)
  • Rows Removed by Filter: 5
70. 340.800 365.263 ↓ 6.8 1,652 1

Bitmap Heap Scan on cq_operation_data_history h (cost=13.58..1,975.16 rows=242 width=16) (actual time=25.322..365.263 rows=1,652 loops=1)

  • Recheck Cond: ((operation_data_id = target_1.operation_data_id) AND (machine_id = 86))
  • Filter: (acquisition_time < '2019-03-18 00:00:00+00'::timestamp with time zone)
  • Rows Removed by Filter: 2134
  • Heap Blocks: exact=3290
71. 24.463 24.463 ↓ 7.6 3,786 1

Bitmap Index Scan on cq_operation_data_history_index04 (cost=0.00..13.52 rows=495 width=0) (actual time=24.463..24.463 rows=3,786 loops=1)

  • Index Cond: ((operation_data_id = target_1.operation_data_id) AND (machine_id = 86))
72.          

Initplan (forSort)

73. 413.864 413.864 ↑ 1.0 1 1

CTE Scan on pretime (cost=0.00..0.02 rows=1 width=8) (actual time=413.835..413.864 rows=1 loops=1)

74. 0.020 0.020 ↑ 1.0 1 1

CTE Scan on pretime pretime_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.010..0.020 rows=1 loops=1)

75. 414.887 484.581 ↓ 1.9 29 1

Nested Loop (cost=0.58..1,847.85 rows=15 width=75) (actual time=478.860..484.581 rows=29 loops=1)

76. 44.764 44.764 ↑ 1.0 6 1

CTE Scan on target (cost=0.00..0.12 rows=6 width=40) (actual time=44.702..44.764 rows=6 loops=1)

77. 24.930 24.930 ↓ 2.5 5 6

Index Scan using cq_operation_data_history_index02 on cq_operation_data_history codh (cost=0.58..307.93 rows=2 width=43) (actual time=3.357..4.155 rows=5 loops=6)

  • Index Cond: ((machine_id = 86) AND (acquisition_time < date_trunc('day'::text, ($15 + '1 day'::interval))) AND (acquisition_time >= date_trunc('day'::text, $16)) AND (operation_data_id = target.operation_data_id))
Planning time : 27.133 ms
Execution time : 489.182 ms