Add some extra indexes on issues

diff --git a/app/models/issue.rb b/app/models/issue.rb
index 27c0a7cc9bef3de79739b08bc827b4ad9a29ca74..dd068859a54dfb8bb8ded4abc4188605c9005a78 100644 (file)
--- a/app/models/issue.rb
+++ b/app/models/issue.rb
@@ -6,7 +6,7 @@
 #  reportable_type  :string           not null
 #  reportable_id    :integer          not null
 #  reported_user_id :integer
 #  reportable_type  :string           not null
 #  reportable_id    :integer          not null
 #  reported_user_id :integer

-#  status           :enum             default(NULL), not null
+#  status           :enum             default("open"), not null

 #  assigned_role    :enum             not null
 #  resolved_at      :datetime
 #  resolved_by      :integer
 #  assigned_role    :enum             not null
 #  resolved_at      :datetime
 #  resolved_by      :integer


@@ -17,8 +17,10 @@
 #
 # Indexes
 #
 #
 # Indexes
 #

+#  index_issues_on_assigned_role                      (assigned_role)

 #  index_issues_on_reportable_type_and_reportable_id  (reportable_type,reportable_id)
 #  index_issues_on_reported_user_id                   (reported_user_id)
 #  index_issues_on_reportable_type_and_reportable_id  (reportable_type,reportable_id)
 #  index_issues_on_reported_user_id                   (reported_user_id)

+#  index_issues_on_status                             (status)

 #  index_issues_on_updated_by                         (updated_by)
 #
 # Foreign Keys
 #  index_issues_on_updated_by                         (updated_by)
 #
 # Foreign Keys

diff --git a/db/migrate/20160822153055_create_issues_and_reports.rb b/db/migrate/20160822153055_create_issues_and_reports.rb
index b03ac24a8f937dcc5ee97686ddd87cc3e857cea1..544ddbd60c09a5bee95ea1f0a7ad884a6aa659d1 100644 (file)
--- a/db/migrate/20160822153055_create_issues_and_reports.rb
+++ b/db/migrate/20160822153055_create_issues_and_reports.rb
@@ -21,9 +21,11 @@ class CreateIssuesAndReports < ActiveRecord::Migration[5.0]
     add_foreign_key :issues, :users, :column => :resolved_by, :name => "issues_resolved_by_fkey"
     add_foreign_key :issues, :users, :column => :updated_by, :name => "issues_updated_by_fkey"
 
     add_foreign_key :issues, :users, :column => :resolved_by, :name => "issues_resolved_by_fkey"
     add_foreign_key :issues, :users, :column => :updated_by, :name => "issues_updated_by_fkey"
 

-    add_index :issues, :reported_user_id

     add_index :issues, [:reportable_type, :reportable_id]
     add_index :issues, [:reportable_type, :reportable_id]

-    add_index :issues, :updated_by
+    add_index :issues, [:reported_user_id]
+    add_index :issues, [:status]
+    add_index :issues, [:assigned_role]
+    add_index :issues, [:updated_by]

 
     create_table :reports do |t|
       t.integer :issue_id, :null => false
 
     create_table :reports do |t|
       t.integer :issue_id, :null => false


@@ -36,8 +38,8 @@ class CreateIssuesAndReports < ActiveRecord::Migration[5.0]
     add_foreign_key :reports, :issues, :name => "reports_issue_id_fkey"
     add_foreign_key :reports, :users, :column => :user_id, :name => "reports_user_id_fkey"
 
     add_foreign_key :reports, :issues, :name => "reports_issue_id_fkey"
     add_foreign_key :reports, :users, :column => :user_id, :name => "reports_user_id_fkey"
 

-    add_index :reports, :user_id

     add_index :reports, :issue_id
     add_index :reports, :issue_id

+    add_index :reports, :user_id

 
     create_table :issue_comments do |t|
       t.integer :issue_id, :null => false
 
     create_table :issue_comments do |t|
       t.integer :issue_id, :null => false


@@ -49,8 +51,8 @@ class CreateIssuesAndReports < ActiveRecord::Migration[5.0]
     add_foreign_key :issue_comments, :issues, :name => "issue_comments_issue_id_fkey"
     add_foreign_key :issue_comments, :users, :column => :user_id, :name => "issue_comments_user_id_fkey"
 
     add_foreign_key :issue_comments, :issues, :name => "issue_comments_issue_id_fkey"
     add_foreign_key :issue_comments, :users, :column => :user_id, :name => "issue_comments_user_id_fkey"
 

-    add_index :issue_comments, :user_id

     add_index :issue_comments, :issue_id
     add_index :issue_comments, :issue_id

+    add_index :issue_comments, :user_id

   end
 
   def down
   end
 
   def down

diff --git a/db/structure.sql b/db/structure.sql
index 077faffad1faef8125de386aadd5add0675bb16b..48fedeba56b65d1247762f7a35eb29f9a5a1b98b 100644 (file)
--- a/db/structure.sql
+++ b/db/structure.sql
@@ -1401,21 +1401,21 @@ ALTER TABLE ONLY gpx_files ALTER COLUMN id SET DEFAULT nextval('gpx_files_id_seq
 
 
 --
 
 
 --

--- Name: messages id; Type: DEFAULT; Schema: public; Owner: -
+-- Name: issue_comments id; Type: DEFAULT; Schema: public; Owner: -

 --
 
 ALTER TABLE ONLY issue_comments ALTER COLUMN id SET DEFAULT nextval('issue_comments_id_seq'::regclass);
 
 
 --
 --
 
 ALTER TABLE ONLY issue_comments ALTER COLUMN id SET DEFAULT nextval('issue_comments_id_seq'::regclass);
 
 
 --

--- Name: id; Type: DEFAULT; Schema: public; Owner: -
+-- Name: issues id; Type: DEFAULT; Schema: public; Owner: -

 --
 
 ALTER TABLE ONLY issues ALTER COLUMN id SET DEFAULT nextval('issues_id_seq'::regclass);
 
 
 --
 --
 
 ALTER TABLE ONLY issues ALTER COLUMN id SET DEFAULT nextval('issues_id_seq'::regclass);
 
 
 --

--- Name: id; Type: DEFAULT; Schema: public; Owner: -
+-- Name: messages id; Type: DEFAULT; Schema: public; Owner: -

 --
 
 ALTER TABLE ONLY messages ALTER COLUMN id SET DEFAULT nextval('messages_id_seq'::regclass);
 --
 
 ALTER TABLE ONLY messages ALTER COLUMN id SET DEFAULT nextval('messages_id_seq'::regclass);


@@ -1457,14 +1457,14 @@ ALTER TABLE ONLY redactions ALTER COLUMN id SET DEFAULT nextval('redactions_id_s
 
 
 --
 
 
 --

--- Name: user_blocks id; Type: DEFAULT; Schema: public; Owner: -
+-- Name: reports id; Type: DEFAULT; Schema: public; Owner: -

 --
 
 ALTER TABLE ONLY reports ALTER COLUMN id SET DEFAULT nextval('reports_id_seq'::regclass);
 
 
 --
 --
 
 ALTER TABLE ONLY reports ALTER COLUMN id SET DEFAULT nextval('reports_id_seq'::regclass);
 
 
 --

--- Name: id; Type: DEFAULT; Schema: public; Owner: -
+-- Name: user_blocks id; Type: DEFAULT; Schema: public; Owner: -

 --
 
 ALTER TABLE ONLY user_blocks ALTER COLUMN id SET DEFAULT nextval('user_blocks_id_seq'::regclass);
 --
 
 ALTER TABLE ONLY user_blocks ALTER COLUMN id SET DEFAULT nextval('user_blocks_id_seq'::regclass);


@@ -1644,7 +1644,7 @@ ALTER TABLE ONLY gpx_files
 
 
 --
 
 
 --

--- Name: issue_comments_pkey; Type: CONSTRAINT; Schema: public; Owner: -
+-- Name: issue_comments issue_comments_pkey; Type: CONSTRAINT; Schema: public; Owner: -

 --
 
 ALTER TABLE ONLY issue_comments
 --
 
 ALTER TABLE ONLY issue_comments


@@ -1652,7 +1652,7 @@ ALTER TABLE ONLY issue_comments
 
 
 --
 
 
 --

--- Name: issues_pkey; Type: CONSTRAINT; Schema: public; Owner: -
+-- Name: issues issues_pkey; Type: CONSTRAINT; Schema: public; Owner: -

 --
 
 ALTER TABLE ONLY issues
 --
 
 ALTER TABLE ONLY issues


@@ -1756,7 +1756,7 @@ ALTER TABLE ONLY relations
 
 
 --
 
 
 --

--- Name: reports_pkey; Type: CONSTRAINT; Schema: public; Owner: -
+-- Name: reports reports_pkey; Type: CONSTRAINT; Schema: public; Owner: -

 --
 
 ALTER TABLE ONLY reports
 --
 
 ALTER TABLE ONLY reports


@@ -2051,6 +2051,13 @@ CREATE INDEX index_issue_comments_on_issue_id ON issue_comments USING btree (iss
 CREATE INDEX index_issue_comments_on_user_id ON issue_comments USING btree (user_id);
 
 
 CREATE INDEX index_issue_comments_on_user_id ON issue_comments USING btree (user_id);
 
 

+--
+-- Name: index_issues_on_assigned_role; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX index_issues_on_assigned_role ON issues USING btree (assigned_role);
+
+

 --
 -- Name: index_issues_on_reportable_type_and_reportable_id; Type: INDEX; Schema: public; Owner: -
 --
 --
 -- Name: index_issues_on_reportable_type_and_reportable_id; Type: INDEX; Schema: public; Owner: -
 --


@@ -2065,6 +2072,13 @@ CREATE INDEX index_issues_on_reportable_type_and_reportable_id ON issues USING b
 CREATE INDEX index_issues_on_reported_user_id ON issues USING btree (reported_user_id);
 
 
 CREATE INDEX index_issues_on_reported_user_id ON issues USING btree (reported_user_id);
 
 

+--
+-- Name: index_issues_on_status; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX index_issues_on_status ON issues USING btree (status);
+
+

 --
 -- Name: index_issues_on_updated_by; Type: INDEX; Schema: public; Owner: -
 --
 --
 -- Name: index_issues_on_updated_by; Type: INDEX; Schema: public; Owner: -
 --


@@ -2541,7 +2555,7 @@ ALTER TABLE ONLY gpx_files
 
 
 --
 
 
 --

--- Name: issue_comments_issue_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
+-- Name: issue_comments issue_comments_issue_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

 --
 
 ALTER TABLE ONLY issue_comments
 --
 
 ALTER TABLE ONLY issue_comments


@@ -2549,7 +2563,7 @@ ALTER TABLE ONLY issue_comments
 
 
 --
 
 
 --

--- Name: issue_comments_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
+-- Name: issue_comments issue_comments_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

 --
 
 ALTER TABLE ONLY issue_comments
 --
 
 ALTER TABLE ONLY issue_comments


@@ -2557,7 +2571,7 @@ ALTER TABLE ONLY issue_comments
 
 
 --
 
 
 --

--- Name: issues_reported_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
+-- Name: issues issues_reported_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

 --
 
 ALTER TABLE ONLY issues
 --
 
 ALTER TABLE ONLY issues


@@ -2565,7 +2579,7 @@ ALTER TABLE ONLY issues
 
 
 --
 
 
 --

--- Name: issues_resolved_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
+-- Name: issues issues_resolved_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

 --
 
 ALTER TABLE ONLY issues
 --
 
 ALTER TABLE ONLY issues


@@ -2573,7 +2587,7 @@ ALTER TABLE ONLY issues
 
 
 --
 
 
 --

--- Name: issues_updated_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
+-- Name: issues issues_updated_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

 --
 
 ALTER TABLE ONLY issues
 --
 
 ALTER TABLE ONLY issues


@@ -2693,7 +2707,7 @@ ALTER TABLE ONLY relations
 
 
 --
 
 
 --

--- Name: reports_issue_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
+-- Name: reports reports_issue_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

 --
 
 ALTER TABLE ONLY reports
 --
 
 ALTER TABLE ONLY reports


@@ -2701,7 +2715,7 @@ ALTER TABLE ONLY reports
 
 
 --
 
 
 --

--- Name: reports_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
+-- Name: reports reports_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

 --
 
 ALTER TABLE ONLY reports
 --
 
 ALTER TABLE ONLY reports