explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rGNV

Settings
# exclusive inclusive rows x rows loops node
1. 0.923 41.500 ↑ 14,140.6 16 1

GroupAggregate (cost=687,416,035.71..724,936,624.43 rows=226,249 width=16) (actual time=40.288..41.500 rows=16 loops=1)

  • Group Key: (timezone('UTC'::text, to_timestamp(((floor((date_part('epoch'::text, to_timestamp((ch.""Timestamp"")::double precision)) / '86400'::double precision)) * '86400'::double precision) + offset_value.offset_val))))
2.          

CTE offset_value

3. 0.006 0.006 ↑ 1.0 1 1

Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=1)

4. 1.883 40.577 ↑ 488,287.3 4,390 1

Sort (cost=687,416,035.69..692,774,988.55 rows=2,143,581,143 width=40) (actual time=40.266..40.577 rows=4,390 loops=1)

  • Sort Key: (timezone('UTC'::text, to_timestamp(((floor((date_part('epoch'::text, to_timestamp((ch.""Timestamp"")::double precision)) / '86400'::double precision)) * '86400'::double precision) + offset_value.offset_val))))
  • Sort Method: quicksort Memory: 535kB
5. 5.581 38.694 ↑ 488,287.3 4,390 1

Nested Loop (cost=653.55..120,734,891.55 rows=2,143,581,143 width=40) (actual time=0.556..38.694 rows=4,390 loops=1)

6. 3.317 20.015 ↑ 1,861.3 4,366 1

Nested Loop (cost=653.26..367,618.39 rows=8,126,466 width=36) (actual time=0.544..20.015 rows=4,366 loops=1)

7. 0.853 3.636 ↑ 7.1 4,354 1

Nested Loop (cost=652.96..94,143.14 rows=30,808 width=32) (actual time=0.524..3.636 rows=4,354 loops=1)

8. 0.002 0.033 ↑ 2.0 1 1

Nested Loop (cost=0.00..2.81 rows=2 width=20) (actual time=0.019..0.033 rows=1 loops=1)

9. 0.009 0.009 ↑ 1.0 1 1

CTE Scan on offset_value (cost=0.00..0.02 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1)

10. 0.022 0.022 ↑ 2.0 1 1

Seq Scan on historical_available ha (cost=0.00..2.77 rows=2 width=12) (actual time=0.010..0.022 rows=1 loops=1)

  • Filter: (((""To"")::text = 'usd'::text) AND ((""Exchange_id"")::text = 'bitfinex'::text) AND ((""From"")::text = 'btc'::text))
  • Rows Removed by Filter: 100
11. 2.386 2.750 ↑ 3.5 4,354 1

Bitmap Heap Scan on crypto_historical ch (cost=652.96..46,916.13 rows=15,404 width=28) (actual time=0.502..2.750 rows=4,354 loops=1)

  • Recheck Cond: ((id = ha.id) AND (""Timestamp"" >= 1587411420) AND (""Timestamp"" <= 1588704599))
  • Heap Blocks: exact=1050
12. 0.364 0.364 ↑ 3.5 4,354 1

Bitmap Index Scan on crypto_historical_pkey (cost=0.00..649.11 rows=15,404 width=0) (actual time=0.364..0.364 rows=4,354 loops=1)

  • Index Cond: ((id = ha.id) AND (""Timestamp"" >= 1587411420) AND (""Timestamp"" <= 1588704599))
13. 13.062 13.062 ↑ 264.0 1 4,354

Index Scan using fiat_historicals_pkey on fiat_historicals fh1 (cost=0.29..6.24 rows=264 width=16) (actual time=0.003..0.003 rows=1 loops=4,354)

  • Index Cond: (((""Fiat_id"")::text = 'usd'::text) AND (""Date"" >= (ch.""Timestamp"" - 86400)) AND (""Date"" <= ch.""Timestamp""))
14. 13.098 13.098 ↑ 264.0 1 4,366

Index Scan using fiat_historicals_pkey on fiat_historicals fh2 (cost=0.29..6.24 rows=264 width=12) (actual time=0.003..0.003 rows=1 loops=4,366)

  • Index Cond: (((""Fiat_id"")::text = 'usd'::text) AND (""Date"" >= (ch.""Timestamp"" - 86400)) AND (""Date"" <= ch.""Timestamp""))
Planning time : 1.076 ms
Execution time : 41.588 ms