= 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 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 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 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 GetByEmailAsync(string email, CancellationToken cancellationToken = default) => await _dbSet.FirstOrDefaultAsync(u => u.Email == email, cancellationToken); public async Task> 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.