explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BBu3

Settings
# exclusive inclusive rows x rows loops node
1. 956.421 74,176.857 ↑ 31.3 52,731 1

GroupAggregate (cost=687,973.23..786,926.73 rows=1,649,225 width=233) (actual time=59,267.613..74,176.857 rows=52,731 loops=1)

  • Output: sensornn.dev_id, signature.sig_id, iphdr.ip_src, iphdr.ip_dst, sensornn.cl_id, signature.sig_name, signature.sig_class_id, max(event."timestamp"), max(event.sid), max(event.cid), max(event.signature), timezone('CDT'::text, max(event."timestamp")), count(event."timestamp"), max(iphdr.ip_proto), max(iphdr.ip_src_country_code), max(iphdr.ip_dst_country_code), max(tcphdr.tcp_sport), max(tcphdr.tcp_dport), max(udphdr.udp_sport), max(udphdr.udp_dport), sensornn.dev_id, signature.sig_id, iphdr.ip_src, iphdr.ip_dst, sensornn.cl_id
  • 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 public.sensor (cost=0.00..2.49 rows=49 width=19) (actual time=0.015..0.032 rows=49 loops=1)

  • Output: sensor.interface, sensor.encoding, sensor.dev_id, sensor.cl_id, sensor.sid
  • Filter: ((sensor.handler_group_2_shifts IS NOT NULL) OR (sensor.handler_group_3_shifts IS NOT NULL) OR (sensor.handler_group_4_shifts IS NOT NULL) OR (sensor.handler_group_5_shifts IS NOT NULL) OR (sensor.handler_group_6_shifts IS NOT NULL) OR (sensor.handler_group_7_shifts IS NOT NULL) OR (sensor.handler_group_8_shifts IS NOT NULL) OR (sensor.handler_group_9_shifts IS NOT NULL) OR (sensor.handler_group_10_shifts IS NOT NULL))
4. 34,117.145 73,220.436 ↓ 1.0 1,649,246 1

Sort (cost=687,970.74..692,093.80 rows=1,649,225 width=131) (actual time=59,267.595..73,220.436 rows=1,649,246 loops=1)

  • Output: sensornn.dev_id, signature.sig_id, iphdr.ip_src, iphdr.ip_dst, sensornn.cl_id, signature.sig_name, signature.sig_class_id, event."timestamp", event.sid, event.cid, event.signature, iphdr.ip_proto, iphdr.ip_src_country_code, iphdr.ip_dst_country_code, tcphdr.tcp_sport, tcphdr.tcp_dport, udphdr.udp_sport, udphdr.udp_dport
  • 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. 606.858 39,103.291 ↓ 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,528.804..39,103.291 rows=1,649,246 loops=1)

  • Output: sensornn.dev_id, signature.sig_id, iphdr.ip_src, iphdr.ip_dst, sensornn.cl_id, signature.sig_name, signature.sig_class_id, event."timestamp", event.sid, event.cid, event.signature, iphdr.ip_proto, iphdr.ip_src_country_code, iphdr.ip_dst_country_code, tcphdr.tcp_sport, tcphdr.tcp_dport, udphdr.udp_sport, udphdr.udp_dport
  • Inner Unique: true
  • Hash Cond: ((event.sid = udphdr.sid) AND (event.cid = udphdr.cid))
6. 520.805 38,476.704 ↓ 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,508.994..38,476.704 rows=1,649,246 loops=1)

  • Output: sensornn.dev_id, sensornn.cl_id, event."timestamp", event.sid, event.cid, event.signature, signature.sig_id, signature.sig_name, signature.sig_class_id, iphdr.ip_src, iphdr.ip_dst, iphdr.ip_proto, iphdr.ip_src_country_code, iphdr.ip_dst_country_code, tcphdr.tcp_sport, tcphdr.tcp_dport
  • Inner Unique: true
  • Hash Cond: (event.signature = signature.sig_id)
7. 501.189 37,951.897 ↓ 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,504.963..37,951.897 rows=1,649,246 loops=1)

  • Output: sensornn.dev_id, sensornn.cl_id, event."timestamp", event.sid, event.cid, event.signature, iphdr.ip_src, iphdr.ip_dst, iphdr.ip_proto, iphdr.ip_src_country_code, iphdr.ip_dst_country_code, tcphdr.tcp_sport, tcphdr.tcp_dport
  • Hash Cond: (event.sid = sensornn.sid)
8. 16,361.803 37,450.629 ↑ 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,504.876..37,450.629 rows=1,649,225 loops=1)

  • Output: event."timestamp", event.sid, event.cid, event.signature, iphdr.ip_src, iphdr.ip_dst, iphdr.ip_proto, iphdr.ip_src_country_code, iphdr.ip_dst_country_code, tcphdr.tcp_sport, tcphdr.tcp_dport
  • Inner Unique: true
  • Hash Cond: ((event.sid = iphdr.sid) AND (event.cid = iphdr.cid))
9. 5,437.555 19,766.067 ↑ 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,181.801..19,766.067 rows=1,649,225 loops=1)

  • Output: event."timestamp", event.sid, event.cid, event.signature, tcphdr.tcp_sport, tcphdr.tcp_dport
  • Inner Unique: true
  • Hash Cond: ((event.sid = tcphdr.sid) AND (event.cid = tcphdr.cid))
10. 14,138.764 14,138.764 ↑ 1.0 1,649,225 1

Seq Scan on public.event (cost=0.00..72,716.25 rows=1,649,225 width=24) (actual time=1,991.820..14,138.764 rows=1,649,225 loops=1)

  • Output: event."timestamp", event.sid, event.cid, event.signature
11. 129.726 189.748 ↑ 1.0 386,663 1

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

  • Output: tcphdr.tcp_sport, tcphdr.tcp_dport, tcphdr.sid, tcphdr.cid
  • Buckets: 65,536 Batches: 8 Memory Usage: 3,150kB
12. 60.022 60.022 ↑ 1.0 386,663 1

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

  • Output: tcphdr.tcp_sport, tcphdr.tcp_dport, tcphdr.sid, tcphdr.cid
13. 898.231 1,322.759 ↓ 1.0 1,651,372 1

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

  • Output: iphdr.ip_src, iphdr.ip_dst, iphdr.ip_proto, iphdr.ip_src_country_code, iphdr.ip_dst_country_code, iphdr.sid, iphdr.cid
  • Buckets: 65,536 Batches: 64 Memory Usage: 2,309kB
14. 424.528 424.528 ↓ 1.0 1,651,372 1

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

  • Output: iphdr.ip_src, iphdr.ip_dst, iphdr.ip_proto, iphdr.ip_src_country_code, iphdr.ip_dst_country_code, iphdr.sid, iphdr.cid
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)

  • Output: sensornn.dev_id, sensornn.cl_id, sensornn.sid
  • 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)

  • Output: sensornn.dev_id, sensornn.cl_id, sensornn.sid
17. 2.483 4.002 ↑ 1.0 9,372 1

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

  • Output: signature.sig_id, signature.sig_name, signature.sig_class_id
  • Buckets: 16,384 Batches: 1 Memory Usage: 999kB
18. 1.519 1.519 ↑ 1.0 9,372 1

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

  • Output: signature.sig_id, signature.sig_name, signature.sig_class_id
19. 12.852 19.729 ↑ 1.0 46,822 1

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

  • Output: udphdr.udp_sport, udphdr.udp_dport, udphdr.sid, udphdr.cid
  • Buckets: 65,536 Batches: 1 Memory Usage: 3,073kB
20. 6.877 6.877 ↑ 1.0 46,822 1

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

  • Output: udphdr.udp_sport, udphdr.udp_dport, udphdr.sid, udphdr.cid
Planning time : 1.905 ms
Execution time : 74,183.373 ms