explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KiYP : Optimization for: plan #T3UG

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2,327.582 44,350.797 ↑ 5.8 336,280 1

Nested Loop (cost=357.32..82,121,335.40 rows=1,946,411 width=285) (actual time=325.237..44,350.797 rows=336,280 loops=1)

2. 1,397.264 18,714.739 ↑ 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=7.103..18,714.739 rows=1,942,373 loops=1)

3. 995.103 1,778.491 ↑ 1.0 1,942,373 1

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

  • Hash Cond: (wifiactivetagspositionhistory.watph_wat_mac = wifiactivetags.wat_mac)
4. 776.512 776.512 ↑ 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.064..776.512 rows=1,942,373 loops=1)

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

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

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

Seq Scan on wifiactivetags (cost=0.00..307.32 rows=632 width=6) (actual time=0.014..6.637 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,928.275 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.000 14.098 ↓ 0.0 0 371

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

16. 0.742 14.098 ↓ 0.0 0 371

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

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

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

18. 8.533 8.533 ↓ 0.0 0 371

Seq Scan on tagdeviceshistory (cost=0.00..6.42 rows=1 width=8) (actual time=0.023..0.023 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.010..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 : 8.200 ms