explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S1VR

Settings
# exclusive inclusive rows x rows loops node
1. 8.075 276.067 ↓ 51.7 11,631 1

Unique (cost=21,586.31..21,594.18 rows=225 width=122) (actual time=264.780..276.067 rows=11,631 loops=1)

2. 14.946 267.992 ↓ 51.7 11,631 1

Sort (cost=21,586.31..21,586.87 rows=225 width=122) (actual time=264.778..267.992 rows=11,631 loops=1)

  • Sort Key: auth_user.last_login DESC, ((SubPlan 2)) DESC, ((SubPlan 1)), ((SubPlan 3)), ((alternatives: SubPlan 4 or hashed SubPlan 5)), ((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
  • Sort Method: quicksort Memory: 3100kB
3. 38.568 253.046 ↓ 51.7 11,631 1

Hash Join (cost=9,319.27..21,577.52 rows=225 width=122) (actual time=52.456..253.046 rows=11,631 loops=1)

  • Hash Cond: (auth_user.id = user_profile_userprofile.user_id)
4. 11.041 11.041 ↓ 1.0 16,199 1

Seq Scan on auth_user (cost=0.00..1,567.83 rows=16,070 width=20) (actual time=0.061..11.041 rows=16,199 loops=1)

  • Filter: (last_login >= '2019-10-27 11:27:36.067515+00'::timestamp with time zone)
  • Rows Removed by Filter: 27793
5. 14.341 51.627 ↓ 38.9 23,917 1

Hash (cost=9,311.58..9,311.58 rows=615 width=100) (actual time=51.627..51.627 rows=23,917 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 4051kB
6. 33.300 37.286 ↓ 38.9 23,917 1

Seq Scan on user_profile_userprofile (cost=5,917.77..9,311.58 rows=615 width=100) (actual time=4.408..37.286 rows=23,917 loops=1)

  • 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)))
  • Rows Removed by Filter: 18108
7.          

SubPlan (for Seq Scan)

8. 0.352 1.583 ↑ 1.0 352 1

Nested Loop (cost=15.49..2,104.91 rows=359 width=4) (actual time=0.103..1.583 rows=352 loops=1)

9. 0.473 0.527 ↑ 1.0 352 1

Bitmap Heap Scan on user_profile_friendship u1 (cost=15.20..946.52 rows=359 width=4) (actual time=0.089..0.527 rows=352 loops=1)

  • Recheck Cond: (person1_id = 124047)
  • Heap Blocks: exact=324
10. 0.054 0.054 ↑ 1.0 355 1

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

  • Index Cond: (person1_id = 124047)
11. 0.704 0.704 ↑ 1.0 1 352

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

  • Index Cond: (id = u1.person2_id)
  • Heap Fetches: 48
12. 0.149 1.244 ↑ 1.3 306 1

Nested Loop (cost=15.90..2,372.34 rows=411 width=4) (actual time=0.081..1.244 rows=306 loops=1)

13. 0.437 0.483 ↑ 1.3 306 1

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

  • Recheck Cond: (top_id = 124047)
  • Heap Blocks: exact=282
14. 0.046 0.046 ↑ 1.3 306 1

Bitmap Index Scan on invitations_top_id_idx (cost=0.00..15.50 rows=411 width=0) (actual time=0.045..0.046 rows=306 loops=1)

  • Index Cond: (top_id = 124047)
15. 0.612 0.612 ↑ 1.0 1 306

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

  • Index Cond: (id = u1_1.fromp_id)
  • Heap Fetches: 30
16. 0.063 0.544 ↓ 4.2 159 1

Nested Loop (cost=0.71..279.17 rows=38 width=4) (actual time=0.020..0.544 rows=159 loops=1)

17. 0.163 0.163 ↓ 4.2 159 1

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.011..0.163 rows=159 loops=1)

  • Index Cond: (fromp_id = 124047)
18. 0.318 0.318 ↑ 1.0 1 159

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

  • Index Cond: (id = u1_2.top_id)
  • Heap Fetches: 19
19. 0.029 0.161 ↓ 1.6 46 1

Nested Loop (cost=0.71..141.85 rows=29 width=4) (actual time=0.029..0.161 rows=46 loops=1)

20. 0.040 0.040 ↓ 1.6 46 1

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.020..0.040 rows=46 loops=1)

  • Index Cond: (from_userprofile_id = 124047)
  • Heap Fetches: 7
21. 0.092 0.092 ↑ 1.0 1 46

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

  • Index Cond: (id = u1_3.to_userprofile_id)
  • Heap Fetches: 5
22. 0.043 0.454 ↑ 1.1 132 1

Nested Loop (cost=0.71..1,017.03 rows=151 width=4) (actual time=0.024..0.454 rows=132 loops=1)

23. 0.147 0.147 ↑ 1.1 132 1

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 time=0.011..0.147 rows=132 loops=1)

  • Index Cond: (to_userprofile_id = 124047)
24. 0.264 0.264 ↑ 1.0 1 132

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

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

SubPlan (for Hash Join)

26. 11.631 46.524 ↑ 1.0 1 11,631

Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=11,631)

27. 34.893 34.893 ↑ 1.0 1 11,631

Index Scan using devices_person_id_idx on user_profile_devices (cost=0.42..8.44 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=11,631)

  • Index Cond: (person_id = user_profile_userprofile.id)
28. 11.631 34.893 ↑ 1.0 1 11,631

Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=11,631)

29. 23.262 23.262 ↑ 1.0 1 11,631

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.002..0.002 rows=1 loops=11,631)

  • Index Cond: (person_id = user_profile_userprofile.id)
30. 11.631 34.893 ↑ 1.0 1 11,631

Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=11,631)

31. 23.262 23.262 ↑ 1.0 1 11,631

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=11,631)

  • Index Cond: (person_id = user_profile_userprofile.id)
32. 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 = 124047) AND (top_id = user_profile_userprofile.id))
33. 0.000 0.000 ↓ 0.0 0

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

34. 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 = 124047)
35. 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)
36. 0.607 0.607 ↓ 4.2 159 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.014..0.607 rows=159 loops=1)

  • Index Cond: (fromp_id = 124047)
37. 11.631 34.893 ↑ 1.0 1 11,631

Limit (cost=0.29..8.31 rows=1 width=1) (actual time=0.003..0.003 rows=1 loops=11,631)

38. 23.262 23.262 ↑ 1.0 1 11,631

Index Scan using user_profile_preferences_83a0eb3f on user_profile_preferences (cost=0.29..8.31 rows=1 width=1) (actual time=0.002..0.002 rows=1 loops=11,631)

  • Index Cond: (profile_id = user_profile_userprofile.id)
Planning time : 2.065 ms
Execution time : 278.753 ms