explain.depesz.com

PostgreSQL's explain analyze made readable

Result: grPi

Settings
# exclusive inclusive rows x rows loops node
1. 16.008 993,873.704 ↑ 1.0 4,293 1

Sort (cost=268,540,781.22..268,540,791.95 rows=4,293 width=244) (actual time=993,872.267..993,873.704 rows=4,293 loops=1)

  • Sort Key: ((tb_f_src_cash_balances_client.cp_entitycode)::character varying(3)) COLLATE "C", tb_f_src_cash_balances_client.cp_entity_account_id, tb_f_src_cash_balances_client.cp_currency COLLATE "C
  • Sort Method: quicksort Memory: 796kB
2.          

Initplan (for Sort)

3. 0.002 0.044 ↑ 1.0 1 1

Limit (cost=0.46..8,698.28 rows=1 width=4) (actual time=0.044..0.044 rows=1 loops=1)

4. 0.001 0.042 ↑ 1.0 1 1

Unique (cost=0.46..8,698.28 rows=1 width=4) (actual time=0.042..0.042 rows=1 loops=1)

5. 0.041 0.041 ↑ 131,631.0 1 1

Index Only Scan using ix_f_clientint_01 on tb_f_src_interest int_inner (cost=0.46..8,369.20 rows=131,631 width=4) (actual time=0.041..0.041 rows=1 loops=1)

  • Index Cond: ((in_businessdate >= (replace("substring"(((to_date('20190830'::text, 'yyyymmdd'::text) - '5 days'::interval day))::text, 1, 10), '-'::text, ''::text))::integer) AND (in_businessdate <= (replace("substring"(((to_date('20190830'::text, 'yyyymmdd'::text) + '5 days'::interval day))::text, 1, 10), '-'::text, ''::text))::integer))
  • Heap Fetches: 0
6. 12.837 993,857.652 ↑ 1.0 4,293 1

Nested Loop (cost=183.74..268,531,823.90 rows=4,293 width=244) (actual time=978,297.247..993,857.652 rows=4,293 loops=1)

7. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on tb_d_bdm_aacb_entities (cost=0.00..12.10 rows=1 width=54) (actual time=0.009..0.014 rows=1 loops=1)

  • Filter: (((em_entitycode)::text = 'FCA'::text) AND (em_activeind = 'Y'::bpchar))
  • Rows Removed by Filter: 9
8. 82.221 993,844.801 ↑ 1.0 4,293 1

Nested Loop Left Join (cost=183.74..268,531,725.94 rows=4,293 width=70) (actual time=978,297.221..993,844.801 rows=4,293 loops=1)

  • Join Filter: ((tb_f_src_cash_balances_client.cp_entity_account_id)::text = (tb_d_src_sf_identifier.si_entity_account_id)::text)
  • Rows Removed by Join Filter: 214,391
9. 7,021.928 993,689.599 ↑ 1.0 4,293 1

Nested Loop Left Join (cost=179.44..268,531,629.89 rows=4,293 width=36) (actual time=978,296.355..993,689.599 rows=4,293 loops=1)

  • Join Filter: ((tb_f_src_cash_balances_client.cp_activeind = int_outer.in_activeind) AND ((tb_f_src_cash_balances_client.cp_entitycode)::text = (int_outer.in_entitycode)::text) AND ((tb_f_src_cash_balances_client.cp_entity_account_id)::text = (int_outer.in_localclientid)::text) AND ((tb_f_src_cash_balances_client.cp_currency)::text = (int_outer.in_interest_currency)::text))
  • Rows Removed by Join Filter: 16,236,723
10. 2.826 3.188 ↑ 1.0 4,293 1

Bitmap Heap Scan on tb_f_src_cash_balances_client (cost=179.02..527.15 rows=4,293 width=20) (actual time=0.373..3.188 rows=4,293 loops=1)

  • Recheck Cond: ((cp_businessdate = 20,190,830) AND ((cp_entitycode)::text = 'FCA'::text) AND (cp_activeind = 'Y'::bpchar))
  • Heap Blocks: exact=51
11. 0.362 0.362 ↑ 1.0 4,293 1

Bitmap Index Scan on ix_f_clientcashp_01 (cost=0.00..177.95 rows=4,293 width=0) (actual time=0.362..0.362 rows=4,293 loops=1)

  • Index Cond: ((cp_businessdate = 20,190,830) AND ((cp_entitycode)::text = 'FCA'::text) AND (cp_activeind = 'Y'::bpchar))
12. 5,376.585 986,664.483 ↓ 17.3 3,783 4,293

Materialize (cost=0.42..268,509,949.53 rows=219 width=37) (actual time=0.006..229.831 rows=3,783 loops=4,293)

13. 417.563 981,287.898 ↓ 17.3 3,783 1

Index Scan using ix_f_clientint_01 on tb_f_src_interest int_outer (cost=0.42..268,509,948.43 rows=219 width=37) (actual time=22.948..981,287.898 rows=3,783 loops=1)

  • Index Cond: ((in_businessdate = $0) AND ((in_entitycode)::text = 'FCA'::text) AND (in_activeind = 'Y'::bpchar))
  • Filter: (in_valuedate = (SubPlan 2))
  • Rows Removed by Filter: 40,094
14.          

SubPlan (for Index Scan)

15. 921.417 980,870.335 ↑ 1.0 1 43,877

Aggregate (cost=6,131.70..6,131.71 rows=1 width=4) (actual time=22.355..22.355 rows=1 loops=43,877)

16. 979,948.918 979,948.918 ↓ 9.5 19 43,877

Seq Scan on tb_f_src_interest int_inner_1 (cost=0.00..6,131.70 rows=2 width=4) (actual time=6.913..22.334 rows=19 loops=43,877)

  • Filter: (((in_localclientid)::text = (int_outer.in_localclientid)::text) AND (in_businessdate = int_outer.in_businessdate) AND (in_activeind = int_outer.in_activeind) AND ((in_entitycode)::text = (int_outer.in_entitycode)::text) AND ((in_interest_currency)::text = (int_outer.in_interest_currency)::text))
  • Rows Removed by Filter: 131,612
17. 72.184 72.981 ↓ 50.0 50 4,293

Materialize (cost=4.30..31.66 rows=1 width=34) (actual time=0.000..0.017 rows=50 loops=4,293)

18. 0.111 0.797 ↓ 50.0 50 1

Nested Loop (cost=4.30..31.66 rows=1 width=34) (actual time=0.166..0.797 rows=50 loops=1)

19. 0.236 0.236 ↓ 50.0 50 1

Seq Scan on tb_d_src_sf_identifier (cost=0.00..17.06 rows=1 width=22) (actual time=0.090..0.236 rows=50 loops=1)

  • Filter: ((si_record_startdate <= 20,190,830) AND (si_record_enddate > 20190830) AND (upper((si_source_system)::text) ~~ 'MICS%'::text))
  • Rows Removed by Filter: 253
20. 0.200 0.450 ↑ 1.0 1 50

Bitmap Heap Scan on tb_d_src_sf_entity (cost=4.30..14.59 rows=1 width=23) (actual time=0.008..0.009 rows=1 loops=50)

  • Recheck Cond: ((se_aacb_entity_unique_id)::text = (tb_d_src_sf_identifier.si_aacb_entity_unique_id)::text)
  • Filter: ((se_record_startdate <= 20,190,830) AND (se_record_enddate > 20190830))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=76
21. 0.250 0.250 ↑ 1.5 2 50

Bitmap Index Scan on ix_tb_d_sf_entity_01 (cost=0.00..4.30 rows=3 width=0) (actual time=0.005..0.005 rows=2 loops=50)

  • Index Cond: ((se_aacb_entity_unique_id)::text = (tb_d_src_sf_identifier.si_aacb_entity_unique_id)::text)
Planning time : 1.648 ms