explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5vT4

Settings
# exclusive inclusive rows x rows loops node
1. 99.941 316.239 ↓ 1,344.7 4,034 1

Nested Loop (cost=18.44..3,665.25 rows=3 width=2,349) (actual time=225.666..316.239 rows=4,034 loops=1)

  • Join Filter: ((ft.payee_payment_type_id)::text = ((pint.payment_type_id)::character varying(20))::text)
  • Rows Removed by Join Filter: 141190
2. 0.082 0.082 ↑ 1.0 36 1

Seq Scan on dim_payinstrument pint (cost=0.00..1.36 rows=36 width=182) (actual time=0.006..0.082 rows=36 loops=1)

3. 65.421 216.216 ↓ 212.3 4,034 36

Materialize (cost=18.44..3,648.28 rows=19 width=1,752) (actual time=0.006..6.006 rows=4,034 loops=36)

4. 8.158 150.795 ↓ 212.3 4,034 1

Nested Loop Left Join (cost=18.44..3,648.18 rows=19 width=1,752) (actual time=0.182..150.795 rows=4,034 loops=1)

5. 3.982 142.637 ↓ 212.3 4,034 1

Nested Loop (cost=18.29..3,644.67 rows=19 width=1,695) (actual time=0.177..142.637 rows=4,034 loops=1)

6. 0.013 0.013 ↑ 1.0 1 1

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

  • Index Cond: ((tran_status_id)::text = 'TS'::text)
7. 10.251 138.642 ↓ 212.3 4,034 1

Nested Loop Left Join (cost=18.15..3,636.32 rows=19 width=1,480) (actual time=0.162..138.642 rows=4,034 loops=1)

8. 3.969 84.017 ↓ 212.3 4,034 1

Nested Loop (cost=17.72..3,475.63 rows=19 width=1,437) (actual time=0.147..84.017 rows=4,034 loops=1)

9. 0.006 0.006 ↑ 1.0 1 1

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

  • Index Cond: (provider_id = 101)
10. 10.200 80.042 ↓ 212.3 4,034 1

Nested Loop (cost=17.58..3,467.28 rows=19 width=1,263) (actual time=0.140..80.042 rows=4,034 loops=1)

11. 3.806 21.434 ↓ 201.7 4,034 1

Nested Loop (cost=17.15..3,298.03 rows=20 width=1,233) (actual time=0.115..21.434 rows=4,034 loops=1)

12. 0.009 0.009 ↑ 1.0 1 1

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

  • Index Cond: ((service_type)::text = 'MERCHPAY'::text)
13. 5.051 17.619 ↓ 201.7 4,034 1

Hash Join (cost=17.00..3,289.67 rows=20 width=1,207) (actual time=0.096..17.619 rows=4,034 loops=1)

  • Hash Cond: ((ft.payer_payment_type_id)::text = ((pin.payment_type_id)::character varying(20))::text)
14. 3.352 12.551 ↓ 1.0 4,034 1

Append (cost=15.45..3,258.46 rows=3,927 width=1,029) (actual time=0.068..12.551 rows=4,034 loops=1)

15. 0.932 0.975 ↓ 1.0 400 1

Bitmap Heap Scan on fact_transaction_header_jan_04_2019_000 ft (cost=15.45..316.32 rows=387 width=1,029) (actual time=0.067..0.975 rows=400 loops=1)

  • Recheck Cond: ((servicetype)::text = 'MERCHPAY'::text)
  • Filter: ((transfer_date >= '2019-01-04 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-05 00:00:00'::timestamp without time zone) AND ((transfer_status)::text = 'TS'::text) (...)
  • Rows Removed by Filter: 9
  • Heap Blocks: exact=220
16. 0.043 0.043 ↑ 1.0 409 1

Bitmap Index Scan on idx_servicetype_jan_04_2019_000 (cost=0.00..15.35 rows=409 width=0) (actual time=0.042..0.043 rows=409 loops=1)

  • Index Cond: ((servicetype)::text = 'MERCHPAY'::text)
17. 0.750 0.785 ↓ 1.0 346 1

Bitmap Heap Scan on fact_transaction_header_jan_04_2019_001 ft_1 (cost=11.04..318.31 rows=335 width=1,029) (actual time=0.057..0.785 rows=346 loops=1)

  • Recheck Cond: ((servicetype)::text = 'MERCHPAY'::text)
  • Filter: ((transfer_date >= '2019-01-04 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-05 00:00:00'::timestamp without time zone) AND ((transfer_status)::text = 'TS'::text) (...)
  • Rows Removed by Filter: 11
  • Heap Blocks: exact=192
18. 0.035 0.035 ↑ 1.0 357 1

Bitmap Index Scan on idx_servicetype_jan_04_2019_001 (cost=0.00..10.96 rows=357 width=0) (actual time=0.035..0.035 rows=357 loops=1)

  • Index Cond: ((servicetype)::text = 'MERCHPAY'::text)
19. 0.934 0.975 ↓ 1.0 437 1

Bitmap Heap Scan on fact_transaction_header_jan_04_2019_002 ft_2 (cost=15.76..324.33 rows=422 width=1,029) (actual time=0.064..0.975 rows=437 loops=1)

  • Recheck Cond: ((servicetype)::text = 'MERCHPAY'::text)
  • Filter: ((transfer_date >= '2019-01-04 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-05 00:00:00'::timestamp without time zone) AND ((transfer_status)::text = 'TS'::text) (...)
  • Rows Removed by Filter: 12
  • Heap Blocks: exact=221
20. 0.041 0.041 ↑ 1.0 449 1

Bitmap Index Scan on idx_servicetype_jan_04_2019_002 (cost=0.00..15.65 rows=449 width=0) (actual time=0.041..0.041 rows=449 loops=1)

  • Index Cond: ((servicetype)::text = 'MERCHPAY'::text)
21. 0.882 0.919 ↓ 1.0 412 1

Bitmap Heap Scan on fact_transaction_header_jan_04_2019_003 ft_3 (cost=15.53..329.65 rows=397 width=1,029) (actual time=0.059..0.919 rows=412 loops=1)

  • Recheck Cond: ((servicetype)::text = 'MERCHPAY'::text)
  • Filter: ((transfer_date >= '2019-01-04 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-05 00:00:00'::timestamp without time zone) AND ((transfer_status)::text = 'TS'::text) (...)
  • Rows Removed by Filter: 8
  • Heap Blocks: exact=215
22. 0.037 0.037 ↑ 1.0 420 1

Bitmap Index Scan on idx_servicetype_jan_04_2019_003 (cost=0.00..15.43 rows=420 width=0) (actual time=0.037..0.037 rows=420 loops=1)

  • Index Cond: ((servicetype)::text = 'MERCHPAY'::text)
23. 0.896 0.934 ↓ 1.0 392 1

Bitmap Heap Scan on fact_transaction_header_jan_04_2019_004 ft_4 (cost=15.43..330.82 rows=384 width=1,030) (actual time=0.061..0.934 rows=392 loops=1)

  • Recheck Cond: ((servicetype)::text = 'MERCHPAY'::text)
  • Filter: ((transfer_date >= '2019-01-04 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-05 00:00:00'::timestamp without time zone) AND ((transfer_status)::text = 'TS'::text) (...)
  • Rows Removed by Filter: 15
  • Heap Blocks: exact=220
24. 0.038 0.038 ↑ 1.0 407 1

Bitmap Index Scan on idx_servicetype_jan_04_2019_004 (cost=0.00..15.34 rows=407 width=0) (actual time=0.038..0.038 rows=407 loops=1)

  • Index Cond: ((servicetype)::text = 'MERCHPAY'::text)
25. 0.901 0.941 ↓ 1.0 413 1

Bitmap Heap Scan on fact_transaction_header_jan_04_2019_005 ft_5 (cost=15.58..325.00 rows=403 width=1,030) (actual time=0.064..0.941 rows=413 loops=1)

  • Recheck Cond: ((servicetype)::text = 'MERCHPAY'::text)
  • Filter: ((transfer_date >= '2019-01-04 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-05 00:00:00'::timestamp without time zone) AND ((transfer_status)::text = 'TS'::text) (...)
  • Rows Removed by Filter: 13
  • Heap Blocks: exact=215
26. 0.040 0.040 ↑ 1.0 426 1

Bitmap Index Scan on idx_servicetype_jan_04_2019_005 (cost=0.00..15.48 rows=426 width=0) (actual time=0.040..0.040 rows=426 loops=1)

  • Index Cond: ((servicetype)::text = 'MERCHPAY'::text)
27. 0.933 0.982 ↓ 1.0 428 1

Bitmap Heap Scan on fact_transaction_header_jan_04_2019_006 ft_6 (cost=15.70..322.31 rows=418 width=1,030) (actual time=0.075..0.982 rows=428 loops=1)

  • Recheck Cond: ((servicetype)::text = 'MERCHPAY'::text)
  • Filter: ((transfer_date >= '2019-01-04 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-05 00:00:00'::timestamp without time zone) AND ((transfer_status)::text = 'TS'::text) (...)
  • Rows Removed by Filter: 14
  • Heap Blocks: exact=230
28. 0.049 0.049 ↑ 1.0 442 1

Bitmap Index Scan on idx_servicetype_jan_04_2019_006 (cost=0.00..15.60 rows=442 width=0) (actual time=0.048..0.049 rows=442 loops=1)

  • Index Cond: ((servicetype)::text = 'MERCHPAY'::text)
29. 0.896 0.932 ↓ 1.0 413 1

Bitmap Heap Scan on fact_transaction_header_jan_04_2019_007 ft_7 (cost=15.59..326.06 rows=403 width=1,029) (actual time=0.059..0.932 rows=413 loops=1)

  • Recheck Cond: ((servicetype)::text = 'MERCHPAY'::text)
  • Filter: ((transfer_date >= '2019-01-04 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-05 00:00:00'::timestamp without time zone) AND ((transfer_status)::text = 'TS'::text) (...)
  • Rows Removed by Filter: 14
  • Heap Blocks: exact=229
30. 0.036 0.036 ↑ 1.0 427 1

Bitmap Index Scan on idx_servicetype_jan_04_2019_007 (cost=0.00..15.49 rows=427 width=0) (actual time=0.035..0.036 rows=427 loops=1)

  • Index Cond: ((servicetype)::text = 'MERCHPAY'::text)
31. 0.862 0.908 ↓ 1.0 405 1

Bitmap Heap Scan on fact_transaction_header_jan_04_2019_008 ft_8 (cost=15.53..321.76 rows=397 width=1,029) (actual time=0.070..0.908 rows=405 loops=1)

  • Recheck Cond: ((servicetype)::text = 'MERCHPAY'::text)
  • Filter: ((transfer_date >= '2019-01-04 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-05 00:00:00'::timestamp without time zone) AND ((transfer_status)::text = 'TS'::text) (...)
  • Rows Removed by Filter: 15
  • Heap Blocks: exact=226
32. 0.046 0.046 ↑ 1.0 420 1

Bitmap Index Scan on idx_servicetype_jan_04_2019_008 (cost=0.00..15.43 rows=420 width=0) (actual time=0.046..0.046 rows=420 loops=1)

  • Index Cond: ((servicetype)::text = 'MERCHPAY'::text)
33. 0.804 0.848 ↓ 1.0 388 1

Bitmap Heap Scan on fact_transaction_header_jan_04_2019_009 ft_9 (cost=15.41..324.25 rows=381 width=1,029) (actual time=0.066..0.848 rows=388 loops=1)

  • Recheck Cond: ((servicetype)::text = 'MERCHPAY'::text)
  • Filter: ((transfer_date >= '2019-01-04 00:00:00'::timestamp without time zone) AND (transfer_date < '2019-01-05 00:00:00'::timestamp without time zone) AND ((transfer_status)::text = 'TS'::text) (...)
  • Rows Removed by Filter: 16
  • Heap Blocks: exact=210
34. 0.044 0.044 ↑ 1.0 404 1

Bitmap Index Scan on idx_servicetype_jan_04_2019_009 (cost=0.00..15.31 rows=404 width=0) (actual time=0.044..0.044 rows=404 loops=1)

  • Index Cond: ((servicetype)::text = 'MERCHPAY'::text)
35. 0.005 0.017 ↑ 1.0 1 1

Hash (cost=1.54..1.54 rows=1 width=182) (actual time=0.017..0.017 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.012 0.012 ↑ 1.0 1 1

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

  • Filter: (((payment_instrument)::text = 'WALLET'::text) AND ((sub_payment_type_name)::text = 'Normal'::text))
  • Rows Removed by Filter: 35
37. 48.408 48.408 ↑ 1.0 1 4,034

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.012 rows=1 loops=4,034)

  • 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))
38. 44.374 44.374 ↑ 1.0 1 4,034

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=4,034)

  • Index Cond: ((CASE WHEN ((st.is_financial)::text = 'Y'::text) THEN ft.payee_user_id ELSE ft.scp_secpartyid END)::text = (user_id)::text)
39. 0.000 0.000 ↓ 0.0 0 4,034

Index Scan using idx_bank_id on dim_bank bnk (cost=0.14..0.17 rows=1 width=136) (actual time=0.000..0.000 rows=0 loops=4,034)

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