explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bW8v

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 4.637 1,127.382 ↑ 11.0 456 1

Nested Loop Left Join (cost=6,217.45..31,203,832.94 rows=5,019 width=363) (actual time=2.210..1,127.382 rows=456 loops=1)

2. 1.369 1,107.697 ↑ 11.0 456 1

Nested Loop Left Join (cost=6,200.82..31,120,103.87 rows=5,019 width=172) (actual time=2.170..1,107.697 rows=456 loops=1)

3. 1.090 2.808 ↑ 11.0 456 1

Hash Join (cost=2.13..8,777.32 rows=5,019 width=34) (actual time=0.032..2.808 rows=456 loops=1)

  • Hash Cond: (wifiactivetagspositionhistory.watph_wat_mac = wifiactivetags.wat_mac)
4. 1.704 1.704 ↑ 11.0 456 1

Index Scan using watph_index_from_wap_basemac on wifiactivetagspositionhistory (cost=0.43..8,760.65 rows=5,019 width=28) (actual time=0.014..1.704 rows=456 loops=1)

  • Index Cond: ((watph_from >= '2020-03-13 00:00:00-03'::timestamp with time zone) AND (watph_from <= '2020-03-14 00:00:00-03'::timestamp with time zone) AND (watph_wap_basemac IS NOT NULL))
5. 0.005 0.014 ↑ 1.0 31 1

Hash (cost=1.31..1.31 rows=31 width=6) (actual time=0.013..0.014 rows=31 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
6. 0.009 0.009 ↑ 1.0 31 1

Seq Scan on wifiactivetags (cost=0.00..1.31 rows=31 width=6) (actual time=0.006..0.009 rows=31 loops=1)

  • Filter: (wat_active IS TRUE)
7. 3.192 1,103.520 ↑ 1.0 1 456

Limit (cost=6,198.69..6,198.69 rows=1 width=152) (actual time=2.414..2.420 rows=1 loops=456)

8. 5.016 1,100.328 ↑ 1.0 1 456

Sort (cost=6,198.69..6,198.69 rows=1 width=152) (actual time=2.413..2.413 rows=1 loops=456)

  • Sort Key: wifiactivetagsassignmenthistory.watah_start
  • Sort Method: quicksort Memory: 25kB
9. 85.728 1,095.312 ↑ 1.0 1 456

Nested Loop (cost=33.69..6,198.68 rows=1 width=152) (actual time=2.238..2.402 rows=1 loops=456)

  • Join Filter: (nla.nl_e_id = entities.e_id)
  • Rows Removed by Join Filter: 1063
10. 39.216 39.216 ↑ 1.0 1,064 456

Seq Scan on entities (cost=0.00..16.64 rows=1,064 width=7) (actual time=0.004..0.086 rows=1,064 loops=456)

11. 44.232 970.368 ↑ 1.0 1 485,184

Materialize (cost=33.69..6,166.08 rows=1 width=155) (actual time=0.002..0.002 rows=1 loops=485,184)

12. 14.592 926.136 ↑ 1.0 1 456

Nested Loop (cost=33.69..6,166.07 rows=1 width=155) (actual time=1.887..2.031 rows=1 loops=456)

  • Join Filter: (wifiactivetagsassignmenthistory.watah_nl_id = nla.nl_id)
  • Rows Removed by Join Filter: 376
13. 3.648 3.648 ↑ 1.0 1 456

Seq Scan on wifiactivetagsassignmenthistory (cost=0.00..1.60 rows=1 width=12) (actual time=0.004..0.008 rows=1 loops=456)

  • 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: 33
14. 142.272 907.896 ↑ 1.0 377 456

Nested Loop Left Join (cost=33.69..6,159.77 rows=377 width=147) (actual time=0.013..1.991 rows=377 loops=456)

  • Join Filter: (equipmentcategories.eqcat_id = nla.nl_eqcat_id)
  • Rows Removed by Join Filter: 1849
15. 123.576 765.624 ↑ 1.0 377 456

Nested Loop Left Join (cost=33.69..6,130.53 rows=377 width=136) (actual time=0.011..1.679 rows=377 loops=456)

  • Join Filter: (personnelcrews.perscr_id = CASE WHEN (tagdeviceshistory.tdh_nl_personnel_id IS NOT NULL) THEN nlp.nl_perscr_id ELSE nla.nl_perscr_id END)
  • Rows Removed by Join Filter: 1460
16. 41.923 642.048 ↑ 1.0 377 456

Hash Left Join (cost=33.69..6,106.93 rows=377 width=124) (actual time=0.010..1.408 rows=377 loops=456)

  • Hash Cond: (CASE WHEN (tagdeviceshistory.tdh_nl_personnel_id IS NOT NULL) THEN nlp.nl_persp_id ELSE nla.nl_persp_id END = personnelpositions.persp_id)
17. 200.184 600.096 ↑ 1.0 377 456

Nested Loop Left Join (cost=32.37..6,104.60 rows=377 width=114) (actual time=0.009..1.316 rows=377 loops=456)

  • Join Filter: (personneldepartments.persd_id = CASE WHEN (tagdeviceshistory.tdh_nl_personnel_id IS NOT NULL) THEN nlp.nl_persd_id ELSE nla.nl_persd_id END)
  • Rows Removed by Join Filter: 2921
18. 149.112 399.912 ↑ 1.0 377 456

Nested Loop Left Join (cost=32.37..6,058.45 rows=377 width=113) (actual time=0.008..0.877 rows=377 loops=456)

  • Join Filter: (personnelcompanies.persc_id = CASE WHEN (tagdeviceshistory.tdh_nl_personnel_id IS NOT NULL) THEN nlp.nl_persc_id ELSE nla.nl_persc_id END)
  • Rows Removed by Join Filter: 1839
19. 40.788 250.800 ↑ 1.0 377 456

Hash Left Join (cost=32.37..6,029.21 rows=377 width=110) (actual time=0.006..0.550 rows=377 loops=456)

  • Hash Cond: (tagdeviceshistory.tdh_nl_personnel_id = nlp.nl_id)
20. 15.504 209.760 ↑ 1.0 377 456

Nested Loop Left Join (cost=15.89..6,011.73 rows=377 width=63) (actual time=0.004..0.460 rows=377 loops=456)

21. 22.344 22.344 ↑ 1.0 377 456

Seq Scan on nodeslogical nla (cost=0.00..11.77 rows=377 width=59) (actual time=0.001..0.049 rows=377 loops=456)

22. 171.895 171.912 ↓ 0.0 0 171,912

Limit (cost=15.89..15.90 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=171,912)

23.          

Initplan (for Limit)

24. 0.017 0.017 ↓ 0.0 0 1

Seq Scan on serveradmin (cost=0.00..1.39 rows=1 width=0) (actual time=0.006..0.017 rows=0 loops=1)

  • Filter: (((sa_key)::text = 'enableRFID'::text) AND ((sa_value)::text = '1'::text))
  • Rows Removed by Filter: 26
25. 0.000 0.000 ↓ 0.0 0 171,912

Sort (cost=14.50..14.51 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=171,912)

  • Sort Key: tagdeviceshistory.tdh_date_start
  • Sort Method: quicksort Memory: 25kB
26. 0.000 0.000 ↓ 0.0 0 171,912

Result (cost=4.21..14.49 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=171,912)

  • One-Time Filter: $2
27. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on tagdeviceshistory (cost=4.21..14.49 rows=1 width=8) (never executed)

  • Recheck Cond: (tdh_nl_lamp_id = nla.nl_id)
  • Filter: ((to_timestamp((tdh_date_start)::double precision) <= wifiactivetagspositionhistory.watph_from) AND ((to_timestamp((tdh_date_end)::double precision) > wifiactivetagspositionhistory.watph_from) OR (tdh_date_end IS NULL)))
28. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on tdh_index_nl_lamp_id (cost=0.00..4.21 rows=8 width=0) (never executed)

  • Index Cond: (tdh_nl_lamp_id = nla.nl_id)
29. 0.153 0.252 ↑ 1.0 377 1

Hash (cost=11.77..11.77 rows=377 width=51) (actual time=0.140..0.252 rows=377 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
30. 0.099 0.099 ↑ 1.0 377 1

Seq Scan on nodeslogical nlp (cost=0.00..11.77 rows=377 width=51) (actual time=0.002..0.099 rows=377 loops=1)

31. 0.000 0.000 ↑ 1.0 5 171,912

Materialize (cost=0.00..1.07 rows=5 width=15) (actual time=0.000..0.000 rows=5 loops=171,912)

32. 0.018 0.018 ↑ 1.0 5 1

Seq Scan on personnelcompanies (cost=0.00..1.05 rows=5 width=15) (actual time=0.003..0.018 rows=5 loops=1)

33. 0.000 0.000 ↑ 1.0 8 171,912

Materialize (cost=0.00..1.12 rows=8 width=13) (actual time=0.000..0.000 rows=8 loops=171,912)

34. 0.017 0.017 ↑ 1.0 8 1

Seq Scan on personneldepartments (cost=0.00..1.08 rows=8 width=13) (actual time=0.002..0.017 rows=8 loops=1)

35. 0.012 0.029 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=22) (actual time=0.006..0.029 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.017 0.017 ↑ 1.0 14 1

Seq Scan on personnelpositions (cost=0.00..1.14 rows=14 width=22) (actual time=0.003..0.017 rows=14 loops=1)

37. 0.000 0.000 ↑ 1.0 4 171,912

Materialize (cost=0.00..1.06 rows=4 width=24) (actual time=0.000..0.000 rows=4 loops=171,912)

38. 0.016 0.016 ↑ 1.0 4 1

Seq Scan on personnelcrews (cost=0.00..1.04 rows=4 width=24) (actual time=0.002..0.016 rows=4 loops=1)

39. 0.000 0.000 ↑ 1.0 5 171,912

Materialize (cost=0.00..1.07 rows=5 width=19) (actual time=0.000..0.000 rows=5 loops=171,912)

40. 0.017 0.017 ↑ 1.0 5 1

Seq Scan on equipmentcategories (cost=0.00..1.05 rows=5 width=19) (actual time=0.002..0.017 rows=5 loops=1)

41. 1.824 15.048 ↑ 1.0 1 456

Limit (cost=16.64..16.64 rows=1 width=75) (actual time=0.031..0.033 rows=1 loops=456)

42. 1.824 13.224 ↑ 1.0 1 456

Sort (cost=16.64..16.64 rows=1 width=75) (actual time=0.029..0.029 rows=1 loops=456)

  • Sort Key: wifiaccesspointsassignmenthistory.wapah_start
  • Sort Method: quicksort Memory: 25kB
43. 2.736 11.400 ↑ 1.0 1 456

Nested Loop (cost=0.55..16.63 rows=1 width=75) (actual time=0.024..0.025 rows=1 loops=456)

44. 5.928 5.928 ↑ 1.0 1 456

Index Scan using wapah_pkey on wifiaccesspointsassignmenthistory (cost=0.27..8.29 rows=1 width=12) (actual time=0.012..0.013 rows=1 loops=456)

  • Index Cond: ((wapah_basemac = wifiactivetagspositionhistory.watph_wap_basemac) AND (wapah_start <= wifiactivetagspositionhistory.watph_from))
  • Filter: ((wapah_end > wifiactivetagspositionhistory.watph_from) OR (wapah_end IS NULL))
45. 2.736 2.736 ↑ 1.0 1 456

Index Scan using nodeslogical_pkey on nodeslogical (cost=0.27..8.29 rows=1 width=67) (actual time=0.006..0.006 rows=1 loops=456)

  • Index Cond: (nl_id = wifiaccesspointsassignmenthistory.wapah_nl_id)