General question about speed and optimization from a new user

Hey! General question for anyone who can answer, I built my mobile app using supabase as the backend but using rest API and cURLs.

example:

/Product?select=*,+Store(id,storeIcon,storeSlug),+Likes(id),+ProductVariant(id,option_1,option_2,option_3,title,price,+ProductImage(id,url)),ProductImage(id,url))&Likes.supabase_user_id=eq.20c06cd5-6f48-4107-bb0d-b05e4f46940&order=created_at.desc&limit=50

/Product?select=*,+Store(id,storeIcon,storeSlug),+Likes(id),+ProductVariant(id,option_1,option_2,option_3,title,price,+ProductImage(id,url)),ProductImage(id,url))&Likes.supabase_user_id=eq.20c06cd5-6f48-4107-bb0d-b05e4f46940&order=created_at.desc&limit=50

/Product?select=*,+Store(id,storeIcon,storeSlug),+Likes(id),+ProductVariant(id,option_1,option_2,option_3,title,price,+ProductImage(id,url)),ProductImage(id,url))&Likes.supabase_user_id=eq.20c06cd5-6f48-4107-bb0d-b05e4f46940&order=created_at.desc&limit=50

/Product?select=*,+Store(id,storeIcon,storeSlug),+Likes(id),+ProductVariant(id,option_1,option_2,option_3,title,price,+ProductImage(id,url)),ProductImage(id,url))&Likes.supabase_user_id=eq.20c06cd5-6f48-4107-bb0d-b05e4f46940&order=created_at.desc&limit=50

Recently I realized it takes a bit of time to sometimes load certain screens of data (product feed list) even though it is only returning the first 50 items + the records may take 15-20 secs to fetch and retrieve (seeing log explorer) even though it is slower as the DB gets bigger. I have upgraded to the Small Add on hoping this would help but its not that big of a difference.

Is it because I am using cURL strings to pull data and I should upgrade to the javascript instead of bash? Any insights would be helpful - new to coding and backends

garyaustin•12/8/22, 3:28 PM

JavaScript would not make a difference. You are accessing 5 or 6 tables there, if each has RLS that is going to take a while. You should do a quick test without RLS on , or with service role to see if that is the impact. You may need to simplify your RLS, your number of joins and make sure you have indexes where they will help.

garyaustin•12/8/22, 3:34 PM

You should also learn how to use explain/analyze either in the SQL editor or now from the API with PostgREST 10. You might get more insight into where the time is going.

cenoOP•12/8/22, 3:41 PM

I just saw that none of my tables have RLS on which may be concerning I guess

cenoOP•12/8/22, 3:41 PM

Then its the fact I am accessing multiple tables?

garyaustin•12/8/22, 3:45 PM

That seems pretty slow for just accessing multiple tables with joins as they use foreign keys which point to a primary key which is indexed. Do you have an index on created_at that you are sorting order on? Same with supabase_user_id that you are filtering on?

Best bet is going to be running explain (you can do that easily in the SQL window if RLS is not involved.

garyaustin•12/8/22, 3:49 PM

You could also try starting with main query and gradually add joins to see if one particular one is the issue to focus on.

cenoOP•12/8/22, 3:59 PM

The problem is, the joins bring together a feed. So the product information in the product, productimages in their table, product options, then likes if the user has like the product to return. I wouldnt know how to decrease the amount of joins but I will look into this thanks

cenoOP•12/8/22, 3:59 PM

And you dont think just upgrading the server itself would help?

garyaustin•12/8/22, 4:05 PM

My last suggestion was just a way to try and narrow done the slow part of the query so you could focus on it ( if it is just one part) not that you would just drop that. Upgrading might help if you are hitting a memory limit and having to role stuff to disk. Just CPU I would think would be linear scale which seems would not help much, especially in the long run.

It is very hard to give accurate advice without really knowing where the bottle neck is.

cenoOP•12/8/22, 4:06 PM

Thanks will dig around if thats the case

garyaustin•12/8/22, 4:09 PM

I would certainly try an index on the sortby case (especially if that is a large table) as that will need to scan every row without one.

General question about speed and optimization from a new user

Similar Threads

Similar Threads

Similar Threads