Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Disable COUNT(*) for filtered collections #6492

Closed
kceb opened this issue Oct 2, 2020 · 2 comments · May be fixed by #7489
Closed

Disable COUNT(*) for filtered collections #6492

kceb opened this issue Oct 2, 2020 · 2 comments · May be fixed by #7489

Comments

@kceb
Copy link

kceb commented Oct 2, 2020 •
edited

Did you find a bug?

I'm using pagination_total false on the index collection, which works fine and as expected. However, I'm noticing in the SQL debug logs that when I filter a collection (by user_id), it is still doing a SELECT COUNT(*) FROM (SELECT 1 AS one FROM "large_table" WHERE "large_table"."user_id" = 123 ORDER BY "large_table"."id" desc LIMIT $1 OFFSET $2) subquery_for_count.

Is there a way to disable this for filters? I have an extremely large table and it is causing my app to timeout.

I'm just using the default filter method on my activeadmin model

Expected behavior

Filtering large tables should not run an expensive COUNT(*) or ORDER BY (even if it is using limit and offset)

Actual behavior

Request times out

How to reproduce

Have an extremely large table and filter on an indexed column like user_id. Expect it to be relatively performant and not run things like COUNT(*) unnecessarily.
@kceb kceb mentioned this issue Oct 2, 2020
Closed
@allisonphillips allisonphillips mentioned this issue May 14, 2022
Open
@allisonphillips
Copy link

I put up a PR for this: #7489

I refactored this to prevent it from triggering a COUNT query on loaded records:

module ActiveAdmin
  module Helpers
    module Collection
      def collection_size(c = collection)
        # 'length' avoids count query on unloaded collection with a limit_value
        return c.length if c.is_a?(Array) || c.limit_value.present?

        c = c.except :select, :order
        # Size prevents redundant COUNT queries on loaded collections
        c.group_values.present? ? c.size.size : c.size
      end
    end
  end
end

Haven't contributed to ActiveAdmin before so I'm not sure how long their review process takes, but I believe this to be the fix.

@javierjulio
Copy link
Member

There is a PR ready for this that needs an update. Thank you.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Collection Helper should not make redundant COUNT queries. allisonphillips/activeadmin
3 participants
@javierjulio @allisonphillips @kceb