explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l8no

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 247,357.213 ↑ 1.0 20 1

Limit (cost=22,842,699.46..22,842,699.51 rows=20 width=42) (actual time=247,357.202..247,357.213 rows=20 loops=1)

2.          

CTE elegible_application_ids

3. 8,054.800 247,352.979 ↓ 102.0 102 1

Nested Loop (cost=1.27..22,842,686.58 rows=1 width=16) (actual time=5,314.743..247,352.979 rows=102 loops=1)

  • Join Filter: (loan_auto_refi_workflow_pipelines.id = loan_auto_refi_workflow_tasks.pipeline_id)
  • Rows Removed by Join Filter: 32360723
4. 107.113 99,239.204 ↓ 175.0 175 1

Nested Loop Left Join (cost=1.27..22,703,757.21 rows=1 width=32) (actual time=467.652..99,239.204 rows=175 loops=1)

  • Join Filter: (loan_application_tags.auto_refinancing_lead_id = loan_auto_refi_bkf_applications.lead_id)
  • Rows Removed by Join Filter: 444928
  • Filter: ((loan_application_tags.type <> 'surprise'::text) OR (loan_application_tags.auto_refinancing_lead_id IS NULL))
  • Rows Removed by Filter: 32
5. 18.923 99,040.421 ↑ 1.3 206 1

Nested Loop (cost=1.27..22,693,534.53 rows=269 width=48) (actual time=465.270..99,040.421 rows=206 loops=1)

  • Join Filter: (underwriting_auto_refi_fidc_applications.bkf_application_id = loan_auto_refi_bkf_applications.id)
6. 185.567 1,452.390 ↓ 14.2 95,004 1

Nested Loop (cost=0.85..244,680.40 rows=6,687 width=64) (actual time=0.035..1,452.390 rows=95,004 loops=1)

7. 300.613 300.613 ↑ 1.0 138,030 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.007..300.613 rows=138,030 loops=1)

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

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.007..0.007 rows=1 loops=138,030)

  • 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. 48,279.648 97,569.108 ↓ 0.0 0 95,004

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=1.026..1.027 rows=0 loops=95,004)

  • 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. 49,237.182 49,289.460 ↑ 1.1 94,269 2,101

Materialize (cost=0.00..6,445.75 rows=104,921 width=16) (actual time=0.006..23.460 rows=94,269 loops=2,101)

12. 52.278 52.278 ↑ 1.0 99,941 1

Seq Scan on closing_auto_refinancing_assignments (cost=0.00..5,408.15 rows=104,921 width=16) (actual time=0.007..52.278 rows=99,941 loops=1)

  • Filter: (unassigned_at IS NULL)
  • Rows Removed by Filter: 128929
13. 90.891 91.670 ↓ 1.0 2,160 206

Materialize (cost=0.00..135.16 rows=2,144 width=25) (actual time=0.001..0.445 rows=2,160 loops=206)

14. 0.779 0.779 ↓ 1.0 2,160 1

Seq Scan on loan_application_tags (cost=0.00..124.44 rows=2,144 width=25) (actual time=0.006..0.779 rows=2,160 loops=1)

15. 140,058.975 140,058.975 ↑ 1.8 184,919 175

Seq Scan on loan_auto_refi_workflow_tasks (cost=0.00..134,810.33 rows=329,523 width=16) (actual time=0.018..800.337 rows=184,919 loops=175)

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

Sort (cost=12.88..12.95 rows=25 width=42) (actual time=247,357.201..247,357.205 rows=20 loops=1)

  • Sort Key: partner_applications.updated_at, partner_applications.created_at
  • Sort Method: quicksort Memory: 27kB
17. 0.382 247,357.180 ↓ 1.2 31 1

HashAggregate (cost=12.05..12.30 rows=25 width=42) (actual time=247,357.171..247,357.180 rows=31 loops=1)

  • Group Key: partner_applications.id, probabilities.closing_probability
18. 0.336 247,356.798 ↓ 29.8 744 1

Nested Loop Left Join (cost=1.72..11.93 rows=25 width=42) (actual time=247,355.113..247,356.798 rows=744 loops=1)

19. 0.047 247,355.470 ↓ 31.0 31 1

Nested Loop Left Join (cost=1.29..9.43 rows=1 width=58) (actual time=247,354.728..247,355.470 rows=31 loops=1)

20. 0.043 247,355.175 ↓ 31.0 31 1

Nested Loop Left Join (cost=0.87..8.95 rows=1 width=64) (actual time=247,354.698..247,355.175 rows=31 loops=1)

21. 0.029 247,354.543 ↓ 31.0 31 1

Nested Loop (cost=0.45..8.49 rows=1 width=48) (actual time=247,354.269..247,354.543 rows=31 loops=1)

22. 0.195 247,353.398 ↓ 31.0 31 1

HashAggregate (cost=0.02..0.03 rows=1 width=16) (actual time=247,353.383..247,353.398 rows=31 loops=1)

  • Group Key: elegible_application_ids.id
23. 247,353.203 247,353.203 ↓ 102.0 102 1

CTE Scan on elegible_application_ids (cost=0.00..0.02 rows=1 width=16) (actual time=5,314.749..247,353.203 rows=102 loops=1)

24. 1.116 1.116 ↑ 1.0 1 31

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.035..0.036 rows=1 loops=31)

  • Index Cond: (id = elegible_application_ids.id)
25. 0.589 0.589 ↑ 1.0 1 31

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.018..0.019 rows=1 loops=31)

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

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.006..0.008 rows=1 loops=31)

  • Index Cond: (partner_applications.bkf_application_id = application_id)
27. 0.992 0.992 ↑ 1.7 24 31

Index Only Scan using loan_auto_refi_workflow_tasks_pipeline_id_index on loan_auto_refi_workflow_tasks tasks (cost=0.43..2.10 rows=40 width=16) (actual time=0.019..0.032 rows=24 loops=31)

  • Index Cond: (pipeline_id = pipelines.id)
  • Heap Fetches: 1210
Planning time : 2.021 ms
Execution time : 247,358.274 ms