explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dMGj : getfolios

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 778,876.192 ↓ 3.0 3 1

Nested Loop Left Join (cost=63,082.52..63,090.61 rows=1 width=388) (actual time=778,876.176..778,876.192 rows=3 loops=1)

2.          

CTE schemes

3. 0.013 778,852.369 ↑ 66.7 3 1

GroupAggregate (cost=62,771.52..62,781.71 rows=200 width=39) (actual time=778,852.362..778,852.369 rows=3 loops=1)

  • Group Key: folio_trxn_brok.folio_no
4. 0.008 778,852.356 ↑ 128.1 8 1

Sort (cost=62,771.52..62,774.08 rows=1,025 width=10) (actual time=778,852.355..778,852.356 rows=8 loops=1)

  • Sort Key: folio_trxn_brok.folio_no
  • Sort Method: quicksort Memory: 25kB
5. 0.002 778,852.348 ↑ 128.1 8 1

Subquery Scan on folio_trxn_brok (cost=62,702.33..62,720.26 rows=1,025 width=10) (actual time=778,852.344..778,852.348 rows=8 loops=1)

6. 0.002 778,852.346 ↑ 128.1 8 1

Unique (cost=62,702.33..62,710.01 rows=1,025 width=10) (actual time=778,852.344..778,852.346 rows=8 loops=1)

7. 0.014 778,852.344 ↑ 128.1 8 1

Sort (cost=62,702.33..62,704.89 rows=1,025 width=10) (actual time=778,852.343..778,852.344 rows=8 loops=1)

  • Sort Key: pt.sch_code, pt.folio_no
  • Sort Method: quicksort Memory: 25kB
8. 0.030 778,852.330 ↑ 128.1 8 1

Group (cost=62,643.38..62,651.07 rows=1,025 width=10) (actual time=778,852.289..778,852.330 rows=8 loops=1)

  • Group Key: pt.folio_no, pt.sch_code
9. 0.571 778,852.300 ↑ 3.7 276 1

Sort (cost=62,643.38..62,645.94 rows=1,025 width=10) (actual time=778,852.288..778,852.300 rows=276 loops=1)

  • Sort Key: pt.folio_no, pt.sch_code
  • Sort Method: quicksort Memory: 37kB
10. 3,004.213 778,851.729 ↑ 3.7 276 1

Nested Loop (cost=283.72..62,592.12 rows=1,025 width=10) (actual time=21,982.631..778,851.729 rows=276 loops=1)

11. 1,451.867 690,902.474 ↓ 1,720.4 4,719,169 1

Nested Loop (cost=283.29..52,710.29 rows=2,743 width=10) (actual time=833.177..690,902.474 rows=4,719,169 loops=1)

12. 16.272 19.296 ↓ 51.0 51 1

Bitmap Heap Scan on r_broker_master bm (cost=48.29..133.38 rows=1 width=10) (actual time=13.644..19.296 rows=51 loops=1)

  • Recheck Cond: ((amfi_regn_no)::text = 'ARN-9992'::text)
  • Filter: ((broker_code)::text = (coalescenonempty(NULL::character varying, broker_code))::text)
  • Heap Blocks: exact=64
13. 3.024 3.024 ↓ 1.7 66 1

Bitmap Index Scan on i190413102749790650722 (cost=0.00..48.29 rows=39 width=0) (actual time=3.024..3.024 rows=66 loops=1)

  • Index Cond: ((amfi_regn_no)::text = 'ARN-9992'::text)
14. 688,350.519 689,431.311 ↓ 3.5 92,533 51

Bitmap Heap Scan on r_processed_trxns pt (cost=234.99..52,309.83 rows=26,709 width=19) (actual time=34.095..13,518.261 rows=92,533 loops=51)

  • Recheck Cond: ((broker_code)::text = (bm.broker_code)::text)
  • Heap Blocks: exact=2434630
15. 1,080.792 1,080.792 ↓ 3.5 92,533 51

Bitmap Index Scan on i190619225056804873894 (cost=0.00..228.32 rows=26,709 width=0) (actual time=21.192..21.192 rows=92,533 loops=51)

  • Index Cond: ((broker_code)::text = (bm.broker_code)::text)
16. 84,945.042 84,945.042 ↓ 0.0 0 4,719,169

Index Scan using i190620105715101520557 on r_customer_master cm_1 (cost=0.43..3.60 rows=1 width=7) (actual time=0.018..0.018 rows=0 loops=4,719,169)

  • Index Cond: ((folio_no)::text = (pt.folio_no)::text)
  • Filter: (((valid_flag IS NULL) OR ((valid_flag)::text = 'Y'::text)) AND ((ssip_plan_period IS NULL) OR (ssip_plan_period = 0)) AND CASE inv_type WHEN '02'::text THEN ((guardian_panno)::text = 'ATQPP6186M'::text) WHEN '28'::text THEN ((guardian_panno)::text = 'ATQPP6186M'::text) ELSE ((pan_no)::text = 'ATQPP6186M'::text) END)
  • Rows Removed by Filter: 1
17. 0.003 778,876.184 ↓ 3.0 3 1

Nested Loop Left Join (cost=300.39..304.45 rows=1 width=386) (actual time=778,876.169..778,876.184 rows=3 loops=1)

18. 0.002 778,875.209 ↓ 3.0 3 1

Subquery Scan on pri (cost=299.96..300.01 rows=1 width=384) (actual time=778,875.204..778,875.209 rows=3 loops=1)

  • Filter: ((pri.joint_no IS NULL) OR (pri.joint_no = 1))
19. 0.012 778,875.207 ↓ 3.0 3 1

WindowAgg (cost=299.96..300.00 rows=1 width=386) (actual time=778,875.203..778,875.207 rows=3 loops=1)

20. 0.023 778,875.195 ↓ 3.0 3 1

Sort (cost=299.96..299.97 rows=1 width=194) (actual time=778,875.194..778,875.195 rows=3 loops=1)

  • Sort Key: cust_holder_profile.folio_no, cust_holder_profile.joint_no
  • Sort Method: quicksort Memory: 25kB
21. 0.003 778,875.172 ↓ 3.0 3 1

Subquery Scan on cust_holder_profile (cost=299.88..299.95 rows=1 width=194) (actual time=778,875.165..778,875.172 rows=3 loops=1)

22. 0.006 778,875.169 ↓ 3.0 3 1

Unique (cost=299.88..299.94 rows=1 width=194) (actual time=778,875.163..778,875.169 rows=3 loops=1)

23. 0.059 778,875.163 ↓ 8.0 8 1

Sort (cost=299.88..299.89 rows=1 width=194) (actual time=778,875.162..778,875.163 rows=8 loops=1)

  • Sort Key: cm.folio_no, cm.pan_no, p_1.investor_data, cm.first_name, jh.jfirst_name, jh.tax_no, jh.joint_no, cm.inv_type, cm.birth_date, cm.mobile_no, cm.email, cm.fh_kyc, cs.hold_mode_code, s.sch_codes, cm.guardian_panno, cm.fh_kyc_type, cm.g_kyc_type, cm.guar_kyc, cm.guardian_name, cm.pin_enabled, cm.folio_create_date
  • Sort Method: quicksort Memory: 27kB
24. 0.009 778,875.104 ↓ 8.0 8 1

Nested Loop Left Join (cost=287.04..299.87 rows=1 width=194) (actual time=778,865.836..778,875.104 rows=8 loops=1)

25. 0.006 778,875.095 ↓ 8.0 8 1

Nested Loop Left Join (cost=286.62..299.21 rows=1 width=192) (actual time=778,865.833..778,875.095 rows=8 loops=1)

26. 0.016 778,873.201 ↓ 8.0 8 1

Nested Loop (cost=286.19..294.76 rows=1 width=162) (actual time=778,864.837..778,873.201 rows=8 loops=1)

  • Join Filter: ((cm.folio_no)::text = (cs.folio_no)::text)
27. 0.020 778,859.550 ↓ 3.0 3 1

Hash Join (cost=182.50..187.02 rows=1 width=198) (actual time=778,859.537..778,859.550 rows=3 loops=1)

  • Hash Cond: ((s.folio_no)::text = (cm.folio_no)::text)
28. 778,852.374 778,852.374 ↑ 66.7 3 1

CTE Scan on schemes s (cost=0.00..4.00 rows=200 width=70) (actual time=778,852.364..778,852.374 rows=3 loops=1)

29. 0.005 7.156 ↑ 1.0 3 1

Hash (cost=182.46..182.46 rows=3 width=128) (actual time=7.156..7.156 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.607 7.151 ↑ 1.0 3 1

Bitmap Heap Scan on r_customer_master cm (cost=132.19..182.46 rows=3 width=128) (actual time=6.553..7.151 rows=3 loops=1)

  • Recheck Cond: (((guardian_panno)::text = 'ATQPP6186M'::text) OR ((pan_no)::text = 'ATQPP6186M'::text))
  • Filter: ((((inv_type)::text = ANY ('{02,28}'::text[])) AND ((guardian_panno)::text = 'ATQPP6186M'::text)) OR (((inv_type)::text <> ALL ('{02,28}'::text[])) AND ((pan_no)::text = 'ATQPP6186M'::text)))
  • Heap Blocks: exact=5
31. 0.001 6.544 ↓ 0.0 0 1

BitmapOr (cost=132.19..132.19 rows=25 width=0) (actual time=6.544..6.544 rows=0 loops=1)

32. 2.884 2.884 ↓ 0.0 0 1

Bitmap Index Scan on i190620105625549945696 (cost=0.00..64.16 rows=22 width=0) (actual time=2.884..2.884 rows=0 loops=1)

  • Index Cond: ((guardian_panno)::text = 'ATQPP6186M'::text)
33. 3.659 3.659 ↓ 1.7 5 1

Bitmap Index Scan on i190620105529075623648 (cost=0.00..68.02 rows=3 width=0) (actual time=3.658..3.659 rows=5 loops=1)

  • Index Cond: ((pan_no)::text = 'ATQPP6186M'::text)
34. 2.718 13.635 ↓ 1.5 3 3

Bitmap Heap Scan on r_customer_schemes cs (cost=103.70..107.72 rows=2 width=9) (actual time=3.947..4.545 rows=3 loops=3)

  • Recheck Cond: ((folio_no)::text = (s.folio_no)::text)
  • Heap Blocks: exact=8
35. 10.917 10.917 ↓ 1.5 3 3

Bitmap Index Scan on i190619101209387442342 (cost=0.00..103.70 rows=2 width=0) (actual time=3.639..3.639 rows=3 loops=3)

  • Index Cond: ((folio_no)::text = (s.folio_no)::text)
36. 1.888 1.888 ↓ 0.0 0 8

Index Scan using i190415011311306449002 on r_joint_holders jh (cost=0.42..4.44 rows=1 width=37) (actual time=0.236..0.236 rows=0 loops=8)

  • Index Cond: ((cm.folio_no)::text = (folio_no)::text)
37. 0.000 0.000 ↓ 0.0 0 8

Index Scan using idx_profiles_pk on profiles p_1 (cost=0.42..0.66 rows=1 width=13) (actual time=0.000..0.000 rows=0 loops=8)

  • Index Cond: (((jh.tax_no)::text = (id)::text) AND ((type)::text = 'INV'::text))
38. 0.972 0.972 ↑ 1.0 1 3

Index Scan using idx_profiles_pk on profiles p (cost=0.42..4.44 rows=1 width=13) (actual time=0.324..0.324 rows=1 loops=3)

  • Index Cond: (((pri.pan_no)::text = (id)::text) AND ((type)::text = 'INV'::text))
39. 0.003 0.003 ↓ 0.0 0 3

Index Scan using idx_profiles_pk on profiles pg (cost=0.42..4.44 rows=1 width=13) (actual time=0.001..0.001 rows=0 loops=3)

  • Index Cond: (((pri.guardian_panno)::text = (id)::text) AND ((type)::text = 'INV'::text))
Planning time : 17.751 ms
Execution time : 778,880.593 ms