explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tlys

Settings
# exclusive inclusive rows x rows loops node
1. 1,152.671 47,472.454 ↑ 5.8 336,280 1

Nested Loop (cost=358.99..84,999,679.75 rows=1,937,922 width=285) (actual time=347.979..47,472.454 rows=336,280 loops=1)

2. 1,697.111 19,119.841 ↓ 1.0 1,942,853 1

Nested Loop Left Join (cost=341.32..50,680,482.77 rows=1,937,922 width=99) (actual time=5.677..19,119.841 rows=1,942,853 loops=1)

3. 1,086.597 1,879.906 ↓ 1.0 1,942,853 1

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

  • Hash Cond: (wifiactivetagspositionhistory.watph_wat_mac = wifiactivetags.wat_mac)
4. 787.764 787.764 ↓ 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.019..787.764 rows=1,942,853 loops=1)

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

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

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

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

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

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

8. 1,942.853 13,599.971 ↓ 0.0 0 1,942,853

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

  • Sort Key: wifiactivetagsassignmenthistory.watah_start
  • Sort Method: quicksort Memory: 25kB
9. 1,930.949 11,657.118 ↓ 0.0 0 1,942,853

Nested Loop Left Join (cost=16.96..26.09 rows=1 width=79) (actual time=0.006..0.006 rows=0 loops=1,942,853)

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

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

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

Nested Loop (cost=0.29..9.34 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_end > wifiactivetagspositionhistory.watph_from) OR (watah_end IS NULL)) AND (watah_mac = wifiactivetagspositionhistory.watph_wat_mac))
  • Rows Removed by Filter: 2
13. 2.232 2.232 ↑ 1.0 1 372

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

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

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

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

Limit (cost=16.39..16.40 rows=1 width=37) (actual time=0.031..0.032 rows=0 loops=372)

16. 0.744 11.160 ↓ 0.0 0 372

Sort (cost=16.39..16.40 rows=1 width=37) (actual time=0.030..0.030 rows=0 loops=372)

  • Sort Key: tagdeviceshistory.tdh_date_start
  • Sort Method: quicksort Memory: 25kB
17. 1.488 10.416 ↓ 0.0 0 372

Nested Loop (cost=0.29..16.38 rows=1 width=37) (actual time=0.028..0.028 rows=0 loops=372)

18. 8.928 8.928 ↓ 0.0 0 372

Seq Scan on tagdeviceshistory (cost=0.00..8.08 rows=1 width=8) (actual time=0.024..0.024 rows=0 loops=372)

  • Filter: ((tdh_nl_lamp_id = nla.nl_id) AND (to_timestamp((tdh_date_start)::double precision) <= wifiactivetagsassignmenthistory.watah_start) AND ((to_timestamp((tdh_date_end)::double precision) > wifiactivetagsassignmenthistory.watah_start) OR (tdh_date_end IS NULL)))
  • 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.853 27,199.942 ↓ 0.0 0 1,942,853

Limit (cost=17.66..17.67 rows=1 width=67) (actual time=0.013..0.014 rows=0 loops=1,942,853)

21. 3,885.706 25,257.089 ↓ 0.0 0 1,942,853

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

  • Sort Key: wifiaccesspointsassignmenthistory.wapah_start
  • Sort Method: quicksort Memory: 25kB
22. 934.013 21,371.383 ↓ 0.0 0 1,942,853

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

23. 7,771.412 19,428.530 ↓ 0.0 0 1,942,853

Nested Loop (cost=0.27..9.35 rows=1 width=18) (actual time=0.010..0.010 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.004..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.05 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=1,942,853)

  • Filter: ((wapah_start <= wifiactivetagspositionhistory.watph_from) AND ((wapah_end > wifiactivetagspositionhistory.watph_from) OR (wapah_end IS NULL)) AND (wapah_basemac = wifiactivetagspositionhistory.watph_wap_basemac))
  • Rows Removed by Filter: 4
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 : 5.512 ms