| 9 | | == Scenario 1 — Quarterly Story Performance Dashboard |
| 10 | | |
| 11 | | 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. |
| 12 | | |
| 13 | | {{{ |
| 14 | | WITH quarterly_story_stats AS ( |
| 15 | | SELECT |
| 16 | | DATE_TRUNC('quarter', s.story_created_at) AS quarter, |
| 17 | | s.story_id, |
| 18 | | s.short_description, |
| 19 | | s.mature_content, |
| 20 | | u.user_id, |
| 21 | | u.username AS writer, |
| 22 | | st.status, |
| 23 | | COUNT(DISTINCT ch.chapter_id) AS total_chapters, |
| 24 | | COALESCE(SUM(ch.view_count), 0) AS total_views, |
| 25 | | COALESCE(SUM(ch.word_count), 0) AS total_words, |
| 26 | | ROUND(AVG(ch.rating), 2) AS avg_rating, |
| 27 | | COUNT(DISTINCT l.user_id) AS total_likes, |
| 28 | | COUNT(DISTINCT c.comment_id) AS total_comments, |
| 29 | | COUNT(DISTINCT col.user_id) AS total_collaborators, |
| 30 | | COUNT(DISTINCT hg.genre_id) AS total_genres, |
| 31 | | COUNT(DISTINCT rli.list_id) AS saved_in_lists |
| 32 | | FROM story s |
| 33 | | JOIN writer w ON s.user_id = w.user_id |
| 34 | | JOIN users u ON w.user_id = u.user_id |
| 35 | | JOIN status st ON s.story_id = st.story_id |
| 36 | | LEFT JOIN chapter ch ON s.story_id = ch.story_id |
| 37 | | LEFT JOIN likes l ON s.story_id = l.story_id |
| 38 | | LEFT JOIN comment c ON s.story_id = c.story_id |
| 39 | | LEFT JOIN collaboration col ON s.story_id = col.story_id |
| 40 | | LEFT JOIN has_genre hg ON s.story_id = hg.story_id |
| 41 | | LEFT JOIN reading_list_items rli ON s.story_id = rli.story_id |
| 42 | | GROUP BY |
| 43 | | DATE_TRUNC('quarter', s.story_created_at), |
| 44 | | s.story_id, s.short_description, s.mature_content, |
| 45 | | u.user_id, u.username, st.status |
| 46 | | ), |
| 47 | | with_engagement AS ( |
| 48 | | SELECT *, |
| 49 | | ROUND((total_likes + total_comments)::DECIMAL / NULLIF(total_views, 0) * 100, 2) AS engagement_rate, |
| 50 | | ROUND(total_views::DECIMAL / NULLIF(total_chapters, 0), 2) AS avg_views_per_chapter, |
| 51 | | LAG(total_views) OVER (PARTITION BY story_id ORDER BY quarter) AS prev_quarter_views, |
| 52 | | LAG(total_likes) OVER (PARTITION BY story_id ORDER BY quarter) AS prev_quarter_likes, |
| 53 | | LAG(total_comments) OVER (PARTITION BY story_id ORDER BY quarter) AS prev_quarter_comments |
| 54 | | FROM quarterly_story_stats |
| 55 | | ), |
| 56 | | with_growth AS ( |
| 57 | | SELECT *, |
| 58 | | ROUND((total_views - prev_quarter_views)::DECIMAL / NULLIF(prev_quarter_views, 0) * 100, 2) AS views_growth_pct, |
| 59 | | ROUND((total_likes - prev_quarter_likes)::DECIMAL / NULLIF(prev_quarter_likes, 0) * 100, 2) AS likes_growth_pct, |
| 60 | | ROUND((total_comments - prev_quarter_comments)::DECIMAL / NULLIF(prev_quarter_comments, 0) * 100, 2) AS comments_growth_pct |
| 61 | | FROM with_engagement |
| 62 | | ) |
| 63 | | SELECT |
| 64 | | TO_CHAR(quarter, 'YYYY "Q"Q') AS period, |
| 65 | | writer, story_id, short_description, status, mature_content, |
| 66 | | total_chapters, total_words, total_genres, total_collaborators, saved_in_lists, |
| 67 | | total_views, avg_views_per_chapter, |
| 68 | | COALESCE(views_growth_pct, 0) AS views_growth_pct, |
| 69 | | total_likes, |
| 70 | | COALESCE(likes_growth_pct, 0) AS likes_growth_pct, |
| 71 | | total_comments, |
| 72 | | COALESCE(comments_growth_pct, 0) AS comments_growth_pct, |
| 73 | | COALESCE(avg_rating, 0) AS avg_rating, |
| 74 | | COALESCE(engagement_rate, 0) AS engagement_rate, |
| 75 | | RANK() OVER (PARTITION BY quarter ORDER BY total_views DESC) AS rank_by_views, |
| 76 | | RANK() OVER (PARTITION BY quarter ORDER BY engagement_rate DESC) AS rank_by_engagement, |
| 77 | | RANK() OVER (PARTITION BY quarter ORDER BY avg_rating DESC) AS rank_by_rating |
| 78 | | FROM with_growth |
| 79 | | ORDER BY quarter DESC, rank_by_views; |
| 80 | | }}} |
| 81 | | |
| 82 | | For better results I inserted this data: |
| 83 | | |
| 84 | | {{{ |
| 85 | | INSERT INTO story (user_id, short_description, mature_content, story_created_at, story_content) |
| 86 | | SELECT |
| 87 | | (SELECT user_id FROM writer ORDER BY random() LIMIT 1), |
| 88 | | 'Test story ' || g, (random() > 0.5), |
| 89 | | NOW() - (random() * INTERVAL '2 years'), |
| 90 | | 'Content for test story ' || g |
| 91 | | FROM generate_series(1, 10000) g; |
| 92 | | |
| 93 | | INSERT INTO chapter (story_id, chapter_number, chapter_name, title, chapter_content, |
| 94 | | word_count, rating, view_count, published_at) |
| 95 | | SELECT |
| 96 | | (SELECT story_id FROM story ORDER BY random() LIMIT 1), |
| 97 | | (random() * 100 + 1)::int, 'Chapter ' || g, 'Title ' || g, 'Content ' || g, |
| 98 | | (random() * 5000)::int, ROUND((random() * 4 + 1)::numeric, 2), |
| 99 | | (random() * 10000)::int, NOW() - (random() * INTERVAL '2 years') |
| 100 | | FROM generate_series(1, 500000) g ON CONFLICT DO NOTHING; |
| 101 | | |
| 102 | | INSERT INTO likes (story_id, user_id) |
| 103 | | SELECT (SELECT story_id FROM story ORDER BY random() LIMIT 1), |
| 104 | | (SELECT user_id FROM users ORDER BY random() LIMIT 1) |
| 105 | | FROM generate_series(1, 200000) g ON CONFLICT DO NOTHING; |
| 106 | | |
| 107 | | INSERT INTO comment (story_id, user_id, comment_content) |
| 108 | | SELECT (SELECT story_id FROM story ORDER BY random() LIMIT 1), |
| 109 | | (SELECT user_id FROM users ORDER BY random() LIMIT 1), |
| 110 | | 'Test comment ' || g |
| 111 | | FROM generate_series(1, 100000) g; |
| 112 | | }}} |
| 113 | | |
| 114 | | == Without index analysis |
| 115 | | {{{ |
| 116 | | |QUERY PLAN | |
| 117 | | |-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |
| 118 | | |Sort (cost=7281598552.60..7281599952.60 rows=560000 width=1539) (actual time=1315.189..1315.201 rows=5 loops=1) | |
| 119 | | | Sort Key: with_engagement.quarter DESC, (rank() OVER (?)) | |
| 120 | | | Sort Method: quicksort Memory: 26kB | |
| 121 | | | -> WindowAgg (cost=7280655711.84..7280794770.41 rows=560000 width=1539) (actual time=1315.151..1315.185 rows=5 loops=1) | |
| 122 | | | -> Incremental Sort (cost=7280655711.65..7280754170.41 rows=560000 width=1363) (actual time=1315.131..1315.142 rows=5 loops=1) | |
| 123 | | | Sort Key: with_engagement.quarter, with_engagement.total_views DESC | |
| 124 | | | Presorted Key: with_engagement.quarter | |
| 125 | | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | |
| 126 | | | -> WindowAgg (cost=7280655252.07..7280715103.02 rows=560000 width=1363) (actual time=1315.102..1315.132 rows=5 loops=1) | |
| 127 | | | -> Incremental Sort (cost=7280655251.97..7280705303.02 rows=560000 width=1355) (actual time=1315.092..1315.103 rows=5 loops=1) | |
| 128 | | | Sort Key: with_engagement.quarter, with_engagement.engagement_rate DESC | |
| 129 | | | Presorted Key: with_engagement.quarter | |
| 130 | | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | |
| 131 | | | -> WindowAgg (cost=7280655035.65..7280666235.63 rows=560000 width=1355) (actual time=1315.060..1315.091 rows=5 loops=1) | |
| 132 | | | -> Sort (cost=7280655035.63..7280656435.63 rows=560000 width=1347) (actual time=1315.048..1315.059 rows=5 loops=1) | |
| 133 | | | Sort Key: with_engagement.quarter, with_engagement.avg_rating DESC | |
| 134 | | | Sort Method: quicksort Memory: 26kB | |
| 135 | | | -> Subquery Scan on with_engagement (cost=37078922.01..7279943128.44 rows=560000 width=1347) (actual time=1315.026..1315.047 rows=5 loops=1) | |
| 136 | | | -> WindowAgg (cost=37078922.01..7279937528.44 rows=560000 width=1351) (actual time=1315.019..1315.039 rows=5 loops=1) | |
| 137 | | | -> Incremental Sort (cost=36897846.44..7279908128.44 rows=560000 width=1259) (actual time=1314.985..1314.994 rows=5 loops=1) | |
| 138 | | | Sort Key: quarterly_story_stats.story_id, quarterly_story_stats.quarter | |
| 139 | | | Presorted Key: quarterly_story_stats.story_id | |
| 140 | | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | |
| 141 | | | -> 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) | |
| 142 | | | -> GroupAggregate (cost=500845.04..7279863461.05 rows=560000 width=1263) (actual time=1314.003..1314.975 rows=5 loops=1) | |
| 143 | | | Group Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id | |
| 144 | | | -> Incremental Sort (cost=500845.04..7083585393.76 rows=6039023609 width=1211) (actual time=1313.282..1314.406 rows=939 loops=1) | |
| 145 | | | Sort Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id, ch.chapter_id | |
| 146 | | | Presorted Key: s.story_id, st.status | |
| 147 | | | Full-sort Groups: 5 Sort Method: quicksort Average Memory: 38kB Peak Memory: 38kB | |
| 148 | | | Pre-sorted Groups: 4 Sort Method: quicksort Average Memory: 107kB Peak Memory: 107kB | |
| 149 | | | -> Nested Loop Left Join (cost=266.68..44077127.06 rows=6039023609 width=1211) (actual time=1312.570..1313.534 rows=939 loops=1) | |
| 150 | | | -> Nested Loop Left Join (cost=266.52..922084.11 rows=187049404 width=1207) (actual time=1312.525..1312.788 rows=313 loops=1) | |
| 151 | | | -> Nested Loop Left Join (cost=266.35..36339.98 rows=7077545 width=1203) (actual time=1312.493..1312.633 rows=120 loops=1) | |
| 152 | | | -> Merge Left Join (cost=266.19..1814.11 rows=267799 width=1199) (actual time=1312.460..1312.526 rows=119 loops=1) | |
| 153 | | | Merge Cond: (s.story_id = l.story_id) | |
| 154 | | | -> Merge Left Join (cost=137.30..253.93 rows=10133 width=1195) (actual time=1312.417..1312.449 rows=27 loops=1) | |
| 155 | | | Merge Cond: (s.story_id = c.story_id) | |
| 156 | | | -> Merge Join (cost=72.81..134.31 rows=771 width=1191) (actual time=1312.375..1312.396 rows=11 loops=1) | |
| 157 | | | Merge Cond: (st.story_id = s.story_id) | |
| 158 | | | -> 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) | |
| 159 | | | Heap Fetches: 5 | |
| 160 | | | -> Sort (cost=72.66..72.91 rows=100 width=1073) (actual time=0.257..0.262 rows=11 loops=1) | |
| 161 | | | Sort Key: s.story_id | |
| 162 | | | Sort Method: quicksort Memory: 26kB | |
| 163 | | | -> Hash Left Join (cost=23.30..69.34 rows=100 width=1073) (actual time=0.212..0.231 rows=11 loops=1) | |
| 164 | | | Hash Cond: (s.story_id = ch.story_id) | |
| 165 | | | -> Nested Loop (cost=11.05..56.12 rows=70 width=1049) (actual time=0.154..0.169 rows=5 loops=1) | |
| 166 | | | -> Hash Join (cost=10.90..21.79 rows=40 width=1053) (actual time=0.117..0.123 rows=5 loops=1) | |
| 167 | | | Hash Cond: (s.user_id = u.user_id) | |
| 168 | | | -> Seq Scan on story s (cost=0.00..10.70 rows=70 width=533) (actual time=0.026..0.027 rows=5 loops=1) | |
| 169 | | | -> Hash (cost=10.40..10.40 rows=40 width=520) (actual time=0.061..0.062 rows=10 loops=1) | |
| 170 | | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| 171 | | | -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=520) (actual time=0.046..0.048 rows=10 loops=1) | |
| 172 | | | -> 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)| |
| 173 | | | Index Cond: (user_id = s.user_id) | |
| 174 | | | Heap Fetches: 5 | |
| 175 | | | -> Hash (cost=11.00..11.00 rows=100 width=28) (actual time=0.032..0.033 rows=11 loops=1) | |
| 176 | | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| 177 | | | -> Seq Scan on chapter ch (cost=0.00..11.00 rows=100 width=28) (actual time=0.018..0.023 rows=11 loops=1) | |
| 178 | | | -> Sort (cost=64.49..66.79 rows=920 width=8) (actual time=0.023..0.026 rows=27 loops=1) | |
| 179 | | | Sort Key: c.story_id | |
| 180 | | | Sort Method: quicksort Memory: 25kB | |
| 181 | | | -> Seq Scan on comment c (cost=0.00..19.20 rows=920 width=8) (actual time=0.016..0.018 rows=11 loops=1) | |
| 182 | | | -> Sort (cost=128.89..133.52 rows=1850 width=8) (actual time=0.025..0.033 rows=119 loops=1) | |
| 183 | | | Sort Key: l.story_id | |
| 184 | | | Sort Method: quicksort Memory: 25kB | |
| 185 | | | -> Seq Scan on likes l (cost=0.00..28.50 rows=1850 width=8) (actual time=0.015..0.017 rows=18 loops=1) | |
| 186 | | | -> Memoize (cost=0.16..14.81 rows=9 width=8) (actual time=0.001..0.001 rows=0 loops=119) | |
| 187 | | | Cache Key: s.story_id | |
| 188 | | | Cache Mode: logical | |
| 189 | | | Hits: 114 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| 190 | | | -> 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) | |
| 191 | | | Index Cond: (story_id = s.story_id) | |
| 192 | | | Heap Fetches: 2 | |
| 193 | | | -> Memoize (cost=0.16..14.81 rows=9 width=8) (actual time=0.000..0.001 rows=3 loops=120) | |
| 194 | | | Cache Key: s.story_id | |
| 195 | | | Cache Mode: logical | |
| 196 | | | Hits: 115 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| 197 | | | -> 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) | |
| 198 | | | Index Cond: (story_id = s.story_id) | |
| 199 | | | Heap Fetches: 13 | |
| 200 | | | -> Memoize (cost=0.17..1.04 rows=11 width=8) (actual time=0.001..0.001 rows=3 loops=313) | |
| 201 | | | Cache Key: s.story_id | |
| 202 | | | Cache Mode: logical | |
| 203 | | | Hits: 308 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| 204 | | | -> 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) | |
| 205 | | | Index Cond: (story_id = s.story_id) | |
| 206 | | | Heap Fetches: 15 | |
| 207 | | |Planning Time: 6.006 ms | |
| 208 | | |JIT: | |
| 209 | | | Functions: 92 | |
| 210 | | | Options: Inlining true, Optimization true, Expressions true, Deforming true | |
| 211 | | | 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 | |
| 212 | | |Execution Time: 1324.013 ms | |
| 213 | | |
| 214 | | Average Execution Time over 10 runs: 1,324 ms |
| 215 | | }}} |
| 216 | | == We add more data |
| 217 | | {{{ |
| 218 | | INSERT INTO story (user_id, short_description, mature_content, story_created_at, story_content, image) |
| 219 | | SELECT |
| 220 | | (SELECT user_id FROM writer ORDER BY random() LIMIT 1), |
| 221 | | 'Test story ' || g, |
| 222 | | (random() > 0.5), |
| 223 | | NOW() - (random() * INTERVAL '2 years'), |
| 224 | | 'Content for test story ' || g, |
| 225 | | NULL |
| 226 | | FROM generate_series(1, 10000) g; |
| 227 | | |
| 228 | | |
| 229 | | INSERT INTO chapter (story_id, chapter_number, chapter_name, title, chapter_content, word_count, rating, view_count, published_at) |
| 230 | | SELECT |
| 231 | | (SELECT story_id FROM story ORDER BY random() LIMIT 1), |
| 232 | | (random() * 100 + 1)::int, |
| 233 | | 'Chapter ' || g, |
| 234 | | 'Title ' || g, |
| 235 | | 'Chapter content ' || g, |
| 236 | | (random() * 5000)::int, |
| 237 | | ROUND((random() * 4 + 1)::numeric, 2), |
| 238 | | (random() * 10000)::int, |
| 239 | | NOW() - (random() * INTERVAL '2 years') |
| 240 | | FROM generate_series(1, 500000) g |
| 241 | | ON CONFLICT DO NOTHING; |
| 242 | | |
| 243 | | |
| 244 | | INSERT INTO likes (story_id, user_id) |
| 245 | | SELECT |
| 246 | | (SELECT story_id FROM story ORDER BY random() LIMIT 1), |
| 247 | | (SELECT user_id FROM users ORDER BY random() LIMIT 1) |
| 248 | | FROM generate_series(1, 200000) g |
| 249 | | ON CONFLICT DO NOTHING; |
| 250 | | |
| 251 | | |
| 252 | | INSERT INTO comment (story_id, user_id, comment_content) |
| 253 | | SELECT |
| 254 | | (SELECT story_id FROM story ORDER BY random() LIMIT 1), |
| 255 | | (SELECT user_id FROM users ORDER BY random() LIMIT 1), |
| 256 | | 'Test comment ' || g |
| 257 | | FROM generate_series(1, 100000) g; |
| 258 | | |
| 259 | | |
| 260 | | INSERT INTO status (story_id, status) |
| 261 | | SELECT story_id, |
| 262 | | CASE (random() * 2)::int |
| 263 | | WHEN 0 THEN 'draft' |
| 264 | | WHEN 1 THEN 'published' |
| 265 | | ELSE 'archived' |
| 266 | | END |
| 267 | | FROM story |
| 268 | | ON CONFLICT DO NOTHING; |
| 269 | | }}} |
| 270 | | |
| 271 | | == Indexes for this queries |
| 272 | | {{{ |
| 273 | | CREATE INDEX idx_story_created_at |
| 274 | | ON story(story_created_at); |
| 275 | | |
| 276 | | CREATE INDEX idx_story_user_id |
| 277 | | ON story(user_id); |
| 278 | | |
| 279 | | CREATE INDEX idx_chapter_story_id |
| 280 | | ON chapter(story_id); |
| 281 | | |
| 282 | | CREATE INDEX idx_likes_story_id |
| 283 | | ON likes(story_id); |
| 284 | | |
| 285 | | CREATE INDEX idx_comment_story_id |
| 286 | | ON comment(story_id); |
| 287 | | |
| 288 | | CREATE INDEX idx_story_created_at ON story(story_created_at); |
| 289 | | |
| 290 | | CREATE INDEX idx_story_user_id ON story(user_id); |
| 291 | | |
| 292 | | CREATE INDEX idx_chapter_story_id ON chapter(story_id); |
| 293 | | |
| 294 | | CREATE INDEX idx_likes_story_id ON likes(story_id); |
| 295 | | |
| 296 | | CREATE INDEX idx_comment_story_id ON comment(story_id); |
| 297 | | }}} |
| 298 | | |
| 299 | | === After analisys we get: |
| 300 | | {{{ |
| 301 | | ANALYZE story; |
| 302 | | ANALYZE chapter; |
| 303 | | ANALYZE likes; |
| 304 | | ANALYZE comment; |
| 305 | | }}} |
| 306 | | {{{ |
| 307 | | |QUERY PLAN | |
| 308 | | |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |
| 309 | | |Sort (cost=213482.19..213732.34 rows=100061 width=928) (actual time=454.241..454.788 rows=10010 loops=1) | |
| 310 | | | Sort Key: with_engagement.quarter DESC, (rank() OVER (?)) | |
| 311 | | | Sort Method: quicksort Memory: 2419kB | |
| 312 | | | -> WindowAgg (cost=101382.95..123768.87 rows=100061 width=928) (actual time=405.334..441.326 rows=10010 loops=1) | |
| 313 | | | -> Incremental Sort (cost=101382.78..116514.44 rows=100061 width=752) (actual time=405.304..428.725 rows=10010 loops=1) | |
| 314 | | | Sort Key: with_engagement.quarter, with_engagement.total_views DESC | |
| 315 | | | Presorted Key: with_engagement.quarter | |
| 316 | | | Full-sort Groups: 9 Sort Method: quicksort Average Memory: 35kB Peak Memory: 35kB | |
| 317 | | | Pre-sorted Groups: 9 Sort Method: quicksort Average Memory: 229kB Peak Memory: 248kB | |
| 318 | | | -> WindowAgg (cost=101313.05..110773.61 rows=100061 width=752) (actual time=402.802..423.048 rows=10010 loops=1) | |
| 319 | | | -> Incremental Sort (cost=101312.95..109022.55 rows=100061 width=744) (actual time=402.786..415.676 rows=10010 loops=1) | |
| 320 | | | Sort Key: with_engagement.quarter, with_engagement.engagement_rate DESC | |
| 321 | | | Presorted Key: with_engagement.quarter | |
| 322 | | | Full-sort Groups: 9 Sort Method: quicksort Average Memory: 34kB Peak Memory: 34kB | |
| 323 | | | Pre-sorted Groups: 9 Sort Method: quicksort Average Memory: 220kB Peak Memory: 238kB | |
| 324 | | | -> WindowAgg (cost=101280.52..103281.72 rows=100061 width=744) (actual time=401.889..410.203 rows=10010 loops=1) | |
| 325 | | | -> Sort (cost=101280.50..101530.65 rows=100061 width=736) (actual time=401.846..402.632 rows=10010 loops=1) | |
| 326 | | | Sort Key: with_engagement.quarter, with_engagement.avg_rating DESC | |
| 327 | | | Sort Method: quicksort Memory: 1793kB | |
| 328 | | | -> Subquery Scan on with_engagement (cost=361.44..27982.17 rows=100061 width=736) (actual time=73.176..395.082 rows=10010 loops=1) | |
| 329 | | | -> WindowAgg (cost=361.44..26981.56 rows=100061 width=740) (actual time=73.169..393.830 rows=10010 loops=1) | |
| 330 | | | -> Incremental Sort (cost=360.73..21728.36 rows=100061 width=648) (actual time=73.134..382.401 rows=10010 loops=1) | |
| 331 | | | Sort Key: quarterly_story_stats.story_id, quarterly_story_stats.quarter | |
| 332 | | | Presorted Key: quarterly_story_stats.story_id | |
| 333 | | | Full-sort Groups: 313 Sort Method: quicksort Average Memory: 30kB Peak Memory: 30kB | |
| 334 | | | -> 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) | |
| 335 | | | -> GroupAggregate (cost=259.66..14986.92 rows=100061 width=652) (actual time=70.128..375.785 rows=10010 loops=1) | |
| 336 | | | Group Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id | |
| 337 | | | -> Incremental Sort (cost=259.66..9983.87 rows=100061 width=594) (actual time=69.186..287.190 rows=111977 loops=1) | |
| 338 | | | Sort Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id, ch.chapter_id | |
| 339 | | | Presorted Key: s.story_id, st.status | |
| 340 | | | Full-sort Groups: 323 Sort Method: quicksort Average Memory: 38kB Peak Memory: 38kB | |
| 341 | | | Pre-sorted Groups: 36 Sort Methods: quicksort, external merge Average Memory: 98kB Peak Memory: 107kB Average Disk: 637kB Peak Disk: 7648kB | |
| 342 | | | -> Merge Left Join (cost=259.40..7263.71 rows=100061 width=594) (actual time=68.586..192.729 rows=111977 loops=1) | |
| 343 | | | Merge Cond: (s.story_id = rli.story_id) | |
| 344 | | | -> Merge Left Join (cost=130.51..6356.99 rows=100061 width=590) (actual time=68.536..150.448 rows=110819 loops=1) | |
| 345 | | | Merge Cond: (s.story_id = hg.story_id) | |
| 346 | | | -> Merge Left Join (cost=130.35..5685.75 rows=100061 width=586) (actual time=68.503..133.749 rows=110339 loops=1) | |
| 347 | | | Merge Cond: (s.story_id = c.story_id) | |
| 348 | | | -> Merge Left Join (cost=130.06..1622.11 rows=10010 width=582) (actual time=68.468..91.605 rows=10192 loops=1) | |
| 349 | | | Merge Cond: (s.story_id = col.story_id) | |
| 350 | | | -> Merge Left Join (cost=1.17..1440.43 rows=10010 width=578) (actual time=68.417..90.052 rows=10190 loops=1) | |
| 351 | | | Merge Cond: (s.story_id = l.story_id) | |
| 352 | | | -> Merge Left Join (cost=1.03..1402.74 rows=10010 width=574) (actual time=68.391..88.255 rows=10122 loops=1) | |
| 353 | | | Merge Cond: (s.story_id = ch.story_id) | |
| 354 | | | -> Nested Loop (cost=0.89..1360.49 rows=10010 width=556) (actual time=68.350..86.481 rows=10010 loops=1) | |
| 355 | | | -> Nested Loop (cost=0.74..1113.00 rows=10010 width=44) (actual time=68.315..81.875 rows=10010 loops=1) | |
| 356 | | | -> Merge Join (cost=0.57..861.93 rows=10010 width=40) (actual time=68.273..76.998 rows=10010 loops=1) | |
| 357 | | | Merge Cond: (st.story_id = s.story_id) | |
| 358 | | | -> 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)| |
| 359 | | | Heap Fetches: 0 | |
| 360 | | | -> 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) | |
| 361 | | | -> Memoize (cost=0.17..0.19 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=10010) | |
| 362 | | | Cache Key: s.user_id | |
| 363 | | | Cache Mode: logical | |
| 364 | | | Hits: 10006 Misses: 4 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| 365 | | | -> 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) | |
| 366 | | | Index Cond: (user_id = s.user_id) | |
| 367 | | | Heap Fetches: 4 | |
| 368 | | | -> Memoize (cost=0.15..0.17 rows=1 width=520) (actual time=0.000..0.000 rows=1 loops=10010) | |
| 369 | | | Cache Key: s.user_id | |
| 370 | | | Cache Mode: logical | |
| 371 | | | Hits: 10006 Misses: 4 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| 372 | | | -> 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) | |
| 373 | | | Index Cond: (user_id = s.user_id) | |
| 374 | | | -> 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) | |
| 375 | | | -> 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) | |
| 376 | | | -> Sort (cost=128.89..133.52 rows=1850 width=8) (actual time=0.035..0.036 rows=3 loops=1) | |
| 377 | | | Sort Key: col.story_id | |
| 378 | | | Sort Method: quicksort Memory: 25kB | |
| 379 | | | -> Seq Scan on collaboration col (cost=0.00..28.50 rows=1850 width=8) (actual time=0.023..0.025 rows=2 loops=1) | |
| 380 | | | -> 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) | |
| 381 | | | -> Materialize (cost=0.15..87.71 rows=2260 width=8) (actual time=0.015..0.070 rows=717 loops=1) | |
| 382 | | | -> 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) | |
| 383 | | | Heap Fetches: 15 | |
| 384 | | | -> Sort (cost=128.89..133.52 rows=1850 width=8) (actual time=0.024..0.130 rows=1867 loops=1) | |
| 385 | | | Sort Key: rli.story_id | |
| 386 | | | Sort Method: quicksort Memory: 25kB | |
| 387 | | | -> 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) | |
| 388 | | |Planning Time: 6.751 ms | |
| 389 | | |JIT: | |
| 390 | | | Functions: 85 | |
| 391 | | | Options: Inlining false, Optimization false, Expressions true, Deforming true | |
| 392 | | | 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 | |
| 393 | | |Execution Time: 465.217 ms | |
| 394 | | |
| 395 | | |
| 396 | | Average Execution Time over 10 runs: 465 ms |
| 397 | | |
| 398 | | }}} |
| 399 | | |
| 400 | | We conclude that the performance of the query is significantly better |
| 401 | | with these indexes and we keep them. The overall improvement is |
| 402 | | ~65% (2.8× faster), dropping from 1,324 ms to 465 ms. |
| 403 | | |
| 404 | | |
| 405 | | == Scenario 2 - Annual genre popularity and engagement trend |
| | 9 | |
| | 10 | == Scenario 1 - Annual genre popularity and engagement trend |
