explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hpCR

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.112 8,224.662 ↑ 100.0 1 1

Result (cost=88,903.89..88,904.40 rows=100 width=32) (actual time=8,224.661..8,224.662 rows=1 loops=1)

2.          

CTE elements

3. 0.212 8,224.504 ↓ 10.0 10 1

Subquery Scan on row (cost=88,903.84..88,903.87 rows=1 width=32) (actual time=8,224.321..8,224.504 rows=10 loops=1)

4. 0.012 8,224.292 ↓ 10.0 10 1

Limit (cost=88,903.84..88,903.85 rows=1 width=828) (actual time=8,224.280..8,224.292 rows=10 loops=1)

5. 0.342 8,224.280 ↓ 10.0 10 1

Sort (cost=88,903.84..88,903.85 rows=1 width=828) (actual time=8,224.278..8,224.280 rows=10 loops=1)

  • Sort Key: (count(w.id)) DESC
  • Sort Method: top-N heapsort Memory: 27kB
6. 32.184 8,223.938 ↓ 234.0 234 1

GroupAggregate (cost=81,108.20..88,903.83 rows=1 width=828) (actual time=8,191.196..8,223.938 rows=234 loops=1)

  • Group Key: ac.name
7. 7.947 8,191.754 ↑ 49.5 1,968 1

Sort (cost=81,108.20..81,351.81 rows=97,445 width=619) (actual time=8,191.105..8,191.754 rows=1,968 loops=1)

  • Sort Key: ac.name
  • Sort Method: quicksort Memory: 473kB
8. 291.750 8,183.807 ↑ 49.5 1,968 1

Nested Loop (cost=318.51..46,052.26 rows=97,445 width=619) (actual time=1.816..8,183.807 rows=1,968 loops=1)

9. 0.972 21.854 ↓ 3.5 1,971 1

Append (cost=0.00..116.31 rows=569 width=103) (actual time=0.041..21.854 rows=1,971 loops=1)

10. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on eod w (cost=0.00..0.00 rows=1 width=1,060) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((put_time_utc > '2020-01-23 04:03:42.837751'::timestamp without time zone) AND (create_time_utc >= '2020-01-30 04:02:42.837751'::timestamp without time zone))
11. 0.008 0.008 ↓ 0.0 0 1

Index Scan using i_eod_2020w0120_create_time on eod_2020w0120 w_1 (cost=0.43..4.49 rows=1 width=100) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (create_time_utc >= '2020-01-30 04:02:42.837751'::timestamp without time zone)
  • Filter: (put_time_utc > '2020-01-23 04:03:42.837751'::timestamp without time zone)
12. 20.872 20.872 ↓ 3.5 1,971 1

Index Scan using i_eod_2020w0127_create_time on eod_2020w0127 w_2 (cost=0.43..111.82 rows=567 width=101) (actual time=0.031..20.872 rows=1,971 loops=1)

  • Index Cond: (create_time_utc >= '2020-01-30 04:02:42.837751'::timestamp without time zone)
  • Filter: (put_time_utc > '2020-01-23 04:03:42.837751'::timestamp without time zone)
13. 244.533 7,870.203 ↑ 171.0 1 1,971

Hash Join (cost=318.51..396.97 rows=171 width=516) (actual time=2.847..3.993 rows=1 loops=1,971)

  • Hash Cond: (ac.id = f.account_id)
14. 5,256.528 5,256.528 ↑ 1.4 748 1,968

Foreign Scan on account ac (cost=100.00..170.07 rows=1,069 width=25) (actual time=0.343..2.671 rows=748 loops=1,968)

15. 7.884 2,369.142 ↑ 32.0 1 1,971

Hash (cost=218.11..218.11 rows=32 width=4) (actual time=1.202..1.202 rows=1 loops=1,971)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 253.590 2,361.258 ↑ 32.0 1 1,971

Nested Loop (cost=210.03..218.11 rows=32 width=4) (actual time=1.196..1.198 rows=1 loops=1,971)

17. 252.288 1,253.556 ↑ 1.0 1 1,971

Limit (cost=109.74..109.76 rows=1 width=12) (actual time=0.636..0.636 rows=1 loops=1,971)

18. 1,001.268 1,001.268 ↑ 2.0 1 1,971

Foreign Scan on allocation a (cost=109.74..109.78 rows=2 width=12) (actual time=0.508..0.508 rows=1 loops=1,971)

19. 854.112 854.112 ↑ 1.0 1 1,968

Foreign Scan on fleet f (cost=100.29..108.33 rows=1 width=8) (actual time=0.434..0.434 rows=1 loops=1,968)

20.          

Initplan (for Result)

21. 8,224.550 8,224.550 ↓ 10.0 10 1

CTE Scan on elements (cost=0.00..0.03 rows=1 width=32) (actual time=8,224.331..8,224.550 rows=10 loops=1)

Planning time : 25.715 ms
Execution time : 8,225.454 ms