graphile
Requested fields seem to be being calculated while paging through data set
If you're writing a query that is paging through data and has a calculated column nested in the result that comes back, the query that postgraphile is sending to the DB seems to take O(n^2) time based on how far through the total pages you're fetching.
We have a query similar to:
query($after: Cursor, $first: Int) {
invoices(after: $after, first: $first) {
edges {
node {
id
items {
edges {
node {
id
users {
edges {
node {
superExpensiveCalculatedColumn
id
}
}
}
}
}
}
}
}
}
}
The first few pages load very fast, but after the first few pages it slows down dramatically. The further through the result set you are the longer this query takes. If you remove the superExpensiveCalculatedColumn from the returned result then the query is super fast.
We worked around this by switching out this specific computed column for a data-loader approach, but this may have worked because then it was just doing index-only scans. Will have to dig in deeper when time. It could be that PostgreSQL v12's CTE inlining would solve this. Also possible that increasing the "cost" of the function would cause it to be cached more heavily.
Another approach is to only select the table columns before applying the filter via SELECT *, and then to apply the calculations/transformations afterwards. This does, however, require that SELECT * is permitted on the table, which means that we'd need branching logic to calculate in two different ways depending on whether SELECT * is permitted or not.
We actually solved this a while ago with the useAsterisk setting, though #765 implies we didn't do it everywhere.
PostGraphile V5's SQL is significantly more efficient than V4's, so I'm going to close this.