| Version 19 (modified by , 5 days ago) ( diff ) |
|---|
Other topics (Performance, Security, …)
The testing methodology is as follows:
A large number of new records are inserted into the relevant tables so that index usage becomes worthwhile. It is well understood that for a table with only a handful of rows, an index offers little to no advantage. Before adding any index, the query is executed 10 times using EXPLAIN ANALYZE. The average Execution Time is recorded and the query plan is saved for later comparison. After adding the indexes, the same query is executed 10 times and the results are compared.
Scenario 1 - Quarterly story performance
Without index analysis
EXPLAIN ANALYZE
WITH quarterly_story_stats AS (
SELECT
DATE_TRUNC('quarter', s.story_created_at) AS quarter,
s.story_id,
s.short_description,
s.mature_content,
u.user_id,
u.username AS writer,
st.status,
COUNT(DISTINCT ch.chapter_id) AS total_chapters,
COALESCE(SUM(ch.view_count), 0) AS total_views,
COALESCE(SUM(ch.word_count), 0) AS total_words,
ROUND(AVG(ch.rating), 2) AS avg_rating,
COUNT(DISTINCT l.user_id) AS total_likes,
COUNT(DISTINCT c.comment_id) AS total_comments,
COUNT(DISTINCT col.user_id) AS total_collaborators,
COUNT(DISTINCT hg.genre_id) AS total_genres,
COUNT(DISTINCT rli.list_id) AS saved_in_lists
FROM story s
JOIN writer w ON s.user_id = w.user_id
JOIN users u ON w.user_id = u.user_id
JOIN status st ON s.story_id = st.story_id
LEFT JOIN chapter ch ON s.story_id = ch.story_id
LEFT JOIN likes l ON s.story_id = l.story_id
LEFT JOIN comment c ON s.story_id = c.story_id
LEFT JOIN collaboration col ON s.story_id = col.story_id
LEFT JOIN has_genre hg ON s.story_id = hg.story_id
LEFT JOIN reading_list_items rli ON s.story_id = rli.story_id
GROUP BY
DATE_TRUNC('quarter', s.story_created_at),
s.story_id, s.short_description, s.mature_content,
u.user_id, u.username, st.status
),
with_engagement AS (
SELECT *,
ROUND((total_likes + total_comments)::DECIMAL / NULLIF(total_views, 0) * 100, 2) AS engagement_rate,
ROUND(total_views::DECIMAL / NULLIF(total_chapters, 0), 2) AS avg_views_per_chapter,
LAG(total_views) OVER (PARTITION BY story_id ORDER BY quarter) AS prev_quarter_views,
LAG(total_likes) OVER (PARTITION BY story_id ORDER BY quarter) AS prev_quarter_likes,
LAG(total_comments) OVER (PARTITION BY story_id ORDER BY quarter) AS prev_quarter_comments
FROM quarterly_story_stats
),
with_growth AS (
SELECT *,
ROUND((total_views - prev_quarter_views)::DECIMAL / NULLIF(prev_quarter_views, 0) * 100, 2) AS views_growth_pct,
ROUND((total_likes - prev_quarter_likes)::DECIMAL / NULLIF(prev_quarter_likes, 0) * 100, 2) AS likes_growth_pct,
ROUND((total_comments - prev_quarter_comments)::DECIMAL / NULLIF(prev_quarter_comments, 0) * 100, 2) AS comments_growth_pct
FROM with_engagement
)
SELECT
TO_CHAR(quarter, 'YYYY "Q"Q') AS period,
writer,
story_id,
short_description,
status,
mature_content,
total_chapters,
total_words,
total_genres,
total_collaborators,
saved_in_lists,
total_views,
avg_views_per_chapter,
COALESCE(views_growth_pct, 0) AS views_growth_pct,
total_likes,
COALESCE(likes_growth_pct, 0) AS likes_growth_pct,
total_comments,
COALESCE(comments_growth_pct, 0) AS comments_growth_pct,
COALESCE(avg_rating, 0) AS avg_rating,
COALESCE(engagement_rate, 0) AS engagement_rate,
RANK() OVER (PARTITION BY quarter ORDER BY total_views DESC) AS rank_by_views,
RANK() OVER (PARTITION BY quarter ORDER BY engagement_rate DESC) AS rank_by_engagement,
RANK() OVER (PARTITION BY quarter ORDER BY avg_rating DESC) AS rank_by_rating
FROM with_growth
ORDER BY quarter DESC, rank_by_views;
|QUERY PLAN |
|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort (cost=3720.58..3723.08 rows=1000 width=486) (actual time=71.545..71.555 rows=50 loops=1) |
| Sort Key: with_engagement.quarter DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 38kB |
| -> WindowAgg (cost=3505.27..3670.75 rows=1000 width=486) (actual time=71.359..71.463 rows=50 loops=1) |
| -> Incremental Sort (cost=3505.16..3598.25 rows=1000 width=310) (actual time=71.348..71.383 rows=50 loops=1) |
| Sort Key: with_engagement.quarter, with_engagement.total_views DESC |
| Presorted Key: with_engagement.quarter |
| Full-sort Groups: 2 Sort Method: quicksort Average Memory: 32kB Peak Memory: 32kB |
| -> WindowAgg (cost=3504.78..3570.14 rows=1000 width=310) (actual time=71.263..71.339 rows=50 loops=1) |
| -> Incremental Sort (cost=3504.71..3552.64 rows=1000 width=302) (actual time=71.260..71.294 rows=50 loops=1) |
| Sort Key: with_engagement.quarter, with_engagement.engagement_rate DESC |
| Presorted Key: with_engagement.quarter |
| Full-sort Groups: 2 Sort Method: quicksort Average Memory: 32kB Peak Memory: 32kB |
| -> WindowAgg (cost=3504.55..3524.53 rows=1000 width=302) (actual time=71.199..71.254 rows=50 loops=1) |
| -> Sort (cost=3504.53..3507.03 rows=1000 width=294) (actual time=71.196..71.205 rows=50 loops=1) |
| Sort Key: with_engagement.quarter, with_engagement.avg_rating DESC |
| Sort Method: quicksort Memory: 35kB |
| -> Subquery Scan on with_engagement (cost=100.81..3454.70 rows=1000 width=294) (actual time=47.476..71.153 rows=50 loops=1) |
| -> WindowAgg (cost=100.81..3444.70 rows=1000 width=298) (actual time=47.475..71.142 rows=50 loops=1) |
| -> Incremental Sort (cost=97.49..3392.20 rows=1000 width=206) (actual time=47.464..71.031 rows=50 loops=1) |
| Sort Key: quarterly_story_stats.story_id, quarterly_story_stats.quarter |
| Presorted Key: quarterly_story_stats.story_id |
| Full-sort Groups: 2 Sort Method: quicksort Average Memory: 31kB Peak Memory: 31kB |
| -> Subquery Scan on quarterly_story_stats (cost=81.02..3364.10 rows=1000 width=206) (actual time=1.962..70.982 rows=50 loops=1) |
| -> GroupAggregate (cost=81.02..3354.10 rows=1000 width=210) (actual time=1.961..70.960 rows=50 loops=1) |
| Group Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id |
| -> Incremental Sort (cost=81.02..2405.34 rows=28654 width=153) (actual time=1.178..52.207 rows=31539 loops=1) |
| Sort Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id, ch.chapter_id |
| Presorted Key: s.story_id, st.status |
| Full-sort Groups: 49 Sort Method: quicksort Average Memory: 38kB Peak Memory: 38kB |
| Pre-sorted Groups: 50 Sort Method: quicksort Average Memory: 255kB Peak Memory: 272kB |
| -> Merge Left Join (cost=61.18..875.71 rows=28654 width=153) (actual time=0.524..23.446 rows=31539 loops=1) |
| Merge Cond: (s.story_id = hg.story_id) |
| -> Merge Left Join (cost=61.03..336.01 rows=9551 width=149) (actual time=0.498..5.824 rows=10513 loops=1) |
| Merge Cond: (s.story_id = rli.story_id) |
| -> Merge Left Join (cost=56.11..174.84 rows=5191 width=145) (actual time=0.457..2.666 rows=5520 loops=1) |
| Merge Cond: (s.story_id = l.story_id) |
| -> Merge Left Join (cost=39.47..78.01 rows=902 width=141) (actual time=0.365..1.020 rows=928 loops=1) |
| Merge Cond: (s.story_id = ch.story_id) |
| -> Merge Left Join (cost=20.80..45.34 rows=191 width=122) (actual time=0.229..0.541 rows=192 loops=1) |
| Merge Cond: (s.story_id = c.story_id) |
| -> Merge Left Join (cost=8.66..30.20 rows=50 width=118) (actual time=0.156..0.376 rows=51 loops=1) |
| Merge Cond: (s.story_id = col.story_id) |
| -> Nested Loop (cost=7.63..29.01 rows=50 width=114) (actual time=0.139..0.341 rows=50 loops=1) |
| -> Merge Join (cost=7.46..21.09 rows=50 width=118) (actual time=0.123..0.230 rows=50 loops=1) |
| Merge Cond: (st.story_id = s.story_id) |
| -> Index Only Scan using status_pk on status st (cost=0.14..12.89 rows=50 width=13) (actual time=0.012..0.064 rows=50 loops=1)|
| Heap Fetches: 50 |
| -> Sort (cost=7.32..7.45 rows=50 width=109) (actual time=0.108..0.117 rows=50 loops=1) |
| Sort Key: s.story_id |
| Sort Method: quicksort Memory: 31kB |
| -> Hash Join (cost=1.23..5.91 rows=50 width=109) (actual time=0.054..0.088 rows=50 loops=1) |
| Hash Cond: (s.user_id = u.user_id) |
| -> Seq Scan on story s (cost=0.00..4.50 rows=50 width=92) (actual time=0.025..0.035 rows=50 loops=1) |
| -> Hash (cost=1.10..1.10 rows=10 width=17) (actual time=0.016..0.017 rows=10 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on users u (cost=0.00..1.10 rows=10 width=17) (actual time=0.008..0.010 rows=10 loops=1) |
| -> Memoize (cost=0.17..1.14 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=50) |
| Cache Key: s.user_id |
| Cache Mode: logical |
| Hits: 45 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Only Scan using writer_pkey on writer w (cost=0.15..1.13 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=5) |
| Index Cond: (user_id = s.user_id) |
| Heap Fetches: 5 |
| -> Sort (cost=1.03..1.03 rows=2 width=8) (actual time=0.015..0.016 rows=2 loops=1) |
| Sort Key: col.story_id |
| Sort Method: quicksort Memory: 25kB |
| -> Seq Scan on collaboration col (cost=0.00..1.02 rows=2 width=8) (actual time=0.009..0.010 rows=2 loops=1) |
| -> Sort (cost=12.15..12.62 rows=191 width=8) (actual time=0.072..0.093 rows=192 loops=1) |
| Sort Key: c.story_id |
| Sort Method: quicksort Memory: 29kB |
| -> Seq Scan on comment c (cost=0.00..4.91 rows=191 width=8) (actual time=0.011..0.043 rows=191 loops=1) |
| -> Sort (cost=18.66..19.25 rows=236 width=23) (actual time=0.134..0.199 rows=925 loops=1) |
| Sort Key: ch.story_id |
| Sort Method: quicksort Memory: 34kB |
| -> Seq Scan on chapter ch (cost=0.00..9.36 rows=236 width=23) (actual time=0.009..0.076 rows=236 loops=1) |
| -> Sort (cost=16.64..17.36 rows=288 width=8) (actual time=0.091..0.440 rows=5501 loops=1) |
| Sort Key: l.story_id |
| Sort Method: quicksort Memory: 31kB |
| -> Seq Scan on likes l (cost=0.00..4.88 rows=288 width=8) (actual time=0.014..0.049 rows=288 loops=1) |
| -> Sort (cost=4.92..5.15 rows=92 width=8) (actual time=0.039..0.639 rows=9674 loops=1) |
| Sort Key: rli.story_id |
| Sort Method: quicksort Memory: 27kB |
| -> Seq Scan on reading_list_items rli (cost=0.00..1.92 rows=92 width=8) (actual time=0.008..0.019 rows=92 loops=1) |
| -> Materialize (cost=0.14..14.77 rows=150 width=8) (actual time=0.018..1.851 rows=31300 loops=1) |
| -> Index Only Scan using has_genre_pk on has_genre hg (cost=0.14..14.39 rows=150 width=8) (actual time=0.016..0.111 rows=150 loops=1) |
| Heap Fetches: 150 |
|Planning Time: 6.234 ms |
|Execution Time: 71.861 ms |
Average time without indexes is: 72.384 ms
Indexes for this queries
CREATE INDEX idx_chapter_covering
ON chapter(story_id, chapter_id, view_count, word_count, rating);
CREATE INDEX idx_likes_covering
ON likes(story_id, user_id);
CREATE INDEX idx_comment_covering
ON comment(story_id, comment_id);
CREATE INDEX idx_collaboration_covering
ON collaboration(story_id, user_id);
CREATE INDEX idx_has_genre_covering
ON has_genre(story_id, genre_id);
CREATE INDEX idx_rli_covering
ON reading_list_items(story_id, list_id);
CREATE INDEX idx_story_covering
ON story(story_id, user_id, story_created_at, mature_content);
CREATE INDEX idx_users_covering
ON users(user_id, username);
ANALYZE story;
ANALYZE chapter;
ANALYZE likes;
ANALYZE comment;
ANALYZE collaboration;
ANALYZE has_genre;
ANALYZE reading_list_items;
ANALYZE status;
ANALYZE users;
|QUERY PLAN |
|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort (cost=3720.58..3723.08 rows=1000 width=486) (actual time=74.567..74.583 rows=50 loops=1) |
| Sort Key: with_engagement.quarter DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 38kB |
| -> WindowAgg (cost=3505.27..3670.75 rows=1000 width=486) (actual time=74.380..74.490 rows=50 loops=1) |
| -> Incremental Sort (cost=3505.16..3598.25 rows=1000 width=310) (actual time=74.364..74.404 rows=50 loops=1) |
| Sort Key: with_engagement.quarter, with_engagement.total_views DESC |
| Presorted Key: with_engagement.quarter |
| Full-sort Groups: 2 Sort Method: quicksort Average Memory: 32kB Peak Memory: 32kB |
| -> WindowAgg (cost=3504.78..3570.14 rows=1000 width=310) (actual time=74.278..74.359 rows=50 loops=1) |
| -> Incremental Sort (cost=3504.71..3552.64 rows=1000 width=302) (actual time=74.274..74.311 rows=50 loops=1) |
| Sort Key: with_engagement.quarter, with_engagement.engagement_rate DESC |
| Presorted Key: with_engagement.quarter |
| Full-sort Groups: 2 Sort Method: quicksort Average Memory: 32kB Peak Memory: 32kB |
| -> WindowAgg (cost=3504.55..3524.53 rows=1000 width=302) (actual time=74.209..74.271 rows=50 loops=1) |
| -> Sort (cost=3504.53..3507.03 rows=1000 width=294) (actual time=74.202..74.216 rows=50 loops=1) |
| Sort Key: with_engagement.quarter, with_engagement.avg_rating DESC |
| Sort Method: quicksort Memory: 35kB |
| -> Subquery Scan on with_engagement (cost=100.81..3454.70 rows=1000 width=294) (actual time=49.439..74.159 rows=50 loops=1) |
| -> WindowAgg (cost=100.81..3444.70 rows=1000 width=298) (actual time=49.437..74.147 rows=50 loops=1) |
| -> Incremental Sort (cost=97.49..3392.20 rows=1000 width=206) (actual time=49.423..74.026 rows=50 loops=1) |
| Sort Key: quarterly_story_stats.story_id, quarterly_story_stats.quarter |
| Presorted Key: quarterly_story_stats.story_id |
| Full-sort Groups: 2 Sort Method: quicksort Average Memory: 31kB Peak Memory: 31kB |
| -> Subquery Scan on quarterly_story_stats (cost=81.02..3364.10 rows=1000 width=206) (actual time=2.134..73.965 rows=50 loops=1) |
| -> GroupAggregate (cost=81.02..3354.10 rows=1000 width=210) (actual time=2.133..73.936 rows=50 loops=1) |
| Group Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id |
| -> Incremental Sort (cost=81.02..2405.34 rows=28654 width=153) (actual time=1.210..53.754 rows=31539 loops=1) |
| Sort Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id, ch.chapter_id |
| Presorted Key: s.story_id, st.status |
| Full-sort Groups: 49 Sort Method: quicksort Average Memory: 38kB Peak Memory: 38kB |
| Pre-sorted Groups: 50 Sort Method: quicksort Average Memory: 255kB Peak Memory: 272kB |
| -> Merge Left Join (cost=61.18..875.71 rows=28654 width=153) (actual time=0.533..24.135 rows=31539 loops=1) |
| Merge Cond: (s.story_id = hg.story_id) |
| -> Merge Left Join (cost=61.03..336.01 rows=9551 width=149) (actual time=0.507..6.219 rows=10513 loops=1) |
| Merge Cond: (s.story_id = rli.story_id) |
| -> Merge Left Join (cost=56.11..174.84 rows=5191 width=145) (actual time=0.467..2.947 rows=5520 loops=1) |
| Merge Cond: (s.story_id = l.story_id) |
| -> Merge Left Join (cost=39.47..78.01 rows=902 width=141) (actual time=0.374..1.145 rows=928 loops=1) |
| Merge Cond: (s.story_id = ch.story_id) |
| -> Merge Left Join (cost=20.80..45.34 rows=191 width=122) (actual time=0.240..0.645 rows=192 loops=1) |
| Merge Cond: (s.story_id = c.story_id) |
| -> Merge Left Join (cost=8.66..30.20 rows=50 width=118) (actual time=0.167..0.471 rows=51 loops=1) |
| Merge Cond: (s.story_id = col.story_id) |
| -> Nested Loop (cost=7.63..29.01 rows=50 width=114) (actual time=0.150..0.432 rows=50 loops=1) |
| -> Merge Join (cost=7.46..21.09 rows=50 width=118) (actual time=0.133..0.291 rows=50 loops=1) |
| Merge Cond: (st.story_id = s.story_id) |
| -> Index Only Scan using status_pk on status st (cost=0.14..12.89 rows=50 width=13) (actual time=0.013..0.102 rows=50 loops=1)|
| Heap Fetches: 50 |
| -> Sort (cost=7.32..7.45 rows=50 width=109) (actual time=0.116..0.130 rows=50 loops=1) |
| Sort Key: s.story_id |
| Sort Method: quicksort Memory: 31kB |
| -> Hash Join (cost=1.23..5.91 rows=50 width=109) (actual time=0.045..0.098 rows=50 loops=1) |
| Hash Cond: (s.user_id = u.user_id) |
| -> Seq Scan on story s (cost=0.00..4.50 rows=50 width=92) (actual time=0.016..0.027 rows=50 loops=1) |
| -> Hash (cost=1.10..1.10 rows=10 width=17) (actual time=0.017..0.018 rows=10 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on users u (cost=0.00..1.10 rows=10 width=17) (actual time=0.008..0.010 rows=10 loops=1) |
| -> Memoize (cost=0.17..1.14 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=50) |
| Cache Key: s.user_id |
| Cache Mode: logical |
| Hits: 45 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Only Scan using writer_pkey on writer w (cost=0.15..1.13 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=5) |
| Index Cond: (user_id = s.user_id) |
| Heap Fetches: 5 |
| -> Sort (cost=1.03..1.03 rows=2 width=8) (actual time=0.015..0.016 rows=2 loops=1) |
| Sort Key: col.story_id |
| Sort Method: quicksort Memory: 25kB |
| -> Seq Scan on collaboration col (cost=0.00..1.02 rows=2 width=8) (actual time=0.009..0.010 rows=2 loops=1) |
| -> Sort (cost=12.15..12.62 rows=191 width=8) (actual time=0.071..0.094 rows=192 loops=1) |
| Sort Key: c.story_id |
| Sort Method: quicksort Memory: 29kB |
| -> Seq Scan on comment c (cost=0.00..4.91 rows=191 width=8) (actual time=0.011..0.043 rows=191 loops=1) |
| -> Sort (cost=18.66..19.25 rows=236 width=23) (actual time=0.133..0.217 rows=925 loops=1) |
| Sort Key: ch.story_id |
| Sort Method: quicksort Memory: 34kB |
| -> Seq Scan on chapter ch (cost=0.00..9.36 rows=236 width=23) (actual time=0.009..0.077 rows=236 loops=1) |
| -> Sort (cost=16.64..17.36 rows=288 width=8) (actual time=0.091..0.464 rows=5501 loops=1) |
| Sort Key: l.story_id |
| Sort Method: quicksort Memory: 31kB |
| -> Seq Scan on likes l (cost=0.00..4.88 rows=288 width=8) (actual time=0.013..0.049 rows=288 loops=1) |
| -> Sort (cost=4.92..5.15 rows=92 width=8) (actual time=0.039..0.657 rows=9674 loops=1) |
| Sort Key: rli.story_id |
| Sort Method: quicksort Memory: 27kB |
| -> Seq Scan on reading_list_items rli (cost=0.00..1.92 rows=92 width=8) (actual time=0.007..0.019 rows=92 loops=1) |
| -> Materialize (cost=0.14..14.77 rows=150 width=8) (actual time=0.018..1.898 rows=31300 loops=1) |
| -> Index Only Scan using idx_has_genre_covering on has_genre hg (cost=0.14..14.39 rows=150 width=8) (actual time=0.015..0.146 rows=150 loops=1) |
| Heap Fetches: 150 |
|Planning Time: 8.520 ms |
|Execution Time: 74.902 ms |
Average time: 72.240 ms
The covering indexes were created and verified across all joined tables. The average execution time is 72.384 ms without indexes and 72.240 ms with indexes. In the execution plan, idx_has_genre_covering, status_pk, and writer_pkey are used as Index Only Scans. The remaining tables use Seq Scan because Merge Left Joins require pre-sorted input, making sequential scans cheaper at this data volume. The indexes are kept as they will be automatically utilized by the planner as data volume grows.
Scenario 2 — Quarterly writer performance report
EXPLAIN ANALYZE
WITH quarterly_stats AS (
SELECT
DATE_TRUNC('quarter', s.story_created_at) AS quarter,
u.user_id,
u.username,
u.user_name,
u.surname,
COUNT(DISTINCT s.story_id) AS stories_published,
COUNT(DISTINCT ch.chapter_id) AS chapters_written,
COALESCE(SUM(ch.view_count), 0) AS total_views,
COALESCE(SUM(ch.word_count), 0) AS total_words,
COUNT(DISTINCT l.user_id) AS total_likes,
COUNT(DISTINCT c.comment_id) AS total_comments,
ROUND(AVG(ch.rating), 2) AS avg_rating
FROM story s
JOIN writer w ON s.user_id = w.user_id
JOIN users u ON w.user_id = u.user_id
JOIN status st ON s.story_id = st.story_id AND st.status = 'published'
LEFT JOIN chapter ch ON s.story_id = ch.story_id
LEFT JOIN likes l ON s.story_id = l.story_id
LEFT JOIN comment c ON s.story_id = c.story_id
GROUP BY
DATE_TRUNC('quarter', s.story_created_at),
u.user_id, u.username, u.user_name, u.surname
),
with_growth AS (
SELECT *,
LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter) AS prev_views,
LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter) AS prev_likes,
LAG(total_comments) OVER (PARTITION BY user_id ORDER BY quarter) AS prev_comments,
ROUND((total_views - LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter))::DECIMAL
/ NULLIF(LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter), 0) * 100, 2) AS views_growth_pct,
ROUND((total_likes - LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter))::DECIMAL
/ NULLIF(LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter), 0) * 100, 2) AS likes_growth_pct
FROM quarterly_stats
)
SELECT
TO_CHAR(quarter, 'YYYY "Q"Q') AS period,
username, user_name, surname,
stories_published, chapters_written, total_words,
total_views,
COALESCE(views_growth_pct, 0) AS views_growth_pct,
total_likes,
COALESCE(likes_growth_pct, 0) AS likes_growth_pct,
total_comments,
COALESCE(avg_rating, 0) AS avg_rating,
RANK() OVER (PARTITION BY quarter ORDER BY total_views DESC) AS rank_by_views
FROM with_growth
ORDER BY quarter DESC, rank_by_views;
Analysis without indexes:
|QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort (cost=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.
Note:
See TracWiki
for help on using the wiki.
