explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c1Eo

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.183 285.376 ↓ 1.3 1,846 1

Merge Anti Join (cost=2,239.17..3,183,785.75 rows=1,424 width=103) (actual time=0.089..285.376 rows=1,846 loops=1)

  • Merge Cond: (nlm.nl_id = tagdeviceshistory.tdh_nl_personnel_id)
2.          

Initplan (for Merge Anti Join)

3. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on serveradmin (cost=0.00..1.46 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=1)

  • Filter: (((sa_key)::text = 'enableRFID'::text) AND ((sa_value)::text = '1'::text))
  • Rows Removed by Filter: 4
4. 2.195 284.150 ↓ 1.3 1,846 1

Nested Loop (cost=2,233.46..3,183,769.33 rows=1,425 width=65) (actual time=0.055..284.150 rows=1,846 loops=1)

5. 0.962 18.490 ↓ 1.6 2,291 1

Nested Loop Left Join (cost=46.92..67,922.03 rows=1,425 width=69) (actual time=0.032..18.490 rows=2,291 loops=1)

6. 2.582 12.946 ↓ 1.6 2,291 1

Nested Loop (cost=0.57..1,841.21 rows=1,425 width=36) (actual time=0.026..12.946 rows=2,291 loops=1)

7. 4.594 4.594 ↑ 1.0 2,885 1

Index Scan using nodeslogical_pkey on nodeslogical nlm (cost=0.29..638.08 rows=2,885 width=37) (actual time=0.011..4.594 rows=2,885 loops=1)

  • Filter: (nl_isactive = 1)
  • Rows Removed by Filter: 6,136
8. 5.770 5.770 ↑ 1.0 1 2,885

Index Scan using entities_pkey on entities (cost=0.29..0.42 rows=1 width=7) (actual time=0.002..0.002 rows=1 loops=2,885)

  • Index Cond: (e_id = nlm.nl_e_id)
  • Filter: ((e_et_code = ANY ('{VMTR,PSD}'::text[])) OR ((e_et_code = 'TAG'::text) AND $0))
  • Rows Removed by Filter: 0
9. 2.291 4.582 ↓ 0.0 0 2,291

Limit (cost=46.35..46.35 rows=1 width=37) (actual time=0.002..0.002 rows=0 loops=2,291)

10. 2.291 2.291 ↓ 0.0 0 2,291

Sort (cost=46.35..46.35 rows=2 width=37) (actual time=0.001..0.001 rows=0 loops=2,291)

  • Sort Key: tagdeviceshistory_1.tdh_date_end DESC
  • Sort Method: quicksort Memory: 25kB
11. 0.000 0.000 ↓ 0.0 0 2,291

Result (cost=0.29..46.34 rows=2 width=37) (actual time=0.000..0.000 rows=0 loops=2,291)

  • One-Time Filter: (entities.e_et_code = 'TAG'::text)
12. 0.046 0.506 ↓ 0.0 0 23

Nested Loop (cost=0.29..46.34 rows=2 width=37) (actual time=0.022..0.022 rows=0 loops=23)

13. 0.460 0.460 ↓ 0.0 0 23

Seq Scan on tagdeviceshistory tagdeviceshistory_1 (cost=0.00..4.76 rows=5 width=12) (actual time=0.020..0.020 rows=0 loops=23)

  • Filter: (tdh_nl_lamp_id = nlm.nl_id)
  • Rows Removed by Filter: 221
14. 0.000 0.000 ↓ 0.0 0

Index Scan using nodeslogical_pkey on nodeslogical nlr (cost=0.29..8.30 rows=1 width=33) (never executed)

  • Index Cond: (nl_id = tagdeviceshistory_1.tdh_nl_personnel_id)
  • Filter: (nl_isactive = 1)
15. 0.000 263.465 ↑ 1.0 1 2,291

Limit (cost=2,186.54..2,186.54 rows=1 width=8) (actual time=0.115..0.115 rows=1 loops=2,291)

16. 103.095 263.465 ↑ 806.0 1 2,291

Sort (cost=2,186.54..2,188.55 rows=806 width=8) (actual time=0.115..0.115 rows=1 loops=2,291)

  • Sort Key: trackingmessagesindexed.tmi_disconnect DESC
  • Sort Method: top-N heapsort Memory: 25kB
17. 123.714 160.370 ↑ 2.9 276 2,291

Bitmap Heap Scan on trackingmessagesindexed (cost=18.67..2,182.51 rows=806 width=8) (actual time=0.019..0.070 rows=276 loops=2,291)

  • Recheck Cond: (tmi_nl_mobile_id = COALESCE(tagdeviceshistory_1.tdh_nl_lamp_id, nlm.nl_id))
  • Heap Blocks: exact=18,715
18. 36.656 36.656 ↑ 2.9 276 2,291

Bitmap Index Scan on tmi_indextrackingmobile (cost=0.00..18.47 rows=806 width=0) (actual time=0.016..0.016 rows=276 loops=2,291)

  • Index Cond: (tmi_nl_mobile_id = COALESCE(tagdeviceshistory_1.tdh_nl_lamp_id, nlm.nl_id))
19. 0.005 0.032 ↑ 1.0 4 1

Sort (cost=4.25..4.26 rows=4 width=4) (actual time=0.030..0.032 rows=4 loops=1)

  • Sort Key: tagdeviceshistory.tdh_nl_personnel_id
  • Sort Method: quicksort Memory: 25kB
20. 0.027 0.027 ↑ 1.0 4 1

Seq Scan on tagdeviceshistory (cost=0.00..4.21 rows=4 width=4) (actual time=0.014..0.027 rows=4 loops=1)

  • Filter: (tdh_date_end IS NULL)
  • Rows Removed by Filter: 217