explain.depesz.com

PostgreSQL's explain analyze made readable

Result: esN

Settings
# exclusive inclusive rows x rows loops node
1. 1,011.917 4,652.374 ↓ 0.0 116,127 1

Sort (cost=0.00..0.00 rows=0 width=0) (actual time=4,482.255..4,652.374 rows=116,127 loops=1)

  • Sort Key: remote_scan.tsym_id, remote_scan.fund_cd, remote_scan.uidd, remote_scan.bbfy, remote_scan.ebfy, remote_scan.patn_id, remote_scan.fund_ctrl_in, remote_scan.patn_cd, remote_scan.fund_id, remote_scan.budt_levl, remote_scan.fund_avab_pctg
  • Sort Method: external merge Disk: 36520kB
2. 1,306.388 3,640.457 ↓ 0.0 116,127 1

HashAggregate (cost=0.00..0.00 rows=0 width=0) (actual time=3,254.100..3,640.457 rows=116,127 loops=1)

  • Group Key: remote_scan.ata_id, remote_scan.aid_id, remote_scan.bpoa, remote_scan.epoa, remote_scan.aval_typ, remote_scan.main_acct, remote_scan.sub_acct, remote_scan.tsym_id, remote_scan.uidd, remote_scan.bbfy, remote_scan.ebfy, remote_scan.fund_cd, remote_scan.patn_id, remote_scan.fund_ctrl
3. 1,523.992 2,334.069 ↓ 0.0 116,127 1

Custom Scan (Citus Real-Time) (cost=0.00..0.00 rows=0 width=0) (actual time=2,311.623..2,334.069 rows=116,127 loops=1)

  • Task Count: 32
  • Tasks Shown: One of 32
  • -> Task
  • Node: host=ec2-174-129-96-158.compute-1.amazonaws.com port=5432 dbname=citus
4. 23.762 810.077 ↑ 1.0 3,687 1

GroupAggregate (cost=143,742.51..144,747.76 rows=3,689 width=1,482) (actual time=786.133..810.077 rows=3,687 loops=1)

  • Group Key: tsym.ata_id, tsym.aid_id, tsym.bpoa, tsym.epoa, tsym.aval_typ, tsym.main_acct, tsym.sub_acct, fund.tsym_id, appr.uidd, fund.bbfy, fund.ebfy, fund.cd, fund.patn_id, fund.fund_ctrl_in, patn.cd, appr.fund_id, appr.budt_levl, appr.fund_crcy_id, crcy.dec_symb, appr.fu
5. 33.733 786.315 ↑ 1.0 3,687 1

Sort (cost=143,742.51..143,751.73 rows=3,689 width=1,482) (actual time=786.091..786.315 rows=3,687 loops=1)

  • Sort Key: tsym.ata_id, tsym.aid_id, tsym.bpoa, tsym.epoa, tsym.aval_typ, tsym.main_acct, tsym.sub_acct, fund.tsym_id, appr.uidd, fund.bbfy, fund.ebfy, fund.cd, fund.patn_id, fund.fund_ctrl_in, patn.cd, appr.fund_id, appr.budt_levl, appr.fund_crcy_id, crcy.dec_symb, ap
  • Sort Method: quicksort Memory: 1095kB
6. 1.568 752.582 ↑ 1.0 3,687 1

Hash Left Join (cost=19,275.98..143,523.95 rows=3,689 width=1,482) (actual time=68.004..752.582 rows=3,687 loops=1)

  • Hash Cond: (appr.fund_crcy_id = crcy.uidy)
7. 1.474 750.921 ↑ 1.0 3,687 1

Hash Left Join (cost=19,258.51..143,455.76 rows=3,689 width=1,480) (actual time=67.902..750.921 rows=3,687 loops=1)

  • Hash Cond: (appr.patn_id = patn.uidy)
8. 1.490 749.283 ↑ 1.0 3,687 1

Hash Left Join (cost=19,238.86..143,385.38 rows=3,689 width=1,488) (actual time=67.724..749.283 rows=3,687 loops=1)

  • Hash Cond: (appr.scty_orgn_id = scty.uidy)
9. 4.169 747.045 ↑ 1.0 3,687 1

Hash Right Join (cost=19,135.23..143,231.03 rows=3,689 width=1,505) (actual time=66.962..747.045 rows=3,687 loops=1)

  • Hash Cond: ((mf_be_fisc_pd.parn_of_perd_uid = appr.uidd) AND (mf_fund.uidy = appr.fund_id))
10. 50.698 701.215 ↑ 94.3 3,687 1

Merge Join (cost=1,724.86..123,212.04 rows=347,766 width=1,337) (actual time=25.277..701.215 rows=3,687 loops=1)

  • Merge Cond: (mf_appr.uidd = mf_be_fisc_pd.parn_of_perd_uid)
11. 15.266 25.561 ↑ 1.0 3,689 1

Sort (cost=1,724.44..1,733.66 rows=3,689 width=67) (actual time=24.864..25.561 rows=3,689 loops=1)

  • Sort Key: mf_appr.uidd
  • Sort Method: quicksort Memory: 614kB
12. 0.977 10.295 ↑ 1.0 3,689 1

Hash Left Join (cost=462.68..1,505.89 rows=3,689 width=67) (actual time=3.018..10.295 rows=3,689 loops=1)

  • Hash Cond: ((mf_fund.bbfy = mf_be_optn.fisc_year) AND (mf_fund.patn_id = mf_be_optn.patn_id))
13. 0.444 9.271 ↑ 1.0 3,689 1

Hash Left Join (cost=456.68..1,425.86 rows=3,689 width=79) (actual time=2.960..9.271 rows=3,689 loops=1)

  • Hash Cond: (mf_fund.actg_cat_id = fundcat.uidy)
14. 0.461 8.803 ↑ 1.0 3,689 1

Hash Left Join (cost=454.33..1,409.28 rows=3,689 width=97) (actual time=2.923..8.803 rows=3,689 loops=1)

  • Hash Cond: (mf_fund.actg_clas_id = fundclas.uidy)
15. 0.479 8.326 ↑ 1.0 3,689 1

Hash Left Join (cost=438.93..1,273.66 rows=3,689 width=116) (actual time=2.896..8.326 rows=3,689 loops=1)

  • Hash Cond: (mf_fund.actg_typ_id = fundtyp.uidy)
16. 0.521 7.821 ↑ 1.0 3,689 1

Hash Left Join (cost=435.40..1,255.97 rows=3,689 width=135) (actual time=2.860..7.821 rows=3,689 loops=1)

  • Hash Cond: (mf_fund.actg_grp_id = fundgrp.uidy)
17. 3.294 7.279 ↑ 1.0 3,689 1

Hash Join (cost=420.00..1,118.19 rows=3,689 width=152) (actual time=2.827..7.279 rows=3,689 loops=1)

  • Hash Cond: (mf_fund.uidy = mf_appr.fund_id)
18. 1.188 1.188 ↑ 1.0 9,185 1

Seq Scan on mf_fund_102823 mf_fund (cost=0.00..626.85 rows=9,185 width=135) (actual time=0.006..1.188 rows=9,185 loops=1)

19. 0.814 2.797 ↑ 1.0 3,689 1

Hash (cost=373.89..373.89 rows=3,689 width=57) (actual time=2.796..2.797 rows=3,689 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 355kB
20. 1.983 1.983 ↑ 1.0 3,689 1

Seq Scan on mf_appr_102858 mf_appr (cost=0.00..373.89 rows=3,689 width=57) (actual time=0.006..1.983 rows=3,689 loops=1)

21. 0.013 0.021 ↑ 8.3 29 1

Hash (cost=12.40..12.40 rows=240 width=32) (actual time=0.021..0.021 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
22. 0.008 0.008 ↑ 8.3 29 1

Seq Scan on mf_fund_grp_102820 fundgrp (cost=0.00..12.40 rows=240 width=32) (actual time=0.005..0.008 rows=29 loops=1)

23. 0.015 0.026 ↑ 1.0 68 1

Hash (cost=2.68..2.68 rows=68 width=19) (actual time=0.026..0.026 rows=68 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
24. 0.011 0.011 ↑ 1.0 68 1

Seq Scan on mf_fund_type_102819 fundtyp (cost=0.00..2.68 rows=68 width=19) (actual time=0.005..0.011 rows=68 loops=1)

25. 0.010 0.016 ↑ 7.1 34 1

Hash (cost=12.40..12.40 rows=240 width=32) (actual time=0.016..0.016 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
26. 0.006 0.006 ↑ 7.1 34 1

Seq Scan on mf_fund_clas_102821 fundclas (cost=0.00..12.40 rows=240 width=32) (actual time=0.003..0.006 rows=34 loops=1)

27. 0.015 0.024 ↑ 1.0 60 1

Hash (cost=1.60..1.60 rows=60 width=18) (actual time=0.024..0.024 rows=60 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
28. 0.009 0.009 ↑ 1.0 60 1

Seq Scan on mf_fund_cat_102822 fundcat (cost=0.00..1.60 rows=60 width=18) (actual time=0.005..0.009 rows=60 loops=1)

29. 0.024 0.047 ↑ 2.4 68 1

Hash (cost=3.60..3.60 rows=160 width=23) (actual time=0.047..0.047 rows=68 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
30. 0.023 0.023 ↑ 1.0 160 1

Seq Scan on mf_be_optn_102856 mf_be_optn (cost=0.00..3.60 rows=160 width=23) (actual time=0.005..0.023 rows=160 loops=1)

31. 77.514 624.956 ↑ 3.0 116,088 1

Subquery Scan on mf_be_fisc_pd (cost=0.42..114,513.83 rows=347,766 width=1,133) (actual time=0.058..624.956 rows=116,088 loops=1)

32. 453.835 547.442 ↑ 3.0 116,088 1

GroupAggregate (cost=0.42..105,819.68 rows=347,766 width=1,069) (actual time=0.053..547.442 rows=116,088 loops=1)

  • Group Key: mf_be_fisc_pd_1.parn_of_perd_uid
33. 93.607 93.607 ↑ 3.9 124,130 1

Index Scan using idx_mf_be_fisc_pd_1_102857 on mf_be_fisc_pd_102857 mf_be_fisc_pd_1 (cost=0.42..32,002.18 rows=489,990 width=120) (actual time=0.013..93.607 rows=124,130 loops=1)

34. 2.238 41.661 ↑ 1.0 3,687 1

Hash (cost=17,355.03..17,355.03 rows=3,689 width=215) (actual time=41.661..41.661 rows=3,687 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 897kB
35. 0.688 39.423 ↑ 1.0 3,687 1

Nested Loop (cost=420.56..17,355.03 rows=3,689 width=215) (actual time=4.278..39.423 rows=3,687 loops=1)

36. 3.813 9.223 ↑ 1.0 3,689 1

Hash Join (cost=420.00..1,118.19 rows=3,689 width=206) (actual time=4.245..9.223 rows=3,689 loops=1)

  • Hash Cond: (fund.uidy = appr.fund_id)
37. 1.204 1.204 ↑ 1.0 9,185 1

Seq Scan on mf_fund_102823 fund (cost=0.00..626.85 rows=9,185 width=103) (actual time=0.010..1.204 rows=9,185 loops=1)

38. 1.328 4.206 ↑ 1.0 3,689 1

Hash (cost=373.89..373.89 rows=3,689 width=137) (actual time=4.206..4.206 rows=3,689 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 650kB
39. 2.878 2.878 ↑ 1.0 3,689 1

Seq Scan on mf_appr_102858 appr (cost=0.00..373.89 rows=3,689 width=137) (actual time=0.009..2.878 rows=3,689 loops=1)

40. 29.512 29.512 ↑ 1.0 1 3,689

Index Scan using idx_mf_tsym_1_102783 on mf_tsym_102783 tsym (cost=0.56..4.39 rows=1 width=74) (actual time=0.008..0.008 rows=1 loops=3,689)

  • Index Cond: (uidy = appr.tsym_id)
41. 0.452 0.748 ↑ 1.0 2,828 1

Hash (cost=68.28..68.28 rows=2,828 width=14) (actual time=0.748..0.748 rows=2,828 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 161kB
42. 0.296 0.296 ↑ 1.0 2,828 1

Seq Scan on mf_scty_orgn_102785 scty (cost=0.00..68.28 rows=2,828 width=14) (actual time=0.008..0.296 rows=2,828 loops=1)

43. 0.090 0.164 ↑ 1.0 518 1

Hash (cost=13.18..13.18 rows=518 width=16) (actual time=0.164..0.164 rows=518 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
44. 0.074 0.074 ↑ 1.0 518 1

Seq Scan on mf_patn_102818 patn (cost=0.00..13.18 rows=518 width=16) (actual time=0.006..0.074 rows=518 loops=1)

45. 0.038 0.093 ↑ 1.0 243 1

Hash (cost=14.43..14.43 rows=243 width=11) (actual time=0.092..0.093 rows=243 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
46. 0.055 0.055 ↑ 1.0 243 1

Seq Scan on mf_crcy_102855 crcy (cost=0.00..14.43 rows=243 width=11) (actual time=0.006..0.055 rows=243 loops=1)

Planning time : 176.048 ms
Execution time : 4,707.232 ms