explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A1hP

Settings
# exclusive inclusive rows x rows loops node
1. 1,588.916 98,081.055 ↑ 31.3 53,024 1

GroupAggregate (cost=834,628.20..971,719.85 rows=1,661,717 width=326) (actual time=78,406.408..98,081.055 rows=53,024 loops=1)

  • Group Key: sensornn.dev_id, signature.sig_id, iphdr.ip_src, iphdr.ip_src_name, iphdr.ip_dst, iphdr.ip_dst_name, sensornn.cl_id
2.          

CTE sensornn

3. 0.028 0.028 ↑ 1.0 49 1

Seq Scan on sensor (cost=0.00..2.49 rows=49 width=19) (actual time=0.012..0.028 rows=49 loops=1)

  • Filter: ((handler_group_2_shifts IS NOT NULL) OR (handler_group_3_shifts IS NOT NULL) OR (handler_group_4_shifts IS NOT NULL) OR (handler_group_5_shifts IS NOT NULL) OR (handler_group_6_shifts IS NOT NULL) OR (handler_group_7_shifts IS NOT NULL) OR (handler_group_8_shifts IS NOT NULL) OR (handler_group_9_shifts IS NOT NULL) OR (handler_group_10_shifts IS NOT NULL))
4. 52,014.432 96,492.139 ↑ 1.0 1,649,246 1

Sort (cost=834,625.71..838,780.00 rows=1,661,717 width=216) (actual time=78,406.386..96,492.139 rows=1,649,246 loops=1)

  • Sort Key: sensornn.dev_id, signature.sig_id, iphdr.ip_src, iphdr.ip_src_name, iphdr.ip_dst, iphdr.ip_dst_name, sensornn.cl_id
  • Sort Method: external merge Disk: 305,928kB
5. 642.561 44,477.707 ↑ 1.0 1,649,246 1

Hash Left Join (cost=117,516.29..322,147.09 rows=1,661,717 width=216) (actual time=10,729.886..44,477.707 rows=1,649,246 loops=1)

  • Hash Cond: ((event.cid = udphdr.cid) AND (event.sid = udphdr.sid))
6. 563.951 43,817.582 ↑ 1.0 1,649,246 1

Hash Left Join (cost=116,000.74..311,907.52 rows=1,661,717 width=208) (actual time=10,712.243..43,817.582 rows=1,649,246 loops=1)

  • Hash Cond: (event.signature = signature.sig_id)
7. 530.577 43,250.262 ↑ 1.0 1,649,246 1

Hash Right Join (cost=115,656.87..307,199.67 rows=1,661,717 width=149) (actual time=10,708.828..43,250.262 rows=1,649,246 loops=1)

  • Hash Cond: (event.sid = sensornn.sid)
8. 17,488.846 42,719.623 ↑ 1.0 1,649,225 1

Hash Left Join (cost=115,655.27..284,349.47 rows=1,661,717 width=107) (actual time=10,708.756..42,719.623 rows=1,649,225 loops=1)

  • Hash Cond: ((event.cid = iphdr.cid) AND (event.sid = iphdr.sid))
9. 5,591.338 23,345.571 ↑ 1.0 1,649,225 1

Hash Left Join (cost=16,326.58..126,123.76 rows=1,661,717 width=44) (actual time=2,125.637..23,345.571 rows=1,649,225 loops=1)

  • Hash Cond: ((event.cid = tcphdr.cid) AND (event.sid = tcphdr.sid))
10. 17,578.640 17,578.640 ↑ 1.0 1,649,225 1

Seq Scan on event (cost=0.00..72,841.17 rows=1,661,717 width=36) (actual time=1,949.961..17,578.640 rows=1,649,225 loops=1)

11. 118.821 175.593 ↑ 1.0 386,663 1

Hash (cost=8,260.63..8,260.63 rows=386,663 width=20) (actual time=175.593..175.593 rows=386,663 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 2,966kB
12. 56.772 56.772 ↑ 1.0 386,663 1

Seq Scan on tcphdr (cost=0.00..8,260.63 rows=386,663 width=20) (actual time=0.013..56.772 rows=386,663 loops=1)

13. 1,380.487 1,885.206 ↓ 1.0 1,651,372 1

Hash (cost=53,597.28..53,597.28 rows=1,651,228 width=75) (actual time=1,885.206..1,885.206 rows=1,651,372 loops=1)

  • Buckets: 65,536 Batches: 64 Memory Usage: 3,469kB
14. 504.719 504.719 ↓ 1.0 1,651,372 1

Seq Scan on iphdr (cost=0.00..53,597.28 rows=1,651,228 width=75) (actual time=0.011..504.719 rows=1,651,372 loops=1)

15. 0.011 0.062 ↑ 1.0 49 1

Hash (cost=0.98..0.98 rows=49 width=46) (actual time=0.062..0.062 rows=49 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
16. 0.051 0.051 ↑ 1.0 49 1

CTE Scan on sensornn (cost=0.00..0.98 rows=49 width=46) (actual time=0.016..0.051 rows=49 loops=1)

17. 1.935 3.369 ↑ 1.0 9,372 1

Hash (cost=226.72..226.72 rows=9,372 width=59) (actual time=3.369..3.369 rows=9,372 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 999kB
18. 1.434 1.434 ↑ 1.0 9,372 1

Seq Scan on signature (cost=0.00..226.72 rows=9,372 width=59) (actual time=0.011..1.434 rows=9,372 loops=1)

19. 11.047 17.564 ↑ 1.0 46,822 1

Hash (cost=813.22..813.22 rows=46,822 width=20) (actual time=17.564..17.564 rows=46,822 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,890kB
20. 6.517 6.517 ↑ 1.0 46,822 1

Seq Scan on udphdr (cost=0.00..813.22 rows=46,822 width=20) (actual time=0.011..6.517 rows=46,822 loops=1)