explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yBQjw

Settings
# exclusive inclusive rows x rows loops node
1. 66,375.352 11,007,420.288 ↓ 75.2 1,044,828 1

Merge Anti Join (cost=166.98..256,273,943.63 rows=13,890 width=477) (actual time=169.847..11,007,420.288 rows=1,044,828 loops=1)

  • Merge Cond: (cp.user_id = vip_queries.user_id)
  • Buffers: shared hit=831656015 read=16526192 dirtied=68151 written=578
2. 3,230.199 10,801,960.180 ↓ 75.2 1,044,830 1

Nested Loop Anti Join (cost=166.84..255,226,453.05 rows=13,890 width=358) (actual time=168.448..10,801,960.180 rows=1,044,830 loops=1)

  • Buffers: shared hit=786878453 read=16526192 dirtied=68151 written=578
3. 5,315.191 10,732,046.931 ↓ 82.4 1,333,661 1

Nested Loop Semi Join (cost=166.42..255,199,973.49 rows=16,184 width=358) (actual time=168.437..10,732,046.931 rows=1,333,661 loops=1)

  • Buffers: shared hit=782160115 read=16401594 dirtied=67719 written=576
4. 5,936.815 10,704,116.084 ↓ 106.7 3,769,276 1

Merge Anti Join (cost=165.98..255,178,928.75 rows=35,312 width=366) (actual time=13.629..10,704,116.084 rows=3,769,276 loops=1)

  • Merge Cond: (cp.user_id = dnd_customers.dnd_user_id)
  • Buffers: shared hit=761848067 read=16401594 dirtied=67719 written=576
5. 9,366.027 10,697,125.484 ↓ 106.5 3,785,897 1

Nested Loop Left Join (cost=165.69..255,177,894.28 rows=35,536 width=366) (actual time=13.617..10,697,125.484 rows=3,785,897 loops=1)

  • Buffers: shared hit=761824455 read=16400663 dirtied=67719 written=576
6. 11,286.344 10,297,812.066 ↓ 106.5 3,785,897 1

Nested Loop Left Join (cost=149.40..254,598,036.41 rows=35,536 width=318) (actual time=11.620..10,297,812.066 rows=3,785,897 loops=1)

  • Buffers: shared hit=739803779 read=15773251 dirtied=67719 written=552
7. 9,097.597 2,692,016.340 ↓ 106.5 3,785,897 1

Nested Loop Left Join (cost=16.50..249,873,987.71 rows=35,536 width=126) (actual time=8.499..2,692,016.340 rows=3,785,897 loops=1)

  • Buffers: shared hit=318893805 read=5654368 dirtied=51788 written=161
8. 49,577.494 1,626,653.480 ↓ 106.5 3,785,897 1

Nested Loop Left Join (cost=1.86..249,352,755.12 rows=35,536 width=114) (actual time=8.123..1,626,653.480 rows=3,785,897 loops=1)

  • Filter: (COALESCE(ui.age, age_from_dob((u.dob)::text)) > 21)
  • Rows Removed by Filter: 284147
  • Buffers: shared hit=291288393 read=3676599 dirtied=36221 written=83
9. 11,225.466 1,279,962.774 ↓ 38.2 4,070,044 1

Nested Loop (cost=1.43..249,184,021.20 rows=106,608 width=85) (actual time=7.673..1,279,962.774 rows=4,070,044 loops=1)

  • Join Filter: (cp.user_id = u.user_id)
  • Buffers: shared hit=277515429 read=3106317 dirtied=24063 written=67
10. 8,893.767 694,861.104 ↓ 37.6 4,070,044 1

Nested Loop (cost=1.00..249,127,145.44 rows=108,170 width=51) (actual time=7.638..694,861.104 rows=4,070,044 loops=1)

  • Buffers: shared hit=262759018 read=1573595 dirtied=18739 written=27
11. 160,725.121 555,618.697 ↓ 36.9 4,073,395 1

Index Scan using idx_user_id_customer_profiles on customer_profiles cp (cost=0.56..248,919,628.88 rows=110,272 width=19) (actual time=4.391..555,618.697 rows=4,073,395 loops=1)

  • Filter: (((customer_type)::text = ANY ('{Amber,Green}'::text[])) AND (customer_profile_id = (SubPlan 9)))
  • Rows Removed by Filter: 24189006
  • Buffers: shared hit=247092750 read=927537 dirtied=2971 written=10
12.          

SubPlan (forIndex Scan)

13. 87,754.128 394,893.576 ↑ 1.0 1 21,938,532

Aggregate (cost=8.71..8.72 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=21,938,532)

  • Buffers: shared hit=221224446 read=487246 dirtied=1997 written=8
14. 307,139.448 307,139.448 ↑ 1.0 7 21,938,532

Index Scan using idx_user_id_customer_profiles on customer_profiles customer_profiles_2 (cost=0.56..8.69 rows=7 width=8) (actual time=0.003..0.014 rows=7 loops=21,938,532)

  • Index Cond: (user_id = cp.user_id)
  • Buffers: shared hit=221224446 read=487246 dirtied=1997 written=8
15. 130,348.640 130,348.640 ↑ 1.0 1 4,073,395

Index Scan using user_details_pkey on user_details ud (cost=0.43..1.88 rows=1 width=32) (actual time=0.032..0.032 rows=1 loops=4,073,395)

  • Index Cond: (user_id = cp.user_id)
  • Filter: ((phone_home)::text ~* '^[6789]'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=15666268 read=646058 dirtied=15768 written=17
16. 573,876.204 573,876.204 ↑ 1.0 1 4,070,044

Index Scan using users_pkey on users u (cost=0.43..0.51 rows=1 width=34) (actual time=0.141..0.141 rows=1 loops=4,070,044)

  • Index Cond: (user_id = ud.user_id)
  • Buffers: shared hit=14756411 read=1532722 dirtied=5324 written=40
17. 297,113.212 297,113.212 ↑ 1.0 1 4,070,044

Index Scan using user_inputs__index_on_user_id on user_inputs ui (cost=0.43..1.32 rows=1 width=51) (actual time=0.072..0.073 rows=1 loops=4,070,044)

  • Index Cond: (user_id = cp.user_id)
  • Buffers: shared hit=13772933 read=570282 dirtied=12158 written=16
18. 41,644.867 1,056,265.263 ↑ 1.0 1 3,785,897

Aggregate (cost=14.64..14.65 rows=1 width=12) (actual time=0.278..0.279 rows=1 loops=3,785,897)

  • Buffers: shared hit=27605412 read=1977769 dirtied=15567 written=78
19. 1,014,620.396 1,014,620.396 ↑ 3.5 4 3,785,897

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.010..0.268 rows=4 loops=3,785,897)

  • Index Cond: (user_id = cp.user_id)
  • Buffers: shared hit=27605412 read=1977769 dirtied=15567 written=78
20. 2,070,885.659 7,594,509.382 ↑ 1.0 1 3,785,897

Aggregate (cost=132.91..132.92 rows=1 width=192) (actual time=2.006..2.006 rows=1 loops=3,785,897)

  • Buffers: shared hit=420909974 read=10118883 dirtied=15931 written=391
21. 288,621.527 5,523,623.723 ↓ 1.1 43 3,785,897

Nested Loop Left Join (cost=2.62..122.96 rows=39 width=9) (actual time=0.086..1.459 rows=43 loops=3,785,897)

  • Buffers: shared hit=420909974 read=10118883 dirtied=15931 written=391
22. 166,579.423 5,073,101.980 ↓ 1.1 43 3,785,897

Hash Join (cost=2.34..100.61 rows=39 width=4) (actual time=0.083..1.340 rows=43 loops=3,785,897)

  • Hash Cond: (ll.lead_log_type_id = llt.id)
  • Buffers: shared hit=162160719 read=10118883 dirtied=15931 written=391
23. 168,815.927 4,906,522.512 ↑ 1.1 43 3,785,897

Nested Loop (cost=1.01..98.71 rows=47 width=8) (actual time=0.081..1.296 rows=43 loops=3,785,897)

  • Buffers: shared hit=162160715 read=10118883 dirtied=15931 written=391
24. 321,801.245 321,801.245 ↑ 1.2 4 3,785,897

Index Scan using idx_user_id_leads on leads l_1 (cost=0.44..6.48 rows=5 width=8) (actual time=0.024..0.085 rows=4 loops=3,785,897)

  • Index Cond: (user_id = cp.user_id)
  • Buffers: shared hit=23929607 read=573264 dirtied=15916 written=5
25. 4,415,905.340 4,415,905.340 ↑ 2.0 11 14,337,355

Index Scan using idx_lead_id_lead_logs on lead_logs ll (cost=0.57..18.22 rows=22 width=28) (actual time=0.119..0.308 rows=11 loops=14,337,355)

  • Index Cond: (lead_id = l_1.id)
  • Buffers: shared hit=138231108 read=9545619 dirtied=15 written=386
26. 0.016 0.045 ↓ 1.5 22 1

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

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

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

  • Buffers: shared hit=1
28. 161,900.216 161,900.216 ↑ 1.0 1 161,900,216

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,900,216)

  • Index Cond: (ll.product_status_id = id)
  • Buffers: shared hit=258749255
29. 22,715.382 389,947.391 ↑ 1.0 1 3,785,897

Aggregate (cost=16.29..16.30 rows=1 width=48) (actual time=0.102..0.103 rows=1 loops=3,785,897)

  • Buffers: shared hit=22020676 read=627412 written=24
30. 367,232.009 367,232.009 ↓ 0.0 0 3,785,897

Index Scan using lo_calls__index_on_user_id on lo_calls (cost=0.43..16.27 rows=1 width=11) (actual time=0.094..0.097 rows=0 loops=3,785,897)

  • Index Cond: (user_id = cp.user_id)
  • Filter: (log_date >= '2018-12-01'::date)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=22020673 read=627412 written=24
31. 1,053.785 1,053.785 ↓ 1.0 24,563 1

Index Scan using dnd_customers__index_on_dnd_user_id on dnd_customers (cost=0.29..882.60 rows=24,312 width=4) (actual time=0.009..1,053.785 rows=24,563 loops=1)

  • Filter: ((dnd_status = ANY ('{DNCT,DNCP}'::text[])) AND (is_active = 1))
  • Rows Removed by Filter: 408
  • Buffers: shared hit=23612 read=931
32. 22,615.656 22,615.656 ↓ 0.0 0 3,769,276

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

  • Index Cond: (user_id = u.user_id)
  • Filter: (created_at >= '2018-09-04'::date)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=20312048
33. 66,683.050 66,683.050 ↓ 0.0 0 1,333,661

Index Scan using applications__index_on_user_id on applications (cost=0.43..1.63 rows=1 width=4) (actual time=0.050..0.050 rows=0 loops=1,333,661)

  • Index Cond: (user_id = cp.user_id)
  • Filter: (applied_date >= (CURRENT_DATE - '4 mons'::interval))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4718337 read=124598 dirtied=431 written=2
34. 0.115 0.115 ↓ 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.115 rows=52 loops=1)

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

SubPlan (forMerge Anti Join)

36. 3,134.484 9,403.452 ↑ 1.0 1 1,044,828

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

  • Buffers: shared hit=6870582
37. 6,268.968 6,268.968 ↑ 1.0 2 1,044,828

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

  • Index Cond: (user_id = cp.user_id)
  • Filter: (type = 'LenderOffer'::leads_lead_type)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=6870582
38. 532.378 1,330.945 ↑ 1.0 1 266,189

Limit (cost=0.44..3.47 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=266,189)

  • Buffers: shared hit=1106567
39. 798.567 798.567 ↑ 2.0 1 266,189

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=266,189)

  • Index Cond: (user_id = cp.user_id)
  • Filter: (type = 'User'::leads_lead_type)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1106567
40. 4,179.312 9,403.452 ↑ 1.0 1 1,044,828

Aggregate (cost=16.27..16.28 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1,044,828)

  • Buffers: shared hit=5943619
41. 5,224.140 5,224.140 ↑ 4.0 3 1,044,828

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,044,828)

  • Index Cond: (user_id = cp.user_id)
  • Heap Fetches: 2959227
  • Buffers: shared hit=5943619
42. 3,134.484 7,313.796 ↑ 1.0 1 1,044,828

Aggregate (cost=16.29..16.30 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1,044,828)

  • Buffers: shared hit=5943619
43. 4,179.312 4,179.312 ↑ 6.0 1 1,044,828

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,044,828)

  • Index Cond: (user_id = cp.user_id)
  • Filter: (dispo <> 'NC'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=5943619
44. 5,224.140 13,582.764 ↑ 1.0 1 1,044,828

Aggregate (cost=8.71..8.72 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1,044,828)

  • Buffers: shared hit=7417410
45. 8,358.624 8,358.624 ↑ 2.3 3 1,044,828

Index Scan using idx_user_id_customer_profiles on customer_profiles (cost=0.56..8.69 rows=7 width=8) (actual time=0.005..0.008 rows=3 loops=1,044,828)

  • Index Cond: (user_id = cp.user_id)
  • Buffers: shared hit=7417410
46. 1,505.892 4,015.712 ↑ 1.0 1 501,964

Aggregate (cost=8.71..8.72 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=501,964)

  • Buffers: shared hit=2709741
47. 2,509.820 2,509.820 ↑ 3.5 2 501,964

Index Scan using idx_user_id_customer_profiles on customer_profiles customer_profiles_1 (cost=0.56..8.69 rows=7 width=8) (actual time=0.004..0.005 rows=2 loops=501,964)

  • Index Cond: (user_id = cp.user_id)
  • Buffers: shared hit=2709741
48. 3,134.484 10,448.280 ↑ 1.0 1 1,044,828

Aggregate (cost=6.50..6.51 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1,044,828)

  • Buffers: shared hit=6870582
49. 7,313.796 7,313.796 ↑ 1.0 2 1,044,828

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.007 rows=2 loops=1,044,828)

  • Index Cond: (user_id = cp.user_id)
  • Filter: (type = 'LenderOffer'::leads_lead_type)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=6870582
50. 11,493.108 83,586.240 ↑ 1.0 1 1,044,828

Aggregate (cost=8.10..8.11 rows=1 width=32) (actual time=0.080..0.080 rows=1 loops=1,044,828)

  • Buffers: shared hit=7915418
51. 34,479.324 72,093.132 ↓ 3.0 3 1,044,828

Hash Right Join (cost=6.51..8.10 rows=1 width=8) (actual time=0.050..0.069 rows=3 loops=1,044,828)

  • Hash Cond: (pf.id = l_3.product_family_id)
  • Buffers: shared hit=7915410
52. 25,075.872 25,075.872 ↓ 1.2 49 1,044,828

Seq Scan on product_families pf (cost=0.00..1.42 rows=42 width=8) (actual time=0.003..0.024 rows=49 loops=1,044,828)

  • Buffers: shared hit=1044828
53. 4,179.312 12,537.936 ↓ 3.0 3 1,044,828

Hash (cost=6.50..6.50 rows=1 width=28) (actual time=0.012..0.012 rows=3 loops=1,044,828)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=6870582
54. 8,358.624 8,358.624 ↓ 3.0 3 1,044,828

Index Scan using idx_user_id_leads on leads l_3 (cost=0.44..6.50 rows=1 width=28) (actual time=0.005..0.008 rows=3 loops=1,044,828)

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