explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oKPSv

Settings
# exclusive inclusive rows x rows loops node
1. 0.964 96.722 ↓ 113.0 113 1

HashAggregate (cost=901.54..901.55 rows=1 width=564) (actual time=96.468..96.722 rows=113 loops=1)

  • Group Key: query_final.id_instrument, query_final.id_manager, query_final.is_negotiable, query_final.nm_fund, query_final.ds_business, query_final.cnpj, query_final.id_calculation_form, query_final.vl_min_apli_ini, query_final.vl_min_apli_adc, query_final.vl_min_deposit, query_final.vl_min_withdraw, query_final.pz_conv_deposit, query_final.pz_liq_deposit, query_final.pz_conv_withdraw, query_final.pz_liq_withdraw, query_final.pz_conv_withdraw_wait, query_final.pz_liq_withdraw_wait, query_final.vl_tax_entry, query_final.vl_tax_exit, query_final.id_category, query_final.id_contract, query_final.id_index, query_final.pz_conv_quote_erosion, query_final.pz_liq_quote_erosion, query_final.vl_tax_adm, query_final.vl_tax_performance, query_final.hh_end_work, query_final.hh_end_work_fund, query_final.is_quote_erosion, query_final.id_group, query_final.is_fund_qualified, query_final.nm_fund_nickname, query_final.id_dist_class, query_final.id_dist_sub_class, query_final.is_fund_risk, query_final.is_active, query_final.risk_level, query_final.is_continuous_days, query_final.is_visible_for_advisor, query_final.days_before_pl_adm, query_final.is_rule_fee_adm_active, query_final.adm_fee_type, query_final.is_visible_for_carteirista, query_final.is_visible_for_not_carteirista, query_final.vl_tax_adm_rebate, query_final.is_from_minicom, query_final.is_gross_up, query_final.id_target_group, query_final.id_distribution, query_final.is_isento_pf, query_final.is_isento_pj, query_final.has_document_246, query_final.has_document_293, query_final.has_document_1, query_final.is_restricted, query_final.id_contract_1, query_final.id_dist_class_1, query_final.id_dist_sub_class_1, query_final.nm_dist_sub_class, query_final.id_contract_2, query_final.id_dist_class_2, query_final.nm_dist_class, query_final.id_contract_3, query_final.id_withdraw_rule, query_final.hh_max_internal_application, query_final.email_warning, query_final.dt_reference
2.          

CTE documents_246

3. 0.459 3.512 ↓ 1.1 535 1

HashAggregate (cost=249.64..254.50 rows=486 width=8) (actual time=3.408..3.512 rows=535 loops=1)

  • Group Key: tb_dist_fund_document.id_contract, tb_dist_fund_document.id_instrument
4. 3.053 3.053 ↑ 1.0 1,126 1

Seq Scan on tb_dist_fund_document (cost=0.00..244.01 rows=1,126 width=8) (actual time=0.022..3.053 rows=1,126 loops=1)

  • Filter: (id_document_type = 246)
  • Rows Removed by Filter: 6,555
5.          

CTE documents_293

6. 0.058 1.239 ↑ 1.0 102 1

HashAggregate (cost=244.60..245.66 rows=106 width=8) (actual time=1.221..1.239 rows=102 loops=1)

  • Group Key: tb_dist_fund_document_1.id_contract, tb_dist_fund_document_1.id_instrument
7. 1.181 1.181 ↑ 1.0 117 1

Seq Scan on tb_dist_fund_document tb_dist_fund_document_1 (cost=0.00..244.01 rows=117 width=8) (actual time=0.007..1.181 rows=117 loops=1)

  • Filter: (id_document_type = 293)
  • Rows Removed by Filter: 7,564
8.          

CTE documents_1

9. 1.785 3.081 ↑ 1.3 403 1

HashAggregate (cost=275.78..281.22 rows=544 width=8) (actual time=3.005..3.081 rows=403 loops=1)

  • Group Key: tb_dist_fund_document_2.id_contract, tb_dist_fund_document_2.id_instrument
10. 1.296 1.296 ↑ 1.0 6,354 1

Seq Scan on tb_dist_fund_document tb_dist_fund_document_2 (cost=0.00..244.01 rows=6,354 width=8) (actual time=0.007..1.296 rows=6,354 loops=1)

  • Filter: (id_document_type = 1)
  • Rows Removed by Filter: 1,327
11.          

CTE query_final

12. 0.351 95.356 ↓ 113.0 113 1

Nested Loop Anti Join (cost=1.82..119.97 rows=1 width=615) (actual time=9.239..95.356 rows=113 loops=1)

13. 0.382 90.485 ↓ 113.0 113 1

Nested Loop Left Join (cost=1.53..112.00 rows=1 width=620) (actual time=8.971..90.485 rows=113 loops=1)

  • Filter: ((whitelist.id_instrument IS NULL) OR (whitelist.id_office = ANY ('{-1,354}'::bigint[])))
  • Rows Removed by Filter: 993
14. 0.414 88.363 ↓ 174.0 174 1

Nested Loop (cost=1.25..109.32 rows=1 width=620) (actual time=8.903..88.363 rows=174 loops=1)

15. 0.156 87.775 ↓ 174.0 174 1

Nested Loop Anti Join (cost=1.25..108.30 rows=1 width=572) (actual time=8.864..87.775 rows=174 loops=1)

16. 9.875 87.271 ↓ 174.0 174 1

Nested Loop Left Join (cost=1.11..107.93 rows=1 width=571) (actual time=8.833..87.271 rows=174 loops=1)

  • Join Filter: ((tb_dist_fund_document_1_1.id_contract = tb_dist_fund_selected.id_contract) AND (tb_dist_fund_document_1_1.id_instrument = tb_dist_fund_selected.id_instrument))
  • Rows Removed by Join Filter: 69,982
17. 2.624 63.128 ↓ 174.0 174 1

Nested Loop Left Join (cost=1.11..95.65 rows=1 width=567) (actual time=5.777..63.128 rows=174 loops=1)

  • Join Filter: ((tb_dist_fund_document_293.id_contract = tb_dist_fund_selected.id_contract) AND (tb_dist_fund_document_293.id_instrument = tb_dist_fund_selected.id_instrument))
  • Rows Removed by Join Filter: 17,719
18. 13.113 56.328 ↓ 174.0 174 1

Nested Loop Left Join (cost=1.11..93.25 rows=1 width=563) (actual time=4.483..56.328 rows=174 loops=1)

  • Join Filter: ((tb_dist_fund_document_246.id_contract = tb_dist_fund_selected.id_contract) AND (tb_dist_fund_document_246.id_instrument = tb_dist_fund_selected.id_instrument))
  • Rows Removed by Join Filter: 92,917
19. 0.825 24.771 ↓ 174.0 174 1

Nested Loop (cost=1.11..82.28 rows=1 width=559) (actual time=1.007..24.771 rows=174 loops=1)

  • Join Filter: ((tb_dist_fund_selected.id_dist_class = tb_dist_sub_class.id_dist_class) AND (tb_dist_fund_selected.id_dist_sub_class = tb_dist_sub_class.id_dist_sub_class))
  • Rows Removed by Join Filter: 5,344
20. 0.045 0.045 ↓ 31.0 31 1

Seq Scan on tb_dist_sub_class (cost=0.00..1.35 rows=1 width=44) (actual time=0.035..0.045 rows=31 loops=1)

  • Filter: (id_contract = 8)
21. 4.032 23.901 ↓ 22.2 178 31

Hash Join (cost=1.11..80.81 rows=8 width=515) (actual time=0.012..0.771 rows=178 loops=31)

  • Hash Cond: (tb_dist_fund_selected.id_dist_class = tb_dist_class.id_dist_class)
22. 19.840 19.840 ↓ 1.9 255 31

Seq Scan on tb_dist_fund_selected (cost=0.00..79.25 rows=137 width=475) (actual time=0.009..0.640 rows=255 loops=31)

  • Filter: ((cnpj IS NOT NULL) AND is_active AND is_negotiable AND (is_visible_for_not_carteirista OR (NOT is_visible_for_carteirista)) AND (NOT is_visible_for_advisor) AND (id_contract = 8))
  • Rows Removed by Filter: 648
23. 0.006 0.029 ↓ 9.0 9 1

Hash (cost=1.10..1.10 rows=1 width=40) (actual time=0.029..0.029 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.023 0.023 ↓ 9.0 9 1

Seq Scan on tb_dist_class (cost=0.00..1.10 rows=1 width=40) (actual time=0.021..0.023 rows=9 loops=1)

  • Filter: (id_contract = 8)
25. 18.444 18.444 ↓ 267.5 535 174

CTE Scan on documents_246 tb_dist_fund_document_246 (cost=0.00..10.93 rows=2 width=8) (actual time=0.020..0.106 rows=535 loops=174)

  • Filter: (id_contract = 8)
26. 4.176 4.176 ↓ 102.0 102 174

CTE Scan on documents_293 tb_dist_fund_document_293 (cost=0.00..2.38 rows=1 width=8) (actual time=0.007..0.024 rows=102 loops=174)

  • Filter: (id_contract = 8)
27. 14.268 14.268 ↓ 134.3 403 174

CTE Scan on documents_1 tb_dist_fund_document_1_1 (cost=0.00..12.24 rows=3 width=8) (actual time=0.017..0.082 rows=403 loops=174)

  • Filter: (id_contract = 8)
28. 0.348 0.348 ↓ 0.0 0 174

Index Scan using tb_dist_fund_selected_restriction_customer_pk on tb_dist_fund_selected_restriction_customer (cost=0.14..0.25 rows=1 width=9) (actual time=0.002..0.002 rows=0 loops=174)

  • Index Cond: ((id_contract = tb_dist_fund_selected.id_contract) AND (id_contract = 8) AND (id_instrument = tb_dist_fund_selected.id_instrument) AND (id_customer = 93,211))
  • Filter: (is_restricted IS TRUE)
29. 0.174 0.174 ↑ 1.0 1 174

Seq Scan on tb_dist_parameters (cost=0.00..1.01 rows=1 width=48) (actual time=0.001..0.001 rows=1 loops=174)

  • Filter: (id_contract = 8)
30. 1.740 1.740 ↑ 1.5 6 174

Index Scan using tb_dist_fund_selected_offices_whitelist_inst_idx on tb_dist_fund_selected_offices_whitelist whitelist (cost=0.28..2.57 rows=9 width=24) (actual time=0.005..0.010 rows=6 loops=174)

  • Index Cond: (id_instrument = tb_dist_fund_selected.id_instrument)
  • Filter: (is_active AND (id_contract = 8) AND (id_contract = tb_dist_fund_selected.id_contract))
  • Rows Removed by Filter: 2
31. 1.243 1.243 ↓ 0.0 0 113

Index Scan using tb_dist_fund_selected_offices_blacklist_pk on tb_dist_fund_selected_offices_blacklist blacklist (cost=0.29..3.87 rows=1 width=16) (actual time=0.011..0.011 rows=0 loops=113)

  • Index Cond: ((id_contract = tb_dist_fund_selected.id_contract) AND (id_contract = 8) AND (id_instrument = tb_dist_fund_selected.id_instrument) AND (id_office = ANY ('{-1,354}'::bigint[])))
  • Filter: is_active
32.          

SubPlan (for Nested Loop Anti Join)

33. 0.113 3.277 ↑ 1.0 1 113

Limit (cost=0.42..0.50 rows=1 width=4) (actual time=0.029..0.029 rows=1 loops=113)

34. 3.164 3.164 ↑ 591.0 1 113

Index Only Scan Backward using tb_dist_fund_daily_pk on tb_dist_fund_daily daily (cost=0.42..45.74 rows=591 width=4) (actual time=0.028..0.028 rows=1 loops=113)

  • Index Cond: ((id_contract = tb_dist_fund_selected.id_contract) AND (id_instrument = tb_dist_fund_selected.id_instrument))
  • Heap Fetches: 111
35. 95.758 95.758 ↓ 113.0 113 1

CTE Scan on query_final (cost=0.00..0.02 rows=1 width=564) (actual time=9.246..95.758 rows=113 loops=1)

Planning time : 10.416 ms
Execution time : 97.754 ms