explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T3UG

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 5,396.375 46,966.919 ↑ 1.0 1,942,373 1

Nested Loop Left Join (cost=357.32..82,121,335.40 rows=1,946,411 width=285) (actual time=1.304..46,966.919 rows=1,942,373 loops=1)

2. 951.127 18,262.068 ↑ 1.0 1,942,373 1

Nested Loop Left Join (cost=339.66..47,666,402.45 rows=1,946,411 width=99) (actual time=1.261..18,262.068 rows=1,942,373 loops=1)

3. 1,086.876 1,771.957 ↑ 1.0 1,942,373 1

Hash Join (cost=315.22..45,350.58 rows=1,946,411 width=34) (actual time=1.202..1,771.957 rows=1,942,373 loops=1)

  • Hash Cond: (wifiactivetagspositionhistory.watph_wat_mac = wifiactivetags.wat_mac)
4. 683.917 683.917 ↑ 1.0 1,942,373 1

Seq Scan on wifiactivetagspositionhistory (cost=0.00..39,891.77 rows=1,946,411 width=28) (actual time=0.012..683.917 rows=1,942,373 loops=1)

  • Filter: (watph_wap_basemac IS NOT NULL)
  • Rows Removed by Filter: 128204
5. 0.136 1.164 ↑ 1.0 632 1

Hash (cost=307.32..307.32 rows=632 width=6) (actual time=1.164..1.164 rows=632 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
6. 1.028 1.028 ↑ 1.0 632 1

Seq Scan on wifiactivetags (cost=0.00..307.32 rows=632 width=6) (actual time=0.033..1.028 rows=632 loops=1)

  • Filter: (wat_active IS TRUE)
7. 1,942.373 15,538.984 ↓ 0.0 0 1,942,373

Limit (cost=24.44..24.45 rows=1 width=79) (actual time=0.008..0.008 rows=0 loops=1,942,373)

8. 1,942.373 13,596.611 ↓ 0.0 0 1,942,373

Sort (cost=24.44..24.45 rows=1 width=79) (actual time=0.007..0.007 rows=0 loops=1,942,373)

  • Sort Key: wifiactivetagsassignmenthistory.watah_start
  • Sort Method: quicksort Memory: 25kB
9. 1,931.985 11,654.238 ↓ 0.0 0 1,942,373

Nested Loop Left Join (cost=15.30..24.43 rows=1 width=79) (actual time=0.006..0.006 rows=0 loops=1,942,373)

10. 0.000 9,711.865 ↓ 0.0 0 1,942,373

Nested Loop (cost=0.57..9.67 rows=1 width=44) (actual time=0.005..0.005 rows=0 loops=1,942,373)

11. 5,825.635 9,711.865 ↓ 0.0 0 1,942,373

Nested Loop (cost=0.29..9.33 rows=1 width=45) (actual time=0.005..0.005 rows=0 loops=1,942,373)

12. 3,884.746 3,884.746 ↓ 0.0 0 1,942,373

Seq Scan on wifiactivetagsassignmenthistory (cost=0.00..1.03 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=1,942,373)

  • Filter: ((wifiactivetagspositionhistory.watph_from >= watah_start) AND (watah_mac = wifiactivetagspositionhistory.watph_wat_mac))
  • Rows Removed by Filter: 2
13. 1.484 1.484 ↑ 1.0 1 371

Index Scan using nodeslogical_pkey on nodeslogical nla (cost=0.29..8.30 rows=1 width=37) (actual time=0.004..0.004 rows=1 loops=371)

  • Index Cond: (nl_id = wifiactivetagsassignmenthistory.watah_nl_id)
14. 1.113 1.113 ↑ 1.0 1 371

Index Scan using entities_pkey on entities (cost=0.29..0.34 rows=1 width=7) (actual time=0.003..0.003 rows=1 loops=371)

  • Index Cond: (e_id = nla.nl_e_id)
15. 0.371 10.388 ↓ 0.0 0 371

Limit (cost=14.73..14.74 rows=1 width=37) (actual time=0.028..0.028 rows=0 loops=371)

16. 0.742 10.017 ↓ 0.0 0 371

Sort (cost=14.73..14.74 rows=1 width=37) (actual time=0.027..0.027 rows=0 loops=371)

  • Sort Key: tagdeviceshistory.tdh_date_start DESC
  • Sort Method: quicksort Memory: 25kB
17. 1.113 9.275 ↓ 0.0 0 371

Nested Loop (cost=0.29..14.72 rows=1 width=37) (actual time=0.025..0.025 rows=0 loops=371)

18. 8.162 8.162 ↓ 0.0 0 371

Seq Scan on tagdeviceshistory (cost=0.00..6.42 rows=1 width=8) (actual time=0.022..0.022 rows=0 loops=371)

  • Filter: ((tdh_date_end IS NULL) AND (tdh_nl_lamp_id = nla.nl_id) AND (to_timestamp((tdh_date_start)::double precision) >= wifiactivetagsassignmenthistory.watah_start))
  • Rows Removed by Filter: 221
19. 0.000 0.000 ↓ 0.0 0

Index Scan using nodeslogical_pkey on nodeslogical nlp (cost=0.29..8.30 rows=1 width=33) (never executed)

  • Index Cond: (nl_id = tagdeviceshistory.tdh_nl_personnel_id)
20. 1,942.373 23,308.476 ↓ 0.0 0 1,942,373

Limit (cost=17.65..17.66 rows=1 width=67) (actual time=0.012..0.012 rows=0 loops=1,942,373)

21. 1,942.373 21,366.103 ↓ 0.0 0 1,942,373

Sort (cost=17.65..17.66 rows=1 width=67) (actual time=0.011..0.011 rows=0 loops=1,942,373)

  • Sort Key: wifiaccesspointsassignmenthistory.wapah_start
  • Sort Method: quicksort Memory: 25kB
22. 933.533 19,423.730 ↓ 0.0 0 1,942,373

Nested Loop Left Join (cost=0.55..17.64 rows=1 width=67) (actual time=0.009..0.010 rows=0 loops=1,942,373)

23. 7,769.492 17,481.357 ↓ 0.0 0 1,942,373

Nested Loop (cost=0.27..9.34 rows=1 width=18) (actual time=0.009..0.009 rows=0 loops=1,942,373)

24. 5,827.119 5,827.119 ↑ 1.0 1 1,942,373

Index Scan using wifiaccesspoints_pkey on wifiaccesspoints (cost=0.27..8.29 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=1,942,373)

  • Index Cond: (wap_basemac = wifiactivetagspositionhistory.watph_wap_basemac)
25. 3,884.746 3,884.746 ↓ 0.0 0 1,942,373

Seq Scan on wifiaccesspointsassignmenthistory (cost=0.00..1.04 rows=1 width=18) (actual time=0.002..0.002 rows=0 loops=1,942,373)

  • Filter: ((wifiactivetagspositionhistory.watph_from >= wapah_start) AND (wapah_basemac = wifiactivetagspositionhistory.watph_wap_basemac))
  • Rows Removed by Filter: 3
26. 1,008.840 1,008.840 ↑ 1.0 1 336,280

Index Scan using nodeslogical_pkey on nodeslogical (cost=0.29..8.30 rows=1 width=53) (actual time=0.003..0.003 rows=1 loops=336,280)

  • Index Cond: (nl_id = wifiaccesspointsassignmenthistory.wapah_nl_id)
Planning time : 2.041 ms