explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u1XP

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

Aggregate (cost=1,782,091.24..1,782,091.25 rows=1 width=40) (actual rows= loops=)

2.          

CTE main

3. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1,750,002.27..1,779,577.05 rows=43,725 width=133) (actual rows= loops=)

  • Hash Cond: (cons_1.client_id = c.id)
4. 0.000 0.000 ↓ 0.0

CTE Scan on consents cons_1 (cost=905,727.00..933,436.67 rows=27,034 width=57) (actual rows= loops=)

  • Filter: ((revision_date_from < '2018-10-01 00:00:00'::timestamp without time zone) AND (COALESCE(revision_date_to, (CURRENT_DATE)::timestamp without time zone) >= revision_date_from) AND (COALESCE((revision_date_to)::timestamp with time zone, now()) > '2018-09-01 00:00:00+03'::timestamp with time zone))
5.          

CTE consents

6. 0.000 0.000 ↓ 0.0

WindowAgg (cost=885,654.40..905,727.00 rows=729,913 width=95) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Sort (cost=885,654.40..887,479.18 rows=729,913 width=95) (actual rows= loops=)

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

WindowAgg (cost=790,848.31..814,570.49 rows=729,913 width=95) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Sort (cost=790,848.31..792,673.10 rows=729,913 width=87) (actual rows= loops=)

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

Gather (cost=281,180.16..719,764.41 rows=729,913 width=87) (actual rows= loops=)

  • Workers Planned: 1
11. 0.000 0.000 ↓ 0.0

Hash Join (cost=280,180.16..645,021.73 rows=429,361 width=87) (actual rows= loops=)

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

Merge Join (cost=251,264.66..613,905.74 rows=429,361 width=55) (actual rows= loops=)

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

Parallel Index Scan using p_audit_revisions_vivus on lo_audit_revisions_vivus rev (cost=0.43..464,536.30 rows=8,645,236 width=16) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Sort (cost=119,853.12..121,677.90 rows=729,913 width=47) (actual rows= loops=)

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

Seq Scan on lo_audit_consents_vivus cons (cost=0.00..48,769.21 rows=729,913 width=47) (actual rows= loops=)

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

Hash (cost=19,900.22..19,900.22 rows=721,222 width=32) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on lo_consents_vivus act (cost=0.00..19,900.22 rows=721,222 width=32) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash (cost=843,728.70..843,728.70 rows=43,725 width=12) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Merge Anti Join (cost=843,426.29..843,728.70 rows=43,725 width=12) (actual rows= loops=)

  • Merge Cond: (c.id = loans.client_id)
20. 0.000 0.000 ↓ 0.0

Sort (cost=762,120.51..762,244.67 rows=49,664 width=12) (actual rows= loops=)

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

Hash Join (cost=723,157.86..758,246.74 rows=49,664 width=12) (actual rows= loops=)

  • Hash Cond: (lo_loans_vivus.client_id = dpd.client_id)
22. 0.000 0.000 ↓ 0.0

Hash Anti Join (cost=417,115.97..452,181.10 rows=9,049 width=20) (actual rows= loops=)

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

Nested Loop Anti Join (cost=299,142.10..334,092.97 rows=9,049 width=20) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash Anti Join (cost=299,141.26..321,833.78 rows=10,482 width=30) (actual rows= loops=)

  • Hash Cond: (c.id = f.client_id)
25. 0.000 0.000 ↓ 0.0

Hash Join (cost=10,726.13..33,282.89 rows=10,643 width=30) (actual rows= loops=)

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

Seq Scan on lo_clients_vivus c (cost=0.00..22,421.45 rows=51,545 width=22) (actual rows= loops=)

  • Filter: ((entity_created < '2018-10-01 00:00:00+03'::timestamp with time zone) AND (date_part('year'::text, age(date_trunc('month'::text, ((now())::date)::timestamp with time zone), date_trunc('month'::text, (date_of_birth)::timestamp with time zone))) < '65'::double precision))
27. 0.000 0.000 ↓ 0.0

Hash (cost=10,304.37..10,304.37 rows=33,741 width=8) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

HashAggregate (cost=9,629.55..9,966.96 rows=33,741 width=8) (actual rows= loops=)

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

Seq Scan on lo_loans_vivus (cost=0.00..9,094.38 rows=214,069 width=8) (actual rows= loops=)

  • Filter: ((client_id IS NOT NULL) AND ((status_detail)::text <> 'VOIDED'::text))
30. 0.000 0.000 ↓ 0.0

Hash (cost=288,384.18..288,384.18 rows=2,476 width=8) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Subquery Scan on f (cost=282,097.42..288,384.18 rows=2,476 width=8) (actual rows= loops=)

  • Filter: ((f.invitations_blocked OR f.profile_blocked) AND (COALESCE(f.revision_date_to, (CURRENT_DATE)::timestamp without time zone) >= f.revision_date_from) AND (COALESCE(f.revision_date_to, (CURRENT_DATE)::timestamp without time zone) >= '2018-09-01 00:00:00'::timestamp without time zone))
32. 0.000 0.000 ↓ 0.0

WindowAgg (cost=282,097.42..285,650.81 rows=109,335 width=42) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Sort (cost=282,097.42..282,370.76 rows=109,335 width=42) (actual rows= loops=)

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

Nested Loop (cost=0.43..272,946.96 rows=109,335 width=42) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Seq Scan on lo_audit_client_restrictions_vivus t1 (cost=0.00..3,223.99 rows=126,986 width=26) (actual rows= loops=)

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

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

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

Nested Loop (cost=0.83..1.16 rows=1 width=10) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Index Scan using i_per_personal_id on cb_person t2 (cost=0.42..0.53 rows=1 width=18) (actual rows= loops=)

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

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

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

Hash (cost=117,973.87..117,973.87 rows=1 width=8) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=117,784.95..117,973.87 rows=1 width=8) (actual rows= loops=)

  • Join Filter: (ca_rules.flag = (a.app_nr)::numeric)
  • Filter: ((a.rej_diff_day)::numeric < (CASE WHEN (ca_rules.parameter_code = 'days_since_last_rej_LAP'::text) THEN ca_rules.parameter_value ELSE NULL::numeric END))
42. 0.000 0.000 ↓ 0.0

Subquery Scan on a (cost=117,784.95..117,971.70 rows=1 width=20) (actual rows= loops=)

  • Filter: ((a.app_nr = 1) AND (a.is_hard_rejected_reasons = 1))
43. 0.000 0.000 ↓ 0.0

WindowAgg (cost=117,784.95..117,926.88 rows=2,988 width=114) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Sort (cost=117,784.95..117,792.42 rows=2,988 width=35) (actual rows= loops=)

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

Nested Loop (cost=348.07..117,612.46 rows=2,988 width=35) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on snapshot_20180901 (cost=347.51..18,963.53 rows=19,236 width=12) (actual rows= loops=)

  • Recheck Cond: (company_code = 'VIVUSBG'::text)
47. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on snapshot_20180901_idx (cost=0.00..342.70 rows=19,236 width=0) (actual rows= loops=)

  • Index Cond: (company_code = 'VIVUSBG'::text)
48. 0.000 0.000 ↓ 0.0

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

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

Seq Scan on ca_rules (cost=0.00..1.45 rows=36 width=37) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Hash (cost=303,727.11..303,727.11 rows=185,182 width=8) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Subquery Scan on dpd (cost=300,023.47..303,727.11 rows=185,182 width=8) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

HashAggregate (cost=300,023.47..301,875.29 rows=185,182 width=12) (actual rows= loops=)

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

Bitmap Heap Scan on loans loans_1 (cost=5,784.65..298,812.77 rows=242,140 width=12) (actual rows= loops=)

  • Recheck Cond: (product_id = 18)
  • Filter: (client_id IS NOT NULL)
54. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on loans_pkey (cost=0.00..5,724.11 rows=242,140 width=0) (actual rows= loops=)

  • Index Cond: (product_id = 18)
55. 0.000 0.000 ↓ 0.0

Sort (cost=81,305.78..81,354.64 rows=19,543 width=8) (actual rows= loops=)

  • Sort Key: loans.client_id
56. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on loans (cost=49,916.85..79,912.92 rows=19,543 width=8) (actual rows= loops=)

  • Recheck Cond: ((product_id = 18) AND ((loan_closed_date IS NULL) OR (loan_closed_date >= '2018-10-01'::date)))
  • Filter: (main_agreement_signed_date < '2018-09-01 00:00:00'::timestamp without time zone)
57. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=49,916.85..49,916.85 rows=20,737 width=0) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on loans_pkey (cost=0.00..5,724.11 rows=242,140 width=0) (actual rows= loops=)

  • Index Cond: (product_id = 18)
59. 0.000 0.000 ↓ 0.0

BitmapOr (cost=44,187.61..44,187.61 rows=1,846,028 width=0) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on loans_closed_date (cost=0.00..29,214.27 rows=1,220,761 width=0) (actual rows= loops=)

  • Index Cond: (loan_closed_date IS NULL)
61. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on loans_closed_date (cost=0.00..14,963.56 rows=625,267 width=0) (actual rows= loops=)

  • Index Cond: (loan_closed_date >= '2018-10-01'::date)
62. 0.000 0.000 ↓ 0.0

CTE Scan on main (cost=0.00..874.50 rows=43,725 width=49) (actual rows= loops=)