explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IO1I

Settings
# exclusive inclusive rows x rows loops node
1. 1.948 7.210 ↓ 74.0 74 1

Nested Loop (cost=1.44..146.35 rows=1 width=2,349) (actual time=0.205..7.210 rows=74 loops=1)

  • Join Filter: ((ft.payee_payment_type_id)::text = ((pint.payment_type_id)::character varying(20))::text)
  • Rows Removed by Join Filter: 2590
2. 0.092 4.078 ↓ 74.0 74 1

Nested Loop Left Join (cost=1.44..144.18 rows=1 width=1,747) (actual time=0.148..4.078 rows=74 loops=1)

3. 0.135 3.912 ↓ 74.0 74 1

Nested Loop (cost=1.29..143.36 rows=1 width=1,713) (actual time=0.139..3.912 rows=74 loops=1)

4. 0.209 3.555 ↓ 74.0 74 1

Nested Loop Left Join (cost=1.15..135.19 rows=1 width=1,498) (actual time=0.128..3.555 rows=74 loops=1)

5. 0.137 2.532 ↓ 74.0 74 1

Nested Loop (cost=0.72..126.73 rows=1 width=1,456) (actual time=0.094..2.532 rows=74 loops=1)

6. 0.136 2.247 ↓ 74.0 74 1

Nested Loop (cost=0.58..118.57 rows=1 width=1,282) (actual time=0.084..2.247 rows=74 loops=1)

7. 0.132 1.149 ↓ 74.0 74 1

Nested Loop (cost=0.15..110.10 rows=1 width=1,255) (actual time=0.057..1.149 rows=74 loops=1)

8. 0.110 0.721 ↓ 74.0 74 1

Nested Loop (cost=0.00..101.93 rows=1 width=1,229) (actual time=0.034..0.721 rows=74 loops=1)

  • Join Filter: ((ft.payer_payment_type_id)::text = ((pin.payment_type_id)::character varying(20))::text)
9. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on dim_payinstrument pin (cost=0.00..1.54 rows=1 width=182) (actual time=0.011..0.013 rows=1 loops=1)

  • Filter: (((payment_instrument)::text = 'WALLET'::text) AND ((sub_payment_type_name)::text = 'Normal'::text))
  • Rows Removed by Filter: 35
10. 0.065 0.598 ↑ 1.0 74 1

Append (cost=0.00..98.91 rows=74 width=1,051) (actual time=0.015..0.598 rows=74 loops=1)

11. 0.055 0.055 ↑ 1.0 8 1

Seq Scan on fact_transaction_header_jan_03_2019_000 ft (cost=0.00..9.02 rows=8 width=1,067) (actual time=0.014..0.055 rows=8 loops=1)

  • Filter: ((transfer_date >= '2019-01-03 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-04 00:00:00'::timestamp without time zone) AND ((servicetype)::text = 'MERCHPAY'::text) AND (...)
  • Rows Removed by Filter: 93
12. 0.070 0.070 ↑ 1.0 10 1

Seq Scan on fact_transaction_header_jan_03_2019_001 ft_1 (cost=0.00..11.56 rows=10 width=1,066) (actual time=0.017..0.070 rows=10 loops=1)

  • Filter: ((transfer_date >= '2019-01-03 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-04 00:00:00'::timestamp without time zone) AND ((servicetype)::text = 'MERCHPAY'::text) AND (...)
  • Rows Removed by Filter: 118
13. 0.010 0.030 ↑ 1.0 2 1

Bitmap Heap Scan on fact_transaction_header_jan_03_2019_002 ft_2 (cost=4.16..9.20 rows=2 width=1,028) (actual time=0.027..0.030 rows=2 loops=1)

  • Recheck Cond: ((servicetype)::text = 'MERCHPAY'::text)
  • Filter: ((transfer_date >= '2019-01-03 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-04 00:00:00'::timestamp without time zone) AND ((transfer_status)::text = 'TS'::text))
  • Heap Blocks: exact=2
14. 0.020 0.020 ↑ 1.0 2 1

Bitmap Index Scan on idx_servicetype_jan_03_2019_002 (cost=0.00..4.16 rows=2 width=0) (actual time=0.019..0.020 rows=2 loops=1)

  • Index Cond: ((servicetype)::text = 'MERCHPAY'::text)
15. 0.062 0.062 ↑ 1.0 12 1

Seq Scan on fact_transaction_header_jan_03_2019_003 ft_3 (cost=0.00..10.32 rows=12 width=1,027) (actual time=0.016..0.062 rows=12 loops=1)

  • Filter: ((transfer_date >= '2019-01-03 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-04 00:00:00'::timestamp without time zone) AND ((servicetype)::text = 'MERCHPAY'::text) AND (...)
  • Rows Removed by Filter: 104
16. 0.065 0.065 ↑ 1.0 11 1

Seq Scan on fact_transaction_header_jan_03_2019_004 ft_4 (cost=0.00..11.68 rows=11 width=1,066) (actual time=0.020..0.065 rows=11 loops=1)

  • Filter: ((transfer_date >= '2019-01-03 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-04 00:00:00'::timestamp without time zone) AND ((servicetype)::text = 'MERCHPAY'::text) AND (...)
  • Rows Removed by Filter: 123
17. 0.053 0.053 ↑ 1.0 8 1

Seq Scan on fact_transaction_header_jan_03_2019_005 ft_5 (cost=0.00..9.02 rows=8 width=1,065) (actual time=0.020..0.053 rows=8 loops=1)

  • Filter: ((transfer_date >= '2019-01-03 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-04 00:00:00'::timestamp without time zone) AND ((servicetype)::text = 'MERCHPAY'::text) AND (...)
  • Rows Removed by Filter: 93
18. 0.056 0.056 ↑ 1.0 9 1

Seq Scan on fact_transaction_header_jan_03_2019_006 ft_6 (cost=0.00..10.48 rows=9 width=1,028) (actual time=0.011..0.056 rows=9 loops=1)

  • Filter: ((transfer_date >= '2019-01-03 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-04 00:00:00'::timestamp without time zone) AND ((servicetype)::text = 'MERCHPAY'::text) AND (...)
  • Rows Removed by Filter: 115
19. 0.042 0.042 ↑ 1.0 2 1

Seq Scan on fact_transaction_header_jan_03_2019_007 ft_7 (cost=0.00..9.08 rows=2 width=1,066) (actual time=0.032..0.042 rows=2 loops=1)

  • Filter: ((transfer_date >= '2019-01-03 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-04 00:00:00'::timestamp without time zone) AND ((servicetype)::text = 'MERCHPAY'::text) AND (...)
  • Rows Removed by Filter: 102
20. 0.048 0.048 ↑ 1.0 6 1

Seq Scan on fact_transaction_header_jan_03_2019_008 ft_8 (cost=0.00..9.12 rows=6 width=1,028) (actual time=0.013..0.048 rows=6 loops=1)

  • Filter: ((transfer_date >= '2019-01-03 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-04 00:00:00'::timestamp without time zone) AND ((servicetype)::text = 'MERCHPAY'::text) AND (...)
  • Rows Removed by Filter: 100
21. 0.052 0.052 ↑ 1.0 6 1

Seq Scan on fact_transaction_header_jan_03_2019_009 ft_9 (cost=0.00..9.06 rows=6 width=1,065) (actual time=0.023..0.052 rows=6 loops=1)

  • Filter: ((transfer_date >= '2019-01-03 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-04 00:00:00'::timestamp without time zone) AND ((servicetype)::text = 'MERCHPAY'::text) AND (...)
  • Rows Removed by Filter: 97
22. 0.296 0.296 ↑ 1.0 1 74

Index Scan using idx_service_type on dim_servicetype st (cost=0.15..8.17 rows=1 width=34) (actual time=0.003..0.004 rows=1 loops=74)

  • Index Cond: ((service_type)::text = 'MERCHPAY'::text)
23. 0.962 0.962 ↑ 1.0 1 74

Index Scan using idx_dim_user_user_id on dim_user u (cost=0.43..8.45 rows=1 width=86) (actual time=0.012..0.013 rows=1 loops=74)

  • Index Cond: ((user_id)::text = (CASE WHEN ((st.is_financial)::text = 'Y'::text) THEN ft.payer_user_id ELSE ft.scp_partyid END)::text)
  • Filter: ((partyacc_providerdimid = 101) AND ((user_type)::text = 'SUBSCRIBER'::text))
24. 0.148 0.148 ↑ 1.0 1 74

Index Scan using idx_providerid on dim_provider p (cost=0.14..8.16 rows=1 width=182) (actual time=0.001..0.002 rows=1 loops=74)

  • Index Cond: (provider_id = 101)
25. 0.814 0.814 ↑ 1.0 1 74

Index Scan using idx_dim_user_user_id on dim_user ue (cost=0.43..8.45 rows=1 width=68) (actual time=0.011..0.011 rows=1 loops=74)

  • Index Cond: ((CASE WHEN ((st.is_financial)::text = 'Y'::text) THEN ft.payee_user_id ELSE ft.scp_secpartyid END)::text = (user_id)::text)
26. 0.222 0.222 ↑ 1.0 1 74

Index Scan using idx_trans_status on dim_transtatus dt (cost=0.14..8.16 rows=1 width=316) (actual time=0.002..0.003 rows=1 loops=74)

  • Index Cond: ((tran_status_id)::text = 'TS'::text)
27. 0.074 0.074 ↓ 0.0 0 74

Index Scan using idx_bank_id on dim_bank bnk (cost=0.14..0.81 rows=1 width=136) (actual time=0.001..0.001 rows=0 loops=74)

  • Index Cond: ((ft.bank_id)::text = (bank_id)::text)
28. 1.184 1.184 ↑ 1.0 36 74

Seq Scan on dim_payinstrument pint (cost=0.00..1.36 rows=36 width=182) (actual time=0.001..0.016 rows=36 loops=74)