explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F7DA : CTE Test 3

Settings
# exclusive inclusive rows x rows loops node
1. 1,297.701 99,836.455 ↑ 30.9 52,731 1

GroupAggregate (cost=760,985.85..887,193.75 rows=1,628,489 width=287) (actual time=85,628.754..99,836.455 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.029 0.029 ↑ 1.0 49 1

Seq Scan on sensor (cost=0.00..2.49 rows=49 width=19) (actual time=0.014..0.029 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. 37,449.733 98,538.754 ↓ 1.0 1,649,246 1

Sort (cost=760,983.36..765,054.58 rows=1,628,489 width=177) (actual time=85,628.730..98,538.754 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: 229,648kB
5. 619.491 61,089.021 ↓ 1.0 1,649,246 1

Hash Left Join (cost=109,455.14..303,520.17 rows=1,628,489 width=177) (actual time=14,674.400..61,089.021 rows=1,649,246 loops=1)

  • Hash Cond: ((event.sid = udphdr.sid) AND (event.cid = udphdr.cid))
6. 539.451 60,450.049 ↓ 1.0 1,649,246 1

Hash Left Join (cost=107,939.59..293,455.06 rows=1,628,489 width=169) (actual time=14,654.708..60,450.049 rows=1,649,246 loops=1)

  • Hash Cond: (event.signature = signature.sig_id)
7. 573.888 59,906.402 ↓ 1.0 1,649,246 1

Hash Right Join (cost=107,595.72..288,834.47 rows=1,628,489 width=110) (actual time=14,650.445..59,906.402 rows=1,649,246 loops=1)

  • Hash Cond: (event.sid = sensornn.sid)
8. 16,798.402 59,332.431 ↓ 1.0 1,649,225 1

Hash Left Join (cost=107,594.12..266,441.15 rows=1,628,489 width=68) (actual time=14,650.347..59,332.431 rows=1,649,225 loops=1)

  • Hash Cond: ((event.sid = iphdr.sid) AND (event.cid = iphdr.cid))
9. 7,204.776 28,175.328 ↓ 1.0 1,649,225 1

Hash Left Join (cost=16,326.58..125,097.03 rows=1,628,489 width=44) (actual time=291.242..28,175.328 rows=1,649,225 loops=1)

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

Seq Scan on event (cost=0.00..72,508.89 rows=1,628,489 width=36) (actual time=27.279..20,706.791 rows=1,649,225 loops=1)

11. 201.839 263.761 ↑ 1.0 386,663 1

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

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

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

13. 1,069.059 14,358.701 ↓ 1.0 1,651,372 1

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

  • Buckets: 65,536 Batches: 64 Memory Usage: 2,509kB
14. 13,289.642 13,289.642 ↓ 1.0 1,651,372 1

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

15. 0.024 0.083 ↑ 1.0 49 1

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

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

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

17. 2.674 4.196 ↑ 1.0 9,372 1

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

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

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

19. 12.547 19.481 ↑ 1.0 46,822 1

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

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

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