explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HS4F

Settings
# exclusive inclusive rows x rows loops node
1. 6,553.831 45,260.252 ↑ 255.6 504,322 1

Hash Right Join (cost=1,618,366.05..62,343,355.49 rows=128,889,953 width=337) (actual time=28,950.248..45,260.252 rows=504,322 loops=1)

  • Hash Cond: ("substring"((deals.user_deal_id)::text, 4) = (raw_convp_deals_2019m01.aggregated_deal)::text)
2. 10,186.425 10,186.425 ↓ 1.0 10,319,583 1

Seq Scan on deals (cost=0.00..456,073.15 rows=10,290,615 width=18) (actual time=0.357..10,186.425 rows=10,319,583 loops=1)

3. 238.124 28,519.996 ↓ 201.1 503,733 1

Hash (cost=1,618,334.74..1,618,334.74 rows=2,505 width=94) (actual time=28,519.996..28,519.996 rows=503,733 loops=1)

  • Buckets: 65536 (originally 4096) Batches: 16 (originally 1) Memory Usage: 6479kB
4. 203.122 28,281.872 ↓ 201.1 503,733 1

Hash Left Join (cost=1,078,722.70..1,618,334.74 rows=2,505 width=94) (actual time=18,885.698..28,281.872 rows=503,733 loops=1)

  • Hash Cond: (((CASE WHEN (c1.precedence < c2.precedence) THEN raw_convp_deals_2019m01.buy_ccy ELSE raw_convp_deals_2019m01.sell_ccy END)::text = r2."CCY") AND (raw_convp_deals_2019m01.deal_date = r2."Date"))
5. 198.247 28,068.990 ↓ 201.1 503,733 1

Hash Left Join (cost=1,077,787.67..1,617,056.23 rows=2,505 width=86) (actual time=18,875.723..28,068.990 rows=503,733 loops=1)

  • Hash Cond: (((CASE WHEN (c1.precedence > c2.precedence) THEN raw_convp_deals_2019m01.buy_ccy ELSE raw_convp_deals_2019m01.sell_ccy END)::text = r1."CCY") AND (raw_convp_deals_2019m01.deal_date = r1."Date"))
6. 125.448 27,854.354 ↓ 201.1 503,733 1

Hash Left Join (cost=1,076,852.65..1,615,777.72 rows=2,505 width=78) (actual time=18,859.262..27,854.354 rows=503,733 loops=1)

  • Hash Cond: ((raw_convp_deals_2019m01.sell_ccy)::text = (c2.currency)::text)
7. 135.855 27,728.892 ↓ 201.1 503,733 1

Hash Left Join (cost=1,076,839.50..1,615,737.64 rows=2,505 width=70) (actual time=18,859.233..27,728.892 rows=503,733 loops=1)

  • Hash Cond: ((raw_convp_deals_2019m01.buy_ccy)::text = (c1.currency)::text)
8. 1,285.329 27,592.048 ↓ 201.1 503,733 1

Hash Join (cost=1,076,826.35..1,615,697.55 rows=2,505 width=62) (actual time=18,858.223..27,592.048 rows=503,733 loops=1)

  • Hash Cond: ((raw_convp_deals_2019m01.buy_cnum)::text = (raw_dlk_individualcustomer.customer)::text)
9. 42.522 7,520.346 ↓ 202.7 509,902 1

Append (cost=0.00..510,318.60 rows=2,515 width=54) (actual time=2.044..7,520.346 rows=509,902 loops=1)

10. 1,482.692 1,482.692 ↓ 192.5 75,671 1

Seq Scan on raw_convp_deals_2019m01 (cost=0.00..79,793.16 rows=393 width=54) (actual time=2.042..1,482.692 rows=75,671 loops=1)

  • Filter: ((linked_deal IS NULL) AND (deal_date >= '2019-01-01'::date) AND (deal_date < '2019-08-01'::date) AND ((narrative)::text = 'RCONFX'::text) AND (CASE WHEN (NOT reverse) THEN 0 ELSE 1 END = 0))
  • Rows Removed by Filter: 1029704
11. 782.854 782.854 ↓ 199.2 72,711 1

Seq Scan on raw_convp_deals_2019m02 (cost=0.00..64,197.28 rows=365 width=54) (actual time=0.400..782.854 rows=72,711 loops=1)

  • Filter: ((linked_deal IS NULL) AND (deal_date >= '2019-01-01'::date) AND (deal_date < '2019-08-01'::date) AND ((narrative)::text = 'RCONFX'::text) AND (CASE WHEN (NOT reverse) THEN 0 ELSE 1 END = 0))
  • Rows Removed by Filter: 817774
12. 1,178.441 1,178.441 ↓ 210.4 83,329 1

Seq Scan on raw_convp_deals_2019m03 (cost=0.00..74,787.60 rows=396 width=54) (actual time=0.429..1,178.441 rows=83,329 loops=1)

  • Filter: ((linked_deal IS NULL) AND (deal_date >= '2019-01-01'::date) AND (deal_date < '2019-08-01'::date) AND ((narrative)::text = 'RCONFX'::text) AND (CASE WHEN (NOT reverse) THEN 0 ELSE 1 END = 0))
  • Rows Removed by Filter: 954007
13. 1,113.694 1,113.694 ↓ 201.4 84,172 1

Seq Scan on raw_convp_deals_2019m04 (cost=0.00..79,169.68 rows=418 width=54) (actual time=0.350..1,113.694 rows=84,172 loops=1)

  • Filter: ((linked_deal IS NULL) AND (deal_date >= '2019-01-01'::date) AND (deal_date < '2019-08-01'::date) AND ((narrative)::text = 'RCONFX'::text) AND (CASE WHEN (NOT reverse) THEN 0 ELSE 1 END = 0))
  • Rows Removed by Filter: 1013788
14. 1,185.530 1,185.530 ↓ 202.8 79,902 1

Seq Scan on raw_convp_deals_2019m05 (cost=0.00..93,088.72 rows=394 width=54) (actual time=5.193..1,185.530 rows=79,902 loops=1)

  • Filter: ((linked_deal IS NULL) AND (deal_date >= '2019-01-01'::date) AND (deal_date < '2019-08-01'::date) AND ((narrative)::text = 'RCONFX'::text) AND (CASE WHEN (NOT reverse) THEN 0 ELSE 1 END = 0))
  • Rows Removed by Filter: 1209148
15. 1,136.551 1,136.551 ↓ 208.0 82,800 1

Seq Scan on raw_convp_deals_2019m06 (cost=0.00..83,328.60 rows=398 width=54) (actual time=0.278..1,136.551 rows=82,800 loops=1)

  • Filter: ((linked_deal IS NULL) AND (deal_date >= '2019-01-01'::date) AND (deal_date < '2019-08-01'::date) AND ((narrative)::text = 'RCONFX'::text) AND (CASE WHEN (NOT reverse) THEN 0 ELSE 1 END = 0))
  • Rows Removed by Filter: 1071975
16. 598.062 598.062 ↓ 207.4 31,317 1

Seq Scan on raw_convp_deals_2019m07 (cost=0.00..35,953.56 rows=151 width=54) (actual time=0.375..598.062 rows=31,317 loops=1)

  • Filter: ((linked_deal IS NULL) AND (deal_date >= '2019-01-01'::date) AND (deal_date < '2019-08-01'::date) AND ((narrative)::text = 'RCONFX'::text) AND (CASE WHEN (NOT reverse) THEN 0 ELSE 1 END = 0))
  • Rows Removed by Filter: 465361
17. 1,908.513 18,786.373 ↓ 1.0 5,855,953 1

Hash (cost=975,381.94..975,381.94 rows=5,835,873 width=15) (actual time=18,786.373..18,786.373 rows=5,855,953 loops=1)

  • Buckets: 131072 Batches: 64 Memory Usage: 5294kB
18. 16,877.860 16,877.860 ↓ 1.0 5,855,953 1

Seq Scan on raw_dlk_individualcustomer (cost=0.00..975,381.94 rows=5,835,873 width=15) (actual time=1.229..16,877.860 rows=5,855,953 loops=1)

  • Filter: (category <> ALL ('{17,18}'::numeric[]))
  • Rows Removed by Filter: 1676
19. 0.011 0.989 ↑ 11.7 12 1

Hash (cost=11.40..11.40 rows=140 width=524) (actual time=0.989..0.989 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.978 0.978 ↑ 11.7 12 1

Seq Scan on currency_precedence c1 (cost=0.00..11.40 rows=140 width=524) (actual time=0.975..0.978 rows=12 loops=1)

21. 0.004 0.014 ↑ 11.7 12 1

Hash (cost=11.40..11.40 rows=140 width=524) (actual time=0.014..0.014 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.010 0.010 ↑ 11.7 12 1

Seq Scan on currency_precedence c2 (cost=0.00..11.40 rows=140 width=524) (actual time=0.008..0.010 rows=12 loops=1)

23. 6.276 16.389 ↑ 1.0 28,554 1

Hash (cost=505.41..505.41 rows=28,641 width=16) (actual time=16.389..16.389 rows=28,554 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1595kB
24. 10.113 10.113 ↑ 1.0 28,554 1

Seq Scan on cb_rates r1 (cost=0.00..505.41 rows=28,641 width=16) (actual time=1.804..10.113 rows=28,554 loops=1)

25. 5.654 9.760 ↑ 1.0 28,554 1

Hash (cost=505.41..505.41 rows=28,641 width=16) (actual time=9.760..9.760 rows=28,554 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1595kB
26. 4.106 4.106 ↑ 1.0 28,554 1

Seq Scan on cb_rates r2 (cost=0.00..505.41 rows=28,641 width=16) (actual time=0.013..4.106 rows=28,554 loops=1)

Planning time : 6.635 ms
Execution time : 45,285.691 ms