https://supabase.com/ logo
Join Discord
Powered by
Will there be an impact on performance if I create...
# off-topic
a
Will there be an impact on performance if I create a function to filter the data and do complex things like 
left join
and `pagination `and call this function whenever the user wants to fetch data and when it reaches 10 rows it calls the function again to fetch the other rows and so on...
s
As with so many use cases, it depends. JOIN statements do impact performance, and as you add more of them, the impact can become quite noticeable. The more complexity you invoke, your more impact you'll see. This will only become more noticeable so here's a few good tips: - Index your tables. PG performance can scale much better if it knows where to look for the data. It's much quicker for PG to index the data before you add anything to the table, but the write performance will suffer a little (but it shouldn't be noticeable in day to day usage) - Use partitions on large tables. This allow PG to break the data down into smaller logical chunks based on some specified criteria, and combined with indexes, it can turn a 4000ms query into a 400ms query in some extreme situations. For smaller tables, it's probably not gonna make much of a difference, but it definitely does for large tables (e.g. 500K+ rows). - Filter at the innermost level of your queries. This is something I see many people overlook - they'll write a query which does some filtering and joins across 4+ tables, but it'll take 1500ms+ to execute because they put their 
WHERE
statements at the end. By moving those 
WHERE
statements to the innermost query, it reduces the amount of data PG has to search through. Do this, and you can often improve the execution time of a query by 2 - 10x . - 
LATERAL
is something that doesn't seem to be mentioned much unless you go looking for it. It allows you to reference other subqueries without having to nest queries inside each other and can make it much easier to debug and read your query code
a
Thank you for these precious tips
and What do you suggest I do because I have a table for the `Posts `and another table called `PostsLikes `and I have created a function that fetches the posts sorted by the highest likes and this function just like this :
Copy code
SELECT p.*, COALESCE(c.LikesCount, 0) LikesCount
FROM Posts p
LEFT JOIN (
    SELECT postId, COUNT(*) LikesCount
    FROM PostsLikes
    GROUP BY postId
) c ON c.postId = p.id
ORDER BY p.id DESC
LIMIT 0,10
Then when the user gets the first 10 I will fetch the other 10 rows and so on by calling this function. But what I'm thinking is for each Post to have a column called `LikesCount `and create a `Trigger `that works when a new row is added in the table of `PostsLikes `and then increase the value of the `LikesCount `in the table of 
Posts
. in This way I no longer need to create a function to do complex operations like LEFT JOIN and COUNT(*) the rows... but will triggers work well without performance issues or impacts.
Or, for example, what will happen if 1000 new rows are added at the same time or more. Will the trigger work, whose task will be to increase the `LikesCount `if a new row is added and if a row is deleted, the `LikesCount`will be decreased, So is this trigger likely to fail if too many requests occur at the same time?
PreviousNext
Powered by