explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5jcR

Settings
# exclusive inclusive rows x rows loops node
1. 7.251 296.459 ↑ 1.0 7,267 1

WindowAgg (cost=26,000.12..26,363.47 rows=7,267 width=862) (actual time=294.022..296.459 rows=7,267 loops=1)

2. 36.248 289.208 ↑ 1.0 7,267 1

Merge Left Join (cost=26,000.12..26,199.97 rows=7,267 width=846) (actual time=250.339..289.208 rows=7,267 loops=1)

  • Merge Cond: (s.id = sa.sensor_id)
3. 5.124 121.446 ↑ 1.0 7,267 1

Sort (cost=15,603.40..15,621.57 rows=7,267 width=304) (actual time=120.098..121.446 rows=7,267 loops=1)

  • Sort Key: s.id
  • Sort Method: quicksort Memory: 2903kB
4. 4.253 116.322 ↑ 1.0 7,267 1

Hash Right Join (cost=14,313.64..15,137.33 rows=7,267 width=304) (actual time=109.870..116.322 rows=7,267 loops=1)

  • Hash Cond: (c.id = uc.contact_id)
5. 2.270 2.270 ↑ 1.0 25,601 1

Seq Scan on contact c (cost=0.00..655.01 rows=25,601 width=38) (actual time=0.005..2.270 rows=25,601 loops=1)

6. 3.277 109.799 ↑ 1.0 7,267 1

Hash (cost=14,222.81..14,222.81 rows=7,267 width=274) (actual time=109.799..109.799 rows=7,267 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 1764kB
7. 1.366 106.522 ↑ 1.0 7,267 1

Merge Left Join (cost=14,142.29..14,222.81 rows=7,267 width=274) (actual time=102.380..106.522 rows=7,267 loops=1)

  • Merge Cond: (s.id = fn.sensor_id)
8. 1.970 105.111 ↑ 1.0 7,267 1

Merge Left Join (cost=14,138.09..14,199.90 rows=7,267 width=261) (actual time=102.334..105.111 rows=7,267 loops=1)

  • Merge Cond: ((s.id = sat.sensor_id) AND (u.organization_id = sat.organization_id))
9. 4.846 103.141 ↑ 1.0 7,267 1

Sort (cost=14,070.27..14,088.43 rows=7,267 width=233) (actual time=102.332..103.141 rows=7,267 loops=1)

  • Sort Key: s.id, u.organization_id
  • Sort Method: quicksort Memory: 2217kB
10. 1.964 98.295 ↑ 1.0 7,267 1

Hash Left Join (cost=12,100.81..13,604.19 rows=7,267 width=233) (actual time=69.588..98.295 rows=7,267 loops=1)

  • Hash Cond: (s.owner = uc.user_id)
11. 2.248 89.905 ↑ 1.0 7,267 1

Hash Left Join (cost=11,363.16..12,766.62 rows=7,267 width=229) (actual time=63.069..89.905 rows=7,267 loops=1)

  • Hash Cond: (l.address_id = a.id)
12. 2.436 85.052 ↑ 1.0 7,267 1

Hash Left Join (cost=11,135.92..12,439.47 rows=7,267 width=174) (actual time=60.435..85.052 rows=7,267 loops=1)

  • Hash Cond: (s.location_id = l.id)
13. 1.200 78.427 ↑ 1.0 7,267 1

Nested Loop (cost=10,703.80..11,907.42 rows=7,267 width=160) (actual time=56.195..78.427 rows=7,267 loops=1)

14. 0.008 0.008 ↑ 1.0 1 1

Index Scan using api_users_pkey on api_users u (cost=0.29..8.30 rows=1 width=4) (actual time=0.006..0.008 rows=1 loops=1)

  • Index Cond: (id = 25)
15. 8.093 77.219 ↑ 1.0 7,267 1

Hash Join (cost=10,703.51..11,826.44 rows=7,267 width=156) (actual time=56.186..77.219 rows=7,267 loops=1)

  • Hash Cond: (s.manufacture_information = mi.id)
16. 1.507 46.750 ↑ 1.0 7,267 1

Subquery Scan on s (cost=8,682.26..9,009.28 rows=7,267 width=123) (actual time=33.401..46.750 rows=7,267 loops=1)

17. 7.612 45.243 ↑ 1.0 7,267 1

Unique (cost=8,682.26..8,936.61 rows=7,267 width=1,299) (actual time=33.400..45.243 rows=7,267 loops=1)

18. 16.556 37.631 ↑ 1.0 7,267 1

Sort (cost=8,682.26..8,700.43 rows=7,267 width=1,299) (actual time=33.395..37.631 rows=7,267 loops=1)

  • Sort Key: s_1.id, s_1.manufacture_information, s_1.location_id, s_1.firmware, s_1.hardware, s_1.crystal, s_1.sensor_type, s_1.features, s_1.owner, s_1.customer_name, s_1.configuration, s_1.primary_gateway_id, ((array_remove(array_agg(DISTINCT p.granted), NULL::metadata.enum_permissions))::character varying[])
  • Sort Method: external sort Disk: 9144kB
19. 15.729 21.075 ↑ 1.0 7,267 1

GroupAggregate (cost=273.68..4,089.69 rows=7,267 width=1,299) (actual time=0.314..21.075 rows=7,267 loops=1)

  • Group Key: s_1.id
20. 1.734 5.346 ↓ 1.0 7,287 1

Merge Left Join (cost=273.68..3,908.01 rows=7,267 width=1,271) (actual time=0.275..5.346 rows=7,287 loops=1)

  • Merge Cond: (s_1.id = usp.sensor_id)
21. 3.323 3.323 ↑ 1.0 7,267 1

Index Scan using sensors_pkey on sensors s_1 (cost=0.28..3,616.03 rows=7,267 width=1,267) (actual time=0.004..3.323 rows=7,267 loops=1)

22. 0.043 0.289 ↓ 3.7 104 1

Sort (cost=273.40..273.47 rows=28 width=8) (actual time=0.268..0.289 rows=104 loops=1)

  • Sort Key: usp.sensor_id
  • Sort Method: quicksort Memory: 29kB
23. 0.011 0.246 ↓ 3.7 104 1

Nested Loop Left Join (cost=0.84..272.72 rows=28 width=8) (actual time=0.023..0.246 rows=104 loops=1)

24. 0.027 0.027 ↓ 3.7 104 1

Index Scan using api_user_sensor_permissions_user_id_idx on api_user_sensor_permissions usp (cost=0.42..35.84 rows=28 width=8) (actual time=0.012..0.027 rows=104 loops=1)

  • Index Cond: (user_id = 25)
25. 0.208 0.208 ↑ 1.0 1 104

Index Scan using api_permissions_pkey on api_permissions p (cost=0.42..8.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=104)

  • Index Cond: (id = usp.permission_id)
  • Filter: ((deleted IS NULL) AND ((begins <= now()) OR (begins IS NULL)) AND ((ends > now()) OR (ends IS NULL)))
  • Rows Removed by Filter: 0
26. 13.116 22.376 ↑ 1.0 48,411 1

Hash (cost=990.11..990.11 rows=48,411 width=41) (actual time=22.376..22.376 rows=48,411 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 2077kB
27. 9.260 9.260 ↑ 1.0 48,411 1

Seq Scan on manufacturing_information mi (cost=0.00..990.11 rows=48,411 width=41) (actual time=0.015..9.260 rows=48,411 loops=1)

28. 2.326 4.189 ↑ 1.0 15,739 1

Hash (cost=235.39..235.39 rows=15,739 width=22) (actual time=4.189..4.189 rows=15,739 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 802kB
29. 1.863 1.863 ↑ 1.0 15,739 1

Seq Scan on metadata_location l (cost=0.00..235.39 rows=15,739 width=22) (actual time=0.010..1.863 rows=15,739 loops=1)

30. 1.592 2.605 ↑ 1.0 6,677 1

Hash (cost=143.77..143.77 rows=6,677 width=59) (actual time=2.605..2.605 rows=6,677 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 698kB
31. 1.013 1.013 ↑ 1.0 6,677 1

Seq Scan on address a (cost=0.00..143.77 rows=6,677 width=59) (actual time=0.005..1.013 rows=6,677 loops=1)

32. 3.443 6.426 ↑ 1.0 25,540 1

Hash (cost=418.40..418.40 rows=25,540 width=8) (actual time=6.426..6.426 rows=25,540 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1254kB
33. 2.983 2.983 ↑ 1.0 25,540 1

Seq Scan on users_contacts uc (cost=0.00..418.40 rows=25,540 width=8) (actual time=0.008..2.983 rows=25,540 loops=1)

34. 0.000 0.000 ↓ 0.0 0

Sort (cost=67.82..70.25 rows=970 width=40) (never executed)

  • Sort Key: sat.sensor_id, sat.organization_id
35. 0.000 0.000 ↓ 0.0 0

Seq Scan on api_sensor_attributes sat (cost=0.00..19.70 rows=970 width=40) (never executed)

36. 0.001 0.045 ↑ 1.0 1 1

Materialize (cost=4.20..4.21 rows=1 width=17) (actual time=0.045..0.045 rows=1 loops=1)

37. 0.009 0.044 ↑ 1.0 1 1

Sort (cost=4.20..4.20 rows=1 width=17) (actual time=0.044..0.044 rows=1 loops=1)

  • Sort Key: fn.sensor_id
  • Sort Method: quicksort Memory: 25kB
38. 0.035 0.035 ↑ 1.0 1 1

Seq Scan on api_sensors_fnames fn (cost=0.00..4.19 rows=1 width=17) (actual time=0.035..0.035 rows=1 loops=1)

  • Filter: ((deleted IS NULL) AND (user_id = 25))
  • Rows Removed by Filter: 175
39. 3.850 131.514 ↓ 34.9 6,980 1

Sort (cost=10,396.72..10,397.22 rows=200 width=36) (actual time=130.224..131.514 rows=6,980 loops=1)

  • Sort Key: sa.sensor_id
  • Sort Method: quicksort Memory: 2050kB
40. 0.948 127.664 ↓ 34.9 6,980 1

Subquery Scan on sa (cost=10,383.58..10,389.08 rows=200 width=36) (actual time=107.962..127.664 rows=6,980 loops=1)

41. 50.637 126.716 ↓ 34.9 6,980 1

HashAggregate (cost=10,383.58..10,387.08 rows=200 width=36) (actual time=107.961..126.716 rows=6,980 loops=1)

  • Group Key: usp_1.sensor_id
42. 3.452 76.079 ↓ 1.2 6,993 1

Hash Join (cost=9,526.69..10,338.20 rows=6,050 width=64) (actual time=70.438..76.079 rows=6,993 loops=1)

  • Hash Cond: (c_1.id = uc_1.contact_id)
43. 2.232 2.232 ↑ 1.0 25,601 1

Seq Scan on contact c_1 (cost=0.00..655.01 rows=25,601 width=38) (actual time=0.003..2.232 rows=25,601 loops=1)

44. 1.281 70.395 ↓ 1.2 6,993 1

Hash (cost=9,451.06..9,451.06 rows=6,050 width=34) (actual time=70.395..70.395 rows=6,993 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 527kB
45. 3.187 69.114 ↓ 1.2 6,993 1

Hash Join (cost=8,876.39..9,451.06 rows=6,050 width=34) (actual time=63.949..69.114 rows=6,993 loops=1)

  • Hash Cond: (uc_1.user_id = u_1.id)
46. 2.012 2.012 ↑ 1.0 25,540 1

Seq Scan on users_contacts uc_1 (cost=0.00..418.40 rows=25,540 width=8) (actual time=0.006..2.012 rows=25,540 loops=1)

47. 1.369 63.915 ↓ 1.2 6,993 1

Hash (cost=8,800.76..8,800.76 rows=6,050 width=34) (actual time=63.915..63.915 rows=6,993 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 512kB
48. 1.844 62.546 ↓ 1.2 6,993 1

Hash Join (cost=8,596.58..8,800.76 rows=6,050 width=34) (actual time=59.910..62.546 rows=6,993 loops=1)

  • Hash Cond: (usp_1.user_id = u_1.id)
49. 2.455 53.175 ↓ 1.2 6,993 1

HashAggregate (cost=7,664.93..7,725.43 rows=6,050 width=8) (actual time=52.288..53.175 rows=6,993 loops=1)

  • Group Key: usp_1.sensor_id, usp_1.user_id
50. 20.375 50.720 ↓ 1.2 7,059 1

Hash Join (cost=3,025.21..7,634.68 rows=6,050 width=8) (actual time=4.561..50.720 rows=7,059 loops=1)

  • Hash Cond: (usp_1.permission_id = p_1.id)
51. 25.861 25.861 ↓ 1.0 237,453 1

Seq Scan on api_user_sensor_permissions usp_1 (cost=0.00..3,658.52 rows=237,452 width=12) (actual time=0.005..25.861 rows=237,453 loops=1)

52. 1.110 4.484 ↓ 1.0 7,212 1

Hash (cost=2,939.34..2,939.34 rows=6,870 width=4) (actual time=4.484..4.484 rows=7,212 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 318kB
53. 2.914 3.374 ↓ 1.0 7,212 1

Bitmap Heap Scan on api_permissions p_1 (cost=148.94..2,939.34 rows=6,870 width=4) (actual time=0.519..3.374 rows=7,212 loops=1)

  • Recheck Cond: ((granted = 'IS_ADMIN'::metadata.enum_permissions) AND (deleted IS NULL))
  • Filter: (((begins <= now()) OR (begins IS NULL)) AND ((ends > now()) OR (ends IS NULL)))
  • Heap Blocks: exact=538
54. 0.460 0.460 ↑ 1.1 7,212 1

Bitmap Index Scan on api_permissions_idx (cost=0.00..147.22 rows=7,840 width=0) (actual time=0.460..0.460 rows=7,212 loops=1)

  • Index Cond: (granted = 'IS_ADMIN'::metadata.enum_permissions)
55. 4.349 7.527 ↑ 1.0 25,540 1

Hash (cost=612.40..612.40 rows=25,540 width=26) (actual time=7.527..7.527 rows=25,540 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1715kB
56. 3.178 3.178 ↑ 1.0 25,540 1

Seq Scan on api_users u_1 (cost=0.00..612.40 rows=25,540 width=26) (actual time=0.004..3.178 rows=25,540 loops=1)

Planning time : 12.006 ms
Execution time : 303.077 ms