explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FTyS : arnab

Settings
# exclusive inclusive rows x rows loops node
1. 1,126.385 62,548.042 ↓ 10.4 604,124 1

HashAggregate (cost=606,395.59..606,976.48 rows=58,089 width=376) (actual time=62,081.167..62,548.042 rows=604,124 loops=1)

  • Group Key: (to_number(((((edb_ora_timestamp_tochar_immutable(a.token_date, 'YYYYMMDD'::character varying))::text || '0431'::text) || (a.int_treasury_code)::text) || lpad((a.token_number)::text, 14, '0'::text)))), (to_number(((((edb_ora_timestamp_tochar_immutable(b.bill_date, 'YYYYMMDD'::character varying))::text || '0530'::text) || (b.int_treasury_code)::text) || lpad((b.ref_no)::text, 14, '0'::text)))), (to_number(((((edb_ora_timestamp_tochar_immutable(b.sanction_date, 'YYYYMMDD'::character varying))::text || '0532'::text) || (b.int_treasury_code)::text) || lpad((d.sanction_number)::text, 14, '0'::text)))), b.ref_no, a.concat_token_number, b.sanction_number
2. 49.234 61,421.657 ↓ 10.4 604,124 1

Append (cost=291,209.69..605,524.25 rows=58,089 width=376) (actual time=58,348.090..61,421.657 rows=604,124 loops=1)

3. 872.262 59,645.327 ↓ 543,024.0 543,024 1

Unique (cost=291,209.69..291,209.70 rows=1 width=131) (actual time=58,348.088..59,645.327 rows=543,024 loops=1)

4. 3,716.143 58,773.065 ↓ 0.0 1,895,096 1

Sort (cost=291,209.69..291,209.69 rows=0 width=131) (actual time=58,348.084..58,773.065 rows=1,895,096 loops=1)

  • Sort Key: (to_number(((((edb_ora_timestamp_tochar_immutable(a.token_date, 'YYYYMMDD'::character varying))::text || '0431'::text) || (a.int_treasury_code)::text) || lpad((a.token_number)::text, 14, '0'::text)))), (to_number(((((edb_ora_timestamp_tochar_immutable(b.bill_date, 'YYYYMMDD'::character varying))::text || '0530'::text) || (b.int_treasury_code)::text) || lpad((b.ref_no)::text, 14, '0'::text)))), (to_number(((((edb_ora_timestamp_tochar_immutable(b.sanction_date, 'YYYYMMDD'::character varying))::text || '0532'::text) || (b.int_treasury_code)::text) || lpad((d.sanction_number)::text, 14, '0'::text)))), b.ref_no, a.concat_token_number, b.sanction_number
  • Sort Method: quicksort Memory: 315,650kB
5. 24,840.189 55,056.922 ↓ 0.0 1,895,096 1

Nested Loop (cost=1,001.97..291,209.68 rows=0 width=131) (actual time=3.674..55,056.922 rows=1,895,096 loops=1)

6. 430.959 22,549.003 ↓ 0.0 547,695 1

Nested Loop Anti Join (cost=1,001.41..291,203.89 rows=0 width=93) (actual time=3.236..22,549.003 rows=547,695 loops=1)

7. 842.439 11,711.839 ↓ 0.0 547,695 1

Nested Loop (cost=1,000.85..291,201.51 rows=0 width=93) (actual time=3.132..11,711.839 rows=547,695 loops=1)

8. 0.000 1,897.479 ↓ 1,281,703.0 1,281,703 1

Gather (cost=1,000.43..291,201.02 rows=1 width=82) (actual time=2.245..1,897.479 rows=1,281,703 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
9. 187.001 4,846.370 ↓ 0.0 213,617 6 / 6

Nested Loop (cost=0.43..290,200.92 rows=0 width=82) (actual time=0.318..4,846.370 rows=213,617 loops=6)

10. 1,202.501 1,202.501 ↓ 4.3 230,458 6 / 6

Parallel Seq Scan on cfms_treasury_bill_hdr a (cost=0.00..191,588.70 rows=53,934 width=42) (actual time=0.051..1,202.501 rows=230,458 loops=6)

  • Filter: (((role_processing_flag)::text = '75'::text) AND ((active_flag)::text = 'Y'::text))
  • Rows Removed by Filter: 1,136,142
11. 3,456.867 3,456.867 ↑ 1.0 1 1,382,747 / 6

Index Scan using idx_cfms_b_tm_bill_hdr_2 on cfms_b_tm_bill_hdr b (cost=0.43..1.82 rows=1 width=63) (actual time=0.014..0.015 rows=1 loops=1,382,747)

  • Index Cond: (ref_no = a.ref_no)
  • Filter: (((a.int_treasury_code)::text = (int_treasury_code)::text) AND (a.token_number = token_number) AND (trunc(a.token_date) = trunc(token_date)))
  • Rows Removed by Filter: 0
12. 8,971.921 8,971.921 ↓ 0.0 0 1,281,703

Index Only Scan using ind_b_td_pay_sanction_sanction_number on b_td_pay_sanction d (cost=0.42..0.48 rows=1 width=11) (actual time=0.007..0.007 rows=0 loops=1,281,703)

  • Index Cond: (sanction_number = (b.sanction_number)::text)
  • Heap Fetches: 1,056,430
13. 10,406.205 10,406.205 ↓ 0.0 0 547,695

Index Only Scan using cfms_treasury_bill_hdr_idx1 on cfms_treasury_bill_hdr (cost=0.56..2.08 rows=1 width=15) (actual time=0.019..0.019 rows=0 loops=547,695)

  • Index Cond: ((concat_token_number = (a.concat_token_number)::text) AND (role_processing_flag < '0'::text))
  • Heap Fetches: 0
14. 7,667.730 7,667.730 ↑ 62.7 3 547,695

Index Scan using idx_cfms_t_td_pay_beneficiary_dtl_billhdrid on cfms_t_td_pay_beneficiary_dtl c (cost=0.56..5.38 rows=188 width=10) (actual time=0.010..0.014 rows=3 loops=547,695)

  • Index Cond: (bill_hdr_id = b.bill_hdr_id)
  • Filter: ((payment_status)::text = 'S'::text)
  • Rows Removed by Filter: 0
15. 1.394 770.190 ↓ 779.0 779 1

Unique (cost=181,334.68..181,334.70 rows=1 width=131) (actual time=768.480..770.190 rows=779 loops=1)

16. 5.300 768.796 ↓ 2,788.0 2,788 1

Sort (cost=181,334.68..181,334.68 rows=1 width=131) (actual time=768.476..768.796 rows=2,788 loops=1)

  • Sort Key: (to_number(((((edb_ora_timestamp_tochar_immutable(a_1.token_date, 'YYYYMMDD'::character varying))::text || '0431'::text) || (a_1.int_treasury_code)::text) || lpad((a_1.token_number)::text, 14, '0'::text)))), (to_number(((((edb_ora_timestamp_tochar_immutable(b_1.bill_date, 'YYYYMMDD'::character varying))::text || '0530'::text) || (b_1.int_treasury_code)::text) || lpad((b_1.ref_no)::text, 14, '0'::text)))), (to_number(((((edb_ora_timestamp_tochar_immutable(b_1.sanction_date, 'YYYYMMDD'::character varying))::text || '0532'::text) || (b_1.int_treasury_code)::text) || lpad((d_1.sanction_number)::text, 14, '0'::text)))), b_1.ref_no, a_1.concat_token_number, b_1.sanction_number
  • Sort Method: quicksort Memory: 489kB
17. 16.039 763.496 ↓ 2,788.0 2,788 1

Gather (cost=1,000.99..181,334.67 rows=1 width=131) (actual time=1.351..763.496 rows=2,788 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
18. 16.882 747.457 ↓ 0.0 465 6 / 6

Nested Loop (cost=0.99..180,334.52 rows=0 width=131) (actual time=1.475..747.457 rows=465 loops=6)

19. 3.778 720.521 ↑ 1.2 10,054 6 / 6

Nested Loop (cost=0.42..119,926.79 rows=11,617 width=64) (actual time=0.283..720.521 rows=10,054 loops=6)

20. 320.373 320.373 ↑ 1.5 18,017 6 / 6

Parallel Seq Scan on cfms_b_tm_bill_hdr b_1 (cost=0.00..94,894.37 rows=26,930 width=53) (actual time=0.045..320.373 rows=18,017 loops=6)

  • Filter: (dml_status_flag = '2'::numeric)
  • Rows Removed by Filter: 224,791
21. 396.370 396.370 ↑ 1.0 1 108,101 / 6

Index Only Scan using ind_b_td_pay_sanction_sanction_number on b_td_pay_sanction d_1 (cost=0.42..0.92 rows=1 width=11) (actual time=0.022..0.022 rows=1 loops=108,101)

  • Index Cond: (sanction_number = (b_1.sanction_number)::text)
  • Heap Fetches: 40,536
22. 10.053 10.053 ↓ 0.0 0 60,321 / 6

Index Scan using cfms_treasury_bill_hdr_idx3 on cfms_treasury_bill_hdr a_1 (cost=0.56..5.19 rows=1 width=42) (actual time=0.001..0.001 rows=0 loops=60,321)

  • Index Cond: ((ref_no = b_1.ref_no) AND (token_number = b_1.token_number) AND (trunc(token_date) = trunc(b_1.token_date)))
  • Filter: ((b_1.int_treasury_code)::text = (int_treasury_code)::text)
23. 12.921 956.906 ↓ 1.0 60,321 1

Subquery Scan on *SELECT* 3 (cost=129,639.84..132,979.84 rows=58,087 width=148) (actual time=915.302..956.906 rows=60,321 loops=1)

24. 81.060 943.985 ↓ 1.0 60,321 1

HashAggregate (cost=129,639.84..132,253.75 rows=58,087 width=120) (actual time=915.266..943.985 rows=60,321 loops=1)

  • Group Key: (0), (to_number(((((edb_ora_timestamp_tochar_immutable(b_2.bill_date, 'YYYYMMDD'::character varying))::text || '0530'::text) || (b_2.int_treasury_code)::text) || lpad((b_2.ref_no)::text, 14, '0'::text)))), (to_number(((((edb_ora_timestamp_tochar_immutable(b_2.sanction_date, 'YYYYMMDD'::character varying))::text || '0532'::text) || (b_2.int_treasury_code)::text) || lpad((d_2.sanction_number)::text, 14, '0'::text)))), b_2.ref_no, ('0'::text), b_2.sanction_number
25. 0.000 862.925 ↓ 1.0 60,321 1

Gather (cost=1,000.42..128,768.53 rows=58,087 width=120) (actual time=1.014..862.925 rows=60,321 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
26. 152.814 875.156 ↑ 1.2 10,054 6 / 6

Nested Loop (cost=0.42..120,333.39 rows=11,617 width=120) (actual time=0.531..875.156 rows=10,054 loops=6)

27. 325.972 325.972 ↑ 1.5 18,017 6 / 6

Parallel Seq Scan on cfms_b_tm_bill_hdr b_2 (cost=0.00..94,894.37 rows=26,930 width=40) (actual time=0.048..325.972 rows=18,017 loops=6)

  • Filter: (dml_status_flag = '2'::numeric)
  • Rows Removed by Filter: 224,791
28. 396.370 396.370 ↑ 1.0 1 108,101 / 6

Index Only Scan using ind_b_td_pay_sanction_sanction_number on b_td_pay_sanction d_2 (cost=0.42..0.92 rows=1 width=11) (actual time=0.022..0.022 rows=1 loops=108,101)

  • Index Cond: (sanction_number = (b_2.sanction_number)::text)
  • Heap Fetches: 38,830
Planning time : 9.723 ms
Execution time : 62,643.262 ms