explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WKpA

Settings
# exclusive inclusive rows x rows loops node
1. 16.577 1,938.568 ↑ 1.0 1 1

Aggregate (cost=400,527.58..400,527.59 rows=1 width=32) (actual time=1,938.557..1,938.568 rows=1 loops=1)

  • Functions: 71
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 5.637 ms, Inlining 0.000 ms, Optimization 1.336 ms, Emission 28.101 ms, Total 35.074 ms
2. 0.007 1,921.758 ↑ 1.0 1 1

Nested Loop Left Join (cost=400,526.52..400,527.56 rows=1 width=32) (actual time=1,921.745..1,921.758 rows=1 loops=1)

3. 30.043 30.043 ↑ 1.0 1 1

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

  • Filter: (id = 1)
  • Rows Removed by Filter: 3
4. 5.706 1,891.708 ↑ 1.0 1 1

Aggregate (cost=400,526.52..400,526.53 rows=1 width=32) (actual time=1,891.698..1,891.708 rows=1 loops=1)

5. 3.638 1,886.002 ↓ 5.2 1,056 1

Nested Loop Left Join (cost=2,019.72..400,523.98 rows=203 width=40) (actual time=4.921..1,886.002 rows=1,056 loops=1)

6. 0.464 2.684 ↓ 5.2 1,056 1

Merge Append (cost=47.05..63.42 rows=203 width=12) (actual time=0.825..2.684 rows=1,056 loops=1)

  • Sort Key: _materialized_hypertable_86."time
7. 1.032 2.049 ↓ 5.3 1,054 1

GroupAggregate (cost=45.96..51.78 rows=200 width=12) (actual time=0.684..2.049 rows=1,054 loops=1)

  • Group Key: _materialized_hypertable_86."time", _materialized_hypertable_86.vendor_id
8. 0.635 1.017 ↓ 2.1 1,054 1

Sort (cost=45.96..47.24 rows=509 width=12) (actual time=0.680..1.017 rows=1,054 loops=1)

  • Sort Key: _materialized_hypertable_86."time
  • Sort Method: quicksort Memory: 98kB
9. 0.076 0.382 ↓ 2.1 1,054 1

Custom Scan (ChunkAppend) on _materialized_hypertable_86 (cost=0.53..23.08 rows=509 width=12) (actual time=0.029..0.382 rows=1,054 loops=1)

  • Chunks excluded during startup: 0
10. 0.306 0.306 ↓ 2.1 1,054 1

Index Only Scan Backward using _hyper_86_150_chunk__materialized_hypertable_86_vendor_id_time_ on _hyper_86_150_chunk (cost=0.53..23.08 rows=509 width=12) (actual time=0.028..0.306 rows=1,054 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-10-01 04:47:15'::timestamp without time zone))
  • Heap Fetches: 1,054
11. 0.138 0.171 ↑ 1.5 2 1

Group (cost=1.08..7.56 rows=3 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.005 0.033 ↓ 8.7 26 1

Custom Scan (ConstraintAwareAppend) (cost=1.08..7.54 rows=3 width=12) (actual time=0.014..0.033 rows=26 loops=1)

  • Hypertable: cost_end_time
  • Chunks left after exclusion: 1
13. 0.003 0.028 ↓ 8.7 26 1

Merge Append (cost=1.08..7.53 rows=3 width=12) (actual time=0.013..0.028 rows=26 loops=1)

  • Sort Key: (time_bucket('00:15:00'::interval, _hyper_84_147_chunk.end_time))
14. 0.025 0.025 ↓ 13.0 26 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.013..0.025 rows=26 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-10-01 04:47:15'::timestamp without time zone))
  • Filter: (time_bucket('00:15:00'::interval, end_time) >= '2020-10-01 04:47:15'::timestamp without time zone)
  • Heap Fetches: 26
15. 35.904 1,878.624 ↑ 1.0 1 1,056

Aggregate (cost=1,972.67..1,972.68 rows=1 width=32) (actual time=1.779..1.779 rows=1 loops=1,056)

16. 13.728 1,842.720 ↑ 1.2 22 1,056

Nested Loop (cost=8.63..1,972.26 rows=27 width=20) (actual time=0.106..1.745 rows=22 loops=1,056)

  • Join Filter: ("*SELECT* 1".node_id = _2__be_0_node.id)
  • Rows Removed by Join Filter: 55
17. 3.168 1,828.992 ↑ 1.2 22 1,056

Append (cost=8.63..1,950.16 rows=27 width=24) (actual time=0.105..1.732 rows=22 loops=1,056)

18. 3.168 91.872 ↓ 2.0 22 1,056

Subquery Scan on *SELECT* 1 (cost=8.63..8.90 rows=11 width=24) (actual time=0.079..0.087 rows=22 loops=1,056)

19. 59.136 88.704 ↓ 2.0 22 1,056

HashAggregate (cost=8.63..8.79 rows=11 width=244) (actual time=0.078..0.084 rows=22 loops=1,056)

  • Group Key: _materialized_hypertable_12."time", _materialized_hypertable_12.vendor_id, _materialized_hypertable_12.pod_id
20. 10.560 29.568 ↓ 2.0 22 1,056

Custom Scan (ChunkAppend) on _materialized_hypertable_12 (cost=0.67..8.49 rows=11 width=131) (actual time=0.015..0.028 rows=22 loops=1,056)

  • Chunks excluded during startup: 0
  • Chunks excluded during runtime: 0
21. 19.008 19.008 ↓ 2.0 22 1,056

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.018 rows=22 loops=1,056)

  • 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. 0.000 1,733.952 ↓ 0.0 0 1,056

Subquery Scan on *SELECT* 2 (cost=1,940.44..1,941.12 rows=16 width=24) (actual time=1.641..1.642 rows=0 loops=1,056)

23. 3.168 1,733.952 ↓ 0.0 0 1,056

GroupAggregate (cost=1,940.44..1,940.96 rows=16 width=244) (actual time=1.640..1.642 rows=0 loops=1,056)

  • Group Key: (time_bucket('00:15:00'::interval, pod_cost.end_time)), pod_cost.vendor_id, pod_cost.pod_id
24. 13.728 1,730.784 ↑ 3.2 5 1,056

Sort (cost=1,940.44..1,940.48 rows=16 width=36) (actual time=1.639..1.639 rows=5 loops=1,056)

  • Sort Key: pod_cost.pod_id
  • Sort Method: quicksort Memory: 43kB
25. 9.504 1,717.056 ↑ 3.2 5 1,056

Custom Scan (ChunkAppend) on pod_cost (cost=0.40..1,940.12 rows=16 width=36) (actual time=1.613..1.626 rows=5 loops=1,056)

  • Chunks excluded during startup: 4
  • Chunks excluded during runtime: 0
26. 1,707.552 1,707.552 ↑ 2.4 5 1,056

Index Scan using _hyper_5_73_chunk_pod_cost_vendor_id_end_time_idx on _hyper_5_73_chunk (cost=0.67..1,926.09 rows=12 width=28) (actual time=1.605..1.617 rows=5 loops=1,056)

  • 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,857
27. 0.000 0.000 ↑ 10.0 3 23,340

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

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.007..0.008 rows=4 loops=1)

29.          

SubPlan (for Aggregate)

30. 0.000 0.000 ↑ 1.0 1 23,340

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

31.          

SubPlan (for Nested Loop Left Join)

32. 1.056 1.056 ↑ 1.0 1 1,056

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

33.          

SubPlan (for Aggregate)

34. 0.233 0.233 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.232..0.233 rows=1 loops=1)

Planning time : 7.375 ms
Execution time : 1,945.995 ms