explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aO6BJ : Optimization for: plan #J8kHV

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 330.866 106,257.132 ↓ 291,972.0 291,972 1

Nested Loop (cost=127,079.81..127,080.17 rows=1 width=7,404) (actual time=22,176.317..106,257.132 rows=291,972 loops=1)

2. 0.109 0.109 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.108..0.109 rows=1 loops=1)

3. 44,425.622 105,926.157 ↓ 291,972.0 291,972 1

Nested Loop Left Join (cost=127,079.81..127,079.88 rows=1 width=7,372) (actual time=22,176.204..105,926.157 rows=291,972 loops=1)

  • Join Filter: (((nf."PaymentID")::text = (withrate.pid)::text) AND (withrate."PaymentExecutionDateKey" = nf."PaymentExecutionDateKey"))
  • Rows Removed by Join Filter: 81,167,938
4.          

CTE factpaymentsfiltered

5. 555.642 555.642 ↓ 200.0 291,972 1

Seq Scan on "FactPayments" (cost=0.00..27,374.88 rows=1,460 width=1,394) (actual time=0.021..555.642 rows=291,972 loops=1)

  • Filter: (("DWHTenantKey" = (NULLIF(current_setting('session.tenantid'::text, true), ''::text))::uuid) AND (((to_timestamp(current_setting('session.lastupdatets'::text, true), 'YYYY-MM-DD HH24:MI:SS'::text))::timestamp without time zone IS NULL) OR ("ETLUpdateTS" >= (to_timestamp(current_setting('session.lastupdatets'::text, true), 'YYYY-MM-DD HH24:MI:SS'::text))::timestamp without time zone)))
6.          

CTE dimcurrencyfiltered

7. 0.296 0.296 ↑ 1.0 230 1

Seq Scan on "DimCurrency" (cost=0.00..13.18 rows=230 width=70) (actual time=0.012..0.296 rows=230 loops=1)

  • Filter: ("DWHTenantKey" = (NULLIF(current_setting('session.tenantid'::text, true), ''::text))::uuid)
8.          

CTE factcashflowsfiltered

9. 0.017 0.017 ↑ 25.0 1 1

Seq Scan on "FactCashFlows" (cost=0.00..3.56 rows=25 width=750) (actual time=0.017..0.017 rows=1 loops=1)

  • Filter: ("DWHTenantKey" = (NULLIF(current_setting('session.tenantid'::text, true), ''::text))::uuid)
10.          

CTE factcurrencyratesfiltered

11. 1,321.206 1,321.206 ↑ 1.0 1,008,183 1

Seq Scan on "FactCurrencyRates" (cost=0.00..69,263.12 rows=1,008,183 width=158) (actual time=0.042..1,321.206 rows=1,008,183 loops=1)

  • Filter: ("DWHTenantKey" = (NULLIF(current_setting('session.tenantid'::text, true), ''::text))::uuid)
12.          

CTE nonnullcurrencyincashflow

13. 0.002 0.022 ↑ 1.0 1 1

Limit (cost=0.00..0.02 rows=1 width=24) (actual time=0.022..0.022 rows=1 loops=1)

14. 0.020 0.020 ↑ 25.0 1 1

CTE Scan on factcashflowsfiltered fcf (cost=0.00..0.50 rows=25 width=24) (actual time=0.020..0.020 rows=1 loops=1)

  • Filter: ("CorporateCurrencyKey" IS NOT NULL)
15.          

CTE mostfrequencyusedcurrencyintransactionsummery

16. 0.002 809.225 ↑ 1.0 1 1

Limit (cost=136.87..136.87 rows=1 width=32) (actual time=809.224..809.225 rows=1 loops=1)

17. 0.052 809.223 ↑ 1,679.0 1 1

Sort (cost=136.87..141.06 rows=1,679 width=32) (actual time=809.223..809.223 rows=1 loops=1)

  • Sort Key: (count(fp."PaymentTransactionCurrency")) DESC
  • Sort Method: top-N heapsort Memory: 25kB
18. 200.352 809.171 ↑ 39.0 43 1

HashAggregate (cost=111.68..128.47 rows=1,679 width=32) (actual time=809.148..809.171 rows=43 loops=1)

  • Group Key: c."CurrencyKey", fp."DWHTenantKey
19. 414.091 608.819 ↓ 173.9 291,972 1

Hash Join (cost=7.48..99.09 rows=1,679 width=40) (actual time=0.349..608.819 rows=291,972 loops=1)

  • Hash Cond: ((fp."PaymentTransactionCurrency")::text = (c."CurrencyCode")::text)
20. 194.421 194.421 ↓ 200.0 291,972 1

CTE Scan on factpaymentsfiltered fp (cost=0.00..29.20 rows=1,460 width=32) (actual time=0.015..194.421 rows=291,972 loops=1)

21. 0.137 0.307 ↑ 1.0 230 1

Hash (cost=4.60..4.60 rows=230 width=32) (actual time=0.307..0.307 rows=230 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
22. 0.170 0.170 ↑ 1.0 230 1

CTE Scan on dimcurrencyfiltered c (cost=0.00..4.60 rows=230 width=32) (actual time=0.002..0.170 rows=230 loops=1)

23.          

CTE newfactpayments

24. 783.242 5,214.268 ↓ 291,972.0 291,972 1

Nested Loop Left Join (cost=73.01..78.58 rows=1 width=7,598) (actual time=2,593.977..5,214.268 rows=291,972 loops=1)

  • Join Filter: (mfc."DWHTenantKey" = f."DWHTenantKey")
25. 546.320 3,263.138 ↓ 291,972.0 291,972 1

Nested Loop Left Join (cost=73.01..78.53 rows=1 width=7,558) (actual time=1,784.731..3,263.138 rows=291,972 loops=1)

  • Join Filter: (nccf."DWHTenantKey" = f."DWHTenantKey")
26. 348.333 2,132.874 ↓ 291,972.0 291,972 1

Hash Right Join (cost=73.01..78.48 rows=1 width=7,550) (actual time=1,784.699..2,132.874 rows=291,972 loops=1)

  • Hash Cond: ((c_1."CurrencyCode")::text = (f."PaymentEquivalentCurrency")::text)
27. 0.513 0.513 ↑ 1.0 230 1

CTE Scan on dimcurrencyfiltered c_1 (cost=0.00..4.60 rows=230 width=32) (actual time=0.014..0.513 rows=230 loops=1)

28. 353.424 1,784.028 ↓ 291,972.0 291,972 1

Hash (cost=73.00..73.00 rows=1 width=7,558) (actual time=1,784.028..1,784.028 rows=291,972 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 2 (originally 1) Memory Usage: 120,858kB
29. 1,430.604 1,430.604 ↓ 291,972.0 291,972 1

CTE Scan on factpaymentsfiltered f (cost=0.00..73.00 rows=1 width=7,558) (actual time=0.027..1,430.604 rows=291,972 loops=1)

  • Filter: (("DWHTenantKey" = (NULLIF(current_setting('session.tenantid'::text, true), ''::text))::uuid) AND (((to_timestamp(current_setting('session.lastupdatets'::text, true), 'YYYY-MM-DD HH24:MI:SS'::text))::timestamp without time zone IS NULL) OR ("ETLUpdateTS" >= (to_timestamp(current_setting('session.lastupdatets'::text, true), 'YYYY-MM-DD HH24:MI:SS'::text))::timestamp without time zone)))
30. 583.944 583.944 ↑ 1.0 1 291,972

CTE Scan on nonnullcurrencyincashflow nccf (cost=0.00..0.03 rows=1 width=24) (actual time=0.001..0.002 rows=1 loops=291,972)

  • Filter: ("DWHTenantKey" = (NULLIF(current_setting('session.tenantid'::text, true), ''::text))::uuid)
31. 1,167.888 1,167.888 ↑ 1.0 1 291,972

CTE Scan on mostfrequencyusedcurrencyintransactionsummery mfc (cost=0.00..0.03 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=291,972)

  • Filter: ("DWHTenantKey" = (NULLIF(current_setting('session.tenantid'::text, true), ''::text))::uuid)
32.          

CTE paymentdateswithrate

33. 830.357 19,581.394 ↓ 278.0 278 1

Subquery Scan on t (cost=30,208.68..30,209.61 rows=1 width=358) (actual time=14,354.970..19,581.394 rows=278 loops=1)

  • Filter: (t.rn = 1)
  • Rows Removed by Filter: 1,697,728
34. 2,081.471 18,751.037 ↓ 67,920.2 1,698,006 1

WindowAgg (cost=30,208.68..30,209.30 rows=25 width=374) (actual time=14,354.964..18,751.037 rows=1,698,006 loops=1)

35. 9,298.547 16,669.566 ↓ 67,920.2 1,698,006 1

Sort (cost=30,208.68..30,208.74 rows=25 width=366) (actual time=14,354.957..16,669.566 rows=1,698,006 loops=1)

  • Sort Key: t2."PaymentID", (abs((t2."PaymentExecutionDateKey" - t1."DateKey")))
  • Sort Method: external merge Disk: 121,688kB
36. 1,466.454 7,371.019 ↓ 67,920.2 1,698,006 1

Hash Join (cost=0.04..30,208.09 rows=25 width=366) (actual time=4,665.772..7,371.019 rows=1,698,006 loops=1)

  • Hash Cond: ((t1."SourceCurrencyKey" = t2."PaymentCurrencyKey") AND (t1."TargetCurrencyKey" = t2."PaymentEquivalentCurrencyKey"))
37. 2,518.213 2,518.213 ↑ 1.3 745,126 1

CTE Scan on factcurrencyratesfiltered t1 (cost=0.00..22,684.12 rows=1,003,142 width=32) (actual time=0.050..2,518.213 rows=745,126 loops=1)

  • Filter: ("CurrencyMidRates" <> '0'::double precision)
  • Rows Removed by Filter: 263,057
38. 9.343 3,386.352 ↓ 13,256.0 13,256 1

Hash (cost=0.02..0.02 rows=1 width=342) (actual time=3,386.352..3,386.352 rows=13,256 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,025kB
39. 3,377.009 3,377.009 ↓ 13,256.0 13,256 1

CTE Scan on newfactpayments t2 (cost=0.00..0.02 rows=1 width=342) (actual time=11.618..3,377.009 rows=13,256 loops=1)

  • Filter: (("PaymentExecutionDateKey" IS NOT NULL) AND ("PaymentCurrencyKey" <> "PaymentEquivalentCurrencyKey"))
  • Rows Removed by Filter: 278,716
40. 2,814.163 2,814.163 ↓ 291,972.0 291,972 1

CTE Scan on newfactpayments nf (cost=0.00..0.02 rows=1 width=7,356) (actual time=2,593.983..2,814.163 rows=291,972 loops=1)

41. 58,686.372 58,686.372 ↓ 278.0 278 291,972

CTE Scan on paymentdateswithrate withrate (cost=0.00..0.02 rows=1 width=334) (actual time=0.050..0.201 rows=278 loops=291,972)

Planning time : 2.327 ms
Execution time : 106,483.733 ms