explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zbCF : qa1_idx_8

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 221,552.690 ↓ 0.0 0 1

Nested Loop (cost=2,656,232.12..2,656,233.30 rows=1 width=91) (actual time=221,552.690..221,552.690 rows=0 loops=1)

2. 0.001 221,552.689 ↓ 0.0 0 1

GroupAggregate (cost=2,656,227.43..2,656,227.47 rows=1 width=82) (actual time=221,552.689..221,552.689 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.034 221,552.688 ↓ 0.0 0 1

Sort (cost=2,656,227.43..2,656,227.44 rows=1 width=87) (actual time=221,552.688..221,552.688 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 221,552.654 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,445,446.63..2,656,227.42 rows=1 width=87) (actual time=221,552.654..221,552.654 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.000 221,552.653 ↓ 0.0 0 1

Nested Loop (cost=1,128,851.58..1,339,632.32 rows=1 width=55) (actual time=221,552.653..221,552.653 rows=0 loops=1)

6. 0.002 221,552.653 ↓ 0.0 0 1

Nested Loop (cost=1,128,851.01..1,338,073.14 rows=1 width=42) (actual time=221,552.653..221,552.653 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))
7. 713.023 221,552.651 ↓ 0.0 0 1

Subquery Scan on tm (cost=1,128,848.20..1,338,070.30 rows=1 width=42) (actual time=221,552.651..221,552.651 rows=0 loops=1)

  • Filter: ((tm.etl_modified_dttm >= '2019-06-12 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-12 00:00:00'::timestamp without time zone + 0.99999)))
  • Rows Removed by Filter: 4593123
8. 2,700.618 220,839.628 ↓ 1.1 4,593,123 1

WindowAgg (cost=1,128,848.20..1,243,920.35 rows=4,184,442 width=129) (actual time=208,830.438..220,839.628 rows=4,593,123 loops=1)

9. 44,675.650 218,139.010 ↓ 1.1 4,593,123 1

Sort (cost=1,128,848.20..1,139,309.30 rows=4,184,442 width=97) (actual time=208,830.420..218,139.010 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. 173,463.360 173,463.360 ↓ 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=10.356..173,463.360 rows=4,593,123 loops=1)

  • Filter: (source_deleted_flg = 'N'::bpchar)
  • Rows Removed by Filter: 1033
11. 0.000 0.000 ↓ 0.0 0

Unique (cost=2.81..2.82 rows=1 width=19) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.81..2.82 rows=1 width=19) (never executed)

  • Sort Key: transaction_master.source_product_id
13. 0.000 0.000 ↓ 0.0 0

Index Scan using transaction_master_transaction_cd_idx3 on transaction_master (cost=0.57..2.80 rows=1 width=19) (never executed)

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

Index Scan using transaction_detail_idx1 on transaction_detail ctd (cost=0.57..1,559.12 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[]))
15. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=1,316,595.05..1,316,595.08 rows=1 width=59) (never executed)

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

Sort (cost=1,316,595.05..1,316,595.05 rows=1 width=36) (never executed)

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

Nested Loop (cost=1,095,347.80..1,316,595.04 rows=1 width=36) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1,095,347.65..1,316,593.76 rows=1 width=46) (never executed)

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

Nested Loop (cost=1,095,344.84..1,316,590.92 rows=1 width=65) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1,095,343.57..1,316,585.38 rows=1 width=36) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Subquery Scan on tm_3 (cost=1,095,343.00..1,315,026.20 rows=1 width=28) (never executed)

  • Filter: ((tm_3.etl_modified_dttm >= '2019-06-12 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-12 00:00:00'::timestamp without time zone + 0.99999)))
22. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=1,095,343.00..1,210,415.15 rows=4,184,442 width=231) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Sort (cost=1,095,343.00..1,105,804.10 rows=4,184,442 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
24. 0.000 0.000 ↓ 0.0 0

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) (never executed)

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

Index Scan using transaction_detail_idx1 on transaction_detail ctd_1 (cost=0.57..1,559.12 rows=6 width=17) (never executed)

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

Nested Loop (cost=1.27..5.54 rows=1 width=55) (never executed)

27. 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))
28. 0.000 0.000 ↓ 0.0 0

Index Scan using transaction_detail_pk on transaction_detail td (cost=0.70..2.73 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 = (ctd_1.detail_cd)::text))
  • Filter: (((detail_cd)::text = ANY ('{INTERESTFEE,CUSTOMARYFEE}'::text[])) AND (source_deleted_flg = 'N'::bpchar))
29. 0.000 0.000 ↓ 0.0 0

Unique (cost=2.81..2.82 rows=1 width=19) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.81..2.82 rows=1 width=19) (never executed)

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

Index Scan using transaction_master_transaction_cd_idx3 on transaction_master transaction_master_1 (cost=0.57..2.80 rows=1 width=19) (never executed)

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

Index Scan using transaction_code_signs_pk_idx on transaction_code_signs tcs (cost=0.14..1.26 rows=1 width=120) (never executed)

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

Bitmap Heap Scan on transaction_master tranmast (cost=4.69..5.81 rows=1 width=28) (never executed)

  • Recheck Cond: (((product_category_cd)::text = 'OMA'::text) AND ((transaction_cd)::text = 'CHARGE'::text) AND ((source_product_id)::text = (tm.source_product_id)::text))
34. 0.000 0.000 ↓ 0.0 0

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

35. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on transaction_master_filt_idx_8 (cost=0.00..1.75 rows=75 width=0) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on transaction_master_a_idx_prod (cost=0.00..2.67 rows=133 width=0) (never executed)

  • Index Cond: ((source_product_id)::text = (tm.source_product_id)::text)