explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BdXT : Optimization for: Optimization for: plan #gsj1; plan #bB3w

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=24,237.36..24,245.27 rows=226 width=122) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=24,237.36..24,237.92 rows=226 width=122) (actual rows= loops=)

  • Sort Key: auth_user.last_login DESC, ((SubPlan 3)) DESC, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 4)), ((alternatives: SubPlan 5 or hashed SubPlan 6)), user_profile_userprofile.country, user_profile_userprofile.city, user_profile_userprofile.profile_picture, user_profile_userprofile.profile_picture_thumb, auth_user.username, user_profile_userprofile.lastseen, user_profile_userprofile.name
3. 0.000 0.000 ↓ 0.0

Hash Join (cost=11,922.96..24,228.52 rows=226 width=122) (actual rows= loops=)

  • Hash Cond: (auth_user.id = user_profile_userprofile.user_id)
4. 0.000 0.000 ↓ 0.0

Seq Scan on auth_user (cost=0.00..1,567.70 rows=16,114 width=20) (actual rows= loops=)

  • Filter: (last_login >= '2019-10-27 10:54:37.77735+00'::timestamp with time zone)
5. 0.000 0.000 ↓ 0.0

Hash (cost=11,915.26..11,915.26 rows=616 width=100) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Seq Scan on user_profile_userprofile (cost=8,521.77..11,915.26 rows=616 width=100) (actual rows= loops=)

  • Filter: ((birthdate >= '1989-11-26'::date) AND (birthdate <= '2005-11-26'::date) AND ((lastseen)::text <> ''::text) AND (id <> 124047) 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)))
7.          

SubPlan (for Seq Scan)

8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=15.49..3,044.91 rows=359 width=4) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on user_profile_friendship u1 (cost=15.20..946.52 rows=359 width=4) (actual rows= loops=)

  • Recheck Cond: (person1_id = 124047)
10. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on friendship_person1_id_idx (cost=0.00..15.11 rows=359 width=0) (actual rows= loops=)

  • Index Cond: (person1_id = 124047)
11. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = u1.person2_id)
12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=15.90..3,432.34 rows=411 width=4) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on user_profile_invitations u1_1 (cost=15.61..1,093.96 rows=411 width=4) (actual rows= loops=)

  • Recheck Cond: (top_id = 124047)
14. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on invitations_top_id_idx (cost=0.00..15.50 rows=411 width=0) (actual rows= loops=)

  • Index Cond: (top_id = 124047)
15. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = u1_1.fromp_id)
16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.71..387.17 rows=38 width=4) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (fromp_id = 124047)
18. 0.000 0.000 ↓ 0.0

Index Only Scan using user_profile_userprofile_pkey on user_profile_userprofile u0_2 (cost=0.29..7.36 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = u1_2.top_id)
19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.71..221.85 rows=29 width=4) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

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 rows= loops=)

  • Index Cond: (from_userprofile_id = 124047)
21. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = u1_3.to_userprofile_id)
22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.71..1,433.03 rows=151 width=4) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Index Scan using user_profile_userprofile_blacklist_to_userprofile_id on user_profile_userprofile_blacklist u1_4 (cost=0.42..426.60 rows=151 width=4) (actual rows= loops=)

  • Index Cond: (to_userprofile_id = 124047)
24. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = u1_4.from_userprofile_id)
25.          

SubPlan (for Hash Join)

26. 0.000 0.000 ↓ 0.0

Limit (cost=0.42..8.44 rows=1 width=4) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Index Scan using devices_person_id_idx on user_profile_devices (cost=0.42..8.44 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (person_id = user_profile_userprofile.id)
28. 0.000 0.000 ↓ 0.0

Limit (cost=0.42..8.44 rows=1 width=4) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

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 rows= loops=)

  • Index Cond: (person_id = user_profile_userprofile.id)
30. 0.000 0.000 ↓ 0.0

Limit (cost=0.42..8.44 rows=1 width=4) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

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 rows= loops=)

  • Index Cond: (person_id = user_profile_userprofile.id)
32. 0.000 0.000 ↓ 0.0

Limit (cost=0.29..8.31 rows=1 width=1) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Index Scan using user_profile_preferences_83a0eb3f on user_profile_preferences (cost=0.29..8.31 rows=1 width=1) (actual rows= loops=)

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

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

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

BitmapAnd (cost=9.69..9.69 rows=1 width=0) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

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

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

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

  • Index Cond: (top_id = user_profile_userprofile.id)
38. 0.000 0.000 ↓ 0.0

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 rows= loops=)

  • Index Cond: (fromp_id = 124047)