Improving Performance of Tags

[bradeaton]

We have an application that makes extensive use of tags across many different models. We just recently upgraded our application to Rails 6.1 and acts-as-taggable 9.0.1. New Relic transaction traces are showing significant time spent these queries:

1,400ms on this one:
SELECT "tags".* FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."taggable_id" = $? AND "taggings"."taggable_type" = $? AND (taggings.context = ? AND taggings.tagger_id IS ?)

425ms on this one:
SELECT "taggings".* FROM "taggings" WHERE "taggings"."taggable_id" = $? AND "taggings"."taggable_type" = $?

Here's what our indexes look like right now:

  create_table "taggings", id: :serial, force: :cascade do |t|
    t.integer "tag_id"
    t.integer "taggable_id"
    t.string "taggable_type", limit: 255
    t.integer "tagger_id"
    t.string "tagger_type", limit: 255
    t.string "context", limit: 128
    t.datetime "created_at"
    t.string "tenant", limit: 128
    t.index ["context"], name: "index_taggings_on_context"
    t.index ["tag_id", "taggable_id", "taggable_type", "context", "tagger_id", "tagger_type"], name: "taggings_idx", unique: true
    t.index ["tag_id"], name: "index_taggings_on_tag_id"
    t.index ["taggable_id", "taggable_type", "context"], name: "index_taggings_on_taggable_id_and_taggable_type_and_context"
    t.index ["taggable_id", "taggable_type", "tagger_id", "context"], name: "taggings_idy"
    t.index ["taggable_id"], name: "index_taggings_on_taggable_id"
    t.index ["taggable_type"], name: "index_taggings_on_taggable_type"
    t.index ["tagger_id", "tagger_type"], name: "index_taggings_on_tagger_id_and_tagger_type"
    t.index ["tagger_id"], name: "index_taggings_on_tagger_id"
    t.index ["tenant"], name: "index_taggings_on_tenant"
  end

  create_table "tags", id: :serial, force: :cascade do |t|
    t.string "name", limit: 255
    t.integer "taggings_count", default: 0
    t.index ["name"], name: "index_tags_on_name", unique: true
  end

We're looking for ways to speed these up. Our application is multi-tenant within the same database. All our model classes are scoped on an account_id field. If we add acts_as_taggable_tenant :account_id on each of these models, is there some migration that we need to do for all the existing taggings?

Are there other things that are suggested to speed things up?

[bradeaton]

Just pinging on this question again... My main question is about tags on existing records. Our application is multi-tenant in the same DB. There is an accounts table and a contacts table. The contacts table has an account_id column. If I add acts_as_taggable_tenant :account_id to the contact model, will all the tags on existing contacts records benefit from this or would I have to delete the tags from each contact and add them back?

[NurfitraPujo]

Hello @bradeaton i know this is old post but anyway, our team also use this gem for tagging conversation rooms in our application. There is one heavy query that do aggregates on our model (rooms count by tag). This query can process up to 8 millions row at once so itu is very slow before optimi zed (~30s) and now it becomes ~5s.

My comments on your query is:

1. Just select the necessary columns
2. Make sure to create multicolumn index that consisted of all columns used in your where clause. include columns that used in your select and join keys

[darkamenosa]

I faced the almost same problem. It seems the best way is to create a new column on the table and store the cached tag. And query on that table not using these tables. (Ref: https://github.com/mbleigh/acts-as-taggable-on/wiki/Caching

I'm testing with my local, hope this works.

[sumirolabs]

Another solution is to add a new scope that reproduces the original query, eg

scope :with_tag, lambda {|tag| joins(:taggings).where(taggings: { tag_id: tag.id }) }

and then replace the tagged_with calls with this scope.

It would be nice to add the option of passing in an actual tag (instead of the name) and then use the query in the scope above.