From: Tom Hughes Date: Sun, 10 Jun 2018 14:13:23 +0000 (+0100) Subject: Add some extra indexes on issues X-Git-Tag: live~2981^2~10 X-Git-Url: https://git.openstreetmap.org/rails.git/commitdiff_plain/f227804093a5a82eaf724f9b028b25aa7cbf41a1 Add some extra indexes on issues --- diff --git a/app/models/issue.rb b/app/models/issue.rb index 27c0a7cc9..dd068859a 100644 --- 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 -# status :enum default(NULL), not null +# status :enum default("open"), not null # assigned_role :enum not null # resolved_at :datetime # resolved_by :integer @@ -17,8 +17,10 @@ # # 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_status (status) # 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 b03ac24a8..544ddbd60 100644 --- 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_index :issues, :reported_user_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 @@ -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_index :reports, :user_id add_index :reports, :issue_id + add_index :reports, :user_id 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_index :issue_comments, :user_id add_index :issue_comments, :issue_id + add_index :issue_comments, :user_id end def down diff --git a/db/structure.sql b/db/structure.sql index 077faffad..48fedeba5 100644 --- 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); -- --- 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); -- --- 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); @@ -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); -- --- 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); @@ -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 @@ -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 @@ -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 @@ -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); +-- +-- 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: - -- @@ -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); +-- +-- 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: - -- @@ -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 @@ -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 @@ -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 @@ -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 @@ -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 @@ -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 @@ -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