explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tqDv

Settings
# exclusive inclusive rows x rows loops node
1. 1.183 30.367 ↓ 1.6 1,877 1

Hash Join (cost=605.98..10,148.84 rows=1,198 width=137) (actual time=8.427..30.367 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.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: 20
4. 1.618 24.629 ↓ 1.9 2,287 1

Nested Loop Left Join (cost=301.54..9,826.29 rows=1,198 width=68) (actual time=3.855..24.629 rows=2,287 loops=1)

5. 0.000 23.011 ↓ 1.9 2,287 1

Nested Loop Left Join (cost=301.26..8,835.97 rows=1,198 width=56) (actual time=3.853..23.011 rows=2,287 loops=1)

6. 0.886 16.254 ↓ 1.9 2,287 1

Nested Loop Left Join (cost=300.96..5,463.24 rows=1,198 width=48) (actual time=3.851..16.254 rows=2,287 loops=1)

7. 1.799 6.220 ↓ 1.9 2,287 1

Hash Join (cost=300.54..495.38 rows=1,198 width=36) (actual time=3.825..6.220 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.650 0.650 ↑ 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.026..0.650 rows=2,885 loops=1)

9. 1.859 3.735 ↓ 1.2 8,426 1

Hash (cost=213.82..213.82 rows=6,915 width=7) (actual time=3.734..3.735 rows=8,426 loops=1)

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

Seq Scan on entities (cost=0.00..213.82 rows=6,915 width=7) (actual time=0.011..1.876 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
11.          

SubPlan (forHash Join)

12. 0.000 0.000 ↓ 0.0 0

Result (cost=1.46..6.23 rows=1 width=0) (never executed)

  • One-Time Filter: $1
13.          

Initplan (forResult)

14. 0.000 0.000 ↓ 0.0 0

Seq Scan on serveradmin serveradmin_1 (cost=0.00..1.46 rows=1 width=0) (never executed)

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

Seq Scan on tagdeviceshistory (cost=1.46..6.23 rows=1 width=0) (never executed)

  • Filter: ((tdh_date_end IS NULL) AND (tdh_nl_lamp_id = nlm.nl_id))
16. 0.002 0.036 ↑ 1.0 4 1

Result (cost=1.46..5.67 rows=4 width=4) (actual time=0.023..0.036 rows=4 loops=1)

  • One-Time Filter: $4
17.          

Initplan (forResult)

18. 0.008 0.008 ↑ 1.0 1 1

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

  • Filter: (((sa_key)::text = 'enableRFID'::text) AND ((sa_value)::text = '1'::text))
  • Rows Removed by Filter: 20
19. 0.026 0.026 ↑ 1.0 4 1

Seq Scan on tagdeviceshistory tagdeviceshistory_1 (cost=1.46..5.67 rows=4 width=4) (actual time=0.014..0.026 rows=4 loops=1)

  • Filter: (tdh_date_end IS NULL)
  • Rows Removed by Filter: 217
20. 0.000 9.148 ↑ 1.0 1 2,287

Limit (cost=0.42..4.13 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..3,135.85 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..3,135.85 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. 2.287 6.861 ↓ 0.0 0 2,287

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

24. 0.260 4.574 ↓ 0.0 0 2,287

Result (cost=0.29..77.88 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..77.88 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.81 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..274.15 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..274.15 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. 2.173 4.544 ↑ 1.0 9,021 1

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

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

Seq Scan on nodeslogical nlf (cost=0.00..190.21 rows=9,021 width=53) (actual time=0.013..2.371 rows=9,021 loops=1)

Planning time : 1.222 ms