explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HPRL

Settings
# exclusive inclusive rows x rows loops node
1. 3.140 7,090.250 ↑ 3.4 6,831 1

Sort (cost=261,684.01..261,742.66 rows=23,459 width=48) (actual time=7,089.762..7,090.250 rows=6,831 loops=1)

  • Sort Key: summed."timestamp
  • Sort Method: quicksort Memory: 726kB
2.          

CTE lastprice

3. 70.824 427.471 ↑ 1.0 23 1

Unique (cost=0.42..44,375.32 rows=23 width=18) (actual time=0.023..427.471 rows=23 loops=1)

4. 356.647 356.647 ↓ 1.0 615,083 1

Index Scan using bmx_sw_mm_exposure_currency_timestamp_idx on bmx_sw_mm_exposure (cost=0.42..42,837.72 rows=615,037 width=18) (actual time=0.021..356.647 rows=615,083 loops=1)

5.          

CTE deltas

6. 0.103 0.423 ↑ 5.7 202 1

HashAggregate (cost=81.63..93.23 rows=1,160 width=48) (actual time=0.392..0.423 rows=202 loops=1)

  • Group Key: (split_part((bmx_sw_mm_hedging_deals.symbol)::text, '_'::text, 1)), ((bmx_sw_mm_hedging_deals."timestamp")::double precision), (CASE WHEN ((bmx_sw_mm_hedging_deals.side)::text = 'buy'::text) THEN (- bmx_sw_mm_hedging_deals.amount) ELSE bmx_sw_mm_hedging_deals.amount END)
7. 0.018 0.320 ↑ 5.7 202 1

Append (cost=9.28..72.93 rows=1,160 width=48) (actual time=0.202..0.320 rows=202 loops=1)

8. 0.069 0.220 ↓ 1.2 120 1

HashAggregate (cost=9.28..10.53 rows=100 width=48) (actual time=0.202..0.220 rows=120 loops=1)

  • Group Key: (split_part((bmx_sw_mm_hedging_deals.symbol)::text, '_'::text, 1)), bmx_sw_mm_hedging_deals."timestamp", (CASE WHEN ((bmx_sw_mm_hedging_deals.side)::text = 'buy'::text) THEN (- bmx_sw_mm_hedging_deals.amount) ELSE bmx_sw_mm_hedging_deals.amount END)
9. 0.012 0.151 ↓ 1.2 120 1

Append (cost=0.00..8.53 rows=100 width=44) (actual time=0.027..0.151 rows=120 loops=1)

10. 0.077 0.077 ↓ 1.2 60 1

Seq Scan on bmx_sw_mm_hedging_deals (cost=0.00..3.64 rows=50 width=44) (actual time=0.027..0.077 rows=60 loops=1)

  • Filter: ((hedger)::text = 'exposure_diff'::text)
  • Rows Removed by Filter: 61
11. 0.062 0.062 ↓ 1.2 60 1

Seq Scan on bmx_sw_mm_hedging_deals bmx_sw_mm_hedging_deals_1 (cost=0.00..3.89 rows=50 width=44) (actual time=0.008..0.062 rows=60 loops=1)

  • Filter: ((hedger)::text = 'exposure_diff'::text)
  • Rows Removed by Filter: 61
12. 0.040 0.040 ↑ 12.9 41 1

Seq Scan on coinbase_hedging_orders (cost=0.00..23.25 rows=530 width=48) (actual time=0.010..0.040 rows=41 loops=1)

13. 0.042 0.042 ↑ 12.9 41 1

Seq Scan on coinbase_hedging_orders coinbase_hedging_orders_1 (cost=0.00..28.55 rows=530 width=48) (actual time=0.009..0.042 rows=41 loops=1)

14.          

CTE pvlog

15. 119.746 119.746 ↓ 1.0 615,083 1

Seq Scan on bmx_sw_mm_exposure bmx_sw_mm_exposure_1 (cost=0.00..10,718.37 rows=615,037 width=18) (actual time=0.013..119.746 rows=615,083 loops=1)

16. 3.286 7,087.110 ↑ 3.4 6,831 1

Merge Join (cost=180,751.19..204,794.23 rows=23,459 width=48) (actual time=6,512.029..7,087.110 rows=6,831 loops=1)

  • Merge Cond: ((summed.currency)::text = (lastprice.currency)::text)
17. 90.890 6,655.562 ↑ 29.9 6,831 1

Subquery Scan on summed (cost=180,750.21..203,814.10 rows=203,987 width=48) (actual time=6,084.411..6,655.562 rows=6,831 loops=1)

  • Filter: ((summed.delted IS NOT NULL) AND (summed."timestamp" > '1560167490'::double precision))
  • Rows Removed by Filter: 608454
18. 402.684 6,564.672 ↓ 1.0 615,285 1

WindowAgg (cost=180,750.21..196,126.14 rows=615,037 width=64) (actual time=6,048.287..6,564.672 rows=615,285 loops=1)

19. 2,720.372 6,161.988 ↓ 1.0 615,285 1

Sort (cost=180,750.21..182,287.80 rows=615,037 width=56) (actual time=6,048.265..6,161.988 rows=615,285 loops=1)

  • Sort Key: (CASE WHEN (pvlog.currency IS NULL) THEN (deltas.currency)::character varying ELSE pvlog.currency END), (CASE WHEN (pvlog.currency IS NULL) THEN deltas."timestamp" ELSE (pvlog."timestamp")::double precision END)
  • Sort Method: external sort Disk: 20752kB
20. 312.667 3,441.616 ↓ 1.0 615,285 1

Merge Full Join (cost=92,544.26..100,588.94 rows=615,037 width=56) (actual time=2,319.117..3,441.616 rows=615,285 loops=1)

  • Merge Cond: (((pvlog.currency)::text = deltas.currency) AND (((pvlog."timestamp")::double precision) = deltas."timestamp"))
21. 2,702.196 3,128.164 ↓ 1.0 615,083 1

Sort (cost=92,462.01..93,999.60 rows=615,037 width=50) (actual time=2,318.426..3,128.164 rows=615,083 loops=1)

  • Sort Key: pvlog.currency, ((pvlog."timestamp")::double precision)
  • Sort Method: external merge Disk: 24488kB
22. 425.968 425.968 ↓ 1.0 615,083 1

CTE Scan on pvlog (cost=0.00..12,300.74 rows=615,037 width=50) (actual time=0.016..425.968 rows=615,083 loops=1)

23. 0.308 0.785 ↑ 5.7 202 1

Sort (cost=82.24..85.14 rows=1,160 width=48) (actual time=0.683..0.785 rows=202 loops=1)

  • Sort Key: deltas.currency, deltas."timestamp
  • Sort Method: quicksort Memory: 40kB
24. 0.477 0.477 ↑ 5.7 202 1

CTE Scan on deltas (cost=0.00..23.20 rows=1,160 width=48) (actual time=0.395..0.477 rows=202 loops=1)

25. 0.730 428.262 ↓ 284.1 6,535 1

Sort (cost=0.98..1.04 rows=23 width=46) (actual time=427.598..428.262 rows=6,535 loops=1)

  • Sort Key: lastprice.currency
  • Sort Method: quicksort Memory: 26kB
26. 427.532 427.532 ↑ 1.0 23 1

CTE Scan on lastprice (cost=0.00..0.46 rows=23 width=46) (actual time=0.035..427.532 rows=23 loops=1)

Planning time : 1.578 ms
Execution time : 7,099.609 ms