explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pn7d : Optimization for: test2; plan #HK9X

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.001 200,143.412 ↓ 0.0 0 1

Nested Loop (cost=4,113,278.27..4,113,456.20 rows=1 width=91) (actual time=200,143.412..200,143.412 rows=0 loops=1)

2. 0.003 200,143.411 ↓ 0.0 0 1

GroupAggregate (cost=4,113,277.70..4,113,277.74 rows=1 width=82) (actual time=200,143.411..200,143.411 rows=0 loops=1)

  • Group Key: tm.source_system_cd, tm.source_transaction_id, tm.source_product_id, tm.transaction_cd, tm.reversal_cd, ctd.detail_cd
3. 0.027 200,143.408 ↓ 0.0 0 1

Sort (cost=4,113,277.70..4,113,277.71 rows=1 width=87) (actual time=200,143.408..200,143.408 rows=0 loops=1)

  • Sort Key: tm.source_system_cd, tm.source_transaction_id, tm.source_product_id, tm.transaction_cd, tm.reversal_cd, ctd.detail_cd
  • Sort Method: quicksort Memory: 25kB
4. 0.001 200,143.381 ↓ 0.0 0 1

Nested Loop Left Join (cost=3,819,497.00..4,113,277.69 rows=1 width=87) (actual time=200,143.381..200,143.381 rows=0 loops=1)

  • Join Filter: (((tm.source_product_id)::text = (tm_2.source_product_id)::text) AND ((tm.source_system_cd)::text = (tm_2.source_system_cd)::text) AND ((ctd.detail_cd)::text = (td.detail_cd)::text))
5. 0.003 200,143.380 ↓ 0.0 0 1

Nested Loop (cost=1,872,755.74..2,166,536.37 rows=1 width=55) (actual time=200,143.380..200,143.380 rows=0 loops=1)

6. 10.893 200,143.377 ↓ 0.0 0 1

Nested Loop (cost=1,872,755.17..2,164,889.25 rows=1 width=42) (actual time=200,143.377..200,143.377 rows=0 loops=1)

  • Join Filter: (((tm.source_product_id)::text = (transaction_master.source_product_id)::text) AND ((tm.source_system_cd)::text = (transaction_master.source_system_cd)::text))
  • Rows Removed by Join Filter: 114392
7. 710.281 199,105.484 ↓ 632.0 632 1

Subquery Scan on tm (cost=1,868,965.39..2,161,099.44 rows=1 width=42) (actual time=191,303.887..199,105.484 rows=632 loops=1)

  • Filter: ((tm.etl_modified_dttm >= '2019-06-11 00:00:00'::timestamp without time zone) AND ((tm.product_status_cd_current)::text = 'NONCURRENT'::text) AND ((tm.product_status_cd_previous)::text = 'CURRENT'::text) AND (tm.etl_modified_dttm <= ('2019-06-11 00:00:00'::timestamp without time zone + 0.99999)))
  • Rows Removed by Filter: 5035021
8. 2,579.805 198,395.203 ↑ 1.2 5,035,653 1

WindowAgg (cost=1,868,965.39..2,029,639.12 rows=5,842,681 width=129) (actual time=190,334.615..198,395.203 rows=5,035,653 loops=1)

9. 30,079.863 195,815.398 ↑ 1.2 5,035,653 1

Sort (cost=1,868,965.39..1,883,572.10 rows=5,842,681 width=97) (actual time=190,334.597..195,815.398 rows=5,035,653 loops=1)

  • Sort Key: tm_1.source_product_id, (to_number(btrim((tm_1.source_transaction_id)::text, 'QFX'::text))), tm_1.transaction_dttm
  • Sort Method: external merge Disk: 422104kB
10. 165,735.535 165,735.535 ↑ 1.2 5,035,653 1

Index Scan using transaction_master_filt_qfund_idx on transaction_master tm_1 (cost=0.43..650,854.86 rows=5,842,681 width=97) (actual time=0.161..165,735.535 rows=5,035,653 loops=1)

  • Filter: (source_deleted_flg = 'N'::bpchar)
  • Rows Removed by Filter: 1186
11. 17.696 1,027.000 ↓ 181.0 181 632

Unique (cost=3,789.77..3,789.78 rows=1 width=19) (actual time=1.584..1.625 rows=181 loops=632)

12. 8.688 1,009.304 ↓ 204.0 204 632

Sort (cost=3,789.77..3,789.78 rows=1 width=19) (actual time=1.584..1.597 rows=204 loops=632)

  • Sort Key: transaction_master.source_product_id
  • Sort Method: quicksort Memory: 34kB
13. 12.498 1,000.616 ↓ 204.0 204 1

Bitmap Heap Scan on transaction_master (cost=3,724.67..3,789.76 rows=1 width=19) (actual time=992.329..1,000.616 rows=204 loops=1)

  • Recheck Cond: ((etl_modified_dttm >= '2019-06-11 00:00:00'::timestamp without time zone) AND (etl_modified_dttm <= ('2019-06-11 00:00:00'::timestamp without time zone + 0.99999)) AND ((product_category_cd)::text = 'OMA'::text))
  • Rows Removed by Index Recheck: 22289
  • Filter: ((source_system_cd)::text = 'QFUND_X'::text)
  • Heap Blocks: exact=52 lossy=992
14. 25.723 988.118 ↓ 0.0 0 1

BitmapAnd (cost=3,724.67..3,724.67 rows=58 width=0) (actual time=988.118..988.118 rows=0 loops=1)

15. 195.099 195.099 ↓ 2.7 289,069 1

Bitmap Index Scan on transaction_master_transaction_cd_idx2 (cost=0.00..1,760.52 rows=109,004 width=0) (actual time=195.099..195.099 rows=289,069 loops=1)

  • Index Cond: ((etl_modified_dttm >= '2019-06-11 00:00:00'::timestamp without time zone) AND (etl_modified_dttm <= ('2019-06-11 00:00:00'::timestamp without time zone + 0.99999)))
16. 767.296 767.296 ↑ 2.1 87,486 1

Bitmap Index Scan on transaction_master_product_category_cd_idx (cost=0.00..1,963.91 rows=180,818 width=0) (actual time=767.296..767.296 rows=87,486 loops=1)

  • Index Cond: ((product_category_cd)::text = 'OMA'::text)
17. 0.000 0.000 ↓ 0.0 0

Index Scan using transaction_detail_idx1 on transaction_detail ctd (cost=0.57..1,647.06 rows=6 width=22) (never executed)

  • Index Cond: ((source_transaction_id)::text = (tm.source_transaction_id)::text)
  • Filter: ((detail_cd)::text = ANY ('{INTERESTFEE,CUSTOMARYFEE}'::text[]))
18. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=1,946,741.26..1,946,741.29 rows=1 width=59) (never executed)

  • Group Key: tm_2.source_product_id, tm_2.source_system_cd, td.detail_cd
19. 0.000 0.000 ↓ 0.0 0

Sort (cost=1,946,741.26..1,946,741.26 rows=1 width=36) (never executed)

  • Sort Key: tm_2.source_product_id, td.detail_cd
20. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1,639,993.27..1,946,741.25 rows=1 width=36) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1,639,993.12..1,946,740.53 rows=1 width=46) (never executed)

  • Join Filter: ((tm_3.source_product_id)::text = (transaction_master_1.source_product_id)::text)
22. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1,636,203.35..1,942,950.72 rows=1 width=65) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Subquery Scan on tm_3 (cost=1,635,031.69..1,941,772.45 rows=1 width=28) (never executed)

  • Filter: ((tm_3.etl_modified_dttm >= '2019-06-11 00:00:00'::timestamp without time zone) AND ((tm_3.source_system_cd)::text = 'QFUND_X'::text) AND ((tm_3.product_status_cd_current)::text = 'NONCURRENT'::text) AND ((tm_3.product_status_cd_previous)::text = 'CURRENT'::text) AND (tm_3.etl_modified_dttm <= ('2019-06-11 00:00:00'::timestamp without time zone + 0.99999)))
24. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=1,635,031.69..1,795,705.42 rows=5,842,681 width=231) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Sort (cost=1,635,031.69..1,649,638.40 rows=5,842,681 width=83) (never executed)

  • Sort Key: tm_4.source_product_id, (to_number(btrim((tm_4.source_transaction_id)::text, 'QFX'::text))), tm_4.transaction_dttm
26. 0.000 0.000 ↓ 0.0 0

Index Scan using transaction_master_filt_qfund_idx on transaction_master tm_4 (cost=0.43..650,854.86 rows=5,842,681 width=83) (never executed)

  • Filter: (source_deleted_flg = 'N'::bpchar)
27. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1,171.66..1,178.22 rows=6 width=64) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Index Scan using transaction_master_a_idx_prod on transaction_master tm_2 (cost=0.57..2.79 rows=1 width=42) (never executed)

  • Index Cond: (((source_product_id)::text = (tm_3.source_product_id)::text) AND ((source_system_cd)::text = 'QFUND_X'::text) AND ((source_transaction_id)::text < (tm_3.source_transaction_id)::text))
29. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1,171.08..1,175.36 rows=6 width=41) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Index Scan using transaction_detail_pk on transaction_detail td (cost=0.70..3.85 rows=1 width=32) (never executed)

  • Index Cond: (((source_system_cd)::text = 'QFUND_X'::text) AND ((source_transaction_id)::text = (tm_2.source_transaction_id)::text) AND ((detail_cd)::text = ANY ('{INTERESTFEE,CUSTOMARYFEE}'::text[])))
  • Filter: (source_deleted_flg = 'N'::bpchar)
31. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on transaction_detail ctd_1 (cost=1,170.38..1,171.50 rows=1 width=17) (never executed)

  • Recheck Cond: (((source_transaction_id)::text = (tm_3.source_transaction_id)::text) AND ((detail_cd)::text = (td.detail_cd)::text) AND ((detail_cd)::text = ANY ('{INTERESTFEE,CUSTOMARYFEE}'::text[])))
32. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=1,170.38..1,170.38 rows=1 width=0) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on transaction_detail_idx1 (cost=0.00..21.08 rows=1,561 width=0) (never executed)

  • Index Cond: ((source_transaction_id)::text = (tm_3.source_transaction_id)::text)
34. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on transaction_detail_detail_cd_idx (cost=0.00..1,126.84 rows=88,847 width=0) (never executed)

  • Index Cond: (((detail_cd)::text = (td.detail_cd)::text) AND ((detail_cd)::text = ANY ('{INTERESTFEE,CUSTOMARYFEE}'::text[])))
35. 0.000 0.000 ↓ 0.0 0

Unique (cost=3,789.77..3,789.78 rows=1 width=19) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Sort (cost=3,789.77..3,789.78 rows=1 width=19) (never executed)

  • Sort Key: transaction_master_1.source_product_id
37. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on transaction_master transaction_master_1 (cost=3,724.67..3,789.76 rows=1 width=19) (never executed)

  • Recheck Cond: ((etl_modified_dttm >= '2019-06-11 00:00:00'::timestamp without time zone) AND (etl_modified_dttm <= ('2019-06-11 00:00:00'::timestamp without time zone + 0.99999)) AND ((product_category_cd)::text = 'OMA'::text))
  • Filter: ((source_system_cd)::text = 'QFUND_X'::text)
38. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=3,724.67..3,724.67 rows=58 width=0) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on transaction_master_transaction_cd_idx2 (cost=0.00..1,760.52 rows=109,004 width=0) (never executed)

  • Index Cond: ((etl_modified_dttm >= '2019-06-11 00:00:00'::timestamp without time zone) AND (etl_modified_dttm <= ('2019-06-11 00:00:00'::timestamp without time zone + 0.99999)))
40. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on transaction_master_product_category_cd_idx (cost=0.00..1,963.91 rows=180,818 width=0) (never executed)

  • Index Cond: ((product_category_cd)::text = 'OMA'::text)
41. 0.000 0.000 ↓ 0.0 0

Index Only Scan using transaction_code_signs_nk_idx on transaction_code_signs tcs (cost=0.14..0.71 rows=1 width=18) (never executed)

  • Index Cond: ((transaction_cd = (tm_2.transaction_cd)::text) AND (reversal_cd = (tm_2.reversal_cd)::text))
  • Heap Fetches: 0
42. 0.000 0.000 ↓ 0.0 0

Index Scan using transaction_master_a_idx_prod on transaction_master tranmast (cost=0.57..178.44 rows=1 width=28) (never executed)

  • Index Cond: ((source_product_id)::text = (tm.source_product_id)::text)
  • Filter: (((product_category_cd)::text = 'OMA'::text) AND ((transaction_cd)::text = 'CHARGE'::text))
Planning time : 18.419 ms
Execution time : 200,221.878 ms