explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R4Rx : dados antigos

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 805.587 ↑ 20.0 1 1

Limit (cost=122,596.08..122,596.13 rows=20 width=62) (actual time=805.586..805.587 rows=1 loops=1)

2.          

CTE elegible_application_ids

3. 0.066 805.454 ↓ 5.0 5 1

Nested Loop (cost=21,024.81..122,580.60 rows=1 width=16) (actual time=802.898..805.454 rows=5 loops=1)

4. 0.354 804.468 ↓ 92.0 92 1

Nested Loop (cost=21,024.38..122,576.30 rows=1 width=32) (actual time=248.258..804.468 rows=92 loops=1)

  • Join Filter: (loan_auto_refi_bkf_applications.id = underwriting_auto_refi_fidc_applications.bkf_application_id)
5. 33.367 802.839 ↓ 255.0 255 1

Nested Loop (cost=21,023.95..122,574.49 rows=1 width=48) (actual time=172.349..802.839 rows=255 loops=1)

6. 65.111 552.438 ↓ 108,517.0 108,517 1

Hash Left Join (cost=21,023.53..122,573.87 rows=1 width=16) (actual time=144.275..552.438 rows=108,517 loops=1)

  • Hash Cond: (loan_auto_refi_bkf_applications.lead_id = loan_application_tags.auto_refinancing_lead_id)
  • Filter: ((loan_application_tags.type <> 'surprise'::text) OR (loan_application_tags.auto_refinancing_lead_id IS NULL))
  • Rows Removed by Filter: 43
7. 393.851 485.975 ↓ 1.4 108,559 1

Bitmap Heap Scan on loan_auto_refi_bkf_applications (cost=20,875.24..121,656.92 rows=76,759 width=32) (actual time=142.894..485.975 rows=108,559 loops=1)

  • Recheck Cond: (discard_reason IS NULL)
  • Rows Removed by Index Recheck: 473557
  • Filter: ((status_flow <> ALL ('{closed,no_closed}'::underwriting_auto_refi_status_enum[])) AND (NOT (hashed SubPlan 1)))
  • Rows Removed by Filter: 43434
  • Heap Blocks: exact=45206 lossy=26657
8. 44.985 44.985 ↓ 1.7 272,745 1

Bitmap Index Scan on loan_auto_refi_bkf_applications_discard_reason_index (cost=0.00..15,212.04 rows=157,282 width=0) (actual time=44.985..44.985 rows=272,745 loops=1)

  • Index Cond: (discard_reason IS NULL)
9.          

SubPlan (for Bitmap Heap Scan)

10. 47.139 47.139 ↑ 1.0 99,890 1

Seq Scan on closing_auto_refinancing_assignments (cost=0.00..5,382.93 rows=104,431 width=16) (actual time=0.007..47.139 rows=99,890 loops=1)

  • Filter: (unassigned_at IS NULL)
  • Rows Removed by Filter: 128839
11. 0.710 1.352 ↓ 1.0 2,123 1

Hash (cost=122.02..122.02 rows=2,102 width=25) (actual time=1.352..1.352 rows=2,123 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 119kB
12. 0.642 0.642 ↓ 1.0 2,123 1

Seq Scan on loan_application_tags (cost=0.00..122.02 rows=2,102 width=25) (actual time=0.006..0.642 rows=2,123 loops=1)

13. 217.034 217.034 ↓ 0.0 0 108,517

Index Scan using loan_auto_refi_workflow_pipelines_bkf_application_id_partner_id on loan_auto_refi_workflow_pipelines (cost=0.42..0.61 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=108,517)

  • Index Cond: ((bkf_application_id = loan_auto_refi_bkf_applications.id) AND (partner_id = '128f2c6a-85b4-11e6-84bf-0f62200dde38'::uuid))
14. 1.275 1.275 ↓ 0.0 0 255

Index Scan using underwriting_auto_refi_fidc_applications_bkf_application_id_ind on underwriting_auto_refi_fidc_applications (cost=0.43..1.79 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=255)

  • 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: 1
15. 0.920 0.920 ↓ 0.0 0 92

Index Scan using loan_auto_refi_workflow_tasks_pipeline_id_index on loan_auto_refi_workflow_tasks (cost=0.43..4.27 rows=4 width=16) (actual time=0.010..0.010 rows=0 loops=92)

  • Index Cond: (pipeline_id = loan_auto_refi_workflow_pipelines.id)
  • Filter: (available AND (team = ANY ('{Docs,Contrato}'::text[])) AND (status = ANY ('{not_done,rework,doing}'::task_statuses[])))
  • Rows Removed by Filter: 24
16. 0.007 805.585 ↑ 25.0 1 1

Sort (cost=15.47..15.54 rows=25 width=62) (actual time=805.585..805.585 rows=1 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: 25kB
17. 0.036 805.578 ↑ 25.0 1 1

GroupAggregate (cost=14.33..14.89 rows=25 width=62) (actual time=805.578..805.578 rows=1 loops=1)

  • Group Key: partner_applications.id, probabilities.closing_probability
18. 0.025 805.542 ↑ 1.0 24 1

Sort (cost=14.33..14.39 rows=25 width=62) (actual time=805.537..805.542 rows=24 loops=1)

  • Sort Key: partner_applications.id, probabilities.closing_probability
  • Sort Method: quicksort Memory: 28kB
19. 0.015 805.517 ↑ 1.0 24 1

Nested Loop Left Join (cost=1.72..13.75 rows=25 width=62) (actual time=805.496..805.517 rows=24 loops=1)

20. 0.006 805.493 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.29..9.43 rows=1 width=58) (actual time=805.489..805.493 rows=1 loops=1)

21. 0.002 805.482 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.87..8.95 rows=1 width=64) (actual time=805.480..805.482 rows=1 loops=1)

22. 0.002 805.476 ↑ 1.0 1 1

Nested Loop (cost=0.45..8.49 rows=1 width=48) (actual time=805.475..805.476 rows=1 loops=1)

23. 0.007 805.467 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=16) (actual time=805.467..805.467 rows=1 loops=1)

  • Group Key: elegible_application_ids.id
24. 805.460 805.460 ↓ 5.0 5 1

CTE Scan on elegible_application_ids (cost=0.00..0.02 rows=1 width=16) (actual time=802.901..805.460 rows=5 loops=1)

25. 0.007 0.007 ↑ 1.0 1 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.006..0.007 rows=1 loops=1)

  • Index Cond: (id = elegible_application_ids.id)
26. 0.004 0.004 ↑ 1.0 1 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.003..0.004 rows=1 loops=1)

  • Index Cond: ((bkf_application_id = partner_applications.bkf_application_id) AND (partner_id = '128f2c6a-85b4-11e6-84bf-0f62200dde38'::uuid))
27. 0.005 0.005 ↑ 1.0 1 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.005..0.005 rows=1 loops=1)

  • Index Cond: (partner_applications.bkf_application_id = application_id)
28. 0.009 0.009 ↑ 1.7 24 1

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.004..0.009 rows=24 loops=1)

  • Index Cond: (pipelines.id = pipeline_id)
Planning time : 1.891 ms
Execution time : 805.722 ms