explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9nJI

Settings
# exclusive inclusive rows x rows loops node
1. 946.213 66,275.808 ↑ 31.3 52,731 1

GroupAggregate (cost=687,973.23..786,926.73 rows=1,649,225 width=233) (actual time=53,647.534..66,275.808 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.032 0.032 ↑ 1.0 49 1

Seq Scan on sensor (cost=0.00..2.49 rows=49 width=19) (actual time=0.015..0.032 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. 29,161.043 65,329.595 ↓ 1.0 1,649,246 1

Sort (cost=687,970.74..692,093.80 rows=1,649,225 width=131) (actual time=53,647.515..65,329.595 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: 197,384kB
5. 619.307 36,168.552 ↓ 1.0 1,649,246 1

Hash Left Join (cost=109,438.01..292,176.57 rows=1,649,225 width=131) (actual time=3,362.352..36,168.552 rows=1,649,246 loops=1)

  • Hash Cond: ((event.sid = udphdr.sid) AND (event.cid = udphdr.cid))
6. 515.541 35,529.115 ↓ 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,342.140..35,529.115 rows=1,649,246 loops=1)

  • Hash Cond: (event.signature = signature.sig_id)
7. 501.971 35,009.617 ↓ 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,338.153..35,009.617 rows=1,649,246 loops=1)

  • Hash Cond: (event.sid = sensornn.sid)
8. 12,131.885 34,507.568 ↑ 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,338.067..34,507.568 rows=1,649,225 loops=1)

  • Hash Cond: ((event.sid = iphdr.sid) AND (event.cid = iphdr.cid))
9. 4,911.113 20,964.188 ↑ 1.0 1,649,225 1

Hash Left Join (cost=16,326.58..119,295.26 rows=1,649,225 width=32) (actual time=1,907.184..20,964.188 rows=1,649,225 loops=1)

  • Hash Cond: ((event.sid = tcphdr.sid) AND (event.cid = tcphdr.cid))
10. 15,866.671 15,866.671 ↑ 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,720.549..15,866.671 rows=1,649,225 loops=1)

11. 126.383 186.404 ↑ 1.0 386,663 1

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

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

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

13. 986.277 1,411.495 ↓ 1.0 1,651,372 1

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

  • Buckets: 65,536 Batches: 64 Memory Usage: 2,309kB
14. 425.218 425.218 ↓ 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.010..425.218 rows=1,651,372 loops=1)

15. 0.013 0.078 ↑ 1.0 49 1

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

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

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

17. 2.443 3.957 ↑ 1.0 9,372 1

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

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

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

19. 13.173 20.130 ↑ 1.0 46,822 1

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

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

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

Planning time : 1.917 ms
Execution time : 66,282.272 ms