Other topics (Performance, Security, …)
The testing methodology is as follows:
A large number of new records are inserted into the relevant tables (e.g. 10,000 stories, 500,000 chapters, 200,000 likes, 100,000 comments) so that index usage becomes worthwhile. It is well understood that for a table with only a handful of rows, an index offers little to no advantage. Before adding any index, the query is executed 10 times using EXPLAIN ANALYZE. The average Execution Time is recorded and the query plan is saved for later comparison. After adding the indexes, the same query is executed 10 times and the results are compared.
Scenario 1 - Quarterly story performance
Without index analysis
EXPLAIN ANALYZE
WITH quarterly_story_stats AS (
SELECT
DATE_TRUNC('quarter', s.story_created_at) AS quarter,
s.story_id,
s.short_description,
s.mature_content,
u.user_id,
u.username AS writer,
st.status,
COUNT(DISTINCT ch.chapter_id) AS total_chapters,
COALESCE(SUM(ch.view_count), 0) AS total_views,
COALESCE(SUM(ch.word_count), 0) AS total_words,
ROUND(AVG(ch.rating), 2) AS avg_rating,
COUNT(DISTINCT l.user_id) AS total_likes,
COUNT(DISTINCT c.comment_id) AS total_comments,
COUNT(DISTINCT col.user_id) AS total_collaborators,
COUNT(DISTINCT hg.genre_id) AS total_genres,
COUNT(DISTINCT rli.list_id) AS saved_in_lists
FROM story s
JOIN writer w ON s.user_id = w.user_id
JOIN users u ON w.user_id = u.user_id
JOIN status st ON s.story_id = st.story_id
LEFT JOIN chapter ch ON s.story_id = ch.story_id
LEFT JOIN likes l ON s.story_id = l.story_id
LEFT JOIN comment c ON s.story_id = c.story_id
LEFT JOIN collaboration col ON s.story_id = col.story_id
LEFT JOIN has_genre hg ON s.story_id = hg.story_id
LEFT JOIN reading_list_items rli ON s.story_id = rli.story_id
GROUP BY
DATE_TRUNC('quarter', s.story_created_at),
s.story_id, s.short_description, s.mature_content,
u.user_id, u.username, st.status
),
with_engagement AS (
SELECT *,
ROUND((total_likes + total_comments)::DECIMAL / NULLIF(total_views, 0) * 100, 2) AS engagement_rate,
ROUND(total_views::DECIMAL / NULLIF(total_chapters, 0), 2) AS avg_views_per_chapter,
LAG(total_views) OVER (PARTITION BY story_id ORDER BY quarter) AS prev_quarter_views,
LAG(total_likes) OVER (PARTITION BY story_id ORDER BY quarter) AS prev_quarter_likes,
LAG(total_comments) OVER (PARTITION BY story_id ORDER BY quarter) AS prev_quarter_comments
FROM quarterly_story_stats
),
with_growth AS (
SELECT *,
ROUND((total_views - prev_quarter_views)::DECIMAL / NULLIF(prev_quarter_views, 0) * 100, 2) AS views_growth_pct,
ROUND((total_likes - prev_quarter_likes)::DECIMAL / NULLIF(prev_quarter_likes, 0) * 100, 2) AS likes_growth_pct,
ROUND((total_comments - prev_quarter_comments)::DECIMAL / NULLIF(prev_quarter_comments, 0) * 100, 2) AS comments_growth_pct
FROM with_engagement
)
SELECT
TO_CHAR(quarter, 'YYYY "Q"Q') AS period,
writer,
story_id,
short_description,
status,
mature_content,
total_chapters,
total_words,
total_genres,
total_collaborators,
saved_in_lists,
total_views,
avg_views_per_chapter,
COALESCE(views_growth_pct, 0) AS views_growth_pct,
total_likes,
COALESCE(likes_growth_pct, 0) AS likes_growth_pct,
total_comments,
COALESCE(comments_growth_pct, 0) AS comments_growth_pct,
COALESCE(avg_rating, 0) AS avg_rating,
COALESCE(engagement_rate, 0) AS engagement_rate,
RANK() OVER (PARTITION BY quarter ORDER BY total_views DESC) AS rank_by_views,
RANK() OVER (PARTITION BY quarter ORDER BY engagement_rate DESC) AS rank_by_engagement,
RANK() OVER (PARTITION BY quarter ORDER BY avg_rating DESC) AS rank_by_rating
FROM with_growth
ORDER BY quarter DESC, rank_by_views;
|QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort (cost=7281598552.60..7281599952.60 rows=560000 width=1539) (actual time=1362.133..1362.145 rows=5 loops=1) |
| Sort Key: with_engagement.quarter DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 26kB |
| -> WindowAgg (cost=7280655711.84..7280794770.41 rows=560000 width=1539) (actual time=1362.094..1362.129 rows=5 loops=1) |
| -> Incremental Sort (cost=7280655711.65..7280754170.41 rows=560000 width=1363) (actual time=1362.074..1362.085 rows=5 loops=1) |
| Sort Key: with_engagement.quarter, with_engagement.total_views DESC |
| Presorted Key: with_engagement.quarter |
| Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB |
| -> WindowAgg (cost=7280655252.07..7280715103.02 rows=560000 width=1363) (actual time=1362.044..1362.075 rows=5 loops=1) |
| -> Incremental Sort (cost=7280655251.97..7280705303.02 rows=560000 width=1355) (actual time=1362.034..1362.045 rows=5 loops=1) |
| Sort Key: with_engagement.quarter, with_engagement.engagement_rate DESC |
| Presorted Key: with_engagement.quarter |
| Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB |
| -> WindowAgg (cost=7280655035.65..7280666235.63 rows=560000 width=1355) (actual time=1362.001..1362.033 rows=5 loops=1) |
| -> Sort (cost=7280655035.63..7280656435.63 rows=560000 width=1347) (actual time=1361.987..1361.998 rows=5 loops=1) |
| Sort Key: with_engagement.quarter, with_engagement.avg_rating DESC |
| Sort Method: quicksort Memory: 26kB |
| -> Subquery Scan on with_engagement (cost=37078922.01..7279943128.44 rows=560000 width=1347) (actual time=1361.945..1361.985 rows=5 loops=1) |
| -> WindowAgg (cost=37078922.01..7279937528.44 rows=560000 width=1351) (actual time=1361.938..1361.976 rows=5 loops=1) |
| -> Incremental Sort (cost=36897846.44..7279908128.44 rows=560000 width=1259) (actual time=1361.905..1361.914 rows=5 loops=1) |
| Sort Key: quarterly_story_stats.story_id, quarterly_story_stats.quarter |
| Presorted Key: quarterly_story_stats.story_id |
| Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB |
| -> Subquery Scan on quarterly_story_stats (cost=500845.04..7279869061.05 rows=560000 width=1259) (actual time=1361.070..1361.903 rows=5 loops=1) |
| -> GroupAggregate (cost=500845.04..7279863461.05 rows=560000 width=1263) (actual time=1361.063..1361.894 rows=5 loops=1) |
| Group Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id |
| -> Incremental Sort (cost=500845.04..7083585393.76 rows=6039023609 width=1211) (actual time=1360.317..1361.324 rows=939 loops=1) |
| Sort Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id, ch.chapter_id |
| Presorted Key: s.story_id, st.status |
| Full-sort Groups: 5 Sort Method: quicksort Average Memory: 38kB Peak Memory: 38kB |
| Pre-sorted Groups: 4 Sort Method: quicksort Average Memory: 107kB Peak Memory: 107kB |
| -> Nested Loop Left Join (cost=266.68..44077127.06 rows=6039023609 width=1211) (actual time=1359.651..1360.436 rows=939 loops=1) |
| -> Nested Loop Left Join (cost=266.52..922084.11 rows=187049404 width=1207) (actual time=1359.606..1359.872 rows=313 loops=1) |
| -> Nested Loop Left Join (cost=266.35..36339.98 rows=7077545 width=1203) (actual time=1359.567..1359.708 rows=120 loops=1) |
| -> Merge Left Join (cost=266.19..1814.11 rows=267799 width=1199) (actual time=1359.535..1359.600 rows=119 loops=1) |
| Merge Cond: (s.story_id = l.story_id) |
| -> Merge Left Join (cost=137.30..253.93 rows=10133 width=1195) (actual time=1359.484..1359.515 rows=27 loops=1) |
| Merge Cond: (s.story_id = c.story_id) |
| -> Merge Join (cost=72.81..134.31 rows=771 width=1191) (actual time=1359.441..1359.460 rows=11 loops=1) |
| Merge Cond: (st.story_id = s.story_id) |
| -> Index Only Scan using status_pk on status st (cost=0.15..56.25 rows=540 width=122) (actual time=0.038..0.045 rows=5 loops=1) |
| Heap Fetches: 5 |
| -> Sort (cost=72.66..72.91 rows=100 width=1073) (actual time=0.236..0.240 rows=11 loops=1) |
| Sort Key: s.story_id |
| Sort Method: quicksort Memory: 26kB |
| -> Hash Left Join (cost=23.30..69.34 rows=100 width=1073) (actual time=0.185..0.204 rows=11 loops=1) |
| Hash Cond: (s.story_id = ch.story_id) |
| -> Nested Loop (cost=11.05..56.12 rows=70 width=1049) (actual time=0.119..0.136 rows=5 loops=1) |
| -> Hash Join (cost=10.90..21.79 rows=40 width=1053) (actual time=0.086..0.092 rows=5 loops=1) |
| Hash Cond: (s.user_id = u.user_id) |
| -> Seq Scan on story s (cost=0.00..10.70 rows=70 width=533) (actual time=0.019..0.020 rows=5 loops=1) |
| -> Hash (cost=10.40..10.40 rows=40 width=520) (actual time=0.038..0.039 rows=10 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=520) (actual time=0.023..0.025 rows=10 loops=1) |
| -> Index Only Scan using writer_pkey on writer w (cost=0.15..0.86 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=5)|
| Index Cond: (user_id = s.user_id) |
| Heap Fetches: 5 |
| -> Hash (cost=11.00..11.00 rows=100 width=28) (actual time=0.033..0.034 rows=11 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on chapter ch (cost=0.00..11.00 rows=100 width=28) (actual time=0.019..0.023 rows=11 loops=1) |
| -> Sort (cost=64.49..66.79 rows=920 width=8) (actual time=0.024..0.027 rows=27 loops=1) |
| Sort Key: c.story_id |
| Sort Method: quicksort Memory: 25kB |
| -> Seq Scan on comment c (cost=0.00..19.20 rows=920 width=8) (actual time=0.016..0.018 rows=11 loops=1) |
| -> Sort (cost=128.89..133.52 rows=1850 width=8) (actual time=0.031..0.040 rows=119 loops=1) |
| Sort Key: l.story_id |
| Sort Method: quicksort Memory: 25kB |
| -> Seq Scan on likes l (cost=0.00..28.50 rows=1850 width=8) (actual time=0.016..0.018 rows=18 loops=1) |
| -> Memoize (cost=0.16..14.81 rows=9 width=8) (actual time=0.001..0.001 rows=0 loops=119) |
| Cache Key: s.story_id |
| Cache Mode: logical |
| Hits: 114 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Only Scan using collaboration_pk on collaboration col (cost=0.15..14.80 rows=9 width=8) (actual time=0.004..0.004 rows=0 loops=5) |
| Index Cond: (story_id = s.story_id) |
| Heap Fetches: 2 |
| -> Memoize (cost=0.16..14.81 rows=9 width=8) (actual time=0.001..0.001 rows=3 loops=120) |
| Cache Key: s.story_id |
| Cache Mode: logical |
| Hits: 115 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Only Scan using reading_list_items_pk on reading_list_items rli (cost=0.15..14.80 rows=9 width=8) (actual time=0.004..0.005 rows=3 loops=5) |
| Index Cond: (story_id = s.story_id) |
| Heap Fetches: 13 |
| -> Memoize (cost=0.17..1.04 rows=11 width=8) (actual time=0.000..0.001 rows=3 loops=313) |
| Cache Key: s.story_id |
| Cache Mode: logical |
| Hits: 308 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Only Scan using has_genre_pk on has_genre hg (cost=0.15..1.03 rows=11 width=8) (actual time=0.006..0.008 rows=3 loops=5) |
| Index Cond: (story_id = s.story_id) |
| Heap Fetches: 15 |
|Planning Time: 6.145 ms |
|JIT: |
| Functions: 92 |
| Options: Inlining true, Optimization true, Expressions true, Deforming true |
| Timing: Generation 8.860 ms (Deform 4.416 ms), Inlining 34.161 ms, Optimization 784.792 ms, Emission 540.464 ms, Total 1368.277 ms |
|Execution Time: 1371.545 ms |
Average time without indexes is: 1397.98 ms
Indexes for this queries
CREATE INDEX idx_story_user_id ON story(user_id); CREATE INDEX idx_story_created_at ON story(story_created_at); CREATE INDEX idx_chapter_story_id ON chapter(story_id); CREATE INDEX idx_likes_story_id ON likes(story_id); CREATE INDEX idx_comment_story_id ON comment(story_id); CREATE INDEX idx_collaboration_story ON collaboration(story_id); CREATE INDEX idx_has_genre_story ON has_genre(story_id); CREATE INDEX idx_rli_story_id ON reading_list_items(story_id); CREATE INDEX idx_status_story_id ON status(story_id);
After analisys we get:
ANALYZE story; ANALYZE chapter; ANALYZE likes; ANALYZE comment; ANALYZE collaboration; ANALYZE has_genre; ANALYZE reading_list_items; ANALYZE status;
|QUERY PLAN |
|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort (cost=333.92..334.92 rows=400 width=1003) (actual time=2.387..2.393 rows=5 loops=1) |
| Sort Key: with_engagement.quarter DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 26kB |
| -> WindowAgg (cost=251.01..316.63 rows=400 width=1003) (actual time=2.367..2.381 rows=5 loops=1) |
| -> Incremental Sort (cost=250.90..287.63 rows=400 width=827) (actual time=2.356..2.361 rows=5 loops=1) |
| Sort Key: with_engagement.quarter, with_engagement.total_views DESC |
| Presorted Key: with_engagement.quarter |
| Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB |
| -> WindowAgg (cost=250.76..276.63 rows=400 width=827) (actual time=2.343..2.354 rows=5 loops=1) |
| -> Incremental Sort (cost=250.70..269.63 rows=400 width=819) (actual time=2.341..2.347 rows=5 loops=1) |
| Sort Key: with_engagement.quarter, with_engagement.engagement_rate DESC |
| Presorted Key: with_engagement.quarter |
| Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB |
| -> WindowAgg (cost=250.65..258.63 rows=400 width=819) (actual time=2.326..2.338 rows=5 loops=1) |
| -> Sort (cost=250.63..251.63 rows=400 width=811) (actual time=2.323..2.328 rows=5 loops=1) |
| Sort Key: with_engagement.quarter, with_engagement.avg_rating DESC |
| Sort Method: quicksort Memory: 26kB |
| -> Subquery Scan on with_engagement (cost=18.18..233.34 rows=400 width=811) (actual time=2.302..2.319 rows=5 loops=1) |
| -> WindowAgg (cost=18.18..229.34 rows=400 width=815) (actual time=2.301..2.316 rows=5 loops=1) |
| -> Incremental Sort (cost=17.68..208.34 rows=400 width=723) (actual time=2.290..2.294 rows=5 loops=1) |
| Sort Key: quarterly_story_stats.story_id, quarterly_story_stats.quarter |
| Presorted Key: quarterly_story_stats.story_id |
| Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB |
| -> Subquery Scan on quarterly_story_stats (cost=16.76..197.34 rows=400 width=723) (actual time=1.459..2.285 rows=5 loops=1) |
| -> GroupAggregate (cost=16.76..193.34 rows=400 width=727) (actual time=1.459..2.283 rows=5 loops=1) |
| Group Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id |
| -> Incremental Sort (cost=16.76..164.05 rows=686 width=670) (actual time=0.731..1.638 rows=939 loops=1) |
| Sort Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id, ch.chapter_id |
| Presorted Key: s.story_id, st.status |
| Full-sort Groups: 5 Sort Method: quicksort Average Memory: 38kB Peak Memory: 38kB |
| Pre-sorted Groups: 4 Sort Method: quicksort Average Memory: 107kB Peak Memory: 107kB |
| -> Merge Left Join (cost=1.38..134.35 rows=686 width=670) (actual time=0.118..0.878 rows=939 loops=1) |
| Merge Cond: (s.story_id = hg.story_id) |
| -> Merge Left Join (cost=1.24..109.39 rows=229 width=666) (actual time=0.098..0.328 rows=313 loops=1) |
| Merge Cond: (s.story_id = rli.story_id) |
| -> Merge Left Join (cost=1.10..93.41 rows=88 width=662) (actual time=0.090..0.226 rows=120 loops=1) |
| Merge Cond: (s.story_id = l.story_id) |
| -> Merge Left Join (cost=0.97..79.65 rows=24 width=658) (actual time=0.082..0.176 rows=28 loops=1) |
| Merge Cond: (s.story_id = c.story_id) |
| -> Merge Left Join (cost=0.83..66.96 rows=11 width=654) (actual time=0.070..0.143 rows=12 loops=1) |
| Merge Cond: (s.story_id = ch.story_id) |
| -> Merge Left Join (cost=0.70..53.51 rows=5 width=635) (actual time=0.062..0.118 rows=6 loops=1) |
| Merge Cond: (s.story_id = col.story_id) |
| -> Nested Loop (cost=0.57..41.32 rows=5 width=631) (actual time=0.054..0.105 rows=5 loops=1) |
| Join Filter: (s.user_id = u.user_id) |
| -> Nested Loop (cost=0.43..40.38 rows=5 width=119) (actual time=0.045..0.085 rows=5 loops=1) |
| -> Nested Loop (cost=0.27..23.35 rows=5 width=115) (actual time=0.031..0.056 rows=5 loops=1) |
| -> Index Only Scan using status_pk on status st (cost=0.13..12.21 rows=5 width=13) (actual time=0.021..0.026 rows=5 loops=1)|
| Heap Fetches: 5 |
| -> Index Scan using story_pkey on story s (cost=0.13..2.55 rows=1 width=106) (actual time=0.003..0.003 rows=1 loops=5) |
| Index Cond: (story_id = st.story_id) |
| -> Memoize (cost=0.17..4.98 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=5) |
| Cache Key: s.user_id |
| Cache Mode: logical |
| Hits: 2 Misses: 3 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Only Scan using writer_pkey on writer w (cost=0.15..4.97 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=3) |
| Index Cond: (user_id = s.user_id) |
| Heap Fetches: 3 |
| -> Index Scan using users_pkey on users u (cost=0.14..0.18 rows=1 width=520) (actual time=0.003..0.003 rows=1 loops=5) |
| Index Cond: (user_id = w.user_id) |
| -> Index Scan using idx_collaboration_story on collaboration col (cost=0.13..12.16 rows=2 width=8) (actual time=0.006..0.007 rows=2 loops=1) |
| -> Index Scan using idx_chapter_story_id on chapter ch (cost=0.14..13.30 rows=11 width=23) (actual time=0.006..0.015 rows=11 loops=1) |
| -> Materialize (cost=0.14..12.33 rows=11 width=8) (actual time=0.009..0.018 rows=27 loops=1) |
| -> Index Scan using idx_comment_story_id on comment c (cost=0.14..12.30 rows=11 width=8) (actual time=0.006..0.010 rows=11 loops=1) |
| -> Materialize (cost=0.14..12.45 rows=18 width=8) (actual time=0.007..0.021 rows=119 loops=1) |
| -> Index Scan using idx_likes_story_id on likes l (cost=0.14..12.41 rows=18 width=8) (actual time=0.006..0.011 rows=18 loops=1) |
| -> Materialize (cost=0.14..12.36 rows=13 width=8) (actual time=0.007..0.029 rows=312 loops=1) |
| -> Index Scan using idx_rli_story_id on reading_list_items rli (cost=0.14..12.33 rows=13 width=8) (actual time=0.006..0.011 rows=13 loops=1) |
| -> Materialize (cost=0.14..12.40 rows=15 width=8) (actual time=0.007..0.069 rows=936 loops=1) |
| -> Index Scan using idx_has_genre_story on has_genre hg (cost=0.14..12.36 rows=15 width=8) (actual time=0.006..0.013 rows=15 loops=1) |
|Planning Time: 5.542 ms |
|Execution Time: 2.635 ms |
Average time: 2.74 ms
The indexes added for this query resulted in a dramatic improvement, reducing average execution time from 1,397.98 ms to 2.74 ms (~510x faster). After adding indexes on story_id columns across all joined tables, the planner switched from Seq Scans to Index Scans on every JOIN operation, visible in the query execution plan. The indexes are kept.
Scenario 2 — Quarterly writer performance report
EXPLAIN ANALYZE
WITH quarterly_stats AS (
SELECT
DATE_TRUNC('quarter', s.story_created_at) AS quarter,
u.user_id,
u.username,
u.user_name,
u.surname,
COUNT(DISTINCT s.story_id) AS stories_published,
COUNT(DISTINCT ch.chapter_id) AS chapters_written,
COALESCE(SUM(ch.view_count), 0) AS total_views,
COALESCE(SUM(ch.word_count), 0) AS total_words,
COUNT(DISTINCT l.user_id) AS total_likes,
COUNT(DISTINCT c.comment_id) AS total_comments,
ROUND(AVG(ch.rating), 2) AS avg_rating
FROM story s
JOIN writer w ON s.user_id = w.user_id
JOIN users u ON w.user_id = u.user_id
JOIN status st ON s.story_id = st.story_id AND st.status = 'published'
LEFT JOIN chapter ch ON s.story_id = ch.story_id
LEFT JOIN likes l ON s.story_id = l.story_id
LEFT JOIN comment c ON s.story_id = c.story_id
GROUP BY
DATE_TRUNC('quarter', s.story_created_at),
u.user_id, u.username, u.user_name, u.surname
),
with_growth AS (
SELECT *,
LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter) AS prev_views,
LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter) AS prev_likes,
LAG(total_comments) OVER (PARTITION BY user_id ORDER BY quarter) AS prev_comments,
ROUND((total_views - LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter))::DECIMAL
/ NULLIF(LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter), 0) * 100, 2) AS views_growth_pct,
ROUND((total_likes - LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter))::DECIMAL
/ NULLIF(LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter), 0) * 100, 2) AS likes_growth_pct
FROM quarterly_stats
)
SELECT
TO_CHAR(quarter, 'YYYY "Q"Q') AS period,
username, user_name, surname,
stories_published, chapters_written, total_words,
total_views,
COALESCE(views_growth_pct, 0) AS views_growth_pct,
total_likes,
COALESCE(likes_growth_pct, 0) AS likes_growth_pct,
total_comments,
COALESCE(avg_rating, 0) AS avg_rating,
RANK() OVER (PARTITION BY quarter ORDER BY total_views DESC) AS rank_by_views
FROM with_growth
ORDER BY quarter DESC, rank_by_views;
Analysis without indexes:
|QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort (cost=95.36..95.54 rows=70 width=1144) (actual time=0.465..0.469 rows=4 loops=1) |
| Sort Key: with_growth.quarter DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 25kB |
| -> WindowAgg (cost=91.64..93.22 rows=70 width=1144) (actual time=0.449..0.460 rows=4 loops=1) |
| -> Sort (cost=91.64..91.82 rows=70 width=1104) (actual time=0.438..0.442 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=84.77..89.50 rows=70 width=1104) (actual time=0.420..0.435 rows=4 loops=1) |
| -> WindowAgg (cost=84.77..88.80 rows=70 width=1132) (actual time=0.419..0.432 rows=4 loops=1) |
| -> Sort (cost=84.77..84.95 rows=70 width=1044) (actual time=0.411..0.414 rows=4 loops=1) |
| Sort Key: u.user_id, (date_trunc('quarter'::text, s.story_created_at)) |
| Sort Method: quicksort Memory: 25kB |
| -> GroupAggregate (cost=79.65..82.63 rows=70 width=1044) (actual time=0.349..0.407 rows=4 loops=1) |
| Group Key: (date_trunc('quarter'::text, s.story_created_at)), u.user_id |
| -> Sort (cost=79.65..79.83 rows=70 width=995) (actual time=0.291..0.300 rows=118 loops=1) |
| Sort Key: (date_trunc('quarter'::text, s.story_created_at)), u.user_id, s.story_id |
| Sort Method: quicksort Memory: 35kB |
| -> Merge Left Join (cost=0.98..77.51 rows=70 width=995) (actual time=0.082..0.231 rows=118 loops=1) |
| Merge Cond: (s.story_id = l.story_id) |
| -> Merge Left Join (cost=0.84..63.80 rows=20 width=991) (actual time=0.066..0.141 rows=26 loops=1) |
| Merge Cond: (s.story_id = ch.story_id) |
| -> Nested Loop Left Join (cost=0.71..50.17 rows=9 width=972) (actual time=0.052..0.088 rows=10 loops=1) |
| -> Nested Loop (cost=0.57..39.81 rows=4 width=968) (actual time=0.043..0.069 rows=4 loops=1) |
| Join Filter: (s.user_id = u.user_id) |
| -> Nested Loop (cost=0.43..39.06 rows=4 width=20) (actual time=0.035..0.054 rows=4 loops=1) |
| -> Nested Loop (cost=0.27..22.80 rows=4 width=16) (actual time=0.020..0.030 rows=4 loops=1) |
| -> Index Only Scan using status_pk on status st (cost=0.13..12.21 rows=4 width=4) (actual time=0.010..0.012 rows=4 loops=1)|
| Index Cond: (status = 'published'::text) |
| Heap Fetches: 4 |
| -> Index Scan using story_pkey on story s (cost=0.13..3.15 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=4) |
| Index Cond: (story_id = st.story_id) |
| -> Memoize (cost=0.17..4.98 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=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..4.97 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=3)|
| Index Cond: (user_id = s.user_id) |
| Heap Fetches: 3 |
| -> Index Scan using users_pkey on users u (cost=0.14..0.18 rows=1 width=956) (actual time=0.003..0.003 rows=1 loops=4) |
| Index Cond: (user_id = w.user_id) |
| -> Index Scan using idx_comment_story_id on comment c (cost=0.14..2.57 rows=2 width=8) (actual time=0.003..0.004 rows=2 loops=4) |
| Index Cond: (story_id = s.story_id) |
| -> Materialize (cost=0.14..13.33 rows=11 width=23) (actual time=0.010..0.038 rows=27 loops=1) |
| -> Index Scan using idx_chapter_story_id on chapter ch (cost=0.14..13.30 rows=11 width=23) (actual time=0.007..0.029 rows=11 loops=1) |
| -> Materialize (cost=0.14..12.45 rows=18 width=8) (actual time=0.008..0.021 rows=119 loops=1) |
| -> Index Scan using idx_likes_story_id on likes l (cost=0.14..12.41 rows=18 width=8) (actual time=0.007..0.012 rows=18 loops=1) |
|Planning Time: 2.783 ms |
|Execution Time: 0.636 ms
We create indexes
CREATE INDEX idx_story_user_id_created ON story(user_id, story_created_at); CREATE INDEX idx_status_published ON status(story_id) WHERE status = 'published'; ANALYZE story; ANALYZE status; ANALYZE chapter; ANALYZE likes; ANALYZE comment;
After indexes we get:
|QUERY PLAN |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort (cost=95.34..95.52 rows=70 width=1144) (actual time=0.464..0.468 rows=4 loops=1) |
| Sort Key: with_growth.quarter DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 25kB |
| -> WindowAgg (cost=91.62..93.20 rows=70 width=1144) (actual time=0.447..0.458 rows=4 loops=1) |
| -> Sort (cost=91.62..91.80 rows=70 width=1104) (actual time=0.436..0.440 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=84.75..89.48 rows=70 width=1104) (actual time=0.418..0.434 rows=4 loops=1) |
| -> WindowAgg (cost=84.75..88.78 rows=70 width=1132) (actual time=0.417..0.431 rows=4 loops=1) |
| -> Sort (cost=84.75..84.93 rows=70 width=1044) (actual time=0.409..0.412 rows=4 loops=1) |
| Sort Key: u.user_id, (date_trunc('quarter'::text, s.story_created_at)) |
| Sort Method: quicksort Memory: 25kB |
| -> GroupAggregate (cost=79.63..82.61 rows=70 width=1044) (actual time=0.348..0.406 rows=4 loops=1) |
| Group Key: (date_trunc('quarter'::text, s.story_created_at)), u.user_id |
| -> Sort (cost=79.63..79.81 rows=70 width=995) (actual time=0.290..0.299 rows=118 loops=1) |
| Sort Key: (date_trunc('quarter'::text, s.story_created_at)), u.user_id, s.story_id |
| Sort Method: quicksort Memory: 35kB |
| -> Merge Left Join (cost=0.98..77.49 rows=70 width=995) (actual time=0.100..0.233 rows=118 loops=1) |
| Merge Cond: (s.story_id = l.story_id) |
| -> Merge Left Join (cost=0.84..63.78 rows=20 width=991) (actual time=0.084..0.145 rows=26 loops=1) |
| Merge Cond: (s.story_id = ch.story_id) |
| -> Nested Loop Left Join (cost=0.70..50.15 rows=9 width=972) (actual time=0.070..0.108 rows=10 loops=1) |
| -> Nested Loop (cost=0.57..39.79 rows=4 width=968) (actual time=0.060..0.087 rows=4 loops=1) |
| Join Filter: (s.user_id = u.user_id) |
| -> Nested Loop (cost=0.43..39.04 rows=4 width=20) (actual time=0.052..0.071 rows=4 loops=1) |
| -> Nested Loop (cost=0.26..22.78 rows=4 width=16) (actual time=0.019..0.029 rows=4 loops=1) |
| -> Index Only Scan using idx_status_published on status st (cost=0.13..12.19 rows=4 width=4) (actual time=0.009..0.011 rows=4 loops=1)|
| Heap Fetches: 4 |
| -> Index Scan using story_pkey on story s (cost=0.13..3.15 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=4) |
| Index Cond: (story_id = st.story_id) |
| -> Memoize (cost=0.17..4.98 rows=1 width=4) (actual time=0.009..0.009 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..4.97 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=3) |
| Index Cond: (user_id = s.user_id) |
| Heap Fetches: 3 |
| -> Index Scan using users_pkey on users u (cost=0.14..0.18 rows=1 width=956) (actual time=0.003..0.003 rows=1 loops=4) |
| Index Cond: (user_id = w.user_id) |
| -> Index Scan using idx_comment_story_id on comment c (cost=0.14..2.57 rows=2 width=8) (actual time=0.003..0.004 rows=2 loops=4) |
| Index Cond: (story_id = s.story_id) |
| -> Materialize (cost=0.14..13.33 rows=11 width=23) (actual time=0.010..0.022 rows=27 loops=1) |
| -> Index Scan using idx_chapter_story_id on chapter ch (cost=0.14..13.30 rows=11 width=23) (actual time=0.007..0.012 rows=11 loops=1) |
| -> Materialize (cost=0.14..12.45 rows=18 width=8) (actual time=0.008..0.021 rows=119 loops=1) |
| -> Index Scan using idx_likes_story_id on likes l (cost=0.14..12.41 rows=18 width=8) (actual time=0.007..0.011 rows=18 loops=1) |
|Planning Time: 2.858 ms |
|Execution Time: 0.631 ms |
Average time: 0.628 ms The indexes added for this query provided no meaningful improvement, with average execution time remaining virtually unchanged from 0.649 ms to 0.628 ms (~3%), which is within normal measurement variance. The indexes are not kept.
Scenario 3 - Annual genre popularity and engagement trend
Without index analysis
EXPLAIN ANALYZE
WITH genre_annual AS (
SELECT
DATE_TRUNC('year', s.story_created_at) AS year,
g.genre_id,
g.genre_name,
COUNT(DISTINCT s.story_id) AS total_stories,
COUNT(DISTINCT w.user_id) AS total_writers,
COALESCE(SUM(ch.view_count), 0) AS total_views,
COALESCE(SUM(ch.word_count), 0) AS total_words,
COUNT(DISTINCT l.user_id) AS total_likes,
COUNT(DISTINCT c.comment_id) AS total_comments,
ROUND(AVG(ch.rating), 2) AS avg_rating
FROM genre g
JOIN has_genre hg ON g.genre_id = hg.genre_id
JOIN story s ON hg.story_id = s.story_id
JOIN writer w ON s.user_id = w.user_id
JOIN status st ON s.story_id = st.story_id AND st.status = 'published'
LEFT JOIN chapter ch ON s.story_id = ch.story_id
LEFT JOIN likes l ON s.story_id = l.story_id
LEFT JOIN comment c ON s.story_id = c.story_id
GROUP BY DATE_TRUNC('year', s.story_created_at), g.genre_id, g.genre_name
),
with_metrics AS (
SELECT *,
ROUND((total_likes + total_comments)::DECIMAL / NULLIF(total_views, 0) * 100, 2) AS engagement_rate,
ROUND(total_views::DECIMAL / NULLIF(total_stories, 0), 2) AS avg_views_per_story,
LAG(total_views) OVER (PARTITION BY genre_id ORDER BY year) AS prev_year_views,
LAG(total_stories) OVER (PARTITION BY genre_id ORDER BY year) AS prev_year_stories
FROM genre_annual
)
SELECT
TO_CHAR(year, 'YYYY') AS year,
genre_name,
total_stories, total_writers, total_views, avg_views_per_story,
total_likes, total_comments,
COALESCE(avg_rating, 0) AS avg_rating,
COALESCE(engagement_rate, 0) AS engagement_rate,
ROUND((total_views - prev_year_views)::DECIMAL / NULLIF(prev_year_views, 0) * 100, 2) AS yoy_views_growth_pct,
ROUND((total_stories - prev_year_stories)::DECIMAL / NULLIF(prev_year_stories, 0) * 100, 2) AS yoy_stories_growth_pct,
RANK() OVER (PARTITION BY year ORDER BY total_views DESC) AS popularity_rank,
RANK() OVER (PARTITION BY year ORDER BY engagement_rate DESC) AS engagement_rank
FROM with_metrics
ORDER BY year DESC, popularity_rank;
|QUERY PLAN |
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort (cost=190.58..191.10 rows=207 width=506) (actual time=0.963..0.969 rows=10 loops=1) |
| Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 26kB |
| -> WindowAgg (cost=158.05..182.62 rows=207 width=506) (actual time=0.919..0.942 rows=10 loops=1) |
| -> Incremental Sort (cost=158.05..171.23 rows=207 width=386) (actual time=0.910..0.916 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=158.02..162.16 rows=207 width=386) (actual time=0.888..0.903 rows=10 loops=1) |
| -> Sort (cost=158.02..158.54 rows=207 width=378) (actual time=0.885..0.890 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=137.12..150.06 rows=207 width=378) (actual time=0.849..0.879 rows=10 loops=1) |
| -> WindowAgg (cost=137.12..147.99 rows=207 width=390) (actual time=0.848..0.876 rows=10 loops=1) |
| -> Sort (cost=137.12..137.64 rows=207 width=302) (actual time=0.838..0.842 rows=10 loops=1) |
| Sort Key: genre_annual.genre_id, genre_annual.year |
| Sort Method: quicksort Memory: 25kB |
| -> Subquery Scan on genre_annual (cost=118.81..129.16 rows=207 width=302) (actual time=0.644..0.833 rows=10 loops=1) |
| -> GroupAggregate (cost=118.81..127.09 rows=207 width=310) (actual time=0.644..0.830 rows=10 loops=1) |
| Group Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id |
| -> Sort (cost=118.81..119.33 rows=207 width=257) (actual time=0.579..0.603 rows=354 loops=1) |
| Sort Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id, s.story_id |
| Sort Method: quicksort Memory: 48kB |
| -> Merge Left Join (cost=1.15..110.85 rows=207 width=257) (actual time=0.093..0.405 rows=354 loops=1) |
| Merge Cond: (s.story_id = l.story_id) |
| -> Merge Left Join (cost=1.01..94.70 rows=57 width=253) (actual time=0.079..0.200 rows=78 loops=1) |
| Merge Cond: (s.story_id = c.story_id) |
| -> Merge Left Join (cost=0.88..81.48 rows=26 width=249) (actual time=0.070..0.162 rows=30 loops=1) |
| Merge Cond: (s.story_id = ch.story_id) |
| -> Nested Loop (cost=0.74..67.76 rows=12 width=238) (actual time=0.055..0.125 rows=12 loops=1) |
| -> Nested Loop (cost=0.57..48.82 rows=12 width=238) (actual time=0.046..0.104 rows=12 loops=1) |
| -> Nested Loop (cost=0.43..43.70 rows=12 width=230) (actual time=0.038..0.081 rows=12 loops=1) |
| -> Nested Loop (cost=0.28..17.58 rows=12 width=12) (actual time=0.028..0.052 rows=12 loops=1) |
| -> Index Only Scan using has_genre_pk on has_genre hg (cost=0.14..12.36 rows=15 width=8) (actual time=0.012..0.019 rows=15 loops=1) |
| Heap Fetches: 15 |
| -> Memoize (cost=0.14..0.96 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=15) |
| Cache Key: hg.story_id |
| Cache Mode: logical |
| Hits: 10 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Only Scan using idx_status_published on status st (cost=0.13..0.95 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=5)|
| Index Cond: (story_id = hg.story_id) |
| Heap Fetches: 4 |
| -> Memoize (cost=0.16..3.11 rows=1 width=222) (actual time=0.002..0.002 rows=1 loops=12) |
| Cache Key: hg.genre_id |
| Cache Mode: logical |
| Hits: 5 Misses: 7 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Scan using genre_pkey on genre g (cost=0.15..3.10 rows=1 width=222) (actual time=0.002..0.002 rows=1 loops=7) |
| Index Cond: (genre_id = hg.genre_id) |
| -> Memoize (cost=0.14..0.96 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=12) |
| Cache Key: hg.story_id |
| Cache Mode: logical |
| Hits: 8 Misses: 4 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Scan using story_pkey on story s (cost=0.13..0.95 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=4) |
| Index Cond: (story_id = hg.story_id) |
| -> Memoize (cost=0.17..4.98 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=12) |
| Cache Key: s.user_id |
| Cache Mode: logical |
| Hits: 9 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.003..0.003 rows=1 loops=3) |
| Index Cond: (user_id = s.user_id) |
| Heap Fetches: 3 |
| -> Materialize (cost=0.14..13.33 rows=11 width=15) (actual time=0.011..0.022 rows=31 loops=1) |
| -> Index Scan using idx_chapter_story_id on chapter ch (cost=0.14..13.30 rows=11 width=15) (actual time=0.008..0.013 rows=11 loops=1) |
| -> Materialize (cost=0.14..12.33 rows=11 width=8) (actual time=0.008..0.017 rows=79 loops=1) |
| -> Index Scan using idx_comment_story_id on comment c (cost=0.14..12.30 rows=11 width=8) (actual time=0.006..0.009 rows=11 loops=1) |
| -> Materialize (cost=0.14..12.45 rows=18 width=8) (actual time=0.007..0.032 rows=355 loops=1) |
| -> Index Scan using idx_likes_story_id on likes l (cost=0.14..12.41 rows=18 width=8) (actual time=0.006..0.010 rows=18 loops=1) |
|Planning Time: 4.355 ms |
|Execution Time: 1.161 ms
Average time without indexes is: 1.179 ms
Indexes for this queries
CREATE INDEX idx_has_genre_genre_id ON has_genre(genre_id); CREATE INDEX idx_has_genre_story_id ON has_genre(story_id); CREATE INDEX idx_status_story_published ON status(story_id) WHERE status = 'published'; ANALYZE genre; ANALYZE has_genre; ANALYZE status; ANALYZE story; ANALYZE chapter; ANALYZE likes; ANALYZE comment;
After analysis we get:
|QUERY PLAN |
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort (cost=119.78..119.91 rows=50 width=298) (actual time=0.999..1.005 rows=10 loops=1) |
| Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 26kB |
| -> WindowAgg (cost=112.40..118.37 rows=50 width=298) (actual time=0.957..0.980 rows=10 loops=1) |
| -> Incremental Sort (cost=112.40..115.62 rows=50 width=178) (actual time=0.949..0.954 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=112.37..113.37 rows=50 width=178) (actual time=0.926..0.942 rows=10 loops=1) |
| -> Sort (cost=112.37..112.49 rows=50 width=170) (actual time=0.923..0.928 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=107.83..110.96 rows=50 width=170) (actual time=0.888..0.917 rows=10 loops=1) |
| -> WindowAgg (cost=107.83..110.46 rows=50 width=182) (actual time=0.887..0.914 rows=10 loops=1) |
| -> Sort (cost=107.83..107.96 rows=50 width=94) (actual time=0.876..0.881 rows=10 loops=1) |
| Sort Key: genre_annual.genre_id, genre_annual.year |
| Sort Method: quicksort Memory: 25kB |
| -> Subquery Scan on genre_annual (cost=100.39..106.42 rows=50 width=94) (actual time=0.684..0.871 rows=10 loops=1) |
| -> GroupAggregate (cost=100.39..105.92 rows=50 width=102) (actual time=0.683..0.869 rows=10 loops=1) |
| Group Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id |
| -> Sort (cost=100.39..100.91 rows=207 width=49) (actual time=0.618..0.641 rows=354 loops=1) |
| Sort Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id, s.story_id |
| Sort Method: quicksort Memory: 48kB |
| -> Merge Left Join (cost=1.14..92.43 rows=207 width=49) (actual time=0.101..0.453 rows=354 loops=1) |
| Merge Cond: (s.story_id = l.story_id) |
| -> Merge Left Join (cost=1.00..76.29 rows=57 width=45) (actual time=0.075..0.215 rows=78 loops=1) |
| Merge Cond: (s.story_id = c.story_id) |
| -> Merge Left Join (cost=0.86..63.07 rows=26 width=41) (actual time=0.067..0.177 rows=30 loops=1) |
| Merge Cond: (s.story_id = ch.story_id) |
| -> Nested Loop (cost=0.73..49.35 rows=12 width=30) (actual time=0.053..0.138 rows=12 loops=1) |
| -> Nested Loop (cost=0.56..30.40 rows=12 width=30) (actual time=0.044..0.117 rows=12 loops=1) |
| -> Nested Loop (cost=0.42..25.28 rows=12 width=22) (actual time=0.036..0.092 rows=12 loops=1) |
| -> Nested Loop (cost=0.28..20.06 rows=15 width=18) (actual time=0.026..0.061 rows=15 loops=1) |
| -> Index Only Scan using has_genre_pk on has_genre hg (cost=0.14..12.36 rows=15 width=8) (actual time=0.011..0.019 rows=15 loops=1) |
| Heap Fetches: 15 |
| -> Memoize (cost=0.15..0.96 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=15) |
| Cache Key: hg.genre_id |
| Cache Mode: logical |
| Hits: 7 Misses: 8 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Scan using genre_pkey on genre g (cost=0.14..0.95 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=8) |
| Index Cond: (genre_id = hg.genre_id) |
| -> Memoize (cost=0.14..0.96 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=15) |
| Cache Key: hg.story_id |
| Cache Mode: logical |
| Hits: 10 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Only Scan using idx_status_story_published on status st (cost=0.13..0.95 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=5)|
| Index Cond: (story_id = hg.story_id) |
| Heap Fetches: 4 |
| -> Memoize (cost=0.14..0.96 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=12) |
| Cache Key: hg.story_id |
| Cache Mode: logical |
| Hits: 8 Misses: 4 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Scan using story_pkey on story s (cost=0.13..0.95 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=4) |
| Index Cond: (story_id = hg.story_id) |
| -> Memoize (cost=0.17..4.98 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=12) |
| Cache Key: s.user_id |
| Cache Mode: logical |
| Hits: 9 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.003..0.003 rows=1 loops=3) |
| Index Cond: (user_id = s.user_id) |
| Heap Fetches: 3 |
| -> Materialize (cost=0.14..13.33 rows=11 width=15) (actual time=0.010..0.023 rows=31 loops=1) |
| -> Index Scan using idx_chapter_story_id on chapter ch (cost=0.14..13.30 rows=11 width=15) (actual time=0.008..0.013 rows=11 loops=1) |
| -> Materialize (cost=0.14..12.33 rows=11 width=8) (actual time=0.007..0.017 rows=79 loops=1) |
| -> Index Scan using idx_comment_story_id on comment c (cost=0.14..12.30 rows=11 width=8) (actual time=0.006..0.009 rows=11 loops=1) |
| -> Materialize (cost=0.14..12.45 rows=18 width=8) (actual time=0.007..0.033 rows=355 loops=1) |
| -> Index Scan using idx_likes_story_id on likes l (cost=0.14..12.41 rows=18 width=8) (actual time=0.006..0.011 rows=18 loops=1) |
|Planning Time: 4.782 ms |
|Execution Time: 1.200 ms
Average time: 1.181 ms
The indexes added for this query provided no meaningful improvement, with average execution time remaining virtually unchanged from 1.179 ms to 1.181 ms (~0%), which is within normal measurement variance. The indexes are not kept.
Last modified
8 days ago
Last modified on 06/08/26 01:14:27
Note:
See TracWiki
for help on using the wiki.
