explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZmIP

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 258,857.840 ↑ 1.0 20 1

Limit (cost=22,842,639.95..22,842,640.00 rows=20 width=62) (actual time=258,857.829..258,857.840 rows=20 loops=1)

  • rows=1 loops=28)
2.          

CTE elegible_application_ids

3. 7,973.943 258,776.401 ↓ 92.0 92 1

Nested Loop (cost=1.27..22,842,624.48 rows=1 width=16) (actual time=4,767.509..258,776.401 rows=92 loops=1)

  • Join Filter: (loan_auto_refi_workflow_pipelines.id = loan_auto_refi_workflow_tasks.pipeline_id)
  • Rows Removed by Join Filter: 31621057
4. 105.667 113,822.908 ↓ 171.0 171 1

Nested Loop Left Join (cost=1.27..22,703,700.44 rows=1 width=32) (actual time=459.240..113,822.908 rows=171 loops=1)

  • Join Filter: (loan_application_tags.auto_refinancing_lead_id = loan_auto_refi_bkf_applications.lead_id)
  • Rows Removed by Join Filter: 436086
  • Filter: ((loan_application_tags.type <> 'surprise'::text) OR (loan_application_tags.auto_refinancing_lead_id IS NULL))
  • Rows Removed by Filter: 32
5. 43.658 113,627.553 ↑ 1.3 202 1

Nested Loop (cost=1.27..22,693,477.76 rows=269 width=48) (actual time=456.897..113,627.553 rows=202 loops=1)

  • Join Filter: (underwriting_auto_refi_fidc_applications.bkf_application_id = loan_auto_refi_bkf_applications.id)
6. 198.947 4,425.448 ↓ 14.2 95,003 1

Nested Loop (cost=0.85..244,624.40 rows=6,687 width=64) (actual time=0.052..4,425.448 rows=95,003 loops=1)

7. 499.772 499.772 ↑ 1.0 138,027 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.019..499.772 rows=138,027 loops=1)

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

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.027..0.027 rows=1 loops=138,027)

  • 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. 60,257.199 109,158.447 ↓ 0.0 0 95,003

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.148..1.149 rows=0 loops=95,003)

  • 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. 48,853.972 48,901.248 ↑ 1.1 94,283 2,112

Materialize (cost=0.00..6,445.75 rows=104,921 width=16) (actual time=0.007..23.154 rows=94,283 loops=2,112)

12. 47.276 47.276 ↑ 1.0 99,944 1

Seq Scan on closing_auto_refinancing_assignments (cost=0.00..5,408.15 rows=104,921 width=16) (actual time=0.008..47.276 rows=99,944 loops=1)

  • Filter: (unassigned_at IS NULL)
  • Rows Removed by Filter: 128919
13. 88.969 89.688 ↓ 1.0 2,159 202

Materialize (cost=0.00..135.16 rows=2,144 width=25) (actual time=0.001..0.444 rows=2,159 loops=202)

14. 0.719 0.719 ↓ 1.0 2,159 1

Seq Scan on loan_application_tags (cost=0.00..124.44 rows=2,144 width=25) (actual time=0.005..0.719 rows=2,159 loops=1)

15. 136,979.550 136,979.550 ↑ 1.8 184,919 171

Seq Scan on loan_auto_refi_workflow_tasks (cost=0.00..134,805.15 rows=329,511 width=16) (actual time=0.021..801.050 rows=184,919 loops=171)

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

Sort (cost=15.47..15.53 rows=25 width=62) (actual time=258,857.827..258,857.830 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: quicksort Memory: 28kB
17. 0.338 258,857.805 ↓ 1.1 28 1

GroupAggregate (cost=14.33..14.89 rows=25 width=62) (actual time=258,857.372..258,857.805 rows=28 loops=1)

  • Group Key: partner_applications.id, probabilities.closing_probability
18. 0.595 258,857.467 ↓ 26.9 672 1

Sort (cost=14.33..14.39 rows=25 width=62) (actual time=258,857.325..258,857.467 rows=672 loops=1)

  • Sort Key: partner_applications.id, probabilities.closing_probability
  • Sort Method: quicksort Memory: 119kB
19. 0.336 258,856.872 ↓ 26.9 672 1

Nested Loop Left Join (cost=1.72..13.75 rows=25 width=62) (actual time=258,780.057..258,856.872 rows=672 loops=1)

20. 32.524 258,839.820 ↓ 28.0 28 1

Nested Loop Left Join (cost=1.29..9.43 rows=1 width=58) (actual time=258,779.700..258,839.820 rows=28 loops=1)

  • -> 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.716..1.158
21. 13.406 258,807.296 ↓ 28.0 28 1

Nested Loop Left Join (cost=0.87..8.95 rows=1 width=64) (actual time=258,778.629..258,807.296 rows=28 loops=1)

  • -> 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.463..0.476 rows=1 loops=28)
  • Index Cond: (partner_applications.bkf_application_id = application_id)
22. 17.054 258,793.890 ↓ 28.0 28 1

Nested Loop (cost=0.45..8.49 rows=1 width=48) (actual time=258,777.896..258,793.890 rows=28 loops=1)

  • -> 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.606..0.607 r ows=1 loops=28)
  • Index Cond: ((bkf_application_id = partner_applications.bkf_application_id) AND (partner_id = '128f2c6a-85b4-11e6-84bf-0f62200dde38'::uuid))
23. 0.227 258,776.836 ↓ 28.0 28 1

HashAggregate (cost=0.02..0.03 rows=1 width=16) (actual time=258,776.811..258,776.836 rows=28 loops=1)

  • Group Key: elegible_application_ids.id
  • Index Cond: (id = elegible_application_ids.id)
24. 258,776.609 258,776.609 ↓ 92.0 92 1

CTE Scan on elegible_application_ids (cost=0.00..0.02 rows=1 width=16) (actual time=4,767.514..258,776.609 rows=92 loops=1)

25. 16.716 16.716 ↑ 1.7 24 28

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.564..0.597 rows=24 loops=28)

  • Index Cond: (pipelines.id = pipeline_id)
Planning time : 2.074 ms
Execution time : 258,858.888 ms