explain.depesz.com

PostgreSQL's explain analyze made readable

Result: L64M

Settings
# exclusive inclusive rows x rows loops node
1. 1.632 11.867 ↓ 296.0 296 1

Nested Loop Left Join (cost=3.65..147.09 rows=1 width=2,781) (actual time=0.204..11.867 rows=296 loops=1)

2.          

CTE paymt_dtls

3. 0.037 0.037 ↑ 1.0 36 1

Seq Scan on dim_payinstrument pin_1 (cost=0.00..1.36 rows=36 width=400) (actual time=0.015..0.037 rows=36 loops=1)

4. 0.632 10.235 ↓ 296.0 296 1

Nested Loop Left Join (cost=2.14..144.22 rows=1 width=1,914) (actual time=0.183..10.235 rows=296 loops=1)

5. 0.766 8.715 ↓ 296.0 296 1

Nested Loop Left Join (cost=1.99..143.07 rows=1 width=1,888) (actual time=0.172..8.715 rows=296 loops=1)

6. 0.628 7.357 ↓ 296.0 296 1

Nested Loop (cost=1.85..141.65 rows=1 width=1,673) (actual time=0.164..7.357 rows=296 loops=1)

  • Join Filter: ((ft.payee_payment_type_id)::text = ((pint.payment_type_id)::character varying(20))::text)
7. 0.405 5.249 ↓ 296.0 296 1

Nested Loop Left Join (cost=1.85..140.73 rows=1 width=1,495) (actual time=0.155..5.249 rows=296 loops=1)

8. 0.197 4.252 ↓ 74.0 74 1

Nested Loop Left Join (cost=1.42..132.28 rows=1 width=1,480) (actual time=0.144..4.252 rows=74 loops=1)

9. 0.188 3.315 ↓ 74.0 74 1

Nested Loop Left Join (cost=0.99..123.82 rows=1 width=1,478) (actual time=0.130..3.315 rows=74 loops=1)

10. 0.174 2.313 ↓ 74.0 74 1

Nested Loop (cost=0.57..115.37 rows=1 width=1,431) (actual time=0.101..2.313 rows=74 loops=1)

11. 0.193 1.991 ↓ 74.0 74 1

Nested Loop (cost=0.43..107.20 rows=1 width=1,257) (actual time=0.094..1.991 rows=74 loops=1)

12. 0.100 0.762 ↓ 74.0 74 1

Nested Loop (cost=0.00..98.74 rows=1 width=1,212) (actual time=0.065..0.762 rows=74 loops=1)

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

CTE Scan on paymt_dtls pin (cost=0.00..0.90 rows=1 width=182) (actual time=0.044..0.046 rows=1 loops=1)

  • Filter: (((payment_instrument)::text = 'WALLET'::text) AND ((sub_payment_type_name)::text = 'Normal'::text))
  • Rows Removed by Filter: 35
14. 0.069 0.616 ↑ 1.0 74 1

Append (cost=0.00..96.36 rows=74 width=1,034) (actual time=0.013..0.616 rows=74 loops=1)

15. 0.053 0.053 ↑ 1.0 8 1

Seq Scan on fact_transaction_header_jan_03_2019_000 ft (cost=0.00..8.77 rows=8 width=1,050) (actual time=0.011..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))
  • Rows Removed by Filter: 93
16. 0.065 0.065 ↑ 1.0 10 1

Seq Scan on fact_transaction_header_jan_03_2019_001 ft_1 (cost=0.00..11.24 rows=10 width=1,049) (actual time=0.014..0.065 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))
  • Rows Removed by Filter: 118
17. 0.011 0.021 ↑ 1.0 2 1

Bitmap Heap Scan on fact_transaction_header_jan_03_2019_002 ft_2 (cost=4.16..9.19 rows=2 width=1,011) (actual time=0.017..0.021 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))
  • Heap Blocks: exact=2
18. 0.010 0.010 ↑ 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.009..0.010 rows=2 loops=1)

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

Seq Scan on fact_transaction_header_jan_03_2019_003 ft_3 (cost=0.00..10.03 rows=12 width=1,010) (actual time=0.015..0.063 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))
  • Rows Removed by Filter: 104
20. 0.077 0.077 ↑ 1.0 11 1

Seq Scan on fact_transaction_header_jan_03_2019_004 ft_4 (cost=0.00..11.35 rows=11 width=1,049) (actual time=0.020..0.077 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))
  • Rows Removed by Filter: 123
21. 0.052 0.052 ↑ 1.0 8 1

Seq Scan on fact_transaction_header_jan_03_2019_005 ft_5 (cost=0.00..8.77 rows=8 width=1,048) (actual time=0.017..0.052 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))
  • Rows Removed by Filter: 93
22. 0.059 0.059 ↑ 1.0 9 1

Seq Scan on fact_transaction_header_jan_03_2019_006 ft_6 (cost=0.00..10.17 rows=9 width=1,011) (actual time=0.012..0.059 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))
  • Rows Removed by Filter: 115
23. 0.057 0.057 ↑ 1.0 2 1

Seq Scan on fact_transaction_header_jan_03_2019_007 ft_7 (cost=0.00..8.82 rows=2 width=1,049) (actual time=0.046..0.057 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))
  • Rows Removed by Filter: 102
24. 0.051 0.051 ↑ 1.0 6 1

Seq Scan on fact_transaction_header_jan_03_2019_008 ft_8 (cost=0.00..8.86 rows=6 width=1,011) (actual time=0.015..0.051 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))
  • Rows Removed by Filter: 100
25. 0.049 0.049 ↑ 1.0 6 1

Seq Scan on fact_transaction_header_jan_03_2019_009 ft_9 (cost=0.00..8.80 rows=6 width=1,048) (actual time=0.020..0.049 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))
  • Rows Removed by Filter: 97
26. 1.036 1.036 ↑ 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.014..0.014 rows=1 loops=74)

  • Index Cond: ((user_id)::text = (ft.payer_user_id)::text)
  • Filter: ((partyacc_providerdimid = 101) AND ((user_type)::text = 'SUBSCRIBER'::text))
27. 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.002..0.002 rows=1 loops=74)

  • Index Cond: (provider_id = 101)
28. 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.010..0.011 rows=1 loops=74)

  • Index Cond: ((ft.payee_user_id)::text = (user_id)::text)
29. 0.740 0.740 ↑ 1.0 1 74

Index Scan using idx_dim_user_user_id on dim_user scp (cost=0.43..8.45 rows=1 width=40) (actual time=0.010..0.010 rows=1 loops=74)

  • Index Cond: ((ft.scp_partyid)::text = (user_id)::text)
30. 0.592 0.592 ↓ 4.0 4 74

Index Scan using idx_dim_user_user_id on dim_user scpe (cost=0.43..8.45 rows=1 width=40) (actual time=0.005..0.008 rows=4 loops=74)

  • Index Cond: ((ft.scp_secpartyid)::text = (user_id)::text)
31. 1.480 1.480 ↑ 1.0 1 296

CTE Scan on paymt_dtls pint (cost=0.00..0.90 rows=1 width=182) (actual time=0.003..0.005 rows=1 loops=296)

  • Filter: (((payment_instrument)::text = 'WALLET'::text) AND ((sub_payment_type_name)::text = 'Normal'::text))
  • Rows Removed by Filter: 35
32. 0.592 0.592 ↑ 1.0 1 296

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

  • Index Cond: ((ft.transfer_status)::text = (tran_status_id)::text)
33. 0.888 0.888 ↑ 1.0 1 296

Index Scan using idx_service_type on dim_servicetype st (cost=0.15..1.14 rows=1 width=34) (actual time=0.002..0.003 rows=1 loops=296)

  • Index Cond: (((ft.servicetype)::text = (service_type)::text) AND ((service_type)::text = 'MERCHPAY'::text))
34. 0.000 0.000 ↓ 0.0 0 296

Index Scan using idx_bank_id on dim_bank bnk (cost=0.14..1.41 rows=1 width=136) (actual time=0.000..0.000 rows=0 loops=296)

  • Index Cond: ((ft.bank_id)::text = (bank_id)::text)