explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M76b

Settings
# exclusive inclusive rows x rows loops node
1. 59,784.698 7,745,350.746 ↓ 76.5 1,045,174 1

Merge Anti Join (cost=228.95..237,265,360.87 rows=13,669 width=477) (actual time=147.425..7,745,350.746 rows=1,045,174 loops=1)

  • Merge Cond: (cp.user_id = vip_queries.user_id)
  • Buffers: shared hit=808613551 read=12754998 dirtied=142569 written=1715
2. 3,079.986 7,558,469.040 ↓ 76.5 1,045,176 1

Nested Loop Anti Join (cost=228.81..236,249,399.81 rows=13,669 width=358) (actual time=146.237..7,558,469.040 rows=1,045,176 loops=1)

  • Buffers: shared hit=765445950 read=12754997 dirtied=142568 written=1715
3. 7,646.451 7,522,041.654 ↓ 84.6 1,333,896 1

Nested Loop Semi Join (cost=228.39..236,223,351.16 rows=15,773 width=358) (actual time=146.226..7,522,041.654 rows=1,333,896 loops=1)

  • Buffers: shared hit=760679514 read=12676245 dirtied=142245 written=1712
4. 5,649.871 7,495,586.193 ↓ 109.3 3,761,802 1

Merge Anti Join (cost=227.95..236,202,858.88 rows=34,416 width=366) (actual time=11.651..7,495,586.193 rows=3,761,802 loops=1)

  • Merge Cond: (cp.user_id = dnd_customers.dnd_user_id)
  • Buffers: shared hit=740413018 read=12676245 dirtied=142245 written=1712
5. 10,359.405 7,489,702.672 ↓ 109.1 3,778,323 1

Nested Loop Left Join (cost=227.66..236,201,832.87 rows=34,618 width=366) (actual time=11.624..7,489,702.672 rows=3,778,323 loops=1)

  • Buffers: shared hit=740389114 read=12675771 dirtied=142242 written=1712
6. 10,976.852 7,267,757.179 ↓ 109.1 3,778,323 1

Nested Loop Left Join (cost=211.37..235,636,954.45 rows=34,618 width=318) (actual time=10.108..7,267,757.179 rows=3,778,323 loops=1)

  • Buffers: shared hit=718256805 read=12210685 dirtied=142242 written=1685
7. 8,664.907 1,559,069.243 ↓ 109.1 3,778,323 1

Nested Loop Left Join (cost=16.37..228,885,327.95 rows=34,618 width=126) (actual time=7.926..1,559,069.243 rows=3,778,323 loops=1)

  • Buffers: shared hit=296109729 read=4358918 dirtied=98452 written=512
8. 43,065.038 1,040,330.731 ↓ 109.1 3,778,323 1

Nested Loop Left Join (cost=1.74..228,377,560.34 rows=34,618 width=114) (actual time=7.430..1,040,330.731 rows=3,778,323 loops=1)

  • Filter: (COALESCE(ui.age, age_from_dob((u.dob)::text)) > 21)
  • Rows Removed by Filter: 283619
  • Buffers: shared hit=267880424 read=3082686 dirtied=89300 written=363
9. 10,558.728 859,159.665 ↓ 39.1 4,061,942 1

Nested Loop (cost=1.31..228,212,254.94 rows=103,855 width=85) (actual time=7.009..859,159.665 rows=4,061,942 loops=1)

  • Join Filter: (cp.user_id = u.user_id)
  • Buffers: shared hit=253950754 read=2693327 dirtied=76545 written=322
10. 10,075.735 556,141.113 ↓ 38.5 4,061,942 1

Nested Loop (cost=0.87..228,156,848.39 rows=105,378 width=51) (actual time=6.978..556,141.113 rows=4,061,942 loops=1)

  • Buffers: shared hit=239065245 read=1321840 dirtied=70855 written=176
11. 116,317.102 464,759.838 ↓ 37.8 4,065,277 1

Index Scan using idx_user_id_customer_profiles on customer_profiles cp (cost=0.44..227,954,209.22 rows=107,425 width=19) (actual time=3.863..464,759.838 rows=4,065,277 loops=1)

  • Filter: (((customer_type)::text = ANY ('{Amber,Green}'::text[])) AND (customer_profile_id = (SubPlan 9)))
  • Rows Removed by Filter: 23986992
  • Buffers: shared hit=223301171 read=800327 dirtied=8579 written=110
12.          

SubPlan (forIndex Scan)

13. 108,888.355 348,442.736 ↑ 1.0 1 21,777,671

Aggregate (cost=8.17..8.18 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=21,777,671)

  • Buffers: shared hit=197472760 read=429248 dirtied=5392 written=62
14. 239,554.381 239,554.381 ↑ 1.2 6 21,777,671

Index Scan using idx_user_id_customer_profiles on customer_profiles customer_profiles_2 (cost=0.44..8.15 rows=7 width=8) (actual time=0.002..0.011 rows=6 loops=21,777,671)

  • Index Cond: (user_id = cp.user_id)
  • Buffers: shared hit=197472760 read=429248 dirtied=5392 written=62
15. 81,305.540 81,305.540 ↑ 1.0 1 4,065,277

Index Scan using user_details_pkey on user_details ud (cost=0.43..1.89 rows=1 width=32) (actual time=0.020..0.020 rows=1 loops=4,065,277)

  • Index Cond: (user_id = cp.user_id)
  • Filter: ((phone_home)::text ~* '^[6789]'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=15764074 read=521513 dirtied=62276 written=66
16. 292,459.824 292,459.824 ↑ 1.0 1 4,061,942

Index Scan using users_pkey on users u (cost=0.43..0.51 rows=1 width=34) (actual time=0.072..0.072 rows=1 loops=4,061,942)

  • Index Cond: (user_id = ud.user_id)
  • Buffers: shared hit=14885509 read=1371487 dirtied=5690 written=146
17. 138,106.028 138,106.028 ↑ 1.0 1 4,061,942

Index Scan using user_inputs__index_on_user_id on user_inputs ui (cost=0.43..1.33 rows=1 width=51) (actual time=0.033..0.034 rows=1 loops=4,061,942)

  • Index Cond: (user_id = cp.user_id)
  • Buffers: shared hit=13929639 read=389359 dirtied=12755 written=41
18. 37,783.230 510,073.605 ↑ 1.0 1 3,778,323

Aggregate (cost=14.64..14.65 rows=1 width=12) (actual time=0.134..0.135 rows=1 loops=3,778,323)

  • Buffers: shared hit=28229305 read=1276232 dirtied=9152 written=149
19. 472,290.375 472,290.375 ↑ 3.5 4 3,778,323

Index Scan using idx_user_id_created_at_usl on utm_source_log (cost=0.56..14.53 rows=14 width=22) (actual time=0.007..0.125 rows=4 loops=3,778,323)

  • Index Cond: (user_id = cp.user_id)
  • Buffers: shared hit=28229305 read=1276232 dirtied=9152 written=149
20. 2,006,289.513 5,697,711.084 ↑ 1.0 1 3,778,323

Aggregate (cost=195.00..195.01 rows=1 width=192) (actual time=1.508..1.508 rows=1 loops=3,778,323)

  • Buffers: shared hit=422147076 read=7851767 dirtied=43790 written=1173
21. 273,304.943 3,691,421.571 ↓ 1.0 43 3,778,323

Nested Loop Left Join (cost=2.62..184.55 rows=41 width=9) (actual time=0.055..0.977 rows=43 loops=3,778,323)

  • Buffers: shared hit=422147076 read=7851767 dirtied=43790 written=1173
22. 158,689.525 3,256,914.426 ↓ 1.0 43 3,778,323

Hash Join (cost=2.34..161.05 rows=41 width=4) (actual time=0.052..0.862 rows=43 loops=3,778,323)

  • Hash Cond: (ll.lead_log_type_id = llt.id)
  • Buffers: shared hit=164118446 read=7851766 dirtied=43790 written=1173
23. 162,996.839 3,098,224.860 ↑ 1.0 43 3,778,323

Nested Loop (cost=1.01..159.19 rows=44 width=8) (actual time=0.051..0.820 rows=43 loops=3,778,323)

  • Buffers: shared hit=164118442 read=7851766 dirtied=43790 written=1173
24. 204,029.442 204,029.442 ↑ 1.2 4 3,778,323

Index Scan using idx_user_id_leads on leads l_1 (cost=0.44..6.48 rows=5 width=8) (actual time=0.015..0.054 rows=4 loops=3,778,323)

  • Index Cond: (user_id = cp.user_id)
  • Buffers: shared hit=24149888 read=539450 dirtied=43698 written=11
25. 2,731,198.579 2,731,198.579 ↑ 4.2 11 14,299,469

Index Scan using idx_lead_id_lead_logs on lead_logs ll (cost=0.57..30.08 rows=46 width=28) (actual time=0.075..0.191 rows=11 loops=14,299,469)

  • Index Cond: (lead_id = l_1.id)
  • Buffers: shared hit=139968554 read=7312316 dirtied=92 written=1162
26. 0.019 0.041 ↓ 1.5 22 1

Hash (cost=1.15..1.15 rows=15 width=4) (actual time=0.041..0.041 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
27. 0.022 0.022 ↓ 1.5 22 1

Seq Scan on lead_log_types llt (cost=0.00..1.15 rows=15 width=4) (actual time=0.011..0.022 rows=22 loops=1)

  • Buffers: shared hit=1
28. 161,202.202 161,202.202 ↑ 1.0 1 161,202,202

Index Scan using product_status_pkey on product_status ps (cost=0.28..0.57 rows=1 width=13) (actual time=0.001..0.001 rows=1 loops=161,202,202)

  • Index Cond: (ll.product_status_id = id)
  • Buffers: shared hit=258028630 read=1
29. 18,891.615 211,586.088 ↑ 1.0 1 3,778,323

Aggregate (cost=16.29..16.30 rows=1 width=48) (actual time=0.056..0.056 rows=1 loops=3,778,323)

  • Buffers: shared hit=22132309 read=465086 written=27
30. 192,694.473 192,694.473 ↓ 0.0 0 3,778,323

Index Scan using lo_calls__index_on_user_id on lo_calls (cost=0.43..16.27 rows=1 width=11) (actual time=0.050..0.051 rows=0 loops=3,778,323)

  • Index Cond: (user_id = cp.user_id)
  • Filter: (log_date >= '2018-12-01'::date)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=22132306 read=465086 written=27
31. 233.650 233.650 ↓ 1.0 24,393 1

Index Scan using dnd_customers__index_on_dnd_user_id on dnd_customers (cost=0.29..876.92 rows=24,209 width=4) (actual time=0.021..233.650 rows=24,393 loops=1)

  • Filter: ((dnd_status = ANY ('{DNCT,DNCP}'::text[])) AND (is_active = 1))
  • Rows Removed by Filter: 399
  • Buffers: shared hit=23904 read=474 dirtied=3
32. 18,809.010 18,809.010 ↓ 0.0 0 3,761,802

Index Scan using idx_user_id_leads on leads l (cost=0.44..0.60 rows=1 width=28) (actual time=0.005..0.005 rows=0 loops=3,761,802)

  • Index Cond: (user_id = u.user_id)
  • Filter: (created_at >= '2018-09-03'::date)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=20266496
33. 33,347.400 33,347.400 ↓ 0.0 0 1,333,896

Index Scan using applications__index_on_user_id on applications (cost=0.43..1.65 rows=1 width=4) (actual time=0.025..0.025 rows=0 loops=1,333,896)

  • Index Cond: (user_id = cp.user_id)
  • Filter: (applied_date >= (CURRENT_DATE - '4 mons'::interval))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4766435 read=78752 dirtied=322 written=3
34. 0.476 0.476 ↓ 1.0 52 1

Index Scan using vip_queries__index_on_user_id on vip_queries (cost=0.14..4.76 rows=51 width=8) (actual time=0.010..0.476 rows=52 loops=1)

  • Filter: ((is_vip = 1) OR (is_jinx = 1))
  • Buffers: shared hit=2 read=1 dirtied=1
35.          

SubPlan (forMerge Anti Join)

36. 3,135.522 9,406.566 ↑ 1.0 1 1,045,174

Aggregate (cost=6.50..6.51 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1,045,174)

  • Buffers: shared hit=6861833
37. 6,271.044 6,271.044 ↑ 1.0 2 1,045,174

Index Scan using idx_user_id_leads on leads (cost=0.44..6.50 rows=2 width=8) (actual time=0.003..0.006 rows=2 loops=1,045,174)

  • Index Cond: (user_id = cp.user_id)
  • Filter: (type = 'LenderOffer'::leads_lead_type)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=6861833
38. 537.624 1,344.060 ↑ 1.0 1 268,812

Limit (cost=0.44..3.47 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=268,812)

  • Buffers: shared hit=1118291
39. 806.436 806.436 ↑ 2.0 1 268,812

Index Scan using idx_user_id_leads on leads leads_1 (cost=0.44..6.50 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=268,812)

  • Index Cond: (user_id = cp.user_id)
  • Filter: (type = 'User'::leads_lead_type)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1118291
40. 3,135.522 8,361.392 ↑ 1.0 1 1,045,174

Aggregate (cost=16.27..16.28 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=1,045,174)

  • Buffers: shared hit=5938366
41. 5,225.870 5,225.870 ↑ 4.0 3 1,045,174

Index Only Scan using lo_calls__index_on_user_id on lo_calls lo_calls_1 (cost=0.43..16.24 rows=12 width=0) (actual time=0.003..0.005 rows=3 loops=1,045,174)

  • Index Cond: (user_id = cp.user_id)
  • Heap Fetches: 2953690
  • Buffers: shared hit=5938366
42. 2,090.348 6,271.044 ↑ 1.0 1 1,045,174

Aggregate (cost=16.29..16.30 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1,045,174)

  • Buffers: shared hit=5938366
43. 4,180.696 4,180.696 ↑ 6.0 1 1,045,174

Index Scan using lo_calls__index_on_user_id on lo_calls lo_calls_2 (cost=0.43..16.27 rows=6 width=0) (actual time=0.003..0.004 rows=1 loops=1,045,174)

  • Index Cond: (user_id = cp.user_id)
  • Filter: (dispo <> 'NC'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=5938366
44. 5,225.870 11,496.914 ↑ 1.0 1 1,045,174

Aggregate (cost=8.17..8.18 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1,045,174)

  • Buffers: shared hit=6368150
45. 6,271.044 6,271.044 ↑ 2.3 3 1,045,174

Index Scan using idx_user_id_customer_profiles on customer_profiles (cost=0.44..8.15 rows=7 width=8) (actual time=0.004..0.006 rows=3 loops=1,045,174)

  • Index Cond: (user_id = cp.user_id)
  • Buffers: shared hit=6368150
46. 1,485.906 3,467.114 ↑ 1.0 1 495,302

Aggregate (cost=8.17..8.18 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=495,302)

  • Buffers: shared hit=2173723
47. 1,981.208 1,981.208 ↑ 3.5 2 495,302

Index Scan using idx_user_id_customer_profiles on customer_profiles customer_profiles_1 (cost=0.44..8.15 rows=7 width=8) (actual time=0.003..0.004 rows=2 loops=495,302)

  • Index Cond: (user_id = cp.user_id)
  • Buffers: shared hit=2173723
48. 3,135.522 9,406.566 ↑ 1.0 1 1,045,174

Aggregate (cost=6.50..6.51 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1,045,174)

  • Buffers: shared hit=6861833
49. 6,271.044 6,271.044 ↑ 1.0 2 1,045,174

Index Scan using idx_user_id_leads on leads l_2 (cost=0.44..6.50 rows=2 width=8) (actual time=0.004..0.006 rows=2 loops=1,045,174)

  • Index Cond: (user_id = cp.user_id)
  • Filter: (type = 'LenderOffer'::leads_lead_type)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=6861833
50. 10,451.740 77,342.876 ↑ 1.0 1 1,045,174

Aggregate (cost=8.10..8.11 rows=1 width=32) (actual time=0.074..0.074 rows=1 loops=1,045,174)

  • Buffers: shared hit=7907015
51. 31,355.220 66,891.136 ↓ 3.0 3 1,045,174

Hash Right Join (cost=6.51..8.10 rows=1 width=8) (actual time=0.046..0.064 rows=3 loops=1,045,174)

  • Hash Cond: (pf.id = l_3.product_family_id)
  • Buffers: shared hit=7907007
52. 24,039.002 24,039.002 ↓ 1.2 49 1,045,174

Seq Scan on product_families pf (cost=0.00..1.42 rows=42 width=8) (actual time=0.003..0.023 rows=49 loops=1,045,174)

  • Buffers: shared hit=1045174
53. 4,180.696 11,496.914 ↓ 3.0 3 1,045,174

Hash (cost=6.50..6.50 rows=1 width=28) (actual time=0.011..0.011 rows=3 loops=1,045,174)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=6861833
54. 7,316.218 7,316.218 ↓ 3.0 3 1,045,174

Index Scan using idx_user_id_leads on leads l_3 (cost=0.44..6.50 rows=1 width=28) (actual time=0.004..0.007 rows=3 loops=1,045,174)

  • Index Cond: (user_id = cp.user_id)
  • Filter: (created_at >= '2018-09-03'::date)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=6861833