explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z9JHg

Settings
# exclusive inclusive rows x rows loops node
1. 44.482 38,164.079 ↑ 2.3 20,080 1

Nested Loop Left Join (cost=7,506.49..283,271,860.72 rows=46,466 width=364) (actual time=17.268..38,164.079 rows=20,080 loops=1)

2. 14.694 37,979.037 ↑ 2.3 20,080 1

Nested Loop Left Join (cost=7,489.85..282,496,658.17 rows=46,466 width=172) (actual time=17.140..37,979.037 rows=20,080 loops=1)

3. 12.278 33.223 ↑ 2.3 20,080 1

Hash Join (cost=1,411.43..55,707.55 rows=46,466 width=34) (actual time=7.097..33.223 rows=20,080 loops=1)

  • Hash Cond: (wifiactivetagspositionhistory.watph_wat_mac = wifiactivetags.wat_mac)
4. 15.923 20.890 ↑ 2.3 20,080 1

Bitmap Heap Scan on wifiactivetagspositionhistory (cost=1,409.73..55,567.21 rows=46,466 width=28) (actual time=7.019..20.890 rows=20,080 loops=1)

  • Recheck Cond: ((watph_from > (now() - '7 days'::interval)) AND (watph_wap_basemac IS NOT NULL))
  • Heap Blocks: exact=3417
5. 4.967 4.967 ↑ 2.3 20,091 1

Bitmap Index Scan on watph_index_from_wap_basemac (cost=0.00..1,398.12 rows=46,466 width=0) (actual time=4.967..4.967 rows=20,091 loops=1)

  • Index Cond: ((watph_from > (now() - '7 days'::interval)) AND (watph_wap_basemac IS NOT NULL))
6. 0.018 0.055 ↑ 1.0 31 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
7. 0.037 0.037 ↑ 1.0 31 1

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

  • Filter: (wat_active IS TRUE)
8. 60.240 37,931.120 ↑ 1.0 1 20,080

Limit (cost=6,078.42..6,078.42 rows=1 width=152) (actual time=1.886..1.889 rows=1 loops=20,080)

9. 40.160 37,870.880 ↑ 1.0 1 20,080

Sort (cost=6,078.42..6,078.42 rows=1 width=152) (actual time=1.886..1.886 rows=1 loops=20,080)

  • Sort Key: wifiactivetagsassignmenthistory.watah_start
  • Sort Method: quicksort Memory: 25kB
10. 0.000 37,830.720 ↑ 1.0 1 20,080

Nested Loop (cost=30.53..6,078.41 rows=1 width=152) (actual time=1.751..1.884 rows=1 loops=20,080)

  • Join Filter: (nla.nl_e_id = entities.e_id)
  • Rows Removed by Join Filter: 1055
11. 1,184.720 1,184.720 ↑ 1.0 1,056 20,080

Seq Scan on entities (cost=0.00..16.56 rows=1,056 width=7) (actual time=0.002..0.059 rows=1,056 loops=20,080)

12. 10,401.440 42,408.960 ↑ 1.0 1 21,204,480

Materialize (cost=30.53..6,046.01 rows=1 width=157) (actual time=0.001..0.002 rows=1 loops=21,204,480)

13. 502.000 32,007.520 ↑ 1.0 1 20,080

Nested Loop (cost=30.53..6,046.01 rows=1 width=157) (actual time=1.515..1.594 rows=1 loops=20,080)

  • Join Filter: (wifiactivetagsassignmenthistory.watah_nl_id = nla.nl_id)
  • Rows Removed by Join Filter: 369
14. 80.320 80.320 ↑ 1.0 1 20,080

Seq Scan on wifiactivetagsassignmenthistory (cost=0.00..1.60 rows=1 width=12) (actual time=0.002..0.004 rows=1 loops=20,080)

  • 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
15. 5,040.080 31,425.200 ↑ 1.0 370 20,080

Nested Loop Left Join (cost=30.53..6,039.79 rows=370 width=149) (actual time=0.007..1.565 rows=370 loops=20,080)

  • Join Filter: (equipmentcategories.eqcat_id = nla.nl_eqcat_id)
  • Rows Removed by Join Filter: 1814
16. 4,277.040 26,385.120 ↑ 1.0 370 20,080

Nested Loop Left Join (cost=30.53..6,011.07 rows=370 width=138) (actual time=0.007..1.314 rows=370 loops=20,080)

  • 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: 1432
17. 1,324.562 22,108.080 ↑ 1.0 370 20,080

Hash Left Join (cost=30.53..5,987.90 rows=370 width=126) (actual time=0.006..1.101 rows=370 loops=20,080)

  • 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)
18. 7,108.320 20,782.800 ↑ 1.0 370 20,080

Nested Loop Left Join (cost=29.22..5,985.59 rows=370 width=116) (actual time=0.005..1.035 rows=370 loops=20,080)

  • 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: 2865
19. 5,301.120 13,674.480 ↑ 1.0 370 20,080

Nested Loop Left Join (cost=29.22..5,940.27 rows=370 width=115) (actual time=0.004..0.681 rows=370 loops=20,080)

  • 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: 1804
20. 1,203.102 8,373.360 ↑ 1.0 370 20,080

Hash Left Join (cost=29.22..5,911.56 rows=370 width=112) (actual time=0.003..0.417 rows=370 loops=20,080)

  • Hash Cond: (tagdeviceshistory.tdh_nl_personnel_id = nlp.nl_id)
21. 0.000 7,168.560 ↑ 1.0 370 20,080

Nested Loop Left Join (cost=15.89..5,897.25 rows=370 width=64) (actual time=0.002..0.357 rows=370 loops=20,080)

22. 642.560 642.560 ↑ 1.0 370 20,080

Seq Scan on nodeslogical nla (cost=0.00..8.70 rows=370 width=60) (actual time=0.001..0.032 rows=370 loops=20,080)

23. 7,429.084 7,429.600 ↓ 0.0 0 7,429,600

Limit (cost=15.89..15.90 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=7,429,600)

24.          

Initplan (for Limit)

25. 0.516 0.516 ↓ 0.0 0 1

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

  • Filter: (((sa_key)::text = 'enableRFID'::text) AND ((sa_value)::text = '1'::text))
  • Rows Removed by Filter: 26
26. 0.000 0.000 ↓ 0.0 0 7,429,600

Sort (cost=14.50..14.51 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=7,429,600)

  • Sort Key: tagdeviceshistory.tdh_date_start
  • Sort Method: quicksort Memory: 25kB
27. 0.000 0.000 ↓ 0.0 0 7,429,600

Result (cost=4.21..14.49 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=7,429,600)

  • One-Time Filter: $2
28. 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)))
29. 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)
30. 0.945 1.698 ↑ 1.0 370 1

Hash (cost=8.70..8.70 rows=370 width=52) (actual time=0.461..1.698 rows=370 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
31. 0.753 0.753 ↑ 1.0 370 1

Seq Scan on nodeslogical nlp (cost=0.00..8.70 rows=370 width=52) (actual time=0.010..0.753 rows=370 loops=1)

32. 0.000 0.000 ↑ 1.0 5 7,429,600

Materialize (cost=0.00..1.07 rows=5 width=15) (actual time=0.000..0.000 rows=5 loops=7,429,600)

33. 0.504 0.504 ↑ 1.0 5 1

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

34. 0.000 0.000 ↑ 1.0 8 7,429,600

Materialize (cost=0.00..1.12 rows=8 width=13) (actual time=0.000..0.000 rows=8 loops=7,429,600)

35. 0.520 0.520 ↑ 1.0 8 1

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

36. 0.190 0.718 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=22) (actual time=0.023..0.718 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.528 0.528 ↑ 1.0 14 1

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

38. 0.000 0.000 ↑ 1.0 4 7,429,600

Materialize (cost=0.00..1.06 rows=4 width=24) (actual time=0.000..0.000 rows=4 loops=7,429,600)

39. 0.526 0.526 ↑ 1.0 4 1

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

40. 0.000 0.000 ↑ 1.0 5 7,429,600

Materialize (cost=0.00..1.07 rows=5 width=19) (actual time=0.000..0.000 rows=5 loops=7,429,600)

41. 0.524 0.524 ↑ 1.0 5 1

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

42. 20.080 140.560 ↑ 1.0 1 20,080

Limit (cost=16.64..16.64 rows=1 width=76) (actual time=0.006..0.007 rows=1 loops=20,080)

43. 20.080 120.480 ↑ 1.0 1 20,080

Sort (cost=16.64..16.64 rows=1 width=76) (actual time=0.006..0.006 rows=1 loops=20,080)

  • Sort Key: wifiaccesspointsassignmenthistory.wapah_start
  • Sort Method: quicksort Memory: 25kB
44. 20.080 100.400 ↑ 1.0 1 20,080

Nested Loop (cost=0.55..16.63 rows=1 width=76) (actual time=0.004..0.005 rows=1 loops=20,080)

45. 60.240 60.240 ↑ 1.0 1 20,080

Index Scan using wapah_pkey on wifiaccesspointsassignmenthistory (cost=0.27..8.29 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=20,080)

  • 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))
46. 20.080 20.080 ↑ 1.0 1 20,080

Index Scan using nodeslogical_pkey on nodeslogical (cost=0.27..8.29 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=20,080)

  • Index Cond: (nl_id = wifiaccesspointsassignmenthistory.wapah_nl_id)