explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4cdF

Settings
# exclusive inclusive rows x rows loops node
1. 150.571 840.865 ↓ 3,409.7 78,422 1

Nested Loop (cost=1,009.61..66,074.11 rows=23 width=2,704) (actual time=3.761..840.865 rows=78,422 loops=1)

2. 0.009 0.009 ↑ 1.0 1 1

Index Scan using dim_servicetype_pkey on dim_servicetype st (cost=0.15..8.17 rows=1 width=28) (actual time=0.007..0.009 rows=1 loops=1)

  • Index Cond: (service_dimid = 21)
3. 93.448 690.285 ↓ 3,409.7 78,422 1

Nested Loop (cost=1,009.46..66,063.48 rows=23 width=1,653) (actual time=3.743..690.285 rows=78,422 loops=1)

4. 0.007 0.007 ↑ 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.004..0.007 rows=1 loops=1)

  • Index Cond: (provider_id = 101)
5. 0.000 596.830 ↓ 3,409.7 78,422 1

Gather (cost=1,009.32..66,055.09 rows=23 width=1,479) (actual time=3.735..596.830 rows=78,422 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 133.806 721.862 ↓ 2,614.1 26,141 3

Nested Loop (cost=9.32..65,052.79 rows=10 width=1,479) (actual time=0.290..721.862 rows=26,141 loops=3)

7. 151.212 588.053 ↓ 2,614.1 26,141 3

Nested Loop (cost=8.89..64,992.42 rows=10 width=1,415) (actual time=0.270..588.053 rows=26,141 loops=3)

8. 88.181 436.837 ↓ 2,614.1 26,141 3

Nested Loop (cost=8.46..64,932.00 rows=10 width=1,353) (actual time=0.236..436.837 rows=26,141 loops=3)

9. 93.378 348.655 ↓ 2,178.4 26,141 3

Nested Loop (cost=8.32..64,930.00 rows=12 width=1,139) (actual time=0.220..348.655 rows=26,141 loops=3)

10. 35.775 255.276 ↓ 1,188.2 26,141 3

Hash Join (cost=8.17..64,926.38 rows=22 width=747) (actual time=0.203..255.276 rows=26,141 loops=3)

  • Hash Cond: (ft.mp_payer_payinstrument_dimid = pin.payinstrument_dimid)
11. 26.790 219.465 ↓ 5.9 26,141 3

Parallel Append (cost=0.00..64,906.24 rows=4,466 width=355) (actual time=0.063..219.465 rows=26,141 loops=3)

12. 192.675 192.675 ↓ 5.9 26,141 3

Parallel Seq Scan on fact_transaction_header_dec_26_2018 ft (cost=0.00..64,883.91 rows=4,466 width=355) (actual time=0.061..192.675 rows=26,141 loops=3)

  • Filter: ((transfer_date >= '2018-12-26 00:00:00'::timestamp without time zone) AND (transfer_date < '2018-12-27 00:00:00'::timestamp without time zone) AND (service_type_dimid = 21) AND ((servicetype) (...)
  • Rows Removed by Filter: 162398
13. 0.005 0.036 ↑ 1.0 1 3

Hash (cost=8.16..8.16 rows=1 width=400) (actual time=0.036..0.036 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.031 0.031 ↑ 1.0 1 3

Index Scan using idx_sub_payment_type_name on dim_payinstrument pin (cost=0.14..8.16 rows=1 width=400) (actual time=0.029..0.031 rows=1 loops=3)

  • Index Cond: ((sub_payment_type_name)::text = 'Normal'::text)
  • Filter: ((payment_instrument)::text = 'WALLET'::text)
15. 0.001 0.001 ↑ 1.0 1 78,422

Index Scan using dim_payinstrument_pkey on dim_payinstrument pint (cost=0.14..0.16 rows=1 width=400) (actual time=0.001..0.001 rows=1 loops=78,422)

  • Index Cond: (payinstrument_dimid = ft.mp_payee_payinstrument_dimid)
16. 0.001 0.001 ↑ 1.0 1 78,422

Index Scan using dim_transtatus_pkey on dim_transtatus dt (cost=0.14..0.17 rows=1 width=222) (actual time=0.001..0.001 rows=1 loops=78,422)

  • Index Cond: (transtatus_dimid = ft.transtatus_dimid)
17. 0.004 0.004 ↑ 1.0 1 78,422

Index Scan using dim_user_pkey on dim_user u (cost=0.43..6.04 rows=1 width=70) (actual time=0.004..0.004 rows=1 loops=78,422)

  • Index Cond: (user_dimid = ft.payer_user_dimid)
  • Filter: ((partyacc_providerdimid = 101) AND ((user_type)::text = 'SUBSCRIBER'::text))
18. 0.003 0.003 ↑ 1.0 1 78,422

Index Scan using dim_user_pkey on dim_user ue (cost=0.43..6.04 rows=1 width=72) (actual time=0.003..0.003 rows=1 loops=78,422)

  • Index Cond: (user_dimid = ft.payee_user_dimid)
Planning time : 11.737 ms
Execution time : 885.508 ms