Changes between Version 3 and Version 4 of OtherDevelopments

Timestamp:

06/04/26 22:05:07 (11 days ago)

Author:

221296

Comment:

--

OtherDevelopments

@@ -90 +90 @@
 The index on enroll_date improves the yearly grouping operation because the report groups data by year.
 
+Result without indexes:
+
+{{{
+Seq Scan on quiz_attempt qa_1
+Seq Scan on quiz q_1
+Seq Scan on lesson l_1
+Seq Scan on module m_1
+Seq Scan on course c_1
+
+Planning Time: 3.565 ms
+Execution Time: 202.790 ms
+}}}
+
+# Before adding indexes, PostgreSQL used sequential scans on the main joined tables, especially on quiz_attempt, which contains 20000 test records. The query execution time was 202.790 ms.
+
+Result with indexes:
+
+{{{
+Seq Scan on quiz_attempt qa_1
+Seq Scan on quiz q_1
+Seq Scan on lesson l_1
+Seq Scan on module m_1
+Seq Scan on course c_1
+
+Planning Time: 8.008 ms
+Execution Time: 218.476 ms
+}}}
+
+# After adding indexes, PostgreSQL still selected sequential scans for the main tables. This happened because the query aggregates almost all quiz_attempt records, so reading the whole table was cheaper than using indexes. The execution time increased slightly from 202.790 ms to 218.476 ms; these indexes did not improve this specific analytical query.
+
 === Scenario 2: Monthly Analysis of Course Activity, Completions, Instructor Involvement and Student Success ===
 
@@ -160 +190 @@
 
 These indexes reduce the amount of data PostgreSQL needs to scan when generating monthly reports.
+
+Result without indexes:
+
+{{{
+Seq Scan on enrollment e
+Seq Scan on course c
+Hash Join
+Merge Left Join
+Sort
+HashAggregate
+
+Planning Time: 5.537 ms
+Execution Time: 87.215 ms
+}}}
+
+Result њith indexes:
+
+{{{
+Seq Scan on enrollment e
+Seq Scan on course c
+Hash Join
+Merge Left Join
+Sort
+HashAggregate
+
+Planning Time: 6.799 ms
+Execution Time: 111.731 ms
+}}}
+
+# After adding indexes, PostgreSQL still selected sequential scans for the main tables. The query performs monthly aggregation over most of the enrollment and quiz_attempt records, so the planner estimated that sequential scanning was cheaper than using indexes. The execution time increased from 87.215 ms to 111.731 ms, therefore these indexes did not improve this specific analytical query.
 
 = Security and Protection Measures =