explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IEaS : qa_idx_7

Settings
# exclusive inclusive rows x rows loops node
1. 0.050 522,086.220 ↓ 2.0 2 1

Nested Loop (cost=2,661,086.82..2,661,087.99 rows=1 width=91) (actual time=522,080.258..522,086.220 rows=2 loops=1)

2. 0.119 522,075.428 ↓ 82.0 82 1

GroupAggregate (cost=2,661,067.22..2,661,067.26 rows=1 width=82) (actual time=522,075.307..522,075.428 rows=82 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.247 522,075.309 ↓ 82.0 82 1

Sort (cost=2,661,067.22..2,661,067.22 rows=1 width=87) (actual time=522,075.295..522,075.309 rows=82 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: 36kB
4. 0.785 522,075.062 ↓ 82.0 82 1

Nested Loop Left Join (cost=2,450,322.57..2,661,067.21 rows=1 width=87) (actual time=511,424.649..522,075.062 rows=82 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))
  • Rows Removed by Join Filter: 6642
5. 0.117 336,564.857 ↓ 82.0 82 1

Nested Loop (cost=1,131,307.63..1,342,052.21 rows=1 width=55) (actual time=325,933.906..336,564.857 rows=82 loops=1)

6. 0.381 336,559.902 ↓ 41.0 41 1

Nested Loop (cost=1,131,307.06..1,340,529.19 rows=1 width=42) (actual time=325,930.381..336,559.902 rows=41 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: 2614
7. 694.263 336,502.350 ↓ 59.0 59 1

Subquery Scan on tm (cost=1,128,848.20..1,338,070.30 rows=1 width=42) (actual time=325,874.222..336,502.350 rows=59 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: 4593064
8. 2,484.485 335,808.087 ↓ 1.1 4,593,123 1

WindowAgg (cost=1,128,848.20..1,243,920.35 rows=4,184,442 width=129) (actual time=324,708.208..335,808.087 rows=4,593,123 loops=1)

9. 43,871.420 333,323.602 ↓ 1.1 4,593,123 1

Sort (cost=1,128,848.20..1,139,309.30 rows=4,184,442 width=97) (actual time=324,708.196..333,323.602 rows=4,593,123 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: 383400kB
10. 289,452.182 289,452.182 ↓ 1.1 4,593,123 1

Index Scan using transaction_master_filt_qfund_idx on transaction_master tm_1 (cost=0.43..400,564.43 rows=4,184,442 width=97) (actual time=0.899..289,452.182 rows=4,593,123 loops=1)

  • Filter: (source_deleted_flg = 'N'::bpchar)
  • Rows Removed by Filter: 1033
11. 0.649 57.171 ↓ 45.0 45 59

Unique (cost=2,458.86..2,458.87 rows=1 width=19) (actual time=0.952..0.969 rows=45 loops=59)

12. 0.506 56.522 ↓ 85.0 85 59

Sort (cost=2,458.86..2,458.87 rows=1 width=19) (actual time=0.952..0.958 rows=85 loops=59)

  • Sort Key: transaction_master.source_product_id
  • Sort Method: quicksort Memory: 28kB
13. 7.058 56.016 ↓ 85.0 85 1

Bitmap Heap Scan on transaction_master (cost=2,430.79..2,458.85 rows=1 width=19) (actual time=49.639..56.016 rows=85 loops=1)

  • Recheck Cond: (((product_category_cd)::text = 'OMA'::text) AND (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)))
  • Filter: ((source_system_cd)::text = 'QFUND_X'::text)
  • Heap Blocks: exact=79
14. 4.350 48.958 ↓ 0.0 0 1

BitmapAnd (cost=2,430.79..2,430.79 rows=25 width=0) (actual time=48.958..48.958 rows=0 loops=1)

15. 31.011 31.011 ↓ 1.2 75,237 1

Bitmap Index Scan on transaction_master_filt_oma_idx (cost=0.00..510.90 rows=63,401 width=0) (actual time=31.011..31.011 rows=75,237 loops=1)

16. 13.597 13.597 ↑ 34.9 3,568 1

Bitmap Index Scan on transaction_master_transaction_cd_idx2 (cost=0.00..1,919.65 rows=124,587 width=0) (actual time=13.597..13.597 rows=3,568 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)))
17. 4.838 4.838 ↑ 3.0 2 41

Index Scan using transaction_detail_idx1 on transaction_detail ctd (cost=0.57..1,522.96 rows=6 width=22) (actual time=0.111..0.118 rows=2 loops=41)

  • Index Cond: ((source_transaction_id)::text = (tm.source_transaction_id)::text)
  • Filter: ((detail_cd)::text = ANY ('{INTERESTFEE,CUSTOMARYFEE}'::text[]))
  • Rows Removed by Filter: 5
18. 17.548 185,509.420 ↓ 82.0 82 82

GroupAggregate (cost=1,319,014.94..1,319,014.97 rows=1 width=59) (actual time=2,262.085..2,262.310 rows=82 loops=82)

  • Group Key: tm_2.source_product_id, tm_2.source_system_cd, td.detail_cd
19. 3.036 185,491.872 ↓ 400.0 400 82

Sort (cost=1,319,014.94..1,319,014.94 rows=1 width=36) (actual time=2,262.066..2,262.096 rows=400 loops=82)

  • Sort Key: tm_2.source_product_id, td.detail_cd
  • Sort Method: quicksort Memory: 56kB
20. 0.780 185,488.836 ↓ 400.0 400 1

Nested Loop (cost=1,097,803.85..1,319,014.93 rows=1 width=36) (actual time=170,557.454..185,488.836 rows=400 loops=1)

21. 2.376 185,484.856 ↓ 400.0 400 1

Nested Loop (cost=1,097,803.71..1,319,013.66 rows=1 width=46) (actual time=170,556.454..185,484.856 rows=400 loops=1)

  • Join Filter: ((tm_3.source_product_id)::text = (transaction_master_1.source_product_id)::text)
  • Rows Removed by Join Filter: 18905
22. 0.380 185,435.290 ↓ 429.0 429 1

Nested Loop (cost=1,095,344.84..1,316,554.77 rows=1 width=65) (actual time=170,516.878..185,435.290 rows=429 loops=1)

23. 0.275 184,566.779 ↓ 99.0 99 1

Nested Loop (cost=1,095,343.57..1,316,549.22 rows=1 width=36) (actual time=170,510.151..184,566.779 rows=99 loops=1)

24. 824.218 184,393.398 ↓ 59.0 59 1

Subquery Scan on tm_3 (cost=1,095,343.00..1,315,026.20 rows=1 width=28) (actual time=170,506.139..184,393.398 rows=59 loops=1)

  • 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)))
  • Rows Removed by Filter: 4593064
25. 3,052.913 183,569.180 ↓ 1.1 4,593,123 1

WindowAgg (cost=1,095,343.00..1,210,415.15 rows=4,184,442 width=231) (actual time=169,528.283..183,569.180 rows=4,593,123 loops=1)

26. 50,989.830 180,516.267 ↓ 1.1 4,593,123 1

Sort (cost=1,095,343.00..1,105,804.10 rows=4,184,442 width=83) (actual time=169,528.272..180,516.267 rows=4,593,123 loops=1)

  • Sort Key: tm_4.source_product_id, (to_number(btrim((tm_4.source_transaction_id)::text, 'QFX'::text))), tm_4.transaction_dttm
  • Sort Method: external merge Disk: 301528kB
27. 129,526.437 129,526.437 ↓ 1.1 4,593,123 1

Index Scan using transaction_master_filt_qfund_idx on transaction_master tm_4 (cost=0.43..400,564.43 rows=4,184,442 width=83) (actual time=1.764..129,526.437 rows=4,593,123 loops=1)

  • Filter: (source_deleted_flg = 'N'::bpchar)
  • Rows Removed by Filter: 1033
28. 173.106 173.106 ↑ 3.0 2 59

Index Scan using transaction_detail_idx1 on transaction_detail ctd_1 (cost=0.57..1,522.96 rows=6 width=17) (actual time=1.230..2.934 rows=2 loops=59)

  • Index Cond: ((source_transaction_id)::text = (tm_3.source_transaction_id)::text)
  • Filter: ((detail_cd)::text = ANY ('{INTERESTFEE,CUSTOMARYFEE}'::text[]))
  • Rows Removed by Filter: 5
29. 1.590 868.131 ↓ 4.0 4 99

Nested Loop (cost=1.27..5.53 rows=1 width=55) (actual time=3.659..8.769 rows=4 loops=99)

30. 226.314 226.314 ↓ 7.0 7 99

Index Scan using transaction_master_a_idx_prod on transaction_master tm_2 (cost=0.57..2.79 rows=1 width=42) (actual time=1.627..2.286 rows=7 loops=99)

  • 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))
31. 640.227 640.227 ↑ 1.0 1 709

Index Scan using transaction_detail_pk on transaction_detail td (cost=0.70..2.73 rows=1 width=32) (actual time=0.897..0.903 rows=1 loops=709)

  • Index Cond: (((source_system_cd)::text = 'QFUND_X'::text) AND ((source_transaction_id)::text = (tm_2.source_transaction_id)::text) AND ((detail_cd)::text = (ctd_1.detail_cd)::text))
  • Filter: (((detail_cd)::text = ANY ('{INTERESTFEE,CUSTOMARYFEE}'::text[])) AND (source_deleted_flg = 'N'::bpchar))
32. 4.719 47.190 ↓ 45.0 45 429

Unique (cost=2,458.86..2,458.87 rows=1 width=19) (actual time=0.093..0.110 rows=45 loops=429)

33. 3.080 42.471 ↓ 85.0 85 429

Sort (cost=2,458.86..2,458.87 rows=1 width=19) (actual time=0.093..0.099 rows=85 loops=429)

  • Sort Key: transaction_master_1.source_product_id
  • Sort Method: quicksort Memory: 28kB
34. 13.798 39.391 ↓ 85.0 85 1

Bitmap Heap Scan on transaction_master transaction_master_1 (cost=2,430.79..2,458.85 rows=1 width=19) (actual time=32.644..39.391 rows=85 loops=1)

  • Recheck Cond: (((product_category_cd)::text = 'OMA'::text) AND (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)))
  • Filter: ((source_system_cd)::text = 'QFUND_X'::text)
  • Heap Blocks: exact=79
35. 4.252 25.593 ↓ 0.0 0 1

BitmapAnd (cost=2,430.79..2,430.79 rows=25 width=0) (actual time=25.593..25.593 rows=0 loops=1)

36. 9.444 9.444 ↓ 1.2 75,237 1

Bitmap Index Scan on transaction_master_filt_oma_idx (cost=0.00..510.90 rows=63,401 width=0) (actual time=9.444..9.444 rows=75,237 loops=1)

37. 11.897 11.897 ↑ 34.9 3,568 1

Bitmap Index Scan on transaction_master_transaction_cd_idx2 (cost=0.00..1,919.65 rows=124,587 width=0) (actual time=11.897..11.897 rows=3,568 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)))
38. 3.200 3.200 ↑ 1.0 1 400

Index Scan using transaction_code_signs_pk_idx on transaction_code_signs tcs (cost=0.14..1.26 rows=1 width=120) (actual time=0.008..0.008 rows=1 loops=400)

  • Index Cond: (((transaction_cd)::text = (tm_2.transaction_cd)::text) AND ((reversal_cd)::text = (tm_2.reversal_cd)::text))
39. 0.082 10.742 ↓ 0.0 0 82

Bitmap Heap Scan on transaction_master tranmast (cost=19.60..20.71 rows=1 width=28) (actual time=0.131..0.131 rows=0 loops=82)

  • Recheck Cond: (((source_product_id)::text = (tm.source_product_id)::text) AND ((transaction_cd)::text = 'CHARGE'::text))
  • Filter: ((product_category_cd)::text = 'OMA'::text)
  • Heap Blocks: exact=2
40. 0.082 10.660 ↓ 0.0 0 82

BitmapAnd (cost=19.60..19.60 rows=1 width=0) (actual time=0.130..0.130 rows=0 loops=82)

41. 0.984 0.984 ↑ 8.9 15 82

Bitmap Index Scan on transaction_master_a_idx_prod (cost=0.00..2.67 rows=133 width=0) (actual time=0.012..0.012 rows=15 loops=82)

  • Index Cond: ((source_product_id)::text = (tm.source_product_id)::text)
42. 9.594 9.594 ↑ 215.6 1,736 82

Bitmap Index Scan on transaction_master_filt_charge_idx (cost=0.00..16.66 rows=374,320 width=0) (actual time=0.117..0.117 rows=1,736 loops=82)