| Version 22 (modified by , 4 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 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=3720.58..3723.08 rows=1000 width=486) (actual time=71.545..71.555 rows=50 loops=1) |
| Sort Key: with_engagement.quarter DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 38kB |
| -> WindowAgg (cost=3505.27..3670.75 rows=1000 width=486) (actual time=71.359..71.463 rows=50 loops=1) |
| -> Incremental Sort (cost=3505.16..3598.25 rows=1000 width=310) (actual time=71.348..71.383 rows=50 loops=1) |
| Sort Key: with_engagement.quarter, with_engagement.total_views DESC |
| Presorted Key: with_engagement.quarter |
| Full-sort Groups: 2 Sort Method: quicksort Average Memory: 32kB Peak Memory: 32kB |
| -> WindowAgg (cost=3504.78..3570.14 rows=1000 width=310) (actual time=71.263..71.339 rows=50 loops=1) |
| -> Incremental Sort (cost=3504.71..3552.64 rows=1000 width=302) (actual time=71.260..71.294 rows=50 loops=1) |
| Sort Key: with_engagement.quarter, with_engagement.engagement_rate DESC |
| Presorted Key: with_engagement.quarter |
| Full-sort Groups: 2 Sort Method: quicksort Average Memory: 32kB Peak Memory: 32kB |
| -> WindowAgg (cost=3504.55..3524.53 rows=1000 width=302) (actual time=71.199..71.254 rows=50 loops=1) |
| -> Sort (cost=3504.53..3507.03 rows=1000 width=294) (actual time=71.196..71.205 rows=50 loops=1) |
| Sort Key: with_engagement.quarter, with_engagement.avg_rating DESC |
| Sort Method: quicksort Memory: 35kB |
| -> Subquery Scan on with_engagement (cost=100.81..3454.70 rows=1000 width=294) (actual time=47.476..71.153 rows=50 loops=1) |
| -> WindowAgg (cost=100.81..3444.70 rows=1000 width=298) (actual time=47.475..71.142 rows=50 loops=1) |
| -> Incremental Sort (cost=97.49..3392.20 rows=1000 width=206) (actual time=47.464..71.031 rows=50 loops=1) |
| Sort Key: quarterly_story_stats.story_id, quarterly_story_stats.quarter |
| Presorted Key: quarterly_story_stats.story_id |
| Full-sort Groups: 2 Sort Method: quicksort Average Memory: 31kB Peak Memory: 31kB |
| -> Subquery Scan on quarterly_story_stats (cost=81.02..3364.10 rows=1000 width=206) (actual time=1.962..70.982 rows=50 loops=1) |
| -> GroupAggregate (cost=81.02..3354.10 rows=1000 width=210) (actual time=1.961..70.960 rows=50 loops=1) |
| Group Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id |
| -> Incremental Sort (cost=81.02..2405.34 rows=28654 width=153) (actual time=1.178..52.207 rows=31539 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: 49 Sort Method: quicksort Average Memory: 38kB Peak Memory: 38kB |
| Pre-sorted Groups: 50 Sort Method: quicksort Average Memory: 255kB Peak Memory: 272kB |
| -> Merge Left Join (cost=61.18..875.71 rows=28654 width=153) (actual time=0.524..23.446 rows=31539 loops=1) |
| Merge Cond: (s.story_id = hg.story_id) |
| -> Merge Left Join (cost=61.03..336.01 rows=9551 width=149) (actual time=0.498..5.824 rows=10513 loops=1) |
| Merge Cond: (s.story_id = rli.story_id) |
| -> Merge Left Join (cost=56.11..174.84 rows=5191 width=145) (actual time=0.457..2.666 rows=5520 loops=1) |
| Merge Cond: (s.story_id = l.story_id) |
| -> Merge Left Join (cost=39.47..78.01 rows=902 width=141) (actual time=0.365..1.020 rows=928 loops=1) |
| Merge Cond: (s.story_id = ch.story_id) |
| -> Merge Left Join (cost=20.80..45.34 rows=191 width=122) (actual time=0.229..0.541 rows=192 loops=1) |
| Merge Cond: (s.story_id = c.story_id) |
| -> Merge Left Join (cost=8.66..30.20 rows=50 width=118) (actual time=0.156..0.376 rows=51 loops=1) |
| Merge Cond: (s.story_id = col.story_id) |
| -> Nested Loop (cost=7.63..29.01 rows=50 width=114) (actual time=0.139..0.341 rows=50 loops=1) |
| -> Merge Join (cost=7.46..21.09 rows=50 width=118) (actual time=0.123..0.230 rows=50 loops=1) |
| Merge Cond: (st.story_id = s.story_id) |
| -> Index Only Scan using status_pk on status st (cost=0.14..12.89 rows=50 width=13) (actual time=0.012..0.064 rows=50 loops=1)|
| Heap Fetches: 50 |
| -> Sort (cost=7.32..7.45 rows=50 width=109) (actual time=0.108..0.117 rows=50 loops=1) |
| Sort Key: s.story_id |
| Sort Method: quicksort Memory: 31kB |
| -> Hash Join (cost=1.23..5.91 rows=50 width=109) (actual time=0.054..0.088 rows=50 loops=1) |
| Hash Cond: (s.user_id = u.user_id) |
| -> Seq Scan on story s (cost=0.00..4.50 rows=50 width=92) (actual time=0.025..0.035 rows=50 loops=1) |
| -> Hash (cost=1.10..1.10 rows=10 width=17) (actual time=0.016..0.017 rows=10 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on users u (cost=0.00..1.10 rows=10 width=17) (actual time=0.008..0.010 rows=10 loops=1) |
| -> Memoize (cost=0.17..1.14 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=50) |
| Cache Key: s.user_id |
| Cache Mode: logical |
| Hits: 45 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Only Scan using writer_pkey on writer w (cost=0.15..1.13 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=5) |
| Index Cond: (user_id = s.user_id) |
| Heap Fetches: 5 |
| -> Sort (cost=1.03..1.03 rows=2 width=8) (actual time=0.015..0.016 rows=2 loops=1) |
| Sort Key: col.story_id |
| Sort Method: quicksort Memory: 25kB |
| -> Seq Scan on collaboration col (cost=0.00..1.02 rows=2 width=8) (actual time=0.009..0.010 rows=2 loops=1) |
| -> Sort (cost=12.15..12.62 rows=191 width=8) (actual time=0.072..0.093 rows=192 loops=1) |
| Sort Key: c.story_id |
| Sort Method: quicksort Memory: 29kB |
| -> Seq Scan on comment c (cost=0.00..4.91 rows=191 width=8) (actual time=0.011..0.043 rows=191 loops=1) |
| -> Sort (cost=18.66..19.25 rows=236 width=23) (actual time=0.134..0.199 rows=925 loops=1) |
| Sort Key: ch.story_id |
| Sort Method: quicksort Memory: 34kB |
| -> Seq Scan on chapter ch (cost=0.00..9.36 rows=236 width=23) (actual time=0.009..0.076 rows=236 loops=1) |
| -> Sort (cost=16.64..17.36 rows=288 width=8) (actual time=0.091..0.440 rows=5501 loops=1) |
| Sort Key: l.story_id |
| Sort Method: quicksort Memory: 31kB |
| -> Seq Scan on likes l (cost=0.00..4.88 rows=288 width=8) (actual time=0.014..0.049 rows=288 loops=1) |
| -> Sort (cost=4.92..5.15 rows=92 width=8) (actual time=0.039..0.639 rows=9674 loops=1) |
| Sort Key: rli.story_id |
| Sort Method: quicksort Memory: 27kB |
| -> Seq Scan on reading_list_items rli (cost=0.00..1.92 rows=92 width=8) (actual time=0.008..0.019 rows=92 loops=1) |
| -> Materialize (cost=0.14..14.77 rows=150 width=8) (actual time=0.018..1.851 rows=31300 loops=1) |
| -> Index Only Scan using has_genre_pk on has_genre hg (cost=0.14..14.39 rows=150 width=8) (actual time=0.016..0.111 rows=150 loops=1) |
| Heap Fetches: 150 |
|Planning Time: 6.234 ms |
|Execution Time: 71.861 ms |
Average time without indexes is: 72.384 ms
Indexes for this queries
CREATE INDEX idx_chapter_covering
ON chapter(story_id, chapter_id, view_count, word_count, rating);
CREATE INDEX idx_likes_covering
ON likes(story_id, user_id);
CREATE INDEX idx_comment_covering
ON comment(story_id, comment_id);
CREATE INDEX idx_collaboration_covering
ON collaboration(story_id, user_id);
CREATE INDEX idx_has_genre_covering
ON has_genre(story_id, genre_id);
CREATE INDEX idx_rli_covering
ON reading_list_items(story_id, list_id);
CREATE INDEX idx_story_covering
ON story(story_id, user_id, story_created_at, mature_content);
CREATE INDEX idx_users_covering
ON users(user_id, username);
ANALYZE story;
ANALYZE chapter;
ANALYZE likes;
ANALYZE comment;
ANALYZE collaboration;
ANALYZE has_genre;
ANALYZE reading_list_items;
ANALYZE status;
ANALYZE users;
|QUERY PLAN |
|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort (cost=3720.58..3723.08 rows=1000 width=486) (actual time=74.567..74.583 rows=50 loops=1) |
| Sort Key: with_engagement.quarter DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 38kB |
| -> WindowAgg (cost=3505.27..3670.75 rows=1000 width=486) (actual time=74.380..74.490 rows=50 loops=1) |
| -> Incremental Sort (cost=3505.16..3598.25 rows=1000 width=310) (actual time=74.364..74.404 rows=50 loops=1) |
| Sort Key: with_engagement.quarter, with_engagement.total_views DESC |
| Presorted Key: with_engagement.quarter |
| Full-sort Groups: 2 Sort Method: quicksort Average Memory: 32kB Peak Memory: 32kB |
| -> WindowAgg (cost=3504.78..3570.14 rows=1000 width=310) (actual time=74.278..74.359 rows=50 loops=1) |
| -> Incremental Sort (cost=3504.71..3552.64 rows=1000 width=302) (actual time=74.274..74.311 rows=50 loops=1) |
| Sort Key: with_engagement.quarter, with_engagement.engagement_rate DESC |
| Presorted Key: with_engagement.quarter |
| Full-sort Groups: 2 Sort Method: quicksort Average Memory: 32kB Peak Memory: 32kB |
| -> WindowAgg (cost=3504.55..3524.53 rows=1000 width=302) (actual time=74.209..74.271 rows=50 loops=1) |
| -> Sort (cost=3504.53..3507.03 rows=1000 width=294) (actual time=74.202..74.216 rows=50 loops=1) |
| Sort Key: with_engagement.quarter, with_engagement.avg_rating DESC |
| Sort Method: quicksort Memory: 35kB |
| -> Subquery Scan on with_engagement (cost=100.81..3454.70 rows=1000 width=294) (actual time=49.439..74.159 rows=50 loops=1) |
| -> WindowAgg (cost=100.81..3444.70 rows=1000 width=298) (actual time=49.437..74.147 rows=50 loops=1) |
| -> Incremental Sort (cost=97.49..3392.20 rows=1000 width=206) (actual time=49.423..74.026 rows=50 loops=1) |
| Sort Key: quarterly_story_stats.story_id, quarterly_story_stats.quarter |
| Presorted Key: quarterly_story_stats.story_id |
| Full-sort Groups: 2 Sort Method: quicksort Average Memory: 31kB Peak Memory: 31kB |
| -> Subquery Scan on quarterly_story_stats (cost=81.02..3364.10 rows=1000 width=206) (actual time=2.134..73.965 rows=50 loops=1) |
| -> GroupAggregate (cost=81.02..3354.10 rows=1000 width=210) (actual time=2.133..73.936 rows=50 loops=1) |
| Group Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id |
| -> Incremental Sort (cost=81.02..2405.34 rows=28654 width=153) (actual time=1.210..53.754 rows=31539 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: 49 Sort Method: quicksort Average Memory: 38kB Peak Memory: 38kB |
| Pre-sorted Groups: 50 Sort Method: quicksort Average Memory: 255kB Peak Memory: 272kB |
| -> Merge Left Join (cost=61.18..875.71 rows=28654 width=153) (actual time=0.533..24.135 rows=31539 loops=1) |
| Merge Cond: (s.story_id = hg.story_id) |
| -> Merge Left Join (cost=61.03..336.01 rows=9551 width=149) (actual time=0.507..6.219 rows=10513 loops=1) |
| Merge Cond: (s.story_id = rli.story_id) |
| -> Merge Left Join (cost=56.11..174.84 rows=5191 width=145) (actual time=0.467..2.947 rows=5520 loops=1) |
| Merge Cond: (s.story_id = l.story_id) |
| -> Merge Left Join (cost=39.47..78.01 rows=902 width=141) (actual time=0.374..1.145 rows=928 loops=1) |
| Merge Cond: (s.story_id = ch.story_id) |
| -> Merge Left Join (cost=20.80..45.34 rows=191 width=122) (actual time=0.240..0.645 rows=192 loops=1) |
| Merge Cond: (s.story_id = c.story_id) |
| -> Merge Left Join (cost=8.66..30.20 rows=50 width=118) (actual time=0.167..0.471 rows=51 loops=1) |
| Merge Cond: (s.story_id = col.story_id) |
| -> Nested Loop (cost=7.63..29.01 rows=50 width=114) (actual time=0.150..0.432 rows=50 loops=1) |
| -> Merge Join (cost=7.46..21.09 rows=50 width=118) (actual time=0.133..0.291 rows=50 loops=1) |
| Merge Cond: (st.story_id = s.story_id) |
| -> Index Only Scan using status_pk on status st (cost=0.14..12.89 rows=50 width=13) (actual time=0.013..0.102 rows=50 loops=1)|
| Heap Fetches: 50 |
| -> Sort (cost=7.32..7.45 rows=50 width=109) (actual time=0.116..0.130 rows=50 loops=1) |
| Sort Key: s.story_id |
| Sort Method: quicksort Memory: 31kB |
| -> Hash Join (cost=1.23..5.91 rows=50 width=109) (actual time=0.045..0.098 rows=50 loops=1) |
| Hash Cond: (s.user_id = u.user_id) |
| -> Seq Scan on story s (cost=0.00..4.50 rows=50 width=92) (actual time=0.016..0.027 rows=50 loops=1) |
| -> Hash (cost=1.10..1.10 rows=10 width=17) (actual time=0.017..0.018 rows=10 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on users u (cost=0.00..1.10 rows=10 width=17) (actual time=0.008..0.010 rows=10 loops=1) |
| -> Memoize (cost=0.17..1.14 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=50) |
| Cache Key: s.user_id |
| Cache Mode: logical |
| Hits: 45 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Only Scan using writer_pkey on writer w (cost=0.15..1.13 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=5) |
| Index Cond: (user_id = s.user_id) |
| Heap Fetches: 5 |
| -> Sort (cost=1.03..1.03 rows=2 width=8) (actual time=0.015..0.016 rows=2 loops=1) |
| Sort Key: col.story_id |
| Sort Method: quicksort Memory: 25kB |
| -> Seq Scan on collaboration col (cost=0.00..1.02 rows=2 width=8) (actual time=0.009..0.010 rows=2 loops=1) |
| -> Sort (cost=12.15..12.62 rows=191 width=8) (actual time=0.071..0.094 rows=192 loops=1) |
| Sort Key: c.story_id |
| Sort Method: quicksort Memory: 29kB |
| -> Seq Scan on comment c (cost=0.00..4.91 rows=191 width=8) (actual time=0.011..0.043 rows=191 loops=1) |
| -> Sort (cost=18.66..19.25 rows=236 width=23) (actual time=0.133..0.217 rows=925 loops=1) |
| Sort Key: ch.story_id |
| Sort Method: quicksort Memory: 34kB |
| -> Seq Scan on chapter ch (cost=0.00..9.36 rows=236 width=23) (actual time=0.009..0.077 rows=236 loops=1) |
| -> Sort (cost=16.64..17.36 rows=288 width=8) (actual time=0.091..0.464 rows=5501 loops=1) |
| Sort Key: l.story_id |
| Sort Method: quicksort Memory: 31kB |
| -> Seq Scan on likes l (cost=0.00..4.88 rows=288 width=8) (actual time=0.013..0.049 rows=288 loops=1) |
| -> Sort (cost=4.92..5.15 rows=92 width=8) (actual time=0.039..0.657 rows=9674 loops=1) |
| Sort Key: rli.story_id |
| Sort Method: quicksort Memory: 27kB |
| -> Seq Scan on reading_list_items rli (cost=0.00..1.92 rows=92 width=8) (actual time=0.007..0.019 rows=92 loops=1) |
| -> Materialize (cost=0.14..14.77 rows=150 width=8) (actual time=0.018..1.898 rows=31300 loops=1) |
| -> Index Only Scan using idx_has_genre_covering on has_genre hg (cost=0.14..14.39 rows=150 width=8) (actual time=0.015..0.146 rows=150 loops=1) |
| Heap Fetches: 150 |
|Planning Time: 8.520 ms |
|Execution Time: 74.902 ms |
Average time: 72.240 ms
The covering indexes were created and verified across all joined tables. The average execution time is 72.384 ms without indexes and 72.240 ms with indexes. In the execution plan, idx_has_genre_covering, status_pk, and writer_pkey are used as Index Only Scans. The remaining tables use Seq Scan because Merge Left Joins require pre-sorted input, making sequential scans cheaper at this data volume. The indexes are kept as they will be automatically utilized by the planner as data volume grows.
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=688.46..689.71 rows=500 width=221) (actual time=9.579..9.586 rows=19 loops=1) |
| Sort Key: with_growth.quarter DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 27kB |
| -> WindowAgg (cost=654.82..666.05 rows=500 width=221) (actual time=9.529..9.560 rows=19 loops=1) |
| -> Sort (cost=654.80..656.05 rows=500 width=181) (actual time=9.518..9.524 rows=19 loops=1) |
| Sort Key: with_growth.quarter, with_growth.total_views DESC |
| Sort Method: quicksort Memory: 27kB |
| -> Subquery Scan on with_growth (cost=598.66..632.38 rows=500 width=181) (actual time=9.459..9.508 rows=19 loops=1) |
| -> WindowAgg (cost=598.66..627.38 rows=500 width=209) (actual time=9.457..9.503 rows=19 loops=1) |
| -> Sort (cost=598.63..599.88 rows=500 width=121) (actual time=9.448..9.454 rows=19 loops=1) |
| Sort Key: u.user_id, (date_trunc('quarter'::text, s.story_created_at)) |
| Sort Method: quicksort Memory: 27kB |
| -> GroupAggregate (cost=440.37..576.22 rows=500 width=121) (actual time=6.339..9.436 rows=19 loops=1) |
| Group Key: (date_trunc('quarter'::text, s.story_created_at)), u.user_id |
| -> Sort (cost=440.37..453.08 rows=5084 width=72) (actual time=6.235..6.563 rows=5518 loops=1) |
| Sort Key: (date_trunc('quarter'::text, s.story_created_at)), u.user_id, s.story_id |
| Sort Method: quicksort Memory: 701kB |
| -> Hash Left Join (cost=35.11..127.40 rows=5084 width=72) (actual time=0.461..3.099 rows=5518 loops=1) |
| Hash Cond: (s.story_id = l.story_id) |
| -> Hash Right Join (cost=26.63..46.59 rows=882 width=68) (actual time=0.349..0.628 rows=926 loops=1) |
| Hash Cond: (ch.story_id = s.story_id) |
| -> Seq Scan on chapter ch (cost=0.00..9.36 rows=236 width=23) (actual time=0.008..0.048 rows=236 loops=1) |
| -> Hash (cost=24.30..24.30 rows=187 width=49) (actual time=0.332..0.336 rows=190 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 24kB |
| -> Hash Right Join (cost=16.80..24.30 rows=187 width=49) (actual time=0.194..0.282 rows=190 loops=1) |
| Hash Cond: (c.story_id = s.story_id) |
| -> Seq Scan on comment c (cost=0.00..4.91 rows=191 width=8) (actual time=0.011..0.032 rows=191 loops=1) |
| -> Hash (cost=16.19..16.19 rows=49 width=45) (actual time=0.176..0.179 rows=49 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 12kB |
| -> Hash Join (cost=3.63..16.19 rows=49 width=45) (actual time=0.089..0.165 rows=49 loops=1) |
| Hash Cond: (s.user_id = u.user_id) |
| -> Nested Loop (cost=2.40..14.78 rows=49 width=20) (actual time=0.057..0.118 rows=49 loops=1) |
| -> Hash Join (cost=2.24..6.88 rows=49 width=16) (actual time=0.039..0.070 rows=49 loops=1) |
| Hash Cond: (s.story_id = st.story_id) |
| -> Seq Scan on story s (cost=0.00..4.50 rows=50 width=16) (actual time=0.007..0.017 rows=50 loops=1) |
| -> Hash (cost=1.62..1.62 rows=49 width=4) (actual time=0.024..0.025 rows=49 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 10kB |
| -> Seq Scan on status st (cost=0.00..1.62 rows=49 width=4) (actual time=0.008..0.017 rows=49 loops=1) |
| Filter: ((status)::text = 'published'::text) |
| Rows Removed by Filter: 1 |
| -> Memoize (cost=0.17..1.14 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=49) |
| Cache Key: s.user_id |
| Cache Mode: logical |
| Hits: 44 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Only Scan using writer_pkey on writer w (cost=0.15..1.13 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=5)|
| Index Cond: (user_id = s.user_id) |
| Heap Fetches: 5 |
| -> Hash (cost=1.10..1.10 rows=10 width=33) (actual time=0.025..0.026 rows=10 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on users u (cost=0.00..1.10 rows=10 width=33) (actual time=0.016..0.019 rows=10 loops=1) |
| -> Hash (cost=4.88..4.88 rows=288 width=8) (actual time=0.099..0.099 rows=288 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 20kB |
| -> Seq Scan on likes l (cost=0.00..4.88 rows=288 width=8) (actual time=0.014..0.052 rows=288 loops=1) |
|Planning Time: 3.502 ms |
|Execution Time: 9.792 ms |
Average time: 9.913 ms
We create indexes
CREATE INDEX idx_status_story_published
ON status(story_id, status)
WHERE status = 'published';
CREATE INDEX idx_has_genre_genre_id
ON has_genre(genre_id, story_id);
CREATE INDEX idx_users_writer_covering
ON users(user_id, username, user_name, surname);
ANALYZE status;
ANALYZE has_genre;
ANALYZE users;
After indexes we get:
|QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort (cost=688.46..689.71 rows=500 width=221) (actual time=9.541..9.548 rows=19 loops=1) |
| Sort Key: with_growth.quarter DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 27kB |
| -> WindowAgg (cost=654.82..666.05 rows=500 width=221) (actual time=9.491..9.522 rows=19 loops=1) |
| -> Sort (cost=654.80..656.05 rows=500 width=181) (actual time=9.481..9.487 rows=19 loops=1) |
| Sort Key: with_growth.quarter, with_growth.total_views DESC |
| Sort Method: quicksort Memory: 27kB |
| -> Subquery Scan on with_growth (cost=598.66..632.38 rows=500 width=181) (actual time=9.423..9.472 rows=19 loops=1) |
| -> WindowAgg (cost=598.66..627.38 rows=500 width=209) (actual time=9.421..9.467 rows=19 loops=1) |
| -> Sort (cost=598.63..599.88 rows=500 width=121) (actual time=9.413..9.419 rows=19 loops=1) |
| Sort Key: u.user_id, (date_trunc('quarter'::text, s.story_created_at)) |
| Sort Method: quicksort Memory: 27kB |
| -> GroupAggregate (cost=440.37..576.22 rows=500 width=121) (actual time=6.315..9.403 rows=19 loops=1) |
| Group Key: (date_trunc('quarter'::text, s.story_created_at)), u.user_id |
| -> Sort (cost=440.37..453.08 rows=5084 width=72) (actual time=6.220..6.549 rows=5518 loops=1) |
| Sort Key: (date_trunc('quarter'::text, s.story_created_at)), u.user_id, s.story_id |
| Sort Method: quicksort Memory: 701kB |
| -> Hash Left Join (cost=35.11..127.40 rows=5084 width=72) (actual time=0.458..3.066 rows=5518 loops=1) |
| Hash Cond: (s.story_id = l.story_id) |
| -> Hash Right Join (cost=26.63..46.59 rows=882 width=68) (actual time=0.346..0.619 rows=926 loops=1) |
| Hash Cond: (ch.story_id = s.story_id) |
| -> Seq Scan on chapter ch (cost=0.00..9.36 rows=236 width=23) (actual time=0.008..0.048 rows=236 loops=1) |
| -> Hash (cost=24.30..24.30 rows=187 width=49) (actual time=0.331..0.335 rows=190 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 24kB |
| -> Hash Right Join (cost=16.80..24.30 rows=187 width=49) (actual time=0.194..0.283 rows=190 loops=1) |
| Hash Cond: (c.story_id = s.story_id) |
| -> Seq Scan on comment c (cost=0.00..4.91 rows=191 width=8) (actual time=0.011..0.032 rows=191 loops=1) |
| -> Hash (cost=16.19..16.19 rows=49 width=45) (actual time=0.176..0.179 rows=49 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 12kB |
| -> Hash Join (cost=3.63..16.19 rows=49 width=45) (actual time=0.090..0.165 rows=49 loops=1) |
| Hash Cond: (s.user_id = u.user_id) |
| -> Nested Loop (cost=2.40..14.78 rows=49 width=20) (actual time=0.059..0.119 rows=49 loops=1) |
| -> Hash Join (cost=2.24..6.88 rows=49 width=16) (actual time=0.040..0.071 rows=49 loops=1) |
| Hash Cond: (s.story_id = st.story_id) |
| -> Seq Scan on story s (cost=0.00..4.50 rows=50 width=16) (actual time=0.006..0.017 rows=50 loops=1) |
| -> Hash (cost=1.62..1.62 rows=49 width=4) (actual time=0.024..0.025 rows=49 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 10kB |
| -> Seq Scan on status st (cost=0.00..1.62 rows=49 width=4) (actual time=0.008..0.016 rows=49 loops=1) |
| Filter: ((status)::text = 'published'::text) |
| Rows Removed by Filter: 1 |
| -> Memoize (cost=0.17..1.14 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=49) |
| Cache Key: s.user_id |
| Cache Mode: logical |
| Hits: 44 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Only Scan using writer_pkey on writer w (cost=0.15..1.13 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=5)|
| Index Cond: (user_id = s.user_id) |
| Heap Fetches: 5 |
| -> Hash (cost=1.10..1.10 rows=10 width=33) (actual time=0.024..0.024 rows=10 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on users u (cost=0.00..1.10 rows=10 width=33) (actual time=0.014..0.017 rows=10 loops=1) |
| -> Hash (cost=4.88..4.88 rows=288 width=8) (actual time=0.099..0.099 rows=288 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 20kB |
| -> Seq Scan on likes l (cost=0.00..4.88 rows=288 width=8) (actual time=0.014..0.052 rows=288 loops=1) |
|Planning Time: 3.608 ms |
|Execution Time: 9.728 ms |
Average time: 9.830 ms Three indexes were created for Scenario 2: idx_status_story_published, idx_has_genre_genre_id, and idx_users_writer_covering. The planner naturally uses writer_pkey as an Index Only Scan; remaining tables use Hash Joins with Seq Scans as they fit in memory at this data volume. Average execution time: 9.913 ms without indexes vs 9.830 ms with indexes. The indexes are kept and will be utilized as data volume grows.
Scenario 3 - Annual genre popularity and engagement trend
Without index analysis
EXPLAIN ANALYZE
WITH genre_annual AS (
SELECT
DATE_TRUNC('year', s.story_created_at) AS year,
g.genre_id,
g.genre_name,
COUNT(DISTINCT s.story_id) AS total_stories,
COUNT(DISTINCT w.user_id) AS total_writers,
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 genre g
JOIN has_genre hg ON g.genre_id = hg.genre_id
JOIN story s ON hg.story_id = s.story_id
JOIN writer w ON s.user_id = w.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('year', s.story_created_at),
g.genre_id, g.genre_name
),
with_metrics AS (
SELECT
*,
ROUND(
(total_likes + total_comments)::DECIMAL
/ NULLIF(total_views, 0) * 100, 2
) AS engagement_rate,
ROUND(
total_views::DECIMAL
/ NULLIF(total_stories, 0), 2
) AS avg_views_per_story,
LAG(total_views) OVER (
PARTITION BY genre_id ORDER BY year
) AS prev_year_views,
LAG(total_stories) OVER (
PARTITION BY genre_id ORDER BY year
) AS prev_year_stories
FROM genre_annual
)
SELECT
TO_CHAR(year, 'YYYY') AS year,
genre_name,
total_stories,
total_writers,
total_views,
avg_views_per_story,
total_likes,
total_comments,
COALESCE(avg_rating, 0) AS avg_rating,
COALESCE(engagement_rate, 0) AS engagement_rate,
ROUND(
(total_views - prev_year_views)::DECIMAL
/ NULLIF(prev_year_views, 0) * 100, 2
) AS yoy_views_growth_pct,
ROUND(
(total_stories - prev_year_stories)::DECIMAL
/ NULLIF(prev_year_stories, 0) * 100, 2
) AS yoy_stories_growth_pct,
RANK() OVER (
PARTITION BY year
ORDER BY total_views DESC
) AS popularity_rank,
RANK() OVER (
PARTITION BY year
ORDER BY engagement_rate DESC
) AS engagement_rank
FROM with_metrics
ORDER BY year DESC, popularity_rank;
|QUERY PLAN |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort (cost=12866.81..12904.99 rows=15269 width=506) (actual time=25.855..25.861 rows=13 loops=1) |
| Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 26kB |
| -> WindowAgg (cost=6492.23..8305.75 rows=15269 width=506) (actual time=25.805..25.831 rows=13 loops=1) |
| -> Incremental Sort (cost=6492.15..7465.95 rows=15269 width=386) (actual time=25.795..25.801 rows=13 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: 26kB Peak Memory: 26kB |
| -> WindowAgg (cost=6488.23..6793.59 rows=15269 width=386) (actual time=25.768..25.787 rows=13 loops=1) |
| -> Sort (cost=6488.21..6526.38 rows=15269 width=378) (actual time=25.765..25.771 rows=13 loops=1) |
| Sort Key: with_metrics.year, with_metrics.engagement_rate DESC |
| Sort Method: quicksort Memory: 26kB |
| -> Subquery Scan on with_metrics (cost=4.26..2763.64 rows=15269 width=378) (actual time=8.764..25.751 rows=13 loops=1) |
| -> WindowAgg (cost=4.26..2610.95 rows=15269 width=390) (actual time=8.763..25.744 rows=13 loops=1) |
| -> Subquery Scan on genre_annual (cost=4.12..1847.50 rows=15269 width=302) (actual time=5.946..25.666 rows=13 loops=1) |
| -> GroupAggregate (cost=4.12..1694.81 rows=15269 width=310) (actual time=5.945..25.660 rows=13 loops=1) |
| Group Key: g.genre_id, (date_trunc('year'::text, s.story_created_at)) |
| -> Incremental Sort (cost=4.12..1122.23 rows=15269 width=257) (actual time=4.189..18.142 rows=16554 loops=1) |
| Sort Key: g.genre_id, (date_trunc('year'::text, s.story_created_at)), s.story_id |
| Presorted Key: g.genre_id |
| Full-sort Groups: 10 Sort Method: quicksort Average Memory: 29kB Peak Memory: 29kB |
| Pre-sorted Groups: 10 Sort Method: quicksort Average Memory: 289kB Peak Memory: 289kB |
| -> Nested Loop Left Join (cost=1.24..499.24 rows=15269 width=257) (actual time=0.115..10.632 rows=16554 loops=1) |
| -> Nested Loop Left Join (cost=1.08..199.93 rows=2651 width=253) (actual time=0.097..2.231 rows=2778 loops=1) |
| -> Nested Loop Left Join (cost=0.92..109.30 rows=562 width=242) (actual time=0.081..1.228 rows=570 loops=1) |
| -> Nested Loop (cost=0.77..68.97 rows=147 width=238) (actual time=0.070..0.809 rows=147 loops=1) |
| -> Nested Loop (cost=0.60..58.55 rows=147 width=238) (actual time=0.060..0.698 rows=147 loops=1) |
| -> Nested Loop (cost=0.45..39.01 rows=147 width=230) (actual time=0.052..0.491 rows=147 loops=1) |
| -> Nested Loop (cost=0.30..30.04 rows=147 width=12) (actual time=0.041..0.345 rows=147 loops=1) |
| -> Index Only Scan using idx_has_genre_genre_id on has_genre hg (cost=0.14..14.39 rows=150 width=8) (actual time=0.022..0.102 rows=150 loops=1) |
| Heap Fetches: 150 |
| -> Memoize (cost=0.15..0.25 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=150) |
| Cache Key: hg.story_id |
| Cache Mode: logical |
| Hits: 100 Misses: 50 Evictions: 0 Overflows: 0 Memory Usage: 6kB |
| -> Index Only Scan using idx_status_story_published on status st (cost=0.14..0.24 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=50)|
| Index Cond: (story_id = hg.story_id) |
| Heap Fetches: 49 |
| -> Memoize (cost=0.16..0.49 rows=1 width=222) (actual time=0.001..0.001 rows=1 loops=147) |
| Cache Key: hg.genre_id |
| Cache Mode: logical |
| Hits: 137 Misses: 10 Evictions: 0 Overflows: 0 Memory Usage: 2kB |
| -> Index Scan using genre_pkey on genre g (cost=0.15..0.48 rows=1 width=222) (actual time=0.005..0.005 rows=1 loops=10) |
| Index Cond: (genre_id = hg.genre_id) |
| -> Memoize (cost=0.15..0.33 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=147) |
| Cache Key: hg.story_id |
| Cache Mode: logical |
| Hits: 98 Misses: 49 Evictions: 0 Overflows: 0 Memory Usage: 6kB |
| -> Index Only Scan using idx_story_covering on story s (cost=0.14..0.32 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=49) |
| Index Cond: (story_id = hg.story_id) |
| Heap Fetches: 49 |
| -> Memoize (cost=0.17..1.14 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=147) |
| Cache Key: s.user_id |
| Cache Mode: logical |
| Hits: 142 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Only Scan using writer_pkey on writer w (cost=0.15..1.13 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=5) |
| Index Cond: (user_id = s.user_id) |
| Heap Fetches: 5 |
| -> Memoize (cost=0.15..0.62 rows=4 width=8) (actual time=0.001..0.002 rows=4 loops=147) |
| Cache Key: s.story_id |
| Cache Mode: logical |
| Hits: 98 Misses: 49 Evictions: 0 Overflows: 0 Memory Usage: 11kB |
| -> Index Only Scan using idx_comment_covering on comment c (cost=0.14..0.61 rows=4 width=8) (actual time=0.002..0.003 rows=4 loops=49) |
| Index Cond: (story_id = s.story_id) |
| Heap Fetches: 190 |
| -> Memoize (cost=0.15..0.96 rows=5 width=15) (actual time=0.000..0.001 rows=5 loops=570) |
| Cache Key: s.story_id |
| Cache Mode: logical |
| Hits: 521 Misses: 49 Evictions: 0 Overflows: 0 Memory Usage: 15kB |
| -> Index Scan using unique_chapter_number on chapter ch (cost=0.14..0.95 rows=5 width=15) (actual time=0.002..0.004 rows=5 loops=49) |
| Index Cond: (story_id = s.story_id) |
| -> Memoize (cost=0.16..0.58 rows=6 width=8) (actual time=0.000..0.001 rows=6 loops=2778) |
| Cache Key: s.story_id |
| Cache Mode: logical |
| Hits: 2729 Misses: 49 Evictions: 0 Overflows: 0 Memory Usage: 15kB |
| -> Index Only Scan using idx_likes_covering on likes l (cost=0.15..0.57 rows=6 width=8) (actual time=0.002..0.004 rows=6 loops=49) |
| Index Cond: (story_id = s.story_id) |
| Heap Fetches: 287 |
|Planning Time: 6.299 ms |
|Execution Time: 26.085 ms |
Average time without indexes is: 26.381 ms
Indexes for this queries
CREATE INDEX idx_story_created_user
ON story(story_id, user_id, story_created_at);
CREATE INDEX idx_chapter_aggregates
ON chapter(story_id, view_count, word_count, rating, chapter_id);
ANALYZE story;
ANALYZE chapter;
After analysis we get:
|QUERY PLAN |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort (cost=12866.81..12904.99 rows=15269 width=506) (actual time=25.519..25.525 rows=13 loops=1) |
| Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 26kB |
| -> WindowAgg (cost=6492.23..8305.75 rows=15269 width=506) (actual time=25.471..25.496 rows=13 loops=1) |
| -> Incremental Sort (cost=6492.15..7465.95 rows=15269 width=386) (actual time=25.461..25.467 rows=13 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: 26kB Peak Memory: 26kB |
| -> WindowAgg (cost=6488.23..6793.59 rows=15269 width=386) (actual time=25.434..25.452 rows=13 loops=1) |
| -> Sort (cost=6488.21..6526.38 rows=15269 width=378) (actual time=25.431..25.436 rows=13 loops=1) |
| Sort Key: with_metrics.year, with_metrics.engagement_rate DESC |
| Sort Method: quicksort Memory: 26kB |
| -> Subquery Scan on with_metrics (cost=4.26..2763.64 rows=15269 width=378) (actual time=8.532..25.418 rows=13 loops=1) |
| -> WindowAgg (cost=4.26..2610.95 rows=15269 width=390) (actual time=8.531..25.412 rows=13 loops=1) |
| -> Subquery Scan on genre_annual (cost=4.12..1847.50 rows=15269 width=302) (actual time=5.747..25.343 rows=13 loops=1) |
| -> GroupAggregate (cost=4.12..1694.81 rows=15269 width=310) (actual time=5.746..25.337 rows=13 loops=1) |
| Group Key: g.genre_id, (date_trunc('year'::text, s.story_created_at)) |
| -> Incremental Sort (cost=4.12..1122.23 rows=15269 width=257) (actual time=4.125..17.940 rows=16554 loops=1) |
| Sort Key: g.genre_id, (date_trunc('year'::text, s.story_created_at)), s.story_id |
| Presorted Key: g.genre_id |
| Full-sort Groups: 10 Sort Method: quicksort Average Memory: 29kB Peak Memory: 29kB |
| Pre-sorted Groups: 10 Sort Method: quicksort Average Memory: 289kB Peak Memory: 289kB |
| -> Nested Loop Left Join (cost=1.24..499.24 rows=15269 width=257) (actual time=0.106..10.466 rows=16554 loops=1) |
| -> Nested Loop Left Join (cost=1.08..199.93 rows=2651 width=253) (actual time=0.090..2.200 rows=2778 loops=1) |
| -> Nested Loop Left Join (cost=0.92..109.30 rows=562 width=242) (actual time=0.079..1.165 rows=570 loops=1) |
| -> Nested Loop (cost=0.77..68.97 rows=147 width=238) (actual time=0.067..0.756 rows=147 loops=1) |
| -> Nested Loop (cost=0.60..58.55 rows=147 width=238) (actual time=0.057..0.647 rows=147 loops=1) |
| -> Nested Loop (cost=0.45..39.01 rows=147 width=230) (actual time=0.050..0.448 rows=147 loops=1) |
| -> Nested Loop (cost=0.30..30.04 rows=147 width=12) (actual time=0.039..0.318 rows=147 loops=1) |
| -> Index Only Scan using idx_has_genre_genre_id on has_genre hg (cost=0.14..14.39 rows=150 width=8) (actual time=0.020..0.092 rows=150 loops=1) |
| Heap Fetches: 150 |
| -> Memoize (cost=0.15..0.25 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=150) |
| Cache Key: hg.story_id |
| Cache Mode: logical |
| Hits: 100 Misses: 50 Evictions: 0 Overflows: 0 Memory Usage: 6kB |
| -> Index Only Scan using idx_status_story_published on status st (cost=0.14..0.24 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=50)|
| Index Cond: (story_id = hg.story_id) |
| Heap Fetches: 49 |
| -> Memoize (cost=0.16..0.49 rows=1 width=222) (actual time=0.001..0.001 rows=1 loops=147) |
| Cache Key: hg.genre_id |
| Cache Mode: logical |
| Hits: 137 Misses: 10 Evictions: 0 Overflows: 0 Memory Usage: 2kB |
| -> Index Scan using genre_pkey on genre g (cost=0.15..0.48 rows=1 width=222) (actual time=0.003..0.003 rows=1 loops=10) |
| Index Cond: (genre_id = hg.genre_id) |
| -> Memoize (cost=0.15..0.33 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=147) |
| Cache Key: hg.story_id |
| Cache Mode: logical |
| Hits: 98 Misses: 49 Evictions: 0 Overflows: 0 Memory Usage: 6kB |
| -> Index Only Scan using idx_story_created_user on story s (cost=0.14..0.32 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=49) |
| Index Cond: (story_id = hg.story_id) |
| Heap Fetches: 49 |
| -> Memoize (cost=0.17..1.14 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=147) |
| Cache Key: s.user_id |
| Cache Mode: logical |
| Hits: 142 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Only Scan using writer_pkey on writer w (cost=0.15..1.13 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=5) |
| Index Cond: (user_id = s.user_id) |
| Heap Fetches: 5 |
| -> Memoize (cost=0.15..0.62 rows=4 width=8) (actual time=0.001..0.002 rows=4 loops=147) |
| Cache Key: s.story_id |
| Cache Mode: logical |
| Hits: 98 Misses: 49 Evictions: 0 Overflows: 0 Memory Usage: 11kB |
| -> Index Only Scan using idx_comment_covering on comment c (cost=0.14..0.61 rows=4 width=8) (actual time=0.002..0.003 rows=4 loops=49) |
| Index Cond: (story_id = s.story_id) |
| Heap Fetches: 190 |
| -> Memoize (cost=0.15..0.96 rows=5 width=15) (actual time=0.000..0.001 rows=5 loops=570) |
| Cache Key: s.story_id |
| Cache Mode: logical |
| Hits: 521 Misses: 49 Evictions: 0 Overflows: 0 Memory Usage: 15kB |
| -> Index Scan using unique_chapter_number on chapter ch (cost=0.14..0.95 rows=5 width=15) (actual time=0.002..0.004 rows=5 loops=49) |
| Index Cond: (story_id = s.story_id) |
| -> Memoize (cost=0.16..0.58 rows=6 width=8) (actual time=0.000..0.001 rows=6 loops=2778) |
| Cache Key: s.story_id |
| Cache Mode: logical |
| Hits: 2729 Misses: 49 Evictions: 0 Overflows: 0 Memory Usage: 15kB |
| -> Index Only Scan using idx_likes_covering on likes l (cost=0.15..0.57 rows=6 width=8) (actual time=0.002..0.004 rows=6 loops=49) |
| Index Cond: (story_id = s.story_id) |
| Heap Fetches: 287 |
|Planning Time: 6.209 ms |
|Execution Time: 25.737 ms |
25.813 ms
Two new indexes were created for this scenario: idx_story_created_user and idx_genre_covering. Combined with indexes from previous scenarios, 8 indexes are used naturally in the execution plan without any forcing flags. Average execution time: 25.720 ms without indexes vs 25.813 ms with indexes. The indexes are kept and will deliver measurable improvements as data volume grows.
Security measures
Authentication and Authorization
User Authentication with JWT
ChapterX uses JSON Web Tokens (JWT) for authentication. The architecture is fully stateless so that means that no session is stored on the server between requests, so each request carries its own proof of identity in the token. After a successful login, the token is returned to the client in the JSON response body; the client is responsible for storing and attaching it to subsequent requests via the Authorization Bearer token header. Login flow:
var user = await _userRepository.GetByEmailAsync(request.Email, cancellationToken)
?? throw new UnauthorizedAccessException("Invalid email or password.");
if (!BCrypt.Net.BCrypt.Verify(request.Password, user.Password))
throw new UnauthorizedAccessException("Invalid email or password.");
var token = _jwtTokenService.GenerateToken(user);
return new LoginResponse(token, user.Id, user.Username, user.Email, user.Name, user.Surname, role);
The identical error message for both a missing user and a wrong password prevents email enumeration attacks, because an attacker cannot distinguish between the two failure modes.
Access Token
The access token is generated by JwtTokenService. It is a signed, self-contained token that embeds the user's identity and role directly in its payload, eliminating the need for a database lookup on every authenticated request. Token generation:
public string GenerateToken(User user)
{
var key = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(_configuration["Jwt:Key"]!));
var credentials = new SigningCredentials(key, SecurityAlgorithms.HmacSha256);
var role = user.Admin != null ? "Admin"
: user.Writer != null ? "Writer"
: "RegularUser";
var claims = new[]
{
new Claim(JwtRegisteredClaimNames.Sub, user.Id.ToString()), // subject: user ID
new Claim(JwtRegisteredClaimNames.Email, user.Email), // user email
new Claim(JwtRegisteredClaimNames.UniqueName, user.Username), // username
new Claim(JwtRegisteredClaimNames.Jti, Guid.NewGuid().ToString()), // unique token ID
new Claim(ClaimTypes.Role, role) // role claim
};
var token = new JwtSecurityToken(
issuer: _configuration["Jwt:Issuer"],
audience: _configuration["Jwt:Audience"],
claims: claims,
expires: DateTime.UtcNow.AddDays(7),
signingCredentials: credentials
);
return new JwtSecurityTokenHandler().WriteToken(token);
}
The token is signed with HMAC-SHA256 using a symmetric key loaded from configuration. This ensures that any modification to the token payload after issuance will invalidate the signature, making it impossible for a client to forge or tamper with claims. The token expires after 7 days.
JWT Validation
ASP.NET Core's built-in JwtBearer middleware intercepts every incoming HTTP request and validates the token before any controller action runs.
builder.Services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
.AddJwtBearer(options =>
{
options.TokenValidationParameters = new TokenValidationParameters
{
ValidateIssuer = true,
ValidateAudience = true,
ValidateLifetime = true,
ValidateIssuerSigningKey = true,
ValidIssuer = builder.Configuration["Jwt:Issuer"],
ValidAudience = builder.Configuration["Jwt:Audience"],
IssuerSigningKey = new SymmetricSecurityKey(
Encoding.UTF8.GetBytes(builder.Configuration["Jwt:Key"]!))
};
});
Security Filter Chain
ASP.NET Core processes requests through an ordered middleware pipeline. Authentication and authorization are placed after CORS and exception handling, and before controller mapping, which means the token is validated on every request regardless of which controller handles it.
Role-Based Access Control
The system defines three roles, derived at login time from the user's related entities in the database and embedded as a ClaimTypes.Role claim in the JWT.
var role = user.Admin != null ? "Admin"
: user.Writer != null ? "Writer"
: "RegularUser";
Note:
See TracWiki
for help on using the wiki.
