It is useful to be able to reset the order-by in some situations, or retrieve the order by. Our case is simple enough, we have a generic function that executes the query and returns a slice of objects, the total number of rows and an error object:
It performs a count on the rows returned by the query, before applying the limit and offset. It does this by way of a cte wrapper over the original query: with count_cte as ( ... original query with order-by ) select count(*) from count_cte;
It then applies the limit and offset, and executes the query again, this time paginated.
Saving the order-by parts, then removing the order-by will allow the count(*) to execute faster, since its not concerned with ordering rows. Then on the second execution, I can apply the order-by, limit and offset. It's unfortunate that Postgresql does not perform this optimisation itself.
Why not use a windowing such as count(*) over() as total_rows? Because this performs worse in most situations (which could be related to the order-by again). Further, as I am using a Go generic function, I cannot create a new struct to scan into, that embeds to type T and TotalRows, as this is forbidden in Go, so we do the count and query as two steps.
It is useful to be able to reset the order-by in some situations, or retrieve the order by. Our case is simple enough, we have a generic function that executes the query and returns a slice of objects, the total number of rows and an error object:
func QueryRowsPaginatedSq[T any](db Dbi, limit, offset uint64, builder squirrel.SelectBuilder) ([]T, int64, error)
It performs a count on the rows returned by the query, before applying the limit and offset. It does this by way of a cte wrapper over the original query: with count_cte as ( ... original query with order-by ) select count(*) from count_cte;
It then applies the limit and offset, and executes the query again, this time paginated.
Saving the order-by parts, then removing the order-by will allow the count(*) to execute faster, since its not concerned with ordering rows. Then on the second execution, I can apply the order-by, limit and offset. It's unfortunate that Postgresql does not perform this optimisation itself.
Why not use a windowing such as count(*) over() as total_rows? Because this performs worse in most situations (which could be related to the order-by again). Further, as I am using a Go generic function, I cannot create a new struct to scan into, that embeds to type T and TotalRows, as this is forbidden in Go, so we do the count and query as two steps.
This request is a very simple change.