explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wTCE

Settings
# exclusive inclusive rows x rows loops node
1. 297.287 297.287 ↓ 5.0 5 1

CTE Scan on data_final (cost=1,996.65..1,996.67 rows=1 width=451) (actual time=297.046..297.287 rows=5 loops=1)

2.          

CTE certificados

3. 5.329 5.329 ↓ 82.0 82 1

Index Scan using tb_dist_certificate_idx2 on tb_dist_certificate (cost=0.43..9.87 rows=1 width=73) (actual time=0.180..5.329 rows=82 loops=1)

  • Index Cond: ((id_contract = 8) AND (id_customer = 18,163) AND (cd_account = 218,740))
  • Filter: (qtd_quotes > '0'::numeric)
  • Rows Removed by Filter: 1,297
4.          

CTE instrumentos_raw

5. 2.982 31.586 ↓ 5.7 3,394 1

WindowAgg (cost=1,805.70..1,817.52 rows=591 width=119) (actual time=28.361..31.586 rows=3,394 loops=1)

6. 4.571 28.604 ↓ 5.7 3,394 1

Sort (cost=1,805.70..1,807.17 rows=591 width=111) (actual time=28.347..28.604 rows=3,394 loops=1)

  • Sort Key: tb_dist_fund_daily.id_instrument, tb_dist_fund_daily.dt_reference DESC
  • Sort Method: quicksort Memory: 575kB
7. 1.900 24.033 ↓ 5.7 3,394 1

Nested Loop (cost=13.03..1,778.49 rows=591 width=111) (actual time=5.441..24.033 rows=3,394 loops=1)

8. 0.038 5.223 ↓ 5.0 5 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=5.214..5.223 rows=5 loops=1)

  • Group Key: certificados.id_instrument
9. 5.185 5.185 ↓ 82.0 82 1

CTE Scan on certificados (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..5.185 rows=82 loops=1)

10. 16.265 16.910 ↓ 1.1 679 5

Bitmap Heap Scan on tb_dist_fund_daily (cost=13.00..1,772.55 rows=591 width=111) (actual time=0.211..3.382 rows=679 loops=5)

  • Recheck Cond: (id_instrument = certificados.id_instrument)
  • Heap Blocks: exact=2,418
11. 0.645 0.645 ↓ 1.1 679 5

Bitmap Index Scan on tb_dist_fund_daily_id_instrument_desc_idx (cost=0.00..12.86 rows=591 width=0) (actual time=0.129..0.129 rows=679 loops=5)

  • Index Cond: (id_instrument = certificados.id_instrument)
12.          

CTE instrumentos

13. 34.230 34.230 ↓ 1.7 5 1

CTE Scan on instrumentos_raw (cost=0.00..13.30 rows=3 width=267) (actual time=28.366..34.230 rows=5 loops=1)

  • Filter: (vl_index = 1)
  • Rows Removed by Filter: 3,389
14.          

CTE certificados_instrumentos

15. 0.080 34.681 ↓ 5.0 5 1

HashAggregate (cost=0.14..0.15 rows=1 width=56) (actual time=34.679..34.681 rows=5 loops=1)

  • Group Key: certificados_1.cd_account, instrumentos.dt_reference, certificados_1.id_contract, certificados_1.id_customer, certificados_1.id_instrument, certificados_1.id_manager, instrumentos.vl_quote_final
16. 0.131 34.601 ↓ 82.0 82 1

Nested Loop Left Join (cost=0.00..0.12 rows=1 width=56) (actual time=28.553..34.601 rows=82 loops=1)

  • Join Filter: ((certificados_1.id_contract = instrumentos.id_contract) AND (certificados_1.id_instrument = instrumentos.id_instrument))
  • Rows Removed by Join Filter: 328
17. 0.194 0.194 ↓ 82.0 82 1

CTE Scan on certificados certificados_1 (cost=0.00..0.02 rows=1 width=20) (actual time=0.181..0.194 rows=82 loops=1)

18. 34.276 34.276 ↓ 1.7 5 82

CTE Scan on instrumentos (cost=0.00..0.06 rows=3 width=44) (actual time=0.346..0.418 rows=5 loops=82)

19.          

CTE daily_raw_indexed

20. 59.612 218.492 ↓ 1,517.1 31,860 1

WindowAgg (cost=88.82..89.35 rows=21 width=141) (actual time=153.495..218.492 rows=31,860 loops=1)

21. 63.007 158.880 ↓ 1,517.1 31,860 1

Sort (cost=88.82..88.87 rows=21 width=133) (actual time=153.470..158.880 rows=31,860 loops=1)

  • Sort Key: tb_dist_certificate_daily.id_customer, tb_dist_certificate_daily.id_contract, tb_dist_certificate_daily.id_instrument, tb_dist_certificate_daily.dt_reference DESC
  • Sort Method: quicksort Memory: 8,477kB
22. 21.495 95.873 ↓ 1,517.1 31,860 1

Nested Loop (cost=0.59..88.36 rows=21 width=133) (actual time=0.052..95.873 rows=31,860 loops=1)

23. 0.012 0.013 ↓ 5.0 5 1

HashAggregate (cost=0.03..0.04 rows=1 width=12) (actual time=0.006..0.013 rows=5 loops=1)

  • Group Key: certificados_instrumentos.id_customer, certificados_instrumentos.id_contract, certificados_instrumentos.id_instrument
24. 0.001 0.001 ↓ 5.0 5 1

CTE Scan on certificados_instrumentos (cost=0.00..0.02 rows=1 width=12) (actual time=0.000..0.001 rows=5 loops=1)

25. 74.365 74.365 ↓ 303.4 6,372 5

Index Scan using tb_dist_certificate_daily_pk on tb_dist_certificate_daily (cost=0.57..88.11 rows=21 width=133) (actual time=0.025..14.873 rows=6,372 loops=5)

  • Index Cond: ((id_customer = certificados_instrumentos.id_customer) AND (id_instrument = certificados_instrumentos.id_instrument) AND (id_contract = certificados_instrumentos.id_contract))
26.          

CTE daily_raw

27. 259.030 259.030 ↓ 82.0 82 1

CTE Scan on daily_raw_indexed (cost=0.00..0.47 rows=1 width=411) (actual time=153.500..259.030 rows=82 loops=1)

  • Filter: (vl_index = 1)
  • Rows Removed by Filter: 31,778
28.          

CTE daily_certificado

29. 0.151 261.669 ↓ 82.0 82 1

Nested Loop (cost=0.43..8.48 rows=1 width=415) (actual time=153.530..261.669 rows=82 loops=1)

30. 259.058 259.058 ↓ 82.0 82 1

CTE Scan on daily_raw (cost=0.00..0.02 rows=1 width=411) (actual time=153.501..259.058 rows=82 loops=1)

31. 2.460 2.460 ↑ 1.0 1 82

Index Scan using tb_dist_certificate_idx2 on tb_dist_certificate tb_dist_certificate_1 (cost=0.43..8.45 rows=1 width=20) (actual time=0.030..0.030 rows=1 loops=82)

  • Index Cond: ((id_contract = daily_raw.id_contract) AND (id_customer = daily_raw.id_customer) AND (id_instrument = daily_raw.id_instrument))
  • Filter: (daily_raw.id_certificate = id_certificate)
  • Rows Removed by Filter: 33
32.          

CTE daily

33. 0.275 262.064 ↓ 5.0 5 1

HashAggregate (cost=0.07..0.10 rows=1 width=304) (actual time=262.052..262.064 rows=5 loops=1)

  • Group Key: daily_certificado.id_customer, daily_certificado.id_contract, daily_certificado.id_instrument, daily_certificado.cd_account
34. 261.789 261.789 ↓ 82.0 82 1

CTE Scan on daily_certificado (cost=0.00..0.02 rows=1 width=214) (actual time=153.533..261.789 rows=82 loops=1)

35.          

CTE withdraws_raw

36. 0.001 0.127 ↓ 0.0 0 1

WindowAgg (cost=8.54..8.57 rows=1 width=33) (actual time=0.127..0.127 rows=0 loops=1)

37. 0.007 0.126 ↓ 0.0 0 1

Sort (cost=8.54..8.55 rows=1 width=25) (actual time=0.125..0.126 rows=0 loops=1)

  • Sort Key: tb_dist_operation_withdraw.id_customer, tb_dist_operation_withdraw.cd_account, tb_dist_operation_withdraw.id_instrument, tb_dist_operation_withdraw.id_contract, tb_dist_operation_withdraw.id_status_order, tb_dist_operation_withdraw.dt_liquidation DESC
  • Sort Method: quicksort Memory: 25kB
38. 0.001 0.119 ↓ 0.0 0 1

Group (cost=8.51..8.53 rows=1 width=25) (actual time=0.119..0.119 rows=0 loops=1)

  • Group Key: tb_dist_operation_withdraw.id_customer, tb_dist_operation_withdraw.cd_account, tb_dist_operation_withdraw.id_instrument, tb_dist_operation_withdraw.id_contract, tb_dist_operation_withdraw.id_status_order, tb_dist_operation_withdraw.dt_liquidation, tb_dist_operation_withdraw.is_total_withdraw
39. 0.035 0.118 ↓ 0.0 0 1

Sort (cost=8.51..8.51 rows=1 width=25) (actual time=0.118..0.118 rows=0 loops=1)

  • Sort Key: tb_dist_operation_withdraw.id_customer, tb_dist_operation_withdraw.cd_account, tb_dist_operation_withdraw.id_instrument, tb_dist_operation_withdraw.id_contract, tb_dist_operation_withdraw.id_status_order, tb_dist_operation_withdraw.dt_liquidation, tb_dist_operation_withdraw.is_total_withdraw
  • Sort Method: quicksort Memory: 25kB
40. 0.005 0.083 ↓ 0.0 0 1

Nested Loop (cost=0.45..8.50 rows=1 width=25) (actual time=0.083..0.083 rows=0 loops=1)

41. 0.013 0.018 ↓ 5.0 5 1

HashAggregate (cost=0.03..0.04 rows=1 width=16) (actual time=0.017..0.018 rows=5 loops=1)

  • Group Key: certificados_instrumentos_1.id_customer, certificados_instrumentos_1.cd_account, certificados_instrumentos_1.id_contract, certificados_instrumentos_1.id_instrument
42. 0.005 0.005 ↓ 5.0 5 1

CTE Scan on certificados_instrumentos certificados_instrumentos_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.000..0.005 rows=5 loops=1)

43. 0.060 0.060 ↓ 0.0 0 5

Index Scan using tb_dist_operation_withdraw_quote_erosin_idx on tb_dist_operation_withdraw (cost=0.42..8.45 rows=1 width=25) (actual time=0.012..0.012 rows=0 loops=5)

  • Index Cond: ((id_contract = certificados_instrumentos_1.id_contract) AND (id_customer = certificados_instrumentos_1.id_customer) AND (id_instrument = certificados_instrumentos_1.id_instrument))
  • Filter: ((certificados_instrumentos_1.cd_account = cd_account) AND (id_status_order = ANY ('{1,2,3,7}'::integer[])))
  • Rows Removed by Filter: 1
44.          

CTE withdraws

45. 0.127 0.127 ↓ 0.0 0 1

CTE Scan on withdraws_raw (cost=0.00..0.02 rows=1 width=33) (actual time=0.127..0.127 rows=0 loops=1)

  • Filter: (vl_index = 1)
46.          

CTE data_final

47. 0.019 297.275 ↓ 5.0 5 1

Nested Loop Left Join (cost=0.97..48.82 rows=1 width=445) (actual time=297.042..297.275 rows=5 loops=1)

48. 0.007 297.071 ↓ 5.0 5 1

Nested Loop Left Join (cost=0.82..17.08 rows=1 width=450) (actual time=296.942..297.071 rows=5 loops=1)

  • Join Filter: (certificados_instrumentos_2.id_contract = tb_dist_class.id_contract)
49. 0.007 297.054 ↓ 5.0 5 1

Nested Loop Left Join (cost=0.69..16.85 rows=1 width=418) (actual time=296.937..297.054 rows=5 loops=1)

50. 0.018 296.992 ↓ 5.0 5 1

Nested Loop Left Join (cost=0.42..8.54 rows=1 width=358) (actual time=296.909..296.992 rows=5 loops=1)

  • Join Filter: ((certificados_instrumentos_2.id_customer = daily.id_customer) AND (certificados_instrumentos_2.id_contract = daily.id_contract) AND (certificados_instrumentos_2.id_instrument = daily.id_instrument) AND (certificados_instrumentos_2.cd_account = daily.cd_account))
  • Rows Removed by Join Filter: 20
51. 0.011 34.899 ↓ 5.0 5 1

Nested Loop Left Join (cost=0.42..8.50 rows=1 width=70) (actual time=34.850..34.899 rows=5 loops=1)

52. 0.005 34.818 ↓ 5.0 5 1

Nested Loop Left Join (cost=0.00..0.06 rows=1 width=61) (actual time=34.811..34.818 rows=5 loops=1)

  • Join Filter: ((certificados_instrumentos_2.id_customer = withdraws.id_customer) AND (certificados_instrumentos_2.cd_account = withdraws.cd_account) AND (certificados_instrumentos_2.id_instrument = withdraws.id_instrument) AND (certificados_instrumentos_2.id_contract = withdraws.id_contract))
53. 34.683 34.683 ↓ 5.0 5 1

CTE Scan on certificados_instrumentos certificados_instrumentos_2 (cost=0.00..0.02 rows=1 width=56) (actual time=34.680..34.683 rows=5 loops=1)

54. 0.130 0.130 ↓ 0.0 0 5

CTE Scan on withdraws (cost=0.00..0.02 rows=1 width=21) (actual time=0.026..0.026 rows=0 loops=5)

55. 0.070 0.070 ↑ 1.0 1 5

Index Scan using tb_dist_custody_pk on tb_dist_custody (cost=0.42..8.44 rows=1 width=17) (actual time=0.014..0.014 rows=1 loops=5)

  • Index Cond: ((certificados_instrumentos_2.id_customer = id_customer) AND (certificados_instrumentos_2.id_instrument = id_instrument) AND (certificados_instrumentos_2.id_contract = id_contract))
56. 262.075 262.075 ↓ 5.0 5 5

CTE Scan on daily (cost=0.00..0.02 rows=1 width=304) (actual time=52.411..52.415 rows=5 loops=5)

57. 0.055 0.055 ↑ 1.0 1 5

Index Scan using tb_dist_fund_selected_pk on tb_dist_fund_selected (cost=0.28..8.29 rows=1 width=68) (actual time=0.010..0.011 rows=1 loops=5)

  • Index Cond: ((certificados_instrumentos_2.id_contract = id_contract) AND (certificados_instrumentos_2.id_instrument = id_instrument))
58. 0.010 0.010 ↓ 0.0 0 5

Index Scan using tb_dist_class_pk on tb_dist_class (cost=0.13..0.22 rows=1 width=40) (actual time=0.002..0.002 rows=0 loops=5)

  • Index Cond: (tb_dist_fund_selected.id_dist_class = id_dist_class)
59. 0.030 0.030 ↑ 1.0 1 5

Index Scan using tb_dist_manager_pk on tb_dist_manager (cost=0.15..0.17 rows=1 width=34) (actual time=0.006..0.006 rows=1 loops=5)

  • Index Cond: ((id_contract = tb_dist_custody.id_contract) AND (id_manager = tb_dist_custody.id_manager))
60.          

SubPlan (for Nested Loop Left Join)

61. 0.005 0.155 ↑ 1.0 1 5

Limit (cost=31.56..31.57 rows=1 width=4) (actual time=0.031..0.031 rows=1 loops=5)

62. 0.010 0.150 ↑ 1.0 1 5

Aggregate (cost=31.56..31.57 rows=1 width=4) (actual time=0.030..0.030 rows=1 loops=5)

63. 0.140 0.140 ↑ 1.0 1 5

Index Scan using tb_dist_operation_application_para_oms on tb_dist_operation_application (cost=0.42..31.56 rows=1 width=4) (actual time=0.024..0.028 rows=1 loops=5)

  • Index Cond: ((id_contract = certificados_instrumentos_2.id_contract) AND (id_status_order = 4) AND (cd_account = certificados_instrumentos_2.cd_account))
  • Filter: ((id_customer = certificados_instrumentos_2.id_customer) AND (id_instrument = certificados_instrumentos_2.id_instrument))
  • Rows Removed by Filter: 17
Planning time : 11.558 ms
Execution time : 302.611 ms