explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RnMG

Settings
# exclusive inclusive rows x rows loops node
1. 4.567 269.399 ↓ 28.3 3,738 1

Sort (cost=13,915.79..13,916.12 rows=132 width=122) (actual time=268.442..269.399 rows=3,738 loops=1)

  • Sort Key: auth_user.last_login DESC, ((SubPlan 6)) DESC
  • Sort Method: quicksort Memory: 950kB
2. 0.000 264.832 ↓ 28.3 3,738 1

Gather (cost=3,819.34..13,911.14 rows=132 width=122) (actual time=78.900..264.832 rows=3,738 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
3. 9.310 188.951 ↓ 24.0 1,869 2

Nested Loop Left Join (cost=2,819.34..6,650.98 rows=78 width=112) (actual time=72.186..188.951 rows=1,869 loops=2)

  • Filter: ((user_profile_devices.dislikes <= user_profile_devices.likes) OR (user_profile_devices.dislikes IS NULL))
  • Rows Removed by Filter: 210
4. 40.256 179.638 ↓ 42.4 2,079 2

Nested Loop (cost=2,818.92..6,306.06 rows=49 width=112) (actual time=72.113..179.638 rows=2,079 loops=2)

5. 18.295 139.374 ↓ 35.3 4,655 2

Hash Join (cost=2,818.51..5,592.48 rows=132 width=100) (actual time=71.902..139.374 rows=4,655 loops=2)

  • Hash Cond: (user_profile_userprofile.id = user_profile_preferences.profile_id)
6. 49.314 49.759 ↓ 27.3 12,704 2

Parallel Seq Scan on user_profile_userprofile (cost=1,124.01..3,893.75 rows=465 width=100) (actual time=0.504..49.759 rows=12,704 loops=2)

  • Filter: ((birthdate >= '1989-11-26'::date) AND (birthdate <= '2005-11-26'::date) AND (id <> 24097) AND (NOT (hashed SubPlan 7)) AND (NOT (hashed SubPlan 8)) AND (NOT (hashed SubPlan 9)) AND (NOT (hashed SubPlan 10)) AND (NOT (hashed SubPlan 11)))
  • Rows Removed by Filter: 8312
7.          

SubPlan (for Parallel Seq Scan)

8. 0.102 0.146 ↑ 1.0 24 2

Nested Loop (cost=0.71..210.77 rows=24 width=4) (actual time=0.029..0.146 rows=24 loops=2)

9. 0.041 0.041 ↑ 1.0 24 2

Index Scan using friendship_person1_id_idx on user_profile_friendship u1 (cost=0.42..83.39 rows=24 width=4) (actual time=0.011..0.041 rows=24 loops=2)

  • Index Cond: (person1_id = 24097)
10. 0.003 0.003 ↑ 1.0 1 48

Index Only Scan using user_profile_userprofile_pkey on user_profile_userprofile u0 (cost=0.29..5.31 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=48)

  • Index Cond: (id = u1.person2_id)
  • Heap Fetches: 8
11. 0.164 0.232 ↓ 1.6 57 2

Nested Loop (cost=0.71..299.55 rows=36 width=4) (actual time=0.018..0.232 rows=57 loops=2)

12. 0.066 0.066 ↓ 1.6 57 2

Index Scan using invitations_top_id_idx on user_profile_invitations u1_1 (cost=0.42..112.48 rows=36 width=4) (actual time=0.009..0.066 rows=57 loops=2)

  • Index Cond: (top_id = 24097)
13. 0.002 0.002 ↑ 1.0 1 114

Index Only Scan using user_profile_userprofile_pkey on user_profile_userprofile u0_1 (cost=0.29..5.20 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=114)

  • Index Cond: (id = u1_1.fromp_id)
  • Heap Fetches: 15
14. 0.001 0.009 ↓ 0.0 0 2

Nested Loop (cost=0.71..307.17 rows=38 width=4) (actual time=0.009..0.009 rows=0 loops=2)

15. 0.008 0.008 ↓ 0.0 0 2

Index Scan using invitations_fromp_id_idx on user_profile_invitations u1_2 (cost=0.42..107.49 rows=38 width=4) (actual time=0.008..0.008 rows=0 loops=2)

  • Index Cond: (fromp_id = 24097)
16. 0.000 0.000 ↓ 0.0 0

Index Only Scan using user_profile_userprofile_pkey on user_profile_userprofile u0_2 (cost=0.29..5.25 rows=1 width=4) (never executed)

  • Index Cond: (id = u1_2.top_id)
  • Heap Fetches: 0
17. 0.006 0.027 ↑ 14.5 2 2

Nested Loop (cost=0.71..157.85 rows=29 width=4) (actual time=0.023..0.027 rows=2 loops=2)

18. 0.016 0.016 ↑ 14.5 2 2

Index Only Scan using user_profile_userprofile_blac_from_userprofile_id_to_userpr_key on user_profile_userprofile_blacklist u1_3 (cost=0.42..8.93 rows=29 width=4) (actual time=0.015..0.016 rows=2 loops=2)

  • Index Cond: (from_userprofile_id = 24097)
  • Heap Fetches: 1
19. 0.005 0.005 ↑ 1.0 1 4

Index Only Scan using user_profile_userprofile_pkey on user_profile_userprofile u0_3 (cost=0.29..5.14 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=4)

  • Index Cond: (id = u1_3.to_userprofile_id)
  • Heap Fetches: 1
20. 0.014 0.031 ↑ 5.7 3 2

Nested Loop (cost=0.71..148.32 rows=17 width=4) (actual time=0.021..0.031 rows=3 loops=2)

21. 0.011 0.011 ↑ 5.7 3 2

Index Scan using user_profile_userprofile_blacklist_to_userprofile_id on user_profile_userprofile_blacklist u1_4 (cost=0.42..55.09 rows=17 width=4) (actual time=0.008..0.011 rows=3 loops=2)

  • Index Cond: (to_userprofile_id = 24097)
22. 0.006 0.006 ↑ 1.0 1 6

Index Only Scan using user_profile_userprofile_pkey on user_profile_userprofile u0_4 (cost=0.29..5.48 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=6)

  • Index Cond: (id = u1_4.from_userprofile_id)
  • Heap Fetches: 2
23. 25.416 71.320 ↓ 1.3 15,804 2

Hash (cost=1,545.76..1,545.76 rows=11,899 width=4) (actual time=71.319..71.320 rows=15,804 loops=2)

  • Buckets: 16384 Batches: 1 Memory Usage: 684kB
24. 45.904 45.904 ↓ 1.3 15,804 2

Seq Scan on user_profile_preferences (cost=0.00..1,545.76 rows=11,899 width=4) (actual time=0.007..45.904 rows=15,804 loops=2)

  • Filter: ((min_age <= 18) AND (max_age >= 18) AND (((country)::text = 'all'::text) OR ((country)::text = 'b''Argentina'''::text)) AND (((city)::text = 'all'::text) OR ((city)::text = 'b''Paternal'''::text)) AND (((gender)::text = 'maleAndFemale'::text) OR ((gender)::text = 'male'::text)))
  • Rows Removed by Filter: 22993
25. 0.008 0.008 ↓ 0.0 0 9,310

Index Scan using auth_user_pkey on auth_user (cost=0.41..5.41 rows=1 width=20) (actual time=0.008..0.008 rows=0 loops=9,310)

  • Index Cond: (id = user_profile_userprofile.user_id)
  • Filter: (last_login >= '2019-10-27 12:09:40.733063+00'::timestamp with time zone)
  • Rows Removed by Filter: 1
26. 0.003 0.003 ↑ 1.0 1 4,158

Index Scan using devices_person_id_idx on user_profile_devices (cost=0.42..7.03 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=4,158)

  • Index Cond: (user_profile_userprofile.id = person_id)
27.          

SubPlan (for Gather)

28. 3.738 26.166 ↑ 1.0 1 3,738

Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.005..0.007 rows=1 loops=3,738)

29. 22.428 22.428 ↑ 1.0 1 3,738

Index Scan using devices_person_id_idx on user_profile_devices user_profile_devices_1 (cost=0.42..8.44 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=3,738)

  • Index Cond: (person_id = user_profile_userprofile.id)
30. 3.738 11.214 ↑ 1.0 1 3,738

Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=3,738)

31. 7.476 7.476 ↑ 1.0 1 3,738

Index Scan using devices_person_id_idx on user_profile_devices user_profile_devices_2 (cost=0.42..8.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=3,738)

  • Index Cond: (person_id = user_profile_userprofile.id)
32. 7.476 22.428 ↑ 1.0 1 3,738

Limit (cost=0.29..8.31 rows=1 width=1) (actual time=0.005..0.006 rows=1 loops=3,738)

33. 14.952 14.952 ↑ 1.0 1 3,738

Index Scan using user_profile_preferences_83a0eb3f on user_profile_preferences user_profile_preferences_1 (cost=0.29..8.31 rows=1 width=1) (actual time=0.003..0.004 rows=1 loops=3,738)

  • Index Cond: (profile_id = user_profile_userprofile.id)
34. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on user_profile_invitations (cost=9.69..13.70 rows=1 width=0) (never executed)

  • Recheck Cond: ((fromp_id = 24097) AND (top_id = user_profile_userprofile.id))
35. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=9.69..9.69 rows=1 width=0) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on invitations_fromp_id_idx (cost=0.00..4.71 rows=38 width=0) (never executed)

  • Index Cond: (fromp_id = 24097)
37. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on invitations_top_id_idx (cost=0.00..4.73 rows=41 width=0) (never executed)

  • Index Cond: (top_id = user_profile_userprofile.id)
38. 0.009 0.009 ↓ 0.0 0 1

Index Scan using invitations_fromp_id_idx on user_profile_invitations user_profile_invitations_1 (cost=0.42..107.49 rows=38 width=4) (actual time=0.009..0.009 rows=0 loops=1)

  • Index Cond: (fromp_id = 24097)
39. 14.952 26.166 ↑ 1.0 1 3,738

Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=3,738)

40. 11.214 11.214 ↑ 1.0 1 3,738

Index Scan using devices_person_id_idx on user_profile_devices user_profile_devices_3 (cost=0.42..8.44 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=3,738)

  • Index Cond: (person_id = user_profile_userprofile.id)
Planning time : 6.902 ms
Execution time : 271.461 ms