explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7l9e

Settings
# exclusive inclusive rows x rows loops node
1. 1.236 31.906 ↓ 1.6 1,877 1

Hash Join (cost=702.87..4,169.43 rows=1,198 width=137) (actual time=9.859..31.906 rows=1,877 loops=1)

  • Hash Cond: (COALESCE(tagtrackingmessagesindexed.ttmi_nl_fixed_id, checkpointmessages.cm_nl_entry_id, trackingmessagesindexed.tmi_nl_fixed_id) = nlf.nl_id)
2.          

Initplan (forHash Join)

3. 0.025 0.025 ↑ 1.0 1 1

Index Scan using serveradmin_pkey on serveradmin (cost=0.14..2.16 rows=1 width=0) (actual time=0.025..0.025 rows=1 loops=1)

  • Index Cond: ((sa_key)::text = 'enableRFID'::text)
  • Filter: ((sa_value)::text = '1'::text)
4. 1.651 24.393 ↓ 1.9 2,287 1

Nested Loop Left Join (cost=325.35..3,773.79 rows=1,198 width=68) (actual time=3.580..24.393 rows=2,287 loops=1)

5. 1.724 22.742 ↓ 1.9 2,287 1

Nested Loop Left Join (cost=325.06..3,229.56 rows=1,198 width=56) (actual time=3.576..22.742 rows=2,287 loops=1)

6. 1.059 16.444 ↓ 1.9 2,287 1

Nested Loop Left Join (cost=324.77..2,087.63 rows=1,198 width=48) (actual time=3.572..16.444 rows=2,287 loops=1)

7. 1.891 6.237 ↓ 1.9 2,287 1

Hash Join (cost=324.34..430.04 rows=1,198 width=36) (actual time=3.538..6.237 rows=2,287 loops=1)

  • Hash Cond: (nlm.nl_e_id = entities.e_id)
  • Join Filter: ((entities.e_et_code = 'VMTR'::text) OR ((entities.e_et_code = 'TAG'::text) AND $0) OR ((entities.e_et_code = 'PSD'::text) AND (NOT (alternatives: SubPlan 3 or hashed SubPlan 5))))
  • Rows Removed by Join Filter: 4
8. 0.829 0.829 ↑ 1.0 2,885 1

Index Scan using nl_index_isactive on nodeslogical nlm (cost=0.28..98.41 rows=2,885 width=37) (actual time=0.011..0.829 rows=2,885 loops=1)

9. 1.366 3.505 ↓ 1.2 8,426 1

Hash (cost=237.63..237.63 rows=6,915 width=7) (actual time=3.504..3.505 rows=8,426 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 458kB
10. 2.139 2.139 ↓ 1.2 8,426 1

Index Only Scan using e_index_et_code on entities (cost=0.29..237.63 rows=6,915 width=7) (actual time=0.034..2.139 rows=8,426 loops=1)

  • Filter: ((e_et_code = 'VMTR'::text) OR (e_et_code = 'TAG'::text) OR (e_et_code = 'PSD'::text))
  • Rows Removed by Filter: 878
  • Heap Fetches: 0
11.          

SubPlan (forHash Join)

12. 0.000 0.000 ↓ 0.0 0

Result (cost=2.29..4.33 rows=1 width=0) (never executed)

  • One-Time Filter: $1
13.          

Initplan (forResult)

14. 0.000 0.000 ↓ 0.0 0

Index Scan using serveradmin_pkey on serveradmin serveradmin_1 (cost=0.14..2.16 rows=1 width=0) (never executed)

  • Index Cond: ((sa_key)::text = 'enableRFID'::text)
  • Filter: ((sa_value)::text = '1'::text)
15. 0.000 0.000 ↓ 0.0 0

Index Only Scan using tdh_index_active on tagdeviceshistory (cost=2.29..4.33 rows=1 width=0) (never executed)

  • Index Cond: (tdh_nl_lamp_id = nlm.nl_id)
  • Heap Fetches: 0
16. 0.002 0.012 ↑ 1.0 4 1

Result (cost=2.29..4.35 rows=4 width=4) (actual time=0.011..0.012 rows=4 loops=1)

  • One-Time Filter: $4
17.          

Initplan (forResult)

18. 0.004 0.004 ↑ 1.0 1 1

Index Scan using serveradmin_pkey on serveradmin serveradmin_2 (cost=0.14..2.16 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: ((sa_key)::text = 'enableRFID'::text)
  • Filter: ((sa_value)::text = '1'::text)
19. 0.006 0.006 ↑ 1.0 4 1

Index Only Scan using tdh_index_active on tagdeviceshistory tagdeviceshistory_1 (cost=2.29..4.35 rows=4 width=4) (actual time=0.005..0.006 rows=4 loops=1)

  • Heap Fetches: 0
20. 0.000 9.148 ↑ 1.0 1 2,287

Limit (cost=0.42..1.36 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=2,287)

21. 2.356 9.148 ↑ 847.0 1 2,287

Result (cost=0.42..795.44 rows=847 width=12) (actual time=0.004..0.004 rows=1 loops=2,287)

  • One-Time Filter: (entities.e_et_code = ANY ('{PSD,VMTR}'::text[]))
22. 6.792 6.792 ↑ 847.0 1 2,264

Index Scan using tmi_indextrackingmobile on trackingmessagesindexed (cost=0.42..795.44 rows=847 width=12) (actual time=0.003..0.003 rows=1 loops=2,264)

  • Index Cond: (tmi_nl_mobile_id = nlm.nl_id)
23. 0.000 4.574 ↓ 0.0 0 2,287

Limit (cost=0.29..0.93 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=2,287)

24. 0.260 4.574 ↓ 0.0 0 2,287

Result (cost=0.29..20.15 rows=31 width=8) (actual time=0.002..0.002 rows=0 loops=2,287)

  • One-Time Filter: (entities.e_et_code = 'PSD'::text)
25. 4.314 4.314 ↓ 0.0 0 2,157

Index Scan Backward using checkpointmessages_pkey on checkpointmessages (cost=0.29..20.15 rows=31 width=8) (actual time=0.002..0.002 rows=0 loops=2,157)

  • Index Cond: ((cm_nl_mobile_id = nlm.nl_id) AND (cm_date >= COALESCE(trackingmessagesindexed.tmi_disconnect, 0)))
26. 0.000 0.000 ↓ 0.0 0 2,287

Limit (cost=0.29..0.43 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=2,287)

27. 0.000 0.000 ↓ 0.0 0 2,287

Result (cost=0.29..78.66 rows=525 width=12) (actual time=0.000..0.000 rows=0 loops=2,287)

  • One-Time Filter: (entities.e_et_code = 'TAG'::text)
28. 0.115 0.115 ↑ 525.0 1 23

Index Scan Backward using tagtrackingmessagesindexed_pkey on tagtrackingmessagesindexed (cost=0.29..78.66 rows=525 width=12) (actual time=0.005..0.005 rows=1 loops=23)

  • Index Cond: ((ttmi_nl_personnel_id = nlm.nl_id) AND (ttmi_te_code = 'TRACKING'::text))
29. 1.844 6.252 ↑ 1.0 9,021 1

Hash (cost=262.60..262.60 rows=9,021 width=53) (actual time=6.252..6.252 rows=9,021 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 570kB
30. 4.408 4.408 ↑ 1.0 9,021 1

Index Scan using nodeslogical_pkey on nodeslogical nlf (cost=0.29..262.60 rows=9,021 width=53) (actual time=0.012..4.408 rows=9,021 loops=1)

Planning time : 1.196 ms