| | 1 | = Other topics (Performance, Security, …) = |
| | 2 | |
| | 3 | === The testing methodology is as follows: |
| | 4 | |
| | 5 | 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. |
| | 6 | 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. |
| | 7 | After adding the indexes, the same query is executed 10 times and the results are compared. |
| | 8 | |
| | 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 | |QUERY PLAN | |
| | 116 | |-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |
| | 117 | |Sort (cost=7281598552.60..7281599952.60 rows=560000 width=1539) (actual time=1315.189..1315.201 rows=5 loops=1) | |
| | 118 | | Sort Key: with_engagement.quarter DESC, (rank() OVER (?)) | |
| | 119 | | Sort Method: quicksort Memory: 26kB | |
| | 120 | | -> WindowAgg (cost=7280655711.84..7280794770.41 rows=560000 width=1539) (actual time=1315.151..1315.185 rows=5 loops=1) | |
| | 121 | | -> Incremental Sort (cost=7280655711.65..7280754170.41 rows=560000 width=1363) (actual time=1315.131..1315.142 rows=5 loops=1) | |
| | 122 | | Sort Key: with_engagement.quarter, with_engagement.total_views DESC | |
| | 123 | | Presorted Key: with_engagement.quarter | |
| | 124 | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | |
| | 125 | | -> WindowAgg (cost=7280655252.07..7280715103.02 rows=560000 width=1363) (actual time=1315.102..1315.132 rows=5 loops=1) | |
| | 126 | | -> Incremental Sort (cost=7280655251.97..7280705303.02 rows=560000 width=1355) (actual time=1315.092..1315.103 rows=5 loops=1) | |
| | 127 | | Sort Key: with_engagement.quarter, with_engagement.engagement_rate DESC | |
| | 128 | | Presorted Key: with_engagement.quarter | |
| | 129 | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | |
| | 130 | | -> WindowAgg (cost=7280655035.65..7280666235.63 rows=560000 width=1355) (actual time=1315.060..1315.091 rows=5 loops=1) | |
| | 131 | | -> Sort (cost=7280655035.63..7280656435.63 rows=560000 width=1347) (actual time=1315.048..1315.059 rows=5 loops=1) | |
| | 132 | | Sort Key: with_engagement.quarter, with_engagement.avg_rating DESC | |
| | 133 | | Sort Method: quicksort Memory: 26kB | |
| | 134 | | -> Subquery Scan on with_engagement (cost=37078922.01..7279943128.44 rows=560000 width=1347) (actual time=1315.026..1315.047 rows=5 loops=1) | |
| | 135 | | -> WindowAgg (cost=37078922.01..7279937528.44 rows=560000 width=1351) (actual time=1315.019..1315.039 rows=5 loops=1) | |
| | 136 | | -> Incremental Sort (cost=36897846.44..7279908128.44 rows=560000 width=1259) (actual time=1314.985..1314.994 rows=5 loops=1) | |
| | 137 | | Sort Key: quarterly_story_stats.story_id, quarterly_story_stats.quarter | |
| | 138 | | Presorted Key: quarterly_story_stats.story_id | |
| | 139 | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | |
| | 140 | | -> 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) | |
| | 141 | | -> GroupAggregate (cost=500845.04..7279863461.05 rows=560000 width=1263) (actual time=1314.003..1314.975 rows=5 loops=1) | |
| | 142 | | Group Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id | |
| | 143 | | -> Incremental Sort (cost=500845.04..7083585393.76 rows=6039023609 width=1211) (actual time=1313.282..1314.406 rows=939 loops=1) | |
| | 144 | | Sort Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id, ch.chapter_id | |
| | 145 | | Presorted Key: s.story_id, st.status | |
| | 146 | | Full-sort Groups: 5 Sort Method: quicksort Average Memory: 38kB Peak Memory: 38kB | |
| | 147 | | Pre-sorted Groups: 4 Sort Method: quicksort Average Memory: 107kB Peak Memory: 107kB | |
| | 148 | | -> Nested Loop Left Join (cost=266.68..44077127.06 rows=6039023609 width=1211) (actual time=1312.570..1313.534 rows=939 loops=1) | |
| | 149 | | -> Nested Loop Left Join (cost=266.52..922084.11 rows=187049404 width=1207) (actual time=1312.525..1312.788 rows=313 loops=1) | |
| | 150 | | -> Nested Loop Left Join (cost=266.35..36339.98 rows=7077545 width=1203) (actual time=1312.493..1312.633 rows=120 loops=1) | |
| | 151 | | -> Merge Left Join (cost=266.19..1814.11 rows=267799 width=1199) (actual time=1312.460..1312.526 rows=119 loops=1) | |
| | 152 | | Merge Cond: (s.story_id = l.story_id) | |
| | 153 | | -> Merge Left Join (cost=137.30..253.93 rows=10133 width=1195) (actual time=1312.417..1312.449 rows=27 loops=1) | |
| | 154 | | Merge Cond: (s.story_id = c.story_id) | |
| | 155 | | -> Merge Join (cost=72.81..134.31 rows=771 width=1191) (actual time=1312.375..1312.396 rows=11 loops=1) | |
| | 156 | | Merge Cond: (st.story_id = s.story_id) | |
| | 157 | | -> 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) | |
| | 158 | | Heap Fetches: 5 | |
| | 159 | | -> Sort (cost=72.66..72.91 rows=100 width=1073) (actual time=0.257..0.262 rows=11 loops=1) | |
| | 160 | | Sort Key: s.story_id | |
| | 161 | | Sort Method: quicksort Memory: 26kB | |
| | 162 | | -> Hash Left Join (cost=23.30..69.34 rows=100 width=1073) (actual time=0.212..0.231 rows=11 loops=1) | |
| | 163 | | Hash Cond: (s.story_id = ch.story_id) | |
| | 164 | | -> Nested Loop (cost=11.05..56.12 rows=70 width=1049) (actual time=0.154..0.169 rows=5 loops=1) | |
| | 165 | | -> Hash Join (cost=10.90..21.79 rows=40 width=1053) (actual time=0.117..0.123 rows=5 loops=1) | |
| | 166 | | Hash Cond: (s.user_id = u.user_id) | |
| | 167 | | -> Seq Scan on story s (cost=0.00..10.70 rows=70 width=533) (actual time=0.026..0.027 rows=5 loops=1) | |
| | 168 | | -> Hash (cost=10.40..10.40 rows=40 width=520) (actual time=0.061..0.062 rows=10 loops=1) | |
| | 169 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 170 | | -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=520) (actual time=0.046..0.048 rows=10 loops=1) | |
| | 171 | | -> 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)| |
| | 172 | | Index Cond: (user_id = s.user_id) | |
| | 173 | | Heap Fetches: 5 | |
| | 174 | | -> Hash (cost=11.00..11.00 rows=100 width=28) (actual time=0.032..0.033 rows=11 loops=1) | |
| | 175 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 176 | | -> Seq Scan on chapter ch (cost=0.00..11.00 rows=100 width=28) (actual time=0.018..0.023 rows=11 loops=1) | |
| | 177 | | -> Sort (cost=64.49..66.79 rows=920 width=8) (actual time=0.023..0.026 rows=27 loops=1) | |
| | 178 | | Sort Key: c.story_id | |
| | 179 | | Sort Method: quicksort Memory: 25kB | |
| | 180 | | -> Seq Scan on comment c (cost=0.00..19.20 rows=920 width=8) (actual time=0.016..0.018 rows=11 loops=1) | |
| | 181 | | -> Sort (cost=128.89..133.52 rows=1850 width=8) (actual time=0.025..0.033 rows=119 loops=1) | |
| | 182 | | Sort Key: l.story_id | |
| | 183 | | Sort Method: quicksort Memory: 25kB | |
| | 184 | | -> Seq Scan on likes l (cost=0.00..28.50 rows=1850 width=8) (actual time=0.015..0.017 rows=18 loops=1) | |
| | 185 | | -> Memoize (cost=0.16..14.81 rows=9 width=8) (actual time=0.001..0.001 rows=0 loops=119) | |
| | 186 | | Cache Key: s.story_id | |
| | 187 | | Cache Mode: logical | |
| | 188 | | Hits: 114 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| | 189 | | -> 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) | |
| | 190 | | Index Cond: (story_id = s.story_id) | |
| | 191 | | Heap Fetches: 2 | |
| | 192 | | -> Memoize (cost=0.16..14.81 rows=9 width=8) (actual time=0.000..0.001 rows=3 loops=120) | |
| | 193 | | Cache Key: s.story_id | |
| | 194 | | Cache Mode: logical | |
| | 195 | | Hits: 115 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| | 196 | | -> 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) | |
| | 197 | | Index Cond: (story_id = s.story_id) | |
| | 198 | | Heap Fetches: 13 | |
| | 199 | | -> Memoize (cost=0.17..1.04 rows=11 width=8) (actual time=0.001..0.001 rows=3 loops=313) | |
| | 200 | | Cache Key: s.story_id | |
| | 201 | | Cache Mode: logical | |
| | 202 | | Hits: 308 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| | 203 | | -> 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) | |
| | 204 | | Index Cond: (story_id = s.story_id) | |
| | 205 | | Heap Fetches: 15 | |
| | 206 | |Planning Time: 6.006 ms | |
| | 207 | |JIT: | |
| | 208 | | Functions: 92 | |
| | 209 | | Options: Inlining true, Optimization true, Expressions true, Deforming true | |
| | 210 | | 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 | |
| | 211 | |Execution Time: 1324.013 ms | |
| | 212 | |
| | 213 | Average Execution Time over 10 runs: 1,324 ms |
| | 214 | |
| | 215 | == We add more data |
| | 216 | {{{ |
| | 217 | INSERT INTO story (user_id, short_description, mature_content, story_created_at, story_content, image) |
| | 218 | SELECT |
| | 219 | (SELECT user_id FROM writer ORDER BY random() LIMIT 1), |
| | 220 | 'Test story ' || g, |
| | 221 | (random() > 0.5), |
| | 222 | NOW() - (random() * INTERVAL '2 years'), |
| | 223 | 'Content for test story ' || g, |
| | 224 | NULL |
| | 225 | FROM generate_series(1, 10000) g; |
| | 226 | |
| | 227 | |
| | 228 | INSERT INTO chapter (story_id, chapter_number, chapter_name, title, chapter_content, word_count, rating, view_count, published_at) |
| | 229 | SELECT |
| | 230 | (SELECT story_id FROM story ORDER BY random() LIMIT 1), |
| | 231 | (random() * 100 + 1)::int, |
| | 232 | 'Chapter ' || g, |
| | 233 | 'Title ' || g, |
| | 234 | 'Chapter content ' || g, |
| | 235 | (random() * 5000)::int, |
| | 236 | ROUND((random() * 4 + 1)::numeric, 2), |
| | 237 | (random() * 10000)::int, |
| | 238 | NOW() - (random() * INTERVAL '2 years') |
| | 239 | FROM generate_series(1, 500000) g |
| | 240 | ON CONFLICT DO NOTHING; |
| | 241 | |
| | 242 | |
| | 243 | INSERT INTO likes (story_id, user_id) |
| | 244 | SELECT |
| | 245 | (SELECT story_id FROM story ORDER BY random() LIMIT 1), |
| | 246 | (SELECT user_id FROM users ORDER BY random() LIMIT 1) |
| | 247 | FROM generate_series(1, 200000) g |
| | 248 | ON CONFLICT DO NOTHING; |
| | 249 | |
| | 250 | |
| | 251 | INSERT INTO comment (story_id, user_id, comment_content) |
| | 252 | SELECT |
| | 253 | (SELECT story_id FROM story ORDER BY random() LIMIT 1), |
| | 254 | (SELECT user_id FROM users ORDER BY random() LIMIT 1), |
| | 255 | 'Test comment ' || g |
| | 256 | FROM generate_series(1, 100000) g; |
| | 257 | |
| | 258 | |
| | 259 | INSERT INTO status (story_id, status) |
| | 260 | SELECT story_id, |
| | 261 | CASE (random() * 2)::int |
| | 262 | WHEN 0 THEN 'draft' |
| | 263 | WHEN 1 THEN 'published' |
| | 264 | ELSE 'archived' |
| | 265 | END |
| | 266 | FROM story |
| | 267 | ON CONFLICT DO NOTHING; |
| | 268 | }}} |
| | 269 | |
| | 270 | == Indexes for this queries |
| | 271 | |
| | 272 | CREATE INDEX idx_story_created_at |
| | 273 | ON story(story_created_at); |
| | 274 | |
| | 275 | CREATE INDEX idx_story_user_id |
| | 276 | ON story(user_id); |
| | 277 | |
| | 278 | CREATE INDEX idx_chapter_story_id |
| | 279 | ON chapter(story_id); |
| | 280 | |
| | 281 | CREATE INDEX idx_likes_story_id |
| | 282 | ON likes(story_id); |
| | 283 | |
| | 284 | CREATE INDEX idx_comment_story_id |
| | 285 | ON comment(story_id); |
| | 286 | |
| | 287 | CREATE INDEX idx_story_created_at ON story(story_created_at); |
| | 288 | |
| | 289 | CREATE INDEX idx_story_user_id ON story(user_id); |
| | 290 | |
| | 291 | CREATE INDEX idx_chapter_story_id ON chapter(story_id); |
| | 292 | |
| | 293 | CREATE INDEX idx_likes_story_id ON likes(story_id); |
| | 294 | |
| | 295 | CREATE INDEX idx_comment_story_id ON comment(story_id); |
| | 296 | |
| | 297 | |
| | 298 | }}} |
| | 299 | |
| | 300 | === After analisys we get: |
| | 301 | {{{ |
| | 302 | ANALYZE story; |
| | 303 | ANALYZE chapter; |
| | 304 | ANALYZE likes; |
| | 305 | ANALYZE comment; |
| | 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 | We conclude that the performance of the query is significantly better |
| | 399 | with these indexes and we keep them. The overall improvement is |
| | 400 | ~65% (2.8× faster), dropping from 1,324 ms to 465 ms. |
| | 401 | |
