explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hw5w

Settings
# exclusive inclusive rows x rows loops node
1. 3,643.761 13,561.633 ↑ 1.0 1 1

Aggregate (cost=1,589,243.76..1,589,243.78 rows=1 width=32) (actual time=13,561.618..13,561.633 rows=1 loops=1)

  • Buffers: shared hit=1,324,348
  • Functions: 107
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 6.336 ms, Inlining 55.077 ms, Optimization 313.778 ms, Emission 287.250 ms, Total 662.441 ms
2. 0.009 9,686.883 ↑ 1.0 1 1

Nested Loop Left Join (cost=1,589,242.70..1,589,243.75 rows=1 width=32) (actual time=9,686.863..9,686.883 rows=1 loops=1)

  • Buffers: shared hit=1,324,348
3. 657.199 657.199 ↑ 1.0 1 1

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

  • Filter: (id = 1)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=1
4. 2,390.462 9,029.675 ↑ 1.0 1 1

Aggregate (cost=1,589,242.70..1,589,242.71 rows=1 width=32) (actual time=9,029.662..9,029.675 rows=1 loops=1)

  • Buffers: shared hit=1,324,347
5. 138.673 6,357.343 ↓ 1.1 281,870 1

Nested Loop Left Join (cost=15.32..1,585,414.87 rows=255,189 width=252) (actual time=0.315..6,357.343 rows=281,870 loops=1)

  • Buffers: shared hit=1,324,347
6. 62.540 299.400 ↓ 1.1 281,870 1

Hash Join (cost=10.68..88,890.87 rows=255,189 width=220) (actual time=0.241..299.400 rows=281,870 loops=1)

  • Hash Cond: (pod_cost.node_id = _1__be_0_node.id)
  • Buffers: shared hit=158,442
7. 32.314 236.840 ↓ 1.1 281,870 1

Custom Scan (ChunkAppend) on pod_cost (cost=0.00..88,196.13 rows=255,189 width=224) (actual time=0.209..236.840 rows=281,870 loops=1)

  • Chunks excluded during runtime: 0
  • Buffers: shared hit=158,439
8. 65.293 65.293 ↑ 1.0 151,894 1

Seq Scan on _hyper_5_38_chunk (cost=0.00..31,883.16 rows=151,953 width=224) (actual time=0.177..65.293 rows=151,894 loops=1)

  • Filter: ((end_time >= '2020-10-03 04:47:15'::timestamp without time zone) AND (vendor.id = vendor_id))
  • Rows Removed by Filter: 17,050
  • Buffers: shared hit=29,349
9. 139.233 139.233 ↓ 1.3 129,976 1

Index Scan using "73_240_pod_cost_pkey" on _hyper_5_73_chunk (cost=0.42..56,312.97 rows=103,236 width=224) (actual time=0.024..139.233 rows=129,976 loops=1)

  • Index Cond: ((vendor_id = vendor.id) AND (end_time >= '2020-10-03 04:47:15'::timestamp without time zone))
  • Buffers: shared hit=129,090
10. 0.004 0.020 ↑ 5.0 6 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
11. 0.016 0.016 ↑ 5.0 6 1

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

  • Buffers: shared hit=3
12. 845.610 5,919.270 ↑ 1.0 1 281,870

Nested Loop Left Join (cost=4.64..5.84 rows=1 width=32) (actual time=0.014..0.021 rows=1 loops=281,870)

  • Buffers: shared hit=1,165,905
13. 845.610 3,946.180 ↑ 1.0 1 281,870

Hash Right Join (cost=2.38..3.54 rows=1 width=77) (actual time=0.007..0.014 rows=1 loops=281,870)

  • Hash Cond: (instance.id = pod.instance_id)
  • Buffers: shared hit=845,610
14. 2,536.830 2,536.830 ↓ 1.3 8 281,870

Seq Scan on instance (cost=0.00..1.14 rows=6 width=36) (actual time=0.002..0.009 rows=8 loops=281,870)

  • Buffers: shared hit=281,870
15.          

SubPlan (for Seq Scan)

16. 0.000 0.000 ↑ 1.0 1 2,254,960

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

17. 281.870 563.740 ↑ 1.0 1 281,870

Hash (cost=2.37..2.37 rows=1 width=49) (actual time=0.002..0.002 rows=1 loops=281,870)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=563,740
18. 281.870 281.870 ↑ 1.0 1 281,870

Index Scan using pod_pkey on pod (cost=0.15..2.37 rows=1 width=49) (actual time=0.001..0.001 rows=1 loops=281,870)

  • Index Cond: (id = pod_cost.pod_id)
  • Buffers: shared hit=563,740
19. 281.870 1,127.480 ↑ 1.0 1 281,870

Aggregate (cost=2.26..2.27 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=281,870)

  • Buffers: shared hit=320,295
20. 371.615 845.610 ↓ 0.0 0 281,870

Nested Loop Left Join (cost=0.00..2.24 rows=1 width=40) (actual time=0.003..0.003 rows=0 loops=281,870)

  • Join Filter: (account_pod.account_id = account.id)
  • Rows Removed by Join Filter: 0
  • Buffers: shared hit=320,295
21. 281.870 281.870 ↓ 0.0 0 281,870

Seq Scan on account_pod (cost=0.00..1.18 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=281,870)

  • Filter: (pod.id = pod_id)
  • Rows Removed by Filter: 16
  • Buffers: shared hit=281,870
22. 192.125 192.125 ↓ 1.5 3 38,425

Seq Scan on account (cost=0.00..1.04 rows=2 width=36) (actual time=0.002..0.005 rows=3 loops=38,425)

  • Buffers: shared hit=38,425
23.          

SubPlan (for Seq Scan)

24. 0.000 0.000 ↑ 1.0 1 115,275

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

25.          

SubPlan (for Aggregate)

26. 0.000 0.000 ↑ 1.0 1 38,425

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

27.          

SubPlan (for Nested Loop Left Join)

28. 0.000 0.000 ↑ 1.0 1 281,870

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

29.          

SubPlan (for Aggregate)

30. 281.870 281.870 ↑ 1.0 1 281,870

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

31.          

SubPlan (for Aggregate)

32. 230.989 230.989 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=219.572..230.989 rows=1 loops=1)

Planning time : 2.674 ms
Execution time : 13,707.373 ms