explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UD2i

Settings
# exclusive inclusive rows x rows loops node
1. 1,390.863 5,904.037 ↑ 1.0 1 1

Aggregate (cost=733,407.27..733,407.28 rows=1 width=32) (actual time=5,904.025..5,904.037 rows=1 loops=1)

  • Functions: 91
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 6.168 ms, Inlining 15.686 ms, Optimization 232.283 ms, Emission 185.612 ms, Total 439.749 ms
2. 0.008 4,429.767 ↑ 1.0 1 1

Nested Loop Left Join (cost=733,406.21..733,407.25 rows=1 width=32) (actual time=4,429.748..4,429.767 rows=1 loops=1)

3. 434.417 434.417 ↑ 1.0 1 1

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

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

Aggregate (cost=733,406.21..733,406.22 rows=1 width=32) (actual time=3,995.331..3,995.342 rows=1 loops=1)

5. 24.238 3,677.889 ↓ 1.3 15,610 1

Nested Loop Left Join (cost=63.52..733,261.43 rows=11,582 width=40) (actual time=0.375..3,677.889 rows=15,610 loops=1)

6. 1.696 16.521 ↓ 1.3 15,610 1

Custom Scan (ChunkAppend) on cost_end_time (cost=0.28..370.52 rows=11,582 width=12) (actual time=0.045..16.521 rows=15,610 loops=1)

  • Order: cost_end_time.end_time
  • Chunks excluded during runtime: 0
7. 7.282 7.282 ↑ 1.0 7,552 1

Index Scan Backward using _hyper_84_149_chunk_cost_end_time_end_time_idx on _hyper_84_149_chunk (cost=0.28..231.62 rows=7,552 width=12) (actual time=0.020..7.282 rows=7,552 loops=1)

  • Index Cond: (end_time >= '2020-10-01 04:47:15'::timestamp without time zone)
  • Filter: (vendor.id = vendor_id)
8. 7.543 7.543 ↓ 2.0 8,058 1

Index Only Scan using "147_304_cost_end_time_pkey" on _hyper_84_147_chunk (cost=0.28..138.90 rows=4,030 width=12) (actual time=0.031..7.543 rows=8,058 loops=1)

  • Index Cond: ((vendor_id = vendor.id) AND (end_time >= '2020-10-01 04:47:15'::timestamp without time zone))
  • Heap Fetches: 8,058
9. 842.940 3,621.520 ↑ 1.0 1 15,610

Aggregate (cost=63.24..63.25 rows=1 width=32) (actual time=0.232..0.232 rows=1 loops=15,610)

10. 62.185 2,778.580 ↓ 11.0 22 15,610

Nested Loop Left Join (cost=0.29..63.20 rows=2 width=76) (actual time=0.076..0.178 rows=22 loops=15,610)

11. 93.660 1,685.880 ↓ 11.0 22 15,610

Nested Loop (cost=0.15..62.43 rows=2 width=44) (actual time=0.071..0.108 rows=22 loops=15,610)

12. 31.220 31.220 ↓ 4.0 4 15,610

Seq Scan on node _2__be_0_node (cost=0.00..10.34 rows=1 width=4) (actual time=0.001..0.002 rows=4 loops=15,610)

  • Filter: (provider = ANY ('{amazon}'::text[]))
  • Rows Removed by Filter: 2
13. 1,029.248 1,561.000 ↑ 4.7 6 62,440

Custom Scan (ChunkAppend) on pod_cost (cost=0.15..51.81 rows=28 width=48) (actual time=0.022..0.025 rows=6 loops=62,440)

  • Chunks excluded during runtime: 4
14. 0.000 0.000 ↓ 0.0 0

Index Scan using _hyper_5_3_chunk_pod_cost_pod_id_end_time_idx on _hyper_5_3_chunk (cost=0.15..4.46 rows=1 width=48) (never executed)

  • Index Cond: (end_time = cost_end_time.end_time)
  • Filter: ((cost_end_time.vendor_id = vendor_id) AND (_2__be_0_node.id = node_id))
15. 0.000 0.000 ↓ 0.0 0

Index Scan using _hyper_5_16_chunk_pod_cost_pod_id_end_time_idx on _hyper_5_16_chunk (cost=0.15..4.46 rows=1 width=48) (never executed)

  • Index Cond: (end_time = cost_end_time.end_time)
  • Filter: ((cost_end_time.vendor_id = vendor_id) AND (_2__be_0_node.id = node_id))
16. 0.000 0.000 ↓ 0.0 0

Index Scan using _hyper_5_20_chunk_pod_cost_vendor_id_end_time_idx on _hyper_5_20_chunk (cost=0.42..13.28 rows=11 width=48) (never executed)

  • Index Cond: ((vendor_id = cost_end_time.vendor_id) AND (end_time = cost_end_time.end_time))
  • Filter: (_2__be_0_node.id = node_id)
17. 241.664 241.664 ↑ 2.2 5 30,208

Index Scan using _hyper_5_38_chunk_pod_cost_vendor_id_end_time_idx on _hyper_5_38_chunk (cost=0.42..16.01 rows=11 width=48) (actual time=0.005..0.008 rows=5 loops=30,208)

  • Index Cond: ((vendor_id = cost_end_time.vendor_id) AND (end_time = cost_end_time.end_time))
  • Filter: (_2__be_0_node.id = node_id)
  • Rows Removed by Filter: 16
18. 290.088 290.088 ↓ 1.5 6 32,232

Index Scan using _hyper_5_73_chunk_pod_cost_vendor_id_end_time_idx on _hyper_5_73_chunk (cost=0.42..13.60 rows=4 width=48) (actual time=0.006..0.009 rows=6 loops=32,232)

  • Index Cond: ((vendor_id = cost_end_time.vendor_id) AND (end_time = cost_end_time.end_time))
  • Filter: (_2__be_0_node.id = node_id)
  • Rows Removed by Filter: 17
19. 1,030.515 1,030.515 ↑ 1.0 1 343,505

Index Scan using pod_pkey on pod (cost=0.15..0.39 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=343,505)

  • Index Cond: (id = pod_cost.pod_id)
20.          

SubPlan (for Index Scan)

21. 0.000 0.000 ↑ 1.0 1 343,505

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

22.          

SubPlan (for Aggregate)

23. 0.000 0.000 ↑ 1.0 1 343,505

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

24.          

SubPlan (for Nested Loop Left Join)

25. 15.610 15.610 ↑ 1.0 1 15,610

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

26.          

SubPlan (for Aggregate)

27. 83.407 83.407 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=78.379..83.407 rows=1 loops=1)

Planning time : 2.080 ms
Execution time : 5,951.178 ms