Optimize count query for pagination_total: false option
#6911
Conversation
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
`total_pages` is listed twice.
Queries like `SELECT COUNT(*) FROM (SELECT DISTINCT resources.* FROM resources ORDER BY resources.created_at DESC LIMIT 1 OFFSET 30) subquery_for_count` are too inefficient
2 tasks
d161507 to
fe0f95e
Compare
|
I updated the PR.
|
fe0f95e to
f9b9f42
Compare
f9b9f42 to
948f71f
Compare
Codecov ReportAll modified and coverable lines are covered by tests ✅
Additional details and impacted files@@ Coverage Diff @@
## master #6911 +/- ##
=======================================
Coverage 99.11% 99.11%
=======================================
Files 141 141
Lines 4067 4069 +2
=======================================
+ Hits 4031 4033 +2
Misses 36 36 ☔ View full report in Codecov by Sentry. |
pagination_total: false option
javierjulio
approved these changes
Aug 27, 2024
There was a problem hiding this comment.
Thanks! If I understand right, this is an optimization for when using the pagination_total: false option? I've updated the PR title to reflect that for the release notes. Feel free to change if you think it can be improved as that's my only concern here. I think it would help to make any pagination query improvements we can so thank you for seeing this through.
mgrunberg
added a commit
that referenced
this pull request
Sep 4, 2024
* Remove duplicated delegation `total_pages` is listed twice. * Remove ORDER BY from count subquery Queries like `SELECT COUNT(*) FROM (SELECT DISTINCT resources.* FROM resources ORDER BY resources.created_at DESC LIMIT 1 OFFSET 30) subquery_for_count` are too inefficient * add specs about ensure count query does not include ORDER BY clause * exclude also select because based on https://github.com/activeadmin/activeadmin/pull/7489\#issuecomment-1554197081 --------- Co-authored-by: Rafael Sales <rafaelcds@gmail.com> Co-authored-by: David Rodríguez <deivid-rodriguez> Co-authored-by: Matias Grunberg <matias@yellowspot.dev>
mgrunberg
added a commit
that referenced
this pull request
Sep 4, 2024
* Remove duplicated delegation `total_pages` is listed twice. * Remove ORDER BY from count subquery Queries like `SELECT COUNT(*) FROM (SELECT DISTINCT resources.* FROM resources ORDER BY resources.created_at DESC LIMIT 1 OFFSET 30) subquery_for_count` are too inefficient * add specs about ensure count query does not include ORDER BY clause * exclude also select because based on https://github.com/activeadmin/activeadmin/pull/7489\#issuecomment-1554197081 --------- Co-authored-by: Rafael Sales <rafaelcds@gmail.com> Co-authored-by: David Rodríguez <deivid-rodriguez> Co-authored-by: Matias Grunberg <matias@yellowspot.dev>
mgrunberg
added a commit
that referenced
this pull request
Sep 4, 2024
* Optimize count query for `pagination_total: false` option (#6911) * Remove duplicated delegation `total_pages` is listed twice. * Remove ORDER BY from count subquery Queries like `SELECT COUNT(*) FROM (SELECT DISTINCT resources.* FROM resources ORDER BY resources.created_at DESC LIMIT 1 OFFSET 30) subquery_for_count` are too inefficient * add specs about ensure count query does not include ORDER BY clause * exclude also select because based on https://github.com/activeadmin/activeadmin/pull/7489\#issuecomment-1554197081 --------- Co-authored-by: Rafael Sales <rafaelcds@gmail.com> Co-authored-by: David Rodríguez <deivid-rodriguez> Co-authored-by: Matias Grunberg <matias@yellowspot.dev> * fix perform_database_query_matcher: make sure to keep match truthy after the first matched query --------- Co-authored-by: David Rodríguez <deivid.rodriguez@riseup.net> Co-authored-by: Rafael Sales <rafaelcds@gmail.com>
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
This is a rebased version of #5389.
Some of the changes proposed there have already been introduced, but there was some extra stuff that still seems useful. I guess this is lacking a spec, but seems ok to me other than that.