| Version 16 (modified by , 12 days ago) ( diff ) |
|---|
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
Without index analysis
EXPLAIN ANALYZE
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;
|QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort (cost=7281598552.60..7281599952.60 rows=560000 width=1539) (actual time=1362.133..1362.145 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=1362.094..1362.129 rows=5 loops=1) |
| -> Incremental Sort (cost=7280655711.65..7280754170.41 rows=560000 width=1363) (actual time=1362.074..1362.085 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=1362.044..1362.075 rows=5 loops=1) |
| -> Incremental Sort (cost=7280655251.97..7280705303.02 rows=560000 width=1355) (actual time=1362.034..1362.045 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=1362.001..1362.033 rows=5 loops=1) |
| -> Sort (cost=7280655035.63..7280656435.63 rows=560000 width=1347) (actual time=1361.987..1361.998 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=1361.945..1361.985 rows=5 loops=1) |
| -> WindowAgg (cost=37078922.01..7279937528.44 rows=560000 width=1351) (actual time=1361.938..1361.976 rows=5 loops=1) |
| -> Incremental Sort (cost=36897846.44..7279908128.44 rows=560000 width=1259) (actual time=1361.905..1361.914 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=1361.070..1361.903 rows=5 loops=1) |
| -> GroupAggregate (cost=500845.04..7279863461.05 rows=560000 width=1263) (actual time=1361.063..1361.894 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=1360.317..1361.324 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=1359.651..1360.436 rows=939 loops=1) |
| -> Nested Loop Left Join (cost=266.52..922084.11 rows=187049404 width=1207) (actual time=1359.606..1359.872 rows=313 loops=1) |
| -> Nested Loop Left Join (cost=266.35..36339.98 rows=7077545 width=1203) (actual time=1359.567..1359.708 rows=120 loops=1) |
| -> Merge Left Join (cost=266.19..1814.11 rows=267799 width=1199) (actual time=1359.535..1359.600 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=1359.484..1359.515 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=1359.441..1359.460 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.038..0.045 rows=5 loops=1) |
| Heap Fetches: 5 |
| -> Sort (cost=72.66..72.91 rows=100 width=1073) (actual time=0.236..0.240 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.185..0.204 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.119..0.136 rows=5 loops=1) |
| -> Hash Join (cost=10.90..21.79 rows=40 width=1053) (actual time=0.086..0.092 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.019..0.020 rows=5 loops=1) |
| -> Hash (cost=10.40..10.40 rows=40 width=520) (actual time=0.038..0.039 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.023..0.025 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.033..0.034 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.019..0.023 rows=11 loops=1) |
| -> Sort (cost=64.49..66.79 rows=920 width=8) (actual time=0.024..0.027 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.031..0.040 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.016..0.018 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.001..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.004..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.000..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.006..0.008 rows=3 loops=5) |
| Index Cond: (story_id = s.story_id) |
| Heap Fetches: 15 |
|Planning Time: 6.145 ms |
|JIT: |
| Functions: 92 |
| Options: Inlining true, Optimization true, Expressions true, Deforming true |
| Timing: Generation 8.860 ms (Deform 4.416 ms), Inlining 34.161 ms, Optimization 784.792 ms, Emission 540.464 ms, Total 1368.277 ms |
|Execution Time: 1371.545 ms |
Average time without indexes is: 1397.98 ms
Indexes for this queries
CREATE INDEX idx_story_user_id ON story(user_id); CREATE INDEX idx_story_created_at ON story(story_created_at); 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_collaboration_story ON collaboration(story_id); CREATE INDEX idx_has_genre_story ON has_genre(story_id); CREATE INDEX idx_rli_story_id ON reading_list_items(story_id); CREATE INDEX idx_status_story_id ON status(story_id);
After analisys we get:
ANALYZE story; ANALYZE chapter; ANALYZE likes; ANALYZE comment; ANALYZE collaboration; ANALYZE has_genre; ANALYZE reading_list_items; ANALYZE status;
|QUERY PLAN |
|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort (cost=333.92..334.92 rows=400 width=1003) (actual time=2.387..2.393 rows=5 loops=1) |
| Sort Key: with_engagement.quarter DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 26kB |
| -> WindowAgg (cost=251.01..316.63 rows=400 width=1003) (actual time=2.367..2.381 rows=5 loops=1) |
| -> Incremental Sort (cost=250.90..287.63 rows=400 width=827) (actual time=2.356..2.361 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=250.76..276.63 rows=400 width=827) (actual time=2.343..2.354 rows=5 loops=1) |
| -> Incremental Sort (cost=250.70..269.63 rows=400 width=819) (actual time=2.341..2.347 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=250.65..258.63 rows=400 width=819) (actual time=2.326..2.338 rows=5 loops=1) |
| -> Sort (cost=250.63..251.63 rows=400 width=811) (actual time=2.323..2.328 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=18.18..233.34 rows=400 width=811) (actual time=2.302..2.319 rows=5 loops=1) |
| -> WindowAgg (cost=18.18..229.34 rows=400 width=815) (actual time=2.301..2.316 rows=5 loops=1) |
| -> Incremental Sort (cost=17.68..208.34 rows=400 width=723) (actual time=2.290..2.294 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=16.76..197.34 rows=400 width=723) (actual time=1.459..2.285 rows=5 loops=1) |
| -> GroupAggregate (cost=16.76..193.34 rows=400 width=727) (actual time=1.459..2.283 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=16.76..164.05 rows=686 width=670) (actual time=0.731..1.638 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 |
| -> Merge Left Join (cost=1.38..134.35 rows=686 width=670) (actual time=0.118..0.878 rows=939 loops=1) |
| Merge Cond: (s.story_id = hg.story_id) |
| -> Merge Left Join (cost=1.24..109.39 rows=229 width=666) (actual time=0.098..0.328 rows=313 loops=1) |
| Merge Cond: (s.story_id = rli.story_id) |
| -> Merge Left Join (cost=1.10..93.41 rows=88 width=662) (actual time=0.090..0.226 rows=120 loops=1) |
| Merge Cond: (s.story_id = l.story_id) |
| -> Merge Left Join (cost=0.97..79.65 rows=24 width=658) (actual time=0.082..0.176 rows=28 loops=1) |
| Merge Cond: (s.story_id = c.story_id) |
| -> Merge Left Join (cost=0.83..66.96 rows=11 width=654) (actual time=0.070..0.143 rows=12 loops=1) |
| Merge Cond: (s.story_id = ch.story_id) |
| -> Merge Left Join (cost=0.70..53.51 rows=5 width=635) (actual time=0.062..0.118 rows=6 loops=1) |
| Merge Cond: (s.story_id = col.story_id) |
| -> Nested Loop (cost=0.57..41.32 rows=5 width=631) (actual time=0.054..0.105 rows=5 loops=1) |
| Join Filter: (s.user_id = u.user_id) |
| -> Nested Loop (cost=0.43..40.38 rows=5 width=119) (actual time=0.045..0.085 rows=5 loops=1) |
| -> Nested Loop (cost=0.27..23.35 rows=5 width=115) (actual time=0.031..0.056 rows=5 loops=1) |
| -> Index Only Scan using status_pk on status st (cost=0.13..12.21 rows=5 width=13) (actual time=0.021..0.026 rows=5 loops=1)|
| Heap Fetches: 5 |
| -> Index Scan using story_pkey on story s (cost=0.13..2.55 rows=1 width=106) (actual time=0.003..0.003 rows=1 loops=5) |
| Index Cond: (story_id = st.story_id) |
| -> Memoize (cost=0.17..4.98 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=5) |
| Cache Key: s.user_id |
| Cache Mode: logical |
| Hits: 2 Misses: 3 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Only Scan using writer_pkey on writer w (cost=0.15..4.97 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=3) |
| Index Cond: (user_id = s.user_id) |
| Heap Fetches: 3 |
| -> Index Scan using users_pkey on users u (cost=0.14..0.18 rows=1 width=520) (actual time=0.003..0.003 rows=1 loops=5) |
| Index Cond: (user_id = w.user_id) |
| -> Index Scan using idx_collaboration_story on collaboration col (cost=0.13..12.16 rows=2 width=8) (actual time=0.006..0.007 rows=2 loops=1) |
| -> Index Scan using idx_chapter_story_id on chapter ch (cost=0.14..13.30 rows=11 width=23) (actual time=0.006..0.015 rows=11 loops=1) |
| -> Materialize (cost=0.14..12.33 rows=11 width=8) (actual time=0.009..0.018 rows=27 loops=1) |
| -> Index Scan using idx_comment_story_id on comment c (cost=0.14..12.30 rows=11 width=8) (actual time=0.006..0.010 rows=11 loops=1) |
| -> Materialize (cost=0.14..12.45 rows=18 width=8) (actual time=0.007..0.021 rows=119 loops=1) |
| -> Index Scan using idx_likes_story_id on likes l (cost=0.14..12.41 rows=18 width=8) (actual time=0.006..0.011 rows=18 loops=1) |
| -> Materialize (cost=0.14..12.36 rows=13 width=8) (actual time=0.007..0.029 rows=312 loops=1) |
| -> Index Scan using idx_rli_story_id on reading_list_items rli (cost=0.14..12.33 rows=13 width=8) (actual time=0.006..0.011 rows=13 loops=1) |
| -> Materialize (cost=0.14..12.40 rows=15 width=8) (actual time=0.007..0.069 rows=936 loops=1) |
| -> Index Scan using idx_has_genre_story on has_genre hg (cost=0.14..12.36 rows=15 width=8) (actual time=0.006..0.013 rows=15 loops=1) |
|Planning Time: 5.542 ms |
|Execution Time: 2.635 ms |
Average time: 2.74 ms
The indexes added for this query resulted in a dramatic improvement, reducing average execution time from 1,397.98 ms to 2.74 ms (~510x faster). After adding indexes on story_id columns across all joined tables, the planner switched from Seq Scans to Index Scans on every JOIN operation, visible in the query execution plan. The indexes are kept.
Scenario 2 — 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. }}} }}}
Note:
See TracWiki
for help on using the wiki.
