Added indexes to allow for User.with_username queries to avoid seq scans

just3ws · just3ws · commit 120712892a45 · 2014-07-03T21:03:11.000-05:00
diff --git a/db/migrate/20140703223632_create_case_insensitive_indexes_on_user.rb b/db/migrate/20140703223632_create_case_insensitive_indexes_on_user.rb
@@ -0,0 +1,29 @@
+class CreateCaseInsensitiveIndexesOnUser < ActiveRecord::Migration
+
+  # User.with_username looks up on following fields almost
+  # constantly but with a UPPER(fieldname) = UPPER(val)
+  # which is nasty and slow, add upcase and downcase indexes
+  # to avoid the problem
+
+  def up
+    execute 'create index ix_users_github_lower on users (lower(github) varchar_pattern_ops)'
+    execute 'create index ix_users_github_upper on users (upper(github) varchar_pattern_ops)'
+    execute 'create index ix_users_linkedin_lower on users (lower(linkedin) varchar_pattern_ops)'
+    execute 'create index ix_users_linkedin_upper on users (upper(linkedin) varchar_pattern_ops)'
+    execute 'create index ix_users_twitter_lower on users (lower(twitter) varchar_pattern_ops)'
+    execute 'create index ix_users_twitter_upper on users (upper(twitter) varchar_pattern_ops)'
+    execute 'create index ix_users_username_lower on users (lower(username) varchar_pattern_ops)'
+    execute 'create index ix_users_username_upper on users (upper(username) varchar_pattern_ops)'
+  end
+
+  def down
+    execute 'drop index if exists ix_users_github_lower'
+    execute 'drop index if exists ix_users_github_upper'
+    execute 'drop index if exists ix_users_linkedin_lower'
+    execute 'drop index if exists ix_users_linkedin_upper'
+    execute 'drop index if exists ix_users_twitter_lower'
+    execute 'drop index if exists ix_users_twitter_upper'
+    execute 'drop index if exists ix_users_username_lower'
+    execute 'drop index if exists ix_users_username_upper'
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
@@ -11,7 +11,7 @@
 #
 # It's strongly recommended to check this file into your version control system.
 
-ActiveRecord::Schema.define(:version => 20140701170008) do
+ActiveRecord::Schema.define(:version => 20140703223632) do
 
   create_table "alias_tags", :id => false, :force => true do |t|
     t.integer "tag_id"

Original file line number	Diff line number	Diff line change
`@@ -11,7 +11,7 @@`
`11`	`11`	`#`
`12`	`12`	`# It's strongly recommended to check this file into your version control system.`
`13`	`13`
`14`		`-ActiveRecord::Schema.define(:version => 20140701170008) do`
	`14`	`+ActiveRecord::Schema.define(:version => 20140703223632) do`
`15`	`15`
`16`	`16`	`create_table "alias_tags", :id => false, :force => true do \|t\|`
`17`	`17`	`t.integer "tag_id"`