search

mbleigh / acts-as-taggable-on

A tagging plugin for Rails applications that allows for custom tagging along dynamic contexts.
http://mbleigh.lighthouseapp.com/projects/10116-acts-as-taggable-on
MIT License
4.96k stars 1.19k forks source link

Performance with wild: true in Postgres #1028

Open multiplegeorges opened 3 years ago

multiplegeorges commented 3 years ago

Hey all,

Thanks for the project! It's great.

I've noticed a performance issue with searching tags with wild: true in Postgres.

For some reason, the query for wildcard tag names is structured like: 

SELECT "tags"."id" FROM "tags" WHERE LOWER("tags"."name") ILIKE '%tag_name%' ESCAPE '!'

Using LOWER with ILIKE is unnecessary and makes it hard to set up a proper GIN/trigram index on tags.name. With a standard schema in Rails, you can't set up an index with LOWER(tags.name) and I noticed that the pg planner wasn't using it anyway, though that may have been situational in my case.

The solution is to change https://github.com/mbleigh/acts-as-taggable-on/blob/master/lib/acts_as_taggable_on/taggable/tagged_with_query/query_base.rb#L29 to:

matches_attribute = matches_attribute.lower unless ActsAsTaggableOn.strict_case_match || ActsAsTaggableOn::Utils.using_postgresql?

and add an index on tags.name with:

ApplicationRecord.connection.execute("CREATE EXTENSION IF NOT EXISTS pg_trgm;")
add_index :tags, :name, using: :gin, opclass: { title: :gin_trgm_ops }, name: :index_on_tags_name_trigram

I've been able to speed up wild tag name searches by 80+% this way.

I'd be happy to create a pull request that implements this change+index, but I'm not sure how you handle migrations and upgrading. If someone could help me out with that, I'll share my branch and we can make a PR from that.

Cheers, hope this helps someone!

Georges

akostadinov commented 2 years ago

To write migrations, you have to create an additional migration file with proper :up and :down methods. You see what commands are needed between main branch and your changes and add to up/down methods.

trostli commented 1 year ago

Hi @multiplegeorges , I've noticed this as well even without the wild: true modifier. Did you end up creating a PR or pushing a branch somewhere?

multiplegeorges commented 1 year ago

@trostli My fork is here https://github.com/multiplegeorges/acts-as-taggable-on/commits/master but it's wildly out of date at this point 😄

The changes are relatively minor so I bet it can be brought up to date pretty easily.