explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KHyn

Settings
# exclusive inclusive rows x rows loops node
1. 0.240 78.631 ↑ 1.3 441 1

Hash Join (cost=9,580.39..9,648.63 rows=590 width=252) (actual time=78.232..78.631 rows=441 loops=1)

  • Hash Cond: (this_.id = device_feed_filter_view.device_feed_id)
2. 0.183 0.183 ↑ 1.0 1,132 1

Seq Scan on device_feed this_ (cost=0.00..61.81 rows=1,181 width=252) (actual time=0.007..0.183 rows=1,132 loops=1)

3. 0.075 78.208 ↓ 2.2 441 1

Hash (cost=9,577.89..9,577.89 rows=200 width=4) (actual time=78.208..78.208 rows=441 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
4. 0.214 78.133 ↓ 2.2 441 1

HashAggregate (cost=9,575.89..9,577.89 rows=200 width=4) (actual time=78.072..78.133 rows=441 loops=1)

  • Group Key: device_feed_filter_view.device_feed_id
5. 1.708 77.919 ↑ 1.8 720 1

Subquery Scan on device_feed_filter_view (cost=9,470.95..9,572.71 rows=1,272 width=4) (actual time=76.920..77.919 rows=720 loops=1)

  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 654
6. 0.470 13.586 ↑ 1.9 1,374 1

Unique (cost=743.22..813.18 rows=2,544 width=124) (actual time=12.969..13.586 rows=1,374 loops=1)

7. 0.635 13.116 ↑ 1.9 1,374 1

Sort (cost=743.22..749.58 rows=2,544 width=124) (actual time=12.969..13.116 rows=1,374 loops=1)

  • Sort Key: df.id, df.device_id, df.source_id, df.feed_type, df.archive_path, d.station_id, (2), (st_setsrid(st_makepoint((s.lon)::double precision, (s.lat)::double precision), 4326)), sa.agent_id, p.parameter_group_id
  • Sort Method: quicksort Memory: 257kB
8. 0.119 12.481 ↑ 1.9 1,374 1

Append (cost=510.29..599.32 rows=2,544 width=124) (actual time=11.038..12.481 rows=1,374 loops=1)

9. 1.094 12.354 ↑ 1.9 1,374 1

Unique (cost=510.29..573.86 rows=2,543 width=153) (actual time=11.038..12.354 rows=1,374 loops=1)

10. 4.591 11.260 ↑ 1.1 2,318 1

Sort (cost=510.29..516.65 rows=2,543 width=153) (actual time=11.038..11.260 rows=2,318 loops=1)

  • Sort Key: df.id, df.device_id, df.source_id, df.feed_type, df.archive_path, d.station_id, (st_setsrid(st_makepoint((s.lon)::double precision, (s.lat)::double precision), 4326)), sa.agent_id, p.parameter_group_id
  • Sort Method: quicksort Memory: 442kB
11. 3.624 6.669 ↑ 1.1 2,318 1

Hash Left Join (cost=226.06..366.45 rows=2,543 width=153) (actual time=1.728..6.669 rows=2,318 loops=1)

  • Hash Cond: (d.station_id = sa.station_id)
12. 0.323 2.576 ↑ 1.1 1,082 1

Hash Left Join (cost=163.92..242.08 rows=1,137 width=127) (actual time=1.220..2.576 rows=1,082 loops=1)

  • Hash Cond: (d.sensor_parameter_id = sp.id)
13. 0.362 1.977 ↑ 1.1 1,082 1

Hash Join (cost=136.94..199.47 rows=1,137 width=127) (actual time=0.925..1.977 rows=1,082 loops=1)

  • Hash Cond: (d.station_id = s.id)
14. 0.444 1.263 ↑ 1.0 1,096 1

Hash Join (cost=76.57..123.52 rows=1,142 width=113) (actual time=0.564..1.263 rows=1,096 loops=1)

  • Hash Cond: (d.id = df.device_id)
15. 0.284 0.284 ↑ 1.3 1,097 1

Seq Scan on device d (cost=0.00..30.33 rows=1,386 width=12) (actual time=0.007..0.284 rows=1,097 loops=1)

  • Filter: visible
  • Rows Removed by Filter: 36
16. 0.231 0.535 ↑ 1.0 1,132 1

Hash (cost=61.81..61.81 rows=1,181 width=105) (actual time=0.535..0.535 rows=1,132 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
17. 0.304 0.304 ↑ 1.0 1,132 1

Seq Scan on device_feed df (cost=0.00..61.81 rows=1,181 width=105) (actual time=0.002..0.304 rows=1,132 loops=1)

18. 0.137 0.352 ↓ 1.0 655 1

Hash (cost=52.40..52.40 rows=637 width=18) (actual time=0.352..0.352 rows=655 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
19. 0.215 0.215 ↓ 1.0 655 1

Seq Scan on station s (cost=0.00..52.40 rows=637 width=18) (actual time=0.006..0.215 rows=655 loops=1)

  • Filter: visible
  • Rows Removed by Filter: 5
20. 0.027 0.276 ↑ 1.4 277 1

Hash (cost=22.31..22.31 rows=374 width=8) (actual time=0.276..0.276 rows=277 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
21. 0.104 0.249 ↑ 1.4 277 1

Hash Left Join (cost=10.80..22.31 rows=374 width=8) (actual time=0.125..0.249 rows=277 loops=1)

  • Hash Cond: (sp.parameter_id = p.id)
22. 0.042 0.042 ↑ 1.4 277 1

Seq Scan on sensor_parameter sp (cost=0.00..6.74 rows=374 width=8) (actual time=0.010..0.042 rows=277 loops=1)

23. 0.035 0.103 ↑ 1.2 219 1

Hash (cost=7.58..7.58 rows=258 width=8) (actual time=0.103..0.103 rows=219 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
24. 0.068 0.068 ↑ 1.2 219 1

Seq Scan on parameter p (cost=0.00..7.58 rows=258 width=8) (actual time=0.007..0.068 rows=219 loops=1)

25. 0.210 0.469 ↑ 1.1 1,344 1

Hash (cost=43.73..43.73 rows=1,473 width=8) (actual time=0.469..0.469 rows=1,344 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 69kB
26. 0.259 0.259 ↑ 1.1 1,344 1

Seq Scan on station_agent sa (cost=0.00..43.73 rows=1,473 width=8) (actual time=0.006..0.259 rows=1,344 loops=1)

27. 0.000 0.008 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.01..0.03 rows=1 width=124) (actual time=0.008..0.008 rows=0 loops=1)

28. 0.000 0.008 ↓ 0.0 0 1

Unique (cost=0.01..0.02 rows=1 width=124) (actual time=0.008..0.008 rows=0 loops=1)

29. 0.008 0.008 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=0 width=124) (actual time=0.008..0.008 rows=0 loops=1)

  • Sort Key: id, source_id, station_id, (st_setsrid(st_makepoint((lon)::double precision, (lat)::double precision), 4326)), agent_id, parameter_group_id
  • Sort Method: quicksort Memory: 25kB
30. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=124) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
31.          

SubPlan (forSubquery Scan)

32. 0.781 62.625 ↑ 2.3 5,405 1

Subquery Scan on device_feed_filter_view_1 (cost=8,221.33..8,696.08 rows=12,660 width=4) (actual time=59.252..62.625 rows=5,405 loops=1)

33. 2.087 61.844 ↑ 2.3 5,405 1

Unique (cost=8,221.33..8,569.48 rows=12,660 width=124) (actual time=59.251..61.844 rows=5,405 loops=1)

34. 2.655 59.757 ↑ 2.3 5,405 1

Sort (cost=8,221.33..8,252.98 rows=12,660 width=124) (actual time=59.251..59.757 rows=5,405 loops=1)

  • Sort Key: df_1.id, df_1.device_id, df_1.source_id, df_1.feed_type, df_1.archive_path, d_1.station_id, (2), (st_setsrid(st_makepoint((s_1.lon)::double precision, (s_1.lat)::double precision), 4326)), sa_1.agent_id, p_1.parameter_group_id
  • Sort Method: quicksort Memory: 954kB
35. 0.376 57.102 ↑ 2.3 5,405 1

Append (cost=479.33..7,358.67 rows=12,660 width=124) (actual time=6.279..57.102 rows=5,405 loops=1)

36. 0.648 7.057 ↑ 3.5 641 1

Unique (cost=479.33..530.39 rows=2,269 width=153) (actual time=6.279..7.057 rows=641 loops=1)

37. 1.426 6.409 ↑ 1.8 1,269 1

Sort (cost=479.33..485.01 rows=2,269 width=153) (actual time=6.279..6.409 rows=1,269 loops=1)

  • Sort Key: df_1.id, df_1.device_id, df_1.source_id, df_1.feed_type, df_1.archive_path, d_1.station_id, (st_setsrid(st_makepoint((s_1.lon)::double precision, (s_1.lat)::double precision), 4326)), p_1.parameter_group_id
  • Sort Method: quicksort Memory: 229kB
38. 2.132 4.983 ↑ 1.8 1,269 1

Hash Join (cost=256.29..352.86 rows=2,269 width=153) (actual time=2.547..4.983 rows=1,269 loops=1)

  • Hash Cond: (sa_1.station_id = s_1.id)
39. 0.340 0.340 ↑ 1.1 1,135 1

Seq Scan on station_agent sa_1 (cost=0.00..47.41 rows=1,277 width=8) (actual time=0.010..0.340 rows=1,135 loops=1)

  • Filter: (agent_id = 9)
  • Rows Removed by Filter: 209
40. 0.321 2.511 ↑ 1.1 1,082 1

Hash (cost=242.08..242.08 rows=1,137 width=131) (actual time=2.511..2.511 rows=1,082 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 120kB
41. 0.329 2.190 ↑ 1.1 1,082 1

Hash Left Join (cost=163.92..242.08 rows=1,137 width=131) (actual time=1.060..2.190 rows=1,082 loops=1)

  • Hash Cond: (d_1.sensor_parameter_id = sp_1.id)
42. 0.320 1.602 ↑ 1.1 1,082 1

Hash Join (cost=136.94..199.47 rows=1,137 width=131) (actual time=0.789..1.602 rows=1,082 loops=1)

  • Hash Cond: (d_1.station_id = s_1.id)
43. 0.342 0.983 ↑ 1.0 1,096 1

Hash Join (cost=76.57..123.52 rows=1,142 width=113) (actual time=0.480..0.983 rows=1,096 loops=1)

  • Hash Cond: (d_1.id = df_1.device_id)
44. 0.188 0.188 ↑ 1.3 1,097 1

Seq Scan on device d_1 (cost=0.00..30.33 rows=1,386 width=12) (actual time=0.007..0.188 rows=1,097 loops=1)

  • Filter: visible
  • Rows Removed by Filter: 36
45. 0.234 0.453 ↑ 1.0 1,132 1

Hash (cost=61.81..61.81 rows=1,181 width=105) (actual time=0.453..0.453 rows=1,132 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
46. 0.219 0.219 ↑ 1.0 1,132 1

Seq Scan on device_feed df_1 (cost=0.00..61.81 rows=1,181 width=105) (actual time=0.005..0.219 rows=1,132 loops=1)

47. 0.127 0.299 ↓ 1.0 655 1

Hash (cost=52.40..52.40 rows=637 width=18) (actual time=0.299..0.299 rows=655 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
48. 0.172 0.172 ↓ 1.0 655 1

Seq Scan on station s_1 (cost=0.00..52.40 rows=637 width=18) (actual time=0.002..0.172 rows=655 loops=1)

  • Filter: visible
  • Rows Removed by Filter: 5
49. 0.040 0.259 ↑ 1.4 277 1

Hash (cost=22.31..22.31 rows=374 width=8) (actual time=0.259..0.259 rows=277 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
50. 0.100 0.219 ↑ 1.4 277 1

Hash Left Join (cost=10.80..22.31 rows=374 width=8) (actual time=0.108..0.219 rows=277 loops=1)

  • Hash Cond: (sp_1.parameter_id = p_1.id)
51. 0.035 0.035 ↑ 1.4 277 1

Seq Scan on sensor_parameter sp_1 (cost=0.00..6.74 rows=374 width=8) (actual time=0.006..0.035 rows=277 loops=1)

52. 0.040 0.084 ↑ 1.2 219 1

Hash (cost=7.58..7.58 rows=258 width=8) (actual time=0.084..0.084 rows=219 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
53. 0.044 0.044 ↑ 1.2 219 1

Seq Scan on parameter p_1 (cost=0.00..7.58 rows=258 width=8) (actual time=0.003..0.044 rows=219 loops=1)

54. 1.188 49.669 ↑ 2.2 4,764 1

Subquery Scan on *SELECT* 2_1 (cost=6,545.82..6,805.60 rows=10,391 width=124) (actual time=46.543..49.669 rows=4,764 loops=1)

55. 1.468 48.481 ↑ 2.2 4,764 1

Unique (cost=6,545.82..6,701.69 rows=10,391 width=124) (actual time=46.541..48.481 rows=4,764 loops=1)

56. 3.698 47.013 ↑ 2.2 4,764 1

Sort (cost=6,545.82..6,571.80 rows=10,391 width=124) (actual time=46.540..47.013 rows=4,764 loops=1)

  • Sort Key: d_2.id, s_2.source_id, d_2.station_id, (st_setsrid(st_makepoint((s_2.lon)::double precision, (s_2.lat)::double precision), 4326)), p_2.parameter_group_id
  • Sort Method: quicksort Memory: 862kB
57. 9.540 43.315 ↑ 2.2 4,764 1

Hash Left Join (cost=2,105.78..5,852.59 rows=10,391 width=124) (actual time=20.793..43.315 rows=4,764 loops=1)

  • Hash Cond: (d_2.enhanced_parameter_id = sp_2.id)
58. 2.236 33.509 ↑ 2.2 4,764 1

Nested Loop (cost=2,078.79..5,578.82 rows=10,391 width=38) (actual time=20.494..33.509 rows=4,764 loops=1)

59. 1.718 22.672 ↑ 1.1 2,867 1

Hash Join (cost=2,078.37..2,833.26 rows=3,285 width=34) (actual time=20.474..22.672 rows=2,867 loops=1)

  • Hash Cond: (sa_2.station_id = s_2.id)
60. 0.733 0.998 ↓ 1.0 3,344 1

Bitmap Heap Scan on station_agent_legacy sa_2 (cost=66.06..775.62 rows=3,325 width=8) (actual time=0.279..0.998 rows=3,344 loops=1)

  • Recheck Cond: (agent_id = 9)
  • Heap Blocks: exact=101
61. 0.265 0.265 ↓ 1.0 3,344 1

Bitmap Index Scan on station_agent_legacy_agent_id_idx (cost=0.00..65.23 rows=3,325 width=0) (actual time=0.265..0.265 rows=3,344 loops=1)

  • Index Cond: (agent_id = 9)
62. 8.667 19.956 ↑ 1.1 39,559 1

Hash (cost=1,480.59..1,480.59 rows=42,538 width=26) (actual time=19.956..19.956 rows=39,559 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2846kB
63. 11.289 11.289 ↑ 1.1 39,559 1

Seq Scan on station s_2 (cost=0.00..1,480.59 rows=42,538 width=26) (actual time=0.006..11.289 rows=39,559 loops=1)

  • Filter: isactive
  • Rows Removed by Filter: 959
64. 8.601 8.601 ↑ 3.0 2 2,867

Index Scan using station_device_station on station_device d_2 (cost=0.42..0.78 rows=6 width=12) (actual time=0.003..0.003 rows=2 loops=2,867)

  • Index Cond: (station_id = s_2.id)
  • Filter: manual_status
65. 0.045 0.266 ↑ 1.4 277 1

Hash (cost=22.31..22.31 rows=374 width=8) (actual time=0.266..0.266 rows=277 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
66. 0.093 0.221 ↑ 1.4 277 1

Hash Left Join (cost=10.80..22.31 rows=374 width=8) (actual time=0.117..0.221 rows=277 loops=1)

  • Hash Cond: (sp_2.parameter_id = p_2.id)
67. 0.036 0.036 ↑ 1.4 277 1

Seq Scan on sensor_parameter sp_2 (cost=0.00..6.74 rows=374 width=8) (actual time=0.009..0.036 rows=277 loops=1)

68. 0.034 0.092 ↑ 1.2 219 1

Hash (cost=7.58..7.58 rows=258 width=8) (actual time=0.092..0.092 rows=219 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
69. 0.058 0.058 ↑ 1.2 219 1

Seq Scan on parameter p_2 (cost=0.00..7.58 rows=258 width=8) (actual time=0.006..0.058 rows=219 loops=1)

Planning time : 5.909 ms
Execution time : 79.062 ms