explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EBM1 : Optimization for: plan #ChvU

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.005 0.104 ↓ 1.5 3 1

Nested Loop Left Join (cost=7.34..33.14 rows=2 width=191) (actual time=0.094..0.104 rows=3 loops=1)

  • Join Filter: (nlf.nl_id = COALESCE(tagtrackingmessagesindexed.ttmi_nl_fixed_id, trackingmessagesindexed.tmi_nl_fixed_id))
  • Rows Removed by Join Filter: 15
2. 0.002 0.093 ↓ 1.5 3 1

Nested Loop Left Join (cost=7.34..31.90 rows=2 width=116) (actual time=0.085..0.093 rows=3 loops=1)

3. 0.002 0.088 ↓ 1.5 3 1

Nested Loop Left Join (cost=7.19..15.44 rows=2 width=116) (actual time=0.084..0.088 rows=3 loops=1)

4. 0.001 0.077 ↓ 1.5 3 1

Unique (cost=7.04..7.05 rows=2 width=104) (actual time=0.076..0.077 rows=3 loops=1)

5. 0.005 0.076 ↓ 2.0 4 1

Sort (cost=7.04..7.04 rows=2 width=104) (actual time=0.075..0.076 rows=4 loops=1)

  • Sort Key: o.mobile_device_id
  • Sort Method: quicksort Memory: 25kB
6. 0.001 0.071 ↓ 2.0 4 1

Subquery Scan on o (cost=7.00..7.03 rows=2 width=104) (actual time=0.070..0.071 rows=4 loops=1)

7. 0.004 0.070 ↓ 2.0 4 1

Sort (cost=7.00..7.01 rows=2 width=108) (actual time=0.070..0.070 rows=4 loops=1)

  • Sort Key: (CASE WHEN (u.e_et_code = 'TAG'::text) THEN 1 ELSE NULL::integer END)
  • Sort Method: quicksort Memory: 25kB
8. 0.001 0.066 ↓ 2.0 4 1

Subquery Scan on u (cost=6.94..6.99 rows=2 width=108) (actual time=0.064..0.066 rows=4 loops=1)

9. 0.002 0.065 ↓ 2.0 4 1

Unique (cost=6.94..6.97 rows=2 width=104) (actual time=0.063..0.065 rows=4 loops=1)

10. 0.005 0.063 ↓ 2.0 4 1

Sort (cost=6.94..6.94 rows=2 width=104) (actual time=0.062..0.063 rows=4 loops=1)

  • Sort Key: nodeslogical.nl_id, tagdeviceshistory.tdh_nl_lamp_id, nodeslogical.nl_label, nodeslogical.nl_details, entities.e_et_code
  • Sort Method: quicksort Memory: 25kB
11. 0.000 0.058 ↓ 2.0 4 1

Append (cost=3.56..6.93 rows=2 width=104) (actual time=0.036..0.058 rows=4 loops=1)

12. 0.002 0.041 ↑ 1.0 1 1

Result (cost=3.56..4.65 rows=1 width=52) (actual time=0.036..0.041 rows=1 loops=1)

  • One-Time Filter: $0
13.          

Initplan (for Result)

14. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on serveradmin (cost=0.00..1.36 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: 23
15. 0.002 0.031 ↑ 1.0 1 1

Nested Loop (cost=2.20..3.29 rows=1 width=52) (actual time=0.026..0.031 rows=1 loops=1)

16. 0.012 0.021 ↓ 2.0 2 1

Hash Join (cost=1.17..2.24 rows=1 width=48) (actual time=0.019..0.021 rows=2 loops=1)

  • Hash Cond: (nodeslogical.nl_e_id = entities.e_id)
17. 0.002 0.002 ↑ 1.0 5 1

Seq Scan on nodeslogical (cost=0.00..1.05 rows=5 width=47) (actual time=0.002..0.002 rows=5 loops=1)

18. 0.003 0.007 ↑ 1.0 2 1

Hash (cost=1.15..1.15 rows=2 width=9) (actual time=0.007..0.007 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.004 0.004 ↑ 1.0 2 1

Seq Scan on entities (cost=0.00..1.15 rows=2 width=9) (actual time=0.003..0.004 rows=2 loops=1)

  • Filter: (e_et_code = 'TAG'::text)
  • Rows Removed by Filter: 10
20. 0.000 0.008 ↓ 0.0 0 2

Limit (cost=1.02..1.03 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=2)

21. 0.004 0.008 ↓ 0.0 0 2

Sort (cost=1.02..1.03 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=2)

  • Sort Key: tagdeviceshistory.tdh_date_start DESC
  • Sort Method: quicksort Memory: 25kB
22. 0.004 0.004 ↓ 0.0 0 2

Seq Scan on tagdeviceshistory (cost=0.00..1.01 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=2)

  • Filter: (tdh_nl_personnel_id = nodeslogical.nl_id)
  • Rows Removed by Filter: 0
23. 0.010 0.017 ↓ 3.0 3 1

Hash Join (cost=1.19..2.25 rows=1 width=52) (actual time=0.016..0.017 rows=3 loops=1)

  • Hash Cond: (nodeslogical_1.nl_e_id = entities_1.e_id)
24. 0.002 0.002 ↑ 1.0 5 1

Seq Scan on nodeslogical nodeslogical_1 (cost=0.00..1.05 rows=5 width=47) (actual time=0.001..0.002 rows=5 loops=1)

25. 0.001 0.005 ↑ 1.0 3 1

Hash (cost=1.15..1.15 rows=3 width=9) (actual time=0.005..0.005 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.004 0.004 ↑ 1.0 3 1

Seq Scan on entities entities_1 (cost=0.00..1.15 rows=3 width=9) (actual time=0.003..0.004 rows=3 loops=1)

  • Filter: (e_et_code = ANY ('{PSD,VMTR}'::text[]))
  • Rows Removed by Filter: 9
27. 0.000 0.009 ↓ 0.0 0 3

Limit (cost=0.15..4.17 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=3)

28. 0.003 0.009 ↓ 0.0 0 3

Result (cost=0.15..20.24 rows=5 width=12) (actual time=0.003..0.003 rows=0 loops=3)

  • One-Time Filter: (o.e_et_code = ANY ('{PSD,VMTR}'::text[]))
29. 0.006 0.006 ↓ 0.0 0 2

Index Scan using tmi_indextrackingmobile on trackingmessagesindexed (cost=0.15..20.24 rows=5 width=12) (actual time=0.003..0.003 rows=0 loops=2)

  • Index Cond: (tmi_nl_mobile_id = o.mobile_device_id)
30. 0.000 0.003 ↓ 0.0 0 3

Limit (cost=0.15..8.21 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=3)

31. 0.000 0.003 ↓ 0.0 0 3

Result (cost=0.15..8.21 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=3)

  • One-Time Filter: (o.e_et_code = 'TAG'::text)
32. 0.003 0.003 ↓ 0.0 0 1

Index Scan Backward using tagtrackingmessagesindexed_pkey on tagtrackingmessagesindexed (cost=0.15..8.21 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((ttmi_nl_personnel_id = o.mobile_device_id) AND (ttmi_te_code = 'TRACKING'::text))
33. 0.003 0.006 ↑ 1.0 5 3

Materialize (cost=0.00..1.07 rows=5 width=43) (actual time=0.001..0.002 rows=5 loops=3)

34. 0.003 0.003 ↑ 1.0 5 1

Seq Scan on nodeslogical nlf (cost=0.00..1.05 rows=5 width=43) (actual time=0.002..0.003 rows=5 loops=1)