Changes between Version 17 and Version 18 of P9


Ignore:
Timestamp:
06/08/26 01:14:27 (12 days ago)
Author:
211099
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • P9

    v17 v18  
    77After adding the indexes, the same query is executed 10 times and the results are compared.
    88
    9 
    10 == Scenario 2 -  Quarterly story performance
     9== Scenario 1 -  Quarterly story performance
    1110
    1211=== Without index analysis
    … …  
    293292The indexes added for this query resulted in a dramatic improvement, reducing average execution time from 1,397.98 ms to 2.74 ms (~510x faster). After adding indexes on story_id columns across all joined tables, the planner switched from Seq Scans to Index Scans on every JOIN operation, visible in the query execution plan. The indexes are kept.
    294293
    295 == Scenario 3 — Quarterly writer performance report
     294== Scenario 2 — Quarterly writer performance report
    296295{{{
    297296EXPLAIN ANALYZE
    … …  
    464463Average time: 0.628 ms
    465464The indexes added for this query provided no meaningful improvement, with average execution time remaining virtually unchanged from 0.649 ms to 0.628 ms (~3%), which is within normal measurement variance. The indexes are not kept.
     465
     466
     467== Scenario 3 - Annual genre popularity and engagement trend
     468=== Without index analysis
     469{{{
     470EXPLAIN ANALYZE
     471WITH genre_annual AS (
     472    SELECT
     473        DATE_TRUNC('year', s.story_created_at) AS year,
     474        g.genre_id,
     475        g.genre_name,
     476        COUNT(DISTINCT s.story_id) AS total_stories,
     477        COUNT(DISTINCT w.user_id) AS total_writers,
     478        COALESCE(SUM(ch.view_count), 0) AS total_views,
     479        COALESCE(SUM(ch.word_count), 0) AS total_words,
     480        COUNT(DISTINCT l.user_id) AS total_likes,
     481        COUNT(DISTINCT c.comment_id) AS total_comments,
     482        ROUND(AVG(ch.rating), 2) AS avg_rating
     483    FROM genre g
     484    JOIN has_genre hg ON g.genre_id = hg.genre_id
     485    JOIN story s      ON hg.story_id = s.story_id
     486    JOIN writer w     ON s.user_id = w.user_id
     487    JOIN status st    ON s.story_id = st.story_id AND st.status = 'published'
     488    LEFT JOIN chapter ch ON s.story_id = ch.story_id
     489    LEFT JOIN likes l    ON s.story_id = l.story_id
     490    LEFT JOIN comment c  ON s.story_id = c.story_id
     491    GROUP BY DATE_TRUNC('year', s.story_created_at), g.genre_id, g.genre_name
     492),
     493with_metrics AS (
     494    SELECT *,
     495        ROUND((total_likes + total_comments)::DECIMAL / NULLIF(total_views, 0) * 100, 2) AS engagement_rate,
     496        ROUND(total_views::DECIMAL / NULLIF(total_stories, 0), 2) AS avg_views_per_story,
     497        LAG(total_views)   OVER (PARTITION BY genre_id ORDER BY year) AS prev_year_views,
     498        LAG(total_stories) OVER (PARTITION BY genre_id ORDER BY year) AS prev_year_stories
     499    FROM genre_annual
     500)
     501SELECT
     502    TO_CHAR(year, 'YYYY') AS year,
     503    genre_name,
     504    total_stories, total_writers, total_views, avg_views_per_story,
     505    total_likes, total_comments,
     506    COALESCE(avg_rating, 0)      AS avg_rating,
     507    COALESCE(engagement_rate, 0) AS engagement_rate,
     508    ROUND((total_views - prev_year_views)::DECIMAL   / NULLIF(prev_year_views, 0) * 100, 2) AS yoy_views_growth_pct,
     509    ROUND((total_stories - prev_year_stories)::DECIMAL / NULLIF(prev_year_stories, 0) * 100, 2) AS yoy_stories_growth_pct,
     510    RANK() OVER (PARTITION BY year ORDER BY total_views DESC)     AS popularity_rank,
     511    RANK() OVER (PARTITION BY year ORDER BY engagement_rate DESC) AS engagement_rank
     512FROM with_metrics
     513ORDER BY year DESC, popularity_rank;
     514}}}
     515{{{
     516|QUERY PLAN                                                                                                                                                                                                                                           |
     517|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
     518|Sort  (cost=190.58..191.10 rows=207 width=506) (actual time=0.963..0.969 rows=10 loops=1)                                                                                                                                                            |
     519|  Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?))                                                                                                                                                                       |
     520|  Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                                               |
     521|  ->  WindowAgg  (cost=158.05..182.62 rows=207 width=506) (actual time=0.919..0.942 rows=10 loops=1)                                                                                                                                                 |
     522|        ->  Incremental Sort  (cost=158.05..171.23 rows=207 width=386) (actual time=0.910..0.916 rows=10 loops=1)                                                                                                                                    |
     523|              Sort Key: with_metrics.year, with_metrics.total_views DESC                                                                                                                                                                             |
     524|              Presorted Key: with_metrics.year                                                                                                                                                                                                       |
     525|              Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB                                                                                                                                                   |
     526|              ->  WindowAgg  (cost=158.02..162.16 rows=207 width=386) (actual time=0.888..0.903 rows=10 loops=1)                                                                                                                                     |
     527|                    ->  Sort  (cost=158.02..158.54 rows=207 width=378) (actual time=0.885..0.890 rows=10 loops=1)                                                                                                                                    |
     528|                          Sort Key: with_metrics.year, with_metrics.engagement_rate DESC                                                                                                                                                             |
     529|                          Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                       |
     530|                          ->  Subquery Scan on with_metrics  (cost=137.12..150.06 rows=207 width=378) (actual time=0.849..0.879 rows=10 loops=1)                                                                                                     |
     531|                                ->  WindowAgg  (cost=137.12..147.99 rows=207 width=390) (actual time=0.848..0.876 rows=10 loops=1)                                                                                                                   |
     532|                                      ->  Sort  (cost=137.12..137.64 rows=207 width=302) (actual time=0.838..0.842 rows=10 loops=1)                                                                                                                  |
     533|                                            Sort Key: genre_annual.genre_id, genre_annual.year                                                                                                                                                       |
     534|                                            Sort Method: quicksort  Memory: 25kB                                                                                                                                                                     |
     535|                                            ->  Subquery Scan on genre_annual  (cost=118.81..129.16 rows=207 width=302) (actual time=0.644..0.833 rows=10 loops=1)                                                                                   |
     536|                                                  ->  GroupAggregate  (cost=118.81..127.09 rows=207 width=310) (actual time=0.644..0.830 rows=10 loops=1)                                                                                            |
     537|                                                        Group Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id                                                                                                                        |
     538|                                                        ->  Sort  (cost=118.81..119.33 rows=207 width=257) (actual time=0.579..0.603 rows=354 loops=1)                                                                                               |
     539|                                                              Sort Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id, s.story_id                                                                                                       |
     540|                                                              Sort Method: quicksort  Memory: 48kB                                                                                                                                                   |
     541|                                                              ->  Merge Left Join  (cost=1.15..110.85 rows=207 width=257) (actual time=0.093..0.405 rows=354 loops=1)                                                                                |
     542|                                                                    Merge Cond: (s.story_id = l.story_id)                                                                                                                                            |
     543|                                                                    ->  Merge Left Join  (cost=1.01..94.70 rows=57 width=253) (actual time=0.079..0.200 rows=78 loops=1)                                                                             |
     544|                                                                          Merge Cond: (s.story_id = c.story_id)                                                                                                                                      |
     545|                                                                          ->  Merge Left Join  (cost=0.88..81.48 rows=26 width=249) (actual time=0.070..0.162 rows=30 loops=1)                                                                       |
     546|                                                                                Merge Cond: (s.story_id = ch.story_id)                                                                                                                               |
     547|                                                                                ->  Nested Loop  (cost=0.74..67.76 rows=12 width=238) (actual time=0.055..0.125 rows=12 loops=1)                                                                     |
     548|                                                                                      ->  Nested Loop  (cost=0.57..48.82 rows=12 width=238) (actual time=0.046..0.104 rows=12 loops=1)                                                               |
     549|                                                                                            ->  Nested Loop  (cost=0.43..43.70 rows=12 width=230) (actual time=0.038..0.081 rows=12 loops=1)                                                         |
     550|                                                                                                  ->  Nested Loop  (cost=0.28..17.58 rows=12 width=12) (actual time=0.028..0.052 rows=12 loops=1)                                                    |
     551|                                                                                                        ->  Index Only Scan using has_genre_pk on has_genre hg  (cost=0.14..12.36 rows=15 width=8) (actual time=0.012..0.019 rows=15 loops=1)        |
     552|                                                                                                              Heap Fetches: 15                                                                                                                       |
     553|                                                                                                        ->  Memoize  (cost=0.14..0.96 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=15)                                                     |
     554|                                                                                                              Cache Key: hg.story_id                                                                                                                 |
     555|                                                                                                              Cache Mode: logical                                                                                                                    |
     556|                                                                                                              Hits: 10  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                     |
     557|                                                                                                              ->  Index Only Scan using idx_status_published on status st  (cost=0.13..0.95 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=5)|
     558|                                                                                                                    Index Cond: (story_id = hg.story_id)                                                                                             |
     559|                                                                                                                    Heap Fetches: 4                                                                                                                  |
     560|                                                                                                  ->  Memoize  (cost=0.16..3.11 rows=1 width=222) (actual time=0.002..0.002 rows=1 loops=12)                                                         |
     561|                                                                                                        Cache Key: hg.genre_id                                                                                                                       |
     562|                                                                                                        Cache Mode: logical                                                                                                                          |
     563|                                                                                                        Hits: 5  Misses: 7  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                            |
     564|                                                                                                        ->  Index Scan using genre_pkey on genre g  (cost=0.15..3.10 rows=1 width=222) (actual time=0.002..0.002 rows=1 loops=7)                     |
     565|                                                                                                              Index Cond: (genre_id = hg.genre_id)                                                                                                   |
     566|                                                                                            ->  Memoize  (cost=0.14..0.96 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=12)                                                                |
     567|                                                                                                  Cache Key: hg.story_id                                                                                                                             |
     568|                                                                                                  Cache Mode: logical                                                                                                                                |
     569|                                                                                                  Hits: 8  Misses: 4  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                                  |
     570|                                                                                                  ->  Index Scan using story_pkey on story s  (cost=0.13..0.95 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=4)                            |
     571|                                                                                                        Index Cond: (story_id = hg.story_id)                                                                                                         |
     572|                                                                                      ->  Memoize  (cost=0.17..4.98 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=12)                                                                       |
     573|                                                                                            Cache Key: s.user_id                                                                                                                                     |
     574|                                                                                            Cache Mode: logical                                                                                                                                      |
     575|                                                                                            Hits: 9  Misses: 3  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                                        |
     576|                                                                                            ->  Index Only Scan using writer_pkey on writer w  (cost=0.15..4.97 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=3)                            |
     577|                                                                                                  Index Cond: (user_id = s.user_id)                                                                                                                  |
     578|                                                                                                  Heap Fetches: 3                                                                                                                                    |
     579|                                                                                ->  Materialize  (cost=0.14..13.33 rows=11 width=15) (actual time=0.011..0.022 rows=31 loops=1)                                                                      |
     580|                                                                                      ->  Index Scan using idx_chapter_story_id on chapter ch  (cost=0.14..13.30 rows=11 width=15) (actual time=0.008..0.013 rows=11 loops=1)                        |
     581|                                                                          ->  Materialize  (cost=0.14..12.33 rows=11 width=8) (actual time=0.008..0.017 rows=79 loops=1)                                                                             |
     582|                                                                                ->  Index Scan using idx_comment_story_id on comment c  (cost=0.14..12.30 rows=11 width=8) (actual time=0.006..0.009 rows=11 loops=1)                                |
     583|                                                                    ->  Materialize  (cost=0.14..12.45 rows=18 width=8) (actual time=0.007..0.032 rows=355 loops=1)                                                                                  |
     584|                                                                          ->  Index Scan using idx_likes_story_id on likes l  (cost=0.14..12.41 rows=18 width=8) (actual time=0.006..0.010 rows=18 loops=1)                                          |
     585|Planning Time: 4.355 ms                                                                                                                                                                                                                              |
     586|Execution Time: 1.161 ms
     587}}}
     588Average time without indexes is: 1.179 ms
     589=== Indexes for this queries
     590{{{
     591CREATE INDEX idx_has_genre_genre_id    ON has_genre(genre_id);
     592CREATE INDEX idx_has_genre_story_id    ON has_genre(story_id);
     593CREATE INDEX idx_status_story_published ON status(story_id) WHERE status = 'published';
     594
     595ANALYZE genre;
     596ANALYZE has_genre;
     597ANALYZE status;
     598ANALYZE story;
     599ANALYZE chapter;
     600ANALYZE likes;
     601ANALYZE comment;
     602}}}
     603After analysis we get:
     604{{{
     605|QUERY PLAN                                                                                                                                                                                                                                           |
     606|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
     607|Sort  (cost=119.78..119.91 rows=50 width=298) (actual time=0.999..1.005 rows=10 loops=1)                                                                                                                                                             |
     608|  Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?))                                                                                                                                                                       |
     609|  Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                                               |
     610|  ->  WindowAgg  (cost=112.40..118.37 rows=50 width=298) (actual time=0.957..0.980 rows=10 loops=1)                                                                                                                                                  |
     611|        ->  Incremental Sort  (cost=112.40..115.62 rows=50 width=178) (actual time=0.949..0.954 rows=10 loops=1)                                                                                                                                     |
     612|              Sort Key: with_metrics.year, with_metrics.total_views DESC                                                                                                                                                                             |
     613|              Presorted Key: with_metrics.year                                                                                                                                                                                                       |
     614|              Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB                                                                                                                                                   |
     615|              ->  WindowAgg  (cost=112.37..113.37 rows=50 width=178) (actual time=0.926..0.942 rows=10 loops=1)                                                                                                                                      |
     616|                    ->  Sort  (cost=112.37..112.49 rows=50 width=170) (actual time=0.923..0.928 rows=10 loops=1)                                                                                                                                     |
     617|                          Sort Key: with_metrics.year, with_metrics.engagement_rate DESC                                                                                                                                                             |
     618|                          Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                       |
     619|                          ->  Subquery Scan on with_metrics  (cost=107.83..110.96 rows=50 width=170) (actual time=0.888..0.917 rows=10 loops=1)                                                                                                      |
     620|                                ->  WindowAgg  (cost=107.83..110.46 rows=50 width=182) (actual time=0.887..0.914 rows=10 loops=1)                                                                                                                    |
     621|                                      ->  Sort  (cost=107.83..107.96 rows=50 width=94) (actual time=0.876..0.881 rows=10 loops=1)                                                                                                                    |
     622|                                            Sort Key: genre_annual.genre_id, genre_annual.year                                                                                                                                                       |
     623|                                            Sort Method: quicksort  Memory: 25kB                                                                                                                                                                     |
     624|                                            ->  Subquery Scan on genre_annual  (cost=100.39..106.42 rows=50 width=94) (actual time=0.684..0.871 rows=10 loops=1)                                                                                     |
     625|                                                  ->  GroupAggregate  (cost=100.39..105.92 rows=50 width=102) (actual time=0.683..0.869 rows=10 loops=1)                                                                                             |
     626|                                                        Group Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id                                                                                                                        |
     627|                                                        ->  Sort  (cost=100.39..100.91 rows=207 width=49) (actual time=0.618..0.641 rows=354 loops=1)                                                                                                |
     628|                                                              Sort Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id, s.story_id                                                                                                       |
     629|                                                              Sort Method: quicksort  Memory: 48kB                                                                                                                                                   |
     630|                                                              ->  Merge Left Join  (cost=1.14..92.43 rows=207 width=49) (actual time=0.101..0.453 rows=354 loops=1)                                                                                  |
     631|                                                                    Merge Cond: (s.story_id = l.story_id)                                                                                                                                            |
     632|                                                                    ->  Merge Left Join  (cost=1.00..76.29 rows=57 width=45) (actual time=0.075..0.215 rows=78 loops=1)                                                                              |
     633|                                                                          Merge Cond: (s.story_id = c.story_id)                                                                                                                                      |
     634|                                                                          ->  Merge Left Join  (cost=0.86..63.07 rows=26 width=41) (actual time=0.067..0.177 rows=30 loops=1)                                                                        |
     635|                                                                                Merge Cond: (s.story_id = ch.story_id)                                                                                                                               |
     636|                                                                                ->  Nested Loop  (cost=0.73..49.35 rows=12 width=30) (actual time=0.053..0.138 rows=12 loops=1)                                                                      |
     637|                                                                                      ->  Nested Loop  (cost=0.56..30.40 rows=12 width=30) (actual time=0.044..0.117 rows=12 loops=1)                                                                |
     638|                                                                                            ->  Nested Loop  (cost=0.42..25.28 rows=12 width=22) (actual time=0.036..0.092 rows=12 loops=1)                                                          |
     639|                                                                                                  ->  Nested Loop  (cost=0.28..20.06 rows=15 width=18) (actual time=0.026..0.061 rows=15 loops=1)                                                    |
     640|                                                                                                        ->  Index Only Scan using has_genre_pk on has_genre hg  (cost=0.14..12.36 rows=15 width=8) (actual time=0.011..0.019 rows=15 loops=1)        |
     641|                                                                                                              Heap Fetches: 15                                                                                                                       |
     642|                                                                                                        ->  Memoize  (cost=0.15..0.96 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=15)                                                    |
     643|                                                                                                              Cache Key: hg.genre_id                                                                                                                 |
     644|                                                                                                              Cache Mode: logical                                                                                                                    |
     645|                                                                                                              Hits: 7  Misses: 8  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                      |
     646|                                                                                                              ->  Index Scan using genre_pkey on genre g  (cost=0.14..0.95 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=8)                |
     647|                                                                                                                    Index Cond: (genre_id = hg.genre_id)                                                                                             |
     648|                                                                                                  ->  Memoize  (cost=0.14..0.96 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=15)                                                           |
     649|                                                                                                        Cache Key: hg.story_id                                                                                                                       |
     650|                                                                                                        Cache Mode: logical                                                                                                                          |
     651|                                                                                                        Hits: 10  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                           |
     652|                                                                                                        ->  Index Only Scan using idx_status_story_published on status st  (cost=0.13..0.95 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=5)|
     653|                                                                                                              Index Cond: (story_id = hg.story_id)                                                                                                   |
     654|                                                                                                              Heap Fetches: 4                                                                                                                        |
     655|                                                                                            ->  Memoize  (cost=0.14..0.96 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=12)                                                                |
     656|                                                                                                  Cache Key: hg.story_id                                                                                                                             |
     657|                                                                                                  Cache Mode: logical                                                                                                                                |
     658|                                                                                                  Hits: 8  Misses: 4  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                                  |
     659|                                                                                                  ->  Index Scan using story_pkey on story s  (cost=0.13..0.95 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=4)                            |
     660|                                                                                                        Index Cond: (story_id = hg.story_id)                                                                                                         |
     661|                                                                                      ->  Memoize  (cost=0.17..4.98 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=12)                                                                       |
     662|                                                                                            Cache Key: s.user_id                                                                                                                                     |
     663|                                                                                            Cache Mode: logical                                                                                                                                      |
     664|                                                                                            Hits: 9  Misses: 3  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                                        |
     665|                                                                                            ->  Index Only Scan using writer_pkey on writer w  (cost=0.15..4.97 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=3)                            |
     666|                                                                                                  Index Cond: (user_id = s.user_id)                                                                                                                  |
     667|                                                                                                  Heap Fetches: 3                                                                                                                                    |
     668|                                                                                ->  Materialize  (cost=0.14..13.33 rows=11 width=15) (actual time=0.010..0.023 rows=31 loops=1)                                                                      |
     669|                                                                                      ->  Index Scan using idx_chapter_story_id on chapter ch  (cost=0.14..13.30 rows=11 width=15) (actual time=0.008..0.013 rows=11 loops=1)                        |
     670|                                                                          ->  Materialize  (cost=0.14..12.33 rows=11 width=8) (actual time=0.007..0.017 rows=79 loops=1)                                                                             |
     671|                                                                                ->  Index Scan using idx_comment_story_id on comment c  (cost=0.14..12.30 rows=11 width=8) (actual time=0.006..0.009 rows=11 loops=1)                                |
     672|                                                                    ->  Materialize  (cost=0.14..12.45 rows=18 width=8) (actual time=0.007..0.033 rows=355 loops=1)                                                                                  |
     673|                                                                          ->  Index Scan using idx_likes_story_id on likes l  (cost=0.14..12.41 rows=18 width=8) (actual time=0.006..0.011 rows=18 loops=1)                                          |
     674|Planning Time: 4.782 ms                                                                                                                                                                                                                              |
     675|Execution Time: 1.200 ms 
     676}}}
     677Average time: 1.181 ms
     678
     679The indexes added for this query provided no meaningful improvement, with average execution time remaining virtually unchanged from 1.179 ms to 1.181 ms (~0%), which is within normal measurement variance. The indexes are not kept.