explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 36hJ

Settings
# exclusive inclusive rows x rows loops node
1. 32.806 9,996.939 ↓ 970.0 970 1

Nested Loop Left Join (cost=88,133.23..103,456.26 rows=1 width=1,284) (actual time=2.466..9,996.939 rows=970 loops=1)

2.          

CTE base

3. 0.547 676.359 ↑ 1.0 1,000 1

Limit (cost=0.98..88,086.10 rows=1,000 width=37) (actual time=2.145..676.359 rows=1,000 loops=1)

4. 1.896 675.812 ↑ 43.6 1,000 1

Unique (cost=0.98..3,838,750.45 rows=43,580 width=37) (actual time=2.145..675.812 rows=1,000 loops=1)

5. 2.471 673.916 ↑ 31.6 1,378 1

Merge Anti Join (cost=0.98..3,838,641.50 rows=43,580 width=37) (actual time=2.144..673.916 rows=1,378 loops=1)

  • Merge Cond: (l.user_id = vas_subscriptions.user_id)
6. 11.771 670.091 ↑ 30.4 1,455 1

Nested Loop (cost=0.56..3,832,469.62 rows=44,256 width=37) (actual time=1.767..670.091 rows=1,455 loops=1)

  • Join Filter: (l.product_status_id = ps.id)
  • Rows Removed by Join Filter: 61,147
7. 647.297 647.297 ↑ 1,378.9 11,023 1

Index Scan using idx_user_id_leads on leads l (cost=0.56..2,464,949.21 rows=15,199,342 width=52) (actual time=0.034..647.297 rows=11,023 loops=1)

  • Filter: ((modified_ts >= '2020-01-01 00:00:00'::timestamp without time zone) AND (modified_ts < '2020-06-24 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 59,311
8. 9.071 11.023 ↑ 1.0 6 11,023

Materialize (cost=0.00..53.82 rows=6 width=13) (actual time=0.000..0.001 rows=6 loops=11,023)

9. 1.952 1.952 ↑ 1.0 6 1

Seq Scan on product_status ps (cost=0.00..53.79 rows=6 width=13) (actual time=0.972..1.952 rows=6 loops=1)

  • Filter: ((status)::text ~* 'CHR(.*?)(000|040|050)'::text)
  • Rows Removed by Filter: 1,618
10. 1.354 1.354 ↑ 228.7 666 1

Index Only Scan using vas_subscriptions__index_on_user_id on vas_subscriptions (cost=0.42..5,673.28 rows=152,324 width=4) (actual time=0.021..1.354 rows=666 loops=1)

  • Heap Fetches: 44
11. 2.676 6,953.253 ↓ 970.0 970 1

Nested Loop Left Join (cost=46.69..15,337.81 rows=1 width=710) (actual time=2.303..6,953.253 rows=970 loops=1)

12. 3.702 6,736.207 ↓ 970.0 970 1

Nested Loop (cost=46.26..15,335.39 rows=1 width=663) (actual time=2.290..6,736.207 rows=970 loops=1)

13. 2.772 6,564.695 ↓ 970.0 970 1

Nested Loop (cost=45.82..15,334.93 rows=1 width=634) (actual time=2.278..6,564.695 rows=970 loops=1)

14. 5.963 6,484.323 ↓ 970.0 970 1

Nested Loop (cost=45.39..15,334.47 rows=1 width=592) (actual time=2.266..6,484.323 rows=970 loops=1)

  • Join Filter: ((b.user_id = cp.user_id) AND ((SubPlan 4) = cp.customer_profile_id))
  • Rows Removed by Join Filter: 733
15. 3,662.073 6,400.484 ↓ 991.0 991 1

Nested Loop (cost=44.82..15,182.95 rows=1 width=572) (actual time=2.232..6,400.484 rows=991 loops=1)

16. 1,342.498 2,256.347 ↓ 996.0 996 1

Nested Loop (cost=13.10..15,149.20 rows=1 width=556) (actual time=2.194..2,256.347 rows=996 loops=1)

17. 677.849 677.849 ↑ 1.0 1,000 1

CTE Scan on base b (cost=0.00..20.00 rows=1,000 width=544) (actual time=2.147..677.849 rows=1,000 loops=1)

18. 0.000 236.000 ↑ 1.0 1 1,000

Index Scan using customer_profiles_pkey on customer_profiles c (cost=13.10..15.12 rows=1 width=20) (actual time=0.236..0.236 rows=1 loops=1,000)

  • Index Cond: (customer_profile_id = (SubPlan 5))
  • Filter: (b.user_id = user_id)
19.          

SubPlan (for Index Scan)

20. 9.000 1,335.000 ↑ 1.0 1 1,000

Aggregate (cost=12.52..12.53 rows=1 width=8) (actual time=1.335..1.335 rows=1 loops=1,000)

21. 1,326.000 1,326.000 ↓ 1.1 17 1,000

Index Scan using idx_user_id_customer_profiles on customer_profiles customer_profiles_2 (cost=0.57..12.48 rows=15 width=8) (actual time=0.109..1.326 rows=17 loops=1,000)

  • Index Cond: (user_id = b.user_id)
22. 0.000 482.064 ↑ 1.0 1 996

Index Scan using utm_source_log_pkey on utm_source_log usl (cost=31.72..33.74 rows=1 width=24) (actual time=0.484..0.484 rows=1 loops=996)

  • Index Cond: (id = (SubPlan 6))
  • Filter: (b.user_id = user_id)
23.          

SubPlan (for Index Scan)

24. 7.968 3,654.324 ↑ 1.0 1 996

Aggregate (cost=31.14..31.15 rows=1 width=8) (actual time=3.669..3.669 rows=1 loops=996)

25. 3,646.356 3,646.356 ↑ 2.2 13 996

Index Scan using idx_user_id_utm on utm_source_log (cost=0.56..31.07 rows=29 width=8) (actual time=0.318..3.661 rows=13 loops=996)

  • Index Cond: (user_id = b.user_id)
26. 14.865 14.865 ↑ 6.0 2 991

Index Scan using idx_user_id_customer_profiles on customer_profiles cp (cost=0.57..0.90 rows=12 width=28) (actual time=0.014..0.015 rows=2 loops=991)

  • Index Cond: (user_id = c.user_id)
  • Filter: ((customer_type)::text = ANY ('{Green,Red}'::text[]))
  • Rows Removed by Filter: 0
27.          

SubPlan (for Nested Loop)

28. 3.406 63.011 ↑ 1.0 1 1,703

Limit (cost=12.53..12.54 rows=1 width=16) (actual time=0.037..0.037 rows=1 loops=1,703)

29. 10.218 59.605 ↑ 1.0 1 1,703

Sort (cost=12.53..12.54 rows=1 width=16) (actual time=0.035..0.035 rows=1 loops=1,703)

  • Sort Key: customer_profiles_1.created_on DESC
  • Sort Method: quicksort Memory: 25kB
30. 49.387 49.387 ↑ 1.0 1 1,703

Index Scan using idx_user_id_customer_profiles on customer_profiles customer_profiles_1 (cost=0.57..12.52 rows=1 width=16) (actual time=0.011..0.029 rows=1 loops=1,703)

  • Index Cond: (user_id = b.user_id)
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 16
31. 77.600 77.600 ↑ 1.0 1 970

Index Scan using user_details_pkey on user_details ud (cost=0.44..0.46 rows=1 width=42) (actual time=0.080..0.080 rows=1 loops=970)

  • Index Cond: (user_id = cp.user_id)
32. 167.810 167.810 ↑ 1.0 1 970

Index Scan using users_pkey on users u (cost=0.44..0.46 rows=1 width=53) (actual time=0.173..0.173 rows=1 loops=970)

  • Index Cond: (user_id = cp.user_id)
33. 214.370 214.370 ↑ 1.0 1 970

Index Scan using user_inputs__index_on_user_id on user_inputs lua (cost=0.43..2.41 rows=1 width=55) (actual time=0.220..0.221 rows=1 loops=970)

  • Index Cond: (user_id = b.user_id)
34. 377.330 377.330 ↑ 1.0 1 970

Index Scan using user_income_user_id on user_income uim (cost=0.43..2.44 rows=1 width=15) (actual time=0.386..0.389 rows=1 loops=970)

  • Index Cond: (user_id = b.user_id)
35.          

SubPlan (for Nested Loop Left Join)

36. 1.940 40.740 ↑ 1.0 1 970

Limit (cost=12.56..12.56 rows=1 width=8) (actual time=0.042..0.042 rows=1 loops=970)

37. 9.700 38.800 ↑ 15.0 1 970

Sort (cost=12.56..12.60 rows=15 width=8) (actual time=0.040..0.040 rows=1 loops=970)

  • Sort Key: customer_profiles.created_on
  • Sort Method: top-N heapsort Memory: 25kB
38. 29.100 29.100 ↓ 1.2 18 970

Index Scan using idx_user_id_customer_profiles on customer_profiles (cost=0.57..12.48 rows=15 width=8) (actual time=0.008..0.030 rows=18 loops=970)

  • Index Cond: (user_id = b.user_id)
39. 0.970 2,592.810 ↑ 1.0 1 970

Limit (cost=17.08..17.08 rows=1 width=48) (actual time=2.673..2.673 rows=1 loops=970)

40. 5.820 2,591.840 ↑ 5.0 1 970

Sort (cost=17.08..17.09 rows=5 width=48) (actual time=2.672..2.672 rows=1 loops=970)

  • Sort Key: user_tokens.modified_ts DESC
  • Sort Method: quicksort Memory: 25kB
41. 2,586.020 2,586.020 ↑ 1.7 3 970

Index Scan using idx_user_id_user_tokens on user_tokens (cost=0.57..17.06 rows=5 width=48) (actual time=0.480..2.666 rows=3 loops=970)

  • Index Cond: (user_id = u.user_id)
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 13
Planning time : 2,659.883 ms
Execution time : 9,998.104 ms