explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O2ox

Settings
# exclusive inclusive rows x rows loops node
1. 26.391 241.418 ↑ 27.7 7,267 1

WindowAgg (cost=163,455.78..176,523.51 rows=201,042 width=856) (actual time=219.512..241.418 rows=7,267 loops=1)

2.          

CTE visible_sensors

3. 5.128 29.227 ↑ 1.0 7,267 1

HashAggregate (cost=2,300.24..2,409.25 rows=7,267 width=49) (actual time=28.081..29.227 rows=7,267 loops=1)

  • Group Key: s_1.id, s_1.firmware, s_1.crystal, s_1.sensor_type, (array_agg(DISTINCT p.granted) FILTER (WHERE (p.granted IS NOT NULL)))::character varying[]
4. 13.897 24.099 ↑ 1.0 7,267 1

GroupAggregate (cost=2,009.14..2,209.40 rows=7,267 width=49) (actual time=6.924..24.099 rows=7,267 loops=1)

  • Group Key: s_1.id
5. 1.893 10.202 ↓ 1.0 7,287 1

Merge Left Join (cost=2,009.14..2,045.90 rows=7,267 width=21) (actual time=6.883..10.202 rows=7,287 loops=1)

  • Merge Cond: (s_1.id = usp.sensor_id)
6. 4.590 7.839 ↑ 1.0 7,267 1

Sort (cost=1,735.74..1,753.91 rows=7,267 width=17) (actual time=6.432..7.839 rows=7,267 loops=1)

  • Sort Key: s_1.id
  • Sort Method: quicksort Memory: 732kB
7. 3.249 3.249 ↑ 1.0 7,267 1

Seq Scan on sensors s_1 (cost=0.00..1,269.67 rows=7,267 width=17) (actual time=0.006..3.249 rows=7,267 loops=1)

8. 0.054 0.470 ↓ 3.7 104 1

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

  • Sort Key: usp.sensor_id
  • Sort Method: quicksort Memory: 29kB
9. 0.047 0.416 ↓ 3.7 104 1

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

10. 0.057 0.057 ↓ 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.021..0.057 rows=104 loops=1)

  • Index Cond: (user_id = 25)
11. 0.312 0.312 ↑ 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.003 rows=1 loops=104)

  • Index Cond: (id = usp.permission_id)
  • Filter: ((deleted IS NULL) AND ((begins IS NULL) OR (begins <= now())) AND ((ends IS NULL) OR (ends > now())))
  • Rows Removed by Filter: 0
12.          

CTE sensor_admins

13. 4.143 77.219 ↓ 1.3 6,985 1

HashAggregate (cost=10,200.41..10,255.74 rows=5,533 width=64) (actual time=76.127..77.219 rows=6,985 loops=1)

  • Group Key: usp_1.sensor_id, uc_1.user_id, u.username, c_1.name, c_1.lastname
14. 3.081 73.076 ↓ 1.3 7,048 1

Hash Join (cost=9,324.90..10,131.24 rows=5,533 width=64) (actual time=67.756..73.076 rows=7,048 loops=1)

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

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

16. 1.285 67.718 ↓ 1.3 7,048 1

Hash (cost=9,255.74..9,255.74 rows=5,533 width=34) (actual time=67.718..67.718 rows=7,048 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 531kB
17. 3.094 66.433 ↓ 1.3 7,048 1

Hash Join (cost=8,686.23..9,255.74 rows=5,533 width=34) (actual time=61.403..66.433 rows=7,048 loops=1)

  • Hash Cond: (uc_1.user_id = u.id)
18. 1.974 1.974 ↑ 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.004..1.974 rows=25,540 loops=1)

19. 1.362 61.365 ↓ 1.3 7,048 1

Hash (cost=8,617.07..8,617.07 rows=5,533 width=34) (actual time=61.365..61.365 rows=7,048 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 531kB
20. 1.705 60.003 ↓ 1.3 7,048 1

Hash Join (cost=3,929.59..8,617.07 rows=5,533 width=34) (actual time=11.352..60.003 rows=7,048 loops=1)

  • Hash Cond: (usp_1.user_id = u.id)
21. 20.740 51.181 ↓ 1.2 7,059 1

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

  • Hash Cond: (usp_1.permission_id = p_1.id)
22. 26.375 26.375 ↓ 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..26.375 rows=237,453 loops=1)

23. 1.134 4.066 ↓ 1.0 7,212 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 318kB
24. 2.492 2.932 ↓ 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.495..2.932 rows=7,212 loops=1)

  • Recheck Cond: ((granted = 'IS_ADMIN'::metadata.enum_permissions) AND (deleted IS NULL))
  • Filter: (((begins IS NULL) OR (begins <= now())) AND ((ends IS NULL) OR (ends > now())))
  • Heap Blocks: exact=538
25. 0.440 0.440 ↑ 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.440..0.440 rows=7,212 loops=1)

  • Index Cond: (granted = 'IS_ADMIN'::metadata.enum_permissions)
26. 3.769 7.117 ↑ 1.0 23,358 1

Hash (cost=612.40..612.40 rows=23,358 width=26) (actual time=7.117..7.117 rows=23,358 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1660kB
27. 3.348 3.348 ↑ 1.0 23,358 1

Seq Scan on api_users u (cost=0.00..612.40 rows=23,358 width=26) (actual time=0.005..3.348 rows=23,358 loops=1)

  • Filter: (deleted IS NULL)
  • Rows Removed by Filter: 2182
28. 32.939 215.027 ↑ 27.7 7,267 1

GroupAggregate (cost=150,790.80..160,842.90 rows=201,042 width=272) (actual time=181.130..215.027 rows=7,267 loops=1)

  • Group Key: s.id, c.name, c.lastname, sp.permissions_list, sa.sensor_id, fn.friendly_name, mi.uuid, mi.manufacture_date, mi.hardware_type, a.id, l.specifier, mi.protocol
29. 4.770 182.088 ↑ 27.6 7,272 1

Sort (cost=150,790.80..151,293.40 rows=201,042 width=680) (actual time=181.097..182.088 rows=7,272 loops=1)

  • Sort Key: s.id, c.name, c.lastname, sp.permissions_list, sa.sensor_id, fn.friendly_name, mi.uuid, mi.manufacture_date, mi.hardware_type, a.id, l.specifier, mi.protocol
  • Sort Method: quicksort Memory: 3411kB
30. 2.050 177.318 ↑ 27.6 7,272 1

Hash Left Join (cost=6,713.33..12,135.88 rows=201,042 width=680) (actual time=168.380..177.318 rows=7,272 loops=1)

  • Hash Cond: (s.owner = uc.user_id)
31. 2.640 154.055 ↑ 27.6 7,272 1

Merge Left Join (cost=4,650.01..7,720.16 rows=201,042 width=646) (actual time=147.071..154.055 rows=7,272 loops=1)

  • Merge Cond: (sp.sensor_id = sa.sensor_id)
32. 2.071 67.892 ↑ 1.0 7,267 1

Merge Left Join (cost=4,195.37..4,231.72 rows=7,267 width=206) (actual time=64.792..67.892 rows=7,267 loops=1)

  • Merge Cond: (s.id = fn.sensor_id)
33. 4.071 65.780 ↑ 1.0 7,267 1

Sort (cost=4,191.17..4,209.34 rows=7,267 width=193) (actual time=64.749..65.780 rows=7,267 loops=1)

  • Sort Key: sp.sensor_id
  • Sort Method: quicksort Memory: 2112kB
34. 1.951 61.709 ↑ 1.0 7,267 1

Hash Left Join (cost=2,380.86..3,725.10 rows=7,267 width=193) (actual time=49.201..61.709 rows=7,267 loops=1)

  • Hash Cond: (l.address_id = a.id)
35. 6.597 57.208 ↑ 1.0 7,267 1

Hash Join (cost=2,153.63..3,397.95 rows=7,267 width=138) (actual time=46.620..57.208 rows=7,267 loops=1)

  • Hash Cond: (mi.id = s.manufacture_information)
36. 4.296 4.296 ↑ 1.0 48,411 1

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

37. 1.631 46.315 ↑ 1.0 7,267 1

Hash (cost=2,062.79..2,062.79 rows=7,267 width=105) (actual time=46.315..46.315 rows=7,267 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 651kB
38. 4.286 44.684 ↑ 1.0 7,267 1

Hash Right Join (cost=1,696.61..2,062.79 rows=7,267 width=105) (actual time=38.831..44.684 rows=7,267 loops=1)

  • Hash Cond: (l.id = s.location_id)
39. 1.621 1.621 ↑ 1.0 15,739 1

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

  • Filter: (deleted IS NULL)
40. 1.567 38.777 ↑ 1.0 7,267 1

Hash (cost=1,605.77..1,605.77 rows=7,267 width=91) (actual time=38.777..38.777 rows=7,267 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 624kB
41. 2.326 37.210 ↑ 1.0 7,267 1

Hash Join (cost=1,360.51..1,605.77 rows=7,267 width=91) (actual time=31.708..37.210 rows=7,267 loops=1)

  • Hash Cond: (sp.sensor_id = s.id)
42. 31.285 31.285 ↑ 1.0 7,267 1

CTE Scan on visible_sensors sp (cost=0.00..145.34 rows=7,267 width=36) (actual time=28.082..31.285 rows=7,267 loops=1)

43. 1.689 3.599 ↑ 1.0 7,267 1

Hash (cost=1,269.67..1,269.67 rows=7,267 width=55) (actual time=3.598..3.599 rows=7,267 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 593kB
44. 1.910 1.910 ↑ 1.0 7,267 1

Seq Scan on sensors s (cost=0.00..1,269.67 rows=7,267 width=55) (actual time=0.004..1.910 rows=7,267 loops=1)

45. 1.589 2.550 ↑ 1.0 6,677 1

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

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

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

47. 0.012 0.041 ↑ 1.0 1 1

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

  • Sort Key: fn.sensor_id
  • Sort Method: quicksort Memory: 25kB
48. 0.029 0.029 ↑ 1.0 1 1

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

  • Filter: ((deleted IS NULL) AND (user_id = 25))
  • Rows Removed by Filter: 175
49. 3.502 83.523 ↓ 1.3 6,985 1

Sort (cost=454.64..468.47 rows=5,533 width=444) (actual time=82.277..83.523 rows=6,985 loops=1)

  • Sort Key: sa.sensor_id
  • Sort Method: quicksort Memory: 1175kB
50. 80.021 80.021 ↓ 1.3 6,985 1

CTE Scan on sensor_admins sa (cost=0.00..110.66 rows=5,533 width=444) (actual time=76.131..80.021 rows=6,985 loops=1)

51. 5.165 21.213 ↑ 1.0 25,540 1

Hash (cost=1,744.06..1,744.06 rows=25,540 width=38) (actual time=21.212..21.213 rows=25,540 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1999kB
52. 7.266 16.048 ↑ 1.0 25,540 1

Hash Right Join (cost=737.65..1,744.06 rows=25,540 width=38) (actual time=6.363..16.048 rows=25,540 loops=1)

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

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

54. 3.395 6.269 ↑ 1.0 25,540 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1254kB
55. 2.874 2.874 ↑ 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.874 rows=25,540 loops=1)

Planning time : 5.939 ms
Execution time : 244.215 ms