Changes between Version 7 and Version 8 of OtherDevelopments


Ignore:
Timestamp:
06/15/26 00:46:32 (33 hours ago)
Author:
221296
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • OtherDevelopments

    v7 v8  
    153153
    154154{{{
    155 Seq Scan on quiz_attempt qa_1
    156 Seq Scan on quiz q_1
    157 Seq Scan on lesson l_1
    158 Seq Scan on module m_1
    159 Seq Scan on course c_1
    160 
    161 Planning Time: 3.565 ms
    162 Execution Time: 202.790 ms
     155GroupAggregate  (cost=80709.32..144979.11 rows=73000 width=176) (actual time=164.281..170.938 rows=2 loops=1)
     156  Group Key: (EXTRACT(year FROM e.enroll_date)), ycs.final_year_success
     157  ->  Incremental Sort  (cost=80709.32..128051.98 rows=555805 width=103) (actual time=148.388..157.374 rows=20006 loops=1)
     158        Sort Key: (EXTRACT(year FROM e.enroll_date)), ycs.final_year_success, c.instructor_id
     159        Presorted Key: (EXTRACT(year FROM e.enroll_date))
     160        Full-sort Groups: 2  Sort Method: quicksort  Average Memory: 29kB  Peak Memory: 29kB
     161        Pre-sorted Groups: 2  Sort Method: quicksort  Average Memory: 1054kB  Peak Memory: 1054kB
     162        ->  Merge Left Join  (cost=80598.37..91715.97 rows=555805 width=103) (actual time=137.946..145.502 rows=20006 loops=1)
     163              Merge Cond: ((EXTRACT(year FROM e.enroll_date)) = ycs.year)
     164              ->  Sort  (cost=79099.25..80488.76 rows=555805 width=43) (actual time=108.940..110.102 rows=20006 loops=1)
     165                    Sort Key: (EXTRACT(year FROM e.enroll_date))
     166                    Sort Method: quicksort  Memory: 2019kB
     167                    ->  Hash Right Join  (cost=1576.68..8962.73 rows=555805 width=43) (actual time=91.417..97.214 rows=20006 loops=1)
     168                          Hash Cond: ((qa.quiz_id = q.quiz_id) AND (qa.user_id = e.user_id))
     169                          ->  Seq Scan on quiz_attempt qa  (cost=0.00..328.00 rows=20000 width=16) (actual time=0.016..1.444 rows=20000 loops=1)
     170                          ->  Hash  (cost=805.50..805.50 rows=51412 width=39) (actual time=86.618..86.623 rows=20006 loops=1)
     171                                Buckets: 65536  Batches: 1  Memory Usage: 1841kB
     172                                ->  Hash Right Join  (cost=235.03..805.50 rows=51412 width=39) (actual time=76.887..80.470 rows=20006 loops=1)
     173                                      Hash Cond: (m.course_id = c.course_id)
     174                                      ->  Hash Right Join  (cost=29.45..67.86 rows=1850 width=8) (actual time=0.114..0.139 rows=18 loops=1)
     175                                            Hash Cond: (l.module_id = m.module_id)
     176                                            ->  Hash Right Join  (cost=14.72..48.18 rows=1850 width=8) (actual time=0.054..0.070 rows=18 loops=1)
     177                                                  Hash Cond: (q.lesson_id = l.lesson_id)
     178                                                  ->  Seq Scan on quiz q  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.007..0.011 rows=18 loops=1)
     179                                                  ->  Hash  (cost=12.10..12.10 rows=210 width=8) (actual time=0.027..0.027 rows=18 loops=1)
     180                                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB
     181                                                        ->  Seq Scan on lesson l  (cost=0.00..12.10 rows=210 width=8) (actual time=0.015..0.017 rows=18 loops=1)
     182                                            ->  Hash  (cost=12.10..12.10 rows=210 width=8) (actual time=0.040..0.040 rows=10 loops=1)
     183                                                  Buckets: 1024  Batches: 1  Memory Usage: 9kB
     184                                                  ->  Seq Scan on module m  (cost=0.00..12.10 rows=210 width=8) (actual time=0.029..0.031 rows=10 loops=1)
     185                                      ->  Hash  (cost=143.05..143.05 rows=5002 width=39) (actual time=76.677..76.679 rows=5002 loops=1)
     186                                            Buckets: 8192  Batches: 1  Memory Usage: 397kB
     187                                            ->  Hash Left Join  (cost=29.45..143.05 rows=5002 width=39) (actual time=72.865..75.398 rows=5002 loops=1)
     188                                                  Hash Cond: (e.enrollment_id = cert.enrollment_id)
     189                                                  ->  Hash Join  (cost=14.05..114.51 rows=5002 width=35) (actual time=72.822..74.641 rows=5002 loops=1)
     190                                                        Hash Cond: (e.course_id = c.course_id)
     191                                                        ->  Seq Scan on enrollment e  (cost=0.00..87.02 rows=5002 width=31) (actual time=0.014..0.477 rows=5002 loops=1)
     192                                                        ->  Hash  (cost=11.80..11.80 rows=180 width=8) (actual time=72.758..72.758 rows=5 loops=1)
     193                                                              Buckets: 1024  Batches: 1  Memory Usage: 9kB
     194                                                              ->  Seq Scan on course c  (cost=0.00..11.80 rows=180 width=8) (actual time=72.722..72.724 rows=5 loops=1)
     195                                                  ->  Hash  (cost=12.40..12.40 rows=240 width=8) (actual time=0.026..0.026 rows=1 loops=1)
     196                                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB
     197                                                        ->  Seq Scan on certificate cert  (cost=0.00..12.40 rows=240 width=8) (actual time=0.019..0.019 rows=1 loops=1)
     198              ->  Sort  (cost=1499.12..1499.62 rows=200 width=64) (actual time=28.978..28.981 rows=2 loops=1)
     199                    Sort Key: ycs.year
     200                    Sort Method: quicksort  Memory: 25kB
     201                    ->  Subquery Scan on ycs  (cost=1486.47..1491.47 rows=200 width=64) (actual time=28.966..28.973 rows=2 loops=1)
     202                          ->  HashAggregate  (cost=1486.47..1489.47 rows=200 width=64) (actual time=28.960..28.966 rows=2 loops=1)
     203                                Group Key: (EXTRACT(year FROM qa_1.attempt_date))
     204                                Batches: 1  Memory Usage: 40kB
     205                                ->  HashAggregate  (cost=836.47..1186.47 rows=20000 width=68) (actual time=28.889..28.938 rows=2 loops=1)
     206                                      Group Key: EXTRACT(year FROM qa_1.attempt_date), c_1.course_id
     207                                      Batches: 1  Memory Usage: 793kB
     208                                      ->  Hash Join  (cost=95.12..686.47 rows=20000 width=40) (actual time=0.189..22.495 rows=20000 loops=1)
     209                                            Hash Cond: (m_1.course_id = c_1.course_id)
     210                                            ->  Hash Join  (cost=81.07..568.81 rows=20000 width=12) (actual time=0.142..15.695 rows=20000 loops=1)
     211                                                  Hash Cond: (l_1.module_id = m_1.module_id)
     212                                                  ->  Hash Join  (cost=66.35..500.53 rows=20000 width=12) (actual time=0.095..11.438 rows=20000 loops=1)
     213                                                        Hash Cond: (q_1.lesson_id = l_1.lesson_id)
     214                                                        ->  Hash Join  (cost=51.62..432.25 rows=20000 width=12) (actual time=0.053..7.286 rows=20000 loops=1)
     215                                                              Hash Cond: (qa_1.quiz_id = q_1.quiz_id)
     216                                                              ->  Seq Scan on quiz_attempt qa_1  (cost=0.00..328.00 rows=20000 width=12) (actual time=0.009..1.902 rows=20000 loops=1)
     217                                                              ->  Hash  (cost=28.50..28.50 rows=1850 width=8) (actual time=0.024..0.024 rows=18 loops=1)
     218                                                                    Buckets: 2048  Batches: 1  Memory Usage: 17kB
     219                                                                    ->  Seq Scan on quiz q_1  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.012..0.015 rows=18 loops=1)
     220                                                        ->  Hash  (cost=12.10..12.10 rows=210 width=8) (actual time=0.023..0.023 rows=18 loops=1)
     221                                                              Buckets: 1024  Batches: 1  Memory Usage: 9kB
     222                                                              ->  Seq Scan on lesson l_1  (cost=0.00..12.10 rows=210 width=8) (actual time=0.012..0.014 rows=18 loops=1)
     223                                                  ->  Hash  (cost=12.10..12.10 rows=210 width=8) (actual time=0.028..0.029 rows=10 loops=1)
     224                                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB
     225                                                        ->  Seq Scan on module m_1  (cost=0.00..12.10 rows=210 width=8) (actual time=0.018..0.020 rows=10 loops=1)
     226                                            ->  Hash  (cost=11.80..11.80 rows=180 width=4) (actual time=0.025..0.026 rows=5 loops=1)
     227                                                  Buckets: 1024  Batches: 1  Memory Usage: 9kB
     228                                                  ->  Seq Scan on course c_1  (cost=0.00..11.80 rows=180 width=4) (actual time=0.017..0.018 rows=5 loops=1)
     229Planning Time: 2.538 ms
     230JIT:
     231  Functions: 112
     232  Options: Inlining false, Optimization false, Expressions true, Deforming true
     233  Timing: Generation 8.302 ms (Deform 4.108 ms), Inlining 0.000 ms, Optimization 3.069 ms, Emission 69.974 ms, Total 81.346 ms
     234Execution Time: 944.483 ms
    163235}}}
    164236
    … …  
    168240
    169241{{{
    170 Seq Scan on quiz_attempt qa_1
    171 Seq Scan on quiz q_1
    172 Seq Scan on lesson l_1
    173 Seq Scan on module m_1
    174 Seq Scan on course c_1
    175 
    176 Planning Time: 8.008 ms
    177 Execution Time: 218.476 ms
    178 }}}
    179 
    180 # 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.
     242GroupAggregate  (cost=80709.32..144979.11 rows=73000 width=176) (actual time=157.669..164.135 rows=2 loops=1)
     243  Group Key: (EXTRACT(year FROM e.enroll_date)), ycs.final_year_success
     244  ->  Incremental Sort  (cost=80709.32..128051.98 rows=555805 width=103) (actual time=141.941..150.977 rows=20006 loops=1)
     245        Sort Key: (EXTRACT(year FROM e.enroll_date)), ycs.final_year_success, c.instructor_id
     246        Presorted Key: (EXTRACT(year FROM e.enroll_date))
     247        Full-sort Groups: 2  Sort Method: quicksort  Average Memory: 29kB  Peak Memory: 29kB
     248        Pre-sorted Groups: 2  Sort Method: quicksort  Average Memory: 1054kB  Peak Memory: 1054kB
     249        ->  Merge Left Join  (cost=80598.37..91715.97 rows=555805 width=103) (actual time=131.481..139.062 rows=20006 loops=1)
     250              Merge Cond: ((EXTRACT(year FROM e.enroll_date)) = ycs.year)
     251              ->  Sort  (cost=79099.25..80488.76 rows=555805 width=43) (actual time=104.761..105.930 rows=20006 loops=1)
     252                    Sort Key: (EXTRACT(year FROM e.enroll_date))
     253                    Sort Method: quicksort  Memory: 2019kB
     254                    ->  Hash Right Join  (cost=1576.68..8962.73 rows=555805 width=43) (actual time=87.109..92.938 rows=20006 loops=1)
     255                          Hash Cond: ((qa.quiz_id = q.quiz_id) AND (qa.user_id = e.user_id))
     256                          ->  Seq Scan on quiz_attempt qa  (cost=0.00..328.00 rows=20000 width=16) (actual time=0.012..1.530 rows=20000 loops=1)
     257                          ->  Hash  (cost=805.50..805.50 rows=51412 width=39) (actual time=82.485..82.490 rows=20006 loops=1)
     258                                Buckets: 65536  Batches: 1  Memory Usage: 1841kB
     259                                ->  Hash Right Join  (cost=235.03..805.50 rows=51412 width=39) (actual time=72.927..76.507 rows=20006 loops=1)
     260                                      Hash Cond: (m.course_id = c.course_id)
     261                                      ->  Hash Right Join  (cost=29.45..67.86 rows=1850 width=8) (actual time=0.106..0.131 rows=18 loops=1)
     262                                            Hash Cond: (l.module_id = m.module_id)
     263                                            ->  Hash Right Join  (cost=14.72..48.18 rows=1850 width=8) (actual time=0.053..0.069 rows=18 loops=1)
     264                                                  Hash Cond: (q.lesson_id = l.lesson_id)
     265                                                  ->  Seq Scan on quiz q  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.006..0.010 rows=18 loops=1)
     266                                                  ->  Hash  (cost=12.10..12.10 rows=210 width=8) (actual time=0.025..0.026 rows=18 loops=1)
     267                                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB
     268                                                        ->  Seq Scan on lesson l  (cost=0.00..12.10 rows=210 width=8) (actual time=0.014..0.016 rows=18 loops=1)
     269                                            ->  Hash  (cost=12.10..12.10 rows=210 width=8) (actual time=0.034..0.034 rows=10 loops=1)
     270                                                  Buckets: 1024  Batches: 1  Memory Usage: 9kB
     271                                                  ->  Seq Scan on module m  (cost=0.00..12.10 rows=210 width=8) (actual time=0.023..0.025 rows=10 loops=1)
     272                                      ->  Hash  (cost=143.05..143.05 rows=5002 width=39) (actual time=72.755..72.757 rows=5002 loops=1)
     273                                            Buckets: 8192  Batches: 1  Memory Usage: 397kB
     274                                            ->  Hash Left Join  (cost=29.45..143.05 rows=5002 width=39) (actual time=68.918..71.468 rows=5002 loops=1)
     275                                                  Hash Cond: (e.enrollment_id = cert.enrollment_id)
     276                                                  ->  Hash Join  (cost=14.05..114.51 rows=5002 width=35) (actual time=68.863..70.694 rows=5002 loops=1)
     277                                                        Hash Cond: (e.course_id = c.course_id)
     278                                                        ->  Seq Scan on enrollment e  (cost=0.00..87.02 rows=5002 width=31) (actual time=0.013..0.508 rows=5002 loops=1)
     279                                                        ->  Hash  (cost=11.80..11.80 rows=180 width=8) (actual time=68.810..68.810 rows=5 loops=1)
     280                                                              Buckets: 1024  Batches: 1  Memory Usage: 9kB
     281                                                              ->  Seq Scan on course c  (cost=0.00..11.80 rows=180 width=8) (actual time=68.775..68.777 rows=5 loops=1)
     282                                                  ->  Hash  (cost=12.40..12.40 rows=240 width=8) (actual time=0.037..0.038 rows=1 loops=1)
     283                                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB
     284                                                        ->  Seq Scan on certificate cert  (cost=0.00..12.40 rows=240 width=8) (actual time=0.030..0.031 rows=1 loops=1)
     285              ->  Sort  (cost=1499.12..1499.62 rows=200 width=64) (actual time=26.690..26.694 rows=2 loops=1)
     286                    Sort Key: ycs.year
     287                    Sort Method: quicksort  Memory: 25kB
     288                    ->  Subquery Scan on ycs  (cost=1486.47..1491.47 rows=200 width=64) (actual time=26.678..26.684 rows=2 loops=1)
     289                          ->  HashAggregate  (cost=1486.47..1489.47 rows=200 width=64) (actual time=26.673..26.678 rows=2 loops=1)
     290                                Group Key: (EXTRACT(year FROM qa_1.attempt_date))
     291                                Batches: 1  Memory Usage: 40kB
     292                                ->  HashAggregate  (cost=836.47..1186.47 rows=20000 width=68) (actual time=26.606..26.652 rows=2 loops=1)
     293                                      Group Key: EXTRACT(year FROM qa_1.attempt_date), c_1.course_id
     294                                      Batches: 1  Memory Usage: 793kB
     295                                      ->  Hash Join  (cost=95.12..686.47 rows=20000 width=40) (actual time=0.181..20.697 rows=20000 loops=1)
     296                                            Hash Cond: (m_1.course_id = c_1.course_id)
     297                                            ->  Hash Join  (cost=81.07..568.81 rows=20000 width=12) (actual time=0.135..14.468 rows=20000 loops=1)
     298                                                  Hash Cond: (l_1.module_id = m_1.module_id)
     299                                                  ->  Hash Join  (cost=66.35..500.53 rows=20000 width=12) (actual time=0.094..10.598 rows=20000 loops=1)
     300                                                        Hash Cond: (q_1.lesson_id = l_1.lesson_id)
     301                                                        ->  Hash Join  (cost=51.62..432.25 rows=20000 width=12) (actual time=0.052..6.728 rows=20000 loops=1)
     302                                                              Hash Cond: (qa_1.quiz_id = q_1.quiz_id)
     303                                                              ->  Seq Scan on quiz_attempt qa_1  (cost=0.00..328.00 rows=20000 width=12) (actual time=0.005..1.791 rows=20000 loops=1)
     304                                                              ->  Hash  (cost=28.50..28.50 rows=1850 width=8) (actual time=0.024..0.025 rows=18 loops=1)
     305                                                                    Buckets: 2048  Batches: 1  Memory Usage: 17kB
     306                                                                    ->  Seq Scan on quiz q_1  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.013..0.015 rows=18 loops=1)
     307                                                        ->  Hash  (cost=12.10..12.10 rows=210 width=8) (actual time=0.023..0.023 rows=18 loops=1)
     308                                                              Buckets: 1024  Batches: 1  Memory Usage: 9kB
     309                                                              ->  Seq Scan on lesson l_1  (cost=0.00..12.10 rows=210 width=8) (actual time=0.012..0.014 rows=18 loops=1)
     310                                                  ->  Hash  (cost=12.10..12.10 rows=210 width=8) (actual time=0.023..0.023 rows=10 loops=1)
     311                                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB
     312                                                        ->  Seq Scan on module m_1  (cost=0.00..12.10 rows=210 width=8) (actual time=0.013..0.015 rows=10 loops=1)
     313                                            ->  Hash  (cost=11.80..11.80 rows=180 width=4) (actual time=0.026..0.026 rows=5 loops=1)
     314                                                  Buckets: 1024  Batches: 1  Memory Usage: 9kB
     315                                                  ->  Seq Scan on course c_1  (cost=0.00..11.80 rows=180 width=4) (actual time=0.017..0.018 rows=5 loops=1)
     316Planning Time: 1.811 ms
     317JIT:
     318  Functions: 112
     319  Options: Inlining false, Optimization false, Expressions true, Deforming true
     320  Timing: Generation 5.875 ms (Deform 2.882 ms), Inlining 0.000 ms, Optimization 2.428 ms, Emission 66.682 ms, Total 74.985 ms
     321Execution Time: 171.114 ms
     322}}}
     323
     324# After adding indexes, the execution time decreased from 944.483 ms to 171.114 ms. This shows that the added indexes significantly improved the execution of the yearly analysis query. The improvement is especially visible because the query joins multiple tables and processes 20000 quiz_attempt records.
    181325
    182326=== Scenario 2: Monthly Analysis of Course Activity, Completions, Instructor Involvement and Student Success ===