explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gpUb

Settings
# exclusive inclusive rows x rows loops node
1. 2,634.751 43,670.100 ↑ 5.8 336,280 1

Nested Loop (cost=365.62..97,848,102.61 rows=1,937,922 width=285) (actual time=416.388..43,670.100 rows=336,280 loops=1)

2. 346.507 17,721.113 ↓ 1.0 1,942,853 1

Nested Loop Left Join (cost=347.96..63,543,440.04 rows=1,937,922 width=99) (actual time=0.777..17,721.113 rows=1,942,853 loops=1)

3. 1,093.343 1,831.782 ↓ 1.0 1,942,853 1

Hash Join (cost=315.22..45,328.14 rows=1,937,922 width=34) (actual time=0.757..1,831.782 rows=1,942,853 loops=1)

  • Hash Cond: (wifiactivetagspositionhistory.watph_wat_mac = wifiactivetags.wat_mac)
4. 737.704 737.704 ↓ 1.0 1,942,853 1

Seq Scan on wifiactivetagspositionhistory (cost=0.00..39,891.77 rows=1,937,922 width=28) (actual time=0.012..737.704 rows=1,942,853 loops=1)

  • Filter: (watph_wap_basemac IS NOT NULL)
  • Rows Removed by Filter: 128684
5. 0.095 0.735 ↑ 1.0 632 1

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

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

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

  • Filter: (wat_active IS TRUE)
7. 1,942.853 15,542.824 ↓ 0.0 0 1,942,853

Limit (cost=32.74..32.75 rows=1 width=79) (actual time=0.008..0.008 rows=0 loops=1,942,853)

8. 3,885.706 13,599.971 ↓ 0.0 0 1,942,853

Sort (cost=32.74..32.75 rows=1 width=79) (actual time=0.007..0.007 rows=0 loops=1,942,853)

  • Sort Key: wifiactivetagsassignmenthistory.watah_start DESC
  • Sort Method: quicksort Memory: 25kB
9. 0.000 9,714.265 ↓ 0.0 0 1,942,853

Nested Loop Left Join (cost=23.61..32.73 rows=1 width=79) (actual time=0.005..0.005 rows=0 loops=1,942,853)

10. 0.000 9,714.265 ↓ 0.0 0 1,942,853

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

11. 5,827.071 9,714.265 ↓ 0.0 0 1,942,853

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

12. 3,885.706 3,885.706 ↓ 0.0 0 1,942,853

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

  • Filter: ((watah_start < wifiactivetagspositionhistory.watph_from) AND (watah_mac = wifiactivetagspositionhistory.watph_wat_mac))
  • Rows Removed by Filter: 2
13. 1.488 1.488 ↑ 1.0 1 372

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=372)

  • Index Cond: (nl_id = wifiactivetagsassignmenthistory.watah_nl_id)
14. 1.116 1.116 ↑ 1.0 1 372

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=372)

  • Index Cond: (e_id = nla.nl_e_id)
15. 0.000 10.416 ↓ 0.0 0 372

Limit (cost=23.04..23.04 rows=1 width=37) (actual time=0.028..0.028 rows=0 loops=372)

16. 0.744 10.416 ↓ 0.0 0 372

Sort (cost=23.04..23.04 rows=2 width=37) (actual time=0.028..0.028 rows=0 loops=372)

  • Sort Key: tagdeviceshistory.tdh_date_start DESC
  • Sort Method: quicksort Memory: 25kB
17. 1.116 9.672 ↓ 0.0 0 372

Nested Loop (cost=0.29..23.03 rows=2 width=37) (actual time=0.026..0.026 rows=0 loops=372)

18. 8.556 8.556 ↓ 0.0 0 372

Seq Scan on tagdeviceshistory (cost=0.00..6.42 rows=2 width=8) (actual time=0.023..0.023 rows=0 loops=372)

  • Filter: ((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. 0.000 23,314.236 ↓ 0.0 0 1,942,853

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

21. 3,885.706 23,314.236 ↓ 0.0 0 1,942,853

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

  • Sort Key: wifiaccesspointsassignmenthistory.wapah_start DESC
  • Sort Method: quicksort Memory: 25kB
22. 1,270.293 19,428.530 ↓ 0.0 0 1,942,853

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

23. 5,828.559 17,485.677 ↓ 0.0 0 1,942,853

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

24. 7,771.412 7,771.412 ↑ 1.0 1 1,942,853

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

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

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

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

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

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