explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I9Ew

Settings
# exclusive inclusive rows x rows loops node
1. 805.130 62,790.344 ↑ 31.3 52,731 1

GroupAggregate (cost=665,426.23..756,133.60 rows=1,649,225 width=221) (actual time=50,779.361..62,790.344 rows=52,731 loops=1)

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

CTE sensornn

3. 0.030 0.030 ↑ 1.0 49 1

Seq Scan on sensor (cost=0.00..2.49 rows=49 width=19) (actual time=0.014..0.030 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. 26,542.052 61,985.214 ↓ 1.0 1,649,246 1

Sort (cost=665,423.74..669,546.80 rows=1,649,225 width=119) (actual time=50,779.342..61,985.214 rows=1,649,246 loops=1)

  • Sort Key: sensornn.dev_id, signature.sig_id, iphdr.ip_src, iphdr.ip_dst, sensornn.cl_id
  • Sort Method: external merge Disk: 171,568kB
5. 608.083 35,443.162 ↓ 1.0 1,649,246 1

Hash Left Join (cost=109,438.01..292,176.57 rows=1,649,225 width=119) (actual time=3,515.202..35,443.162 rows=1,649,246 loops=1)

  • Hash Cond: ((event.sid = udphdr.sid) AND (event.cid = udphdr.cid))
6. 508.605 34,810.518 ↓ 1.0 1,649,246 1

Hash Left Join (cost=107,922.46..282,002.59 rows=1,649,225 width=123) (actual time=3,490.564..34,810.518 rows=1,649,246 loops=1)

  • Hash Cond: (event.signature = signature.sig_id)
7. 495.207 34,297.863 ↓ 1.0 1,649,246 1

Hash Right Join (cost=107,578.59..277,327.55 rows=1,649,225 width=64) (actual time=3,486.485..34,297.863 rows=1,649,246 loops=1)

  • Hash Cond: (event.sid = sensornn.sid)
8. 13,090.997 33,802.580 ↑ 1.0 1,649,225 1

Hash Left Join (cost=107,577.00..254,649.11 rows=1,649,225 width=56) (actual time=3,486.402..33,802.580 rows=1,649,225 loops=1)

  • Hash Cond: ((event.sid = iphdr.sid) AND (event.cid = iphdr.cid))
9. 5,650.718 19,399.519 ↑ 1.0 1,649,225 1

Hash Left Join (cost=16,326.58..119,295.26 rows=1,649,225 width=32) (actual time=2,174.000..19,399.519 rows=1,649,225 loops=1)

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

Seq Scan on event (cost=0.00..72,716.25 rows=1,649,225 width=24) (actual time=1,984.075..13,559.112 rows=1,649,225 loops=1)

11. 129.777 189.689 ↑ 1.0 386,663 1

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

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,150kB
12. 59.912 59.912 ↑ 1.0 386,663 1

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

13. 889.787 1,312.064 ↓ 1.0 1,651,372 1

Hash (cost=53,592.37..53,592.37 rows=1,650,737 width=36) (actual time=1,312.064..1,312.064 rows=1,651,372 loops=1)

  • Buckets: 65,536 Batches: 64 Memory Usage: 2,309kB
14. 422.277 422.277 ↓ 1.0 1,651,372 1

Seq Scan on iphdr (cost=0.00..53,592.37 rows=1,650,737 width=36) (actual time=0.011..422.277 rows=1,651,372 loops=1)

15. 0.013 0.076 ↑ 1.0 49 1

Hash (cost=0.98..0.98 rows=49 width=12) (actual time=0.076..0.076 rows=49 loops=1)

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

CTE Scan on sensornn (cost=0.00..0.98 rows=49 width=12) (actual time=0.018..0.063 rows=49 loops=1)

17. 2.526 4.050 ↑ 1.0 9,372 1

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

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

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

19. 15.061 24.561 ↑ 1.0 46,822 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,073kB
20. 9.500 9.500 ↑ 1.0 46,822 1

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

Planning time : 1.886 ms
Execution time : 62,796.563 ms