= Other topics (Performance, Security, …) = === The testing methodology is as follows: A large number of new records are inserted into the relevant tables (e.g. 10,000 stories, 500,000 chapters, 200,000 likes, 100,000 comments) so that index usage becomes worthwhile. It is well understood that for a table with only a handful of rows, an index offers little to no advantage. Before adding any index, the query is executed 10 times using EXPLAIN ANALYZE. The average Execution Time is recorded and the query plan is saved for later comparison. After adding the indexes, the same query is executed 10 times and the results are compared. == Scenario 1 — Quarterly Story Performance Dashboard Complex reporting query that aggregates per-story statistics grouped by calendar quarter, then computes engagement rates, quarter-over-quarter growth percentages, and per-quarter rankings using window functions across three CTEs. {{{ WITH quarterly_story_stats AS ( SELECT DATE_TRUNC('quarter', s.story_created_at) AS quarter, s.story_id, s.short_description, s.mature_content, u.user_id, u.username AS writer, st.status, COUNT(DISTINCT ch.chapter_id) AS total_chapters, COALESCE(SUM(ch.view_count), 0) AS total_views, COALESCE(SUM(ch.word_count), 0) AS total_words, ROUND(AVG(ch.rating), 2) AS avg_rating, COUNT(DISTINCT l.user_id) AS total_likes, COUNT(DISTINCT c.comment_id) AS total_comments, COUNT(DISTINCT col.user_id) AS total_collaborators, COUNT(DISTINCT hg.genre_id) AS total_genres, COUNT(DISTINCT rli.list_id) AS saved_in_lists FROM story s JOIN writer w ON s.user_id = w.user_id JOIN users u ON w.user_id = u.user_id JOIN status st ON s.story_id = st.story_id LEFT JOIN chapter ch ON s.story_id = ch.story_id LEFT JOIN likes l ON s.story_id = l.story_id LEFT JOIN comment c ON s.story_id = c.story_id LEFT JOIN collaboration col ON s.story_id = col.story_id LEFT JOIN has_genre hg ON s.story_id = hg.story_id LEFT JOIN reading_list_items rli ON s.story_id = rli.story_id GROUP BY DATE_TRUNC('quarter', s.story_created_at), s.story_id, s.short_description, s.mature_content, u.user_id, u.username, st.status ), with_engagement AS ( SELECT *, ROUND((total_likes + total_comments)::DECIMAL / NULLIF(total_views, 0) * 100, 2) AS engagement_rate, ROUND(total_views::DECIMAL / NULLIF(total_chapters, 0), 2) AS avg_views_per_chapter, LAG(total_views) OVER (PARTITION BY story_id ORDER BY quarter) AS prev_quarter_views, LAG(total_likes) OVER (PARTITION BY story_id ORDER BY quarter) AS prev_quarter_likes, LAG(total_comments) OVER (PARTITION BY story_id ORDER BY quarter) AS prev_quarter_comments FROM quarterly_story_stats ), with_growth AS ( SELECT *, ROUND((total_views - prev_quarter_views)::DECIMAL / NULLIF(prev_quarter_views, 0) * 100, 2) AS views_growth_pct, ROUND((total_likes - prev_quarter_likes)::DECIMAL / NULLIF(prev_quarter_likes, 0) * 100, 2) AS likes_growth_pct, ROUND((total_comments - prev_quarter_comments)::DECIMAL / NULLIF(prev_quarter_comments, 0) * 100, 2) AS comments_growth_pct FROM with_engagement ) SELECT TO_CHAR(quarter, 'YYYY "Q"Q') AS period, writer, story_id, short_description, status, mature_content, total_chapters, total_words, total_genres, total_collaborators, saved_in_lists, total_views, avg_views_per_chapter, COALESCE(views_growth_pct, 0) AS views_growth_pct, total_likes, COALESCE(likes_growth_pct, 0) AS likes_growth_pct, total_comments, COALESCE(comments_growth_pct, 0) AS comments_growth_pct, COALESCE(avg_rating, 0) AS avg_rating, COALESCE(engagement_rate, 0) AS engagement_rate, RANK() OVER (PARTITION BY quarter ORDER BY total_views DESC) AS rank_by_views, RANK() OVER (PARTITION BY quarter ORDER BY engagement_rate DESC) AS rank_by_engagement, RANK() OVER (PARTITION BY quarter ORDER BY avg_rating DESC) AS rank_by_rating FROM with_growth ORDER BY quarter DESC, rank_by_views; }}} For better results I inserted this data: {{{ INSERT INTO story (user_id, short_description, mature_content, story_created_at, story_content) SELECT (SELECT user_id FROM writer ORDER BY random() LIMIT 1), 'Test story ' || g, (random() > 0.5), NOW() - (random() * INTERVAL '2 years'), 'Content for test story ' || g FROM generate_series(1, 10000) g; INSERT INTO chapter (story_id, chapter_number, chapter_name, title, chapter_content, word_count, rating, view_count, published_at) SELECT (SELECT story_id FROM story ORDER BY random() LIMIT 1), (random() * 100 + 1)::int, 'Chapter ' || g, 'Title ' || g, 'Content ' || g, (random() * 5000)::int, ROUND((random() * 4 + 1)::numeric, 2), (random() * 10000)::int, NOW() - (random() * INTERVAL '2 years') FROM generate_series(1, 500000) g ON CONFLICT DO NOTHING; INSERT INTO likes (story_id, user_id) SELECT (SELECT story_id FROM story ORDER BY random() LIMIT 1), (SELECT user_id FROM users ORDER BY random() LIMIT 1) FROM generate_series(1, 200000) g ON CONFLICT DO NOTHING; INSERT INTO comment (story_id, user_id, comment_content) SELECT (SELECT story_id FROM story ORDER BY random() LIMIT 1), (SELECT user_id FROM users ORDER BY random() LIMIT 1), 'Test comment ' || g FROM generate_series(1, 100000) g; }}} == Without index analysis {{{ |QUERY PLAN | |-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |Sort (cost=7281598552.60..7281599952.60 rows=560000 width=1539) (actual time=1315.189..1315.201 rows=5 loops=1) | | Sort Key: with_engagement.quarter DESC, (rank() OVER (?)) | | Sort Method: quicksort Memory: 26kB | | -> WindowAgg (cost=7280655711.84..7280794770.41 rows=560000 width=1539) (actual time=1315.151..1315.185 rows=5 loops=1) | | -> Incremental Sort (cost=7280655711.65..7280754170.41 rows=560000 width=1363) (actual time=1315.131..1315.142 rows=5 loops=1) | | Sort Key: with_engagement.quarter, with_engagement.total_views DESC | | Presorted Key: with_engagement.quarter | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | | -> WindowAgg (cost=7280655252.07..7280715103.02 rows=560000 width=1363) (actual time=1315.102..1315.132 rows=5 loops=1) | | -> Incremental Sort (cost=7280655251.97..7280705303.02 rows=560000 width=1355) (actual time=1315.092..1315.103 rows=5 loops=1) | | Sort Key: with_engagement.quarter, with_engagement.engagement_rate DESC | | Presorted Key: with_engagement.quarter | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | | -> WindowAgg (cost=7280655035.65..7280666235.63 rows=560000 width=1355) (actual time=1315.060..1315.091 rows=5 loops=1) | | -> Sort (cost=7280655035.63..7280656435.63 rows=560000 width=1347) (actual time=1315.048..1315.059 rows=5 loops=1) | | Sort Key: with_engagement.quarter, with_engagement.avg_rating DESC | | Sort Method: quicksort Memory: 26kB | | -> Subquery Scan on with_engagement (cost=37078922.01..7279943128.44 rows=560000 width=1347) (actual time=1315.026..1315.047 rows=5 loops=1) | | -> WindowAgg (cost=37078922.01..7279937528.44 rows=560000 width=1351) (actual time=1315.019..1315.039 rows=5 loops=1) | | -> Incremental Sort (cost=36897846.44..7279908128.44 rows=560000 width=1259) (actual time=1314.985..1314.994 rows=5 loops=1) | | Sort Key: quarterly_story_stats.story_id, quarterly_story_stats.quarter | | Presorted Key: quarterly_story_stats.story_id | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | | -> Subquery Scan on quarterly_story_stats (cost=500845.04..7279869061.05 rows=560000 width=1259) (actual time=1314.009..1314.983 rows=5 loops=1) | | -> GroupAggregate (cost=500845.04..7279863461.05 rows=560000 width=1263) (actual time=1314.003..1314.975 rows=5 loops=1) | | Group Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id | | -> Incremental Sort (cost=500845.04..7083585393.76 rows=6039023609 width=1211) (actual time=1313.282..1314.406 rows=939 loops=1) | | Sort Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id, ch.chapter_id | | Presorted Key: s.story_id, st.status | | Full-sort Groups: 5 Sort Method: quicksort Average Memory: 38kB Peak Memory: 38kB | | Pre-sorted Groups: 4 Sort Method: quicksort Average Memory: 107kB Peak Memory: 107kB | | -> Nested Loop Left Join (cost=266.68..44077127.06 rows=6039023609 width=1211) (actual time=1312.570..1313.534 rows=939 loops=1) | | -> Nested Loop Left Join (cost=266.52..922084.11 rows=187049404 width=1207) (actual time=1312.525..1312.788 rows=313 loops=1) | | -> Nested Loop Left Join (cost=266.35..36339.98 rows=7077545 width=1203) (actual time=1312.493..1312.633 rows=120 loops=1) | | -> Merge Left Join (cost=266.19..1814.11 rows=267799 width=1199) (actual time=1312.460..1312.526 rows=119 loops=1) | | Merge Cond: (s.story_id = l.story_id) | | -> Merge Left Join (cost=137.30..253.93 rows=10133 width=1195) (actual time=1312.417..1312.449 rows=27 loops=1) | | Merge Cond: (s.story_id = c.story_id) | | -> Merge Join (cost=72.81..134.31 rows=771 width=1191) (actual time=1312.375..1312.396 rows=11 loops=1) | | Merge Cond: (st.story_id = s.story_id) | | -> Index Only Scan using status_pk on status st (cost=0.15..56.25 rows=540 width=122) (actual time=0.075..0.083 rows=5 loops=1) | | Heap Fetches: 5 | | -> Sort (cost=72.66..72.91 rows=100 width=1073) (actual time=0.257..0.262 rows=11 loops=1) | | Sort Key: s.story_id | | Sort Method: quicksort Memory: 26kB | | -> Hash Left Join (cost=23.30..69.34 rows=100 width=1073) (actual time=0.212..0.231 rows=11 loops=1) | | Hash Cond: (s.story_id = ch.story_id) | | -> Nested Loop (cost=11.05..56.12 rows=70 width=1049) (actual time=0.154..0.169 rows=5 loops=1) | | -> Hash Join (cost=10.90..21.79 rows=40 width=1053) (actual time=0.117..0.123 rows=5 loops=1) | | Hash Cond: (s.user_id = u.user_id) | | -> Seq Scan on story s (cost=0.00..10.70 rows=70 width=533) (actual time=0.026..0.027 rows=5 loops=1) | | -> Hash (cost=10.40..10.40 rows=40 width=520) (actual time=0.061..0.062 rows=10 loops=1) | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | | -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=520) (actual time=0.046..0.048 rows=10 loops=1) | | -> 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)| | Index Cond: (user_id = s.user_id) | | Heap Fetches: 5 | | -> Hash (cost=11.00..11.00 rows=100 width=28) (actual time=0.032..0.033 rows=11 loops=1) | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | | -> Seq Scan on chapter ch (cost=0.00..11.00 rows=100 width=28) (actual time=0.018..0.023 rows=11 loops=1) | | -> Sort (cost=64.49..66.79 rows=920 width=8) (actual time=0.023..0.026 rows=27 loops=1) | | Sort Key: c.story_id | | Sort Method: quicksort Memory: 25kB | | -> Seq Scan on comment c (cost=0.00..19.20 rows=920 width=8) (actual time=0.016..0.018 rows=11 loops=1) | | -> Sort (cost=128.89..133.52 rows=1850 width=8) (actual time=0.025..0.033 rows=119 loops=1) | | Sort Key: l.story_id | | Sort Method: quicksort Memory: 25kB | | -> Seq Scan on likes l (cost=0.00..28.50 rows=1850 width=8) (actual time=0.015..0.017 rows=18 loops=1) | | -> Memoize (cost=0.16..14.81 rows=9 width=8) (actual time=0.001..0.001 rows=0 loops=119) | | Cache Key: s.story_id | | Cache Mode: logical | | Hits: 114 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB | | -> 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) | | Index Cond: (story_id = s.story_id) | | Heap Fetches: 2 | | -> Memoize (cost=0.16..14.81 rows=9 width=8) (actual time=0.000..0.001 rows=3 loops=120) | | Cache Key: s.story_id | | Cache Mode: logical | | Hits: 115 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB | | -> 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.003..0.005 rows=3 loops=5) | | Index Cond: (story_id = s.story_id) | | Heap Fetches: 13 | | -> Memoize (cost=0.17..1.04 rows=11 width=8) (actual time=0.001..0.001 rows=3 loops=313) | | Cache Key: s.story_id | | Cache Mode: logical | | Hits: 308 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB | | -> Index Only Scan using has_genre_pk on has_genre hg (cost=0.15..1.03 rows=11 width=8) (actual time=0.033..0.036 rows=3 loops=5) | | Index Cond: (story_id = s.story_id) | | Heap Fetches: 15 | |Planning Time: 6.006 ms | |JIT: | | Functions: 92 | | Options: Inlining true, Optimization true, Expressions true, Deforming true | | Timing: Generation 8.286 ms (Deform 4.024 ms), Inlining 33.181 ms, Optimization 758.555 ms, Emission 520.555 ms, Total 1320.577 ms | |Execution Time: 1324.013 ms | Average Execution Time over 10 runs: 1,324 ms }}} == We add more data {{{ INSERT INTO story (user_id, short_description, mature_content, story_created_at, story_content, image) SELECT (SELECT user_id FROM writer ORDER BY random() LIMIT 1), 'Test story ' || g, (random() > 0.5), NOW() - (random() * INTERVAL '2 years'), 'Content for test story ' || g, NULL FROM generate_series(1, 10000) g; INSERT INTO chapter (story_id, chapter_number, chapter_name, title, chapter_content, word_count, rating, view_count, published_at) SELECT (SELECT story_id FROM story ORDER BY random() LIMIT 1), (random() * 100 + 1)::int, 'Chapter ' || g, 'Title ' || g, 'Chapter content ' || g, (random() * 5000)::int, ROUND((random() * 4 + 1)::numeric, 2), (random() * 10000)::int, NOW() - (random() * INTERVAL '2 years') FROM generate_series(1, 500000) g ON CONFLICT DO NOTHING; INSERT INTO likes (story_id, user_id) SELECT (SELECT story_id FROM story ORDER BY random() LIMIT 1), (SELECT user_id FROM users ORDER BY random() LIMIT 1) FROM generate_series(1, 200000) g ON CONFLICT DO NOTHING; INSERT INTO comment (story_id, user_id, comment_content) SELECT (SELECT story_id FROM story ORDER BY random() LIMIT 1), (SELECT user_id FROM users ORDER BY random() LIMIT 1), 'Test comment ' || g FROM generate_series(1, 100000) g; INSERT INTO status (story_id, status) SELECT story_id, CASE (random() * 2)::int WHEN 0 THEN 'draft' WHEN 1 THEN 'published' ELSE 'archived' END FROM story ON CONFLICT DO NOTHING; }}} == Indexes for this queries {{{ CREATE INDEX idx_story_created_at ON story(story_created_at); CREATE INDEX idx_story_user_id ON story(user_id); CREATE INDEX idx_chapter_story_id ON chapter(story_id); CREATE INDEX idx_likes_story_id ON likes(story_id); CREATE INDEX idx_comment_story_id ON comment(story_id); CREATE INDEX idx_story_created_at ON story(story_created_at); CREATE INDEX idx_story_user_id ON story(user_id); CREATE INDEX idx_chapter_story_id ON chapter(story_id); CREATE INDEX idx_likes_story_id ON likes(story_id); CREATE INDEX idx_comment_story_id ON comment(story_id); }}} === After analisys we get: {{{ ANALYZE story; ANALYZE chapter; ANALYZE likes; ANALYZE comment; }}} {{{ |QUERY PLAN | |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |Sort (cost=213482.19..213732.34 rows=100061 width=928) (actual time=454.241..454.788 rows=10010 loops=1) | | Sort Key: with_engagement.quarter DESC, (rank() OVER (?)) | | Sort Method: quicksort Memory: 2419kB | | -> WindowAgg (cost=101382.95..123768.87 rows=100061 width=928) (actual time=405.334..441.326 rows=10010 loops=1) | | -> Incremental Sort (cost=101382.78..116514.44 rows=100061 width=752) (actual time=405.304..428.725 rows=10010 loops=1) | | Sort Key: with_engagement.quarter, with_engagement.total_views DESC | | Presorted Key: with_engagement.quarter | | Full-sort Groups: 9 Sort Method: quicksort Average Memory: 35kB Peak Memory: 35kB | | Pre-sorted Groups: 9 Sort Method: quicksort Average Memory: 229kB Peak Memory: 248kB | | -> WindowAgg (cost=101313.05..110773.61 rows=100061 width=752) (actual time=402.802..423.048 rows=10010 loops=1) | | -> Incremental Sort (cost=101312.95..109022.55 rows=100061 width=744) (actual time=402.786..415.676 rows=10010 loops=1) | | Sort Key: with_engagement.quarter, with_engagement.engagement_rate DESC | | Presorted Key: with_engagement.quarter | | Full-sort Groups: 9 Sort Method: quicksort Average Memory: 34kB Peak Memory: 34kB | | Pre-sorted Groups: 9 Sort Method: quicksort Average Memory: 220kB Peak Memory: 238kB | | -> WindowAgg (cost=101280.52..103281.72 rows=100061 width=744) (actual time=401.889..410.203 rows=10010 loops=1) | | -> Sort (cost=101280.50..101530.65 rows=100061 width=736) (actual time=401.846..402.632 rows=10010 loops=1) | | Sort Key: with_engagement.quarter, with_engagement.avg_rating DESC | | Sort Method: quicksort Memory: 1793kB | | -> Subquery Scan on with_engagement (cost=361.44..27982.17 rows=100061 width=736) (actual time=73.176..395.082 rows=10010 loops=1) | | -> WindowAgg (cost=361.44..26981.56 rows=100061 width=740) (actual time=73.169..393.830 rows=10010 loops=1) | | -> Incremental Sort (cost=360.73..21728.36 rows=100061 width=648) (actual time=73.134..382.401 rows=10010 loops=1) | | Sort Key: quarterly_story_stats.story_id, quarterly_story_stats.quarter | | Presorted Key: quarterly_story_stats.story_id | | Full-sort Groups: 313 Sort Method: quicksort Average Memory: 30kB Peak Memory: 30kB | | -> Subquery Scan on quarterly_story_stats (cost=259.66..15987.53 rows=100061 width=648) (actual time=70.134..377.285 rows=10010 loops=1) | | -> GroupAggregate (cost=259.66..14986.92 rows=100061 width=652) (actual time=70.128..375.785 rows=10010 loops=1) | | Group Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id | | -> Incremental Sort (cost=259.66..9983.87 rows=100061 width=594) (actual time=69.186..287.190 rows=111977 loops=1) | | Sort Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id, ch.chapter_id | | Presorted Key: s.story_id, st.status | | Full-sort Groups: 323 Sort Method: quicksort Average Memory: 38kB Peak Memory: 38kB | | Pre-sorted Groups: 36 Sort Methods: quicksort, external merge Average Memory: 98kB Peak Memory: 107kB Average Disk: 637kB Peak Disk: 7648kB | | -> Merge Left Join (cost=259.40..7263.71 rows=100061 width=594) (actual time=68.586..192.729 rows=111977 loops=1) | | Merge Cond: (s.story_id = rli.story_id) | | -> Merge Left Join (cost=130.51..6356.99 rows=100061 width=590) (actual time=68.536..150.448 rows=110819 loops=1) | | Merge Cond: (s.story_id = hg.story_id) | | -> Merge Left Join (cost=130.35..5685.75 rows=100061 width=586) (actual time=68.503..133.749 rows=110339 loops=1) | | Merge Cond: (s.story_id = c.story_id) | | -> Merge Left Join (cost=130.06..1622.11 rows=10010 width=582) (actual time=68.468..91.605 rows=10192 loops=1) | | Merge Cond: (s.story_id = col.story_id) | | -> Merge Left Join (cost=1.17..1440.43 rows=10010 width=578) (actual time=68.417..90.052 rows=10190 loops=1) | | Merge Cond: (s.story_id = l.story_id) | | -> Merge Left Join (cost=1.03..1402.74 rows=10010 width=574) (actual time=68.391..88.255 rows=10122 loops=1) | | Merge Cond: (s.story_id = ch.story_id) | | -> Nested Loop (cost=0.89..1360.49 rows=10010 width=556) (actual time=68.350..86.481 rows=10010 loops=1) | | -> Nested Loop (cost=0.74..1113.00 rows=10010 width=44) (actual time=68.315..81.875 rows=10010 loops=1) | | -> Merge Join (cost=0.57..861.93 rows=10010 width=40) (actual time=68.273..76.998 rows=10010 loops=1) | | Merge Cond: (st.story_id = s.story_id) | | -> Index Only Scan using status_pk on status st (cost=0.29..314.44 rows=10010 width=12) (actual time=0.023..1.895 rows=10010 loops=1)| | Heap Fetches: 0 | | -> Index Scan using story_pkey on story s (cost=0.29..397.36 rows=10005 width=32) (actual time=0.042..2.933 rows=10005 loops=1) | | -> Memoize (cost=0.17..0.19 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=10010) | | Cache Key: s.user_id | | Cache Mode: logical | | Hits: 10006 Misses: 4 Evictions: 0 Overflows: 0 Memory Usage: 1kB | | -> Index Only Scan using writer_pkey on writer w (cost=0.15..0.18 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=4) | | Index Cond: (user_id = s.user_id) | | Heap Fetches: 4 | | -> Memoize (cost=0.15..0.17 rows=1 width=520) (actual time=0.000..0.000 rows=1 loops=10010) | | Cache Key: s.user_id | | Cache Mode: logical | | Hits: 10006 Misses: 4 Evictions: 0 Overflows: 0 Memory Usage: 1kB | | -> Index Scan using users_pkey on users u (cost=0.14..0.16 rows=1 width=520) (actual time=0.005..0.005 rows=1 loops=4) | | Index Cond: (user_id = s.user_id) | | -> Index Scan using idx_chapter_story_id on chapter ch (cost=0.14..15.83 rows=112 width=22) (actual time=0.020..0.070 rows=123 loops=1) | | -> Index Scan using idx_likes_story_id on likes l (cost=0.14..12.42 rows=19 width=8) (actual time=0.009..0.036 rows=91 loops=1) | | -> Sort (cost=128.89..133.52 rows=1850 width=8) (actual time=0.035..0.036 rows=3 loops=1) | | Sort Key: col.story_id | | Sort Method: quicksort Memory: 25kB | | -> Seq Scan on collaboration col (cost=0.00..28.50 rows=1850 width=8) (actual time=0.023..0.025 rows=2 loops=1) | | -> Index Scan using idx_comment_story_id on comment c (cost=0.29..2786.46 rows=100011 width=8) (actual time=0.017..22.307 rows=100240 loops=1) | | -> Materialize (cost=0.15..87.71 rows=2260 width=8) (actual time=0.015..0.070 rows=717 loops=1) | | -> Index Only Scan using has_genre_pk on has_genre hg (cost=0.15..82.06 rows=2260 width=8) (actual time=0.012..0.023 rows=15 loops=1) | | Heap Fetches: 15 | | -> Sort (cost=128.89..133.52 rows=1850 width=8) (actual time=0.024..0.130 rows=1867 loops=1) | | Sort Key: rli.story_id | | Sort Method: quicksort Memory: 25kB | | -> Seq Scan on reading_list_items rli (cost=0.00..28.50 rows=1850 width=8) (actual time=0.015..0.017 rows=13 loops=1) | |Planning Time: 6.751 ms | |JIT: | | Functions: 85 | | Options: Inlining false, Optimization false, Expressions true, Deforming true | | Timing: Generation 7.719 ms (Deform 3.719 ms), Inlining 0.000 ms, Optimization 2.798 ms, Emission 65.672 ms, Total 76.189 ms | |Execution Time: 465.217 ms | Average Execution Time over 10 runs: 465 ms }}} We conclude that the performance of the query is significantly better with these indexes and we keep them. The overall improvement is ~65% (2.8× faster), dropping from 1,324 ms to 465 ms. == Scenario 2 - Annual genre popularity and engagement trend === Without index analysis {{{ |QUERY PLAN | |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |Sort (cost=188601.52..189051.70 rows=180072 width=296) (actual time=735.924..735.941 rows=19 loops=1) | | Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?)) | | Sort Method: quicksort Memory: 27kB | | -> WindowAgg (cost=123725.89..148260.34 rows=180072 width=296) (actual time=735.837..735.903 rows=19 loops=1) | | -> Incremental Sort (cost=123725.80..138356.38 rows=180072 width=176) (actual time=735.816..735.833 rows=19 loops=1) | | Sort Key: with_metrics.year, with_metrics.total_views DESC | | Presorted Key: with_metrics.year | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB | | -> WindowAgg (cost=123663.61..127265.03 rows=180072 width=176) (actual time=735.768..735.813 rows=19 loops=1) | | -> Sort (cost=123663.59..124113.77 rows=180072 width=168) (actual time=735.756..735.773 rows=19 loops=1) | | Sort Key: with_metrics.year, with_metrics.engagement_rate DESC | | Sort Method: quicksort Memory: 27kB | | -> Subquery Scan on with_metrics (cost=81917.02..93171.41 rows=180072 width=168) (actual time=735.680..735.750 rows=19 loops=1) | | -> WindowAgg (cost=81917.02..91370.69 rows=180072 width=180) (actual time=735.674..735.739 rows=19 loops=1) | | -> Sort (cost=81916.91..82367.09 rows=180072 width=92) (actual time=735.611..735.627 rows=19 loops=1) | | Sort Key: genre_annual.genre_id, genre_annual.year | | Sort Method: quicksort Memory: 26kB | | -> Subquery Scan on genre_annual (cost=45259.58..56965.23 rows=180072 width=92) (actual time=474.888..735.603 rows=19 loops=1) | | -> GroupAggregate (cost=45259.58..55164.51 rows=180072 width=100) (actual time=474.881..735.582 rows=19 loops=1) | | Group Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id | | -> Sort (cost=45259.58..46009.99 rows=300163 width=46) (actual time=474.740..592.149 rows=330994 loops=1) | | Sort Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id, s.story_id | | Sort Method: external merge Disk: 17832kB | | -> Hash Right Join (cost=2402.96..8715.17 rows=300163 width=46) (actual time=104.929..272.256 rows=330994 loops=1) | | Hash Cond: (c.story_id = s.story_id) | | -> Seq Scan on comment c (cost=0.00..1935.11 rows=100011 width=8) (actual time=0.008..8.474 rows=100011 loops=1) | | -> Hash (cost=2027.61..2027.61 rows=30028 width=42) (actual time=104.842..104.854 rows=30561 loops=1) | | Buckets: 32768 Batches: 1 Memory Usage: 2253kB | | -> Hash Join (cost=1084.06..2027.61 rows=30028 width=42) (actual time=76.783..95.747 rows=30561 loops=1) | | Hash Cond: (hg.genre_id = g.genre_id) | | -> Hash Join (cost=1082.66..1928.82 rows=30028 width=34) (actual time=16.868..29.358 rows=30561 loops=1) | | Hash Cond: (hg.story_id = s.story_id) | | -> 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) | | -> Hash (cost=957.59..957.59 rows=10005 width=34) (actual time=16.819..16.828 rows=10145 loops=1) | | Buckets: 16384 Batches: 1 Memory Usage: 645kB | | -> Hash Left Join (cost=812.87..957.59 rows=10005 width=34) (actual time=7.357..14.471 rows=10145 loops=1) | | Hash Cond: (s.story_id = l.story_id) | | -> Hash Join (cost=811.44..868.43 rows=10005 width=30) (actual time=7.310..12.546 rows=10111 loops=1) | | Hash Cond: (s.story_id = st.story_id) | | -> Hash Join (cost=416.49..447.21 rows=10005 width=26) (actual time=3.835..7.095 rows=10111 loops=1) | | Hash Cond: (s.user_id = w.user_id) | | -> Hash Right Join (cost=349.11..353.53 rows=10005 width=26) (actual time=3.787..5.295 rows=10111 loops=1) | | Hash Cond: (ch.story_id = s.story_id) | | -> Seq Scan on chapter ch (cost=0.00..4.12 rows=112 width=14) (actual time=0.011..0.028 rows=112 loops=1) | | -> Hash (cost=224.05..224.05 rows=10005 width=16) (actual time=3.728..3.729 rows=10005 loops=1) | | Buckets: 16384 Batches: 1 Memory Usage: 597kB | | -> Seq Scan on story s (cost=0.00..224.05 rows=10005 width=16) (actual time=0.018..1.867 rows=10005 loops=1)| | -> Hash (cost=35.50..35.50 rows=2550 width=4) (actual time=0.027..0.028 rows=5 loops=1) | | Buckets: 4096 Batches: 1 Memory Usage: 33kB | | -> Seq Scan on writer w (cost=0.00..35.50 rows=2550 width=4) (actual time=0.011..0.013 rows=5 loops=1) | | -> Hash (cost=269.89..269.89 rows=10005 width=4) (actual time=3.443..3.443 rows=10005 loops=1) | | Buckets: 16384 Batches: 1 Memory Usage: 480kB | | -> Seq Scan on status st (cost=0.00..269.89 rows=10005 width=4) (actual time=0.031..2.005 rows=10005 loops=1) | | Filter: ((status)::text = 'published'::text) | | Rows Removed by Filter: 5026 | | -> Hash (cost=1.19..1.19 rows=19 width=8) (actual time=0.025..0.026 rows=19 loops=1) | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | | -> Seq Scan on likes l (cost=0.00..1.19 rows=19 width=8) (actual time=0.013..0.016 rows=19 loops=1) | | -> Hash (cost=1.18..1.18 rows=18 width=12) (actual time=59.895..59.896 rows=18 loops=1) | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | | -> Seq Scan on genre g (cost=0.00..1.18 rows=18 width=12) (actual time=0.017..0.020 rows=18 loops=1) | |Planning Time: 8.265 ms | |JIT: | | Functions: 84 | | Options: Inlining false, Optimization false, Expressions true, Deforming true | | 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 | |Execution Time: 747.006 ms | }}} Average time without indexes is: 749 ms === Indexes for this queries {{{ CREATE INDEX idx_has_genre_genre_id ON has_genre(genre_id); CREATE INDEX idx_has_genre_story_id ON has_genre(story_id); CREATE INDEX idx_status_story_published ON status(story_id) WHERE status = 'published'; }}} === After analisys we get: {{{ ANALYZE genre; ANALYZE has_genre; ANALYZE status; ANALYZE story; ANALYZE chapter; ANALYZE likes; ANALYZE comment; }}} {{{ |QUERY PLAN | |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |Sort (cost=188601.52..189051.70 rows=180072 width=296) (actual time=731.664..731.683 rows=19 loops=1) | | Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?)) | | Sort Method: quicksort Memory: 27kB | | -> WindowAgg (cost=123725.89..148260.34 rows=180072 width=296) (actual time=731.578..731.645 rows=19 loops=1) | | -> Incremental Sort (cost=123725.80..138356.38 rows=180072 width=176) (actual time=731.558..731.576 rows=19 loops=1) | | Sort Key: with_metrics.year, with_metrics.total_views DESC | | Presorted Key: with_metrics.year | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB | | -> WindowAgg (cost=123663.61..127265.03 rows=180072 width=176) (actual time=731.509..731.556 rows=19 loops=1) | | -> Sort (cost=123663.59..124113.77 rows=180072 width=168) (actual time=731.498..731.515 rows=19 loops=1) | | Sort Key: with_metrics.year, with_metrics.engagement_rate DESC | | Sort Method: quicksort Memory: 27kB | | -> Subquery Scan on with_metrics (cost=81917.02..93171.41 rows=180072 width=168) (actual time=731.423..731.493 rows=19 loops=1) | | -> WindowAgg (cost=81917.02..91370.69 rows=180072 width=180) (actual time=731.416..731.482 rows=19 loops=1) | | -> Sort (cost=81916.91..82367.09 rows=180072 width=92) (actual time=731.353..731.369 rows=19 loops=1) | | Sort Key: genre_annual.genre_id, genre_annual.year | | Sort Method: quicksort Memory: 26kB | | -> Subquery Scan on genre_annual (cost=45259.58..56965.23 rows=180072 width=92) (actual time=470.799..731.345 rows=19 loops=1) | | -> GroupAggregate (cost=45259.58..55164.51 rows=180072 width=100) (actual time=470.793..731.331 rows=19 loops=1) | | Group Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id | | -> Sort (cost=45259.58..46009.99 rows=300163 width=46) (actual time=470.650..587.960 rows=330994 loops=1) | | Sort Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id, s.story_id | | Sort Method: external merge Disk: 17832kB | | -> Hash Right Join (cost=2402.96..8715.17 rows=300163 width=46) (actual time=102.927..268.242 rows=330994 loops=1) | | Hash Cond: (c.story_id = s.story_id) | | -> Seq Scan on comment c (cost=0.00..1935.11 rows=100011 width=8) (actual time=0.008..8.499 rows=100011 loops=1) | | -> Hash (cost=2027.61..2027.61 rows=30028 width=42) (actual time=102.841..102.853 rows=30561 loops=1) | | Buckets: 32768 Batches: 1 Memory Usage: 2253kB | | -> Hash Join (cost=1084.06..2027.61 rows=30028 width=42) (actual time=74.863..93.838 rows=30561 loops=1) | | Hash Cond: (hg.genre_id = g.genre_id) | | -> Hash Join (cost=1082.66..1928.82 rows=30028 width=34) (actual time=16.771..29.144 rows=30561 loops=1) | | Hash Cond: (hg.story_id = s.story_id) | | -> 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) | | -> Hash (cost=957.59..957.59 rows=10005 width=34) (actual time=16.724..16.733 rows=10145 loops=1) | | Buckets: 16384 Batches: 1 Memory Usage: 645kB | | -> Hash Left Join (cost=812.87..957.59 rows=10005 width=34) (actual time=7.297..14.431 rows=10145 loops=1) | | Hash Cond: (s.story_id = l.story_id) | | -> Hash Join (cost=811.44..868.43 rows=10005 width=30) (actual time=7.250..12.492 rows=10111 loops=1) | | Hash Cond: (s.story_id = st.story_id) | | -> Hash Join (cost=416.49..447.21 rows=10005 width=26) (actual time=3.788..7.039 rows=10111 loops=1) | | Hash Cond: (s.user_id = w.user_id) | | -> Hash Right Join (cost=349.11..353.53 rows=10005 width=26) (actual time=3.743..5.203 rows=10111 loops=1) | | Hash Cond: (ch.story_id = s.story_id) | | -> Seq Scan on chapter ch (cost=0.00..4.12 rows=112 width=14) (actual time=0.011..0.028 rows=112 loops=1) | | -> Hash (cost=224.05..224.05 rows=10005 width=16) (actual time=3.685..3.686 rows=10005 loops=1) | | Buckets: 16384 Batches: 1 Memory Usage: 597kB | | -> Seq Scan on story s (cost=0.00..224.05 rows=10005 width=16) (actual time=0.017..1.835 rows=10005 loops=1)| | -> Hash (cost=35.50..35.50 rows=2550 width=4) (actual time=0.024..0.026 rows=5 loops=1) | | Buckets: 4096 Batches: 1 Memory Usage: 33kB | | -> Seq Scan on writer w (cost=0.00..35.50 rows=2550 width=4) (actual time=0.010..0.012 rows=5 loops=1) | | -> Hash (cost=269.89..269.89 rows=10005 width=4) (actual time=3.426..3.427 rows=10005 loops=1) | | Buckets: 16384 Batches: 1 Memory Usage: 480kB | | -> Seq Scan on status st (cost=0.00..269.89 rows=10005 width=4) (actual time=0.029..1.999 rows=10005 loops=1) | | Filter: ((status)::text = 'published'::text) | | Rows Removed by Filter: 5026 | | -> Hash (cost=1.19..1.19 rows=19 width=8) (actual time=0.025..0.026 rows=19 loops=1) | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | | -> Seq Scan on likes l (cost=0.00..1.19 rows=19 width=8) (actual time=0.012..0.016 rows=19 loops=1) | | -> Hash (cost=1.18..1.18 rows=18 width=12) (actual time=58.071..58.072 rows=18 loops=1) | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | | -> Seq Scan on genre g (cost=0.00..1.18 rows=18 width=12) (actual time=0.015..0.018 rows=18 loops=1) | |Planning Time: 8.416 ms | |JIT: | | Functions: 84 | | Options: Inlining false, Optimization false, Expressions true, Deforming true | | 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 | |Execution Time: 742.778 ms | }}} Average time: 746.354 ms 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. == Scenario 3 — Quarterly writer performance report {{{ EXPLAIN ANALYZE WITH quarterly_stats AS ( SELECT DATE_TRUNC('quarter', s.story_created_at) AS quarter, u.user_id, u.username, u.user_name, u.surname, COUNT(DISTINCT s.story_id) AS stories_published, COUNT(DISTINCT ch.chapter_id) AS chapters_written, COALESCE(SUM(ch.view_count), 0) AS total_views, COALESCE(SUM(ch.word_count), 0) AS total_words, COUNT(DISTINCT l.user_id) AS total_likes, COUNT(DISTINCT c.comment_id) AS total_comments, ROUND(AVG(ch.rating), 2) AS avg_rating FROM story s JOIN writer w ON s.user_id = w.user_id JOIN users u ON w.user_id = u.user_id JOIN status st ON s.story_id = st.story_id AND st.status = 'published' LEFT JOIN chapter ch ON s.story_id = ch.story_id LEFT JOIN likes l ON s.story_id = l.story_id LEFT JOIN comment c ON s.story_id = c.story_id GROUP BY DATE_TRUNC('quarter', s.story_created_at), u.user_id, u.username, u.user_name, u.surname ), with_growth AS ( SELECT *, LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter) AS prev_views, LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter) AS prev_likes, LAG(total_comments) OVER (PARTITION BY user_id ORDER BY quarter) AS prev_comments, ROUND((total_views - LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter))::DECIMAL / NULLIF(LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter), 0) * 100, 2) AS views_growth_pct, ROUND((total_likes - LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter))::DECIMAL / NULLIF(LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter), 0) * 100, 2) AS likes_growth_pct FROM quarterly_stats ) SELECT TO_CHAR(quarter, 'YYYY "Q"Q') AS period, username, user_name, surname, stories_published, chapters_written, total_words, total_views, COALESCE(views_growth_pct, 0) AS views_growth_pct, total_likes, COALESCE(likes_growth_pct, 0) AS likes_growth_pct, total_comments, COALESCE(avg_rating, 0) AS avg_rating, RANK() OVER (PARTITION BY quarter ORDER BY total_views DESC) AS rank_by_views FROM with_growth ORDER BY quarter DESC, rank_by_views; }}} === Analysis without indexes: {{{ |QUERY PLAN | |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |Sort (cost=295447.54..295697.57 rows=100011 width=1144) (actual time=380.965..380.977 rows=14 loops=1) | | Sort Key: with_growth.quarter DESC, (rank() OVER (?)) | | Sort Method: quicksort Memory: 26kB | | -> WindowAgg (cost=185071.50..187321.72 rows=100011 width=1144) (actual time=380.907..380.958 rows=14 loops=1) | | -> Sort (cost=185071.48..185321.50 rows=100011 width=1104) (actual time=380.856..380.868 rows=14 loops=1) | | Sort Key: with_growth.quarter, with_growth.total_views DESC | | Sort Method: quicksort Memory: 26kB | | -> Subquery Scan on with_growth (cost=28996.63..80361.66 rows=100011 width=1104) (actual time=160.911..380.850 rows=14 loops=1) | | -> WindowAgg (cost=28996.63..79361.55 rows=100011 width=1132) (actual time=160.905..380.839 rows=14 loops=1) | | -> GroupAggregate (cost=28995.22..73860.95 rows=100011 width=1044) (actual time=160.788..380.735 rows=14 loops=1) | | Group Key: u.user_id, (date_trunc('quarter'::text, s.story_created_at)) | | -> Incremental Sort (cost=28995.22..69860.51 rows=100011 width=994) (actual time=160.712..342.505 rows=110218 loops=1) | | Sort Key: u.user_id, (date_trunc('quarter'::text, s.story_created_at)), s.story_id | | Presorted Key: u.user_id | | Full-sort Groups: 3 Sort Method: quicksort Average Memory: 30kB Peak Memory: 30kB | | Pre-sorted Groups: 2 Sort Methods: quicksort, external merge Average Memory: 13kB Peak Memory: 26kB Average Disk: 3760kB Peak Disk: 7520kB | | -> Merge Join (cost=15790.20..17540.59 rows=100011 width=994) (actual time=160.635..229.966 rows=110218 loops=1) | | Merge Cond: (s.user_id = u.user_id) | | -> Sort (cost=15778.73..16028.76 rows=100011 width=46) (actual time=160.547..175.009 rows=110218 loops=1) | | Sort Key: s.user_id | | Sort Method: external merge Disk: 4504kB | | -> Hash Right Join (cost=1082.66..4392.92 rows=100011 width=46) (actual time=74.630..114.895 rows=110218 loops=1) | | Hash Cond: (c.story_id = s.story_id) | | -> Seq Scan on comment c (cost=0.00..1935.11 rows=100011 width=8) (actual time=0.011..8.173 rows=100011 loops=1) | | -> Hash (cost=957.59..957.59 rows=10005 width=42) (actual time=74.569..74.577 rows=10145 loops=1) | | Buckets: 16384 Batches: 1 Memory Usage: 726kB | | -> Hash Left Join (cost=812.87..957.59 rows=10005 width=42) (actual time=64.682..71.998 rows=10145 loops=1) | | Hash Cond: (s.story_id = l.story_id) | | -> Hash Join (cost=811.44..868.43 rows=10005 width=38) (actual time=64.638..70.028 rows=10111 loops=1) | | Hash Cond: (s.story_id = st.story_id) | | -> Hash Join (cost=416.49..447.21 rows=10005 width=38) (actual time=3.787..7.183 rows=10111 loops=1) | | Hash Cond: (s.user_id = w.user_id) | | -> Hash Right Join (cost=349.11..353.53 rows=10005 width=34) (actual time=3.736..5.286 rows=10111 loops=1) | | Hash Cond: (ch.story_id = s.story_id) | | -> Seq Scan on chapter ch (cost=0.00..4.12 rows=112 width=22) (actual time=0.012..0.030 rows=112 loops=1) | | -> Hash (cost=224.05..224.05 rows=10005 width=16) (actual time=3.677..3.678 rows=10005 loops=1) | | Buckets: 16384 Batches: 1 Memory Usage: 597kB | | -> Seq Scan on story s (cost=0.00..224.05 rows=10005 width=16) (actual time=0.017..1.823 rows=10005 loops=1)| | -> Hash (cost=35.50..35.50 rows=2550 width=4) (actual time=0.025..0.026 rows=5 loops=1) | | Buckets: 4096 Batches: 1 Memory Usage: 33kB | | -> Seq Scan on writer w (cost=0.00..35.50 rows=2550 width=4) (actual time=0.010..0.012 rows=5 loops=1) | | -> Hash (cost=269.89..269.89 rows=10005 width=4) (actual time=60.813..60.813 rows=10005 loops=1) | | Buckets: 16384 Batches: 1 Memory Usage: 480kB | | -> Seq Scan on status st (cost=0.00..269.89 rows=10005 width=4) (actual time=57.194..59.348 rows=10005 loops=1) | | Filter: ((status)::text = 'published'::text) | | Rows Removed by Filter: 5026 | | -> Hash (cost=1.19..1.19 rows=19 width=8) (actual time=0.024..0.025 rows=19 loops=1) | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | | -> Seq Scan on likes l (cost=0.00..1.19 rows=19 width=8) (actual time=0.012..0.015 rows=19 loops=1) | | -> Sort (cost=11.46..11.56 rows=40 width=956) (actual time=0.043..0.044 rows=9 loops=1) | | Sort Key: u.user_id | | Sort Method: quicksort Memory: 25kB | | -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=956) (actual time=0.022..0.025 rows=10 loops=1) | |Planning Time: 4.139 ms | |JIT: | | Functions: 75 | | Options: Inlining false, Optimization false, Expressions true, Deforming true | | Timing: Generation 5.977 ms (Deform 3.329 ms), Inlining 0.000 ms, Optimization 2.386 ms, Emission 55.005 ms, Total 63.368 ms | |Execution Time: 390.828 ms | }}} === We create indexes {{{ CREATE INDEX idx_story_quarter ON story(story_created_at, user_id, story_id); ANALYZE story; ANALYZE status; ANALYZE chapter; ANALYZE likes; ANALYZE comment; }}} === After indexes we get: {{{ |QUERY PLAN | |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |Sort (cost=295447.54..295697.57 rows=100011 width=1144) (actual time=385.005..385.018 rows=14 loops=1) | | Sort Key: with_growth.quarter DESC, (rank() OVER (?)) | | Sort Method: quicksort Memory: 26kB | | -> WindowAgg (cost=185071.50..187321.72 rows=100011 width=1144) (actual time=384.949..384.998 rows=14 loops=1) | | -> Sort (cost=185071.48..185321.50 rows=100011 width=1104) (actual time=384.909..384.921 rows=14 loops=1) | | Sort Key: with_growth.quarter, with_growth.total_views DESC | | Sort Method: quicksort Memory: 26kB | | -> Subquery Scan on with_growth (cost=28996.63..80361.66 rows=100011 width=1104) (actual time=164.271..384.904 rows=14 loops=1) | | -> WindowAgg (cost=28996.63..79361.55 rows=100011 width=1132) (actual time=164.265..384.892 rows=14 loops=1) | | -> GroupAggregate (cost=28995.22..73860.95 rows=100011 width=1044) (actual time=164.169..384.779 rows=14 loops=1) | | Group Key: u.user_id, (date_trunc('quarter'::text, s.story_created_at)) | | -> Incremental Sort (cost=28995.22..69860.51 rows=100011 width=994) (actual time=164.095..346.459 rows=110218 loops=1) | | Sort Key: u.user_id, (date_trunc('quarter'::text, s.story_created_at)), s.story_id | | Presorted Key: u.user_id | | Full-sort Groups: 3 Sort Method: quicksort Average Memory: 30kB Peak Memory: 30kB | | Pre-sorted Groups: 2 Sort Methods: quicksort, external merge Average Memory: 13kB Peak Memory: 26kB Average Disk: 3760kB Peak Disk: 7520kB | | -> Merge Join (cost=15790.20..17540.59 rows=100011 width=994) (actual time=164.019..233.324 rows=110218 loops=1) | | Merge Cond: (s.user_id = u.user_id) | | -> Sort (cost=15778.73..16028.76 rows=100011 width=46) (actual time=163.944..178.489 rows=110218 loops=1) | | Sort Key: s.user_id | | Sort Method: external merge Disk: 4504kB | | -> Hash Right Join (cost=1082.66..4392.92 rows=100011 width=46) (actual time=76.636..117.141 rows=110218 loops=1) | | Hash Cond: (c.story_id = s.story_id) | | -> Seq Scan on comment c (cost=0.00..1935.11 rows=100011 width=8) (actual time=0.010..8.251 rows=100011 loops=1) | | -> Hash (cost=957.59..957.59 rows=10005 width=42) (actual time=76.575..76.583 rows=10145 loops=1) | | Buckets: 16384 Batches: 1 Memory Usage: 726kB | | -> Hash Left Join (cost=812.87..957.59 rows=10005 width=42) (actual time=66.657..73.978 rows=10145 loops=1) | | Hash Cond: (s.story_id = l.story_id) | | -> Hash Join (cost=811.44..868.43 rows=10005 width=38) (actual time=66.611..71.975 rows=10111 loops=1) | | Hash Cond: (s.story_id = st.story_id) | | -> Hash Join (cost=416.49..447.21 rows=10005 width=38) (actual time=3.840..7.192 rows=10111 loops=1) | | Hash Cond: (s.user_id = w.user_id) | | -> Hash Right Join (cost=349.11..353.53 rows=10005 width=34) (actual time=3.785..5.305 rows=10111 loops=1) | | Hash Cond: (ch.story_id = s.story_id) | | -> Seq Scan on chapter ch (cost=0.00..4.12 rows=112 width=22) (actual time=0.011..0.028 rows=112 loops=1) | | -> Hash (cost=224.05..224.05 rows=10005 width=16) (actual time=3.725..3.726 rows=10005 loops=1) | | Buckets: 16384 Batches: 1 Memory Usage: 597kB | | -> Seq Scan on story s (cost=0.00..224.05 rows=10005 width=16) (actual time=0.018..1.860 rows=10005 loops=1)| | -> Hash (cost=35.50..35.50 rows=2550 width=4) (actual time=0.029..0.030 rows=5 loops=1) | | Buckets: 4096 Batches: 1 Memory Usage: 33kB | | -> Seq Scan on writer w (cost=0.00..35.50 rows=2550 width=4) (actual time=0.012..0.013 rows=5 loops=1) | | -> Hash (cost=269.89..269.89 rows=10005 width=4) (actual time=62.733..62.734 rows=10005 loops=1) | | Buckets: 16384 Batches: 1 Memory Usage: 480kB | | -> Seq Scan on status st (cost=0.00..269.89 rows=10005 width=4) (actual time=59.051..61.260 rows=10005 loops=1) | | Filter: ((status)::text = 'published'::text) | | Rows Removed by Filter: 5026 | | -> Hash (cost=1.19..1.19 rows=19 width=8) (actual time=0.025..0.026 rows=19 loops=1) | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | | -> Seq Scan on likes l (cost=0.00..1.19 rows=19 width=8) (actual time=0.012..0.016 rows=19 loops=1) | | -> Sort (cost=11.46..11.56 rows=40 width=956) (actual time=0.032..0.033 rows=9 loops=1) | | Sort Key: u.user_id | | Sort Method: quicksort Memory: 25kB | | -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=956) (actual time=0.021..0.023 rows=10 loops=1) | |Planning Time: 4.181 ms | |JIT: | | Functions: 75 | | Options: Inlining false, Optimization false, Expressions true, Deforming true | | Timing: Generation 5.996 ms (Deform 3.374 ms), Inlining 0.000 ms, Optimization 2.381 ms, Emission 56.875 ms, Total 65.252 ms | |Execution Time: 394.812 ms | }}} Average time: 396.791 ms We conclude that the indexes added for this query provided no meaningful improvement, with execution time remaining virtually unchanged from 393 ms to 397 ms (~+1%), which is within normal measurement variance. The query plan reveals two core issues: sort operations spilling to disk (up to 7.5 MB), and several tables (chapter, likes, writer) being too small for index scans to be beneficial. The indexes are therefore not kept, as they introduce write overhead without any measurable read benefit. }}} }}}