explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xpa

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

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

2.          

CTE certificados

3. 3.580 3.580 ↓ 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.138..3.580 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. 3.208 22.971 ↓ 5.7 3,394 1

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

6. 3.519 19.763 ↓ 5.7 3,394 1

Sort (cost=1,805.70..1,807.17 rows=591 width=111) (actual time=19.482..19.763 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.403 16.244 ↓ 5.7 3,394 1

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

8. 0.037 3.511 ↓ 5.0 5 1

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

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

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

10. 10.780 11.330 ↓ 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.186..2.266 rows=679 loops=5)

  • Recheck Cond: (id_instrument = certificados.id_instrument)
  • Heap Blocks: exact=2,418
11. 0.550 0.550 ↓ 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.110..0.110 rows=679 loops=5)

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

CTE instrumentos

13. 25.627 25.627 ↓ 1.7 5 1

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

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

CTE certificados_instrumentos

15. 0.080 26.038 ↓ 5.0 5 1

HashAggregate (cost=0.14..0.15 rows=1 width=56) (actual time=26.036..26.038 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.138 25.958 ↓ 82.0 82 1

Nested Loop Left Join (cost=0.00..0.12 rows=1 width=56) (actual time=19.644..25.958 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.154 0.154 ↓ 82.0 82 1

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

18. 25.666 25.666 ↓ 1.7 5 82

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

19.          

CTE daily_raw_indexed

20. 59.576 207.906 ↓ 1,517.1 31,860 1

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

21. 60.329 148.330 ↓ 1,517.1 31,860 1

Sort (cost=88.82..88.87 rows=21 width=133) (actual time=143.117..148.330 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. 20.774 88.001 ↓ 1,517.1 31,860 1

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

23. 0.021 0.022 ↓ 5.0 5 1

HashAggregate (cost=0.03..0.04 rows=1 width=12) (actual time=0.006..0.022 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. 67.205 67.205 ↓ 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.021..13.441 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. 246.276 246.276 ↓ 82.0 82 1

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

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

CTE daily_certificado

29. 0.105 248.621 ↓ 82.0 82 1

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

30. 246.302 246.302 ↓ 82.0 82 1

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

31. 2.214 2.214 ↑ 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.027..0.027 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.253 248.985 ↓ 5.0 5 1

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

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

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

35.          

CTE withdraws_raw

36. 0.001 0.115 ↓ 0.0 0 1

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

37. 0.007 0.114 ↓ 0.0 0 1

Sort (cost=8.54..8.55 rows=1 width=25) (actual time=0.114..0.114 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.107 ↓ 0.0 0 1

Group (cost=8.51..8.53 rows=1 width=25) (actual time=0.107..0.107 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.037 0.106 ↓ 0.0 0 1

Sort (cost=8.51..8.51 rows=1 width=25) (actual time=0.106..0.106 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.069 ↓ 0.0 0 1

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

41. 0.009 0.014 ↓ 5.0 5 1

HashAggregate (cost=0.03..0.04 rows=1 width=16) (actual time=0.013..0.014 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.050 0.050 ↓ 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.010..0.010 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.116 0.116 ↓ 0.0 0 1

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

  • Filter: (vl_index = 1)
46.          

CTE data_final

47. 0.023 275.730 ↓ 5.0 5 1

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

48. 0.009 275.412 ↓ 5.0 5 1

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

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

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

50. 0.018 275.263 ↓ 5.0 5 1

Nested Loop Left Join (cost=0.42..8.54 rows=1 width=358) (actual time=275.145..275.263 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 26.245 ↓ 5.0 5 1

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

52. 0.009 26.164 ↓ 5.0 5 1

Nested Loop Left Join (cost=0.00..0.06 rows=1 width=61) (actual time=26.157..26.164 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. 26.040 26.040 ↓ 5.0 5 1

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

54. 0.115 0.115 ↓ 0.0 0 5

CTE Scan on withdraws (cost=0.00..0.02 rows=1 width=21) (actual time=0.023..0.023 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. 249.000 249.000 ↓ 5.0 5 5

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

57. 0.110 0.110 ↑ 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.022..0.022 rows=1 loops=5)

  • Index Cond: ((certificados_instrumentos_2.id_contract = id_contract) AND (certificados_instrumentos_2.id_instrument = id_instrument))
58. 0.020 0.020 ↓ 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.004..0.004 rows=0 loops=5)

  • Index Cond: (tb_dist_fund_selected.id_dist_class = id_dist_class)
59. 0.035 0.035 ↑ 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.007..0.007 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.010 0.260 ↑ 1.0 1 5

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

62. 0.010 0.250 ↑ 1.0 1 5

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

63. 0.240 0.240 ↑ 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.042..0.048 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 : 10.182 ms
Execution time : 281.511 ms