explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fiuz

Settings
# exclusive inclusive rows x rows loops node
1. 289.042 34,363.501 ↑ 1.0 1 1

Aggregate (cost=2,074,427.30..2,074,427.31 rows=1 width=32) (actual time=34,363.485..34,363.501 rows=1 loops=1)

  • Functions: 73
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 5.813 ms, Inlining 17.456 ms, Optimization 242.080 ms, Emission 208.715 ms, Total 474.064 ms
2. 0.008 34,053.133 ↑ 1.0 1 1

Nested Loop Left Join (cost=2,074,426.24..2,074,427.28 rows=1 width=32) (actual time=34,053.114..34,053.133 rows=1 loops=1)

3. 468.813 468.813 ↑ 1.0 1 1

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

  • Filter: (id = 1)
  • Rows Removed by Filter: 3
4. 122.851 33,584.312 ↑ 1.0 1 1

Aggregate (cost=2,074,426.24..2,074,426.25 rows=1 width=32) (actual time=33,584.299..33,584.312 rows=1 loops=1)

5. 55.753 33,461.461 ↓ 19.9 15,594 1

Nested Loop Left Join (cost=3,468.80..2,074,416.44 rows=784 width=40) (actual time=11.900..33,461.461 rows=15,594 loops=1)

6. 6.971 34.548 ↓ 19.9 15,594 1

Merge Append (cost=824.07..910.11 rows=784 width=12) (actual time=7.120..34.548 rows=15,594 loops=1)

  • Sort Key: _materialized_hypertable_85."time
7. 15.255 27.367 ↓ 19.9 15,593 1

GroupAggregate (cost=822.98..889.46 rows=782 width=12) (actual time=6.913..27.367 rows=15,593 loops=1)

  • Group Key: _materialized_hypertable_85."time", _materialized_hypertable_85.vendor_id
8. 7.082 12.112 ↓ 2.0 15,593 1

Sort (cost=822.98..842.53 rows=7,822 width=12) (actual time=6.905..12.112 rows=15,593 loops=1)

  • Sort Key: _materialized_hypertable_85."time
  • Sort Method: quicksort Memory: 1,115kB
9. 1.108 5.030 ↓ 2.0 15,593 1

Custom Scan (ChunkAppend) on _materialized_hypertable_85 (cost=0.54..317.15 rows=7,822 width=12) (actual time=0.016..5.030 rows=15,593 loops=1)

  • Chunks excluded during startup: 0
10. 3.922 3.922 ↓ 2.0 15,593 1

Index Only Scan Backward using _hyper_85_152_chunk__materialized_hypertable_85_vendor_id_time_ on _hyper_85_152_chunk (cost=0.54..317.15 rows=7,822 width=12) (actual time=0.015..3.922 rows=15,593 loops=1)

  • Index Cond: ((vendor_id = vendor.id) AND ("time" < COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('85'::oid)), '-infinity'::timestamp without time zone)) AND ("time" >= '2020-10-01 04:47:15'::timestamp without time zone))
  • Heap Fetches: 15,593
11. 0.190 0.210 ↑ 2.0 1 1

Group (cost=1.08..4.96 rows=2 width=12) (actual time=0.204..0.210 rows=1 loops=1)

  • Group Key: (time_bucket('00:01:00'::interval, cost_end_time.end_time)), cost_end_time.vendor_id
12. 0.004 0.020 ↑ 2.0 1 1

Custom Scan (ConstraintAwareAppend) (cost=1.08..4.94 rows=2 width=12) (actual time=0.014..0.020 rows=1 loops=1)

  • Hypertable: cost_end_time
  • Chunks left after exclusion: 1
13. 0.002 0.016 ↑ 2.0 1 1

Merge Append (cost=1.08..4.94 rows=2 width=12) (actual time=0.012..0.016 rows=1 loops=1)

  • Sort Key: (time_bucket('00:01:00'::interval, _hyper_84_147_chunk.end_time))
14. 0.014 0.014 ↑ 1.0 1 1

Index Scan Backward using _hyper_84_147_chunk_cost_end_time_end_time_idx on _hyper_84_147_chunk (cost=0.54..2.76 rows=1 width=12) (actual time=0.011..0.014 rows=1 loops=1)

  • Index Cond: ((end_time >= COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('85'::oid)), '-infinity'::timestamp without time zone)) AND (end_time >= '2020-10-01 04:47:15'::timestamp without time zone))
  • Filter: ((vendor.id = vendor_id) AND (time_bucket('00:01:00'::interval, end_time) >= '2020-10-01 04:47:15'::timestamp without time zone))
15. 530.196 33,355.566 ↑ 1.0 1 15,594

Aggregate (cost=2,644.74..2,644.75 rows=1 width=32) (actual time=2.138..2.139 rows=1 loops=15,594)

16. 171.534 32,825.370 ↑ 1.4 22 15,594

Nested Loop (cost=9.25..2,644.28 rows=30 width=20) (actual time=0.118..2.105 rows=22 loops=15,594)

  • Join Filter: ("*SELECT* 1".node_id = _2__be_0_node.id)
  • Rows Removed by Join Filter: 54
17. 46.782 32,653.836 ↑ 1.5 22 15,594

Append (cost=9.25..2,619.03 rows=34 width=24) (actual time=0.117..2.094 rows=22 loops=15,594)

18. 46.782 1,372.272 ↓ 2.0 22 15,594

Subquery Scan on *SELECT* 1 (cost=9.25..9.53 rows=11 width=24) (actual time=0.081..0.088 rows=22 loops=15,594)

19. 904.452 1,325.490 ↓ 2.0 22 15,594

HashAggregate (cost=9.25..9.42 rows=11 width=244) (actual time=0.080..0.085 rows=22 loops=15,594)

  • Group Key: _materialized_hypertable_11."time", _materialized_hypertable_11.vendor_id, _materialized_hypertable_11.pod_id
20. 155.940 421.038 ↓ 2.0 22 15,594

Custom Scan (ChunkAppend) on _materialized_hypertable_11 (cost=0.68..9.11 rows=11 width=131) (actual time=0.015..0.027 rows=22 loops=15,594)

  • Chunks excluded during startup: 0
  • Chunks excluded during runtime: 0
21. 265.098 265.098 ↓ 2.0 22 15,594

Index Scan using _hyper_11_8_chunk__materialized_hypertable_11_vendor_id_time_id on _hyper_11_8_chunk (cost=0.68..9.11 rows=11 width=131) (actual time=0.006..0.017 rows=22 loops=15,594)

  • Index Cond: ((vendor_id = _materialized_hypertable_85.vendor_id) AND ("time" < COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('11'::oid)), '-infinity'::timestamp without time zone)) AND ("time" = _materialized_hypertable_85."time"))
22. 0.000 31,234.782 ↓ 0.0 0 15,594

Subquery Scan on *SELECT* 2 (cost=2,608.36..2,609.33 rows=23 width=24) (actual time=2.003..2.003 rows=0 loops=15,594)

23. 15.594 31,234.782 ↓ 0.0 0 15,594

GroupAggregate (cost=2,608.36..2,609.10 rows=23 width=244) (actual time=2.003..2.003 rows=0 loops=15,594)

  • Group Key: (time_bucket('00:01:00'::interval, pod_cost.end_time)), pod_cost.vendor_id, pod_cost.pod_id
24. 202.722 31,219.188 ↓ 0.0 0 15,594

Sort (cost=2,608.36..2,608.41 rows=23 width=36) (actual time=2.002..2.002 rows=0 loops=15,594)

  • Sort Key: pod_cost.pod_id
  • Sort Method: quicksort Memory: 26kB
25. 140.346 31,016.466 ↓ 0.0 0 15,594

Custom Scan (ChunkAppend) on pod_cost (cost=0.40..2,607.84 rows=23 width=36) (actual time=1.976..1.989 rows=0 loops=15,594)

  • Chunks excluded during startup: 4
  • Chunks excluded during runtime: 0
26. 30,876.120 30,876.120 ↓ 0.0 0 15,594

Index Scan using _hyper_5_73_chunk_pod_cost_vendor_id_end_time_idx on _hyper_5_73_chunk (cost=0.68..2,593.79 rows=19 width=28) (actual time=1.967..1.980 rows=0 loops=15,594)

  • Index Cond: ((vendor_id = _materialized_hypertable_85.vendor_id) AND (end_time >= COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark('11'::oid)), '-infinity'::timestamp without time zone)))
  • Filter: (_materialized_hypertable_85."time" = time_bucket('00:01:00'::interval, end_time))
  • Rows Removed by Filter: 4,774
27. 0.000 0.000 ↑ 10.0 3 343,153

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

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 343,153

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

31.          

SubPlan (for Nested Loop Left Join)

32. 15.594 15.594 ↑ 1.0 1 15,594

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

33.          

SubPlan (for Aggregate)

34. 21.326 21.326 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=21.324..21.326 rows=1 loops=1)

Planning time : 5.087 ms
Execution time : 34,380.905 ms