explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DCFS : Optimization for: plan #bW8v

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 5.917 1,147.871 ↑ 11.0 456 1

Nested Loop (cost=6,217.45..31,203,832.94 rows=5,019 width=363) (actual time=2.998..1,147.871 rows=456 loops=1)

2. 1.679 1,124.170 ↑ 11.0 456 1

Nested Loop (cost=6,200.82..31,120,103.87 rows=5,019 width=172) (actual time=2.948..1,124.170 rows=456 loops=1)

3. 1.274 3.467 ↑ 11.0 456 1

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

  • Hash Cond: (wifiactivetagspositionhistory.watph_wat_mac = wifiactivetags.wat_mac)
4. 2.177 2.177 ↑ 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.018..2.177 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.016 ↑ 1.0 31 1

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

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

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

  • Filter: (wat_active IS TRUE)
7. 4.104 1,119.024 ↑ 1.0 1 456

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

8. 5.928 1,114.920 ↑ 1.0 1 456

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

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

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

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

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

11. 36.936 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. 15.048 933.432 ↑ 1.0 1 456

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

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

Seq Scan on wifiactivetagsassignmenthistory (cost=0.00..1.60 rows=1 width=12) (actual time=0.005..0.009 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.728 914.280 ↑ 1.0 377 456

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

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

Nested Loop Left Join (cost=33.69..6,130.53 rows=377 width=136) (actual time=0.013..1.692 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. 40.984 644.328 ↑ 1.0 377 456

Hash Left Join (cost=33.69..6,106.93 rows=377 width=124) (actual time=0.011..1.413 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. 203.376 603.288 ↑ 1.0 377 456

Nested Loop Left Join (cost=32.37..6,104.60 rows=377 width=114) (actual time=0.010..1.323 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. 150.024 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. 36.680 249.888 ↑ 1.0 377 456

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

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

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

21. 25.536 25.536 ↑ 1.0 377 456

Seq Scan on nodeslogical nla (cost=0.00..11.77 rows=377 width=59) (actual time=0.001..0.056 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.164 0.256 ↑ 1.0 377 1

Hash (cost=11.77..11.77 rows=377 width=51) (actual time=0.109..0.256 rows=377 loops=1)

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

Seq Scan on nodeslogical nlp (cost=0.00..11.77 rows=377 width=51) (actual time=0.002..0.092 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.017 0.017 ↑ 1.0 5 1

Seq Scan on personnelcompanies (cost=0.00..1.05 rows=5 width=15) (actual time=0.003..0.017 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.018 0.018 ↑ 1.0 8 1

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

35. 0.039 0.056 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=22) (actual time=0.006..0.056 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.014 0.014 ↑ 1.0 4 1

Seq Scan on personnelcrews (cost=0.00..1.04 rows=4 width=24) (actual time=0.002..0.014 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.019 0.019 ↑ 1.0 5 1

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

41. 1.824 17.784 ↑ 1.0 1 456

Limit (cost=16.64..16.64 rows=1 width=75) (actual time=0.037..0.039 rows=1 loops=456)

42. 2.280 15.960 ↑ 1.0 1 456

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

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

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

44. 7.752 7.752 ↑ 1.0 1 456

Index Scan using wapah_pkey on wifiaccesspointsassignmenthistory (cost=0.27..8.29 rows=1 width=12) (actual time=0.016..0.017 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. 3.192 3.192 ↑ 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.007 rows=1 loops=456)

  • Index Cond: (nl_id = wifiaccesspointsassignmenthistory.wapah_nl_id)