explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CIYN

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 5,954.780 ↓ 10.0 10 1

Limit (cost=1,116.21..1,116.22 rows=1 width=278) (actual time=5,954.779..5,954.780 rows=10 loops=1)

2. 4.251 5,954.778 ↓ 10.0 10 1

Sort (cost=1,116.21..1,116.22 rows=1 width=278) (actual time=5,954.778..5,954.778 rows=10 loops=1)

  • Sort Key: d.placement
  • Sort Method: top-N heapsort Memory: 32kB
3. 31.954 5,950.527 ↓ 1,992.0 1,992 1

Nested Loop Left Join (cost=940.89..1,116.20 rows=1 width=278) (actual time=6.557..5,950.527 rows=1,992 loops=1)

  • Join Filter: ((d.product)::text = (pr."productCode")::text)
  • Rows Removed by Join Filter: 406,379
4. 241.459 5,888.693 ↓ 1,992.0 1,992 1

Nested Loop Left Join (cost=940.89..1,106.59 rows=1 width=203) (actual time=6.544..5,888.693 rows=1,992 loops=1)

  • Join Filter: (s."deviceId" = d.id)
  • Rows Removed by Join Filter: 5,318,640
5. 1.427 15.850 ↓ 1,992.0 1,992 1

Nested Loop (cost=23.54..33.78 rows=1 width=188) (actual time=0.998..15.850 rows=1,992 loops=1)

6. 1.977 2.447 ↓ 1,996.0 1,996 1

HashAggregate (cost=23.26..25.47 rows=1 width=16) (actual time=0.983..2.447 rows=1,996 loops=1)

  • Group Key: tl.deviceid
  • Filter: (count(tl.id) = 1)
7. 0.165 0.470 ↓ 11.3 1,996 1

Nested Loop (cost=0.28..22.37 rows=177 width=20) (actual time=0.031..0.470 rows=1,996 loops=1)

8. 0.017 0.017 ↑ 1.0 1 1

Seq Scan on tags t (cost=0.00..1.16 rows=1 width=4) (actual time=0.016..0.017 rows=1 loops=1)

  • Filter: ((slug)::text = 'os-ios'::text)
  • Rows Removed by Filter: 12
9. 0.288 0.288 ↓ 6.9 1,996 1

Index Scan using tag_links_tagid on tag_links tl (cost=0.28..18.33 rows=288 width=24) (actual time=0.013..0.288 rows=1,996 loops=1)

  • Index Cond: (tagid = t.id)
10. 11.976 11.976 ↑ 1.0 1 1,996

Index Scan using device_tmp_pkey on devices d (cost=0.28..8.30 rows=1 width=188) (actual time=0.006..0.006 rows=1 loops=1,996)

  • Index Cond: (id = tl.deviceid)
  • Filter: (status = 'operation'::device_status_type)
  • Rows Removed by Filter: 0
11. 537.840 5,631.384 ↓ 111.3 2,671 1,992

Subquery Scan on s (cost=917.35..1,072.51 rows=24 width=31) (actual time=0.003..2.827 rows=2,671 loops=1,992)

  • Filter: (s.dest_rank = 1)
  • Rows Removed by Filter: 2,093
12. 4,661.280 5,093.544 ↑ 1.0 4,764 1,992

WindowAgg (cost=917.35..1,012.83 rows=4,774 width=39) (actual time=0.003..2.557 rows=4,764 loops=1,992)

13. 428.704 432.264 ↑ 1.0 4,764 1,992

Sort (cost=917.35..929.29 rows=4,774 width=31) (actual time=0.003..0.217 rows=4,764 loops=1,992)

  • Sort Key: device_status."deviceId", device_status."changedAt" DESC
  • Sort Method: quicksort Memory: 565kB
14. 3.560 3.560 ↑ 1.0 4,764 1

Seq Scan on device_status (cost=0.00..625.64 rows=4,774 width=31) (actual time=0.009..3.560 rows=4,764 loops=1)

  • Filter: ((status)::text = ANY ('{online,offline}'::text[]))
  • Rows Removed by Filter: 20,327
15. 29.880 29.880 ↑ 1.0 205 1,992

Seq Scan on products pr (cost=0.00..7.05 rows=205 width=100) (actual time=0.002..0.015 rows=205 loops=1,992)

Planning time : 0.687 ms
Execution time : 5,954.876 ms