explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W1U

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 13,322.761 ↑ 28.6 7 1

Sort (cost=159,904.35..159,904.85 rows=200 width=76) (actual time=13,322.761..13,322.761 rows=7 loops=1)

  • Sort Key: tmp_result.date
  • Sort Method: quicksort Memory: 25kB
2.          

CTE args

3. 0.770 0.770 ↑ 410.0 1 1

Seq Scan on sites (cost=0.00..125.83 rows=410 width=112) (actual time=0.769..0.770 rows=1 loops=1)

4.          

CTE job_agents

5. 0.000 1.045 ↑ 1.1 7 1

Unique (cost=37.52..37.56 rows=8 width=14) (actual time=1.011..1.045 rows=7 loops=1)

6.          

Initplan (forUnique)

7. 0.771 0.771 ↑ 410.0 1 1

CTE Scan on args (cost=0.00..8.20 rows=410 width=16) (actual time=0.770..0.771 rows=1 loops=1)

8. 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)

9. 0.035 1.027 ↑ 1.1 7 1

Sort (cost=21.12..21.14 rows=8 width=14) (actual time=1.011..1.027 rows=7 loops=1)

  • Sort Key: jobs.agent_id, jobs.started DESC
  • Sort Method: quicksort Memory: 25kB
10. 0.003 0.992 ↑ 1.1 7 1

Nested Loop (cost=1.83..21.00 rows=8 width=14) (actual time=0.873..0.992 rows=7 loops=1)

11. 0.002 0.823 ↓ 2.0 2 1

Nested Loop (cost=0.30..7.98 rows=1 width=16) (actual time=0.814..0.823 rows=2 loops=1)

12. 0.002 0.807 ↓ 2.0 2 1

Nested Loop (cost=0.15..5.60 rows=1 width=32) (actual time=0.800..0.807 rows=2 loops=1)

13. 0.790 0.790 ↑ 1.0 1 1

Index Only Scan using components_pkey on components (cost=0.15..2.37 rows=1 width=0) (actual time=0.790..0.790 rows=1 loops=1)

  • Index Cond: (id = $1)
  • Heap Fetches: 1
14. 0.015 0.015 ↓ 2.0 2 1

Seq Scan on processes (cost=0.00..3.23 rows=1 width=32) (actual time=0.009..0.015 rows=2 loops=1)

  • Filter: (name = $2)
  • Rows Removed by Filter: 78
15. 0.014 0.014 ↑ 1.0 1 2

Index Only Scan using plans_pkey on plans (cost=0.15..2.37 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=2)

  • Index Cond: (id = processes.plan_id)
  • Heap Fetches: 2
16. 0.100 0.166 ↑ 5.0 4 2

Bitmap Heap Scan on jobs (cost=1.53..12.83 rows=20 width=30) (actual time=0.041..0.083 rows=4 loops=2)

  • Recheck Cond: (process_id = processes.id)
  • Heap Blocks: exact=5
17. 0.066 0.066 ↑ 5.0 4 2

Bitmap Index Scan on jobs_process_id_started_idx (cost=0.00..1.52 rows=20 width=0) (actual time=0.032..0.033 rows=4 loops=2)

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

CTE unique_sn

19. 199.882 3,223.004 ↓ 29.0 136,722 1

HashAggregate (cost=18,454.62..18,501.73 rows=4,711 width=27) (actual time=3,152.594..3,223.004 rows=136,722 loops=1)

  • Group Key: logs_serials.serial_type, logs_serials.serial
20.          

Initplan (forHashAggregate)

21. 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.000..0.001 rows=1 loops=1)

22. 0.000 0.000 ↑ 410.0 1 1

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

23. 60.678 3,023.121 ↓ 54.9 258,507 1

Nested Loop (cost=1.13..18,414.66 rows=4,711 width=27) (actual time=1.245..3,023.121 rows=258,507 loops=1)

24. 23.264 894.387 ↓ 56.3 258,507 1

Nested Loop (cost=0.56..5,247.65 rows=4,589 width=16) (actual time=1.167..894.387 rows=258,507 loops=1)

25. 1.072 1.072 ↑ 1.1 7 1

CTE Scan on job_agents (cost=0.00..0.16 rows=8 width=6) (actual time=1.012..1.072 rows=7 loops=1)

26. 870.051 870.051 ↓ 64.3 36,930 7

Index Scan using logs_v2_agent_id_created_idx on logs_v2 (cost=0.56..650.20 rows=574 width=22) (actual time=0.058..124.293 rows=36,930 loops=7)

  • Index Cond: ((agent_id = job_agents.agent_id) AND (created >= $6) AND (created < $7))
27. 2,068.056 2,068.056 ↑ 2.0 1 258,507

Index Scan using logs_serials_log_id_idx on logs_serials (cost=0.56..2.85 rows=2 width=43) (actual time=0.008..0.008 rows=1 loops=258,507)

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

CTE filter_by_seen

29. 32.193 13,140.860 ↓ 28.4 133,702 1

Nested Loop (cost=46.24..140,817.96 rows=4,711 width=88) (actual time=3,153.474..13,140.860 rows=133,702 loops=1)

30.          

Initplan (forNested Loop)

31. 0.016 0.016 ↑ 410.0 1 1

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

32. 0.003 0.003 ↑ 410.0 1 1

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

33. 3,264.664 3,264.664 ↓ 29.0 136,722 1

CTE Scan on unique_sn (cost=0.00..94.22 rows=4,711 width=64) (actual time=3,152.627..3,264.664 rows=136,722 loops=1)

34. 136.722 9,843.984 ↑ 1.0 1 136,722

Subquery Scan on log (cost=29.84..29.86 rows=1 width=24) (actual time=0.071..0.072 rows=1 loops=136,722)

  • Filter: ((log.first_seen >= $11) AND (log.first_seen < $12))
  • Rows Removed by Filter: 0
35. 0.000 9,707.262 ↑ 1.0 1 136,722

Limit (cost=29.84..29.84 rows=1 width=24) (actual time=0.071..0.071 rows=1 loops=136,722)

36. 136.722 9,707.262 ↑ 1.0 1 136,722

Sort (cost=29.84..29.84 rows=1 width=24) (actual time=0.071..0.071 rows=1 loops=136,722)

  • Sort Key: logs_v2_1.created DESC
  • Sort Method: top-N heapsort Memory: 25kB
37. 273.444 9,570.540 ↓ 2.0 2 136,722

WindowAgg (cost=29.81..29.83 rows=1 width=24) (actual time=0.069..0.070 rows=2 loops=136,722)

38. 136.722 9,297.096 ↓ 2.0 2 136,722

Sort (cost=29.81..29.81 rows=1 width=12) (actual time=0.068..0.068 rows=2 loops=136,722)

  • Sort Key: logs_v2_1.created
  • Sort Method: quicksort Memory: 25kB
39. 130.357 9,160.374 ↓ 2.0 2 136,722

Hash Join (cost=1.39..29.80 rows=1 width=12) (actual time=0.022..0.067 rows=2 loops=136,722)

  • Hash Cond: (logs_v2_1.agent_id = job_agents_1.agent_id)
40. 815.394 9,023.652 ↑ 1.1 7 136,722

Nested Loop (cost=1.13..29.50 rows=8 width=18) (actual time=0.014..0.066 rows=7 loops=136,722)

41. 2,187.552 2,187.552 ↑ 1.1 7 136,722

Index Scan using logs_serials_serial_type_serial_text_ops_idx on logs_serials logs_serials_1 (cost=0.56..7.24 rows=8 width=16) (actual time=0.008..0.016 rows=7 loops=136,722)

  • Index Cond: ((serial_type = unique_sn.serial_type) AND (serial = unique_sn.serial))
42. 6,020.706 6,020.706 ↑ 1.0 1 1,003,451

Index Scan using logs_v2_pkey on logs_v2 logs_v2_1 (cost=0.56..2.78 rows=1 width=34) (actual time=0.006..0.006 rows=1 loops=1,003,451)

  • Index Cond: (id = logs_serials_1.log_id)
43. 3.400 6.365 ↑ 1.1 7 1

Hash (cost=0.16..0.16 rows=8 width=6) (actual time=0.012..6.365 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 2.965 2.965 ↑ 1.1 7 1

CTE Scan on job_agents job_agents_1 (cost=0.00..0.16 rows=8 width=6) (actual time=0.005..2.965 rows=7 loops=1)

45.          

CTE filter_by_config

46. 13,180.908 13,180.908 ↓ 28.4 133,702 1

CTE Scan on filter_by_seen (cost=0.00..94.22 rows=4,711 width=88) (actual time=3,153.481..13,180.908 rows=133,702 loops=1)

47.          

CTE tmp_result

48. 13,233.789 13,233.792 ↓ 28.4 133,702 1

CTE Scan on filter_by_config (cost=8.20..125.98 rows=4,711 width=44) (actual time=3,153.502..13,233.792 rows=133,702 loops=1)

49.          

Initplan (forCTE Scan)

50. 0.003 0.003 ↑ 410.0 1 1

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

51. 38.018 13,322.736 ↑ 28.6 7 1

HashAggregate (cost=188.44..193.44 rows=200 width=76) (actual time=13,322.728..13,322.736 rows=7 loops=1)

  • Group Key: tmp_result.date
52. 13,284.718 13,284.718 ↓ 28.4 133,702 1

CTE Scan on tmp_result (cost=0.00..94.22 rows=4,711 width=12) (actual time=3,153.510..13,284.718 rows=133,702 loops=1)

Planning time : 6.296 ms
Execution time : 13,371.524 ms