explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D1mK

Settings
# exclusive inclusive rows x rows loops node
1. 4,432.666 4,432.666 ↓ 9.5 951 1

CTE Scan on tmp_result (cost=623,904.84..623,906.84 rows=100 width=360) (actual time=4,423.017..4,432.666 rows=951 loops=1)

2.          

CTE args

3. 0.007 0.007 ↑ 410.0 1 1

Seq Scan on sites (cost=0.00..14.10 rows=410 width=176) (actual time=0.007..0.007 rows=1 loops=1)

4.          

CTE args_choices

5. 0.007 0.045 ↑ 4,100,000.0 1 1

ProjectSet (cost=0.01..21,635.71 rows=4,100,000 width=64) (actual time=0.041..0.045 rows=1 loops=1)

6. 0.004 0.038 ↑ 41,000.0 1 1

Nested Loop (cost=0.01..828.21 rows=41,000 width=64) (actual time=0.036..0.038 rows=1 loops=1)

7. 0.010 0.010 ↑ 410.0 1 1

CTE Scan on args (cost=0.00..8.20 rows=410 width=32) (actual time=0.009..0.010 rows=1 loops=1)

8. 0.024 0.024 ↑ 100.0 1 1

Function Scan on jsonb_each configs (cost=0.01..1.01 rows=100 width=64) (actual time=0.024..0.024 rows=1 loops=1)

9.          

CTE option_choices

10. 0.022 0.055 ↑ 41.1 18 1

Hash Join (cost=26.88..46.23 rows=740 width=80) (actual time=0.040..0.055 rows=18 loops=1)

  • Hash Cond: (choices.option_id = options.id)
11. 0.017 0.017 ↑ 41.1 18 1

Seq Scan on choices (cost=0.00..17.40 rows=740 width=64) (actual time=0.012..0.017 rows=18 loops=1)

12. 0.006 0.016 ↑ 150.0 5 1

Hash (cost=17.50..17.50 rows=750 width=48) (actual time=0.016..0.016 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.010 0.010 ↑ 150.0 5 1

Seq Scan on options (cost=0.00..17.50 rows=750 width=48) (actual time=0.007..0.010 rows=5 loops=1)

14.          

CTE exclude_choices

15. 0.006 23.611 ↑ 1.0 1 1

Aggregate (cost=225,533.71..225,533.72 rows=1 width=64) (actual time=23.611..23.611 rows=1 loops=1)

16. 23.467 23.605 ↑ 185.0 1 1

Hash Anti Join (cost=225,504.50..225,532.78 rows=185 width=48) (actual time=23.595..23.605 rows=1 loops=1)

  • Hash Cond: (option_choices.choice_value = args_choices_1.choice_value)
17. 0.012 0.133 ↑ 185.0 2 1

Hash Join (cost=92,254.50..92,275.36 rows=370 width=48) (actual time=0.124..0.133 rows=2 loops=1)

  • Hash Cond: (option_choices.option_name = args_choices.option_name)
18. 0.067 0.067 ↑ 41.1 18 1

CTE Scan on option_choices (cost=0.00..14.80 rows=740 width=80) (actual time=0.042..0.067 rows=18 loops=1)

19. 0.002 0.054 ↑ 200.0 1 1

Hash (cost=92,252.00..92,252.00 rows=200 width=32) (actual time=0.054..0.054 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.005 0.052 ↑ 200.0 1 1

HashAggregate (cost=92,250.00..92,252.00 rows=200 width=32) (actual time=0.051..0.052 rows=1 loops=1)

  • Group Key: args_choices.option_name
21. 0.047 0.047 ↑ 4,100,000.0 1 1

CTE Scan on args_choices (cost=0.00..82,000.00 rows=4,100,000 width=32) (actual time=0.042..0.047 rows=1 loops=1)

22. 0.003 0.005 ↑ 4,100,000.0 1 1

Hash (cost=82,000.00..82,000.00 rows=4,100,000 width=32) (actual time=0.005..0.005 rows=1 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 32769kB
23. 0.002 0.002 ↑ 4,100,000.0 1 1

CTE Scan on args_choices args_choices_1 (cost=0.00..82,000.00 rows=4,100,000 width=32) (actual time=0.002..0.002 rows=1 loops=1)

24.          

CTE tmp_agents

25. 0.005 0.085 ↓ 2.0 8 1

Unique (cost=44.86..44.88 rows=4 width=14) (actual time=0.075..0.085 rows=8 loops=1)

26.          

Initplan (forUnique)

27. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_1 (cost=0.00..8.20 rows=410 width=32) (actual time=0.001..0.001 rows=1 loops=1)

28. 0.015 0.079 ↓ 4.5 18 1

Sort (cost=36.66..36.67 rows=4 width=14) (actual time=0.074..0.079 rows=18 loops=1)

  • Sort Key: jobs.agent_id, jobs.started DESC
  • Sort Method: quicksort Memory: 25kB
29. 0.006 0.064 ↓ 4.5 18 1

Nested Loop (cost=1.54..36.62 rows=4 width=14) (actual time=0.040..0.064 rows=18 loops=1)

30. 0.017 0.017 ↑ 2.0 1 1

Seq Scan on processes (cost=0.00..16.12 rows=2 width=16) (actual time=0.016..0.017 rows=1 loops=1)

  • Filter: (name = $5)
  • Rows Removed by Filter: 53
31. 0.026 0.041 ↑ 1.2 18 1

Bitmap Heap Scan on jobs (cost=1.54..10.04 rows=21 width=46) (actual time=0.021..0.041 rows=18 loops=1)

  • Recheck Cond: (process_id = processes.id)
  • Heap Blocks: exact=7
32. 0.015 0.015 ↑ 1.2 18 1

Bitmap Index Scan on jobs_process_id_started_idx (cost=0.00..1.54 rows=21 width=0) (actual time=0.015..0.015 rows=18 loops=1)

  • Index Cond: (process_id = processes.id)
33.          

CTE all_sn

34. 0.134 33.260 ↑ 1.0 1,000 1

Limit (cost=8,516.58..8,526.58 rows=1,000 width=32) (actual time=32.393..33.260 rows=1,000 loops=1)

35.          

Initplan (forLimit)

36. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_2 (cost=0.00..8.20 rows=410 width=8) (actual time=0.001..0.001 rows=1 loops=1)

37. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_3 (cost=0.00..8.20 rows=410 width=8) (actual time=0.001..0.001 rows=1 loops=1)

38. 2.341 33.124 ↑ 1.8 1,000 1

HashAggregate (cost=8,500.18..8,517.89 rows=1,771 width=32) (actual time=32.392..33.124 rows=1,000 loops=1)

  • Group Key: logs_serials.serial_type, logs_serials.serial
39. 1.121 30.783 ↓ 2.4 4,179 1

Nested Loop (cost=1.13..8,491.33 rows=1,771 width=32) (actual time=0.116..30.783 rows=4,179 loops=1)

40. 0.377 4.588 ↓ 2.6 4,179 1

Nested Loop (cost=0.56..1,836.60 rows=1,608 width=16) (actual time=0.099..4.588 rows=4,179 loops=1)

41. 0.091 0.091 ↓ 2.0 8 1

CTE Scan on tmp_agents (cost=0.00..0.08 rows=4 width=6) (actual time=0.076..0.091 rows=8 loops=1)

42. 4.120 4.120 ↓ 1.3 522 8

Index Scan using logs_v2_agent_id_created_idx on logs_v2 (cost=0.56..455.11 rows=402 width=22) (actual time=0.011..0.515 rows=522 loops=8)

  • Index Cond: ((agent_id = tmp_agents.agent_id) AND (created >= $8) AND (created < $9))
43. 25.074 25.074 ↑ 5.0 1 4,179

Index Scan using logs_serials_log_id_idx on logs_serials (cost=0.56..4.09 rows=5 width=48) (actual time=0.006..0.006 rows=1 loops=4,179)

  • Index Cond: (log_id = logs_v2.id)
44.          

CTE tmp_sn

45. 0.428 115.174 ↑ 1.1 951 1

Nested Loop (cost=37.18..20,849.73 rows=1,000 width=64) (actual time=32.497..115.174 rows=951 loops=1)

46.          

Initplan (forNested Loop)

47. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_4 (cost=0.00..8.20 rows=410 width=8) (actual time=0.001..0.001 rows=1 loops=1)

48. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_5 (cost=0.00..8.20 rows=410 width=8) (actual time=0.000..0.001 rows=1 loops=1)

49. 33.744 33.744 ↑ 1.0 1,000 1

CTE Scan on all_sn (cost=0.00..20.00 rows=1,000 width=64) (actual time=32.394..33.744 rows=1,000 loops=1)

50. 1.000 81.000 ↑ 1.0 1 1,000

Subquery Scan on first_log (cost=20.78..20.80 rows=1 width=0) (actual time=0.079..0.081 rows=1 loops=1,000)

  • Filter: ((first_log.first_seen >= $13) AND (first_log.first_seen < $14))
  • Rows Removed by Filter: 0
51. 2.000 80.000 ↑ 1.0 1 1,000

Limit (cost=20.78..20.79 rows=1 width=8) (actual time=0.079..0.080 rows=1 loops=1,000)

52. 2.000 78.000 ↑ 1.0 1 1,000

Sort (cost=20.78..20.79 rows=1 width=8) (actual time=0.078..0.078 rows=1 loops=1,000)

  • Sort Key: logs_v2_1.created
  • Sort Method: quicksort Memory: 25kB
53. 1.912 76.000 ↓ 2.0 2 1,000

Hash Join (cost=1.26..20.77 rows=1 width=8) (actual time=0.033..0.076 rows=2 loops=1,000)

  • Hash Cond: (logs_v2_1.agent_id = tmp_agents_1.agent_id)
54. 4.393 74.000 ↓ 1.4 7 1,000

Nested Loop (cost=1.13..20.61 rows=5 width=14) (actual time=0.023..0.074 rows=7 loops=1,000)

55. 22.000 22.000 ↓ 1.4 7 1,000

Index Scan using logs_serials_serial_type_serial_text_ops_idx on logs_serials logs_serials_1 (cost=0.56..6.70 rows=5 width=16) (actual time=0.013..0.022 rows=7 loops=1,000)

  • Index Cond: ((serial_type = all_sn.serial_type) AND (serial = all_sn.serial))
56. 47.607 47.607 ↑ 1.0 1 6,801

Index Scan using logs_v2_pkey on logs_v2 logs_v2_1 (cost=0.56..2.78 rows=1 width=30) (actual time=0.007..0.007 rows=1 loops=6,801)

  • Index Cond: (id = logs_serials_1.log_id)
57. 0.060 0.088 ↓ 2.0 8 1

Hash (cost=0.08..0.08 rows=4 width=6) (actual time=0.003..0.088 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
58. 0.028 0.028 ↓ 2.0 8 1

CTE Scan on tmp_agents tmp_agents_1 (cost=0.00..0.08 rows=4 width=6) (actual time=0.001..0.028 rows=8 loops=1)

59.          

CTE tmp_his

60. 1.044 4,400.455 ↓ 4.8 951 1

GroupAggregate (cost=341,790.87..342,505.37 rows=200 width=328) (actual time=4,399.371..4,400.455 rows=951 loops=1)

  • Group Key: tmp_sn.serial_type, tmp_sn.serial
61.          

Initplan (forGroupAggregate)

62. 0.000 0.000 ↑ 410.0 1 1

CTE Scan on args args_6 (cost=0.00..8.20 rows=410 width=32) (actual time=0.000..0.000 rows=1 loops=1)

63. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_7 (cost=0.00..8.20 rows=410 width=32) (actual time=0.001..0.001 rows=1 loops=1)

64. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_8 (cost=0.00..8.20 rows=410 width=32) (actual time=0.001..0.001 rows=1 loops=1)

65. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_9 (cost=0.00..8.20 rows=410 width=32) (actual time=0.000..0.001 rows=1 loops=1)

66. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_10 (cost=0.00..8.20 rows=410 width=32) (actual time=0.001..0.001 rows=1 loops=1)

67. 0.000 0.000 ↑ 410.0 1 1

CTE Scan on args args_11 (cost=0.00..8.20 rows=410 width=32) (actual time=0.000..0.000 rows=1 loops=1)

68. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_12 (cost=0.00..8.20 rows=410 width=32) (actual time=0.001..0.001 rows=1 loops=1)

69. 2.624 4,399.406 ↑ 15.8 951 1

Sort (cost=341,733.47..341,770.97 rows=15,000 width=356) (actual time=4,399.354..4,399.406 rows=951 loops=1)

  • Sort Key: tmp_sn.serial_type, tmp_sn.serial
  • Sort Method: quicksort Memory: 277kB
70. 1.546 4,396.782 ↑ 15.8 951 1

Nested Loop (cost=340.30..340,693.02 rows=15,000 width=356) (actual time=35.814..4,396.782 rows=951 loops=1)

71. 115.736 115.736 ↑ 1.1 951 1

CTE Scan on tmp_sn (cost=0.00..20.00 rows=1,000 width=64) (actual time=32.499..115.736 rows=951 loops=1)

72. 0.945 4,279.500 ↑ 15.0 1 951

Unique (cost=340.30..340.37 rows=15 width=332) (actual time=4.500..4.500 rows=1 loops=951)

73.          

Initplan (forUnique)

74. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_13 (cost=0.00..8.20 rows=410 width=32) (actual time=0.000..0.001 rows=1 loops=1)

75. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_14 (cost=0.00..8.20 rows=410 width=32) (actual time=0.001..0.001 rows=1 loops=1)

76. 0.002 0.002 ↑ 410.0 1 1

CTE Scan on args args_15 (cost=0.00..8.20 rows=410 width=32) (actual time=0.001..0.002 rows=1 loops=1)

77. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_16 (cost=0.00..8.20 rows=410 width=32) (actual time=0.001..0.001 rows=1 loops=1)

78. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_17 (cost=0.00..8.20 rows=410 width=32) (actual time=0.001..0.001 rows=1 loops=1)

79. 2.853 4,278.549 ↑ 7.5 2 951

Sort (cost=299.30..299.34 rows=15 width=332) (actual time=4.499..4.499 rows=2 loops=951)

  • Sort Key: part_history_v2.process, part_history_v2.created DESC
  • Sort Method: quicksort Memory: 25kB
80. 3.419 4,275.696 ↑ 7.5 2 951

Nested Loop Left Join (cost=17.94..299.00 rows=15 width=332) (actual time=3.978..4.496 rows=2 loops=951)

81. 14.357 3,776.421 ↑ 7.5 2 951

Nested Loop Left Join (cost=0.26..32.94 rows=15 width=200) (actual time=3.700..3.971 rows=2 loops=951)

  • Join Filter: (part_history_v2.process = $26)
82. 3,277.146 3,277.146 ↑ 7.5 2 951

Function Scan on part_history_v2 (cost=0.25..14.00 rows=15 width=104) (actual time=3.443..3.446 rows=2 loops=951)

  • Filter: (process = ANY (ARRAY[$28, $29, $30]))
  • Rows Removed by Filter: 16
83. 484.918 484.918 ↑ 1.0 1 1,823

Function Scan on jsonb_to_recordset raw_data (cost=0.01..1.25 rows=1 width=96) (actual time=0.244..0.266 rows=1 loops=1,823)

  • Filter: (sub_test = $27)
  • Rows Removed by Filter: 598
84. 3.644 495.856 ↑ 1.0 1 1,823

Aggregate (cost=17.68..17.69 rows=1 width=192) (actual time=0.272..0.272 rows=1 loops=1,823)

85.          

Initplan (forAggregate)

86. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_18 (cost=0.00..8.20 rows=410 width=32) (actual time=0.001..0.001 rows=1 loops=1)

87. 0.001 0.001 ↑ 410.0 1 1

CTE Scan on args args_19 (cost=0.00..8.20 rows=410 width=32) (actual time=0.000..0.001 rows=1 loops=1)

88. 12.761 492.210 ↓ 2.0 2 1,823

Result (cost=0.01..1.26 rows=1 width=64) (actual time=0.254..0.270 rows=2 loops=1,823)

  • One-Time Filter: (part_history_v2.process = $33)
89. 479.449 479.449 ↓ 2.0 2 1,823

Function Scan on jsonb_to_recordset raw_data_1 (cost=0.01..1.26 rows=1 width=64) (actual time=0.247..0.263 rows=2 loops=1,823)

  • Filter: (test = $34)
  • Rows Removed by Filter: 597
90.          

CTE tmp_result

91. 0.344 4,432.249 ↓ 9.5 951 1

Nested Loop (cost=23.68..4,748.52 rows=100 width=360) (actual time=4,423.015..4,432.249 rows=951 loops=1)

  • Join Filter: COALESCE((NOT ((array_agg(DISTINCT choices_1.value)) && exclude_choices.exclude_choice_values)), true)
92. 23.612 23.612 ↑ 1.0 1 1

CTE Scan on exclude_choices (cost=0.00..0.02 rows=1 width=32) (actual time=23.612..23.612 rows=1 loops=1)

93. 0.781 4,408.293 ↓ 4.8 951 1

Nested Loop Left Join (cost=23.68..4,746.00 rows=200 width=360) (actual time=4,399.399..4,408.293 rows=951 loops=1)

94. 4,400.855 4,400.855 ↓ 4.8 951 1

CTE Scan on tmp_his (cost=0.00..4.00 rows=200 width=328) (actual time=4,399.372..4,400.855 rows=951 loops=1)

95. 1.902 6.657 ↑ 1.0 1 951

Aggregate (cost=23.68..23.69 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=951)

96. 1.902 4.755 ↑ 33.3 3 951

Hash Join (cost=2.25..23.43 rows=100 width=32) (actual time=0.004..0.005 rows=3 loops=951)

  • Hash Cond: (choices_1.id = unnested.unnested)
97. 1.902 1.902 ↑ 41.1 18 951

Seq Scan on choices choices_1 (cost=0.00..17.40 rows=740 width=48) (actual time=0.000..0.002 rows=18 loops=951)

98. 0.000 0.951 ↑ 33.3 3 951

Hash (cost=1.00..1.00 rows=100 width=16) (actual time=0.001..0.001 rows=3 loops=951)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
99. 0.951 0.951 ↑ 33.3 3 951

Function Scan on unnest unnested (cost=0.00..1.00 rows=100 width=16) (actual time=0.001..0.001 rows=3 loops=951)

Planning time : 3.827 ms
Execution time : 4,435.065 ms