Context Navigation

indexes.sql

Last change on this file was 2853f2e, checked in by Stefan-Saveski <stefansaveski19@…>, 7 hours ago

Add performance analysis scripts and schema for educational database

Created performance analysis scripts for student dossier, subject pass rate, and top students per major.
Added indexes to improve query performance on key tables.
Generated bulk test data for performance analysis in the 'perf' schema.
Established the 'perf' schema with necessary tables and types for educational data management.

File size: 1.3 KB

Line
1	SET search_path TO perf;
2
3	-- Foreign keys with no supporting index. PostgreSQL indexes primary keys and
4	-- UNIQUE constraints automatically, but never the referencing side of a
5	-- foreign key, and a composite index only helps queries that use its
6	-- leading column.
7	CREATE INDEX IF NOT EXISTS ix_semesters_subjects_subject ON semesters_subjects (subjects_id);
8	CREATE INDEX IF NOT EXISTS ix_semesters_subjects_professor ON semesters_subjects (professor_id);
9	CREATE INDEX IF NOT EXISTS ix_enrolled_semesters_semester ON enrolled_semesters (semester_id);
10	CREATE INDEX IF NOT EXISTS ix_enrolled_semesters_major ON enrolled_semesters (major_id);
11	CREATE INDEX IF NOT EXISTS ix_payment_enrollment ON payment (enrollment_id);
12	CREATE INDEX IF NOT EXISTS ix_major_subjects_subject ON major_subjects (subject_id);
13	CREATE INDEX IF NOT EXISTS ix_professour_subjects_semester ON professour_subjects (active_semester_id, subject_id);
14	CREATE INDEX IF NOT EXISTS ix_user_documents_document ON user_documents (document_id);
15	CREATE INDEX IF NOT EXISTS ix_token_user ON token (user_id);
16
17	-- Supports the very common "this student's grades" path without touching the heap.
18	CREATE INDEX IF NOT EXISTS ix_passed_subjects_enrolled_grade ON passed_subjects (enrolled_id, grade);
19
20	ANALYZE;

Note: See TracBrowser for help on using the repository browser.