explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yJq3

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

Aggregate (cost=9,356,163.12..9,356,163.13 rows=1 width=40) (actual rows= loops=)

2.          

CTE main

3. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=9,037,891.63..9,356,163.01 rows=2 width=125) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9,037,891.07..9,356,154.47 rows=2 width=57) (actual rows= loops=)

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

Nested Loop Left Join (cost=7,875,091.18..8,183,687.49 rows=1 width=65) (actual rows= loops=)

  • Join Filter: (cons_1.client_id = c.id)
6. 0.000 0.000 ↓ 0.0

Merge Anti Join (cost=4,074,425.76..4,282,025.51 rows=1 width=16) (actual rows= loops=)

  • Merge Cond: (c.id = a.client_id)
7. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=1,596,562.95..1,780,607.57 rows=1 width=16) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Merge Anti Join (cost=1,596,562.09..1,630,498.42 rows=134,520 width=26) (actual rows= loops=)

  • Merge Cond: (c.id = f.client_id)
9. 0.000 0.000 ↓ 0.0

Sort (cost=169,153.28..169,500.17 rows=138,758 width=26) (actual rows= loops=)

  • Sort Key: c.id
10. 0.000 0.000 ↓ 0.0

Hash Join (cost=72,584.40..157,301.81 rows=138,758 width=26) (actual rows= loops=)

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

Seq Scan on lo_clients_vivus c (cost=0.00..82,296.46 rows=922,263 width=18) (actual rows= loops=)

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

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

13. 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
14. 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))
15. 0.000 0.000 ↓ 0.0

Subquery Scan on f (cost=1,427,405.33..1,460,537.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))
16. 0.000 0.000 ↓ 0.0

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

17. 0.000 0.000 ↓ 0.0

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

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

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

19. 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)
20. 0.000 0.000 ↓ 0.0

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

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

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

22. 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)
23. 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
24. 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))))
25. 0.000 0.000 ↓ 0.0

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

26. 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
27. 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))
28. 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)
29. 0.000 0.000 ↓ 0.0

CTE Scan on consents cons_1 (cost=3,800,665.42..3,899,343.18 rows=103,058 width=57) (actual rows= loops=)

  • Filter: ((revision_date_from < '2018-12-01 00:00:00+02'::timestamp with time zone) AND (COALESCE(revision_date_to, now()) > '2018-11-01 00:00:00+02'::timestamp with time zone) AND (COALESCE(revision_date_to, (CURRENT_DATE)::timestamp with time zone) >= revision_date_from))
30.          

CTE consents

31. 0.000 0.000 ↓ 0.0

WindowAgg (cost=3,724,145.08..3,800,665.42 rows=2,782,558 width=94) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Sort (cost=3,724,145.08..3,731,101.47 rows=2,782,558 width=94) (actual rows= loops=)

  • Sort Key: cons.id, cons.rev
33. 0.000 0.000 ↓ 0.0

WindowAgg (cost=3,244,154.96..3,334,588.09 rows=2,782,558 width=94) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Sort (cost=3,244,154.96..3,251,111.35 rows=2,782,558 width=86) (actual rows= loops=)

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

Gather (cost=1,093,769.12..2,860,713.47 rows=2,782,558 width=86) (actual rows= loops=)

  • Workers Planned: 2
36. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,092,769.12..2,577,399.77 rows=1,159,399 width=86) (actual rows= loops=)

  • Hash Cond: (cons.id = act.id)
37. 0.000 0.000 ↓ 0.0

Merge Join (cost=982,281.42..2,460,970.16 rows=1,159,399 width=54) (actual rows= loops=)

  • Merge Cond: (rev.id = cons.rev)
38. 0.000 0.000 ↓ 0.0

Parallel Index Scan using p_audit_revisions_vivus on lo_audit_revisions_vivus rev (cost=0.56..2,004,963.84 rows=27,930,125 width=16) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Sort (cost=397,334.75..404,291.15 rows=2,782,558 width=46) (actual rows= loops=)

  • Sort Key: cons.rev
40. 0.000 0.000 ↓ 0.0

Seq Scan on lo_audit_consents_vivus cons (cost=0.00..99,490.02 rows=2,782,558 width=46) (actual rows= loops=)

  • Filter: ((consent_type)::text = ANY ('{ACCEPT_MARKETING_EMAILS,ACCEPT_MARKETING_SMS,ACCEPT_MARKETING_CALLS}'::text[]))
41. 0.000 0.000 ↓ 0.0

Hash (cost=75,782.31..75,782.31 rows=2,776,431 width=32) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Seq Scan on lo_consents_vivus act (cost=0.00..75,782.31 rows=2,776,431 width=32) (actual rows= loops=)

43. 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)
44. 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))
45. 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)
46. 0.000 0.000 ↓ 0.0

Index Scan using loans_client_id_product_id on loans (cost=0.56..3.03 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)))
47. 0.000 0.000 ↓ 0.0

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