search

Couchers-org / couchers

The next-generation couch surfing platform. Free forever. Community‑led. Non‑profit. Modern. Chuck us a star :)
https://couchers.org
MIT License
388 stars 79 forks source link

slow performance and timeouts during search #4830

Open jesseallhands opened 1 month ago

jesseallhands commented 1 month ago

Reports of slow performance and timeouts during search need to be addressed as discussed in meeting

aapeliv commented 1 month ago

Also needs to remedy the fact that we didn't find out about this...

jesseallhands commented 1 month ago

We can take a look at metrics in grafana and see if we can set some alarms

aapeliv commented 1 month ago

An offending query:

SELECT users.id, users.username, users.email, users.hashed_password, users.phone, ST_AsEWKB(users.geom) AS geom, users.geom_radius, users.city, users.hometown, users.joined, users.last_active, users.last_notified_message_id, users.last_notified_request_message_id, users.name, users.gender, users.pronouns, users.birthdate, users.full_name, users.avatar_key, users.hosting_status, users.meetup_status, users.community_standing, users.occupation, users.education, users.about_me, users.my_travels, users.things_i_like, users.about_place, users.additional_information, users.is_banned, users.is_deleted, users.is_superuser, users.undelete_token, users.undelete_until, users.max_guests, users.last_minute, users.has_pets, users.accepts_pets, users.pet_details, users.has_kids, users.accepts_kids, users.kid_details, users.has_housemates, users.housemate_details, users.wheelchair_accessible, users.smoking_allowed, users.smokes_at_home, users.drinking_allowed, users.drinks_at_home, users.other_host_info, users.sleeping_arrangement, users.sleeping_details, users.area, users.house_rules, users.parking, users.parking_details, users.camping_ok, users.accepted_tos, users.accepted_community_guidelines, users.filled_contributor_form, users.onboarding_emails_sent, users.last_onboarding_email_sent, users.in_sync_with_newsletter, users.opt_out_of_newsletter, users.digest_frequency, users.last_digest_sent, users.new_email, users.new_email_token, users.new_email_token_created, users.new_email_token_expiry, users.recommendation_score, users.phone_verification_token, users.phone_verification_sent, users.phone_verification_verified, users.phone_verification_attempts, users.stripe_customer_id, users.stripe_customer_id_old, users.has_passport_sex_gender_exception, users.has_donated, users.do_not_email, users.admin_note 
FROM users 
WHERE NOT (users.is_banned OR users.is_deleted) AND (users.id NOT IN (SELECT anon_1.blocked_user_id 
FROM (SELECT user_blocks.blocked_user_id AS blocked_user_id 
FROM user_blocks 
WHERE user_blocks.blocking_user_id = 1 UNION SELECT user_blocks.blocking_user_id AS blocking_user_id 
FROM user_blocks 
WHERE user_blocks.blocked_user_id = 1) AS anon_1)) AND (users.name ILIKE 'fun' OR users.username ILIKE 'fun' OR users.city ILIKE 'fun' OR users.hometown ILIKE 'fun' OR users.about_me ILIKE 'fun' OR users.my_travels ILIKE 'fun' OR users.things_i_like ILIKE 'fun' OR users.about_place ILIKE 'fun' OR users.additional_information ILIKE 'fun') AND ST_Within(users.geom, ST_MakeEnvelope(-120.234375,-36.315445,104.765625,61.143044, 4326)) AND users.recommendation_score <= 200000 ORDER BY users.recommendation_score DESC 
LIMIT 100;

With JIT on:

                                                                                                                                                                                                                                                                                                                                                          QUERY PLAN                                                                                                                                                                                                                                                                                                                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=512828.14..512829.10 rows=7 width=1326) (actual time=1934.759..1934.766 rows=0 loops=1)
   ->  Result  (cost=512828.14..512829.10 rows=7 width=1326) (actual time=462.715..462.721 rows=0 loops=1)
         ->  Sort  (cost=512828.14..512828.16 rows=7 width=1326) (actual time=462.714..462.719 rows=0 loops=1)
               Sort Key: users.recommendation_score DESC
               Sort Method: quicksort  Memory: 25kB
               ->  Bitmap Heap Scan on users  (cost=4392.73..512828.04 rows=7 width=1326) (actual time=462.695..462.700 rows=0 loops=1)
                     Recheck Cond: ((NOT is_banned) AND (NOT is_deleted))
                     Filter: ((NOT (hashed SubPlan 1)) AND (recommendation_score <= '200000'::double precision) AND (((name)::text ~~* 'fun'::text) OR ((username)::text ~~* 'fun'::text) OR ((city)::text ~~* 'fun'::text) OR ((hometown)::text ~~* 'fun'::text) OR ((about_me)::text ~~* 'fun'::text) OR ((my_travels)::text ~~* 'fun'::text) OR ((things_i_like)::text ~~* 'fun'::text) OR ((about_place)::text ~~* 'fun'::text) OR ((additional_information)::text ~~* 'fun'::text)) AND st_within(geom, '0103000020E6100000010000000500000000000000000F5EC0E6577380602842C000000000000F5EC0CCF10A444F924E400000000000315A40CCF10A444F924E400000000000315A40E6577380602842C000000000000F5EC0E6577380602842C0'::geometry))
                     Rows Removed by Filter: 39033
                     Heap Blocks: exact=7937
                     ->  BitmapAnd  (cost=4353.60..4353.60 rows=39835 width=0) (actual time=17.270..17.271 rows=0 loops=1)
                           ->  Bitmap Index Scan on ix_users_active  (cost=0.00..1201.46 rows=44234 width=0) (actual time=3.242..3.242 rows=44184 loops=1)
                           ->  Bitmap Index Scan on idx_users_geom  (cost=0.00..3151.89 rows=41548 width=0) (actual time=13.654..13.654 rows=40775 loops=1)
                                 Index Cond: (geom @ '0103000020E6100000010000000500000000000000000F5EC0E6577380602842C000000000000F5EC0CCF10A444F924E400000000000315A40CCF10A444F924E400000000000315A40E6577380602842C000000000000F5EC0E6577380602842C0'::geometry)
                     SubPlan 1
                       ->  HashAggregate  (cost=38.96..39.10 rows=14 width=8) (actual time=0.019..0.022 rows=0 loops=1)
                             Group Key: user_blocks.blocked_user_id
                             Batches: 1  Memory Usage: 24kB
                             ->  Append  (cost=4.21..38.92 rows=14 width=8) (actual time=0.017..0.019 rows=0 loops=1)
                                   ->  Bitmap Heap Scan on user_blocks  (cost=4.21..14.35 rows=7 width=8) (actual time=0.013..0.013 rows=0 loops=1)
                                         Recheck Cond: (blocking_user_id = 1)
                                         ->  Bitmap Index Scan on uq_user_blocks_blocking_user_id  (cost=0.00..4.21 rows=7 width=0) (actual time=0.010..0.011 rows=0 loops=1)
                                               Index Cond: (blocking_user_id = 1)
                                   ->  Bitmap Heap Scan on user_blocks user_blocks_1  (cost=14.35..24.50 rows=7 width=8) (actual time=0.002..0.003 rows=0 loops=1)
                                         Recheck Cond: (blocked_user_id = 1)
                                         ->  Bitmap Index Scan on uq_user_blocks_blocking_user_id  (cost=0.00..14.35 rows=7 width=0) (actual time=0.001..0.002 rows=0 loops=1)
                                               Index Cond: (blocked_user_id = 1)
 Planning Time: 4.431 ms
 JIT:
   Functions: 35
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 7.169 ms, Inlining 116.144 ms, Optimization 803.969 ms, Emission 551.957 ms, Total 1479.239 ms
 Execution Time: 1941.748 ms
(33 rows)

With JIT off:

                                                                                                                                                                                                                                                                                                                                                          QUERY PLAN                                                                                                                                                                                                                                                                                                                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=512828.14..512829.10 rows=7 width=1326) (actual time=513.268..513.274 rows=0 loops=1)
   ->  Result  (cost=512828.14..512829.10 rows=7 width=1326) (actual time=513.266..513.271 rows=0 loops=1)
         ->  Sort  (cost=512828.14..512828.16 rows=7 width=1326) (actual time=513.266..513.271 rows=0 loops=1)
               Sort Key: users.recommendation_score DESC
               Sort Method: quicksort  Memory: 25kB
               ->  Bitmap Heap Scan on users  (cost=4392.73..512828.04 rows=7 width=1326) (actual time=513.261..513.265 rows=0 loops=1)
                     Recheck Cond: ((NOT is_banned) AND (NOT is_deleted))
                     Filter: ((NOT (hashed SubPlan 1)) AND (recommendation_score <= '200000'::double precision) AND (((name)::text ~~* 'fun'::text) OR ((username)::text ~~* 'fun'::text) OR ((city)::text ~~* 'fun'::text) OR ((hometown)::text ~~* 'fun'::text) OR ((about_me)::text ~~* 'fun'::text) OR ((my_travels)::text ~~* 'fun'::text) OR ((things_i_like)::text ~~* 'fun'::text) OR ((about_place)::text ~~* 'fun'::text) OR ((additional_information)::text ~~* 'fun'::text)) AND st_within(geom, '0103000020E6100000010000000500000000000000000F5EC0E6577380602842C000000000000F5EC0CCF10A444F924E400000000000315A40CCF10A444F924E400000000000315A40E6577380602842C000000000000F5EC0E6577380602842C0'::geometry))
                     Rows Removed by Filter: 39033
                     Heap Blocks: exact=7937
                     ->  BitmapAnd  (cost=4353.60..4353.60 rows=39835 width=0) (actual time=13.634..13.635 rows=0 loops=1)
                           ->  Bitmap Index Scan on ix_users_active  (cost=0.00..1201.46 rows=44234 width=0) (actual time=3.239..3.239 rows=44184 loops=1)
                           ->  Bitmap Index Scan on idx_users_geom  (cost=0.00..3151.89 rows=41548 width=0) (actual time=10.090..10.090 rows=40775 loops=1)
                                 Index Cond: (geom @ '0103000020E6100000010000000500000000000000000F5EC0E6577380602842C000000000000F5EC0CCF10A444F924E400000000000315A40CCF10A444F924E400000000000315A40E6577380602842C000000000000F5EC0E6577380602842C0'::geometry)
                     SubPlan 1
                       ->  HashAggregate  (cost=38.96..39.10 rows=14 width=8) (actual time=0.017..0.020 rows=0 loops=1)
                             Group Key: user_blocks.blocked_user_id
                             Batches: 1  Memory Usage: 24kB
                             ->  Append  (cost=4.21..38.92 rows=14 width=8) (actual time=0.015..0.017 rows=0 loops=1)
                                   ->  Bitmap Heap Scan on user_blocks  (cost=4.21..14.35 rows=7 width=8) (actual time=0.011..0.011 rows=0 loops=1)
                                         Recheck Cond: (blocking_user_id = 1)
                                         ->  Bitmap Index Scan on uq_user_blocks_blocking_user_id  (cost=0.00..4.21 rows=7 width=0) (actual time=0.008..0.009 rows=0 loops=1)
                                               Index Cond: (blocking_user_id = 1)
                                   ->  Bitmap Heap Scan on user_blocks user_blocks_1  (cost=14.35..24.50 rows=7 width=8) (actual time=0.002..0.003 rows=0 loops=1)
                                         Recheck Cond: (blocked_user_id = 1)
                                         ->  Bitmap Index Scan on uq_user_blocks_blocking_user_id  (cost=0.00..14.35 rows=7 width=0) (actual time=0.001..0.002 rows=0 loops=1)
                                               Index Cond: (blocked_user_id = 1)
 Planning Time: 8.825 ms
 Execution Time: 513.460 ms
(29 rows)
bakeiro commented 1 month ago

do we need info like users.my_travels, users.education, users.undelete_token and many other fields? I feel like the less things we are selecting better performance (I think it's small difference but it's something)

bakeiro commented 1 month ago

Hi I just think about something...

what about the frontend loads the blocked users at login time, saves it in the memory and then filters the results from the backend, this way we could move the load to the frontend, something which I believe makes much sense since if we want to implement automatic search load as the user moves the map we need that performance