explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c8Z

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

Aggregate (cost=7,981,269.00..7,981,269.01 rows=1 width=40) (actual rows= loops=)

2.          

CTE main

3. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=7,682,025.73..7,981,268.88 rows=2 width=125) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=7,682,025.17..7,981,260.42 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=6,519,225.28..6,808,793.44 rows=1 width=65) (actual rows= loops=)

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

Nested Loop Anti Join (cost=2,719,702.94..2,908,299.56 rows=1 width=16) (actual rows= loops=)

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

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

8. 0.000 0.000 ↓ 0.0

Merge Anti Join (cost=1,596,644.39..1,630,573.20 rows=134,570 width=26) (actual rows= loops=)

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

Sort (cost=169,587.29..169,934.19 rows=138,758 width=26) (actual rows= loops=)

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

Hash Join (cost=72,859.82..157,735.83 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,426.68 rows=933,074 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=71,098.83..71,098.83 rows=140,879 width=8) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

HashAggregate (cost=68,281.25..69,690.04 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,304.46 rows=1,190,719 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,053.62..1,460,178.79 rows=28,593 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,053.62..1,445,776.54 rows=576,090 width=42) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Sort (cost=1,427,053.62..1,428,493.84 rows=576,090 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,371,933.52 rows=576,090 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,569.18 rows=612,966 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=1,123,057.69..1,127,612.94 rows=1 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=1,123,057.69..1,126,199.24 rows=62,831 width=114) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Sort (cost=1,123,057.69..1,123,214.77 rows=62,831 width=44) (actual rows= loops=)

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

Nested Loop (cost=1,331.88..1,118,050.31 rows=62,831 width=44) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on snapshot_20181101 (cost=1,331.32..41,483.13 rows=73,986 width=12) (actual rows= loops=)

  • Recheck Cond: (company_code = 'VIVUSES'::text)
29. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on snapshot_20181101_idx (cost=0.00..1,312.82 rows=73,986 width=0) (actual rows= loops=)

  • Index Cond: (company_code = 'VIVUSES'::text)
30. 0.000 0.000 ↓ 0.0

Index Scan using applications_client_id_product_id_idx on applications lap (cost=0.56..14.54 rows=1 width=40) (actual rows= loops=)

  • Index Cond: ((client_id = snapshot_20181101.client_id) AND (product_id = 12))
  • Filter: ((resolution_detail <> 'NOT_LATEST'::text) AND ((created_at)::date < (date_trunc('month'::text, (snapshot_20181101.report_dt)::timestamp with time zone))::date) AND (((date_trunc('month'::text, (snapshot_20181101.report_dt)::timestamp with time zone))::date - (created_at)::date) <= 80))
31. 0.000 0.000 ↓ 0.0

CTE Scan on consents cons_1 (cost=3,799,522.34..3,898,175.65 rows=103,032 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))
32.          

CTE consents

33. 0.000 0.000 ↓ 0.0

WindowAgg (cost=3,723,020.94..3,799,522.34 rows=2,781,869 width=94) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Sort (cost=3,723,020.94..3,729,975.61 rows=2,781,869 width=94) (actual rows= loops=)

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

WindowAgg (cost=3,243,154.44..3,333,565.18 rows=2,781,869 width=94) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Sort (cost=3,243,154.44..3,250,109.11 rows=2,781,869 width=86) (actual rows= loops=)

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

Gather (cost=1,093,424.53..2,859,811.92 rows=2,781,869 width=86) (actual rows= loops=)

  • Workers Planned: 2
38. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,092,424.53..2,576,568.13 rows=1,159,112 width=86) (actual rows= loops=)

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

Merge Join (cost=981,985.17..2,460,188.32 rows=1,159,112 width=54) (actual rows= loops=)

  • Merge Cond: (rev.id = cons.rev)
40. 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,302.76 rows=27,920,855 width=16) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Sort (cost=397,231.38..404,186.05 rows=2,781,869 width=46) (actual rows= loops=)

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

Seq Scan on lo_audit_consents_vivus cons (cost=0.00..99,465.36 rows=2,781,869 width=46) (actual rows= loops=)

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

Hash (cost=75,749.16..75,749.16 rows=2,775,216 width=32) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Seq Scan on lo_consents_vivus act (cost=0.00..75,749.16 rows=2,775,216 width=32) (actual rows= loops=)

45. 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)
46. 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))
47. 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)
48. 0.000 0.000 ↓ 0.0

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

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