explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v98z : orig_qa1

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 738,784.148 ↓ 0.0 0 1

Nested Loop (cost=2,962,513.40..2,962,651.40 rows=1 width=91) (actual time=738,784.148..738,784.148 rows=0 loops=1)

2. 0.092 738,742.753 ↓ 20.0 20 1

GroupAggregate (cost=2,962,512.82..2,962,512.86 rows=1 width=82) (actual time=738,742.668..738,742.753 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.082 738,742.661 ↓ 20.0 20 1

Sort (cost=2,962,512.82..2,962,512.83 rows=1 width=87) (actual time=738,742.649..738,742.661 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.082 738,742.579 ↓ 20.0 20 1

Nested Loop Left Join (cost=2,730,095.03..2,962,512.81 rows=1 width=87) (actual time=722,437.896..738,742.579 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.025 388,736.057 ↓ 20.0 20 1

Nested Loop (cost=1,261,551.52..1,493,969.25 rows=1 width=55) (actual time=372,433.321..388,736.057 rows=20 loops=1)

6. 0.074 388,729.632 ↓ 10.0 10 1

Nested Loop (cost=1,261,550.95..1,492,424.53 rows=1 width=42) (actual time=372,431.691..388,729.632 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. 993.311 388,652.052 ↓ 26.0 26 1

Subquery Scan on tm (cost=1,258,462.23..1,489,335.78 rows=1 width=42) (actual time=372,355.126..388,652.052 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: 4592871
8. 3,410.579 387,658.741 ↑ 1.0 4,592,897 1

WindowAgg (cost=1,258,462.23..1,385,442.68 rows=4,617,471 width=129) (actual time=371,030.286..387,658.741 rows=4,592,897 loops=1)

9. 54,784.720 384,248.162 ↑ 1.0 4,592,897 1

Sort (cost=1,258,462.23..1,270,005.91 rows=4,617,471 width=97) (actual time=371,030.271..384,248.162 rows=4,592,897 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: 383392kB
10. 329,463.442 329,463.442 ↑ 1.0 4,592,897 1

Index Scan using transaction_master_filt_qfund_idx on transaction_master tm_1 (cost=0.43..451,532.06 rows=4,617,471 width=97) (actual time=7.777..329,463.442 rows=4,592,897 loops=1)

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

Unique (cost=3,088.72..3,088.72 rows=1 width=19) (actual time=2.959..2.981 rows=14 loops=26)

12. 0.786 77.246 ↓ 53.0 53 26

Sort (cost=3,088.72..3,088.72 rows=1 width=19) (actual time=2.959..2.971 rows=53 loops=26)

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

Bitmap Heap Scan on transaction_master (cost=3,038.20..3,088.71 rows=1 width=19) (actual time=74.021..76.460 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. 6.866 63.724 ↓ 0.0 0 1

BitmapAnd (cost=3,038.20..3,038.20 rows=45 width=0) (actual time=63.724..63.724 rows=0 loops=1)

15. 49.425 49.425 ↑ 1.8 75,564 1

Bitmap Index Scan on transaction_master_product_category_cd_idx (cost=0.00..1,445.49 rows=137,362 width=0) (actual time=49.425..49.425 rows=75,564 loops=1)

  • Index Cond: ((product_category_cd)::text = 'OMA'::text)
16. 7.433 7.433 ↑ 39.7 2,599 1

Bitmap Index Scan on transaction_master_transaction_cd_idx2 (cost=0.00..1,592.46 rows=103,309 width=0) (actual time=7.433..7.433 rows=2,599 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. 6.400 6.400 ↑ 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.255..0.640 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. 2.200 350,006.440 ↓ 20.0 20 20

GroupAggregate (cost=1,468,543.50..1,468,543.54 rows=1 width=59) (actual time=17,500.230..17,500.322 rows=20 loops=20)

  • Group Key: tm_2.source_product_id, tm_2.source_system_cd, td.detail_cd
19. 0.099 350,004.240 ↓ 20.0 20 20

Sort (cost=1,468,543.50..1,468,543.51 rows=1 width=36) (actual time=17,500.210..17,500.212 rows=20 loops=20)

  • Sort Key: tm_2.source_product_id, td.detail_cd
  • Sort Method: quicksort Memory: 26kB
20. 0.110 350,004.141 ↓ 20.0 20 1

Nested Loop (cost=1,226,117.92..1,468,543.49 rows=1 width=36) (actual time=190,106.370..350,004.141 rows=20 loops=1)

21. 0.284 350,002.971 ↓ 20.0 20 1

Nested Loop (cost=1,226,117.78..1,468,542.22 rows=1 width=46) (actual time=190,105.512..350,002.971 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.175 349,947.671 ↓ 46.0 46 1

Nested Loop (cost=1,223,029.07..1,465,453.48 rows=1 width=65) (actual time=190,051.143..349,947.671 rows=46 loops=1)

23. 785.045 202,155.696 ↓ 26.0 26 1

Subquery Scan on tm_3 (cost=1,221,488.43..1,463,905.66 rows=1 width=28) (actual time=190,028.342..202,155.696 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: 4592871
24. 2,694.423 201,370.651 ↑ 1.0 4,592,897 1

WindowAgg (cost=1,221,488.43..1,348,468.88 rows=4,617,471 width=231) (actual time=188,889.427..201,370.651 rows=4,592,897 loops=1)

25. 43,692.629 198,676.228 ↑ 1.0 4,592,897 1

Sort (cost=1,221,488.43..1,233,032.11 rows=4,617,471 width=83) (actual time=188,889.407..198,676.228 rows=4,592,897 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: 301488kB
26. 154,983.599 154,983.599 ↑ 1.0 4,592,897 1

Index Scan using transaction_master_filt_qfund_idx on transaction_master tm_4 (cost=0.43..451,532.06 rows=4,617,471 width=83) (actual time=0.420..154,983.599 rows=4,592,897 loops=1)

  • Filter: (source_deleted_flg = 'N'::bpchar)
  • Rows Removed by Filter: 1033
27. 0.318 147,791.800 ↑ 3.0 2 26

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

28. 256.698 256.698 ↓ 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.374..9.873 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. 1.050 147,534.784 ↑ 6.0 1 64

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

30. 30.272 30.272 ↑ 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.418..0.473 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.662 147,503.462 ↑ 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=3,206.272..3,206.597 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.874 147,466.800 ↓ 0.0 0 46

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

33. 19.136 19.136 ↑ 207.7 7 46

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

  • Index Cond: ((source_transaction_id)::text = (tm_3.source_transaction_id)::text)
34. 147,446.790 147,446.790 ↓ 47.2 5,249,933 46

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

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

Unique (cost=3,088.72..3,088.72 rows=1 width=19) (actual time=1.184..1.196 rows=14 loops=46)

36. 0.442 54.694 ↓ 53.0 53 46

Sort (cost=3,088.72..3,088.72 rows=1 width=19) (actual time=1.183..1.189 rows=53 loops=46)

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

Bitmap Heap Scan on transaction_master transaction_master_1 (cost=3,038.20..3,088.71 rows=1 width=19) (actual time=51.794..54.252 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. 6.188 39.008 ↓ 0.0 0 1

BitmapAnd (cost=3,038.20..3,038.20 rows=45 width=0) (actual time=39.008..39.008 rows=0 loops=1)

39. 31.561 31.561 ↑ 1.8 75,564 1

Bitmap Index Scan on transaction_master_product_category_cd_idx (cost=0.00..1,445.49 rows=137,362 width=0) (actual time=31.561..31.561 rows=75,564 loops=1)

  • Index Cond: ((product_category_cd)::text = 'OMA'::text)
40. 1.259 1.259 ↑ 39.7 2,600 1

Bitmap Index Scan on transaction_master_transaction_cd_idx2 (cost=0.00..1,592.46 rows=103,309 width=0) (actual time=1.259..1.259 rows=2,600 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. 1.060 1.060 ↑ 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.053..0.053 rows=1 loops=20)

  • Index Cond: (((transaction_cd)::text = (tm_2.transaction_cd)::text) AND ((reversal_cd)::text = (tm_2.reversal_cd)::text))
42. 41.380 41.380 ↓ 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.069..2.069 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 : 56.383 ms
Execution time : 738,953.739 ms