HazelnootSQL (PostgreSQL) optimization question
Hi 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
Hi 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
F95zone | Adult Games | Comics | Mods | Cheats [Unofficial]
