explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M801 : Optimization for: plan #c1Eo

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 19.807 ↓ 1.3 1,846 1

Nested Loop (cost=271.85..67,304.58 rows=1,424 width=103) (actual time=1.707..19.807 rows=1,846 loops=1)

2.          

Initplan (for Nested Loop)

3. 0.010 0.010 ↑ 1.0 1 1

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

  • Filter: (((sa_key)::text = 'enableRFID'::text) AND ((sa_value)::text = '1'::text))
  • Rows Removed by Filter: 4
4. 1.938 10.666 ↓ 1.6 2,288 1

Nested Loop Anti Join (cost=269.96..66,616.19 rows=1,424 width=69) (actual time=1.686..10.666 rows=2,288 loops=1)

  • Join Filter: (tagdeviceshistory.tdh_nl_personnel_id = nlm.nl_id)
  • Rows Removed by Join Filter: 9,157
5. 1.142 8.728 ↓ 1.6 2,291 1

Nested Loop Left Join (cost=269.96..66,526.47 rows=1,425 width=69) (actual time=1.641..8.728 rows=2,291 loops=1)

6. 1.538 5.295 ↓ 1.6 2,291 1

Hash Join (cost=223.61..445.66 rows=1,425 width=36) (actual time=1.626..5.295 rows=2,291 loops=1)

  • Hash Cond: (entities.e_id = nlm.nl_e_id)
7. 2.158 2.158 ↓ 1.8 8,426 1

Seq Scan on entities (cost=0.00..190.56 rows=4,597 width=7) (actual time=0.015..2.158 rows=8,426 loops=1)

  • Filter: ((e_et_code = ANY ('{VMTR,PSD}'::text[])) OR ((e_et_code = 'TAG'::text) AND $0))
  • Rows Removed by Filter: 878
8. 0.560 1.599 ↑ 1.0 2,885 1

Hash (cost=187.55..187.55 rows=2,885 width=37) (actual time=1.599..1.599 rows=2,885 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 180kB
9. 1.039 1.039 ↑ 1.0 2,885 1

Index Scan using nl_index_isactive on nodeslogical nlm (cost=0.28..187.55 rows=2,885 width=37) (actual time=0.014..1.039 rows=2,885 loops=1)

10. 0.000 2.291 ↓ 0.0 0 2,291

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

11. 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
12. 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)
13. 0.069 0.529 ↓ 0.0 0 23

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

14. 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
15. 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)
16. 0.000 0.000 ↑ 1.0 4 2,291

Materialize (cost=0.00..4.23 rows=4 width=4) (actual time=0.000..0.000 rows=4 loops=2,291)

17. 0.027 0.027 ↑ 1.0 4 1

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

  • Filter: (tdh_date_end IS NULL)
  • Rows Removed by Filter: 217
18. 2.288 9.152 ↑ 1.0 1 2,288

Limit (cost=0.42..0.46 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=2,288)

19. 6.864 6.864 ↑ 806.0 1 2,288

Index Only Scan using tmi_indextrackingmobile on trackingmessagesindexed (cost=0.42..26.53 rows=806 width=8) (actual time=0.003..0.003 rows=1 loops=2,288)

  • Index Cond: (tmi_nl_mobile_id = COALESCE(tagdeviceshistory_1.tdh_nl_lamp_id, nlm.nl_id))
  • Heap Fetches: 0