explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KZmN

Settings
# exclusive inclusive rows x rows loops node
1. 1.275 79.986 ↓ 11.4 2,271 1

Hash Left Join (cost=28,825.44..29,866.41 rows=200 width=181) (actual time=50.057..79.986 rows=2,271 loops=1)

  • Hash Cond: (COALESCE(tagtrackingmessagesindexed.ttmi_nl_fixed_id, trackingmessagesindexed.tmi_nl_fixed_id) = nlf.nl_id)
2. 1.715 66.395 ↓ 11.4 2,271 1

Nested Loop Left Join (cost=28,522.47..29,560.41 rows=200 width=116) (actual time=37.600..66.395 rows=2,271 loops=1)

3. 10.746 64.680 ↓ 11.4 2,271 1

Nested Loop Left Join (cost=28,522.19..29,395.08 rows=200 width=116) (actual time=37.594..64.680 rows=2,271 loops=1)

4. 4.585 42.579 ↓ 11.4 2,271 1

Unique (cost=28,521.76..28,562.61 rows=200 width=104) (actual time=37.532..42.579 rows=2,271 loops=1)

5. 1.764 37.994 ↑ 3.6 2,295 1

Sort (cost=28,521.76..28,542.19 rows=8,170 width=104) (actual time=37.530..37.994 rows=2,295 loops=1)

  • Sort Key: o.mobile_device_id
  • Sort Method: quicksort Memory: 276kB
6. 0.485 36.230 ↑ 3.6 2,295 1

Subquery Scan on o (cost=27,888.74..27,990.87 rows=8,170 width=104) (actual time=35.551..36.230 rows=2,295 loops=1)

7. 0.922 35.745 ↑ 3.6 2,295 1

Sort (cost=27,888.74..27,909.17 rows=8,170 width=108) (actual time=35.549..35.745 rows=2,295 loops=1)

  • Sort Key: (CASE WHEN (u.e_et_code = 'TAG'::text) THEN 1 ELSE NULL::integer END)
  • Sort Method: quicksort Memory: 276kB
8. 0.339 34.823 ↑ 3.6 2,295 1

Subquery Scan on u (cost=27,174.03..27,357.85 rows=8,170 width=108) (actual time=33.927..34.823 rows=2,295 loops=1)

9. 1.733 34.484 ↑ 3.6 2,295 1

HashAggregate (cost=27,174.03..27,255.73 rows=8,170 width=104) (actual time=33.925..34.484 rows=2,295 loops=1)

  • Group Key: nodeslogical.nl_id, tagdeviceshistory.tdh_nl_lamp_id, nodeslogical.nl_label, nodeslogical.nl_details, entities.e_et_code
10. 0.190 32.751 ↑ 3.6 2,295 1

Append (cost=250.05..27,071.90 rows=8,170 width=104) (actual time=11.277..32.751 rows=2,295 loops=1)

11. 0.008 27.910 ↑ 248.7 24 1

Result (cost=250.05..26,539.76 rows=5,968 width=40) (actual time=11.275..27.910 rows=24 loops=1)

  • One-Time Filter: $0
12.          

Initplan (for Result)

13. 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
14. 0.260 27.891 ↑ 248.7 24 1

Nested Loop (cost=248.59..26,538.30 rows=5,968 width=40) (actual time=11.261..27.891 rows=24 loops=1)

15. 3.176 15.321 ↓ 1.0 6,155 1

Hash Join (cost=244.24..458.14 rows=5,968 width=36) (actual time=11.054..15.321 rows=6,155 loops=1)

  • Hash Cond: (nodeslogical.nl_e_id = entities.e_id)
16. 1.193 1.193 ↑ 1.0 9,021 1

Seq Scan on nodeslogical (cost=0.00..190.21 rows=9,021 width=37) (actual time=0.020..1.193 rows=9,021 loops=1)

17. 9.285 10.952 ↑ 1.0 6,155 1

Hash (cost=167.30..167.30 rows=6,155 width=7) (actual time=10.952..10.952 rows=6,155 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 305kB
18. 1.667 1.667 ↑ 1.0 6,155 1

Seq Scan on entities (cost=0.00..167.30 rows=6,155 width=7) (actual time=0.021..1.667 rows=6,155 loops=1)

  • Filter: (e_et_code = 'TAG'::text)
  • Rows Removed by Filter: 3,149
19. 0.000 12.310 ↓ 0.0 0 6,155

Limit (cost=4.35..4.35 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=6,155)

20. 6.155 12.310 ↓ 0.0 0 6,155

Sort (cost=4.35..4.37 rows=9 width=8) (actual time=0.002..0.002 rows=0 loops=6,155)

  • Sort Key: tagdeviceshistory.tdh_date_start DESC
  • Sort Method: top-N heapsort Memory: 25kB
21. 6.155 6.155 ↓ 0.0 0 6,155

Index Only Scan using tagdeviceshistory_pkey on tagdeviceshistory (cost=0.14..4.30 rows=9 width=8) (actual time=0.001..0.001 rows=0 loops=6,155)

  • Index Cond: (tdh_nl_personnel_id = nodeslogical.nl_id)
  • Heap Fetches: 0
22. 1.868 4.651 ↓ 1.0 2,271 1

Hash Join (cost=195.69..409.59 rows=2,202 width=40) (actual time=2.028..4.651 rows=2,271 loops=1)

  • Hash Cond: (nodeslogical_1.nl_e_id = entities_1.e_id)
23. 0.826 0.826 ↑ 1.0 9,021 1

Seq Scan on nodeslogical nodeslogical_1 (cost=0.00..190.21 rows=9,021 width=37) (actual time=0.019..0.826 rows=9,021 loops=1)

24. 0.455 1.957 ↑ 1.0 2,271 1

Hash (cost=167.30..167.30 rows=2,271 width=7) (actual time=1.957..1.957 rows=2,271 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 122kB
25. 1.502 1.502 ↑ 1.0 2,271 1

Seq Scan on entities entities_1 (cost=0.00..167.30 rows=2,271 width=7) (actual time=0.012..1.502 rows=2,271 loops=1)

  • Filter: (e_et_code = ANY ('{PSD,VMTR}'::text[]))
  • Rows Removed by Filter: 7,033
26. 0.000 11.355 ↑ 1.0 1 2,271

Limit (cost=0.42..4.13 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=2,271)

27. 2.323 11.355 ↑ 835.0 1 2,271

Result (cost=0.42..3,096.07 rows=835 width=12) (actual time=0.005..0.005 rows=1 loops=2,271)

  • One-Time Filter: (o.e_et_code = ANY ('{PSD,VMTR}'::text[]))
28. 9.032 9.032 ↑ 835.0 1 2,258

Index Scan using tmi_indextrackingmobile on trackingmessagesindexed (cost=0.42..3,096.07 rows=835 width=12) (actual time=0.004..0.004 rows=1 loops=2,258)

  • Index Cond: (tmi_nl_mobile_id = o.mobile_device_id)
29. 0.000 0.000 ↓ 0.0 0 2,271

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

30. 0.000 0.000 ↓ 0.0 0 2,271

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

  • One-Time Filter: (o.e_et_code = 'TAG'::text)
31. 0.130 0.130 ↓ 0.0 0 13

Index Scan Backward using tagtrackingmessagesindexed_pkey on tagtrackingmessagesindexed (cost=0.29..274.15 rows=525 width=8) (actual time=0.010..0.010 rows=0 loops=13)

  • Index Cond: ((ttmi_nl_personnel_id = o.mobile_device_id) AND (ttmi_te_code = 'TRACKING'::text))
32. 3.635 12.316 ↑ 1.0 9,021 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 569kB
33. 8.681 8.681 ↑ 1.0 9,021 1

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