explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bKgx

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Aggregate (cost=5,584,930.79..5,584,930.80 rows=1 width=40) (actual rows= loops=)

2.          

CTE main

3. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=5,266,120.36..5,584,930.69 rows=2 width=109) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Hash Anti Join (cost=5,266,119.80..5,584,922.53 rows=2 width=41) (actual rows= loops=)

  • Hash Cond: (c.id = a.client_id)
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,764,701.81..3,083,504.52 rows=2 width=41) (actual rows= loops=)

  • Join Filter: (lo_loans_vivus.client_id = loans_1.client_id)
6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,601,901.93..1,911,037.54 rows=1 width=49) (actual rows= loops=)

  • Join Filter: (sub1.client_id = c.id)
7. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=1,500,029.58..1,773,573.15 rows=1 width=16) (actual rows= loops=)

  • Join Filter: (f.client_id = c.id)
8. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=72,585.25..312,639.14 rows=1 width=16) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Hash Join (cost=72,584.40..158,123.13 rows=138,697 width=26) (actual rows= loops=)

  • Hash Cond: (c.id = lo_loans_vivus.client_id)
10. 0.000 0.000 ↓ 0.0

Seq Scan on lo_clients_vivus c (cost=0.00..82,978.30 rows=975,398 width=18) (actual rows= loops=)

  • Filter: (entity_created < '2018-12-01 00:00:00'::timestamp without time zone)
11. 0.000 0.000 ↓ 0.0

Hash (cost=70,823.41..70,823.41 rows=140,879 width=8) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

HashAggregate (cost=68,005.83..69,414.62 rows=140,879 width=8) (actual rows= loops=)

  • Group Key: lo_loans_vivus.client_id
13. 0.000 0.000 ↓ 0.0

Seq Scan on lo_loans_vivus (cost=0.00..65,063.38 rows=1,176,982 width=8) (actual rows= loops=)

  • Filter: ((client_id IS NOT NULL) AND ((status_detail)::text <> 'VOIDED'::text) AND (closed_date < '2018-11-01'::date))
14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..1.10 rows=1 width=9) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Index Scan using i_per_personal_id on cb_person t2 (cost=0.43..0.48 rows=1 width=17) (actual rows= loops=)

  • Index Cond: ((c.personal_id)::text = (personal_id)::text)
16. 0.000 0.000 ↓ 0.0

Index Scan using i_ibl_person on cb_internal_blacklist t3 (cost=0.43..0.62 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (person_id = t2.id)
  • Filter: active
17. 0.000 0.000 ↓ 0.0

Subquery Scan on f (cost=1,427,444.33..1,460,576.52 rows=28,599 width=8) (actual rows= loops=)

  • Filter: ((f.invitations_blocked OR f.profile_blocked) AND (COALESCE(f.revision_date_to, (CURRENT_DATE)::timestamp with time zone) >= f.revision_date_from) AND (COALESCE(f.revision_date_to, (CURRENT_DATE)::timestamp with time zone) >= '2018-11-01 00:00:00+02'::timestamp with time zone))
18. 0.000 0.000 ↓ 0.0

WindowAgg (cost=1,427,444.33..1,446,171.22 rows=576,212 width=42) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Sort (cost=1,427,444.33..1,428,884.86 rows=576,212 width=42) (actual rows= loops=)

  • Sort Key: t1.client_id, t1.id, (timezone('Europe/Madrid'::text, rev.revision_date)), t1.rev
20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..1,372,311.67 rows=576,212 width=42) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on lo_audit_client_restrictions_vivus t1 (cost=0.00..15,572.48 rows=613,096 width=26) (actual rows= loops=)

  • Filter: (client_id IS NOT NULL)
22. 0.000 0.000 ↓ 0.0

Index Scan using p_audit_revisions_vivus on lo_audit_revisions_vivus rev (cost=0.56..2.21 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = t1.rev)
  • Filter: (revision_date < '2018-11-02'::date)
23. 0.000 0.000 ↓ 0.0

CTE Scan on consents sub1 (cost=101,872.35..121,447.97 rows=711,841 width=41) (actual rows= loops=)

24.          

CTE consents

25. 0.000 0.000 ↓ 0.0

Seq Scan on lo_consents_vivus cons (cost=0.00..101,872.35 rows=711,841 width=45) (actual rows= loops=)

  • Filter: (((consent_type)::text = ANY ('{ACCEPT_MARKETING_EMAILS,ACCEPT_MARKETING_SMS,ACCEPT_MARKETING_CALLS}'::text[])) AND (timezone('Europe/Madrid'::text, grant_date) < '2018-12-01 00:00:00'::timestamp without time zone))
26. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,162,799.89..1,165,774.38 rows=297,449 width=12) (actual rows= loops=)

  • Group Key: loans_1.client_id
  • Filter: (max(loans_1.max_delayed_payment_days) < 10)
27. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on loans loans_1 (cost=36,091.52..1,160,263.52 rows=507,273 width=12) (actual rows= loops=)

  • Recheck Cond: (product_id = 12)
  • Filter: ((client_id IS NOT NULL) AND (date_part('year'::text, age(date_trunc('month'::text, ((now())::date)::timestamp with time zone), date_trunc('month'::text, ((main_agreement_signed_date)::date)::timestamp with time zone))) < '13'::double precision))
28. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on loans_pkey (cost=0.00..35,964.71 rows=1,521,819 width=0) (actual rows= loops=)

  • Index Cond: (product_id = 12)
29. 0.000 0.000 ↓ 0.0

Hash (cost=2,501,417.91..2,501,417.91 rows=6 width=8) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Subquery Scan on a (cost=2,477,862.82..2,501,417.91 rows=6 width=8) (actual rows= loops=)

  • Filter: ((a.app_nr = 1) AND (((a.is_hard_rejected_reasons = 1) AND (a.rej_diff_day < 80)) OR ((a.is_hard_rejected_reasons = 2) AND (a.rej_diff_day < 30))))
31. 0.000 0.000 ↓ 0.0

WindowAgg (cost=2,477,862.82..2,493,264.22 rows=362,386 width=110) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Sort (cost=2,477,862.82..2,478,768.78 rows=362,386 width=40) (actual rows= loops=)

  • Sort Key: lap.client_id, lap.created_at DESC
33. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on applications lap (cost=90,086.53..2,444,401.60 rows=362,386 width=40) (actual rows= loops=)

  • Recheck Cond: (product_id = 12)
  • Filter: ((resolution_detail <> 'NOT_LATEST'::text) AND ((created_at)::date < '2018-11-01'::date) AND (('2018-11-01'::date - (created_at)::date) <= 80))
34. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on applications_pkey (cost=0.00..89,995.94 rows=3,265,983 width=0) (actual rows= loops=)

  • Index Cond: (product_id = 12)
35. 0.000 0.000 ↓ 0.0

Index Scan using loans_client_id_product_id on loans (cost=0.56..2.91 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((client_id = c.id) AND (product_id = 12))
  • Filter: ((main_agreement_signed_date < '2018-11-01 00:00:00'::timestamp without time zone) AND ((loan_closed_date IS NULL) OR (loan_closed_date >= '2018-12-01'::date)))
36. 0.000 0.000 ↓ 0.0

CTE Scan on main (cost=0.00..0.04 rows=2 width=41) (actual rows= loops=)