Changes between Version 14 and Version 15 of P9


Ignore:
Timestamp:
06/08/26 00:40:35 (11 days ago)
Author:
211099
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • P9

    v14 v15  
    1313{{{
    1414EXPLAIN ANALYZE
    15 WITH genre_annual AS (
     15WITH quarterly_story_stats AS (
    1616    SELECT
    17         DATE_TRUNC('year', s.story_created_at) AS year,
    18         g.genre_id,
    19         g.genre_name,
    20         COUNT(DISTINCT s.story_id)      AS total_stories,
    21         COUNT(DISTINCT w.user_id)       AS total_writers,
     17        DATE_TRUNC('quarter', s.story_created_at) AS quarter,
     18        s.story_id,
     19        s.short_description,
     20        s.mature_content,
     21        u.user_id,
     22        u.username AS writer,
     23        st.status,
     24        COUNT(DISTINCT ch.chapter_id) AS total_chapters,
    2225        COALESCE(SUM(ch.view_count), 0) AS total_views,
    2326        COALESCE(SUM(ch.word_count), 0) AS total_words,
    24         COUNT(DISTINCT l.user_id)       AS total_likes,
    25         COUNT(DISTINCT c.comment_id)    AS total_comments,
    26         ROUND(AVG(ch.rating), 2)        AS avg_rating
    27     FROM genre g
    28     JOIN has_genre hg ON g.genre_id = hg.genre_id
    29     JOIN story s      ON hg.story_id = s.story_id
    30     JOIN writer w     ON s.user_id = w.user_id
    31     JOIN status st    ON s.story_id = st.story_id AND st.status = 'published'
    32     LEFT JOIN chapter ch ON s.story_id = ch.story_id
    33     LEFT JOIN likes l    ON s.story_id = l.story_id
    34     LEFT JOIN comment c  ON s.story_id = c.story_id
    35     GROUP BY DATE_TRUNC('year', s.story_created_at), g.genre_id, g.genre_name
     27        ROUND(AVG(ch.rating), 2) AS avg_rating,
     28        COUNT(DISTINCT l.user_id) AS total_likes,
     29        COUNT(DISTINCT c.comment_id) AS total_comments,
     30        COUNT(DISTINCT col.user_id) AS total_collaborators,
     31        COUNT(DISTINCT hg.genre_id) AS total_genres,
     32        COUNT(DISTINCT rli.list_id) AS saved_in_lists
     33    FROM story s
     34    JOIN writer w   ON s.user_id = w.user_id
     35    JOIN users u    ON w.user_id = u.user_id
     36    JOIN status st  ON s.story_id = st.story_id
     37    LEFT JOIN chapter ch         ON s.story_id = ch.story_id
     38    LEFT JOIN likes l            ON s.story_id = l.story_id
     39    LEFT JOIN comment c          ON s.story_id = c.story_id
     40    LEFT JOIN collaboration col  ON s.story_id = col.story_id
     41    LEFT JOIN has_genre hg       ON s.story_id = hg.story_id
     42    LEFT JOIN reading_list_items rli ON s.story_id = rli.story_id
     43    GROUP BY
     44        DATE_TRUNC('quarter', s.story_created_at),
     45        s.story_id, s.short_description, s.mature_content,
     46        u.user_id, u.username, st.status
    3647),
    37 with_metrics AS (
     48with_engagement AS (
    3849    SELECT *,
    3950        ROUND((total_likes + total_comments)::DECIMAL / NULLIF(total_views, 0) * 100, 2) AS engagement_rate,
    40         ROUND(total_views::DECIMAL / NULLIF(total_stories, 0), 2) AS avg_views_per_story,
    41         LAG(total_views)   OVER (PARTITION BY genre_id ORDER BY year) AS prev_year_views,
    42         LAG(total_stories) OVER (PARTITION BY genre_id ORDER BY year) AS prev_year_stories
    43     FROM genre_annual
     51        ROUND(total_views::DECIMAL / NULLIF(total_chapters, 0), 2) AS avg_views_per_chapter,
     52        LAG(total_views)    OVER (PARTITION BY story_id ORDER BY quarter) AS prev_quarter_views,
     53        LAG(total_likes)    OVER (PARTITION BY story_id ORDER BY quarter) AS prev_quarter_likes,
     54        LAG(total_comments) OVER (PARTITION BY story_id ORDER BY quarter) AS prev_quarter_comments
     55    FROM quarterly_story_stats
     56),
     57with_growth AS (
     58    SELECT *,
     59        ROUND((total_views    - prev_quarter_views)::DECIMAL    / NULLIF(prev_quarter_views, 0)    * 100, 2) AS views_growth_pct,
     60        ROUND((total_likes    - prev_quarter_likes)::DECIMAL    / NULLIF(prev_quarter_likes, 0)    * 100, 2) AS likes_growth_pct,
     61        ROUND((total_comments - prev_quarter_comments)::DECIMAL / NULLIF(prev_quarter_comments, 0) * 100, 2) AS comments_growth_pct
     62    FROM with_engagement
    4463)
    4564SELECT
    46     TO_CHAR(year, 'YYYY') AS year,
    47     genre_name,
    48     total_stories, total_writers, total_views, avg_views_per_story,
    49     total_likes, total_comments,
    50     COALESCE(avg_rating, 0)      AS avg_rating,
    51     COALESCE(engagement_rate, 0) AS engagement_rate,
    52     ROUND((total_views - prev_year_views)::DECIMAL   / NULLIF(prev_year_views, 0) * 100, 2) AS yoy_views_growth_pct,
    53     ROUND((total_stories - prev_year_stories)::DECIMAL / NULLIF(prev_year_stories, 0) * 100, 2) AS yoy_stories_growth_pct,
    54     RANK() OVER (PARTITION BY year ORDER BY total_views DESC)     AS popularity_rank,
    55     RANK() OVER (PARTITION BY year ORDER BY engagement_rate DESC) AS engagement_rank
    56 FROM with_metrics
    57 ORDER BY year DESC, popularity_rank;
    58 }}}
    59 {{{
    60 |QUERY PLAN                                                                                                                                                                                                                              |
    61 |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
    62 |Sort  (cost=188601.52..189051.70 rows=180072 width=296) (actual time=735.924..735.941 rows=19 loops=1)                                                                                                                                  |
    63 |  Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?))                                                                                                                                                          |
    64 |  Sort Method: quicksort  Memory: 27kB                                                                                                                                                                                                  |
    65 |  ->  WindowAgg  (cost=123725.89..148260.34 rows=180072 width=296) (actual time=735.837..735.903 rows=19 loops=1)                                                                                                                       |
    66 |        ->  Incremental Sort  (cost=123725.80..138356.38 rows=180072 width=176) (actual time=735.816..735.833 rows=19 loops=1)                                                                                                          |
    67 |              Sort Key: with_metrics.year, with_metrics.total_views DESC                                                                                                                                                                |
    68 |              Presorted Key: with_metrics.year                                                                                                                                                                                          |
    69 |              Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB                                                                                                                                      |
    70 |              ->  WindowAgg  (cost=123663.61..127265.03 rows=180072 width=176) (actual time=735.768..735.813 rows=19 loops=1)                                                                                                           |
    71 |                    ->  Sort  (cost=123663.59..124113.77 rows=180072 width=168) (actual time=735.756..735.773 rows=19 loops=1)                                                                                                          |
    72 |                          Sort Key: with_metrics.year, with_metrics.engagement_rate DESC                                                                                                                                                |
    73 |                          Sort Method: quicksort  Memory: 27kB                                                                                                                                                                          |
    74 |                          ->  Subquery Scan on with_metrics  (cost=81917.02..93171.41 rows=180072 width=168) (actual time=735.680..735.750 rows=19 loops=1)                                                                             |
    75 |                                ->  WindowAgg  (cost=81917.02..91370.69 rows=180072 width=180) (actual time=735.674..735.739 rows=19 loops=1)                                                                                           |
    76 |                                      ->  Sort  (cost=81916.91..82367.09 rows=180072 width=92) (actual time=735.611..735.627 rows=19 loops=1)                                                                                           |
    77 |                                            Sort Key: genre_annual.genre_id, genre_annual.year                                                                                                                                          |
    78 |                                            Sort Method: quicksort  Memory: 26kB                                                                                                                                                        |
    79 |                                            ->  Subquery Scan on genre_annual  (cost=45259.58..56965.23 rows=180072 width=92) (actual time=474.888..735.603 rows=19 loops=1)                                                            |
    80 |                                                  ->  GroupAggregate  (cost=45259.58..55164.51 rows=180072 width=100) (actual time=474.881..735.582 rows=19 loops=1)                                                                    |
    81 |                                                        Group Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id                                                                                                           |
    82 |                                                        ->  Sort  (cost=45259.58..46009.99 rows=300163 width=46) (actual time=474.740..592.149 rows=330994 loops=1)                                                                     |
    83 |                                                              Sort Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id, s.story_id                                                                                          |
    84 |                                                              Sort Method: external merge  Disk: 17832kB                                                                                                                                |
    85 |                                                              ->  Hash Right Join  (cost=2402.96..8715.17 rows=300163 width=46) (actual time=104.929..272.256 rows=330994 loops=1)                                                      |
    86 |                                                                    Hash Cond: (c.story_id = s.story_id)                                                                                                                                |
    87 |                                                                    ->  Seq Scan on comment c  (cost=0.00..1935.11 rows=100011 width=8) (actual time=0.008..8.474 rows=100011 loops=1)                                                  |
    88 |                                                                    ->  Hash  (cost=2027.61..2027.61 rows=30028 width=42) (actual time=104.842..104.854 rows=30561 loops=1)                                                             |
    89 |                                                                          Buckets: 32768  Batches: 1  Memory Usage: 2253kB                                                                                                              |
    90 |                                                                          ->  Hash Join  (cost=1084.06..2027.61 rows=30028 width=42) (actual time=76.783..95.747 rows=30561 loops=1)                                                    |
    91 |                                                                                Hash Cond: (hg.genre_id = g.genre_id)                                                                                                                   |
    92 |                                                                                ->  Hash Join  (cost=1082.66..1928.82 rows=30028 width=34) (actual time=16.868..29.358 rows=30561 loops=1)                                              |
    93 |                                                                                      Hash Cond: (hg.story_id = s.story_id)                                                                                                             |
    94 |                                                                                      ->  Seq Scan on has_genre hg  (cost=0.00..433.28 rows=30028 width=8) (actual time=0.012..2.278 rows=30028 loops=1)                                |
    95 |                                                                                      ->  Hash  (cost=957.59..957.59 rows=10005 width=34) (actual time=16.819..16.828 rows=10145 loops=1)                                               |
    96 |                                                                                            Buckets: 16384  Batches: 1  Memory Usage: 645kB                                                                                             |
    97 |                                                                                            ->  Hash Left Join  (cost=812.87..957.59 rows=10005 width=34) (actual time=7.357..14.471 rows=10145 loops=1)                                |
    98 |                                                                                                  Hash Cond: (s.story_id = l.story_id)                                                                                                  |
    99 |                                                                                                  ->  Hash Join  (cost=811.44..868.43 rows=10005 width=30) (actual time=7.310..12.546 rows=10111 loops=1)                               |
    100 |                                                                                                        Hash Cond: (s.story_id = st.story_id)                                                                                           |
    101 |                                                                                                        ->  Hash Join  (cost=416.49..447.21 rows=10005 width=26) (actual time=3.835..7.095 rows=10111 loops=1)                          |
    102 |                                                                                                              Hash Cond: (s.user_id = w.user_id)                                                                                        |
    103 |                                                                                                              ->  Hash Right Join  (cost=349.11..353.53 rows=10005 width=26) (actual time=3.787..5.295 rows=10111 loops=1)              |
    104 |                                                                                                                    Hash Cond: (ch.story_id = s.story_id)                                                                               |
    105 |                                                                                                                    ->  Seq Scan on chapter ch  (cost=0.00..4.12 rows=112 width=14) (actual time=0.011..0.028 rows=112 loops=1)         |
    106 |                                                                                                                    ->  Hash  (cost=224.05..224.05 rows=10005 width=16) (actual time=3.728..3.729 rows=10005 loops=1)                   |
    107 |                                                                                                                          Buckets: 16384  Batches: 1  Memory Usage: 597kB                                                               |
    108 |                                                                                                                          ->  Seq Scan on story s  (cost=0.00..224.05 rows=10005 width=16) (actual time=0.018..1.867 rows=10005 loops=1)|
    109 |                                                                                                              ->  Hash  (cost=35.50..35.50 rows=2550 width=4) (actual time=0.027..0.028 rows=5 loops=1)                                 |
    110 |                                                                                                                    Buckets: 4096  Batches: 1  Memory Usage: 33kB                                                                       |
    111 |                                                                                                                    ->  Seq Scan on writer w  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.011..0.013 rows=5 loops=1)            |
    112 |                                                                                                        ->  Hash  (cost=269.89..269.89 rows=10005 width=4) (actual time=3.443..3.443 rows=10005 loops=1)                                |
    113 |                                                                                                              Buckets: 16384  Batches: 1  Memory Usage: 480kB                                                                           |
    114 |                                                                                                              ->  Seq Scan on status st  (cost=0.00..269.89 rows=10005 width=4) (actual time=0.031..2.005 rows=10005 loops=1)           |
    115 |                                                                                                                    Filter: ((status)::text = 'published'::text)                                                                        |
    116 |                                                                                                                    Rows Removed by Filter: 5026                                                                                        |
    117 |                                                                                                  ->  Hash  (cost=1.19..1.19 rows=19 width=8) (actual time=0.025..0.026 rows=19 loops=1)                                                |
    118 |                                                                                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                    |
    119 |                                                                                                        ->  Seq Scan on likes l  (cost=0.00..1.19 rows=19 width=8) (actual time=0.013..0.016 rows=19 loops=1)                           |
    120 |                                                                                ->  Hash  (cost=1.18..1.18 rows=18 width=12) (actual time=59.895..59.896 rows=18 loops=1)                                                               |
    121 |                                                                                      Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                                      |
    122 |                                                                                      ->  Seq Scan on genre g  (cost=0.00..1.18 rows=18 width=12) (actual time=0.017..0.020 rows=18 loops=1)                                            |
    123 |Planning Time: 8.265 ms                                                                                                                                                                                                                 |
    124 |JIT:                                                                                                                                                                                                                                    |
    125 |  Functions: 84                                                                                                                                                                                                                         |
    126 |  Options: Inlining false, Optimization false, Expressions true, Deforming true                                                                                                                                                         |
    127 |  Timing: Generation 5.934 ms (Deform 3.054 ms), Inlining 0.000 ms, Optimization 2.243 ms, Emission 57.903 ms, Total 66.080 ms                                                                                                          |
    128 |Execution Time: 747.006 ms                                                                                                                                                                                                              |
    129 
    130 }}}
    131 Average time without indexes is: 749 ms
     65    TO_CHAR(quarter, 'YYYY "Q"Q') AS period,
     66    writer,
     67    story_id,
     68    short_description,
     69    status,
     70    mature_content,
     71    total_chapters,
     72    total_words,
     73    total_genres,
     74    total_collaborators,
     75    saved_in_lists,
     76    total_views,
     77    avg_views_per_chapter,
     78    COALESCE(views_growth_pct, 0)    AS views_growth_pct,
     79    total_likes,
     80    COALESCE(likes_growth_pct, 0)    AS likes_growth_pct,
     81    total_comments,
     82    COALESCE(comments_growth_pct, 0) AS comments_growth_pct,
     83    COALESCE(avg_rating, 0)          AS avg_rating,
     84    COALESCE(engagement_rate, 0)     AS engagement_rate,
     85    RANK() OVER (PARTITION BY quarter ORDER BY total_views DESC)      AS rank_by_views,
     86    RANK() OVER (PARTITION BY quarter ORDER BY engagement_rate DESC)  AS rank_by_engagement,
     87    RANK() OVER (PARTITION BY quarter ORDER BY avg_rating DESC)       AS rank_by_rating
     88FROM with_growth
     89ORDER BY quarter DESC, rank_by_views;
     90}}}
     91{{{
     92|QUERY PLAN                                                                                                                                                                                                                                                   |
     93|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
     94|Sort  (cost=7281598552.60..7281599952.60 rows=560000 width=1539) (actual time=1362.133..1362.145 rows=5 loops=1)                                                                                                                                             |
     95|  Sort Key: with_engagement.quarter DESC, (rank() OVER (?))                                                                                                                                                                                                  |
     96|  Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                                                       |
     97|  ->  WindowAgg  (cost=7280655711.84..7280794770.41 rows=560000 width=1539) (actual time=1362.094..1362.129 rows=5 loops=1)                                                                                                                                  |
     98|        ->  Incremental Sort  (cost=7280655711.65..7280754170.41 rows=560000 width=1363) (actual time=1362.074..1362.085 rows=5 loops=1)                                                                                                                     |
     99|              Sort Key: with_engagement.quarter, with_engagement.total_views DESC                                                                                                                                                                            |
     100|              Presorted Key: with_engagement.quarter                                                                                                                                                                                                         |
     101|              Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB                                                                                                                                                           |
     102|              ->  WindowAgg  (cost=7280655252.07..7280715103.02 rows=560000 width=1363) (actual time=1362.044..1362.075 rows=5 loops=1)                                                                                                                      |
     103|                    ->  Incremental Sort  (cost=7280655251.97..7280705303.02 rows=560000 width=1355) (actual time=1362.034..1362.045 rows=5 loops=1)                                                                                                         |
     104|                          Sort Key: with_engagement.quarter, with_engagement.engagement_rate DESC                                                                                                                                                            |
     105|                          Presorted Key: with_engagement.quarter                                                                                                                                                                                             |
     106|                          Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB                                                                                                                                               |
     107|                          ->  WindowAgg  (cost=7280655035.65..7280666235.63 rows=560000 width=1355) (actual time=1362.001..1362.033 rows=5 loops=1)                                                                                                          |
     108|                                ->  Sort  (cost=7280655035.63..7280656435.63 rows=560000 width=1347) (actual time=1361.987..1361.998 rows=5 loops=1)                                                                                                         |
     109|                                      Sort Key: with_engagement.quarter, with_engagement.avg_rating DESC                                                                                                                                                     |
     110|                                      Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                   |
     111|                                      ->  Subquery Scan on with_engagement  (cost=37078922.01..7279943128.44 rows=560000 width=1347) (actual time=1361.945..1361.985 rows=5 loops=1)                                                                         |
     112|                                            ->  WindowAgg  (cost=37078922.01..7279937528.44 rows=560000 width=1351) (actual time=1361.938..1361.976 rows=5 loops=1)                                                                                          |
     113|                                                  ->  Incremental Sort  (cost=36897846.44..7279908128.44 rows=560000 width=1259) (actual time=1361.905..1361.914 rows=5 loops=1)                                                                             |
     114|                                                        Sort Key: quarterly_story_stats.story_id, quarterly_story_stats.quarter                                                                                                                              |
     115|                                                        Presorted Key: quarterly_story_stats.story_id                                                                                                                                                        |
     116|                                                        Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB                                                                                                                 |
     117|                                                        ->  Subquery Scan on quarterly_story_stats  (cost=500845.04..7279869061.05 rows=560000 width=1259) (actual time=1361.070..1361.903 rows=5 loops=1)                                                   |
     118|                                                              ->  GroupAggregate  (cost=500845.04..7279863461.05 rows=560000 width=1263) (actual time=1361.063..1361.894 rows=5 loops=1)                                                                     |
     119|                                                                    Group Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id                                                                                           |
     120|                                                                    ->  Incremental Sort  (cost=500845.04..7083585393.76 rows=6039023609 width=1211) (actual time=1360.317..1361.324 rows=939 loops=1)                                                       |
     121|                                                                          Sort Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id, ch.chapter_id                                                                       |
     122|                                                                          Presorted Key: s.story_id, st.status                                                                                                                                               |
     123|                                                                          Full-sort Groups: 5  Sort Method: quicksort  Average Memory: 38kB  Peak Memory: 38kB                                                                                               |
     124|                                                                          Pre-sorted Groups: 4  Sort Method: quicksort  Average Memory: 107kB  Peak Memory: 107kB                                                                                            |
     125|                                                                          ->  Nested Loop Left Join  (cost=266.68..44077127.06 rows=6039023609 width=1211) (actual time=1359.651..1360.436 rows=939 loops=1)                                                 |
     126|                                                                                ->  Nested Loop Left Join  (cost=266.52..922084.11 rows=187049404 width=1207) (actual time=1359.606..1359.872 rows=313 loops=1)                                              |
     127|                                                                                      ->  Nested Loop Left Join  (cost=266.35..36339.98 rows=7077545 width=1203) (actual time=1359.567..1359.708 rows=120 loops=1)                                           |
     128|                                                                                            ->  Merge Left Join  (cost=266.19..1814.11 rows=267799 width=1199) (actual time=1359.535..1359.600 rows=119 loops=1)                                             |
     129|                                                                                                  Merge Cond: (s.story_id = l.story_id)                                                                                                                      |
     130|                                                                                                  ->  Merge Left Join  (cost=137.30..253.93 rows=10133 width=1195) (actual time=1359.484..1359.515 rows=27 loops=1)                                          |
     131|                                                                                                        Merge Cond: (s.story_id = c.story_id)                                                                                                                |
     132|                                                                                                        ->  Merge Join  (cost=72.81..134.31 rows=771 width=1191) (actual time=1359.441..1359.460 rows=11 loops=1)                                            |
     133|                                                                                                              Merge Cond: (st.story_id = s.story_id)                                                                                                         |
     134|                                                                                                              ->  Index Only Scan using status_pk on status st  (cost=0.15..56.25 rows=540 width=122) (actual time=0.038..0.045 rows=5 loops=1)              |
     135|                                                                                                                    Heap Fetches: 5                                                                                                                          |
     136|                                                                                                              ->  Sort  (cost=72.66..72.91 rows=100 width=1073) (actual time=0.236..0.240 rows=11 loops=1)                                                   |
     137|                                                                                                                    Sort Key: s.story_id                                                                                                                     |
     138|                                                                                                                    Sort Method: quicksort  Memory: 26kB                                                                                                     |
     139|                                                                                                                    ->  Hash Left Join  (cost=23.30..69.34 rows=100 width=1073) (actual time=0.185..0.204 rows=11 loops=1)                                   |
     140|                                                                                                                          Hash Cond: (s.story_id = ch.story_id)                                                                                              |
     141|                                                                                                                          ->  Nested Loop  (cost=11.05..56.12 rows=70 width=1049) (actual time=0.119..0.136 rows=5 loops=1)                                  |
     142|                                                                                                                                ->  Hash Join  (cost=10.90..21.79 rows=40 width=1053) (actual time=0.086..0.092 rows=5 loops=1)                              |
     143|                                                                                                                                      Hash Cond: (s.user_id = u.user_id)                                                                                     |
     144|                                                                                                                                      ->  Seq Scan on story s  (cost=0.00..10.70 rows=70 width=533) (actual time=0.019..0.020 rows=5 loops=1)                |
     145|                                                                                                                                      ->  Hash  (cost=10.40..10.40 rows=40 width=520) (actual time=0.038..0.039 rows=10 loops=1)                             |
     146|                                                                                                                                            Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                     |
     147|                                                                                                                                            ->  Seq Scan on users u  (cost=0.00..10.40 rows=40 width=520) (actual time=0.023..0.025 rows=10 loops=1)         |
     148|                                                                                                                                ->  Index Only Scan using writer_pkey on writer w  (cost=0.15..0.86 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=5)|
     149|                                                                                                                                      Index Cond: (user_id = s.user_id)                                                                                      |
     150|                                                                                                                                      Heap Fetches: 5                                                                                                        |
     151|                                                                                                                          ->  Hash  (cost=11.00..11.00 rows=100 width=28) (actual time=0.033..0.034 rows=11 loops=1)                                         |
     152|                                                                                                                                Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                 |
     153|                                                                                                                                ->  Seq Scan on chapter ch  (cost=0.00..11.00 rows=100 width=28) (actual time=0.019..0.023 rows=11 loops=1)                  |
     154|                                                                                                        ->  Sort  (cost=64.49..66.79 rows=920 width=8) (actual time=0.024..0.027 rows=27 loops=1)                                                            |
     155|                                                                                                              Sort Key: c.story_id                                                                                                                           |
     156|                                                                                                              Sort Method: quicksort  Memory: 25kB                                                                                                           |
     157|                                                                                                              ->  Seq Scan on comment c  (cost=0.00..19.20 rows=920 width=8) (actual time=0.016..0.018 rows=11 loops=1)                                      |
     158|                                                                                                  ->  Sort  (cost=128.89..133.52 rows=1850 width=8) (actual time=0.031..0.040 rows=119 loops=1)                                                              |
     159|                                                                                                        Sort Key: l.story_id                                                                                                                                 |
     160|                                                                                                        Sort Method: quicksort  Memory: 25kB                                                                                                                 |
     161|                                                                                                        ->  Seq Scan on likes l  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.016..0.018 rows=18 loops=1)                                             |
     162|                                                                                            ->  Memoize  (cost=0.16..14.81 rows=9 width=8) (actual time=0.001..0.001 rows=0 loops=119)                                                                       |
     163|                                                                                                  Cache Key: s.story_id                                                                                                                                      |
     164|                                                                                                  Cache Mode: logical                                                                                                                                        |
     165|                                                                                                  Hits: 114  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                                        |
     166|                                                                                                  ->  Index Only Scan using collaboration_pk on collaboration col  (cost=0.15..14.80 rows=9 width=8) (actual time=0.004..0.004 rows=0 loops=5)               |
     167|                                                                                                        Index Cond: (story_id = s.story_id)                                                                                                                  |
     168|                                                                                                        Heap Fetches: 2                                                                                                                                      |
     169|                                                                                      ->  Memoize  (cost=0.16..14.81 rows=9 width=8) (actual time=0.001..0.001 rows=3 loops=120)                                                                             |
     170|                                                                                            Cache Key: s.story_id                                                                                                                                            |
     171|                                                                                            Cache Mode: logical                                                                                                                                              |
     172|                                                                                            Hits: 115  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                                              |
     173|                                                                                            ->  Index Only Scan using reading_list_items_pk on reading_list_items rli  (cost=0.15..14.80 rows=9 width=8) (actual time=0.004..0.005 rows=3 loops=5)           |
     174|                                                                                                  Index Cond: (story_id = s.story_id)                                                                                                                        |
     175|                                                                                                  Heap Fetches: 13                                                                                                                                           |
     176|                                                                                ->  Memoize  (cost=0.17..1.04 rows=11 width=8) (actual time=0.000..0.001 rows=3 loops=313)                                                                                   |
     177|                                                                                      Cache Key: s.story_id                                                                                                                                                  |
     178|                                                                                      Cache Mode: logical                                                                                                                                                    |
     179|                                                                                      Hits: 308  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                                                    |
     180|                                                                                      ->  Index Only Scan using has_genre_pk on has_genre hg  (cost=0.15..1.03 rows=11 width=8) (actual time=0.006..0.008 rows=3 loops=5)                                    |
     181|                                                                                            Index Cond: (story_id = s.story_id)                                                                                                                              |
     182|                                                                                            Heap Fetches: 15                                                                                                                                                 |
     183|Planning Time: 6.145 ms                                                                                                                                                                                                                                      |
     184|JIT:                                                                                                                                                                                                                                                         |
     185|  Functions: 92                                                                                                                                                                                                                                              |
     186|  Options: Inlining true, Optimization true, Expressions true, Deforming true                                                                                                                                                                                |
     187|  Timing: Generation 8.860 ms (Deform 4.416 ms), Inlining 34.161 ms, Optimization 784.792 ms, Emission 540.464 ms, Total 1368.277 ms                                                                                                                         |
     188|Execution Time: 1371.545 ms                                                                                                                                                                                                                                  |
     189
     190}}}
     191Average time without indexes is: 1397.98 ms
    132192=== Indexes for this queries
    133193{{{
    134 CREATE INDEX idx_has_genre_genre_id
    135     ON has_genre(genre_id);
    136 
    137 CREATE INDEX idx_has_genre_story_id
    138     ON has_genre(story_id);
    139 
    140 CREATE INDEX idx_status_story_published
    141     ON status(story_id)
    142     WHERE status = 'published';
     194CREATE INDEX idx_story_user_id         ON story(user_id);
     195CREATE INDEX idx_story_created_at      ON story(story_created_at);
     196CREATE INDEX idx_chapter_story_id      ON chapter(story_id);
     197CREATE INDEX idx_likes_story_id        ON likes(story_id);
     198CREATE INDEX idx_comment_story_id      ON comment(story_id);
     199CREATE INDEX idx_collaboration_story   ON collaboration(story_id);
     200CREATE INDEX idx_has_genre_story       ON has_genre(story_id);
     201CREATE INDEX idx_rli_story_id          ON reading_list_items(story_id);
     202CREATE INDEX idx_status_story_id       ON status(story_id);
    143203}}}
    144204=== After analisys we get:
    145205{{{
    146 ANALYZE genre;
    147 ANALYZE has_genre;
    148 ANALYZE status;
    149206ANALYZE story;
    150207ANALYZE chapter;
    151208ANALYZE likes;
    152209ANALYZE comment;
    153 }}}
    154 {{{
    155 |QUERY PLAN                                                                                                                                                                                                                              |
    156 |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
    157 |Sort  (cost=188601.52..189051.70 rows=180072 width=296) (actual time=731.664..731.683 rows=19 loops=1)                                                                                                                                  |
    158 |  Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?))                                                                                                                                                          |
    159 |  Sort Method: quicksort  Memory: 27kB                                                                                                                                                                                                  |
    160 |  ->  WindowAgg  (cost=123725.89..148260.34 rows=180072 width=296) (actual time=731.578..731.645 rows=19 loops=1)                                                                                                                       |
    161 |        ->  Incremental Sort  (cost=123725.80..138356.38 rows=180072 width=176) (actual time=731.558..731.576 rows=19 loops=1)                                                                                                          |
    162 |              Sort Key: with_metrics.year, with_metrics.total_views DESC                                                                                                                                                                |
    163 |              Presorted Key: with_metrics.year                                                                                                                                                                                          |
    164 |              Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB                                                                                                                                      |
    165 |              ->  WindowAgg  (cost=123663.61..127265.03 rows=180072 width=176) (actual time=731.509..731.556 rows=19 loops=1)                                                                                                           |
    166 |                    ->  Sort  (cost=123663.59..124113.77 rows=180072 width=168) (actual time=731.498..731.515 rows=19 loops=1)                                                                                                          |
    167 |                          Sort Key: with_metrics.year, with_metrics.engagement_rate DESC                                                                                                                                                |
    168 |                          Sort Method: quicksort  Memory: 27kB                                                                                                                                                                          |
    169 |                          ->  Subquery Scan on with_metrics  (cost=81917.02..93171.41 rows=180072 width=168) (actual time=731.423..731.493 rows=19 loops=1)                                                                             |
    170 |                                ->  WindowAgg  (cost=81917.02..91370.69 rows=180072 width=180) (actual time=731.416..731.482 rows=19 loops=1)                                                                                           |
    171 |                                      ->  Sort  (cost=81916.91..82367.09 rows=180072 width=92) (actual time=731.353..731.369 rows=19 loops=1)                                                                                           |
    172 |                                            Sort Key: genre_annual.genre_id, genre_annual.year                                                                                                                                          |
    173 |                                            Sort Method: quicksort  Memory: 26kB                                                                                                                                                        |
    174 |                                            ->  Subquery Scan on genre_annual  (cost=45259.58..56965.23 rows=180072 width=92) (actual time=470.799..731.345 rows=19 loops=1)                                                            |
    175 |                                                  ->  GroupAggregate  (cost=45259.58..55164.51 rows=180072 width=100) (actual time=470.793..731.331 rows=19 loops=1)                                                                    |
    176 |                                                        Group Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id                                                                                                           |
    177 |                                                        ->  Sort  (cost=45259.58..46009.99 rows=300163 width=46) (actual time=470.650..587.960 rows=330994 loops=1)                                                                     |
    178 |                                                              Sort Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id, s.story_id                                                                                          |
    179 |                                                              Sort Method: external merge  Disk: 17832kB                                                                                                                                |
    180 |                                                              ->  Hash Right Join  (cost=2402.96..8715.17 rows=300163 width=46) (actual time=102.927..268.242 rows=330994 loops=1)                                                      |
    181 |                                                                    Hash Cond: (c.story_id = s.story_id)                                                                                                                                |
    182 |                                                                    ->  Seq Scan on comment c  (cost=0.00..1935.11 rows=100011 width=8) (actual time=0.008..8.499 rows=100011 loops=1)                                                  |
    183 |                                                                    ->  Hash  (cost=2027.61..2027.61 rows=30028 width=42) (actual time=102.841..102.853 rows=30561 loops=1)                                                             |
    184 |                                                                          Buckets: 32768  Batches: 1  Memory Usage: 2253kB                                                                                                              |
    185 |                                                                          ->  Hash Join  (cost=1084.06..2027.61 rows=30028 width=42) (actual time=74.863..93.838 rows=30561 loops=1)                                                    |
    186 |                                                                                Hash Cond: (hg.genre_id = g.genre_id)                                                                                                                   |
    187 |                                                                                ->  Hash Join  (cost=1082.66..1928.82 rows=30028 width=34) (actual time=16.771..29.144 rows=30561 loops=1)                                              |
    188 |                                                                                      Hash Cond: (hg.story_id = s.story_id)                                                                                                             |
    189 |                                                                                      ->  Seq Scan on has_genre hg  (cost=0.00..433.28 rows=30028 width=8) (actual time=0.011..2.336 rows=30028 loops=1)                                |
    190 |                                                                                      ->  Hash  (cost=957.59..957.59 rows=10005 width=34) (actual time=16.724..16.733 rows=10145 loops=1)                                               |
    191 |                                                                                            Buckets: 16384  Batches: 1  Memory Usage: 645kB                                                                                             |
    192 |                                                                                            ->  Hash Left Join  (cost=812.87..957.59 rows=10005 width=34) (actual time=7.297..14.431 rows=10145 loops=1)                                |
    193 |                                                                                                  Hash Cond: (s.story_id = l.story_id)                                                                                                  |
    194 |                                                                                                  ->  Hash Join  (cost=811.44..868.43 rows=10005 width=30) (actual time=7.250..12.492 rows=10111 loops=1)                               |
    195 |                                                                                                        Hash Cond: (s.story_id = st.story_id)                                                                                           |
    196 |                                                                                                        ->  Hash Join  (cost=416.49..447.21 rows=10005 width=26) (actual time=3.788..7.039 rows=10111 loops=1)                          |
    197 |                                                                                                              Hash Cond: (s.user_id = w.user_id)                                                                                        |
    198 |                                                                                                              ->  Hash Right Join  (cost=349.11..353.53 rows=10005 width=26) (actual time=3.743..5.203 rows=10111 loops=1)              |
    199 |                                                                                                                    Hash Cond: (ch.story_id = s.story_id)                                                                               |
    200 |                                                                                                                    ->  Seq Scan on chapter ch  (cost=0.00..4.12 rows=112 width=14) (actual time=0.011..0.028 rows=112 loops=1)         |
    201 |                                                                                                                    ->  Hash  (cost=224.05..224.05 rows=10005 width=16) (actual time=3.685..3.686 rows=10005 loops=1)                   |
    202 |                                                                                                                          Buckets: 16384  Batches: 1  Memory Usage: 597kB                                                               |
    203 |                                                                                                                          ->  Seq Scan on story s  (cost=0.00..224.05 rows=10005 width=16) (actual time=0.017..1.835 rows=10005 loops=1)|
    204 |                                                                                                              ->  Hash  (cost=35.50..35.50 rows=2550 width=4) (actual time=0.024..0.026 rows=5 loops=1)                                 |
    205 |                                                                                                                    Buckets: 4096  Batches: 1  Memory Usage: 33kB                                                                       |
    206 |                                                                                                                    ->  Seq Scan on writer w  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.010..0.012 rows=5 loops=1)            |
    207 |                                                                                                        ->  Hash  (cost=269.89..269.89 rows=10005 width=4) (actual time=3.426..3.427 rows=10005 loops=1)                                |
    208 |                                                                                                              Buckets: 16384  Batches: 1  Memory Usage: 480kB                                                                           |
    209 |                                                                                                              ->  Seq Scan on status st  (cost=0.00..269.89 rows=10005 width=4) (actual time=0.029..1.999 rows=10005 loops=1)           |
    210 |                                                                                                                    Filter: ((status)::text = 'published'::text)                                                                        |
    211 |                                                                                                                    Rows Removed by Filter: 5026                                                                                        |
    212 |                                                                                                  ->  Hash  (cost=1.19..1.19 rows=19 width=8) (actual time=0.025..0.026 rows=19 loops=1)                                                |
    213 |                                                                                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                    |
    214 |                                                                                                        ->  Seq Scan on likes l  (cost=0.00..1.19 rows=19 width=8) (actual time=0.012..0.016 rows=19 loops=1)                           |
    215 |                                                                                ->  Hash  (cost=1.18..1.18 rows=18 width=12) (actual time=58.071..58.072 rows=18 loops=1)                                                               |
    216 |                                                                                      Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                                      |
    217 |                                                                                      ->  Seq Scan on genre g  (cost=0.00..1.18 rows=18 width=12) (actual time=0.015..0.018 rows=18 loops=1)                                            |
    218 |Planning Time: 8.416 ms                                                                                                                                                                                                                 |
    219 |JIT:                                                                                                                                                                                                                                    |
    220 |  Functions: 84                                                                                                                                                                                                                         |
    221 |  Options: Inlining false, Optimization false, Expressions true, Deforming true                                                                                                                                                         |
    222 |  Timing: Generation 5.938 ms (Deform 3.089 ms), Inlining 0.000 ms, Optimization 2.169 ms, Emission 56.154 ms, Total 64.260 ms                                                                                                          |
    223 |Execution Time: 742.778 ms                                                                                                                                                                                                              |
    224 }}}
    225 Average time: 746.354 ms
    226 
    227 We conclude that the indexes added for this query provided no meaningful improvement, dropping from 748 ms to 746 ms (~0.We conclude that the indexes added for this query provided no meaningful improvement, dropping from 748 ms to 746 ms (~0.3%), which is within normal measurement variance. The primary bottleneck is the sort operation spilling to disk (external merge, 17.8 MB), which cannot be resolved through indexing. The indexes are therefore not kept, as they add write overhead without any measurable read benefit.
     210ANALYZE collaboration;
     211ANALYZE has_genre;
     212ANALYZE reading_list_items;
     213ANALYZE status;
     214}}}
     215{{{
     216|QUERY PLAN                                                                                                                                                                                                                                                    |
     217|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
     218|Sort  (cost=333.92..334.92 rows=400 width=1003) (actual time=2.387..2.393 rows=5 loops=1)                                                                                                                                                                     |
     219|  Sort Key: with_engagement.quarter DESC, (rank() OVER (?))                                                                                                                                                                                                   |
     220|  Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                                                        |
     221|  ->  WindowAgg  (cost=251.01..316.63 rows=400 width=1003) (actual time=2.367..2.381 rows=5 loops=1)                                                                                                                                                          |
     222|        ->  Incremental Sort  (cost=250.90..287.63 rows=400 width=827) (actual time=2.356..2.361 rows=5 loops=1)                                                                                                                                              |
     223|              Sort Key: with_engagement.quarter, with_engagement.total_views DESC                                                                                                                                                                             |
     224|              Presorted Key: with_engagement.quarter                                                                                                                                                                                                          |
     225|              Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB                                                                                                                                                            |
     226|              ->  WindowAgg  (cost=250.76..276.63 rows=400 width=827) (actual time=2.343..2.354 rows=5 loops=1)                                                                                                                                               |
     227|                    ->  Incremental Sort  (cost=250.70..269.63 rows=400 width=819) (actual time=2.341..2.347 rows=5 loops=1)                                                                                                                                  |
     228|                          Sort Key: with_engagement.quarter, with_engagement.engagement_rate DESC                                                                                                                                                             |
     229|                          Presorted Key: with_engagement.quarter                                                                                                                                                                                              |
     230|                          Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB                                                                                                                                                |
     231|                          ->  WindowAgg  (cost=250.65..258.63 rows=400 width=819) (actual time=2.326..2.338 rows=5 loops=1)                                                                                                                                   |
     232|                                ->  Sort  (cost=250.63..251.63 rows=400 width=811) (actual time=2.323..2.328 rows=5 loops=1)                                                                                                                                  |
     233|                                      Sort Key: with_engagement.quarter, with_engagement.avg_rating DESC                                                                                                                                                      |
     234|                                      Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                    |
     235|                                      ->  Subquery Scan on with_engagement  (cost=18.18..233.34 rows=400 width=811) (actual time=2.302..2.319 rows=5 loops=1)                                                                                                 |
     236|                                            ->  WindowAgg  (cost=18.18..229.34 rows=400 width=815) (actual time=2.301..2.316 rows=5 loops=1)                                                                                                                  |
     237|                                                  ->  Incremental Sort  (cost=17.68..208.34 rows=400 width=723) (actual time=2.290..2.294 rows=5 loops=1)                                                                                                     |
     238|                                                        Sort Key: quarterly_story_stats.story_id, quarterly_story_stats.quarter                                                                                                                               |
     239|                                                        Presorted Key: quarterly_story_stats.story_id                                                                                                                                                         |
     240|                                                        Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB                                                                                                                  |
     241|                                                        ->  Subquery Scan on quarterly_story_stats  (cost=16.76..197.34 rows=400 width=723) (actual time=1.459..2.285 rows=5 loops=1)                                                                         |
     242|                                                              ->  GroupAggregate  (cost=16.76..193.34 rows=400 width=727) (actual time=1.459..2.283 rows=5 loops=1)                                                                                           |
     243|                                                                    Group Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id                                                                                            |
     244|                                                                    ->  Incremental Sort  (cost=16.76..164.05 rows=686 width=670) (actual time=0.731..1.638 rows=939 loops=1)                                                                                 |
     245|                                                                          Sort Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id, ch.chapter_id                                                                        |
     246|                                                                          Presorted Key: s.story_id, st.status                                                                                                                                                |
     247|                                                                          Full-sort Groups: 5  Sort Method: quicksort  Average Memory: 38kB  Peak Memory: 38kB                                                                                                |
     248|                                                                          Pre-sorted Groups: 4  Sort Method: quicksort  Average Memory: 107kB  Peak Memory: 107kB                                                                                             |
     249|                                                                          ->  Merge Left Join  (cost=1.38..134.35 rows=686 width=670) (actual time=0.118..0.878 rows=939 loops=1)                                                                             |
     250|                                                                                Merge Cond: (s.story_id = hg.story_id)                                                                                                                                        |
     251|                                                                                ->  Merge Left Join  (cost=1.24..109.39 rows=229 width=666) (actual time=0.098..0.328 rows=313 loops=1)                                                                       |
     252|                                                                                      Merge Cond: (s.story_id = rli.story_id)                                                                                                                                 |
     253|                                                                                      ->  Merge Left Join  (cost=1.10..93.41 rows=88 width=662) (actual time=0.090..0.226 rows=120 loops=1)                                                                   |
     254|                                                                                            Merge Cond: (s.story_id = l.story_id)                                                                                                                             |
     255|                                                                                            ->  Merge Left Join  (cost=0.97..79.65 rows=24 width=658) (actual time=0.082..0.176 rows=28 loops=1)                                                              |
     256|                                                                                                  Merge Cond: (s.story_id = c.story_id)                                                                                                                       |
     257|                                                                                                  ->  Merge Left Join  (cost=0.83..66.96 rows=11 width=654) (actual time=0.070..0.143 rows=12 loops=1)                                                        |
     258|                                                                                                        Merge Cond: (s.story_id = ch.story_id)                                                                                                                |
     259|                                                                                                        ->  Merge Left Join  (cost=0.70..53.51 rows=5 width=635) (actual time=0.062..0.118 rows=6 loops=1)                                                    |
     260|                                                                                                              Merge Cond: (s.story_id = col.story_id)                                                                                                         |
     261|                                                                                                              ->  Nested Loop  (cost=0.57..41.32 rows=5 width=631) (actual time=0.054..0.105 rows=5 loops=1)                                                  |
     262|                                                                                                                    Join Filter: (s.user_id = u.user_id)                                                                                                      |
     263|                                                                                                                    ->  Nested Loop  (cost=0.43..40.38 rows=5 width=119) (actual time=0.045..0.085 rows=5 loops=1)                                            |
     264|                                                                                                                          ->  Nested Loop  (cost=0.27..23.35 rows=5 width=115) (actual time=0.031..0.056 rows=5 loops=1)                                      |
     265|                                                                                                                                ->  Index Only Scan using status_pk on status st  (cost=0.13..12.21 rows=5 width=13) (actual time=0.021..0.026 rows=5 loops=1)|
     266|                                                                                                                                      Heap Fetches: 5                                                                                                         |
     267|                                                                                                                                ->  Index Scan using story_pkey on story s  (cost=0.13..2.55 rows=1 width=106) (actual time=0.003..0.003 rows=1 loops=5)      |
     268|                                                                                                                                      Index Cond: (story_id = st.story_id)                                                                                    |
     269|                                                                                                                          ->  Memoize  (cost=0.17..4.98 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=5)                                             |
     270|                                                                                                                                Cache Key: s.user_id                                                                                                          |
     271|                                                                                                                                Cache Mode: logical                                                                                                           |
     272|                                                                                                                                Hits: 2  Misses: 3  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                             |
     273|                                                                                                                                ->  Index Only Scan using writer_pkey on writer w  (cost=0.15..4.97 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=3) |
     274|                                                                                                                                      Index Cond: (user_id = s.user_id)                                                                                       |
     275|                                                                                                                                      Heap Fetches: 3                                                                                                         |
     276|                                                                                                                    ->  Index Scan using users_pkey on users u  (cost=0.14..0.18 rows=1 width=520) (actual time=0.003..0.003 rows=1 loops=5)                  |
     277|                                                                                                                          Index Cond: (user_id = w.user_id)                                                                                                   |
     278|                                                                                                              ->  Index Scan using idx_collaboration_story on collaboration col  (cost=0.13..12.16 rows=2 width=8) (actual time=0.006..0.007 rows=2 loops=1)  |
     279|                                                                                                        ->  Index Scan using idx_chapter_story_id on chapter ch  (cost=0.14..13.30 rows=11 width=23) (actual time=0.006..0.015 rows=11 loops=1)               |
     280|                                                                                                  ->  Materialize  (cost=0.14..12.33 rows=11 width=8) (actual time=0.009..0.018 rows=27 loops=1)                                                              |
     281|                                                                                                        ->  Index Scan using idx_comment_story_id on comment c  (cost=0.14..12.30 rows=11 width=8) (actual time=0.006..0.010 rows=11 loops=1)                 |
     282|                                                                                            ->  Materialize  (cost=0.14..12.45 rows=18 width=8) (actual time=0.007..0.021 rows=119 loops=1)                                                                   |
     283|                                                                                                  ->  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)                           |
     284|                                                                                      ->  Materialize  (cost=0.14..12.36 rows=13 width=8) (actual time=0.007..0.029 rows=312 loops=1)                                                                         |
     285|                                                                                            ->  Index Scan using idx_rli_story_id on reading_list_items rli  (cost=0.14..12.33 rows=13 width=8) (actual time=0.006..0.011 rows=13 loops=1)                    |
     286|                                                                                ->  Materialize  (cost=0.14..12.40 rows=15 width=8) (actual time=0.007..0.069 rows=936 loops=1)                                                                               |
     287|                                                                                      ->  Index Scan using idx_has_genre_story on has_genre hg  (cost=0.14..12.36 rows=15 width=8) (actual time=0.006..0.013 rows=15 loops=1)                                 |
     288|Planning Time: 5.542 ms                                                                                                                                                                                                                                       |
     289|Execution Time: 2.635 ms                                                                                                                                                                                                                                      |
     290}}}
     291Average time: 2.74 ms
     292
     293The 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.
    228294
    229295== Scenario 2 — Quarterly writer performance report