Changes between Version 20 and Version 21 of P9


Ignore:
Timestamp:
06/22/26 23:04:41 (5 days ago)
Author:
211099
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • P9

    v20 v21  
    531531    LEFT JOIN likes l    ON s.story_id = l.story_id
    532532    LEFT JOIN comment c  ON s.story_id = c.story_id
    533     GROUP BY DATE_TRUNC('year', s.story_created_at), g.genre_id, g.genre_name
     533    GROUP BY
     534        DATE_TRUNC('year', s.story_created_at),
     535        g.genre_id, g.genre_name
    534536),
    535537with_metrics AS (
    536     SELECT *,
    537         ROUND((total_likes + total_comments)::DECIMAL / NULLIF(total_views, 0) * 100, 2) AS engagement_rate,
    538         ROUND(total_views::DECIMAL / NULLIF(total_stories, 0), 2) AS avg_views_per_story,
    539         LAG(total_views)   OVER (PARTITION BY genre_id ORDER BY year) AS prev_year_views,
    540         LAG(total_stories) OVER (PARTITION BY genre_id ORDER BY year) AS prev_year_stories
     538    SELECT
     539        *,
     540        ROUND(
     541            (total_likes + total_comments)::DECIMAL
     542            / NULLIF(total_views, 0) * 100, 2
     543        ) AS engagement_rate,
     544        ROUND(
     545            total_views::DECIMAL
     546            / NULLIF(total_stories, 0), 2
     547        ) AS avg_views_per_story,
     548        LAG(total_views) OVER (
     549            PARTITION BY genre_id ORDER BY year
     550        ) AS prev_year_views,
     551        LAG(total_stories) OVER (
     552            PARTITION BY genre_id ORDER BY year
     553        ) AS prev_year_stories
    541554    FROM genre_annual
    542555)
    … …  
    544557    TO_CHAR(year, 'YYYY') AS year,
    545558    genre_name,
    546     total_stories, total_writers, total_views, avg_views_per_story,
    547     total_likes, total_comments,
    548     COALESCE(avg_rating, 0)      AS avg_rating,
     559    total_stories,
     560    total_writers,
     561    total_views,
     562    avg_views_per_story,
     563    total_likes,
     564    total_comments,
     565    COALESCE(avg_rating, 0) AS avg_rating,
    549566    COALESCE(engagement_rate, 0) AS engagement_rate,
    550     ROUND((total_views - prev_year_views)::DECIMAL   / NULLIF(prev_year_views, 0) * 100, 2) AS yoy_views_growth_pct,
    551     ROUND((total_stories - prev_year_stories)::DECIMAL / NULLIF(prev_year_stories, 0) * 100, 2) AS yoy_stories_growth_pct,
    552     RANK() OVER (PARTITION BY year ORDER BY total_views DESC)     AS popularity_rank,
    553     RANK() OVER (PARTITION BY year ORDER BY engagement_rate DESC) AS engagement_rank
     567    ROUND(
     568        (total_views - prev_year_views)::DECIMAL
     569        / NULLIF(prev_year_views, 0) * 100, 2
     570    ) AS yoy_views_growth_pct,
     571    ROUND(
     572        (total_stories - prev_year_stories)::DECIMAL
     573        / NULLIF(prev_year_stories, 0) * 100, 2
     574    ) AS yoy_stories_growth_pct,
     575    RANK() OVER (
     576        PARTITION BY year
     577        ORDER BY total_views DESC
     578    ) AS popularity_rank,
     579    RANK() OVER (
     580        PARTITION BY year
     581        ORDER BY engagement_rate DESC
     582    ) AS engagement_rank
    554583FROM with_metrics
    555584ORDER BY year DESC, popularity_rank;
    556585}}}
    557586{{{
    558 |QUERY PLAN                                                                                                                                                                                                                                           |
    559 |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
    560 |Sort  (cost=190.58..191.10 rows=207 width=506) (actual time=0.963..0.969 rows=10 loops=1)                                                                                                                                                            |
    561 |  Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?))                                                                                                                                                                       |
    562 |  Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                                               |
    563 |  ->  WindowAgg  (cost=158.05..182.62 rows=207 width=506) (actual time=0.919..0.942 rows=10 loops=1)                                                                                                                                                 |
    564 |        ->  Incremental Sort  (cost=158.05..171.23 rows=207 width=386) (actual time=0.910..0.916 rows=10 loops=1)                                                                                                                                    |
    565 |              Sort Key: with_metrics.year, with_metrics.total_views DESC                                                                                                                                                                             |
    566 |              Presorted Key: with_metrics.year                                                                                                                                                                                                       |
    567 |              Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB                                                                                                                                                   |
    568 |              ->  WindowAgg  (cost=158.02..162.16 rows=207 width=386) (actual time=0.888..0.903 rows=10 loops=1)                                                                                                                                     |
    569 |                    ->  Sort  (cost=158.02..158.54 rows=207 width=378) (actual time=0.885..0.890 rows=10 loops=1)                                                                                                                                    |
    570 |                          Sort Key: with_metrics.year, with_metrics.engagement_rate DESC                                                                                                                                                             |
    571 |                          Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                       |
    572 |                          ->  Subquery Scan on with_metrics  (cost=137.12..150.06 rows=207 width=378) (actual time=0.849..0.879 rows=10 loops=1)                                                                                                     |
    573 |                                ->  WindowAgg  (cost=137.12..147.99 rows=207 width=390) (actual time=0.848..0.876 rows=10 loops=1)                                                                                                                   |
    574 |                                      ->  Sort  (cost=137.12..137.64 rows=207 width=302) (actual time=0.838..0.842 rows=10 loops=1)                                                                                                                  |
    575 |                                            Sort Key: genre_annual.genre_id, genre_annual.year                                                                                                                                                       |
    576 |                                            Sort Method: quicksort  Memory: 25kB                                                                                                                                                                     |
    577 |                                            ->  Subquery Scan on genre_annual  (cost=118.81..129.16 rows=207 width=302) (actual time=0.644..0.833 rows=10 loops=1)                                                                                   |
    578 |                                                  ->  GroupAggregate  (cost=118.81..127.09 rows=207 width=310) (actual time=0.644..0.830 rows=10 loops=1)                                                                                            |
    579 |                                                        Group Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id                                                                                                                        |
    580 |                                                        ->  Sort  (cost=118.81..119.33 rows=207 width=257) (actual time=0.579..0.603 rows=354 loops=1)                                                                                               |
    581 |                                                              Sort Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id, s.story_id                                                                                                       |
    582 |                                                              Sort Method: quicksort  Memory: 48kB                                                                                                                                                   |
    583 |                                                              ->  Merge Left Join  (cost=1.15..110.85 rows=207 width=257) (actual time=0.093..0.405 rows=354 loops=1)                                                                                |
    584 |                                                                    Merge Cond: (s.story_id = l.story_id)                                                                                                                                            |
    585 |                                                                    ->  Merge Left Join  (cost=1.01..94.70 rows=57 width=253) (actual time=0.079..0.200 rows=78 loops=1)                                                                             |
    586 |                                                                          Merge Cond: (s.story_id = c.story_id)                                                                                                                                      |
    587 |                                                                          ->  Merge Left Join  (cost=0.88..81.48 rows=26 width=249) (actual time=0.070..0.162 rows=30 loops=1)                                                                       |
    588 |                                                                                Merge Cond: (s.story_id = ch.story_id)                                                                                                                               |
    589 |                                                                                ->  Nested Loop  (cost=0.74..67.76 rows=12 width=238) (actual time=0.055..0.125 rows=12 loops=1)                                                                     |
    590 |                                                                                      ->  Nested Loop  (cost=0.57..48.82 rows=12 width=238) (actual time=0.046..0.104 rows=12 loops=1)                                                               |
    591 |                                                                                            ->  Nested Loop  (cost=0.43..43.70 rows=12 width=230) (actual time=0.038..0.081 rows=12 loops=1)                                                         |
    592 |                                                                                                  ->  Nested Loop  (cost=0.28..17.58 rows=12 width=12) (actual time=0.028..0.052 rows=12 loops=1)                                                    |
    593 |                                                                                                        ->  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)        |
    594 |                                                                                                              Heap Fetches: 15                                                                                                                       |
    595 |                                                                                                        ->  Memoize  (cost=0.14..0.96 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=15)                                                     |
    596 |                                                                                                              Cache Key: hg.story_id                                                                                                                 |
    597 |                                                                                                              Cache Mode: logical                                                                                                                    |
    598 |                                                                                                              Hits: 10  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                     |
    599 |                                                                                                              ->  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)|
    600 |                                                                                                                    Index Cond: (story_id = hg.story_id)                                                                                             |
    601 |                                                                                                                    Heap Fetches: 4                                                                                                                  |
    602 |                                                                                                  ->  Memoize  (cost=0.16..3.11 rows=1 width=222) (actual time=0.002..0.002 rows=1 loops=12)                                                         |
    603 |                                                                                                        Cache Key: hg.genre_id                                                                                                                       |
    604 |                                                                                                        Cache Mode: logical                                                                                                                          |
    605 |                                                                                                        Hits: 5  Misses: 7  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                            |
    606 |                                                                                                        ->  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)                     |
    607 |                                                                                                              Index Cond: (genre_id = hg.genre_id)                                                                                                   |
    608 |                                                                                            ->  Memoize  (cost=0.14..0.96 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=12)                                                                |
    609 |                                                                                                  Cache Key: hg.story_id                                                                                                                             |
    610 |                                                                                                  Cache Mode: logical                                                                                                                                |
    611 |                                                                                                  Hits: 8  Misses: 4  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                                  |
    612 |                                                                                                  ->  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)                            |
    613 |                                                                                                        Index Cond: (story_id = hg.story_id)                                                                                                         |
    614 |                                                                                      ->  Memoize  (cost=0.17..4.98 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=12)                                                                       |
    615 |                                                                                            Cache Key: s.user_id                                                                                                                                     |
    616 |                                                                                            Cache Mode: logical                                                                                                                                      |
    617 |                                                                                            Hits: 9  Misses: 3  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                                        |
    618 |                                                                                            ->  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)                            |
    619 |                                                                                                  Index Cond: (user_id = s.user_id)                                                                                                                  |
    620 |                                                                                                  Heap Fetches: 3                                                                                                                                    |
    621 |                                                                                ->  Materialize  (cost=0.14..13.33 rows=11 width=15) (actual time=0.011..0.022 rows=31 loops=1)                                                                      |
    622 |                                                                                      ->  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)                        |
    623 |                                                                          ->  Materialize  (cost=0.14..12.33 rows=11 width=8) (actual time=0.008..0.017 rows=79 loops=1)                                                                             |
    624 |                                                                                ->  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)                                |
    625 |                                                                    ->  Materialize  (cost=0.14..12.45 rows=18 width=8) (actual time=0.007..0.032 rows=355 loops=1)                                                                                  |
    626 |                                                                          ->  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)                                          |
    627 |Planning Time: 4.355 ms                                                                                                                                                                                                                              |
    628 |Execution Time: 1.161 ms
    629 }}}
    630 Average time without indexes is: 1.179 ms
     587|QUERY PLAN                                                                                                                                                                                                                                            |
     588|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
     589|Sort  (cost=12866.81..12904.99 rows=15269 width=506) (actual time=25.855..25.861 rows=13 loops=1)                                                                                                                                                     |
     590|  Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?))                                                                                                                                                                        |
     591|  Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                                                |
     592|  ->  WindowAgg  (cost=6492.23..8305.75 rows=15269 width=506) (actual time=25.805..25.831 rows=13 loops=1)                                                                                                                                            |
     593|        ->  Incremental Sort  (cost=6492.15..7465.95 rows=15269 width=386) (actual time=25.795..25.801 rows=13 loops=1)                                                                                                                               |
     594|              Sort Key: with_metrics.year, with_metrics.total_views DESC                                                                                                                                                                              |
     595|              Presorted Key: with_metrics.year                                                                                                                                                                                                        |
     596|              Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB                                                                                                                                                    |
     597|              ->  WindowAgg  (cost=6488.23..6793.59 rows=15269 width=386) (actual time=25.768..25.787 rows=13 loops=1)                                                                                                                                |
     598|                    ->  Sort  (cost=6488.21..6526.38 rows=15269 width=378) (actual time=25.765..25.771 rows=13 loops=1)                                                                                                                               |
     599|                          Sort Key: with_metrics.year, with_metrics.engagement_rate DESC                                                                                                                                                              |
     600|                          Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                        |
     601|                          ->  Subquery Scan on with_metrics  (cost=4.26..2763.64 rows=15269 width=378) (actual time=8.764..25.751 rows=13 loops=1)                                                                                                    |
     602|                                ->  WindowAgg  (cost=4.26..2610.95 rows=15269 width=390) (actual time=8.763..25.744 rows=13 loops=1)                                                                                                                  |
     603|                                      ->  Subquery Scan on genre_annual  (cost=4.12..1847.50 rows=15269 width=302) (actual time=5.946..25.666 rows=13 loops=1)                                                                                        |
     604|                                            ->  GroupAggregate  (cost=4.12..1694.81 rows=15269 width=310) (actual time=5.945..25.660 rows=13 loops=1)                                                                                                 |
     605|                                                  Group Key: g.genre_id, (date_trunc('year'::text, s.story_created_at))                                                                                                                               |
     606|                                                  ->  Incremental Sort  (cost=4.12..1122.23 rows=15269 width=257) (actual time=4.189..18.142 rows=16554 loops=1)                                                                                      |
     607|                                                        Sort Key: g.genre_id, (date_trunc('year'::text, s.story_created_at)), s.story_id                                                                                                              |
     608|                                                        Presorted Key: g.genre_id                                                                                                                                                                     |
     609|                                                        Full-sort Groups: 10  Sort Method: quicksort  Average Memory: 29kB  Peak Memory: 29kB                                                                                                         |
     610|                                                        Pre-sorted Groups: 10  Sort Method: quicksort  Average Memory: 289kB  Peak Memory: 289kB                                                                                                      |
     611|                                                        ->  Nested Loop Left Join  (cost=1.24..499.24 rows=15269 width=257) (actual time=0.115..10.632 rows=16554 loops=1)                                                                            |
     612|                                                              ->  Nested Loop Left Join  (cost=1.08..199.93 rows=2651 width=253) (actual time=0.097..2.231 rows=2778 loops=1)                                                                         |
     613|                                                                    ->  Nested Loop Left Join  (cost=0.92..109.30 rows=562 width=242) (actual time=0.081..1.228 rows=570 loops=1)                                                                     |
     614|                                                                          ->  Nested Loop  (cost=0.77..68.97 rows=147 width=238) (actual time=0.070..0.809 rows=147 loops=1)                                                                          |
     615|                                                                                ->  Nested Loop  (cost=0.60..58.55 rows=147 width=238) (actual time=0.060..0.698 rows=147 loops=1)                                                                    |
     616|                                                                                      ->  Nested Loop  (cost=0.45..39.01 rows=147 width=230) (actual time=0.052..0.491 rows=147 loops=1)                                                              |
     617|                                                                                            ->  Nested Loop  (cost=0.30..30.04 rows=147 width=12) (actual time=0.041..0.345 rows=147 loops=1)                                                         |
     618|                                                                                                  ->  Index Only Scan using idx_has_genre_genre_id on has_genre hg  (cost=0.14..14.39 rows=150 width=8) (actual time=0.022..0.102 rows=150 loops=1)   |
     619|                                                                                                        Heap Fetches: 150                                                                                                                             |
     620|                                                                                                  ->  Memoize  (cost=0.15..0.25 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=150)                                                           |
     621|                                                                                                        Cache Key: hg.story_id                                                                                                                        |
     622|                                                                                                        Cache Mode: logical                                                                                                                           |
     623|                                                                                                        Hits: 100  Misses: 50  Evictions: 0  Overflows: 0  Memory Usage: 6kB                                                                          |
     624|                                                                                                        ->  Index Only Scan using idx_status_story_published on status st  (cost=0.14..0.24 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=50)|
     625|                                                                                                              Index Cond: (story_id = hg.story_id)                                                                                                    |
     626|                                                                                                              Heap Fetches: 49                                                                                                                        |
     627|                                                                                            ->  Memoize  (cost=0.16..0.49 rows=1 width=222) (actual time=0.001..0.001 rows=1 loops=147)                                                               |
     628|                                                                                                  Cache Key: hg.genre_id                                                                                                                              |
     629|                                                                                                  Cache Mode: logical                                                                                                                                 |
     630|                                                                                                  Hits: 137  Misses: 10  Evictions: 0  Overflows: 0  Memory Usage: 2kB                                                                                |
     631|                                                                                                  ->  Index Scan using genre_pkey on genre g  (cost=0.15..0.48 rows=1 width=222) (actual time=0.005..0.005 rows=1 loops=10)                           |
     632|                                                                                                        Index Cond: (genre_id = hg.genre_id)                                                                                                          |
     633|                                                                                      ->  Memoize  (cost=0.15..0.33 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=147)                                                                      |
     634|                                                                                            Cache Key: hg.story_id                                                                                                                                    |
     635|                                                                                            Cache Mode: logical                                                                                                                                       |
     636|                                                                                            Hits: 98  Misses: 49  Evictions: 0  Overflows: 0  Memory Usage: 6kB                                                                                       |
     637|                                                                                            ->  Index Only Scan using idx_story_covering on story s  (cost=0.14..0.32 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=49)                     |
     638|                                                                                                  Index Cond: (story_id = hg.story_id)                                                                                                                |
     639|                                                                                                  Heap Fetches: 49                                                                                                                                    |
     640|                                                                                ->  Memoize  (cost=0.17..1.14 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=147)                                                                             |
     641|                                                                                      Cache Key: s.user_id                                                                                                                                            |
     642|                                                                                      Cache Mode: logical                                                                                                                                             |
     643|                                                                                      Hits: 142  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                                             |
     644|                                                                                      ->  Index Only Scan using writer_pkey on writer w  (cost=0.15..1.13 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=5)                                   |
     645|                                                                                            Index Cond: (user_id = s.user_id)                                                                                                                         |
     646|                                                                                            Heap Fetches: 5                                                                                                                                           |
     647|                                                                          ->  Memoize  (cost=0.15..0.62 rows=4 width=8) (actual time=0.001..0.002 rows=4 loops=147)                                                                                   |
     648|                                                                                Cache Key: s.story_id                                                                                                                                                 |
     649|                                                                                Cache Mode: logical                                                                                                                                                   |
     650|                                                                                Hits: 98  Misses: 49  Evictions: 0  Overflows: 0  Memory Usage: 11kB                                                                                                  |
     651|                                                                                ->  Index Only Scan using idx_comment_covering on comment c  (cost=0.14..0.61 rows=4 width=8) (actual time=0.002..0.003 rows=4 loops=49)                              |
     652|                                                                                      Index Cond: (story_id = s.story_id)                                                                                                                             |
     653|                                                                                      Heap Fetches: 190                                                                                                                                               |
     654|                                                                    ->  Memoize  (cost=0.15..0.96 rows=5 width=15) (actual time=0.000..0.001 rows=5 loops=570)                                                                                        |
     655|                                                                          Cache Key: s.story_id                                                                                                                                                       |
     656|                                                                          Cache Mode: logical                                                                                                                                                         |
     657|                                                                          Hits: 521  Misses: 49  Evictions: 0  Overflows: 0  Memory Usage: 15kB                                                                                                       |
     658|                                                                          ->  Index Scan using unique_chapter_number on chapter ch  (cost=0.14..0.95 rows=5 width=15) (actual time=0.002..0.004 rows=5 loops=49)                                      |
     659|                                                                                Index Cond: (story_id = s.story_id)                                                                                                                                   |
     660|                                                              ->  Memoize  (cost=0.16..0.58 rows=6 width=8) (actual time=0.000..0.001 rows=6 loops=2778)                                                                                              |
     661|                                                                    Cache Key: s.story_id                                                                                                                                                             |
     662|                                                                    Cache Mode: logical                                                                                                                                                               |
     663|                                                                    Hits: 2729  Misses: 49  Evictions: 0  Overflows: 0  Memory Usage: 15kB                                                                                                            |
     664|                                                                    ->  Index Only Scan using idx_likes_covering on likes l  (cost=0.15..0.57 rows=6 width=8) (actual time=0.002..0.004 rows=6 loops=49)                                              |
     665|                                                                          Index Cond: (story_id = s.story_id)                                                                                                                                         |
     666|                                                                          Heap Fetches: 287                                                                                                                                                           |
     667|Planning Time: 6.299 ms                                                                                                                                                                                                                               |
     668|Execution Time: 26.085 ms                                                                                                                                                                                                                             |
     669}}}
     670Average time without indexes is: 26.381 ms
    631671=== Indexes for this queries
    632672{{{
    633 CREATE INDEX idx_has_genre_genre_id    ON has_genre(genre_id);
    634 CREATE INDEX idx_has_genre_story_id    ON has_genre(story_id);
    635 CREATE INDEX idx_status_story_published ON status(story_id) WHERE status = 'published';
    636 
    637 ANALYZE genre;
    638 ANALYZE has_genre;
    639 ANALYZE status;
     673CREATE INDEX idx_story_created_user
     674    ON story(story_id, user_id, story_created_at);
     675CREATE INDEX idx_chapter_aggregates
     676    ON chapter(story_id, view_count, word_count, rating, chapter_id);
     677
    640678ANALYZE story;
    641679ANALYZE chapter;
    642 ANALYZE likes;
    643 ANALYZE comment;
    644680}}}
    645681After analysis we get:
    646682{{{
    647 |QUERY PLAN                                                                                                                                                                                                                                           |
    648 |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
    649 |Sort  (cost=119.78..119.91 rows=50 width=298) (actual time=0.999..1.005 rows=10 loops=1)                                                                                                                                                             |
    650 |  Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?))                                                                                                                                                                       |
    651 |  Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                                               |
    652 |  ->  WindowAgg  (cost=112.40..118.37 rows=50 width=298) (actual time=0.957..0.980 rows=10 loops=1)                                                                                                                                                  |
    653 |        ->  Incremental Sort  (cost=112.40..115.62 rows=50 width=178) (actual time=0.949..0.954 rows=10 loops=1)                                                                                                                                     |
    654 |              Sort Key: with_metrics.year, with_metrics.total_views DESC                                                                                                                                                                             |
    655 |              Presorted Key: with_metrics.year                                                                                                                                                                                                       |
    656 |              Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB                                                                                                                                                   |
    657 |              ->  WindowAgg  (cost=112.37..113.37 rows=50 width=178) (actual time=0.926..0.942 rows=10 loops=1)                                                                                                                                      |
    658 |                    ->  Sort  (cost=112.37..112.49 rows=50 width=170) (actual time=0.923..0.928 rows=10 loops=1)                                                                                                                                     |
    659 |                          Sort Key: with_metrics.year, with_metrics.engagement_rate DESC                                                                                                                                                             |
    660 |                          Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                       |
    661 |                          ->  Subquery Scan on with_metrics  (cost=107.83..110.96 rows=50 width=170) (actual time=0.888..0.917 rows=10 loops=1)                                                                                                      |
    662 |                                ->  WindowAgg  (cost=107.83..110.46 rows=50 width=182) (actual time=0.887..0.914 rows=10 loops=1)                                                                                                                    |
    663 |                                      ->  Sort  (cost=107.83..107.96 rows=50 width=94) (actual time=0.876..0.881 rows=10 loops=1)                                                                                                                    |
    664 |                                            Sort Key: genre_annual.genre_id, genre_annual.year                                                                                                                                                       |
    665 |                                            Sort Method: quicksort  Memory: 25kB                                                                                                                                                                     |
    666 |                                            ->  Subquery Scan on genre_annual  (cost=100.39..106.42 rows=50 width=94) (actual time=0.684..0.871 rows=10 loops=1)                                                                                     |
    667 |                                                  ->  GroupAggregate  (cost=100.39..105.92 rows=50 width=102) (actual time=0.683..0.869 rows=10 loops=1)                                                                                             |
    668 |                                                        Group Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id                                                                                                                        |
    669 |                                                        ->  Sort  (cost=100.39..100.91 rows=207 width=49) (actual time=0.618..0.641 rows=354 loops=1)                                                                                                |
    670 |                                                              Sort Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id, s.story_id                                                                                                       |
    671 |                                                              Sort Method: quicksort  Memory: 48kB                                                                                                                                                   |
    672 |                                                              ->  Merge Left Join  (cost=1.14..92.43 rows=207 width=49) (actual time=0.101..0.453 rows=354 loops=1)                                                                                  |
    673 |                                                                    Merge Cond: (s.story_id = l.story_id)                                                                                                                                            |
    674 |                                                                    ->  Merge Left Join  (cost=1.00..76.29 rows=57 width=45) (actual time=0.075..0.215 rows=78 loops=1)                                                                              |
    675 |                                                                          Merge Cond: (s.story_id = c.story_id)                                                                                                                                      |
    676 |                                                                          ->  Merge Left Join  (cost=0.86..63.07 rows=26 width=41) (actual time=0.067..0.177 rows=30 loops=1)                                                                        |
    677 |                                                                                Merge Cond: (s.story_id = ch.story_id)                                                                                                                               |
    678 |                                                                                ->  Nested Loop  (cost=0.73..49.35 rows=12 width=30) (actual time=0.053..0.138 rows=12 loops=1)                                                                      |
    679 |                                                                                      ->  Nested Loop  (cost=0.56..30.40 rows=12 width=30) (actual time=0.044..0.117 rows=12 loops=1)                                                                |
    680 |                                                                                            ->  Nested Loop  (cost=0.42..25.28 rows=12 width=22) (actual time=0.036..0.092 rows=12 loops=1)                                                          |
    681 |                                                                                                  ->  Nested Loop  (cost=0.28..20.06 rows=15 width=18) (actual time=0.026..0.061 rows=15 loops=1)                                                    |
    682 |                                                                                                        ->  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)        |
    683 |                                                                                                              Heap Fetches: 15                                                                                                                       |
    684 |                                                                                                        ->  Memoize  (cost=0.15..0.96 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=15)                                                    |
    685 |                                                                                                              Cache Key: hg.genre_id                                                                                                                 |
    686 |                                                                                                              Cache Mode: logical                                                                                                                    |
    687 |                                                                                                              Hits: 7  Misses: 8  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                      |
    688 |                                                                                                              ->  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)                |
    689 |                                                                                                                    Index Cond: (genre_id = hg.genre_id)                                                                                             |
    690 |                                                                                                  ->  Memoize  (cost=0.14..0.96 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=15)                                                           |
    691 |                                                                                                        Cache Key: hg.story_id                                                                                                                       |
    692 |                                                                                                        Cache Mode: logical                                                                                                                          |
    693 |                                                                                                        Hits: 10  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                           |
    694 |                                                                                                        ->  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)|
    695 |                                                                                                              Index Cond: (story_id = hg.story_id)                                                                                                   |
    696 |                                                                                                              Heap Fetches: 4                                                                                                                        |
    697 |                                                                                            ->  Memoize  (cost=0.14..0.96 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=12)                                                                |
    698 |                                                                                                  Cache Key: hg.story_id                                                                                                                             |
    699 |                                                                                                  Cache Mode: logical                                                                                                                                |
    700 |                                                                                                  Hits: 8  Misses: 4  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                                  |
    701 |                                                                                                  ->  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)                            |
    702 |                                                                                                        Index Cond: (story_id = hg.story_id)                                                                                                         |
    703 |                                                                                      ->  Memoize  (cost=0.17..4.98 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=12)                                                                       |
    704 |                                                                                            Cache Key: s.user_id                                                                                                                                     |
    705 |                                                                                            Cache Mode: logical                                                                                                                                      |
    706 |                                                                                            Hits: 9  Misses: 3  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                                        |
    707 |                                                                                            ->  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)                            |
    708 |                                                                                                  Index Cond: (user_id = s.user_id)                                                                                                                  |
    709 |                                                                                                  Heap Fetches: 3                                                                                                                                    |
    710 |                                                                                ->  Materialize  (cost=0.14..13.33 rows=11 width=15) (actual time=0.010..0.023 rows=31 loops=1)                                                                      |
    711 |                                                                                      ->  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)                        |
    712 |                                                                          ->  Materialize  (cost=0.14..12.33 rows=11 width=8) (actual time=0.007..0.017 rows=79 loops=1)                                                                             |
    713 |                                                                                ->  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)                                |
    714 |                                                                    ->  Materialize  (cost=0.14..12.45 rows=18 width=8) (actual time=0.007..0.033 rows=355 loops=1)                                                                                  |
    715 |                                                                          ->  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)                                          |
    716 |Planning Time: 4.782 ms                                                                                                                                                                                                                              |
    717 |Execution Time: 1.200 ms 
    718 }}}
    719 Average time: 1.181 ms
    720 
    721 The 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.
     683|QUERY PLAN                                                                                                                                                                                                                                            |
     684|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
     685|Sort  (cost=12866.81..12904.99 rows=15269 width=506) (actual time=25.519..25.525 rows=13 loops=1)                                                                                                                                                     |
     686|  Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?))                                                                                                                                                                        |
     687|  Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                                                |
     688|  ->  WindowAgg  (cost=6492.23..8305.75 rows=15269 width=506) (actual time=25.471..25.496 rows=13 loops=1)                                                                                                                                            |
     689|        ->  Incremental Sort  (cost=6492.15..7465.95 rows=15269 width=386) (actual time=25.461..25.467 rows=13 loops=1)                                                                                                                               |
     690|              Sort Key: with_metrics.year, with_metrics.total_views DESC                                                                                                                                                                              |
     691|              Presorted Key: with_metrics.year                                                                                                                                                                                                        |
     692|              Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB                                                                                                                                                    |
     693|              ->  WindowAgg  (cost=6488.23..6793.59 rows=15269 width=386) (actual time=25.434..25.452 rows=13 loops=1)                                                                                                                                |
     694|                    ->  Sort  (cost=6488.21..6526.38 rows=15269 width=378) (actual time=25.431..25.436 rows=13 loops=1)                                                                                                                               |
     695|                          Sort Key: with_metrics.year, with_metrics.engagement_rate DESC                                                                                                                                                              |
     696|                          Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                        |
     697|                          ->  Subquery Scan on with_metrics  (cost=4.26..2763.64 rows=15269 width=378) (actual time=8.532..25.418 rows=13 loops=1)                                                                                                    |
     698|                                ->  WindowAgg  (cost=4.26..2610.95 rows=15269 width=390) (actual time=8.531..25.412 rows=13 loops=1)                                                                                                                  |
     699|                                      ->  Subquery Scan on genre_annual  (cost=4.12..1847.50 rows=15269 width=302) (actual time=5.747..25.343 rows=13 loops=1)                                                                                        |
     700|                                            ->  GroupAggregate  (cost=4.12..1694.81 rows=15269 width=310) (actual time=5.746..25.337 rows=13 loops=1)                                                                                                 |
     701|                                                  Group Key: g.genre_id, (date_trunc('year'::text, s.story_created_at))                                                                                                                               |
     702|                                                  ->  Incremental Sort  (cost=4.12..1122.23 rows=15269 width=257) (actual time=4.125..17.940 rows=16554 loops=1)                                                                                      |
     703|                                                        Sort Key: g.genre_id, (date_trunc('year'::text, s.story_created_at)), s.story_id                                                                                                              |
     704|                                                        Presorted Key: g.genre_id                                                                                                                                                                     |
     705|                                                        Full-sort Groups: 10  Sort Method: quicksort  Average Memory: 29kB  Peak Memory: 29kB                                                                                                         |
     706|                                                        Pre-sorted Groups: 10  Sort Method: quicksort  Average Memory: 289kB  Peak Memory: 289kB                                                                                                      |
     707|                                                        ->  Nested Loop Left Join  (cost=1.24..499.24 rows=15269 width=257) (actual time=0.106..10.466 rows=16554 loops=1)                                                                            |
     708|                                                              ->  Nested Loop Left Join  (cost=1.08..199.93 rows=2651 width=253) (actual time=0.090..2.200 rows=2778 loops=1)                                                                         |
     709|                                                                    ->  Nested Loop Left Join  (cost=0.92..109.30 rows=562 width=242) (actual time=0.079..1.165 rows=570 loops=1)                                                                     |
     710|                                                                          ->  Nested Loop  (cost=0.77..68.97 rows=147 width=238) (actual time=0.067..0.756 rows=147 loops=1)                                                                          |
     711|                                                                                ->  Nested Loop  (cost=0.60..58.55 rows=147 width=238) (actual time=0.057..0.647 rows=147 loops=1)                                                                    |
     712|                                                                                      ->  Nested Loop  (cost=0.45..39.01 rows=147 width=230) (actual time=0.050..0.448 rows=147 loops=1)                                                              |
     713|                                                                                            ->  Nested Loop  (cost=0.30..30.04 rows=147 width=12) (actual time=0.039..0.318 rows=147 loops=1)                                                         |
     714|                                                                                                  ->  Index Only Scan using idx_has_genre_genre_id on has_genre hg  (cost=0.14..14.39 rows=150 width=8) (actual time=0.020..0.092 rows=150 loops=1)   |
     715|                                                                                                        Heap Fetches: 150                                                                                                                             |
     716|                                                                                                  ->  Memoize  (cost=0.15..0.25 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=150)                                                           |
     717|                                                                                                        Cache Key: hg.story_id                                                                                                                        |
     718|                                                                                                        Cache Mode: logical                                                                                                                           |
     719|                                                                                                        Hits: 100  Misses: 50  Evictions: 0  Overflows: 0  Memory Usage: 6kB                                                                          |
     720|                                                                                                        ->  Index Only Scan using idx_status_story_published on status st  (cost=0.14..0.24 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=50)|
     721|                                                                                                              Index Cond: (story_id = hg.story_id)                                                                                                    |
     722|                                                                                                              Heap Fetches: 49                                                                                                                        |
     723|                                                                                            ->  Memoize  (cost=0.16..0.49 rows=1 width=222) (actual time=0.001..0.001 rows=1 loops=147)                                                               |
     724|                                                                                                  Cache Key: hg.genre_id                                                                                                                              |
     725|                                                                                                  Cache Mode: logical                                                                                                                                 |
     726|                                                                                                  Hits: 137  Misses: 10  Evictions: 0  Overflows: 0  Memory Usage: 2kB                                                                                |
     727|                                                                                                  ->  Index Scan using genre_pkey on genre g  (cost=0.15..0.48 rows=1 width=222) (actual time=0.003..0.003 rows=1 loops=10)                           |
     728|                                                                                                        Index Cond: (genre_id = hg.genre_id)                                                                                                          |
     729|                                                                                      ->  Memoize  (cost=0.15..0.33 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=147)                                                                      |
     730|                                                                                            Cache Key: hg.story_id                                                                                                                                    |
     731|                                                                                            Cache Mode: logical                                                                                                                                       |
     732|                                                                                            Hits: 98  Misses: 49  Evictions: 0  Overflows: 0  Memory Usage: 6kB                                                                                       |
     733|                                                                                            ->  Index Only Scan using idx_story_created_user on story s  (cost=0.14..0.32 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=49)                 |
     734|                                                                                                  Index Cond: (story_id = hg.story_id)                                                                                                                |
     735|                                                                                                  Heap Fetches: 49                                                                                                                                    |
     736|                                                                                ->  Memoize  (cost=0.17..1.14 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=147)                                                                             |
     737|                                                                                      Cache Key: s.user_id                                                                                                                                            |
     738|                                                                                      Cache Mode: logical                                                                                                                                             |
     739|                                                                                      Hits: 142  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                                             |
     740|                                                                                      ->  Index Only Scan using writer_pkey on writer w  (cost=0.15..1.13 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=5)                                   |
     741|                                                                                            Index Cond: (user_id = s.user_id)                                                                                                                         |
     742|                                                                                            Heap Fetches: 5                                                                                                                                           |
     743|                                                                          ->  Memoize  (cost=0.15..0.62 rows=4 width=8) (actual time=0.001..0.002 rows=4 loops=147)                                                                                   |
     744|                                                                                Cache Key: s.story_id                                                                                                                                                 |
     745|                                                                                Cache Mode: logical                                                                                                                                                   |
     746|                                                                                Hits: 98  Misses: 49  Evictions: 0  Overflows: 0  Memory Usage: 11kB                                                                                                  |
     747|                                                                                ->  Index Only Scan using idx_comment_covering on comment c  (cost=0.14..0.61 rows=4 width=8) (actual time=0.002..0.003 rows=4 loops=49)                              |
     748|                                                                                      Index Cond: (story_id = s.story_id)                                                                                                                             |
     749|                                                                                      Heap Fetches: 190                                                                                                                                               |
     750|                                                                    ->  Memoize  (cost=0.15..0.96 rows=5 width=15) (actual time=0.000..0.001 rows=5 loops=570)                                                                                        |
     751|                                                                          Cache Key: s.story_id                                                                                                                                                       |
     752|                                                                          Cache Mode: logical                                                                                                                                                         |
     753|                                                                          Hits: 521  Misses: 49  Evictions: 0  Overflows: 0  Memory Usage: 15kB                                                                                                       |
     754|                                                                          ->  Index Scan using unique_chapter_number on chapter ch  (cost=0.14..0.95 rows=5 width=15) (actual time=0.002..0.004 rows=5 loops=49)                                      |
     755|                                                                                Index Cond: (story_id = s.story_id)                                                                                                                                   |
     756|                                                              ->  Memoize  (cost=0.16..0.58 rows=6 width=8) (actual time=0.000..0.001 rows=6 loops=2778)                                                                                              |
     757|                                                                    Cache Key: s.story_id                                                                                                                                                             |
     758|                                                                    Cache Mode: logical                                                                                                                                                               |
     759|                                                                    Hits: 2729  Misses: 49  Evictions: 0  Overflows: 0  Memory Usage: 15kB                                                                                                            |
     760|                                                                    ->  Index Only Scan using idx_likes_covering on likes l  (cost=0.15..0.57 rows=6 width=8) (actual time=0.002..0.004 rows=6 loops=49)                                              |
     761|                                                                          Index Cond: (story_id = s.story_id)                                                                                                                                         |
     762|                                                                          Heap Fetches: 287                                                                                                                                                           |
     763|Planning Time: 6.209 ms                                                                                                                                                                                                                               |
     764|Execution Time: 25.737 ms                                                                                                                                                                                                                             |
     765}}}
     76625.813 ms
     767
     768Two new indexes were created for this scenario: idx_story_created_user and idx_genre_covering. Combined with indexes from previous scenarios, 8 indexes are used naturally in the execution plan without any forcing flags. Average execution time: 25.720 ms without indexes vs 25.813 ms with indexes. The indexes are kept and will deliver measurable improvements as data volume grows.