explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wbKT

Settings
# exclusive inclusive rows x rows loops node
1. 0.952 39.561 ↓ 11.4 2,271 1

Hash Left Join (cost=28,825.16..29,700.09 rows=200 width=181) (actual time=29.654..39.561 rows=2,271 loops=1)

  • Hash Cond: (trackingmessagesindexed.tmi_nl_fixed_id = nlf.nl_id)
2. 1.579 34.553 ↓ 11.4 2,271 1

Nested Loop Left Join (cost=28,522.19..29,394.09 rows=200 width=112) (actual time=25.476..34.553 rows=2,271 loops=1)

3. 0.461 26.161 ↓ 11.4 2,271 1

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

4. 1.164 25.700 ↑ 3.6 2,295 1

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

  • Sort Key: o.mobile_device_id
  • Sort Method: quicksort Memory: 276kB
5. 0.337 24.536 ↑ 3.6 2,295 1

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

6. 0.884 24.199 ↑ 3.6 2,295 1

Sort (cost=27,888.74..27,909.17 rows=8,170 width=108) (actual time=24.064..24.199 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
7. 0.338 23.315 ↑ 3.6 2,295 1

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

8. 1.725 22.977 ↑ 3.6 2,295 1

HashAggregate (cost=27,174.03..27,255.73 rows=8,170 width=104) (actual time=22.409..22.977 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
9. 0.180 21.252 ↑ 3.6 2,295 1

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

10. 0.007 16.607 ↑ 248.7 24 1

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

  • One-Time Filter: $0
11.          

Initplan (for Result)

12. 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
13. 3.915 16.590 ↑ 248.7 24 1

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

14. 2.912 6.520 ↓ 1.0 6,155 1

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

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

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

16. 1.225 2.786 ↑ 1.0 6,155 1

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

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

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

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

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

19. 0.000 6.155 ↓ 0.0 0 6,155

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

  • Sort Key: tagdeviceshistory.tdh_date_start DESC
  • Sort Method: top-N heapsort Memory: 25kB
20. 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
21. 1.706 4.465 ↓ 1.0 2,271 1

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

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

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

23. 0.531 1.973 ↑ 1.0 2,271 1

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

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

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

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

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

26. 6.813 6.813 ↑ 822.0 1 2,271

Index Scan using tmi_indextrackingmobile on trackingmessagesindexed (cost=0.42..3,043.81 rows=822 width=12) (actual time=0.003..0.003 rows=1 loops=2,271)

  • Index Cond: (tmi_nl_mobile_id = o.mobile_device_id)
27. 2.179 4.056 ↑ 1.0 9,021 1

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

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

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