explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z3ym : CTE test 6

Settings
# exclusive inclusive rows x rows loops node
1. 945.079 71,119.248 ↑ 30.9 52,731 1

GroupAggregate (cost=681,469.85..779,179.19 rows=1,628,489 width=233) (actual time=58,516.565..71,119.248 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.031 0.031 ↑ 1.0 49 1

Seq Scan on sensor (cost=0.00..2.49 rows=49 width=19) (actual time=0.014..0.031 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,835.534 70,174.169 ↓ 1.0 1,649,246 1

Sort (cost=681,467.36..685,538.58 rows=1,628,489 width=131) (actual time=58,516.546..70,174.169 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. 610.619 40,338.635 ↓ 1.0 1,649,246 1

Hash Left Join (cost=109,455.14..290,798.17 rows=1,628,489 width=131) (actual time=3,106.303..40,338.635 rows=1,649,246 loops=1)

  • Hash Cond: ((event.sid = udphdr.sid) AND (event.cid = udphdr.cid))
6. 505.965 39,706.813 ↓ 1.0 1,649,246 1

Hash Left Join (cost=107,939.59..280,733.06 rows=1,628,489 width=123) (actual time=3,084.877..39,706.813 rows=1,649,246 loops=1)

  • Hash Cond: (event.signature = signature.sig_id)
7. 501.242 39,195.738 ↓ 1.0 1,649,246 1

Hash Right Join (cost=107,595.72..276,112.47 rows=1,628,489 width=64) (actual time=3,079.697..39,195.738 rows=1,649,246 loops=1)

  • Hash Cond: (event.sid = sensornn.sid)
8. 12,696.096 38,694.419 ↓ 1.0 1,649,225 1

Hash Left Join (cost=107,594.12..253,719.15 rows=1,628,489 width=56) (actual time=3,079.613..38,694.419 rows=1,649,225 loops=1)

  • Hash Cond: ((event.sid = iphdr.sid) AND (event.cid = iphdr.cid))
9. 5,042.585 24,757.351 ↓ 1.0 1,649,225 1

Hash Left Join (cost=16,326.58..118,735.03 rows=1,628,489 width=32) (actual time=1,815.515..24,757.351 rows=1,649,225 loops=1)

  • Hash Cond: ((event.sid = tcphdr.sid) AND (event.cid = tcphdr.cid))
10. 19,522.553 19,522.553 ↓ 1.0 1,649,225 1

Seq Scan on event (cost=0.00..72,508.89 rows=1,628,489 width=24) (actual time=1,622.899..19,522.553 rows=1,649,225 loops=1)

11. 131.553 192.213 ↑ 1.0 386,663 1

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

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

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

13. 820.204 1,240.972 ↓ 1.0 1,651,372 1

Hash (cost=53,597.62..53,597.62 rows=1,651,262 width=36) (actual time=1,240.972..1,240.972 rows=1,651,372 loops=1)

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

Seq Scan on iphdr (cost=0.00..53,597.62 rows=1,651,262 width=36) (actual time=0.022..420.768 rows=1,651,372 loops=1)

15. 0.013 0.077 ↑ 1.0 49 1

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

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

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

17. 3.565 5.110 ↑ 1.0 9,372 1

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

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

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

19. 14.209 21.203 ↑ 1.0 46,822 1

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

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

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