explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iRNe

Settings
# exclusive inclusive rows x rows loops node
1. 98.055 50,716.008 ↑ 1.0 1 1

Aggregate (cost=1,782,139.38..1,782,139.39 rows=1 width=40) (actual time=50,716.008..50,716.008 rows=1 loops=1)

2.          

CTE main

3. 222.189 50,568.304 ↓ 2.5 107,661 1

Hash Right Join (cost=1,750,048.37..1,779,625.19 rows=43,725 width=133) (actual time=48,887.538..50,568.304 rows=107,661 loops=1)

  • Hash Cond: (cons_1.client_id = c.id)
4. 9,738.967 9,738.967 ↓ 16.3 441,918 1

CTE Scan on consents cons_1 (cost=905,770.10..933,481.78 rows=27,036 width=57) (actual time=8,280.267..9,738.967 rows=441,918 loops=1)

  • 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))
  • Rows Removed by Filter: 287643
5.          

CTE consents

6. 863.023 9,195.295 ↑ 1.0 729,561 1

WindowAgg (cost=885,696.04..905,770.10 rows=729,966 width=95) (actual time=8,280.258..9,195.295 rows=729,561 loops=1)

7. 397.907 8,332.272 ↑ 1.0 729,561 1

Sort (cost=885,696.04..887,520.95 rows=729,966 width=95) (actual time=8,280.240..8,332.272 rows=729,561 loops=1)

  • Sort Key: cons.id, cons.rev
  • Sort Method: quicksort Memory: 127171kB
8. 416.219 7,934.365 ↑ 1.0 729,561 1

WindowAgg (cost=790,882.69..814,606.58 rows=729,966 width=95) (actual time=7,439.155..7,934.365 rows=729,561 loops=1)

9. 557.725 7,518.146 ↑ 1.0 729,561 1

Sort (cost=790,882.69..792,707.60 rows=729,966 width=87) (actual time=7,439.144..7,518.146 rows=729,561 loops=1)

  • Sort Key: act.client_id, cons.id, (timezone('Europe/Sofia'::text, rev.revision_date)), cons.rev
  • Sort Method: quicksort Memory: 127171kB
10. 138.377 6,960.421 ↑ 1.0 729,561 1

Gather (cost=281,192.88..719,793.23 rows=729,966 width=87) (actual time=2,500.961..6,960.421 rows=729,561 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
11. 390.023 6,822.044 ↑ 1.2 364,780 2

Hash Join (cost=280,192.88..645,045.20 rows=429,392 width=87) (actual time=2,501.902..6,822.044 rows=364,780 loops=2)

  • Hash Cond: (cons.id = act.id)
12. 955.470 5,909.787 ↑ 1.2 364,883 2

Merge Join (cost=251,277.38..613,929.06 rows=429,392 width=55) (actual time=1,967.580..5,909.787 rows=364,883 loops=2)

  • Merge Cond: (rev.id = cons.rev)
13. 3,931.013 3,931.013 ↑ 1.2 7,357,464 2

Parallel Index Scan using p_audit_revisions_vivus on lo_audit_revisions_vivus rev (cost=0.43..464,549.40 rows=8,645,485 width=16) (actual time=0.222..3,931.013 rows=7,357,464 loops=2)

14. 327.487 1,023.304 ↑ 1.0 729,758 2

Sort (cost=119,862.15..121,687.07 rows=729,966 width=47) (actual time=953.582..1,023.304 rows=729,758 loops=2)

  • Sort Key: cons.rev
  • Sort Method: quicksort Memory: 81589kB
15. 695.817 695.817 ↑ 1.0 729,766 2

Seq Scan on lo_audit_consents_vivus cons (cost=0.00..48,772.70 rows=729,966 width=47) (actual time=0.023..695.817 rows=729,766 loops=2)

  • Filter: ((consent_type)::text = ANY ('{ACCEPT_MARKETING_EMAILS,ACCEPT_MARKETING_SMS,ACCEPT_MARKETING_CALLS}'::text[]))
  • Rows Removed by Filter: 780573
16. 244.394 522.234 ↓ 1.0 724,470 2

Hash (cost=19,900.22..19,900.22 rows=721,222 width=32) (actual time=522.234..522.234 rows=724,470 loops=2)

  • Buckets: 1048576 Batches: 1 Memory Usage: 53184kB
17. 277.840 277.840 ↓ 1.0 724,470 2

Seq Scan on lo_consents_vivus act (cost=0.00..19,900.22 rows=721,222 width=32) (actual time=0.018..277.840 rows=724,470 loops=2)

18. 5.946 40,607.148 ↑ 1.4 30,842 1

Hash (cost=843,731.70..843,731.70 rows=43,725 width=12) (actual time=40,607.148..40,607.148 rows=30,842 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1838kB
19. 8.577 40,601.202 ↑ 1.4 30,842 1

Merge Anti Join (cost=843,429.29..843,731.70 rows=43,725 width=12) (actual time=40,586.865..40,601.202 rows=30,842 loops=1)

  • Merge Cond: (c.id = loans.client_id)
20. 16.475 28,183.027 ↑ 1.5 33,010 1

Sort (cost=762,123.51..762,247.67 rows=49,664 width=12) (actual time=28,179.155..28,183.027 rows=33,010 loops=1)

  • Sort Key: c.id
  • Sort Method: quicksort Memory: 3084kB
21. 25.920 28,166.552 ↑ 1.5 33,010 1

Hash Join (cost=723,160.86..758,249.74 rows=49,664 width=12) (actual time=26,455.460..28,166.552 rows=33,010 loops=1)

  • Hash Cond: (lo_loans_vivus.client_id = dpd.client_id)
22. 13.226 27,392.668 ↓ 5.1 46,229 1

Hash Anti Join (cost=417,118.97..452,184.10 rows=9,049 width=20) (actual time=25,705.103..27,392.668 rows=46,229 loops=1)

  • Hash Cond: (c.id = a.client_id)
23. 19.882 15,548.465 ↓ 5.1 46,273 1

Nested Loop Anti Join (cost=299,145.10..334,095.97 rows=9,049 width=20) (actual time=13,874.111..15,548.465 rows=46,273 loops=1)

24. 23.943 14,377.279 ↓ 5.0 52,332 1

Hash Anti Join (cost=299,144.26..321,836.78 rows=10,482 width=30) (actual time=13,873.121..14,377.279 rows=52,332 loops=1)

  • Hash Cond: (c.id = f.client_id)
25. 77.527 712.237 ↓ 5.3 56,922 1

Hash Join (cost=10,726.13..33,282.89 rows=10,643 width=30) (actual time=232.004..712.237 rows=56,922 loops=1)

  • Hash Cond: (c.id = lo_loans_vivus.client_id)
26. 403.830 403.830 ↓ 2.9 152,035 1

Seq Scan on lo_clients_vivus c (cost=0.00..22,421.45 rows=51,545 width=22) (actual time=1.023..403.830 rows=152,035 loops=1)

  • 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))
  • Rows Removed by Filter: 11118
27. 8.959 230.880 ↓ 1.8 59,871 1

Hash (cost=10,304.37..10,304.37 rows=33,741 width=8) (actual time=230.880..230.880 rows=59,871 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2851kB
28. 71.132 221.921 ↓ 1.8 59,871 1

HashAggregate (cost=9,629.55..9,966.96 rows=33,741 width=8) (actual time=208.649..221.921 rows=59,871 loops=1)

  • Group Key: lo_loans_vivus.client_id
29. 150.789 150.789 ↓ 1.0 214,104 1

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

  • Filter: ((client_id IS NOT NULL) AND ((status_detail)::text <> 'VOIDED'::text))
  • Rows Removed by Filter: 16830
30. 3.919 13,641.099 ↓ 7.9 19,544 1

Hash (cost=288,387.18..288,387.18 rows=2,476 width=8) (actual time=13,641.099..13,641.099 rows=19,544 loops=1)

  • Buckets: 32768 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1020kB
31. 16.559 13,637.180 ↓ 7.9 19,544 1

Subquery Scan on f (cost=282,100.42..288,387.18 rows=2,476 width=8) (actual time=13,545.137..13,637.180 rows=19,544 loops=1)

  • 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))
  • Rows Removed by Filter: 99002
32. 65.908 13,620.621 ↓ 1.1 118,546 1

WindowAgg (cost=282,100.42..285,653.81 rows=109,335 width=42) (actual time=13,545.121..13,620.621 rows=118,546 loops=1)

33. 90.104 13,554.713 ↓ 1.1 118,546 1

Sort (cost=282,100.42..282,373.76 rows=109,335 width=42) (actual time=13,545.111..13,554.713 rows=118,546 loops=1)

  • Sort Key: t1.client_id, t1.id, (timezone('Europe/Sofia'::text, rev_1.revision_date)), t1.rev
  • Sort Method: quicksort Memory: 12334kB
34. 204.687 13,464.609 ↓ 1.1 118,546 1

Nested Loop (cost=0.43..272,949.96 rows=109,335 width=42) (actual time=0.961..13,464.609 rows=118,546 loops=1)

35. 51.610 51.610 ↓ 1.0 127,003 1

Seq Scan on lo_audit_client_restrictions_vivus t1 (cost=0.00..3,223.99 rows=126,986 width=26) (actual time=0.640..51.610 rows=127,003 loops=1)

  • Filter: (client_id IS NOT NULL)
  • Rows Removed by Filter: 10
36. 13,208.312 13,208.312 ↑ 1.0 1 127,003

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

  • Index Cond: (id = t1.rev)
  • Filter: (revision_date < '2018-09-02'::date)
  • Rows Removed by Filter: 0
37. 52.324 1,151.304 ↓ 0.0 0 52,332

Nested Loop (cost=0.83..1.16 rows=1 width=10) (actual time=0.022..0.022 rows=0 loops=52,332)

38. 680.316 680.316 ↑ 1.0 1 52,332

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

  • Index Cond: ((c.personal_id)::text = (personal_id)::text)
39. 418.664 418.664 ↓ 0.0 0 52,333

Index Scan using i_ibl_person on cb_internal_blacklist t3 (cost=0.41..0.62 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=52,333)

  • Index Cond: (person_id = t2.id)
  • Filter: active
  • Rows Removed by Filter: 0
40. 0.038 11,830.977 ↓ 50.0 50 1

Hash (cost=117,973.87..117,973.87 rows=1 width=8) (actual time=11,830.977..11,830.977 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
41. 1.041 11,830.939 ↓ 50.0 50 1

Nested Loop Left Join (cost=117,784.95..117,973.87 rows=1 width=8) (actual time=11,807.090..11,830.939 rows=50 loops=1)

  • 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))
  • Rows Removed by Filter: 2290
42. 1.339 11,828.663 ↓ 65.0 65 1

Subquery Scan on a (cost=117,784.95..117,971.70 rows=1 width=20) (actual time=11,806.821..11,828.663 rows=65 loops=1)

  • Filter: ((a.app_nr = 1) AND (a.is_hard_rejected_reasons = 1))
  • Rows Removed by Filter: 12814
43. 18.946 11,827.324 ↓ 4.3 12,879 1

WindowAgg (cost=117,784.95..117,926.88 rows=2,988 width=114) (actual time=11,806.770..11,827.324 rows=12,879 loops=1)

44. 12.761 11,808.378 ↓ 4.3 12,879 1

Sort (cost=117,784.95..117,792.42 rows=2,988 width=35) (actual time=11,806.752..11,808.378 rows=12,879 loops=1)

  • Sort Key: lap.client_id, lap.created_at DESC
  • Sort Method: quicksort Memory: 1499kB
45. 28.230 11,795.617 ↓ 4.3 12,879 1

Nested Loop (cost=348.07..117,612.46 rows=2,988 width=35) (actual time=203.155..11,795.617 rows=12,879 loops=1)

46. 10.734 31.111 ↓ 1.1 20,809 1

Bitmap Heap Scan on snapshot_20180901 (cost=347.51..18,963.53 rows=19,236 width=12) (actual time=20.433..31.111 rows=20,809 loops=1)

  • Recheck Cond: (company_code = 'VIVUSBG'::text)
  • Heap Blocks: exact=469
47. 20.377 20.377 ↓ 1.1 20,809 1

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

  • Index Cond: (company_code = 'VIVUSBG'::text)
48. 11,736.276 11,736.276 ↑ 1.0 1 20,809

Index Scan using applications_client_id_product_id_idx on applications lap (cost=0.56..5.12 rows=1 width=31) (actual time=0.487..0.564 rows=1 loops=20,809)

  • 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))
  • Rows Removed by Filter: 11
49. 1.235 1.235 ↑ 1.0 36 65

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

50. 7.809 747.964 ↑ 5.0 37,344 1

Hash (cost=303,727.11..303,727.11 rows=185,182 width=8) (actual time=747.964..747.964 rows=37,344 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 3507kB
51. 4.875 740.155 ↑ 5.0 37,344 1

Subquery Scan on dpd (cost=300,023.47..303,727.11 rows=185,182 width=8) (actual time=719.968..740.155 rows=37,344 loops=1)

52. 153.374 735.280 ↑ 5.0 37,344 1

HashAggregate (cost=300,023.47..301,875.29 rows=185,182 width=12) (actual time=719.967..735.280 rows=37,344 loops=1)

  • Group Key: loans_1.client_id
  • Filter: (max(loans_1.max_delayed_payment_days) < 31)
  • Rows Removed by Filter: 26736
53. 106.210 581.906 ↑ 1.1 228,802 1

Bitmap Heap Scan on loans loans_1 (cost=5,784.65..298,812.77 rows=242,140 width=12) (actual time=482.197..581.906 rows=228,802 loops=1)

  • Recheck Cond: (product_id = 18)
  • Filter: (client_id IS NOT NULL)
  • Heap Blocks: exact=37609
54. 475.696 475.696 ↑ 1.1 228,802 1

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

  • Index Cond: (product_id = 18)
55. 5.640 12,409.598 ↑ 1.3 14,698 1

Sort (cost=81,305.78..81,354.64 rows=19,543 width=8) (actual time=12,407.703..12,409.598 rows=14,698 loops=1)

  • Sort Key: loans.client_id
  • Sort Method: quicksort Memory: 1073kB
56. 31.103 12,403.958 ↑ 1.3 14,698 1

Bitmap Heap Scan on loans (cost=49,916.85..79,912.92 rows=19,543 width=8) (actual time=12,374.955..12,403.958 rows=14,698 loops=1)

  • 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)
  • Rows Removed by Filter: 20654
  • Heap Blocks: exact=12782
57. 5.562 12,372.855 ↓ 0.0 0 1

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

58. 18.796 18.796 ↑ 1.1 228,802 1

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

  • Index Cond: (product_id = 18)
59. 0.002 12,348.497 ↓ 0.0 0 1

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

60. 12,015.306 12,015.306 ↑ 1.1 1,090,303 1

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

  • Index Cond: (loan_closed_date IS NULL)
61. 333.189 333.189 ↑ 1.0 601,379 1

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

  • Index Cond: (loan_closed_date >= '2018-10-01'::date)
62. 50,617.953 50,617.953 ↓ 2.5 107,661 1

CTE Scan on main (cost=0.00..874.50 rows=43,725 width=49) (actual time=48,887.541..50,617.953 rows=107,661 loops=1)