Record count queries are extremely slow (>5 MINUTES)

[dja]

Expected behavior

We expect counts to be super fast and optimized for performance.

Actual behavior

Counts end up being 5+ minutes long, slowing our database to a crawl. The query should be extremely simple, like select count(*) from invoices instead of what you see below.

Failure Logs

N/A

Context

• Package Version: 6.3.2
• Rails Version: 5.2.5
• Database Dialect: PG
• Database Version: 10.12

[louisteenbrink]

Hi @dja 👋 ,

Forest Admin API is generating a default count query to manage the display and pagination of your data.

The solution to your problem would be to override this default query using an approximative count. If you don't need an exact count, it will be much faster to retrieve the data for your big tables.

Here is a code example for rails:

if ForestLiana::UserSpace.const_defined?('[ModelName]Controller')
  ForestLiana::UserSpace::[ModelName]Controller.class_eval do
    alias_method :default_count, :count

    # Get a number of ...
    def count
      if (!params['search'] && !params['filter'])
        sqlQuery = "SELECT reltuples AS count FROM pg_class WHERE relname = '[tableName]';"
        result = ActiveRecord::Base.connection.execute(sqlQuery)
        if result[0]
          return render serializer: nil, json: { count: result[0]['count'] }
        end
        default_count
      else
        default_count
      end
    end
  end
end

Let me know if that helps!

[dja]

Hi Louis, thanks for the fast reply - this looks like we would need to specify this override for every AR model we have, which adds unnecessary complexity to our app. I believe this should be something that's resolved directly in the Forest liana gem to make these queries performant directly.

[dja]

We've also tried this override:

# frozen_string_literal: true

module ForestLiana
  # This overrides the count method in ForestLiana to reduce the overhead of
  # performing a Postgres COUNT.
  module ResourcesGetterExtensions
    def count
      return super if @records.where_clause.any?

      table_name = ActiveRecord::Base.connection.quote(@resource.table_name)
      sql = "SELECT n_live_tup AS count FROM pg_stat_user_tables WHERE relname = #{table_name}"
      result = ActiveRecord::Base.connection.execute(sql)
      @records_count = result.values.dig(0, 0)
    end
  end
end

We tried this instead of reltuples because we were running into issues with it (only updated on analyze or vacuum), and the replacement we went with (above) was working but breaks with any maintenance/vacuums.

Additionally, having an inaccurate count negatively impacts pagination.

[louisteenbrink]

Hey @dja,

We just released the ability to deactivate count request in Forest Admin while keep a smooth UI/UX for end users.

class Forest::BooksController < ForestLiana::ResourcesController
  def count
    if (params[:filters])
      params[:collection] = 'Book'
      super
    else
      deactivate_count_response
    end
  end
end

Here are more info from our doc. Hope you'll find it useful.

[matthv]

The solution was given in @louisteenbrink's previous comment.

I’m closing this inactive issue. Don’t hesitate to open a new one if you encounter a bug.