Changes between Version 10 and Version 11 of OtherDevelopments


Ignore:
Timestamp:
06/15/26 01:37:44 (32 hours ago)
Author:
221296
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • OtherDevelopments

    v10 v11  
    480480
    481481{{{
    482 Seq Scan on enrollment e
    483 Seq Scan on course c
    484 Hash Join
    485 Merge Left Join
    486 Sort
    487 HashAggregate
    488 
    489 Planning Time: 5.537 ms
    490 Execution Time: 87.215 ms
     482GroupAggregate  (cost=4486.03..5392.22 rows=5002 width=184) (actual time=52.268..79.158 rows=13 loops=1)
     483  Group Key: (EXTRACT(year FROM e.enroll_date)), (EXTRACT(month FROM e.enroll_date)), mps.final_month_success
     484  CTE student_course_month_performance
     485    ->  HashAggregate  (cost=986.47..1386.47 rows=20000 width=104) (actual time=38.750..38.848 rows=13 loops=1)
     486          Group Key: EXTRACT(year FROM qa.attempt_date), EXTRACT(month FROM qa.attempt_date), c_1.course_id, qa.user_id
     487          Batches: 1  Memory Usage: 793kB
     488          ->  Hash Join  (cost=95.12..736.47 rows=20000 width=76) (actual time=0.233..29.694 rows=20000 loops=1)
     489                Hash Cond: (m.course_id = c_1.course_id)
     490                ->  Hash Join  (cost=81.07..568.81 rows=20000 width=16) (actual time=0.183..17.984 rows=20000 loops=1)
     491                      Hash Cond: (l.module_id = m.module_id)
     492                      ->  Hash Join  (cost=66.35..500.53 rows=20000 width=16) (actual time=0.128..13.219 rows=20000 loops=1)
     493                            Hash Cond: (q.lesson_id = l.lesson_id)
     494                            ->  Hash Join  (cost=51.62..432.25 rows=20000 width=16) (actual time=0.074..8.352 rows=20000 loops=1)
     495                                  Hash Cond: (qa.quiz_id = q.quiz_id)
     496                                  ->  Seq Scan on quiz_attempt qa  (cost=0.00..328.00 rows=20000 width=16) (actual time=0.013..2.193 rows=20000 loops=1)
     497                                  ->  Hash  (cost=28.50..28.50 rows=1850 width=8) (actual time=0.028..0.029 rows=18 loops=1)
     498                                        Buckets: 2048  Batches: 1  Memory Usage: 17kB
     499                                        ->  Seq Scan on quiz q  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.018..0.020 rows=18 loops=1)
     500                            ->  Hash  (cost=12.10..12.10 rows=210 width=8) (actual time=0.028..0.028 rows=18 loops=1)
     501                                  Buckets: 1024  Batches: 1  Memory Usage: 9kB
     502                                  ->  Seq Scan on lesson l  (cost=0.00..12.10 rows=210 width=8) (actual time=0.011..0.014 rows=18 loops=1)
     503                      ->  Hash  (cost=12.10..12.10 rows=210 width=8) (actual time=0.028..0.028 rows=10 loops=1)
     504                            Buckets: 1024  Batches: 1  Memory Usage: 9kB
     505                            ->  Seq Scan on module m  (cost=0.00..12.10 rows=210 width=8) (actual time=0.019..0.021 rows=10 loops=1)
     506                ->  Hash  (cost=11.80..11.80 rows=180 width=4) (actual time=0.027..0.028 rows=5 loops=1)
     507                      Buckets: 1024  Batches: 1  Memory Usage: 9kB
     508                      ->  Seq Scan on course c_1  (cost=0.00..11.80 rows=180 width=4) (actual time=0.013..0.014 rows=5 loops=1)
     509  CTE course_month_performance
     510    ->  HashAggregate  (cost=600.00..630.00 rows=2000 width=100) (actual time=38.873..38.896 rows=13 loops=1)
     511          Group Key: student_course_month_performance.year, student_course_month_performance.month, student_course_month_performance.course_id
     512          Batches: 1  Memory Usage: 121kB
     513          ->  CTE Scan on student_course_month_performance  (cost=0.00..400.00 rows=20000 width=100) (actual time=38.759..38.858 rows=13 loops=1)
     514  ->  Incremental Sort  (cost=2469.56..3138.16 rows=5002 width=179) (actual time=49.907..76.256 rows=5002 loops=1)
     515        Sort Key: (EXTRACT(year FROM e.enroll_date)), (EXTRACT(month FROM e.enroll_date)), mps.final_month_success, e.enrollment_id
     516        Presorted Key: (EXTRACT(year FROM e.enroll_date)), (EXTRACT(month FROM e.enroll_date))
     517        Full-sort Groups: 13  Sort Method: quicksort  Average Memory: 28kB  Peak Memory: 28kB
     518        Pre-sorted Groups: 13  Sort Method: quicksort  Average Memory: 35kB  Peak Memory: 35kB
     519        ->  Merge Left Join  (cost=2467.91..2973.88 rows=5002 width=179) (actual time=48.700..61.930 rows=5002 loops=1)
     520              Merge Cond: ((EXTRACT(year FROM e.enroll_date) = cmp.year) AND (EXTRACT(month FROM e.enroll_date) = cmp.month))
     521              Join Filter: (cmp.course_id = e.course_id)
     522              Rows Removed by Join Filter: 5002
     523              ->  Merge Left Join  (cost=2318.26..2721.07 rows=5002 width=91) (actual time=48.654..55.635 rows=5002 loops=1)
     524                    Merge Cond: ((EXTRACT(year FROM e.enroll_date) = scmp.year) AND (EXTRACT(month FROM e.enroll_date) = scmp.month))
     525                    Join Filter: ((scmp.course_id = e.course_id) AND (scmp.user_id = e.user_id))
     526                    Rows Removed by Join Filter: 5002
     527                    ->  Merge Left Join  (cost=489.49..554.76 rows=5002 width=63) (actual time=48.610..50.944 rows=5002 loops=1)
     528                          Merge Cond: (((EXTRACT(year FROM e.enroll_date)) = mps.year) AND ((EXTRACT(month FROM e.enroll_date)) = mps.month))
     529                          ->  Sort  (cost=421.84..434.35 rows=5002 width=31) (actual time=9.656..10.054 rows=5002 loops=1)
     530                                Sort Key: (EXTRACT(year FROM e.enroll_date)), (EXTRACT(month FROM e.enroll_date))
     531                                Sort Method: quicksort  Memory: 505kB
     532                                ->  Hash Join  (cost=14.05..114.51 rows=5002 width=31) (actual time=0.084..3.772 rows=5002 loops=1)
     533                                      Hash Cond: (e.course_id = c.course_id)
     534                                      ->  Seq Scan on enrollment e  (cost=0.00..87.02 rows=5002 width=27) (actual time=0.015..0.505 rows=5002 loops=1)
     535                                      ->  Hash  (cost=11.80..11.80 rows=180 width=8) (actual time=0.033..0.033 rows=5 loops=1)
     536                                            Buckets: 1024  Batches: 1  Memory Usage: 9kB
     537                                            ->  Seq Scan on course c  (cost=0.00..11.80 rows=180 width=8) (actual time=0.017..0.019 rows=5 loops=1)
     538                          ->  Sort  (cost=67.64..68.14 rows=200 width=96) (actual time=38.949..38.952 rows=13 loops=1)
     539                                Sort Key: mps.year, mps.month
     540                                Sort Method: quicksort  Memory: 25kB
     541                                ->  Subquery Scan on mps  (cost=55.00..60.00 rows=200 width=96) (actual time=38.921..38.932 rows=13 loops=1)
     542                                      ->  HashAggregate  (cost=55.00..58.00 rows=200 width=96) (actual time=38.921..38.929 rows=13 loops=1)
     543                                            Group Key: course_month_performance.year, course_month_performance.month
     544                                            Batches: 1  Memory Usage: 40kB
     545                                            ->  CTE Scan on course_month_performance  (cost=0.00..40.00 rows=2000 width=96) (actual time=38.875..38.903 rows=13 loops=1)
     546                    ->  Sort  (cost=1828.77..1878.77 rows=20000 width=104) (actual time=0.038..0.328 rows=4794 loops=1)
     547                          Sort Key: scmp.year, scmp.month
     548                          Sort Method: quicksort  Memory: 25kB
     549                          ->  CTE Scan on student_course_month_performance scmp  (cost=0.00..400.00 rows=20000 width=104) (actual time=0.002..0.004 rows=13 loops=1)
     550              ->  Sort  (cost=149.66..154.66 rows=2000 width=100) (actual time=0.040..0.330 rows=4794 loops=1)
     551                    Sort Key: cmp.year, cmp.month
     552                    Sort Method: quicksort  Memory: 25kB
     553                    ->  CTE Scan on course_month_performance cmp  (cost=0.00..40.00 rows=2000 width=100) (actual time=0.001..0.003 rows=13 loops=1)
     554Planning Time: 3.274 ms
     555Execution Time: 80.672 ms
    491556}}}
    492557
    … …  
    494559
    495560{{{
    496 Seq Scan on enrollment e
    497 Seq Scan on course c
    498 Hash Join
    499 Merge Left Join
    500 Sort
    501 HashAggregate
    502 
    503 Planning Time: 6.799 ms
    504 Execution Time: 111.731 ms
    505 }}}
    506 
    507 # 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.
    508 
    509 **Overall Conclusion**
    510 
    511 Both analytical queries were tested using PostgreSQL EXPLAIN ANALYZE before and after index creation.
    512 
    513 Although indexes were added on the main JOIN and date columns, PostgreSQL continued to choose Sequential Scan operations for the largest tables.
    514 
    515 This behavior is expected for analytical reports that process a large portion of the available data through JOIN, GROUP BY, COUNT and AVG operations.
    516 
    517 The analysis demonstrates how PostgreSQL selects the most cost-effective execution plan and confirms that indexes do not necessarily improve every type of query.
     561GroupAggregate  (cost=4486.03..5392.22 rows=5002 width=184) (actual time=51.214..77.962 rows=13 loops=1)
     562  Group Key: (EXTRACT(year FROM e.enroll_date)), (EXTRACT(month FROM e.enroll_date)), mps.final_month_success
     563  CTE student_course_month_performance
     564    ->  HashAggregate  (cost=986.47..1386.47 rows=20000 width=104) (actual time=38.401..38.500 rows=13 loops=1)
     565          Group Key: EXTRACT(year FROM qa.attempt_date), EXTRACT(month FROM qa.attempt_date), c_1.course_id, qa.user_id
     566          Batches: 1  Memory Usage: 793kB
     567          ->  Hash Join  (cost=95.12..736.47 rows=20000 width=76) (actual time=0.105..29.696 rows=20000 loops=1)
     568                Hash Cond: (m.course_id = c_1.course_id)
     569                ->  Hash Join  (cost=81.07..568.81 rows=20000 width=16) (actual time=0.079..17.990 rows=20000 loops=1)
     570                      Hash Cond: (l.module_id = m.module_id)
     571                      ->  Hash Join  (cost=66.35..500.53 rows=20000 width=16) (actual time=0.058..13.125 rows=20000 loops=1)
     572                            Hash Cond: (q.lesson_id = l.lesson_id)
     573                            ->  Hash Join  (cost=51.62..432.25 rows=20000 width=16) (actual time=0.031..8.272 rows=20000 loops=1)
     574                                  Hash Cond: (qa.quiz_id = q.quiz_id)
     575                                  ->  Seq Scan on quiz_attempt qa  (cost=0.00..328.00 rows=20000 width=16) (actual time=0.007..1.975 rows=20000 loops=1)
     576                                  ->  Hash  (cost=28.50..28.50 rows=1850 width=8) (actual time=0.018..0.019 rows=18 loops=1)
     577                                        Buckets: 2048  Batches: 1  Memory Usage: 17kB
     578                                        ->  Seq Scan on quiz q  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.007..0.010 rows=18 loops=1)
     579                            ->  Hash  (cost=12.10..12.10 rows=210 width=8) (actual time=0.018..0.018 rows=18 loops=1)
     580                                  Buckets: 1024  Batches: 1  Memory Usage: 9kB
     581                                  ->  Seq Scan on lesson l  (cost=0.00..12.10 rows=210 width=8) (actual time=0.007..0.009 rows=18 loops=1)
     582                      ->  Hash  (cost=12.10..12.10 rows=210 width=8) (actual time=0.012..0.012 rows=10 loops=1)
     583                            Buckets: 1024  Batches: 1  Memory Usage: 9kB
     584                            ->  Seq Scan on module m  (cost=0.00..12.10 rows=210 width=8) (actual time=0.008..0.009 rows=10 loops=1)
     585                ->  Hash  (cost=11.80..11.80 rows=180 width=4) (actual time=0.012..0.012 rows=5 loops=1)
     586                      Buckets: 1024  Batches: 1  Memory Usage: 9kB
     587                      ->  Seq Scan on course c_1  (cost=0.00..11.80 rows=180 width=4) (actual time=0.009..0.010 rows=5 loops=1)
     588  CTE course_month_performance
     589    ->  HashAggregate  (cost=600.00..630.00 rows=2000 width=100) (actual time=38.529..38.551 rows=13 loops=1)
     590          Group Key: student_course_month_performance.year, student_course_month_performance.month, student_course_month_performance.course_id
     591          Batches: 1  Memory Usage: 121kB
     592          ->  CTE Scan on student_course_month_performance  (cost=0.00..400.00 rows=20000 width=100) (actual time=38.403..38.505 rows=13 loops=1)
     593  ->  Incremental Sort  (cost=2469.56..3138.16 rows=5002 width=179) (actual time=48.839..75.086 rows=5002 loops=1)
     594        Sort Key: (EXTRACT(year FROM e.enroll_date)), (EXTRACT(month FROM e.enroll_date)), mps.final_month_success, e.enrollment_id
     595        Presorted Key: (EXTRACT(year FROM e.enroll_date)), (EXTRACT(month FROM e.enroll_date))
     596        Full-sort Groups: 13  Sort Method: quicksort  Average Memory: 28kB  Peak Memory: 28kB
     597        Pre-sorted Groups: 13  Sort Method: quicksort  Average Memory: 35kB  Peak Memory: 35kB
     598        ->  Merge Left Join  (cost=2467.91..2973.88 rows=5002 width=179) (actual time=47.704..60.866 rows=5002 loops=1)
     599              Merge Cond: ((EXTRACT(year FROM e.enroll_date) = cmp.year) AND (EXTRACT(month FROM e.enroll_date) = cmp.month))
     600              Join Filter: (cmp.course_id = e.course_id)
     601              Rows Removed by Join Filter: 5002
     602              ->  Merge Left Join  (cost=2318.26..2721.07 rows=5002 width=91) (actual time=47.674..54.628 rows=5002 loops=1)
     603                    Merge Cond: ((EXTRACT(year FROM e.enroll_date) = scmp.year) AND (EXTRACT(month FROM e.enroll_date) = scmp.month))
     604                    Join Filter: ((scmp.course_id = e.course_id) AND (scmp.user_id = e.user_id))
     605                    Rows Removed by Join Filter: 5002
     606                    ->  Merge Left Join  (cost=489.49..554.76 rows=5002 width=63) (actual time=47.638..49.989 rows=5002 loops=1)
     607                          Merge Cond: (((EXTRACT(year FROM e.enroll_date)) = mps.year) AND ((EXTRACT(month FROM e.enroll_date)) = mps.month))
     608                          ->  Sort  (cost=421.84..434.35 rows=5002 width=31) (actual time=9.036..9.445 rows=5002 loops=1)
     609                                Sort Key: (EXTRACT(year FROM e.enroll_date)), (EXTRACT(month FROM e.enroll_date))
     610                                Sort Method: quicksort  Memory: 505kB
     611                                ->  Hash Join  (cost=14.05..114.51 rows=5002 width=31) (actual time=0.045..3.475 rows=5002 loops=1)
     612                                      Hash Cond: (e.course_id = c.course_id)
     613                                      ->  Seq Scan on enrollment e  (cost=0.00..87.02 rows=5002 width=27) (actual time=0.013..0.440 rows=5002 loops=1)
     614                                      ->  Hash  (cost=11.80..11.80 rows=180 width=8) (actual time=0.016..0.016 rows=5 loops=1)
     615                                            Buckets: 1024  Batches: 1  Memory Usage: 9kB
     616                                            ->  Seq Scan on course c  (cost=0.00..11.80 rows=180 width=8) (actual time=0.010..0.011 rows=5 loops=1)
     617                          ->  Sort  (cost=67.64..68.14 rows=200 width=96) (actual time=38.598..38.601 rows=13 loops=1)
     618                                Sort Key: mps.year, mps.month
     619                                Sort Method: quicksort  Memory: 25kB
     620                                ->  Subquery Scan on mps  (cost=55.00..60.00 rows=200 width=96) (actual time=38.569..38.580 rows=13 loops=1)
     621                                      ->  HashAggregate  (cost=55.00..58.00 rows=200 width=96) (actual time=38.568..38.577 rows=13 loops=1)
     622                                            Group Key: course_month_performance.year, course_month_performance.month
     623                                            Batches: 1  Memory Usage: 40kB
     624                                            ->  CTE Scan on course_month_performance  (cost=0.00..40.00 rows=2000 width=96) (actual time=38.531..38.558 rows=13 loops=1)
     625                    ->  Sort  (cost=1828.77..1878.77 rows=20000 width=104) (actual time=0.030..0.352 rows=4794 loops=1)
     626                          Sort Key: scmp.year, scmp.month
     627                          Sort Method: quicksort  Memory: 25kB
     628                          ->  CTE Scan on student_course_month_performance scmp  (cost=0.00..400.00 rows=20000 width=104) (actual time=0.002..0.004 rows=13 loops=1)
     629              ->  Sort  (cost=149.66..154.66 rows=2000 width=100) (actual time=0.025..0.333 rows=4794 loops=1)
     630                    Sort Key: cmp.year, cmp.month
     631                    Sort Method: quicksort  Memory: 25kB
     632                    ->  CTE Scan on course_month_performance cmp  (cost=0.00..40.00 rows=2000 width=100) (actual time=0.001..0.003 rows=13 loops=1)
     633Planning Time: 1.730 ms
     634Execution Time: 78.569 ms
     635}}}
     636
     637# # After adding indexes, the execution time decreased from 80.672 ms to 78.569 ms. The improvement is smaller compared to Scenario 1, because this query performs monthly aggregation and still processes a large part of the dataset. However, the indexes slightly reduced the execution time and planning time.
    518638
    519639= Security and Protection Measures =