explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9mwt

Settings
# exclusive inclusive rows x rows loops node
1. 6.618 1,962.939 ↑ 1.0 1 1

Aggregate (cost=1,770,201.04..1,770,201.05 rows=1 width=32) (actual time=1,962.921..1,962.939 rows=1 loops=1)

  • Functions: 172
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 13.899 ms, Inlining 61.294 ms, Optimization 812.545 ms, Emission 769.571 ms, Total 1657.309 ms
2. 0.003 1,955.827 ↑ 1.0 1 1

Nested Loop Left Join (cost=1,770,199.95..1,770,201.02 rows=1 width=68) (actual time=1,955.805..1,955.827 rows=1 loops=1)

3. 0.009 1,646.807 ↑ 1.0 1 1

Nested Loop Left Join (cost=59.66..60.71 rows=1 width=36) (actual time=1,646.799..1,646.807 rows=1 loops=1)

4. 1,645.889 1,645.889 ↑ 1.0 1 1

Seq Scan on vendor (cost=0.00..1.01 rows=1 width=4) (actual time=1,645.886..1,645.889 rows=1 loops=1)

  • Filter: (id = 1)
  • Rows Removed by Filter: 3
5. 0.044 0.909 ↑ 1.0 1 1

Aggregate (cost=59.66..59.67 rows=1 width=32) (actual time=0.906..0.909 rows=1 loops=1)

6. 0.006 0.861 ↑ 1.5 2 1

Nested Loop Left Join (cost=17.41..59.62 rows=3 width=44) (actual time=0.614..0.861 rows=2 loops=1)

7. 0.007 0.041 ↑ 1.5 2 1

Nested Loop Semi Join (cost=0.14..7.72 rows=3 width=12) (actual time=0.034..0.041 rows=2 loops=1)

8. 0.014 0.014 ↓ 1.3 4 1

Seq Scan on instance (cost=0.00..1.07 rows=3 width=12) (actual time=0.013..0.014 rows=4 loops=1)

  • Filter: (vendor.id = vendor_id)
  • Rows Removed by Filter: 4
9. 0.020 0.020 ↓ 0.0 0 4

Index Only Scan using unique_node on node _1__be_0_node (cost=0.14..2.21 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=4)

  • Index Cond: (instance_id = instance.id)
  • Heap Fetches: 2
10. 0.710 0.814 ↑ 1.0 1 2

Aggregate (cost=17.27..17.28 rows=1 width=32) (actual time=0.407..0.407 rows=1 loops=2)

11. 0.104 0.104 ↓ 1.9 186 2

Seq Scan on pod (cost=0.00..15.82 rows=96 width=45) (actual time=0.004..0.052 rows=186 loops=2)

  • Filter: (instance.id = instance_id)
  • Rows Removed by Filter: 212
12.          

SubPlan (for Aggregate)

13. 0.000 0.000 ↑ 1.0 1 373

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=373)

14.          

SubPlan (for Aggregate)

15. 0.004 0.004 ↑ 1.0 1 2

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=2)

16. 1.234 309.017 ↑ 1.0 1 1

Aggregate (cost=1,770,140.29..1,770,140.30 rows=1 width=32) (actual time=309.004..309.017 rows=1 loops=1)

17. 0.488 307.783 ↑ 3.3 71 1

Nested Loop Left Join (cost=7,665.67..1,770,137.40 rows=231 width=40) (actual time=9.277..307.783 rows=71 loops=1)

18. 0.028 1.143 ↑ 3.3 71 1

Merge Append (cost=3.00..50.15 rows=231 width=12) (actual time=0.585..1.143 rows=71 loops=1)

  • Sort Key: "*SELECT* 1"."time
19. 0.017 0.633 ↓ 2.2 69 1

Subquery Scan on *SELECT* 1 (cost=0.81..6.99 rows=31 width=12) (actual time=0.423..0.633 rows=69 loops=1)

20. 0.445 0.616 ↓ 2.2 69 1

GroupAggregate (cost=0.81..6.68 rows=31 width=12) (actual time=0.422..0.616 rows=69 loops=1)

  • Group Key: _materialized_hypertable_88."time", _materialized_hypertable_88.vendor_id
21. 0.024 0.171 ↓ 2.2 69 1

Custom Scan (ConstraintAwareAppend) (cost=0.81..6.22 rows=31 width=12) (actual time=0.039..0.171 rows=69 loops=1)

  • Hypertable: _materialized_hypertable_88
  • Chunks left after exclusion: 2
22. 0.028 0.147 ↓ 2.2 69 1

Merge Append (cost=0.81..6.22 rows=31 width=12) (actual time=0.038..0.147 rows=69 loops=1)

  • Sort Key: _hyper_88_151_chunk."time
23. 0.094 0.094 ↓ 2.2 65 1

Index Only Scan Backward using _hyper_88_151_chunk__materialized_hypertable_88_vendor_id_time_ on _hyper_88_151_chunk (cost=0.39..3.27 rows=30 width=12) (actual time=0.020..0.094 rows=65 loops=1)

  • Index Cond: ((vendor_id = vendor.id) AND ("time" < COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('88'::oid)), '-infinity'::timestamp without time zone)) AND ("time" >= '2020-09-15 11:20:45'::timestamp without time zone))
  • Heap Fetches: 65
24. 0.025 0.025 ↓ 4.0 4 1

Index Only Scan Backward using _hyper_88_192_chunk__materialized_hypertable_88_vendor_id_time_ on _hyper_88_192_chunk (cost=0.41..2.63 rows=1 width=12) (actual time=0.017..0.025 rows=4 loops=1)

  • Index Cond: ((vendor_id = vendor.id) AND ("time" < COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('88'::oid)), '-infinity'::timestamp without time zone)) AND ("time" >= '2020-09-15 11:20:45'::timestamp without time zone))
  • Heap Fetches: 4
25. 0.003 0.482 ↑ 100.0 2 1

Subquery Scan on *SELECT* 2 (cost=2.18..40.84 rows=200 width=12) (actual time=0.159..0.482 rows=2 loops=1)

26. 0.151 0.479 ↑ 100.0 2 1

Group (cost=2.18..38.84 rows=200 width=12) (actual time=0.158..0.479 rows=2 loops=1)

  • Group Key: (time_bucket('06:00:00'::interval, cost_end_time.end_time)), cost_end_time.vendor_id
27. 0.057 0.328 ↓ 3.1 684 1

Custom Scan (ConstraintAwareAppend) (cost=2.18..37.24 rows=220 width=12) (actual time=0.044..0.328 rows=684 loops=1)

  • Hypertable: cost_end_time
  • Chunks left after exclusion: 1
28. 0.051 0.271 ↓ 3.1 684 1

Merge Append (cost=2.18..36.69 rows=220 width=12) (actual time=0.043..0.271 rows=684 loops=1)

  • Sort Key: (time_bucket('06:00:00'::interval, _hyper_84_176_chunk.end_time))
29. 0.220 0.220 ↓ 3.2 684 1

Index Scan Backward using _hyper_84_176_chunk_cost_end_time_end_time_idx on _hyper_84_176_chunk (cost=0.53..26.91 rows=217 width=12) (actual time=0.042..0.220 rows=684 loops=1)

  • Index Cond: ((end_time >= COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('88'::oid)), '-infinity'::timestamp without time zone)) AND (end_time >= '2020-09-15 11:20:45'::timestamp without time zone))
  • Filter: ((vendor.id = vendor_id) AND (time_bucket('06:00:00'::interval, end_time) >= '2020-09-15 11:20:45'::timestamp without time zone))
30. 1.842 306.010 ↑ 1.0 1 71

Aggregate (cost=7,662.67..7,662.68 rows=1 width=32) (actual time=4.309..4.310 rows=1 loops=71)

31. 0.269 303.880 ↑ 5.0 2 71

Nested Loop Left Join (cost=775.12..7,662.52 rows=10 width=72) (actual time=2.049..4.280 rows=2 loops=71)

32. 0.195 21.371 ↑ 5.0 2 71

Hash Join (cost=15.93..70.31 rows=10 width=40) (actual time=0.061..0.301 rows=2 loops=71)

  • Hash Cond: ("*SELECT* 1_1".node_id = _11__be_0_node.id)
33. 0.071 21.158 ↑ 5.0 2 71

Append (cost=5.25..59.61 rows=10 width=40) (actual time=0.060..0.298 rows=2 loops=71)

34. 0.071 4.118 ↑ 1.0 2 71

Subquery Scan on *SELECT* 1_1 (cost=5.25..5.34 rows=2 width=40) (actual time=0.050..0.058 rows=2 loops=71)

35. 1.136 4.047 ↑ 1.0 2 71

GroupAggregate (cost=5.25..5.32 rows=2 width=140) (actual time=0.050..0.057 rows=2 loops=71)

  • Group Key: _materialized_hypertable_93."time", _materialized_hypertable_93.vendor_id, _materialized_hypertable_93.instance_id, _materialized_hypertable_93.node_id
36. 1.775 2.911 ↑ 1.0 2 71

Sort (cost=5.25..5.25 rows=2 width=164) (actual time=0.041..0.041 rows=2 loops=71)

  • Sort Key: _materialized_hypertable_93.instance_id, _materialized_hypertable_93.node_id
  • Sort Method: quicksort Memory: 25kB
37. 0.692 1.136 ↑ 1.0 2 71

Custom Scan (ChunkAppend) on _materialized_hypertable_93 (cost=0.40..5.24 rows=2 width=164) (actual time=0.014..0.016 rows=2 loops=71)

  • Chunks excluded during startup: 0
  • Chunks excluded during runtime: 1
38. 0.390 0.390 ↓ 2.0 2 65

Index Scan using _hyper_93_156_chunk__materialized_hypertable_93_vendor_id_time_ on _hyper_93_156_chunk (cost=0.40..2.62 rows=1 width=212) (actual time=0.004..0.006 rows=2 loops=65)

  • Index Cond: ((vendor_id = "*SELECT* 1".vendor_id) AND ("time" < COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('93'::oid)), '-infinity'::timestamp without time zone)) AND ("time" = "*SELECT* 1"."time"))
39. 0.054 0.054 ↓ 2.0 2 6

Index Scan using _hyper_93_188_chunk__materialized_hypertable_93_vendor_id_time_ on _hyper_93_188_chunk (cost=0.40..2.62 rows=1 width=116) (actual time=0.005..0.009 rows=2 loops=6)

  • Index Cond: ((vendor_id = "*SELECT* 1".vendor_id) AND ("time" < COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('93'::oid)), '-infinity'::timestamp without time zone)) AND ("time" = "*SELECT* 1"."time"))
40. 0.000 16.969 ↓ 0.0 0 71

Subquery Scan on *SELECT* 2_1 (cost=53.81..54.21 rows=8 width=40) (actual time=0.237..0.239 rows=0 loops=71)

41. 0.426 16.969 ↓ 0.0 0 71

GroupAggregate (cost=53.81..54.13 rows=8 width=140) (actual time=0.236..0.239 rows=0 loops=71)

  • Group Key: (time_bucket('06:00:00'::interval, node_cost.end_time)), node_cost.vendor_id, node_cost.instance_id, node_cost.node_id
42. 1.775 16.543 ↓ 1.1 9 71

Sort (cost=53.81..53.83 rows=8 width=52) (actual time=0.232..0.233 rows=9 loops=71)

  • Sort Key: node_cost.instance_id, node_cost.node_id
  • Sort Method: quicksort Memory: 116kB
43. 0.639 14.768 ↓ 1.1 9 71

Custom Scan (ChunkAppend) on node_cost (cost=0.40..53.69 rows=8 width=52) (actual time=0.204..0.208 rows=9 loops=71)

  • Chunks excluded during startup: 5
  • Chunks excluded during runtime: 0
44. 14.129 14.129 ↓ 3.0 9 71

Index Scan using _hyper_4_174_chunk_node_cost_end_time_idx on _hyper_4_174_chunk (cost=0.53..41.81 rows=3 width=44) (actual time=0.195..0.199 rows=9 loops=71)

  • Index Cond: (end_time >= COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('93'::oid)), '-infinity'::timestamp without time zone))
  • Filter: (("*SELECT* 1".vendor_id = vendor_id) AND ("*SELECT* 1"."time" = time_bucket('06:00:00'::interval, end_time)))
  • Rows Removed by Filter: 639
45. 0.004 0.018 ↑ 5.0 6 1

Hash (cost=10.30..10.30 rows=30 width=4) (actual time=0.017..0.018 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
46. 0.014 0.014 ↑ 5.0 6 1

Seq Scan on node _11__be_0_node (cost=0.00..10.30 rows=30 width=4) (actual time=0.008..0.014 rows=6 loops=1)

47. 7.200 282.240 ↑ 1.0 1 144

Aggregate (cost=759.19..759.20 rows=1 width=32) (actual time=1.960..1.960 rows=1 loops=144)

48. 0.144 275.040 ↑ 2.1 12 144

Append (cost=14.24..758.81 rows=25 width=80) (actual time=0.150..1.910 rows=12 loops=144)

49. 0.432 14.976 ↑ 1.0 11 144

Subquery Scan on *SELECT* 1_2 (cost=14.24..14.68 rows=11 width=80) (actual time=0.097..0.104 rows=11 loops=144)

50. 9.936 14.544 ↑ 1.0 11 144

HashAggregate (cost=14.24..14.57 rows=11 width=248) (actual time=0.095..0.101 rows=11 loops=144)

  • Group Key: _materialized_hypertable_98."time", _materialized_hypertable_98.vendor_id, _materialized_hypertable_98.instance_id, _materialized_hypertable_98.node_id, _materialized_hypertable_98.pod_id
51. 1.408 4.608 ↑ 1.0 11 144

Custom Scan (ChunkAppend) on _materialized_hypertable_98 (cost=0.53..13.88 rows=11 width=480) (actual time=0.016..0.032 rows=11 loops=144)

  • Chunks excluded during startup: 0
  • Chunks excluded during runtime: 1
52. 2.990 2.990 ↓ 3.0 12 130

Index Scan using _hyper_98_163_chunk__materialized_hypertable_98_node_id_time_id on _hyper_98_163_chunk (cost=0.53..4.97 rows=4 width=480) (actual time=0.007..0.023 rows=12 loops=130)

  • Index Cond: ((node_id = "*SELECT* 1_1".node_id) AND ("time" < COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('98'::oid)), '-infinity'::timestamp without time zone)) AND ("time" = "*SELECT* 1_1"."time"))
53. 0.210 0.210 ↓ 1.3 9 14

Index Scan using _hyper_98_190_chunk__materialized_hypertable_98_node_id_time_id on _hyper_98_190_chunk (cost=0.40..8.91 rows=7 width=480) (actual time=0.006..0.015 rows=9 loops=14)

  • Index Cond: ((node_id = "*SELECT* 1_1".node_id) AND ("time" < COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('98'::oid)), '-infinity'::timestamp without time zone)) AND ("time" = "*SELECT* 1_1"."time"))
54. 0.144 259.920 ↓ 0.0 0 144

Subquery Scan on *SELECT* 2_2 (cost=742.93..744.01 rows=14 width=80) (actual time=1.794..1.805 rows=0 loops=144)

55. 1.584 259.776 ↓ 0.0 0 144

GroupAggregate (cost=742.93..743.87 rows=14 width=248) (actual time=1.793..1.804 rows=0 loops=144)

  • Group Key: (time_bucket('06:00:00'::interval, pod_cost.end_time)), pod_cost.vendor_id, pod_cost.instance_id, pod_cost.node_id, pod_cost.pod_id
56. 4.320 258.192 ↓ 3.6 50 144

Sort (cost=742.93..742.96 rows=14 width=88) (actual time=1.791..1.793 rows=50 loops=144)

  • Sort Key: pod_cost.vendor_id, pod_cost.instance_id, pod_cost.pod_id
  • Sort Method: quicksort Memory: 598kB
57. 2.160 253.872 ↓ 3.6 50 144

Custom Scan (ChunkAppend) on pod_cost (cost=0.40..742.66 rows=14 width=88) (actual time=1.725..1.763 rows=50 loops=144)

  • Chunks excluded during startup: 5
  • Chunks excluded during runtime: 0
58. 251.712 251.712 ↓ 5.6 50 144

Index Scan using _hyper_5_175_chunk_pod_cost_end_time_idx on _hyper_5_175_chunk (cost=0.67..725.92 rows=9 width=88) (actual time=1.717..1.748 rows=50 loops=144)

  • Index Cond: (end_time >= COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('98'::oid)), '-infinity'::timestamp without time zone))
  • Filter: (("*SELECT* 1_1".node_id = node_id) AND ("*SELECT* 1_1"."time" = time_bucket('06:00:00'::interval, end_time)))
  • Rows Removed by Filter: 7,078
59.          

SubPlan (for Aggregate)

60. 0.000 0.000 ↑ 1.0 1 1,667

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1,667)

61.          

SubPlan (for Aggregate)

62. 0.288 0.288 ↑ 1.0 1 144

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=144)

63.          

SubPlan (for Nested Loop Left Join)

64. 0.142 0.142 ↑ 1.0 1 71

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=71)

65.          

SubPlan (for Aggregate)

66. 0.494 0.494 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.493..0.494 rows=1 loops=1)

Planning time : 19.868 ms
Execution time : 2,012.059 ms