explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E2GY : qa1_source_product_cd_idx

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 873,907.763 ↓ 0.0 0 1

Nested Loop (cost=2,962,790.10..2,962,928.10 rows=1 width=91) (actual time=873,907.763..873,907.763 rows=0 loops=1)

2. 0.074 873,867.106 ↓ 20.0 20 1

GroupAggregate (cost=2,962,789.53..2,962,789.57 rows=1 width=82) (actual time=873,867.038..873,867.106 rows=20 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.061 873,867.032 ↓ 20.0 20 1

Sort (cost=2,962,789.53..2,962,789.53 rows=1 width=87) (actual time=873,867.023..873,867.032 rows=20 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: 27kB
4. 0.062 873,866.971 ↓ 20.0 20 1

Nested Loop Left Join (cost=2,730,361.43..2,962,789.52 rows=1 width=87) (actual time=853,808.577..873,866.971 rows=20 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: 380
5. 0.017 371,862.189 ↓ 20.0 20 1

Nested Loop (cost=1,261,681.37..1,494,109.39 rows=1 width=55) (actual time=351,804.271..371,862.189 rows=20 loops=1)

6. 0.068 371,851.382 ↓ 10.0 10 1

Nested Loop (cost=1,261,680.79..1,492,564.67 rows=1 width=42) (actual time=351,802.500..371,851.382 rows=10 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: 354
7. 1,159.163 371,797.546 ↓ 26.0 26 1

Subquery Scan on tm (cost=1,258,513.65..1,489,397.50 rows=1 width=42) (actual time=351,748.939..371,797.546 rows=26 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: 4592904
8. 4,197.577 370,638.383 ↑ 1.0 4,592,930 1

WindowAgg (cost=1,258,513.65..1,385,499.76 rows=4,617,677 width=129) (actual time=350,718.351..370,638.383 rows=4,592,930 loops=1)

9. 49,362.496 366,440.806 ↑ 1.0 4,592,930 1

Sort (cost=1,258,513.65..1,270,057.84 rows=4,617,677 width=97) (actual time=350,718.336..366,440.806 rows=4,592,930 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. 317,078.310 317,078.310 ↑ 1.0 4,592,930 1

Index Scan using transaction_master_filt_qfund_idx on transaction_master tm_1 (cost=0.43..451,542.79 rows=4,617,677 width=97) (actual time=0.032..317,078.310 rows=4,592,930 loops=1)

  • Filter: (source_deleted_flg = 'N'::bpchar)
  • Rows Removed by Filter: 1033
11. 0.104 53.768 ↓ 14.0 14 26

Unique (cost=3,167.15..3,167.15 rows=1 width=19) (actual time=2.060..2.068 rows=14 loops=26)

12. 0.181 53.664 ↓ 53.0 53 26

Sort (cost=3,167.15..3,167.15 rows=1 width=19) (actual time=2.060..2.064 rows=53 loops=26)

  • Sort Key: transaction_master.source_product_id
  • Sort Method: quicksort Memory: 27kB
13. 14.799 53.483 ↓ 53.0 53 1

Bitmap Heap Scan on transaction_master (cost=3,114.39..3,167.14 rows=1 width=19) (actual time=49.661..53.483 rows=53 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=223
14. 4.208 38.684 ↓ 0.0 0 1

BitmapAnd (cost=3,114.39..3,114.39 rows=47 width=0) (actual time=38.684..38.684 rows=0 loops=1)

15. 27.039 27.039 ↑ 1.8 75,564 1

Bitmap Index Scan on transaction_master_product_category_cd_idx (cost=0.00..1,445.53 rows=137,368 width=0) (actual time=27.039..27.039 rows=75,564 loops=1)

  • Index Cond: ((product_category_cd)::text = 'OMA'::text)
16. 7.437 7.437 ↑ 39.8 2,724 1

Bitmap Index Scan on transaction_master_transaction_cd_idx2 (cost=0.00..1,668.61 rows=108,283 width=0) (actual time=7.437..7.437 rows=2,724 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. 10.790 10.790 ↑ 3.0 2 10

Index Scan using transaction_detail_idx1 on transaction_detail ctd (cost=0.57..1,544.66 rows=6 width=22) (actual time=0.312..1.079 rows=2 loops=10)

  • 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. 0.400 502,004.720 ↓ 20.0 20 20

GroupAggregate (cost=1,468,680.06..1,468,680.10 rows=1 width=59) (actual time=25,100.217..25,100.236 rows=20 loops=20)

  • Group Key: tm_2.source_product_id, tm_2.source_system_cd, td.detail_cd
19. 0.089 502,004.320 ↓ 20.0 20 20

Sort (cost=1,468,680.06..1,468,680.07 rows=1 width=36) (actual time=25,100.214..25,100.216 rows=20 loops=20)

  • Sort Key: tm_2.source_product_id, td.detail_cd
  • Sort Method: quicksort Memory: 26kB
20. 0.089 502,004.231 ↓ 20.0 20 1

Nested Loop (cost=1,226,243.67..1,468,680.05 rows=1 width=36) (actual time=354,120.316..502,004.231 rows=20 loops=1)

21. 0.226 502,003.202 ↓ 20.0 20 1

Nested Loop (cost=1,226,243.53..1,468,678.78 rows=1 width=46) (actual time=354,119.560..502,003.202 rows=20 loops=1)

  • Join Filter: ((tm_3.source_product_id)::text = (transaction_master_1.source_product_id)::text)
  • Rows Removed by Join Filter: 624
22. 0.133 501,953.066 ↓ 46.0 46 1

Nested Loop (cost=1,223,076.38..1,465,511.61 rows=1 width=65) (actual time=354,070.281..501,953.066 rows=46 loops=1)

23. 994.626 369,963.829 ↓ 26.0 26 1

Subquery Scan on tm_3 (cost=1,221,535.75..1,463,963.79 rows=1 width=28) (actual time=354,046.320..369,963.829 rows=26 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: 4592904
24. 3,581.352 368,969.203 ↑ 1.0 4,592,930 1

WindowAgg (cost=1,221,535.75..1,348,521.86 rows=4,617,677 width=231) (actual time=352,772.976..368,969.203 rows=4,592,930 loops=1)

25. 46,361.833 365,387.851 ↑ 1.0 4,592,930 1

Sort (cost=1,221,535.75..1,233,079.94 rows=4,617,677 width=83) (actual time=352,772.964..365,387.851 rows=4,592,930 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: 301504kB
26. 319,026.018 319,026.018 ↑ 1.0 4,592,930 1

Index Scan using transaction_master_filt_qfund_idx on transaction_master tm_4 (cost=0.43..451,542.79 rows=4,617,677 width=83) (actual time=0.690..319,026.018 rows=4,592,930 loops=1)

  • Filter: (source_deleted_flg = 'N'::bpchar)
  • Rows Removed by Filter: 1033
27. 0.264 131,989.104 ↑ 3.0 2 26

Nested Loop (cost=1,540.63..1,547.76 rows=6 width=64) (actual time=2,335.426..5,076.504 rows=2 loops=26)

28. 95.784 95.784 ↓ 2.0 2 26

Index Scan using transaction_master_a_idx_prod on transaction_master tm_2 (cost=0.57..2.79 rows=1 width=42) (actual time=2.534..3.684 rows=2 loops=26)

  • 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.788 131,893.056 ↑ 6.0 1 64

Nested Loop (cost=1,540.06..1,544.90 rows=6 width=41) (actual time=1,432.940..2,060.829 rows=1 loops=64)

30. 46.976 46.976 ↑ 1.0 1 64

Index Scan using transaction_detail_pk on transaction_detail td (cost=0.70..4.41 rows=1 width=32) (actual time=0.616..0.734 rows=1 loops=64)

  • 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. 36.524 131,845.292 ↑ 1.0 1 46

Bitmap Heap Scan on transaction_detail ctd_1 (cost=1,539.36..1,540.48 rows=1 width=17) (actual time=2,865.714..2,866.202 rows=1 loops=46)

  • 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[])))
  • Rows Removed by Index Recheck: 3
  • Heap Blocks: exact=118
32. 0.644 131,808.768 ↓ 0.0 0 46

BitmapAnd (cost=1,539.36..1,539.36 rows=1 width=0) (actual time=2,865.408..2,865.408 rows=0 loops=46)

33. 30.544 30.544 ↑ 207.7 7 46

Bitmap Index Scan on transaction_detail_idx1 (cost=0.00..18.08 rows=1,454 width=0) (actual time=0.664..0.664 rows=7 loops=46)

  • Index Cond: ((source_transaction_id)::text = (tm_3.source_transaction_id)::text)
34. 131,777.580 131,777.580 ↓ 47.2 5,249,965 46

Bitmap Index Scan on transaction_detail_detail_cd_idx (cost=0.00..1,493.20 rows=111,323 width=0) (actual time=2,864.730..2,864.730 rows=5,249,965 loops=46)

  • Index Cond: (((detail_cd)::text = (td.detail_cd)::text) AND ((detail_cd)::text = ANY ('{INTERESTFEE,CUSTOMARYFEE}'::text[])))
35. 0.322 49.910 ↓ 14.0 14 46

Unique (cost=3,167.15..3,167.15 rows=1 width=19) (actual time=1.073..1.085 rows=14 loops=46)

36. 0.388 49.588 ↓ 53.0 53 46

Sort (cost=3,167.15..3,167.15 rows=1 width=19) (actual time=1.072..1.078 rows=53 loops=46)

  • Sort Key: transaction_master_1.source_product_id
  • Sort Method: quicksort Memory: 27kB
37. 11.351 49.200 ↓ 53.0 53 1

Bitmap Heap Scan on transaction_master transaction_master_1 (cost=3,114.39..3,167.14 rows=1 width=19) (actual time=44.563..49.200 rows=53 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=223
38. 4.189 37.849 ↓ 0.0 0 1

BitmapAnd (cost=3,114.39..3,114.39 rows=47 width=0) (actual time=37.849..37.849 rows=0 loops=1)

39. 31.158 31.158 ↑ 1.8 75,564 1

Bitmap Index Scan on transaction_master_product_category_cd_idx (cost=0.00..1,445.53 rows=137,368 width=0) (actual time=31.158..31.158 rows=75,564 loops=1)

  • Index Cond: ((product_category_cd)::text = 'OMA'::text)
40. 2.502 2.502 ↑ 39.0 2,780 1

Bitmap Index Scan on transaction_master_transaction_cd_idx2 (cost=0.00..1,668.61 rows=108,283 width=0) (actual time=2.502..2.502 rows=2,780 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)))
41. 0.940 0.940 ↑ 1.0 1 20

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.046..0.047 rows=1 loops=20)

  • Index Cond: (((transaction_cd)::text = (tm_2.transaction_cd)::text) AND ((reversal_cd)::text = (tm_2.reversal_cd)::text))
42. 40.640 40.640 ↓ 0.0 0 20

Index Scan using transaction_master_a_idx_prod on transaction_master tranmast (cost=0.57..138.52 rows=1 width=28) (actual time=2.032..2.032 rows=0 loops=20)

  • Index Cond: ((source_product_id)::text = (tm.source_product_id)::text)
  • Filter: (((product_category_cd)::text = 'OMA'::text) AND ((transaction_cd)::text = 'CHARGE'::text))
  • Rows Removed by Filter: 8
Planning time : 24.872 ms
Execution time : 874,133.150 ms