explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rjgB

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 2.637 ↓ 1.9 17 1

Sort (cost=751.17..751.19 rows=9 width=75) (actual time=2.629..2.637 rows=17 loops=1)

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

CTE target

3. 0.018 1.970 ↑ 1.0 6 1

HashAggregate (cost=435.98..436.04 rows=6 width=8) (actual time=1.967..1.970 rows=6 loops=1)

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

Append (cost=4.37..435.95 rows=6 width=8) (actual time=0.289..1.952 rows=6 loops=1)

5. 0.018 0.429 ↑ 1.0 1 1

Nested Loop (cost=4.37..57.44 rows=1 width=8) (actual time=0.288..0.429 rows=1 loops=1)

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

Seq Scan on cq_machines m (cost=0.00..5.80 rows=1 width=8) (actual time=0.019..0.029 rows=1 loops=1)

  • Filter: (id = 86)
  • Rows Removed by Filter: 143
7. 0.059 0.382 ↑ 1.0 14 1

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

8. 0.024 0.211 ↑ 1.0 14 1

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

9. 0.015 0.136 ↑ 1.0 1 1

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

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

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

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

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

  • Filter: (data_no = 1)
  • Rows Removed by Filter: 765
12. 0.036 0.051 ↑ 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.022..0.051 rows=14 loops=1)

  • Recheck Cond: (message_format_id = me.id)
  • Heap Blocks: exact=14
13. 0.015 0.015 ↑ 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.015..0.015 rows=14 loops=1)

  • Index Cond: (message_format_id = me.id)
14. 0.112 0.112 ↑ 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.008..0.008 rows=1 loops=14)

  • Index Cond: (id = memp.operation_data_id)
15. 0.023 0.345 ↑ 1.0 1 1

Nested Loop (cost=4.68..75.69 rows=1 width=8) (actual time=0.258..0.345 rows=1 loops=1)

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

Seq Scan on cq_machines m_1 (cost=0.00..5.80 rows=1 width=8) (actual time=0.014..0.018 rows=1 loops=1)

  • Filter: (id = 86)
  • Rows Removed by Filter: 143
17. 0.040 0.304 ↑ 1.0 14 1

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

18. 0.024 0.194 ↑ 1.0 14 1

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

19. 0.010 0.123 ↑ 1.0 1 1

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

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

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

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

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

  • Filter: ((byte_position = 1) AND (bit_position = 8))
  • Rows Removed by Filter: 775
22. 0.035 0.047 ↑ 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=0.018..0.047 rows=14 loops=1)

  • Recheck Cond: (message_format_id = me_1.id)
  • Heap Blocks: exact=14
23. 0.012 0.012 ↑ 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.012..0.012 rows=14 loops=1)

  • Index Cond: (message_format_id = me_1.id)
24. 0.070 0.070 ↑ 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.004..0.005 rows=1 loops=14)

  • Index Cond: (id = memp_1.operation_data_id)
25. 0.019 0.298 ↑ 1.0 1 1

Nested Loop (cost=4.68..75.69 rows=1 width=8) (actual time=0.222..0.298 rows=1 loops=1)

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

Seq Scan on cq_machines m_2 (cost=0.00..5.80 rows=1 width=8) (actual time=0.009..0.016 rows=1 loops=1)

  • Filter: (id = 86)
  • Rows Removed by Filter: 143
27. 0.039 0.263 ↑ 1.0 14 1

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

28. 0.024 0.182 ↑ 1.0 14 1

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

29. 0.010 0.133 ↑ 1.0 1 1

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

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

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

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

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

  • Filter: ((byte_position = 1) AND (bit_position = 5))
  • Rows Removed by Filter: 775
32. 0.016 0.025 ↑ 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.013..0.025 rows=14 loops=1)

  • Recheck Cond: (message_format_id = me_2.id)
  • Heap Blocks: exact=14
33. 0.009 0.009 ↑ 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.009..0.009 rows=14 loops=1)

  • Index Cond: (message_format_id = me_2.id)
34. 0.042 0.042 ↑ 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.002..0.003 rows=1 loops=14)

  • Index Cond: (id = memp_2.operation_data_id)
35. 0.019 0.300 ↑ 1.0 1 1

Nested Loop (cost=4.68..75.69 rows=1 width=8) (actual time=0.207..0.300 rows=1 loops=1)

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

Seq Scan on cq_machines m_3 (cost=0.00..5.80 rows=1 width=8) (actual time=0.006..0.013 rows=1 loops=1)

  • Filter: (id = 86)
  • Rows Removed by Filter: 143
37. 0.051 0.268 ↑ 1.0 14 1

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

38. 0.025 0.189 ↑ 1.0 14 1

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

39. 0.014 0.137 ↑ 1.0 1 1

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

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

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

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

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

  • Filter: ((byte_position = 1) AND (bit_position = 1))
  • Rows Removed by Filter: 775
42. 0.019 0.027 ↑ 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=0.014..0.027 rows=14 loops=1)

  • Recheck Cond: (message_format_id = me_3.id)
  • Heap Blocks: exact=14
43. 0.008 0.008 ↑ 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.008..0.008 rows=14 loops=1)

  • Index Cond: (message_format_id = me_3.id)
44. 0.028 0.028 ↑ 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.002..0.002 rows=1 loops=14)

  • Index Cond: (id = memp_3.operation_data_id)
45. 0.024 0.332 ↑ 1.0 1 1

Nested Loop (cost=4.68..75.69 rows=1 width=8) (actual time=0.273..0.332 rows=1 loops=1)

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

Seq Scan on cq_machines m_4 (cost=0.00..5.80 rows=1 width=8) (actual time=0.011..0.015 rows=1 loops=1)

  • Filter: (id = 86)
  • Rows Removed by Filter: 143
47. 0.049 0.293 ↑ 1.0 14 1

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

48. 0.029 0.202 ↑ 1.0 14 1

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

49. 0.014 0.142 ↑ 1.0 1 1

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

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

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

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

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

  • Filter: ((byte_position = 1) AND (bit_position = 7))
  • Rows Removed by Filter: 775
52. 0.019 0.031 ↑ 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.017..0.031 rows=14 loops=1)

  • Recheck Cond: (message_format_id = me_4.id)
  • Heap Blocks: exact=14
53. 0.012 0.012 ↑ 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.012..0.012 rows=14 loops=1)

  • Index Cond: (message_format_id = me_4.id)
54. 0.042 0.042 ↑ 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.002..0.003 rows=1 loops=14)

  • Index Cond: (id = memp_4.operation_data_id)
55. 0.016 0.233 ↑ 1.0 1 1

Nested Loop (cost=4.68..75.69 rows=1 width=8) (actual time=0.176..0.233 rows=1 loops=1)

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

Seq Scan on cq_machines m_5 (cost=0.00..5.80 rows=1 width=8) (actual time=0.007..0.011 rows=1 loops=1)

  • Filter: (id = 86)
  • Rows Removed by Filter: 143
57. 0.038 0.206 ↑ 1.0 14 1

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

58. 0.021 0.140 ↑ 1.0 14 1

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

59. 0.010 0.100 ↑ 1.0 1 1

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

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

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

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

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

  • Filter: ((byte_position = 1) AND (bit_position = 6))
  • Rows Removed by Filter: 775
62. 0.013 0.019 ↑ 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.010..0.019 rows=14 loops=1)

  • Recheck Cond: (message_format_id = me_5.id)
  • Heap Blocks: exact=14
63. 0.006 0.006 ↑ 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.006..0.006 rows=14 loops=1)

  • Index Cond: (message_format_id = me_5.id)
64. 0.028 0.028 ↑ 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.001..0.002 rows=1 loops=14)

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

CTE pretime

66. 0.003 0.457 ↑ 1.0 1 1

Limit (cost=0.42..61.84 rows=1 width=8) (actual time=0.456..0.457 rows=1 loops=1)

67. 0.184 0.454 ↑ 172.0 1 1

Nested Loop (cost=0.42..10,565.28 rows=172 width=8) (actual time=0.454..0.454 rows=1 loops=1)

  • Join Filter: (h.operation_data_id = target_1.operation_data_id)
  • Rows Removed by Join Filter: 65
68. 0.138 0.138 ↑ 1,658.1 66 1

Index Scan Backward using cq_operation_data_history_index01 on cq_operation_data_history h (cost=0.42..8,102.87 rows=109,435 width=16) (actual time=0.087..0.138 rows=66 loops=1)

  • Index Cond: ((machine_id = 86) AND (acquisition_time < '2019-03-18 00:00:00+09'::timestamp with time zone))
69. 0.132 0.132 ↑ 1.0 1 66

CTE Scan on target target_1 (cost=0.00..0.14 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=66)

  • Filter: (data_code = 'KEYON'::text)
  • Rows Removed by Filter: 5
70.          

Initplan (forSort)

71. 0.460 0.460 ↑ 1.0 1 1

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

72. 0.001 0.001 ↑ 1.0 1 1

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

73. 0.514 2.601 ↓ 1.9 17 1

Nested Loop (cost=0.43..253.10 rows=9 width=75) (actual time=2.487..2.601 rows=17 loops=1)

74. 1.973 1.973 ↑ 1.0 6 1

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

75. 0.114 0.114 ↓ 3.0 3 6

Index Scan using cq_operation_data_history_index02 on cq_operation_data_history codh (cost=0.43..42.15 rows=1 width=43) (actual time=0.009..0.019 rows=3 loops=6)

  • Index Cond: ((machine_id = 86) AND (acquisition_time < date_trunc('day'::text, ($14 + '1 day'::interval))) AND (acquisition_time >= date_trunc('day'::text, $15)) AND (operation_data_id = target.operation_data_id))