explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O0z : CTE test 7

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

GroupAggregate (cost=687,973.23..786,926.73 rows=1,649,225 width=233) (actual time=53,971.460..66,451.772 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. 28,813.616 65,501.736 ↓ 1.0 1,649,246 1

Sort (cost=687,970.74..692,093.80 rows=1,649,225 width=131) (actual time=53,971.442..65,501.736 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. 603.653 36,688.120 ↓ 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,440.476..36,688.120 rows=1,649,246 loops=1)

  • Hash Cond: ((event.sid = udphdr.sid) AND (event.cid = udphdr.cid))
6. 497.551 36,064.280 ↓ 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,420.204..36,064.280 rows=1,649,246 loops=1)

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

  • Hash Cond: (event.sid = sensornn.sid)
8. 11,554.399 35,050.872 ↑ 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,416.164..35,050.872 rows=1,649,225 loops=1)

  • Hash Cond: ((event.sid = iphdr.sid) AND (event.cid = iphdr.cid))
9. 5,281.948 22,071.097 ↑ 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,909.722..22,071.097 rows=1,649,225 loops=1)

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

11. 130.642 190.905 ↑ 1.0 386,663 1

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

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,150kB
12. 60.263 60.263 ↑ 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.263 rows=386,663 loops=1)

13. 995.878 1,425.376 ↓ 1.0 1,651,372 1

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

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

15. 0.014 0.079 ↑ 1.0 49 1

Hash (cost=0.98..0.98 rows=49 width=12) (actual time=0.079..0.079 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.017..0.065 rows=49 loops=1)

17. 2.431 3.917 ↑ 1.0 9,372 1

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

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

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

19. 13.203 20.187 ↑ 1.0 46,822 1

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

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

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