explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7JPvV

Settings
# exclusive inclusive rows x rows loops node
1. 3.355 37.693 ↓ 75.9 911 1

Sort (cost=12,661.13..12,661.16 rows=12 width=1,160) (actual time=37.640..37.693 rows=911 loops=1)

  • Sort Key: s.fund_id, s.anum, (0), (0), (((to_date('20200630'::text, 'yyyymmdd'::text)))::timestamp without time zone), (1000000000)
  • Sort Method: quicksort Memory: 264kB
2. 0.105 34.338 ↓ 75.9 911 1

Append (cost=0.43..12,660.91 rows=12 width=1,160) (actual time=13.007..34.338 rows=911 loops=1)

3. 0.021 29.434 ↓ 11.2 90 1

Result (cost=0.43..8,308.28 rows=8 width=1,158) (actual time=13.006..29.434 rows=90 loops=1)

4. 0.012 29.413 ↓ 11.2 90 1

Append (cost=0.43..8,308.18 rows=8 width=1,154) (actual time=13.004..29.413 rows=90 loops=1)

5. 0.044 28.092 ↓ 11.2 45 1

Nested Loop Left Join (cost=0.43..4,154.03 rows=4 width=888) (actual time=13.003..28.092 rows=45 loops=1)

  • Join Filter: (fnd.fund_group_id = fg.fund_group_id)
6. 0.009 28.048 ↓ 11.2 45 1

Nested Loop (cost=0.43..4,105.59 rows=4 width=47) (actual time=12.993..28.048 rows=45 loops=1)

7. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on fund fnd (cost=0.00..2.81 rows=1 width=15) (actual time=0.005..0.012 rows=1 loops=1)

  • Filter: (fund_id = 8)
  • Rows Removed by Filter: 64
8. 0.495 28.027 ↓ 11.2 45 1

Nested Loop (cost=0.43..4,102.74 rows=4 width=36) (actual time=12.985..28.027 rows=45 loops=1)

9. 0.058 0.058 ↑ 1.0 482 1

Seq Scan on trialbal tb (cost=0.00..12.82 rows=482 width=24) (actual time=0.003..0.058 rows=482 loops=1)

10. 27.474 27.474 ↓ 0.0 0 482

Index Scan using balance_pk on balance s (cost=0.43..8.48 rows=1 width=20) (actual time=0.057..0.057 rows=0 loops=482)

  • Index Cond: ((fund_id = 8) AND (anum = tb.anum) AND (akey = tb.anum) AND (value_dt = to_date('20200630'::text, 'yyyymmdd'::text)))
  • Filter: ((anum = akey) AND (anum = ANY ('{13001,13002,13003,13004,13501,13502,13503,13504,13506,13507,13508,22001,22002,22003,22501,22502,22503,22504,23001,23002,23003,23004,23005,23007,23008,23009,23010,23011,23012,23013,23014,23015,23501,23502,24001,24002,24003,24501,30001,30501,30502,30503,31001,31002,33001,40000,40500,41501,41503,41509,51501,51502,51503,51504,51505,52001,52002,52003,52501,52502,52503,52504,52505,52506,52507,53001,53002,53501,53506,53507,53508,53509}'::integer[])))
11. 0.000 0.000 ↓ 0.0 0 45

Materialize (cost=0.00..17.95 rows=530 width=122) (actual time=0.000..0.000 rows=0 loops=45)

12. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on fund_group fg (cost=0.00..15.30 rows=530 width=122) (actual time=0.002..0.002 rows=0 loops=1)

13. 0.034 1.309 ↓ 11.2 45 1

Nested Loop Left Join (cost=0.43..4,154.03 rows=4 width=917) (actual time=0.175..1.309 rows=45 loops=1)

  • Join Filter: (fnd_1.fund_group_id = fg_1.fund_group_id)
14. 0.007 1.275 ↓ 11.2 45 1

Nested Loop (cost=0.43..4,105.59 rows=4 width=47) (actual time=0.169..1.275 rows=45 loops=1)

15. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on fund fnd_1 (cost=0.00..2.81 rows=1 width=15) (actual time=0.003..0.007 rows=1 loops=1)

  • Filter: (fund_id = 8)
  • Rows Removed by Filter: 64
16. 0.253 1.261 ↓ 11.2 45 1

Nested Loop (cost=0.43..4,102.74 rows=4 width=36) (actual time=0.165..1.261 rows=45 loops=1)

17. 0.044 0.044 ↑ 1.0 482 1

Seq Scan on trialbal tb_1 (cost=0.00..12.82 rows=482 width=24) (actual time=0.002..0.044 rows=482 loops=1)

18. 0.964 0.964 ↓ 0.0 0 482

Index Scan using balance_pk on balance s_1 (cost=0.43..8.48 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=482)

  • Index Cond: ((fund_id = 8) AND (anum = tb_1.anum) AND (akey = tb_1.anum) AND (value_dt = to_date('20191231'::text, 'yyyymmdd'::text)))
  • Filter: ((anum = akey) AND (anum = ANY ('{13001,13002,13003,13004,13501,13502,13503,13504,13506,13507,13508,22001,22002,22003,22501,22502,22503,22504,23001,23002,23003,23004,23005,23007,23008,23009,23010,23011,23012,23013,23014,23015,23501,23502,24001,24002,24003,24501,30001,30501,30502,30503,31001,31002,33001,40000,40500,41501,41503,41509,51501,51502,51503,51504,51505,52001,52002,52003,52501,52502,52503,52504,52505,52506,52507,53001,53002,53501,53506,53507,53508,53509}'::integer[])))
19. 0.000 0.000 ↓ 0.0 0 45

Materialize (cost=0.00..17.95 rows=530 width=122) (actual time=0.000..0.000 rows=0 loops=45)

20. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on fund_group fg_1 (cost=0.00..15.30 rows=530 width=122) (actual time=0.001..0.001 rows=0 loops=1)

21. 0.197 4.799 ↓ 205.2 821 1

Subquery Scan on *SELECT* 3 (cost=0.99..4,352.58 rows=4 width=417) (actual time=0.225..4.799 rows=821 loops=1)

22. 0.583 4.602 ↓ 205.2 821 1

Nested Loop Left Join (cost=0.99..4,352.53 rows=4 width=389) (actual time=0.223..4.602 rows=821 loops=1)

  • Join Filter: (fnd_2.fund_group_id = fg_2.fund_group_id)
23. 0.128 4.019 ↓ 205.2 821 1

Nested Loop (cost=0.99..4,304.06 rows=4 width=128) (actual time=0.211..4.019 rows=821 loops=1)

24. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on fund fnd_2 (cost=0.00..2.81 rows=1 width=15) (actual time=0.003..0.007 rows=1 loops=1)

  • Filter: (fund_id = 8)
  • Rows Removed by Filter: 64
25. 0.143 3.884 ↓ 205.2 821 1

Nested Loop (cost=0.99..4,301.21 rows=4 width=117) (actual time=0.207..3.884 rows=821 loops=1)

26. 0.611 2.099 ↓ 68.4 821 1

Nested Loop (cost=0.56..4,199.81 rows=12 width=59) (actual time=0.194..2.099 rows=821 loops=1)

27. 0.042 0.042 ↑ 1.0 482 1

Seq Scan on trialbal tb_2 (cost=0.00..12.82 rows=482 width=24) (actual time=0.001..0.042 rows=482 loops=1)

28. 1.446 1.446 ↓ 2.0 2 482

Index Scan using entries_ind1 on entries s_2 (cost=0.56..8.68 rows=1 width=43) (actual time=0.002..0.003 rows=2 loops=482)

  • Index Cond: ((fund_id = 8) AND (akey = tb_2.anum) AND (anum = tb_2.anum) AND (value_dt >= to_date('20200101'::text, 'yyyymmdd'::text)) AND (value_dt <= to_date('20200614'::text, 'yyyymmdd'::text)))
  • Filter: ((anum = akey) AND (anum = ANY ('{13001,13002,13003,13004,13501,13502,13503,13504,13506,13507,13508,22001,22002,22003,22501,22502,22503,22504,23001,23002,23003,23004,23005,23007,23008,23009,23010,23011,23012,23013,23014,23015,23501,23502,24001,24002,24003,24501,30001,30501,30502,30503,31001,31002,33001,40000,40500,41501,41503,41509,51501,51502,51503,51504,51505,52001,52002,52003,52501,52502,52503,52504,52505,52506,52507,53001,53002,53501,53506,53507,53508,53509}'::integer[])))
29. 1.642 1.642 ↑ 1.0 1 821

Index Scan using trans_pk on trans t (cost=0.43..8.45 rows=1 width=62) (actual time=0.002..0.002 rows=1 loops=821)

  • Index Cond: ((fund_id = 8) AND (trans_id = s_2.trans_id))
30. 0.000 0.000 ↓ 0.0 0 821

Materialize (cost=0.00..17.95 rows=530 width=122) (actual time=0.000..0.000 rows=0 loops=821)

31. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on fund_group fg_2 (cost=0.00..15.30 rows=530 width=122) (actual time=0.001..0.001 rows=0 loops=1)

Planning time : 2.873 ms
Execution time : 41.257 ms