explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0MfD : dados novos

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 276,683.935 ↑ 1.0 20 1

Limit (cost=22,842,970.57..22,842,970.62 rows=20 width=62) (actual time=276,683.924..276,683.935 rows=20 loops=1)

2.          

CTE elegible_application_ids

3. 9,854.822 276,636.853 ↓ 130.0 130 1

Nested Loop (cost=1.27..22,842,955.10 rows=1 width=16) (actual time=5,167.755..276,636.853 rows=130 loops=1)

  • Join Filter: (loan_auto_refi_workflow_pipelines.id = loan_auto_refi_workflow_tasks.pipeline_id)
  • Rows Removed by Join Filter: 39210118
4. 123.651 96,821.419 ↓ 212.0 212 1

Nested Loop Left Join (cost=1.27..22,703,952.69 rows=1 width=32) (actual time=498.438..96,821.419 rows=212 loops=1)

  • Join Filter: (loan_application_tags.auto_refinancing_lead_id = loan_auto_refi_bkf_applications.lead_id)
  • Rows Removed by Join Filter: 512331
  • Filter: ((loan_application_tags.type <> 'surprise'::text) OR (loan_application_tags.auto_refinancing_lead_id IS NULL))
  • Rows Removed by Filter: 25
5. 52.895 96,589.208 ↑ 1.1 236 1

Nested Loop (cost=1.27..22,693,730.01 rows=269 width=48) (actual time=494.283..96,589.208 rows=236 loops=1)

  • Join Filter: (underwriting_auto_refi_fidc_applications.bkf_application_id = loan_auto_refi_bkf_applications.id)
6. 122.364 2,115.259 ↓ 14.2 94,991 1

Nested Loop (cost=0.85..244,872.40 rows=6,687 width=64) (actual time=0.045..2,115.259 rows=94,991 loops=1)

7. 335.995 335.995 ↑ 1.0 138,075 1

Index Scan using loan_auto_refi_workflow_pipelines_pkey on loan_auto_refi_workflow_pipelines (cost=0.42..13,870.26 rows=138,396 width=32) (actual time=0.010..335.995 rows=138,075 loops=1)

  • Filter: (partner_id = '128f2c6a-85b4-11e6-84bf-0f62200dde38'::uuid)
8. 1,656.900 1,656.900 ↑ 1.0 1 138,075

Index Scan using underwriting_auto_refi_fidc_applications_bkf_application_id_ind on underwriting_auto_refi_fidc_applications (cost=0.43..1.66 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=138,075)

  • Index Cond: (bkf_application_id = loan_auto_refi_workflow_pipelines.bkf_application_id)
  • Filter: ((updated_at >= '2017-05-02 03:00:00'::timestamp without time zone) AND (credit_approval_situation <> 'prepending'::credit_approval_situation_enum) AND (status_flow = 'formalization'::underwriting_auto_refi_status_enum))
  • Rows Removed by Filter: 0
9. 47,697.242 94,421.054 ↓ 0.0 0 94,991

Index Scan using loan_auto_refi_bkf_applications_pkey on loan_auto_refi_bkf_applications (cost=0.43..3,357.08 rows=1 width=32) (actual time=0.994..0.994 rows=0 loops=94,991)

  • Index Cond: (id = loan_auto_refi_workflow_pipelines.bkf_application_id)
  • Filter: ((discard_reason IS NULL) AND (status_flow <> ALL ('{closed,no_closed}'::underwriting_auto_refi_status_enum[])) AND (NOT (SubPlan 1)))
  • Rows Removed by Filter: 1
10.          

SubPlan (for Index Scan)

11. 46,646.069 46,723.812 ↑ 1.1 94,311 2,029

Materialize (cost=0.00..6,445.75 rows=104,921 width=16) (actual time=0.005..23.028 rows=94,311 loops=2,029)

12. 77.743 77.743 ↑ 1.0 99,949 1

Seq Scan on closing_auto_refinancing_assignments (cost=0.00..5,408.15 rows=104,921 width=16) (actual time=0.013..77.743 rows=99,949 loops=1)

  • Filter: (unassigned_at IS NULL)
  • Rows Removed by Filter: 128960
13. 106.048 108.560 ↓ 1.0 2,171 236

Materialize (cost=0.00..135.16 rows=2,144 width=25) (actual time=0.002..0.460 rows=2,171 loops=236)

14. 2.512 2.512 ↓ 1.0 2,171 1

Seq Scan on loan_application_tags (cost=0.00..124.44 rows=2,144 width=25) (actual time=0.457..2.512 rows=2,171 loops=1)

15. 169,960.612 169,960.612 ↑ 1.8 184,954 212

Seq Scan on loan_auto_refi_workflow_tasks (cost=0.00..134,881.20 rows=329,697 width=16) (actual time=0.028..801.701 rows=184,954 loops=212)

  • Filter: (available AND (team = ANY ('{Docs,Contrato}'::text[])) AND (status = ANY ('{not_done,rework,doing}'::task_statuses[])))
  • Rows Removed by Filter: 3291374
16. 0.044 276,683.926 ↑ 1.2 20 1

Sort (cost=15.47..15.53 rows=25 width=62) (actual time=276,683.922..276,683.926 rows=20 loops=1)

  • Sort Key: (count(DISTINCT tasks.id) FILTER (WHERE (tasks.status = 'done'::task_statuses))), partner_applications.updated_at, partner_applications.created_at
  • Sort Method: top-N heapsort Memory: 27kB
17. 3.103 276,683.882 ↓ 1.7 42 1

GroupAggregate (cost=14.33..14.89 rows=25 width=62) (actual time=276,682.338..276,683.882 rows=42 loops=1)

  • Group Key: partner_applications.id, probabilities.closing_probability
18. 0.958 276,680.779 ↓ 40.3 1,008 1

Sort (cost=14.33..14.39 rows=25 width=62) (actual time=276,680.557..276,680.779 rows=1,008 loops=1)

  • Sort Key: partner_applications.id, probabilities.closing_probability
  • Sort Method: quicksort Memory: 166kB
19. 3.292 276,679.821 ↓ 40.3 1,008 1

Nested Loop Left Join (cost=1.72..13.75 rows=25 width=62) (actual time=276,638.996..276,679.821 rows=1,008 loops=1)

20. 0.518 276,666.869 ↓ 42.0 42 1

Nested Loop Left Join (cost=1.29..9.43 rows=1 width=58) (actual time=276,638.135..276,666.869 rows=42 loops=1)

21. 0.101 276,655.137 ↓ 42.0 42 1

Nested Loop Left Join (cost=0.87..8.95 rows=1 width=64) (actual time=276,637.428..276,655.137 rows=42 loops=1)

22. 1.268 276,646.090 ↓ 42.0 42 1

Nested Loop (cost=0.45..8.49 rows=1 width=48) (actual time=276,637.407..276,646.090 rows=42 loops=1)

23. 0.290 276,637.430 ↓ 42.0 42 1

HashAggregate (cost=0.02..0.03 rows=1 width=16) (actual time=276,637.389..276,637.430 rows=42 loops=1)

  • Group Key: elegible_application_ids.id
24. 276,637.140 276,637.140 ↓ 130.0 130 1

CTE Scan on elegible_application_ids (cost=0.00..0.02 rows=1 width=16) (actual time=5,167.762..276,637.140 rows=130 loops=1)

25. 7.392 7.392 ↑ 1.0 1 42

Index Scan using underwriting_auto_refi_fidc_applications_pkey on underwriting_auto_refi_fidc_applications partner_applications (cost=0.43..8.45 rows=1 width=48) (actual time=0.174..0.176 rows=1 loops=42)

  • Index Cond: (id = elegible_application_ids.id)
26. 8.946 8.946 ↑ 1.0 1 42

Index Scan using loan_auto_refi_workflow_pipelines_bkf_application_id_partner_id on loan_auto_refi_workflow_pipelines pipelines (cost=0.42..0.45 rows=1 width=32) (actual time=0.204..0.213 rows=1 loops=42)

  • Index Cond: ((bkf_application_id = partner_applications.bkf_application_id) AND (partner_id = '128f2c6a-85b4-11e6-84bf-0f62200dde38'::uuid))
27. 11.214 11.214 ↑ 1.0 1 42

Index Scan using unique_closing_auto_refinancing_data_probabilities_by_applicati on closing_auto_refinancing_data_probabilities probabilities (cost=0.42..0.47 rows=1 width=26) (actual time=0.102..0.267 rows=1 loops=42)

  • Index Cond: (partner_applications.bkf_application_id = application_id)
28. 9.660 9.660 ↑ 1.7 24 42

Index Scan using loan_auto_refi_workflow_tasks_pipeline_id_index on loan_auto_refi_workflow_tasks tasks (cost=0.43..3.92 rows=40 width=36) (actual time=0.197..0.230 rows=24 loops=42)

  • Index Cond: (pipelines.id = pipeline_id)
Planning time : 11.610 ms
Execution time : 276,685.492 ms