explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QHu4

Settings
# exclusive inclusive rows x rows loops node
1. 1,577.249 102,383.495 ↑ 31.3 53,024 1

GroupAggregate (cost=782,434.90..919,526.55 rows=1,661,717 width=326) (actual time=80,412.616..102,383.495 rows=53,024 loops=1)

  • Group Key: sensor.dev_id, signature.sig_id, iphdr.ip_src, iphdr.ip_src_name, iphdr.ip_dst, iphdr.ip_dst_name, sensor.cl_id
2. 56,420.385 100,806.246 ↑ 1.0 1,649,246 1

Sort (cost=782,434.90..786,589.19 rows=1,661,717 width=189) (actual time=80,412.594..100,806.246 rows=1,649,246 loops=1)

  • Sort Key: sensor.dev_id, signature.sig_id, iphdr.ip_src, iphdr.ip_src_name, iphdr.ip_dst, iphdr.ip_dst_name, sensor.cl_id
  • Sort Method: external merge Disk: 305,928kB
3. 650.773 44,385.861 ↑ 1.0 1,649,246 1

Hash Left Join (cost=117,517.80..304,039.28 rows=1,661,717 width=189) (actual time=11,010.381..44,385.861 rows=1,649,246 loops=1)

  • Hash Cond: ((event.cid = udphdr.cid) AND (event.sid = udphdr.sid))
4. 564.117 43,716.435 ↑ 1.0 1,649,246 1

Hash Left Join (cost=116,002.25..293,799.71 rows=1,661,717 width=181) (actual time=10,991.511..43,716.435 rows=1,649,246 loops=1)

  • Hash Cond: (event.signature = signature.sig_id)
5. 515.954 43,148.708 ↑ 1.0 1,649,246 1

Hash Right Join (cost=115,658.38..289,091.87 rows=1,661,717 width=122) (actual time=10,987.820..43,148.708 rows=1,649,246 loops=1)

  • Hash Cond: (event.sid = sensor.sid)
6. 17,368.533 42,632.712 ↑ 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,987.762..42,632.712 rows=1,649,225 loops=1)

  • Hash Cond: ((event.cid = iphdr.cid) AND (event.sid = iphdr.sid))
7. 5,462.012 23,317.029 ↑ 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,175.518..23,317.029 rows=1,649,225 loops=1)

  • Hash Cond: ((event.cid = tcphdr.cid) AND (event.sid = tcphdr.sid))
8. 17,642.190 17,642.190 ↑ 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,962.451..17,642.190 rows=1,649,225 loops=1)

9. 154.248 212.827 ↑ 1.0 386,663 1

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

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

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

11. 1,438.596 1,947.150 ↓ 1.0 1,651,372 1

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

  • Buckets: 65,536 Batches: 64 Memory Usage: 3,469kB
12. 508.554 508.554 ↓ 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.021..508.554 rows=1,651,372 loops=1)

13. 0.015 0.042 ↑ 1.0 49 1

Hash (cost=2.49..2.49 rows=49 width=19) (actual time=0.042..0.042 rows=49 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
14. 0.027 0.027 ↑ 1.0 49 1

Seq Scan on sensor (cost=0.00..2.49 rows=49 width=19) (actual time=0.011..0.027 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))
15. 2.168 3.610 ↑ 1.0 9,372 1

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

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

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

17. 12.034 18.653 ↑ 1.0 46,822 1

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

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

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