Post B6dCXBBFHUEC3HEmKe by natty@astolfo.social
 (DIR) More posts by natty@astolfo.social
 (DIR) Post #B6dCXArkRydF4o7CwS by hazelnoot@enby.life
       0 likes, 0 repeats
       
       SQL (PostgreSQL) optimization questionHi friends! I'm struggling with a query optimization problem, where the logical solution is clear but I'm not sure SQL supports any mechanism to implement it.The problem is that I have large number of similar queries, each fetching the same type of data, performing similar baseline filtering, and following up with case-specific selection logic. For all of these queries, 99% of execution time is spent in the "similar baseline filtering" step, particularly in an ugly (but necessary) recursive join with up to 4 recursions for each row.This is where the optimization is possible: the values retrieved by those recursions will almost never change once they've been calculated. With a small change to application logic, they can be made to actually never change - the value will be deterministic from the moment each row is inserted.Unfortunately, I cannot change the schema of this table. If I could, then I'd just add a block of de-normalized fields and call it a day. And if the queries weren't paginated, then I could move the filtering into application code and cache it there. But neither option is possible here, so I'm faced with the prospect of teaching PostgreSQL how to cache the result of a join.I first considered Materialized Views, which seemed like a perfect solution until I realized that they don't automatically add new rows. I'd have to refresh the whole thing a hundred times per second which is obviously not practical. Regular Views are also not an option, as we'd still execute the recursive join on every query. I could potentially add a new table containing just the resolved values, populated from a trigger on the source table - but our migration tooling doesn't support triggers, so I'd have to implement that first. It will also still require a join (just not a recursive one).Currently, I'm leaning towards option 3 - new table with trigger. But given the downsides, I'd like to make sure I didn't miss any better solution. Does anyone have thoughts, suggestions, or ideas for this? Any help will be greatly appreciated!!#SoftwareDevelopment #SoftwareDev #DevHelp #SQL #PostgreSQL
       
 (DIR) Post #B6dCXBBFHUEC3HEmKe by natty@astolfo.social
       0 likes, 0 repeats
       
       @hazelnoot@enby.life Damn is this about the Misskey timelines?You could store the IDs of the filter results, then do the lookup; then you'd know what to pull/cache ahead of time, but you could still call the normal query, just with a much better cache hit rate if you used some temporary table or viewThe downside is that this is two table round trips, but you could even use something that's not Postgres for the IDsI am still thinking about a similar problem so I am careful not to get too nerd-sniped ​:neofox_woozy:​
       
 (DIR) Post #B6dCkkTPNFSpH9Ceu0 by natty@astolfo.social
       0 likes, 0 repeats
       
       @hazelnoot@enby.life I even considered literally throwing the timelines in a per-user sqlite file (Bluesky moment?), since they are never correlated
       
 (DIR) Post #B6dDWhoSZJo8vMuwyG by kopper@not-brain.d.on-t.work
       0 likes, 0 repeats
       
       @natty @hazelnoot at what point are you Just reinventing the redis fanout timelines both misskey and mastodon have
       
 (DIR) Post #B6dDWi1Dns13Ywt8rY by natty@astolfo.social
       0 likes, 0 repeats
       
       @kopper@not-brain.d.on-t.work @hazelnoot@enby.life yes but what I am suggesting is a read optimization by improving the cache rate, not feeding the timeline from the Redis exclusively