explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Mwa3

Settings
# exclusive inclusive rows x rows loops node
1. 17.571 2,365.582 ↑ 1.0 1 1

Aggregate (cost=404,227.35..404,227.37 rows=1 width=32) (actual time=2,365.568..2,365.582 rows=1 loops=1)

  • Functions: 71
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 5.447 ms, Inlining 0.000 ms, Optimization 1.331 ms, Emission 27.680 ms, Total 34.459 ms
2. 0.007 2,347.705 ↑ 1.0 1 1

Nested Loop Left Join (cost=404,226.29..404,227.34 rows=1 width=32) (actual time=2,347.688..2,347.705 rows=1 loops=1)

3. 29.641 29.641 ↑ 1.0 1 1

Seq Scan on vendor (cost=0.00..1.01 rows=1 width=4) (actual time=29.638..29.641 rows=1 loops=1)

  • Filter: (id = 1)
  • Rows Removed by Filter: 3
4. 3.622 2,318.057 ↑ 1.0 1 1

Aggregate (cost=404,226.29..404,226.30 rows=1 width=32) (actual time=2,318.045..2,318.057 rows=1 loops=1)

5. 5.063 2,314.435 ↓ 7.1 1,443 1

Nested Loop Left Join (cost=2,044.95..404,223.74 rows=204 width=40) (actual time=4.445..2,314.435 rows=1,443 loops=1)

6. 0.601 3.458 ↓ 7.1 1,443 1

Merge Append (cost=63.91..83.38 rows=204 width=12) (actual time=0.950..3.458 rows=1,443 loops=1)

  • Sort Key: _materialized_hypertable_86."time
7. 1.416 2.686 ↓ 7.2 1,441 1

GroupAggregate (cost=62.27..69.54 rows=200 width=12) (actual time=0.809..2.686 rows=1,441 loops=1)

  • Group Key: _materialized_hypertable_86."time", _materialized_hypertable_86.vendor_id
8. 0.793 1.270 ↓ 2.1 1,441 1

Sort (cost=62.27..64.03 rows=702 width=12) (actual time=0.805..1.270 rows=1,441 loops=1)

  • Sort Key: _materialized_hypertable_86."time
  • Sort Method: quicksort Memory: 116kB
9. 0.103 0.477 ↓ 2.1 1,441 1

Custom Scan (ChunkAppend) on _materialized_hypertable_86 (cost=0.53..29.08 rows=702 width=12) (actual time=0.023..0.477 rows=1,441 loops=1)

  • Chunks excluded during startup: 0
10. 0.374 0.374 ↓ 2.1 1,441 1

Index Only Scan Backward using _hyper_86_150_chunk__materialized_hypertable_86_vendor_id_time_ on _hyper_86_150_chunk (cost=0.53..29.08 rows=702 width=12) (actual time=0.022..0.374 rows=1,441 loops=1)

  • Index Cond: ((vendor_id = vendor.id) AND ("time" < COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('86'::oid)), '-infinity'::timestamp without time zone)) AND ("time" >= '2020-09-01 04:47:15'::timestamp without time zone))
  • Heap Fetches: 1,441
11. 0.138 0.171 ↑ 2.0 2 1

Group (cost=1.63..9.75 rows=4 width=12) (actual time=0.139..0.171 rows=2 loops=1)

  • Group Key: (time_bucket('00:15:00'::interval, cost_end_time.end_time)), cost_end_time.vendor_id
12. 0.006 0.033 ↓ 6.8 27 1

Custom Scan (ConstraintAwareAppend) (cost=1.63..9.72 rows=4 width=12) (actual time=0.013..0.033 rows=27 loops=1)

  • Hypertable: cost_end_time
  • Chunks left after exclusion: 1
13. 0.003 0.027 ↓ 6.8 27 1

Merge Append (cost=1.63..9.71 rows=4 width=12) (actual time=0.012..0.027 rows=27 loops=1)

  • Sort Key: (time_bucket('00:15:00'::interval, _hyper_84_147_chunk.end_time))
14. 0.024 0.024 ↓ 13.5 27 1

Index Only Scan using "147_304_cost_end_time_pkey" on _hyper_84_147_chunk (cost=0.54..5.35 rows=2 width=12) (actual time=0.012..0.024 rows=27 loops=1)

  • Index Cond: ((vendor_id = vendor.id) AND (end_time >= COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('86'::oid)), '-infinity'::timestamp without time zone)) AND (end_time >= '2020-09-01 04:47:15'::timestamp without time zone))
  • Filter: (time_bucket('00:15:00'::interval, end_time) >= '2020-09-01 04:47:15'::timestamp without time zone)
  • Heap Fetches: 27
15. 49.062 2,304.471 ↑ 1.0 1 1,443

Aggregate (cost=1,981.04..1,981.05 rows=1 width=32) (actual time=1.597..1.597 rows=1 loops=1,443)

16. 17.316 2,255.409 ↑ 1.2 22 1,443

Nested Loop (cost=8.63..1,980.63 rows=27 width=20) (actual time=0.094..1.563 rows=22 loops=1,443)

  • Join Filter: ("*SELECT* 1".node_id = _2__be_0_node.id)
  • Rows Removed by Join Filter: 43
17. 1.443 2,238.093 ↑ 1.2 22 1,443

Append (cost=8.63..1,958.53 rows=27 width=24) (actual time=0.092..1.551 rows=22 loops=1,443)

18. 5.772 121.212 ↓ 2.0 22 1,443

Subquery Scan on *SELECT* 1 (cost=8.63..8.90 rows=11 width=24) (actual time=0.076..0.084 rows=22 loops=1,443)

19. 79.365 115.440 ↓ 2.0 22 1,443

HashAggregate (cost=8.63..8.79 rows=11 width=244) (actual time=0.075..0.080 rows=22 loops=1,443)

  • Group Key: _materialized_hypertable_12."time", _materialized_hypertable_12.vendor_id, _materialized_hypertable_12.pod_id
20. 14.430 36.075 ↓ 2.0 22 1,443

Custom Scan (ChunkAppend) on _materialized_hypertable_12 (cost=0.67..8.49 rows=11 width=131) (actual time=0.014..0.025 rows=22 loops=1,443)

  • Chunks excluded during startup: 0
  • Chunks excluded during runtime: 0
21. 21.645 21.645 ↓ 2.0 22 1,443

Index Scan using _hyper_12_9_chunk__materialized_hypertable_12_vendor_id_time_id on _hyper_12_9_chunk (cost=0.67..8.49 rows=11 width=131) (actual time=0.006..0.015 rows=22 loops=1,443)

  • Index Cond: ((vendor_id = _materialized_hypertable_86.vendor_id) AND ("time" < COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('12'::oid)), '-infinity'::timestamp without time zone)) AND ("time" = _materialized_hypertable_86."time"))
22. 1.443 2,115.438 ↓ 0.0 0 1,443

Subquery Scan on *SELECT* 2 (cost=1,948.81..1,949.49 rows=16 width=24) (actual time=1.464..1.466 rows=0 loops=1,443)

23. 2.886 2,113.995 ↓ 0.0 0 1,443

GroupAggregate (cost=1,948.81..1,949.33 rows=16 width=244) (actual time=1.464..1.465 rows=0 loops=1,443)

  • Group Key: (time_bucket('00:15:00'::interval, pod_cost.end_time)), pod_cost.vendor_id, pod_cost.pod_id
24. 18.759 2,111.109 ↑ 5.3 3 1,443

Sort (cost=1,948.81..1,948.85 rows=16 width=36) (actual time=1.463..1.463 rows=3 loops=1,443)

  • Sort Key: pod_cost.pod_id
  • Sort Method: quicksort Memory: 45kB
25. 12.987 2,092.350 ↑ 5.3 3 1,443

Custom Scan (ChunkAppend) on pod_cost (cost=0.40..1,948.49 rows=16 width=36) (actual time=1.441..1.450 rows=3 loops=1,443)

  • Chunks excluded during startup: 4
  • Chunks excluded during runtime: 0
26. 2,079.363 2,079.363 ↑ 4.0 3 1,443

Index Scan using _hyper_5_73_chunk_pod_cost_vendor_id_end_time_idx on _hyper_5_73_chunk (cost=0.67..1,934.46 rows=12 width=28) (actual time=1.432..1.441 rows=3 loops=1,443)

  • Index Cond: ((vendor_id = _materialized_hypertable_86.vendor_id) AND (end_time >= COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('12'::oid)), '-infinity'::timestamp without time zone)))
  • Filter: (_materialized_hypertable_86."time" = time_bucket('00:15:00'::interval, end_time))
  • Rows Removed by Filter: 4,881
27. 0.000 0.000 ↑ 10.0 3 31,510

Materialize (cost=0.00..10.45 rows=30 width=4) (actual time=0.000..0.000 rows=3 loops=31,510)

28. 0.008 0.008 ↑ 7.5 4 1

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

29.          

SubPlan (for Aggregate)

30. 0.000 0.000 ↑ 1.0 1 31,510

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

31.          

SubPlan (for Nested Loop Left Join)

32. 1.443 1.443 ↑ 1.0 1 1,443

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

33.          

SubPlan (for Aggregate)

34. 0.306 0.306 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.305..0.306 rows=1 loops=1)

Planning time : 5.202 ms
Execution time : 2,372.670 ms