explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FCgB

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.033 160.570 ↑ 1.0 1 1

Aggregate (cost=7,997.03..7,997.04 rows=1 width=0) (actual time=160.570..160.570 rows=1 loops=1)

2. 1.427 160.537 ↑ 3.1 100 1

Merge Join (cost=7,973.13..7,996.27 rows=306 width=0) (actual time=157.002..160.537 rows=100 loops=1)

  • Merge Cond: (all_devices_emon_status_data.nid = node_parents.nid)
3. 8.056 56.927 ↓ 1.9 5,365 1

Sort (cost=1,844.23..1,851.21 rows=2,795 width=16) (actual time=54.778..56.927 rows=5,365 loops=1)

  • Sort Key: all_devices_emon_status_data.nid
  • Sort Method: external merge Disk: 128kB
4. 2.445 48.871 ↓ 1.9 5,390 1

Subquery Scan on all_devices_emon_status_data (cost=1,027.32..1,635.23 rows=2,795 width=16) (actual time=30.911..48.871 rows=5,390 loops=1)

5. 11.848 46.426 ↓ 1.9 5,390 1

Unique (cost=1,027.32..1,607.28 rows=2,795 width=153) (actual time=30.910..46.426 rows=5,390 loops=1)

6. 24.932 34.578 ↓ 1.9 5,390 1

Sort (cost=1,027.32..1,034.31 rows=2,795 width=153) (actual time=30.909..34.578 rows=5,390 loops=1)

  • Sort Key: "*SELECT* 1".endpoint_id, "*SELECT* 1".ipaddress, "*SELECT* 1".lastcontactedon, "*SELECT* 1".syncedon, "*SELECT* 1".user_name, "*SELECT* 1".d
  • Sort Method: external merge Disk: 808kB
7. 2.057 9.646 ↓ 1.9 5,390 1

Append (cost=5.38..650.33 rows=2,795 width=153) (actual time=0.126..9.646 rows=5,390 loops=1)

8. 0.115 0.563 ↑ 1.0 150 1

Subquery Scan on *SELECT* 1 (cost=5.38..29.44 rows=150 width=2,329) (actual time=0.126..0.563 rows=150 loops=1)

9. 0.231 0.448 ↑ 1.0 150 1

Hash Join (cost=5.38..27.94 rows=150 width=2,329) (actual time=0.125..0.448 rows=150 loops=1)

  • Hash Cond: (w.endpoint_id = f.endpoint_id)
10. 0.111 0.111 ↑ 1.0 150 1

Seq Scan on emon_status_wide_data w (cost=0.00..20.50 rows=150 width=2,297) (actual time=0.004..0.111 rows=150 loops=1)

11. 0.059 0.106 ↑ 1.0 150 1

Hash (cost=3.50..3.50 rows=150 width=36) (actual time=0.106..0.106 rows=150 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.047 0.047 ↑ 1.0 150 1

Seq Scan on emon_frequent_data f (cost=0.00..3.50 rows=150 width=36) (actual time=0.005..0.047 rows=150 loops=1)

13. 6.784 7.026 ↓ 2.0 5,240 1

Seq Scan on device (cost=28.31..594.44 rows=2,645 width=30) (actual time=0.326..7.026 rows=5,240 loops=1)

  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 150
14.          

SubPlan (forSeq Scan)

15. 0.118 0.242 ↑ 1.0 150 1

Hash Join (cost=5.38..27.94 rows=150 width=16) (actual time=0.099..0.242 rows=150 loops=1)

  • Hash Cond: (w.endpoint_id = f.endpoint_id)
16. 0.039 0.039 ↑ 1.0 150 1

Seq Scan on emon_status_wide_data w (cost=0.00..20.50 rows=150 width=20) (actual time=0.001..0.039 rows=150 loops=1)

17. 0.044 0.085 ↑ 1.0 150 1

Hash (cost=3.50..3.50 rows=150 width=4) (actual time=0.085..0.085 rows=150 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
18. 0.041 0.041 ↑ 1.0 150 1

Seq Scan on emon_frequent_data f (cost=0.00..3.50 rows=150 width=4) (actual time=0.003..0.041 rows=150 loops=1)

19. 0.094 102.183 ↑ 12.2 100 1

Sort (cost=6,128.91..6,131.96 rows=1,220 width=16) (actual time=102.162..102.183 rows=100 loops=1)

  • Sort Key: node_parents.nid
  • Sort Method: quicksort Memory: 20kB
20. 102.089 102.089 ↑ 12.2 100 1

Seq Scan on node_parents (cost=0.00..6,066.36 rows=1,220 width=16) (actual time=102.030..102.089 rows=100 loops=1)

  • Filter: ((parent_nid)::text = '6589a325-de9e-44b1-84ee-0b2023093d13'::text)
  • Rows Removed by Filter: 243364