explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0M4r

Settings
# exclusive inclusive rows x rows loops node
1. 466.751 466.751 ↑ 1,072,024.7 1,621 1

CTE Scan on turnover_records tr (cost=869,126,877.60..1,051,590,839.39 rows=1,737,751,971 width=812) (actual time=406.382..466.751 rows=1,621 loops=1)

  • Filter: ((cat_revenue IS NOT NULL) OR (reduction_chargetype = ANY ('{FixedChargeOnTransaction,ExactChargeOnTransaction}'::text[])) OR (tariff_profile_counter IS NOT NULL) OR (no_tariff_profile_counter IS NOT NULL))
  • Rows Removed by Filter: 530
2.          

CTE functionarguments

3. 0.011 0.011 ↑ 1.0 1 1

Result (cost=0.00..0.02 rows=1 width=16) (actual time=0.010..0.011 rows=1 loops=1)

4.          

CTE prepaid

5. 0.001 0.070 ↓ 0.0 0 1

GroupAggregate (cost=8.47..8.50 rows=1 width=12) (actual time=0.070..0.070 rows=0 loops=1)

  • Group Key: fcpy.transaction_key
6. 0.014 0.069 ↓ 0.0 0 1

Sort (cost=8.47..8.48 rows=1 width=7) (actual time=0.069..0.069 rows=0 loops=1)

  • Sort Key: fcpy.transaction_key
  • Sort Method: quicksort Memory: 25kB
7. 0.055 0.055 ↓ 0.0 0 1

Index Scan using fact_customerpayment_payment_date_fk on fact_customerpayment fcpy (cost=0.44..8.46 rows=1 width=7) (actual time=0.055..0.055 rows=0 loops=1)

  • Index Cond: ((payment_date >= ('2019-12-08 00:00:00+0000'::cstring)::timestamp with time zone) AND (payment_date < ('2019-12-08 00:00:00+0000'::cstring)::timestamp with time zone))
  • Filter: ((fact_type = ANY ('{PU,CV}'::text[])) AND (transaction_key > 0))
8.          

CTE turnover_records

9. 19.665 456.397 ↑ 807,881.1 2,151 1

Hash Left Join (cost=64,497.49..869,126,869.08 rows=1,737,752,186 width=518) (actual time=406.344..456.397 rows=2,151 loops=1)

  • Hash Cond: (fcpy_1.tax_key = dtax.tax_key)
10. 0.977 436.712 ↑ 807,881.1 2,151 1

Hash Left Join (cost=64,496.46..102,343,715.98 rows=1,737,752,186 width=441) (actual time=406.200..436.712 rows=2,151 loops=1)

  • Hash Cond: (fcpy_1.tariffprofile_key = dtpf.tariffprofile_key)
11. 1.052 435.703 ↑ 807,881.1 2,151 1

Hash Left Join (cost=64,495.28..89,508,045.23 rows=1,737,752,186 width=405) (actual time=406.152..435.703 rows=2,151 loops=1)

  • Hash Cond: (fcpct.customertype_key = dcty.customertype_key)
12. 0.823 434.608 ↑ 807,881.1 2,151 1

Hash Left Join (cost=64,493.69..77,033,465.46 rows=1,737,752,186 width=373) (actual time=406.086..434.608 rows=2,151 loops=1)

  • Hash Cond: (fcpy_1.company_key = dcmp.company_key)
13. 1.112 433.704 ↑ 807,881.1 2,151 1

Hash Left Join (cost=64,489.18..53,311,423.06 rows=1,737,752,186 width=353) (actual time=405.985..433.704 rows=2,151 loops=1)

  • Hash Cond: (fcpy_1.station_key = dstn.station_key)
14. 0.800 432.554 ↑ 807,881.1 2,151 1

Hash Left Join (cost=64,487.54..40,849,909.03 rows=1,737,752,186 width=321) (actual time=405.928..432.554 rows=2,151 loops=1)

  • Hash Cond: (fcpy_1.shift_key = dsft.shift_key)
15. 1.380 426.103 ↑ 807,881.1 2,151 1

Hash Left Join (cost=64,083.80..16,957,023.26 rows=1,737,752,186 width=317) (actual time=400.173..426.103 rows=2,151 loops=1)

  • Hash Cond: (fcpy_1.currency = dcur.currency_name)
16. 0.710 424.693 ↑ 807,881.1 2,151 1

Merge Left Join (cost=64,082.70..4,647,944.17 rows=1,737,752,186 width=285) (actual time=400.117..424.693 rows=2,151 loops=1)

  • Merge Cond: (fcpy_1.transaction_key = fcpy_pucv.transaction_key)
17. 1.791 423.872 ↑ 807,881.1 2,151 1

Nested Loop Left Join (cost=64,082.67..303,437.49 rows=1,737,752,186 width=277) (actual time=400.003..423.872 rows=2,151 loops=1)

18. 4.721 411.326 ↑ 104.8 2,151 1

Nested Loop Left Join (cost=64,082.24..128,896.06 rows=225,465 width=273) (actual time=399.960..411.326 rows=2,151 loops=1)

  • Join Filter: (fcpy_1.paymenttype_key = dpty.paymenttype_key)
  • Rows Removed by Join Filter: 31427
19. 87.922 404.454 ↑ 104.8 2,151 1

Merge Join (cost=64,082.24..111,984.97 rows=225,465 width=213) (actual time=399.918..404.454 rows=2,151 loops=1)

  • Merge Cond: (dtrn.transaction_key = fcpy_1.transaction_key)
  • Join Filter: ((dtrn.transaction_key > 0) OR (fcpy_1.fact_type = ANY ('{RG,RU,CV}'::text[])))
  • Rows Removed by Join Filter: 6
20. 309.025 309.025 ↑ 1.0 965,925 1

Index Scan using pk_dim_transaction on dim_transaction dtrn (cost=0.42..40,170.71 rows=992,619 width=12) (actual time=0.011..309.025 rows=965,925 loops=1)

21. 0.455 7.507 ↑ 104.5 2,157 1

Materialize (cost=63,978.74..65,106.06 rows=225,465 width=205) (actual time=6.119..7.507 rows=2,157 loops=1)

22. 2.254 7.052 ↑ 104.5 2,157 1

Sort (cost=63,978.74..64,542.40 rows=225,465 width=205) (actual time=6.112..7.052 rows=2,157 loops=1)

  • Sort Key: fcpy_1.transaction_key
  • Sort Method: quicksort Memory: 400kB
23. 0.506 4.798 ↑ 104.5 2,157 1

Hash Left Join (cost=25.52..21,581.03 rows=225,465 width=205) (actual time=0.233..4.798 rows=2,157 loops=1)

  • Hash Cond: (fcpy_1.eventtype_key = devt.eventtype_key)
  • Join Filter: (fcpy_1.fact_type = ANY ('{PA,PP,TP}'::text[]))
  • Rows Removed by Join Filter: 55
24. 0.456 4.247 ↑ 104.5 2,157 1

Hash Left Join (cost=22.08..20,677.22 rows=225,465 width=188) (actual time=0.168..4.247 rows=2,157 loops=1)

  • Hash Cond: (fcpy_1.reduction_key = drdn.reduction_key)
  • Join Filter: (fcpy_1.fact_type = ANY ('{RG,RU}'::text[]))
25. 0.603 3.786 ↑ 104.5 2,157 1

Hash Left Join (cost=1.58..19,521.11 rows=225,465 width=171) (actual time=0.148..3.786 rows=2,157 loops=1)

  • Hash Cond: (fcpy_1.paymentresult_key = dprs.paymentresult_key)
26. 0.598 3.143 ↑ 104.5 2,157 1

Nested Loop (cost=0.43..17,922.91 rows=225,465 width=143) (actual time=0.082..3.143 rows=2,157 loops=1)

27. 0.013 0.013 ↑ 1.0 1 1

CTE Scan on functionarguments fa (cost=0.00..0.02 rows=1 width=16) (actual time=0.012..0.013 rows=1 loops=1)

28. 2.532 2.532 ↑ 104.5 2,157 1

Index Scan using fact_customerpayment_payment_date_fk on fact_customerpayment fcpy_1 (cost=0.43..15,668.24 rows=225,465 width=143) (actual time=0.066..2.532 rows=2,157 loops=1)

  • Index Cond: ((payment_date >= fa.start_tz) AND (payment_date < fa.end_tz))
  • Filter: (fact_type = ANY ('{CO,CV,PA,PH,PP,PU,RG,RU,SF,TP,TX}'::text[]))
  • Rows Removed by Filter: 56
29. 0.010 0.040 ↓ 2.7 8 1

Hash (cost=1.11..1.11 rows=3 width=36) (actual time=0.040..0.040 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.030 0.030 ↓ 2.7 8 1

Seq Scan on dim_paymentresult dprs (cost=0.00..1.11 rows=3 width=36) (actual time=0.027..0.030 rows=8 loops=1)

  • Filter: (paymentresult_key > 0)
  • Rows Removed by Filter: 1
31. 0.001 0.005 ↓ 0.0 0 1

Hash (cost=15.25..15.25 rows=420 width=21) (actual time=0.005..0.005 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
32. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on dim_reduction drdn (cost=0.00..15.25 rows=420 width=21) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: (reduction_key > 0)
33. 0.004 0.045 ↑ 2.0 1 1

Hash (cost=3.41..3.41 rows=2 width=25) (actual time=0.045..0.045 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.041 0.041 ↑ 2.0 1 1

Seq Scan on dim_eventtype devt (cost=0.00..3.41 rows=2 width=25) (actual time=0.021..0.041 rows=1 loops=1)

  • Filter: (eventtype_name = ANY ('{"Customer value payment","Reductions used"}'::text[]))
  • Rows Removed by Filter: 112
35. 2.123 2.151 ↓ 3.0 15 2,151

Materialize (cost=0.00..1.22 rows=5 width=68) (actual time=0.000..0.001 rows=15 loops=2,151)

36. 0.028 0.028 ↓ 3.0 15 1

Seq Scan on dim_paymenttype dpty (cost=0.00..1.20 rows=5 width=68) (actual time=0.026..0.028 rows=15 loops=1)

  • Filter: (paymenttype_key > 0)
  • Rows Removed by Filter: 1
37. 10.755 10.755 ↑ 4.0 1 2,151

Index Scan using fact_customerpayment_transaction_fk on fact_customerpayment fcpct (cost=0.43..0.73 rows=4 width=8) (actual time=0.004..0.005 rows=1 loops=2,151)

  • Index Cond: (fcpy_1.transaction_key = transaction_key)
  • Filter: (fact_type = 'PH'::text)
  • Rows Removed by Filter: 1
38. 0.016 0.111 ↓ 0.0 0 1

Materialize (cost=0.03..0.04 rows=1 width=12) (actual time=0.111..0.111 rows=0 loops=1)

39. 0.023 0.095 ↓ 0.0 0 1

Sort (cost=0.03..0.04 rows=1 width=12) (actual time=0.095..0.095 rows=0 loops=1)

  • Sort Key: fcpy_pucv.transaction_key
  • Sort Method: quicksort Memory: 25kB
40. 0.072 0.072 ↓ 0.0 0 1

CTE Scan on prepaid fcpy_pucv (cost=0.00..0.02 rows=1 width=12) (actual time=0.071..0.072 rows=0 loops=1)

41. 0.009 0.030 ↓ 2.5 5 1

Hash (cost=1.07..1.07 rows=2 width=64) (actual time=0.030..0.030 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 0.021 0.021 ↓ 2.5 5 1

Seq Scan on dim_currency dcur (cost=0.00..1.07 rows=2 width=64) (actual time=0.020..0.021 rows=5 loops=1)

  • Filter: (currency_key > 0)
  • Rows Removed by Filter: 1
43. 2.228 5.651 ↑ 1.0 10,789 1

Hash (cost=268.88..268.88 rows=10,789 width=12) (actual time=5.650..5.651 rows=10,789 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 592kB
44. 3.423 3.423 ↑ 1.0 10,789 1

Seq Scan on dim_shift dsft (cost=0.00..268.88 rows=10,789 width=12) (actual time=0.037..3.423 rows=10,789 loops=1)

  • Filter: (shift_key > 0)
  • Rows Removed by Filter: 1
45. 0.013 0.038 ↓ 2.8 37 1

Hash (cost=1.48..1.48 rows=13 width=36) (actual time=0.038..0.038 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
46. 0.025 0.025 ↓ 2.8 37 1

Seq Scan on dim_station dstn (cost=0.00..1.48 rows=13 width=36) (actual time=0.019..0.025 rows=37 loops=1)

  • Filter: (station_key > 0)
  • Rows Removed by Filter: 1
47. 0.021 0.081 ↑ 1.0 100 1

Hash (cost=3.26..3.26 rows=100 width=24) (actual time=0.081..0.081 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
48. 0.060 0.060 ↑ 1.0 100 1

Seq Scan on dim_company dcmp (cost=0.00..3.26 rows=100 width=24) (actual time=0.026..0.060 rows=100 loops=1)

  • Filter: (company_key > 0)
  • Rows Removed by Filter: 1
49. 0.016 0.043 ↓ 2.8 34 1

Hash (cost=1.44..1.44 rows=12 width=36) (actual time=0.043..0.043 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
50. 0.027 0.027 ↓ 2.8 34 1

Seq Scan on dim_customertype dcty (cost=0.00..1.44 rows=12 width=36) (actual time=0.022..0.027 rows=34 loops=1)

  • Filter: (customertype_key > 0)
  • Rows Removed by Filter: 1
51. 0.009 0.032 ↓ 2.5 10 1

Hash (cost=1.14..1.14 rows=4 width=36) (actual time=0.031..0.032 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 0.023 0.023 ↓ 2.5 10 1

Seq Scan on dim_tariffprofile dtpf (cost=0.00..1.14 rows=4 width=36) (actual time=0.021..0.023 rows=10 loops=1)

  • Filter: (tariffprofile_key > 0)
  • Rows Removed by Filter: 1
53. 0.000 0.020 ↓ 0.0 0 1

Hash (cost=1.01..1.01 rows=1 width=36) (actual time=0.020..0.020 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
54. 0.020 0.020 ↓ 0.0 0 1

Seq Scan on dim_tax dtax (cost=0.00..1.01 rows=1 width=36) (actual time=0.020..0.020 rows=0 loops=1)

  • Filter: (tax_key > 0)
  • Rows Removed by Filter: 1
Planning time : 13.463 ms
Execution time : 468.424 ms