| Version 28 (modified by , 13 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,
s.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
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, s.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=11491685270.39..11491685274.39 rows=1600 width=1539\) \(actual time=1.816..1.822 rows=5 loops=1\) |
| Sort Key: with\_engagement.quarter DESC, \(rank\(\) OVER \(?\)\) |
| Sort Method: quicksort Memory: 26kB |
| -> WindowAgg \(cost=11491684914.18..11491685185.24 rows=1600 width=1539\) \(actual time=1.800..1.813 rows=5 loops=1\) |
| -> Incremental Sort \(cost=11491684914.18..11491685069.24 rows=1600 width=1363\) \(actual time=1.785..1.792 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=11491684913.52..11491685021.24 rows=1600 width=1363\) \(actual time=1.775..1.786 rows=5 loops=1\) |
| -> Incremental Sort \(cost=11491684913.52..11491684993.24 rows=1600 width=1355\) \(actual time=1.773..1.780 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=11491684913.24..11491684945.24 rows=1600 width=1355\) \(actual time=1.761..1.773 rows=5 loops=1\) |
| -> Sort \(cost=11491684913.24..11491684917.24 rows=1600 width=1347\) \(actual time=1.758..1.765 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=11491684724.09..11491684828.09 rows=1600 width=1347\) \(actual time=1.744..1.758 rows=5 loops=1\) |
| -> WindowAgg \(cost=11491684724.09..11491684812.09 rows=1600 width=1351\) \(actual time=1.743..1.756 rows=5 loops=1\) |
| -> Sort \(cost=11491684724.09..11491684728.09 rows=1600 width=1259\) \(actual time=1.732..1.738 rows=5 loops=1\) |
| Sort Key: quarterly\_story\_stats.story\_id, quarterly\_story\_stats.quarter |
| Sort Method: quicksort Memory: 25kB |
| -> Subquery Scan on quarterly\_story\_stats \(cost=11381017300.90..11491684638.94 rows=1600 width=1259\) \(actual time=1.473..1.733 rows=5 loops=1\) |
| -> GroupAggregate \(cost=11381017300.90..11491684622.94 rows=1600 width=1263\) \(actual time=1.472..1.731 rows=5 loops=1\) |
| Group Key: \(date\_trunc\('quarter'::text, s.story\_created\_at\)\), s.story\_id, u.user\_id |
| -> Sort \(cost=11381017300.90..11389530169.67 rows=3405147509 width=1211\) \(actual time=1.237..1.274 rows=939 loops=1\) |
| Sort Key: \(date\_trunc\('quarter'::text, s.story\_created\_at\)\), s.story\_id, u.user\_id, ch.chapter\_id |
| Sort Method: quicksort Memory: 195kB |
| -> Nested Loop Left Join \(cost=96.66..17916485.18 rows=3405147509 width=1211\) \(actual time=0.184..0.706 rows=939 loops=1\) |
| -> Nested Loop Left Join \(cost=96.50..199887.04 rows=73624811 width=1207\) \(actual time=0.170..0.331 rows=360 loops=1\) |
| -> Nested Loop Left Join \(cost=96.33..4359.52 rows=1303094 width=1203\) \(actual time=0.160..0.218 rows=120 loops=1\) |
| -> Hash Right Join \(cost=96.17..209.67 rows=28175 width=1199\) \(actual time=0.148..0.166 rows=28 loops=1\) |
| Hash Cond: \(c.story\_id = s.story\_id\) |
| -> Seq Scan on comment c \(cost=0.00..19.20 rows=920 width=8\) \(actual time=0.006..0.008 rows=11 loops=1\) |
| -> Hash \(cost=80.86..80.86 rows=1225 width=1195\) \(actual time=0.134..0.138 rows=12 loops=1\) |
| Buckets: 2048 Batches: 1 Memory Usage: 18kB |
| -> Hash Right Join \(cost=61.06..80.86 rows=1225 width=1195\) \(actual time=0.124..0.132 rows=12 loops=1\) |
| Hash Cond: \(col.story\_id = s.story\_id\) |
| -> Seq Scan on collaboration col \(cost=0.00..14.90 rows=490 width=8\) \(actual time=0.006..0.006 rows=2 loops=1\) |
| -> Hash \(cost=59.81..59.81 rows=100 width=1191\) \(actual time=0.111..0.114 rows=11 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 10kB |
| -> Hash Left Join \(cost=23.29..59.81 rows=100 width=1191\) \(actual time=0.094..0.105 rows=11 loops=1\) |
| Hash Cond: \(s.story\_id = ch.story\_id\) |
| -> Nested Loop \(cost=11.04..47.01 rows=40 width=1167\) \(actual time=0.072..0.080 rows=5 loops=1\) |
| Join Filter: \(u.user\_id = s.user\_id\) |
| -> Hash Join \(cost=10.90..38.76 rows=40 width=655\) \(actual time=0.053..0.057 rows=5 loops=1\) |
| Hash Cond: \(w.user\_id = s.user\_id\) |
| -> Seq Scan on writer w \(cost=0.00..22.70 rows=1270 width=4\) \(actual time=0.029..0.030 rows=5 loops=1\) |
| -> Hash \(cost=10.40..10.40 rows=40 width=651\) \(actual time=0.015..0.015 rows=5 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on story s \(cost=0.00..10.40 rows=40 width=651\) \(actual time=0.007..0.008 rows=5 loops=1\) |
| -> Index Scan using users\_pkey on users u \(cost=0.14..0.20 rows=1 width=520\) \(actual time=0.004..0.004 rows=1 loops=5\) |
| Index Cond: \(user\_id = w.user\_id\) |
| -> Hash \(cost=11.00..11.00 rows=100 width=28\) \(actual time=0.015..0.015 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.008..0.011 rows=11 loops=1\) |
| -> Memoize \(cost=0.16..15.33 rows=9 width=8\) \(actual time=0.001..0.001 rows=4 loops=28\) |
| Cache Key: s.story\_id |
| Cache Mode: logical |
| Hits: 23 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 2kB |
| -> Index Only Scan using like\_pk on likes l \(cost=0.15..15.32 rows=9 width=8\) \(actual time=0.003..0.004 rows=4 loops=5\) |
| Index Cond: \(story\_id = s.story\_id\) |
| Heap Fetches: 18 |
| -> Memoize \(cost=0.17..1.56 rows=11 width=8\) \(actual time=0.000..0.000 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 has\_genre\_pk on has\_genre hg \(cost=0.15..1.55 rows=11 width=8\) \(actual time=0.002..0.003 rows=3 loops=5\) |
| Index Cond: \(story\_id = s.story\_id\) |
| Heap Fetches: 15 |
| -> Memoize \(cost=0.16..15.33 rows=9 width=8\) \(actual time=0.000..0.000 rows=3 loops=360\) |
| Cache Key: s.story\_id |
| Cache Mode: logical |
| Hits: 355 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..15.32 rows=9 width=8\) \(actual time=0.002..0.003 rows=3 loops=5\) |
| Index Cond: \(story\_id = s.story\_id\) |
| Heap Fetches: 13 |
| Planning Time: 4.031 ms |
| Execution Time: 2.159 ms |
Average time without indexes is: 4.49 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 users;
| QUERY PLAN |
| :--- |
| Sort \(cost=115.88..116.01 rows=50 width=478\) \(actual time=1.845..1.853 rows=5 loops=1\) |
| Sort Key: with\_engagement.quarter DESC, \(rank\(\) OVER \(?\)\) |
| Sort Method: quicksort Memory: 26kB |
| -> WindowAgg \(cost=104.59..114.47 rows=50 width=478\) \(actual time=1.829..1.844 rows=5 loops=1\) |
| -> Incremental Sort \(cost=104.59..110.84 rows=50 width=302\) \(actual time=1.814..1.823 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=104.50..108.59 rows=50 width=302\) \(actual time=1.803..1.816 rows=5 loops=1\) |
| -> Incremental Sort \(cost=104.50..107.72 rows=50 width=294\) \(actual time=1.801..1.809 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=104.47..105.47 rows=50 width=294\) \(actual time=1.789..1.802 rows=5 loops=1\) |
| -> Sort \(cost=104.47..104.59 rows=50 width=286\) \(actual time=1.785..1.792 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=99.81..103.06 rows=50 width=286\) \(actual time=1.768..1.784 rows=5 loops=1\) |
| -> WindowAgg \(cost=99.81..102.56 rows=50 width=290\) \(actual time=1.766..1.782 rows=5 loops=1\) |
| -> Sort \(cost=99.81..99.93 rows=50 width=198\) \(actual time=1.750..1.758 rows=5 loops=1\) |
| Sort Key: quarterly\_story\_stats.story\_id, quarterly\_story\_stats.quarter |
| Sort Method: quicksort Memory: 25kB |
| -> Subquery Scan on quarterly\_story\_stats \(cost=74.73..98.40 rows=50 width=198\) \(actual time=1.463..1.752 rows=5 loops=1\) |
| -> GroupAggregate \(cost=74.73..97.90 rows=50 width=202\) \(actual time=1.463..1.750 rows=5 loops=1\) |
| Group Key: \(date\_trunc\('quarter'::text, s.story\_created\_at\)\), s.story\_id, u.user\_id |
| -> Sort \(cost=74.73..76.44 rows=686 width=145\) \(actual time=1.229..1.267 rows=939 loops=1\) |
| Sort Key: \(date\_trunc\('quarter'::text, s.story\_created\_at\)\), s.story\_id, u.user\_id, ch.chapter\_id |
| Sort Method: quicksort Memory: 195kB |
| -> Hash Left Join \(cost=7.83..42.41 rows=686 width=145\) \(actual time=0.236..0.641 rows=939 loops=1\) |
| Hash Cond: \(s.story\_id = rli.story\_id\) |
| -> Hash Left Join \(cost=6.54..30.89 rows=264 width=141\) \(actual time=0.218..0.335 rows=360 loops=1\) |
| Hash Cond: \(s.story\_id = l.story\_id\) |
| -> Hash Left Join \(cost=5.13..26.27 rows=72 width=137\) \(actual time=0.169..0.223 rows=84 loops=1\) |
| Hash Cond: \(s.story\_id = hg.story\_id\) |
| -> Hash Left Join \(cost=3.79..24.06 rows=24 width=133\) \(actual time=0.145..0.182 rows=28 loops=1\) |
| Hash Cond: \(s.story\_id = c.story\_id\) |
| -> Hash Left Join \(cost=2.54..22.50 rows=11 width=129\) \(actual time=0.082..0.113 rows=12 loops=1\) |
| Hash Cond: \(s.story\_id = ch.story\_id\) |
| -> Nested Loop Left Join \(cost=0.30..20.12 rows=5 width=110\) \(actual time=0.063..0.089 rows=6 loops=1\) |
| Join Filter: \(s.story\_id = col.story\_id\) |
| Rows Removed by Join Filter: 8 |
| -> Nested Loop \(cost=0.30..18.94 rows=5 width=106\) \(actual time=0.053..0.075 rows=5 loops=1\) |
| Join Filter: \(s.user\_id = u.user\_id\) |
| -> Nested Loop \(cost=0.16..18.07 rows=5 width=97\) \(actual time=0.046..0.060 rows=5 loops=1\) |
| -> Seq Scan on story s \(cost=0.00..1.05 rows=5 width=93\) \(actual time=0.027..0.028 rows=5 loops=1\) |
| -> Memoize \(cost=0.16..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.006..0.006 rows=1 loops=3\) |
| Index Cond: \(user\_id = s.user\_id\) |
| Heap Fetches: 3 |
| -> Index Only Scan using idx\_users\_covering on users u \(cost=0.14..0.16 rows=1 width=17\) \(actual time=0.002..0.002 rows=1 loops=5\) |
| Index Cond: \(user\_id = w.user\_id\) |
| Heap Fetches: 5 |
| -> Materialize \(cost=0.00..1.03 rows=2 width=8\) \(actual time=0.002..0.002 rows=2 loops=5\) |
| -> Seq Scan on collaboration col \(cost=0.00..1.02 rows=2 width=8\) \(actual time=0.006..0.007 rows=2 loops=1\) |
| -> Hash \(cost=2.11..2.11 rows=11 width=23\) \(actual time=0.014..0.015 rows=11 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on chapter ch \(cost=0.00..2.11 rows=11 width=23\) \(actual time=0.007..0.010 rows=11 loops=1\) |
| -> Hash \(cost=1.11..1.11 rows=11 width=8\) \(actual time=0.014..0.014 rows=11 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on comment c \(cost=0.00..1.11 rows=11 width=8\) \(actual time=0.009..0.010 rows=11 loops=1\) |
| -> Hash \(cost=1.15..1.15 rows=15 width=8\) \(actual time=0.011..0.012 rows=15 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on has\_genre hg \(cost=0.00..1.15 rows=15 width=8\) \(actual time=0.007..0.008 rows=15 loops=1\) |
| -> Hash \(cost=1.18..1.18 rows=18 width=8\) \(actual time=0.011..0.012 rows=18 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on likes l \(cost=0.00..1.18 rows=18 width=8\) \(actual time=0.006..0.008 rows=18 loops=1\) |
| -> Hash \(cost=1.13..1.13 rows=13 width=8\) \(actual time=0.009..0.009 rows=13 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on reading\_list\_items rli \(cost=0.00..1.13 rows=13 width=8\) \(actual time=0.005..0.006 rows=13 loops=1\) |
| Planning Time: 5.364 ms |
| Execution Time: 2.217 ms |
Average time: 2.78 ms
The covering indexes were created and verified across all joined tables. The average execution time is 4.49 ms without indexes and 2.78 ms with indexes, based on 10 runs each. In the execution plan, idx_users_covering and writer_pkey are used as Index Only Scans. The remaining tables (story, chapter, comment, has_genre, likes, reading_list_items, collaboration) use Seq Scan or Hash Join because the dataset is small enough that sequential scans and in-memory hashing remain cheaper than index lookups 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
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
WHERE s.status = 'published'
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=35.16..35.26 rows=40 width=221\) \(actual time=0.444..0.447 rows=4 loops=1\) |
| Sort Key: with\_growth.quarter DESC, \(rank\(\) OVER \(?\)\) |
| Sort Method: quicksort Memory: 25kB |
| -> WindowAgg \(cost=33.20..34.10 rows=40 width=221\) \(actual time=0.430..0.437 rows=4 loops=1\) |
| -> Sort \(cost=33.20..33.30 rows=40 width=181\) \(actual time=0.402..0.405 rows=4 loops=1\) |
| Sort Key: with\_growth.quarter, with\_growth.total\_views DESC |
| Sort Method: quicksort Memory: 25kB |
| -> Subquery Scan on with\_growth \(cost=29.43..32.13 rows=40 width=181\) \(actual time=0.389..0.398 rows=4 loops=1\) |
| -> WindowAgg \(cost=29.43..31.73 rows=40 width=209\) \(actual time=0.388..0.397 rows=4 loops=1\) |
| -> Sort \(cost=29.43..29.53 rows=40 width=121\) \(actual time=0.380..0.382 rows=4 loops=1\) |
| Sort Key: u.user\_id, \(date\_trunc\('quarter'::text, s.story\_created\_at\)\) |
| Sort Method: quicksort Memory: 25kB |
| -> GroupAggregate \(cost=25.92..28.37 rows=40 width=121\) \(actual time=0.337..0.375 rows=4 loops=1\) |
| Group Key: \(date\_trunc\('quarter'::text, s.story\_created\_at\)\), u.user\_id |
| -> Sort \(cost=25.92..26.09 rows=70 width=72\) \(actual time=0.286..0.292 rows=118 loops=1\) |
| Sort Key: \(date\_trunc\('quarter'::text, s.story\_created\_at\)\), u.user\_id, s.story\_id |
| Sort Method: quicksort Memory: 36kB |
| -> Hash Left Join \(cost=6.17..23.77 rows=70 width=72\) \(actual time=0.196..0.246 rows=118 loops=1\) |
| Hash Cond: \(s.story\_id = l.story\_id\) |
| -> Hash Left Join \(cost=4.76..21.30 rows=20 width=68\) \(actual time=0.166..0.183 rows=26 loops=1\) |
| Hash Cond: \(s.story\_id = c.story\_id\) |
| -> Hash Left Join \(cost=3.51..19.80 rows=9 width=64\) \(actual time=0.143..0.156 rows=10 loops=1\) |
| Hash Cond: \(s.story\_id = ch.story\_id\) |
| -> Nested Loop \(cost=1.27..17.43 rows=4 width=45\) \(actual time=0.122..0.133 rows=4 loops=1\) |
| Join Filter: \(s.user\_id = w.user\_id\) |
| -> Hash Join \(cost=1.11..2.30 rows=4 width=49\) \(actual time=0.102..0.105 rows=4 loops=1\) |
| Hash Cond: \(u.user\_id = s.user\_id\) |
| -> Seq Scan on users u \(cost=0.00..1.10 rows=10 width=33\) \(actual time=0.032..0.033 rows=10 loops=1\) |
| -> Hash \(cost=1.06..1.06 rows=4 width=16\) \(actual time=0.019..0.019 rows=4 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on story s \(cost=0.00..1.06 rows=4 width=16\) \(actual time=0.011..0.013 rows=4 loops=1\) |
| Filter: \(\(status\)::text = 'published'::text\) |
| Rows Removed by Filter: 1 |
| -> Index Only Scan using writer\_pkey on writer w \(cost=0.15..3.77 rows=1 width=4\) \(actual time=0.006..0.006 rows=1 loops=4\) |
| Index Cond: \(user\_id = u.user\_id\) |
| Heap Fetches: 4 |
| -> Hash \(cost=2.11..2.11 rows=11 width=23\) \(actual time=0.017..0.017 rows=11 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on chapter ch \(cost=0.00..2.11 rows=11 width=23\) \(actual time=0.009..0.013 rows=11 loops=1\) |
| -> Hash \(cost=1.11..1.11 rows=11 width=8\) \(actual time=0.018..0.018 rows=11 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on comment c \(cost=0.00..1.11 rows=11 width=8\) \(actual time=0.010..0.011 rows=11 loops=1\) |
| -> Hash \(cost=1.18..1.18 rows=18 width=8\) \(actual time=0.016..0.016 rows=18 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on likes l \(cost=0.00..1.18 rows=18 width=8\) \(actual time=0.010..0.012 rows=18 loops=1\) |
| Planning Time: 1.241 ms |
| Execution Time: 0.603 ms |
Average time: 1.596 ms
We create indexes
CREATE INDEX idx_story_status_published
ON story(story_id, status)
WHERE status = 'published';
CREATE INDEX idx_users_writer_covering
ON users(user_id, username, user_name, surname);
ANALYZE story;
ANALYZE users;
After indexes we get:
| QUERY PLAN |
| :--- |
| Sort \(cost=35.16..35.26 rows=40 width=221\) \(actual time=0.418..0.422 rows=4 loops=1\) |
| Sort Key: with\_growth.quarter DESC, \(rank\(\) OVER \(?\)\) |
| Sort Method: quicksort Memory: 25kB |
| -> WindowAgg \(cost=33.20..34.10 rows=40 width=221\) \(actual time=0.403..0.411 rows=4 loops=1\) |
| -> Sort \(cost=33.20..33.30 rows=40 width=181\) \(actual time=0.390..0.394 rows=4 loops=1\) |
| Sort Key: with\_growth.quarter, with\_growth.total\_views DESC |
| Sort Method: quicksort Memory: 25kB |
| -> Subquery Scan on with\_growth \(cost=29.43..32.13 rows=40 width=181\) \(actual time=0.377..0.387 rows=4 loops=1\) |
| -> WindowAgg \(cost=29.43..31.73 rows=40 width=209\) \(actual time=0.377..0.386 rows=4 loops=1\) |
| -> Sort \(cost=29.43..29.53 rows=40 width=121\) \(actual time=0.367..0.369 rows=4 loops=1\) |
| Sort Key: u.user\_id, \(date\_trunc\('quarter'::text, s.story\_created\_at\)\) |
| Sort Method: quicksort Memory: 25kB |
| -> GroupAggregate \(cost=25.92..28.37 rows=40 width=121\) \(actual time=0.258..0.362 rows=4 loops=1\) |
| Group Key: \(date\_trunc\('quarter'::text, s.story\_created\_at\)\), u.user\_id |
| -> Sort \(cost=25.92..26.09 rows=70 width=72\) \(actual time=0.215..0.285 rows=118 loops=1\) |
| Sort Key: \(date\_trunc\('quarter'::text, s.story\_created\_at\)\), u.user\_id, s.story\_id |
| Sort Method: quicksort Memory: 36kB |
| -> Hash Left Join \(cost=6.17..23.77 rows=70 width=72\) \(actual time=0.129..0.176 rows=118 loops=1\) |
| Hash Cond: \(s.story\_id = l.story\_id\) |
| -> Hash Left Join \(cost=4.76..21.30 rows=20 width=68\) \(actual time=0.113..0.128 rows=26 loops=1\) |
| Hash Cond: \(s.story\_id = c.story\_id\) |
| -> Hash Left Join \(cost=3.51..19.80 rows=9 width=64\) \(actual time=0.099..0.110 rows=10 loops=1\) |
| Hash Cond: \(s.story\_id = ch.story\_id\) |
| -> Nested Loop \(cost=1.27..17.43 rows=4 width=45\) \(actual time=0.078..0.087 rows=4 loops=1\) |
| Join Filter: \(w.user\_id = s.user\_id\) |
| -> Hash Join \(cost=1.11..2.30 rows=4 width=49\) \(actual time=0.060..0.063 rows=4 loops=1\) |
| Hash Cond: \(u.user\_id = s.user\_id\) |
| -> Seq Scan on users u \(cost=0.00..1.10 rows=10 width=33\) \(actual time=0.026..0.026 rows=10 loops=1\) |
| -> Hash \(cost=1.06..1.06 rows=4 width=16\) \(actual time=0.016..0.017 rows=4 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on story s \(cost=0.00..1.06 rows=4 width=16\) \(actual time=0.010..0.012 rows=4 loops=1\) |
| Filter: \(\(status\)::text = 'published'::text\) |
| Rows Removed by Filter: 1 |
| -> Index Only Scan using writer\_pkey on writer w \(cost=0.15..3.77 rows=1 width=4\) \(actual time=0.005..0.005 rows=1 loops=4\) |
| Index Cond: \(user\_id = u.user\_id\) |
| Heap Fetches: 4 |
| -> Hash \(cost=2.11..2.11 rows=11 width=23\) \(actual time=0.017..0.017 rows=11 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on chapter ch \(cost=0.00..2.11 rows=11 width=23\) \(actual time=0.008..0.012 rows=11 loops=1\) |
| -> Hash \(cost=1.11..1.11 rows=11 width=8\) \(actual time=0.011..0.011 rows=11 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on comment c \(cost=0.00..1.11 rows=11 width=8\) \(actual time=0.006..0.007 rows=11 loops=1\) |
| -> Hash \(cost=1.18..1.18 rows=18 width=8\) \(actual time=0.010..0.010 rows=18 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on likes l \(cost=0.00..1.18 rows=18 width=8\) \(actual time=0.005..0.007 rows=18 loops=1\) |
| Planning Time: 1.316 ms |
| Execution Time: 0.610 ms |
Average time: 0.998 ms Two indexes were created for Scenario 2: idx_story_status_published and idx_users_writer_covering. The planner naturally uses writer_pkey as an Index Only Scan; remaining tables (story, chapter, comment, likes) use Hash Joins with Seq Scans as they fit in memory at this data volume. Average execution time: 1.596 ms without indexes vs 0.998 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 AND s.status = 'published'
JOIN writer w ON s.user_id = w.user_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
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=127.28..127.80 rows=207 width=506\) \(actual time=0.668..0.671 rows=10 loops=1\) |
| Sort Key: \(to\_char\(with\_metrics.year, 'YYYY'::text\)\) DESC, \(rank\(\) OVER \(?\)\) |
| Sort Method: quicksort Memory: 26kB |
| -> WindowAgg \(cost=94.75..119.32 rows=207 width=506\) \(actual time=0.627..0.643 rows=10 loops=1\) |
| -> Incremental Sort \(cost=94.75..107.93 rows=207 width=386\) \(actual time=0.618..0.621 rows=10 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=94.72..98.86 rows=207 width=386\) \(actual time=0.600..0.611 rows=10 loops=1\) |
| -> Sort \(cost=94.72..95.24 rows=207 width=378\) \(actual time=0.599..0.602 rows=10 loops=1\) |
| Sort Key: with\_metrics.year, with\_metrics.engagement\_rate DESC |
| Sort Method: quicksort Memory: 26kB |
| -> Subquery Scan on with\_metrics \(cost=73.82..86.76 rows=207 width=378\) \(actual time=0.573..0.592 rows=10 loops=1\) |
| -> WindowAgg \(cost=73.82..84.69 rows=207 width=390\) \(actual time=0.572..0.590 rows=10 loops=1\) |
| -> Sort \(cost=73.82..74.34 rows=207 width=302\) \(actual time=0.563..0.565 rows=10 loops=1\) |
| Sort Key: genre\_annual.genre\_id, genre\_annual.year |
| Sort Method: quicksort Memory: 25kB |
| -> Subquery Scan on genre\_annual \(cost=55.51..65.86 rows=207 width=302\) \(actual time=0.430..0.558 rows=10 loops=1\) |
| -> GroupAggregate \(cost=55.51..63.79 rows=207 width=310\) \(actual time=0.429..0.557 rows=10 loops=1\) |
| Group Key: \(date\_trunc\('year'::text, s.story\_created\_at\)\), g.genre\_id |
| -> Sort \(cost=55.51..56.03 rows=207 width=257\) \(actual time=0.381..0.394 rows=354 loops=1\) |
| Sort Key: \(date\_trunc\('year'::text, s.story\_created\_at\)\), g.genre\_id, s.story\_id |
| Sort Method: quicksort Memory: 51kB |
| -> Hash Left Join \(cost=22.34..47.55 rows=207 width=257\) \(actual time=0.163..0.276 rows=354 loops=1\) |
| Hash Cond: \(s.story\_id = l.story\_id\) |
| -> Hash Join \(cost=20.93..43.08 rows=57 width=253\) \(actual time=0.144..0.170 rows=78 loops=1\) |
| Hash Cond: \(s.story\_id = hg.story\_id\) |
| -> Hash Left Join \(cost=3.66..25.08 rows=20 width=31\) \(actual time=0.114..0.128 rows=26 loops=1\) |
| Hash Cond: \(s.story\_id = c.story\_id\) |
| -> Hash Left Join \(cost=2.41..23.57 rows=9 width=27\) \(actual time=0.098..0.108 rows=10 loops=1\) |
| Hash Cond: \(s.story\_id = ch.story\_id\) |
| -> Nested Loop \(cost=0.16..21.21 rows=4 width=16\) \(actual time=0.070..0.079 rows=4 loops=1\) |
| -> Seq Scan on story s \(cost=0.00..1.06 rows=4 width=16\) \(actual time=0.044..0.045 rows=4 loops=1\) |
| Filter: \(\(status\)::text = 'published'::text\) |
| Rows Removed by Filter: 1 |
| -> Memoize \(cost=0.16..6.18 rows=1 width=4\) \(actual time=0.007..0.007 rows=1 loops=4\) |
| Cache Key: s.user\_id |
| Cache Mode: logical |
| Hits: 1 Misses: 3 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Only Scan using writer\_pkey on writer w \(cost=0.15..6.17 rows=1 width=4\) \(actual time=0.007..0.007 rows=1 loops=3\) |
| Index Cond: \(user\_id = s.user\_id\) |
| Heap Fetches: 3 |
| -> Hash \(cost=2.11..2.11 rows=11 width=15\) \(actual time=0.018..0.019 rows=11 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on chapter ch \(cost=0.00..2.11 rows=11 width=15\) \(actual time=0.008..0.013 rows=11 loops=1\) |
| -> Hash \(cost=1.11..1.11 rows=11 width=8\) \(actual time=0.013..0.013 rows=11 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on comment c \(cost=0.00..1.11 rows=11 width=8\) \(actual time=0.006..0.008 rows=11 loops=1\) |
| -> Hash \(cost=17.09..17.09 rows=15 width=226\) \(actual time=0.027..0.028 rows=15 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Hash Join \(cost=1.34..17.09 rows=15 width=226\) \(actual time=0.021..0.024 rows=15 loops=1\) |
| Hash Cond: \(g.genre\_id = hg.genre\_id\) |
| -> Seq Scan on genre g \(cost=0.00..13.20 rows=320 width=222\) \(actual time=0.005..0.006 rows=10 loops=1\) |
| -> Hash \(cost=1.15..1.15 rows=15 width=8\) \(actual time=0.010..0.010 rows=15 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on has\_genre hg \(cost=0.00..1.15 rows=15 width=8\) \(actual time=0.005..0.006 rows=15 loops=1\) |
| -> Hash \(cost=1.18..1.18 rows=18 width=8\) \(actual time=0.011..0.011 rows=18 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on likes l \(cost=0.00..1.18 rows=18 width=8\) \(actual time=0.006..0.007 rows=18 loops=1\) |
| Planning Time: 2.065 ms |
| Execution Time: 0.982 ms |
Average time without indexes is: 1.35 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=127.28..127.80 rows=207 width=506\) \(actual time=0.927..0.931 rows=10 loops=1\) |
| Sort Key: \(to\_char\(with\_metrics.year, 'YYYY'::text\)\) DESC, \(rank\(\) OVER \(?\)\) |
| Sort Method: quicksort Memory: 26kB |
| -> WindowAgg \(cost=94.75..119.32 rows=207 width=506\) \(actual time=0.886..0.903 rows=10 loops=1\) |
| -> Incremental Sort \(cost=94.75..107.93 rows=207 width=386\) \(actual time=0.876..0.879 rows=10 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=94.72..98.86 rows=207 width=386\) \(actual time=0.850..0.861 rows=10 loops=1\) |
| -> Sort \(cost=94.72..95.24 rows=207 width=378\) \(actual time=0.847..0.850 rows=10 loops=1\) |
| Sort Key: with\_metrics.year, with\_metrics.engagement\_rate DESC |
| Sort Method: quicksort Memory: 26kB |
| -> Subquery Scan on with\_metrics \(cost=73.82..86.76 rows=207 width=378\) \(actual time=0.820..0.840 rows=10 loops=1\) |
| -> WindowAgg \(cost=73.82..84.69 rows=207 width=390\) \(actual time=0.819..0.838 rows=10 loops=1\) |
| -> Sort \(cost=73.82..74.34 rows=207 width=302\) \(actual time=0.808..0.811 rows=10 loops=1\) |
| Sort Key: genre\_annual.genre\_id, genre\_annual.year |
| Sort Method: quicksort Memory: 25kB |
| -> Subquery Scan on genre\_annual \(cost=55.51..65.86 rows=207 width=302\) \(actual time=0.671..0.804 rows=10 loops=1\) |
| -> GroupAggregate \(cost=55.51..63.79 rows=207 width=310\) \(actual time=0.670..0.802 rows=10 loops=1\) |
| Group Key: \(date\_trunc\('year'::text, s.story\_created\_at\)\), g.genre\_id |
| -> Sort \(cost=55.51..56.03 rows=207 width=257\) \(actual time=0.567..0.580 rows=354 loops=1\) |
| Sort Key: \(date\_trunc\('year'::text, s.story\_created\_at\)\), g.genre\_id, s.story\_id |
| Sort Method: quicksort Memory: 51kB |
| -> Hash Left Join \(cost=22.34..47.55 rows=207 width=257\) \(actual time=0.325..0.444 rows=354 loops=1\) |
| Hash Cond: \(s.story\_id = l.story\_id\) |
| -> Hash Join \(cost=20.93..43.08 rows=57 width=253\) \(actual time=0.281..0.310 rows=78 loops=1\) |
| Hash Cond: \(s.story\_id = hg.story\_id\) |
| -> Hash Left Join \(cost=3.66..25.08 rows=20 width=31\) \(actual time=0.186..0.202 rows=26 loops=1\) |
| Hash Cond: \(s.story\_id = c.story\_id\) |
| -> Hash Left Join \(cost=2.41..23.57 rows=9 width=27\) \(actual time=0.154..0.166 rows=10 loops=1\) |
| Hash Cond: \(s.story\_id = ch.story\_id\) |
| -> Nested Loop \(cost=0.16..21.21 rows=4 width=16\) \(actual time=0.113..0.123 rows=4 loops=1\) |
| -> Seq Scan on story s \(cost=0.00..1.06 rows=4 width=16\) \(actual time=0.059..0.062 rows=4 loops=1\) |
| Filter: \(\(status\)::text = 'published'::text\) |
| Rows Removed by Filter: 1 |
| -> Memoize \(cost=0.16..6.18 rows=1 width=4\) \(actual time=0.014..0.014 rows=1 loops=4\) |
| Cache Key: s.user\_id |
| Cache Mode: logical |
| Hits: 1 Misses: 3 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Only Scan using writer\_pkey on writer w \(cost=0.15..6.17 rows=1 width=4\) \(actual time=0.015..0.015 rows=1 loops=3\) |
| Index Cond: \(user\_id = s.user\_id\) |
| Heap Fetches: 3 |
| -> Hash \(cost=2.11..2.11 rows=11 width=15\) \(actual time=0.028..0.028 rows=11 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on chapter ch \(cost=0.00..2.11 rows=11 width=15\) \(actual time=0.013..0.021 rows=11 loops=1\) |
| -> Hash \(cost=1.11..1.11 rows=11 width=8\) \(actual time=0.022..0.022 rows=11 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on comment c \(cost=0.00..1.11 rows=11 width=8\) \(actual time=0.015..0.017 rows=11 loops=1\) |
| -> Hash \(cost=17.09..17.09 rows=15 width=226\) \(actual time=0.087..0.087 rows=15 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Hash Join \(cost=1.34..17.09 rows=15 width=226\) \(actual time=0.041..0.079 rows=15 loops=1\) |
| Hash Cond: \(g.genre\_id = hg.genre\_id\) |
| -> Seq Scan on genre g \(cost=0.00..13.20 rows=320 width=222\) \(actual time=0.013..0.013 rows=10 loops=1\) |
| -> Hash \(cost=1.15..1.15 rows=15 width=8\) \(actual time=0.019..0.019 rows=15 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on has\_genre hg \(cost=0.00..1.15 rows=15 width=8\) \(actual time=0.013..0.015 rows=15 loops=1\) |
| -> Hash \(cost=1.18..1.18 rows=18 width=8\) \(actual time=0.019..0.019 rows=18 loops=1\) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on likes l \(cost=0.00..1.18 rows=18 width=8\) \(actual time=0.011..0.012 rows=18 loops=1\) |
| Planning Time: 1.757 ms |
| Execution Time: 1.185 ms |
Average time: 0.910 ms
Two new indexes were created for this scenario: idx_story_created_user and idx_chapter_aggregates. Combined with indexes from previous scenarios, writer_pkey is used naturally in the execution plan without any forcing flags. Average execution time: 1.351 ms without indexes vs 0.910 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";
Password Security
Passwords must never be stored as plaintext in the database. Instead, ChapterX uses BCrypt to hash every password before it is saved. BCrypt is configured with a default work factor of 11 rounds, which makes brute-force attacks computationally expensive:
var user = new Domain.Entities.User
{
Username = request.Username,
Email = request.Email,
Password = BCrypt.Net.BCrypt.HashPassword(request.Password),
...
};
At login, the submitted password is verified against the stored hash without ever reconstructing the original:
if (!BCrypt.Net.BCrypt.Verify(request.Password, user.Password))
throw new UnauthorizedAccessException("Invalid email or password.");
SQL Injection Prevention
To reduce the risk of SQL injection, ChapterX uses Entity Framework Core as its ORM instead of writing raw SQL queries. EF Core automatically generates parameterized queries under the hood, user-supplied values are never concatenated directly into query strings.
Because all database access goes through EF Core's API, parameterization is handled implicitly:
public async Task<T?> GetByIdAsync(int id, CancellationToken cancellationToken = default)
=> await _dbSet.FindAsync([id], cancellationToken);
public async Task AddAsync(T entity, CancellationToken cancellationToken = default)
{
await _dbSet.AddAsync(entity, cancellationToken);
await _context.SaveChangesAsync(cancellationToken);
}
Custom queries in repositories follow the same pattern — LINQ expressions are translated by EF Core into parameterized SQL, so user input is always treated as a value, never as executable SQL:
public async Task<User?> GetByEmailAsync(string email, CancellationToken cancellationToken = default)
=> await _dbSet.FirstOrDefaultAsync(u => u.Email == email, cancellationToken);
public async Task<IEnumerable<Chapter>> GetByStoryIdAsync(int storyId, CancellationToken cancellationToken = default)
=> await _dbSet.Where(c => c.StoryId == storyId).ToListAsync(cancellationToken);
CORS Configuration
ChapterX defines a CORS policy that restricts which origins are allowed to make requests to the API. Without this, any website could send requests on behalf of a logged-in user from their browser. The policy is registered in Program.cs and applied globally before any other middleware:
builder.Services.AddCors(options =>
{
options.AddPolicy("Frontend", policy =>
policy.WithOrigins("http://localhost:5173", "https://localhost:5173")
.AllowAnyHeader()
.AllowAnyMethod());
});
app.UseCors("Frontend");
Only the frontend development server (localhost:5173) is whitelisted as an allowed origin. Requests originating from any other domain are rejected at the browser level before they reach any controller logic. In a production environment, localhost:5173 should be replaced with the actual deployed frontend domain.
Note:
See TracWiki
for help on using the wiki.
