| Version 14 (modified by , 3 weeks ago) ( diff ) |
|---|
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 - 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=188601.52..189051.70 rows=180072 width=296) (actual time=735.924..735.941 rows=19 loops=1) |
| Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 27kB |
| -> WindowAgg (cost=123725.89..148260.34 rows=180072 width=296) (actual time=735.837..735.903 rows=19 loops=1) |
| -> Incremental Sort (cost=123725.80..138356.38 rows=180072 width=176) (actual time=735.816..735.833 rows=19 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: 27kB Peak Memory: 27kB |
| -> WindowAgg (cost=123663.61..127265.03 rows=180072 width=176) (actual time=735.768..735.813 rows=19 loops=1) |
| -> Sort (cost=123663.59..124113.77 rows=180072 width=168) (actual time=735.756..735.773 rows=19 loops=1) |
| Sort Key: with_metrics.year, with_metrics.engagement_rate DESC |
| Sort Method: quicksort Memory: 27kB |
| -> Subquery Scan on with_metrics (cost=81917.02..93171.41 rows=180072 width=168) (actual time=735.680..735.750 rows=19 loops=1) |
| -> WindowAgg (cost=81917.02..91370.69 rows=180072 width=180) (actual time=735.674..735.739 rows=19 loops=1) |
| -> Sort (cost=81916.91..82367.09 rows=180072 width=92) (actual time=735.611..735.627 rows=19 loops=1) |
| Sort Key: genre_annual.genre_id, genre_annual.year |
| Sort Method: quicksort Memory: 26kB |
| -> Subquery Scan on genre_annual (cost=45259.58..56965.23 rows=180072 width=92) (actual time=474.888..735.603 rows=19 loops=1) |
| -> GroupAggregate (cost=45259.58..55164.51 rows=180072 width=100) (actual time=474.881..735.582 rows=19 loops=1) |
| Group Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id |
| -> Sort (cost=45259.58..46009.99 rows=300163 width=46) (actual time=474.740..592.149 rows=330994 loops=1) |
| Sort Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id, s.story_id |
| Sort Method: external merge Disk: 17832kB |
| -> Hash Right Join (cost=2402.96..8715.17 rows=300163 width=46) (actual time=104.929..272.256 rows=330994 loops=1) |
| Hash Cond: (c.story_id = s.story_id) |
| -> Seq Scan on comment c (cost=0.00..1935.11 rows=100011 width=8) (actual time=0.008..8.474 rows=100011 loops=1) |
| -> Hash (cost=2027.61..2027.61 rows=30028 width=42) (actual time=104.842..104.854 rows=30561 loops=1) |
| Buckets: 32768 Batches: 1 Memory Usage: 2253kB |
| -> Hash Join (cost=1084.06..2027.61 rows=30028 width=42) (actual time=76.783..95.747 rows=30561 loops=1) |
| Hash Cond: (hg.genre_id = g.genre_id) |
| -> Hash Join (cost=1082.66..1928.82 rows=30028 width=34) (actual time=16.868..29.358 rows=30561 loops=1) |
| Hash Cond: (hg.story_id = s.story_id) |
| -> Seq Scan on has_genre hg (cost=0.00..433.28 rows=30028 width=8) (actual time=0.012..2.278 rows=30028 loops=1) |
| -> Hash (cost=957.59..957.59 rows=10005 width=34) (actual time=16.819..16.828 rows=10145 loops=1) |
| Buckets: 16384 Batches: 1 Memory Usage: 645kB |
| -> Hash Left Join (cost=812.87..957.59 rows=10005 width=34) (actual time=7.357..14.471 rows=10145 loops=1) |
| Hash Cond: (s.story_id = l.story_id) |
| -> Hash Join (cost=811.44..868.43 rows=10005 width=30) (actual time=7.310..12.546 rows=10111 loops=1) |
| Hash Cond: (s.story_id = st.story_id) |
| -> Hash Join (cost=416.49..447.21 rows=10005 width=26) (actual time=3.835..7.095 rows=10111 loops=1) |
| Hash Cond: (s.user_id = w.user_id) |
| -> Hash Right Join (cost=349.11..353.53 rows=10005 width=26) (actual time=3.787..5.295 rows=10111 loops=1) |
| Hash Cond: (ch.story_id = s.story_id) |
| -> Seq Scan on chapter ch (cost=0.00..4.12 rows=112 width=14) (actual time=0.011..0.028 rows=112 loops=1) |
| -> Hash (cost=224.05..224.05 rows=10005 width=16) (actual time=3.728..3.729 rows=10005 loops=1) |
| Buckets: 16384 Batches: 1 Memory Usage: 597kB |
| -> Seq Scan on story s (cost=0.00..224.05 rows=10005 width=16) (actual time=0.018..1.867 rows=10005 loops=1)|
| -> Hash (cost=35.50..35.50 rows=2550 width=4) (actual time=0.027..0.028 rows=5 loops=1) |
| Buckets: 4096 Batches: 1 Memory Usage: 33kB |
| -> Seq Scan on writer w (cost=0.00..35.50 rows=2550 width=4) (actual time=0.011..0.013 rows=5 loops=1) |
| -> Hash (cost=269.89..269.89 rows=10005 width=4) (actual time=3.443..3.443 rows=10005 loops=1) |
| Buckets: 16384 Batches: 1 Memory Usage: 480kB |
| -> Seq Scan on status st (cost=0.00..269.89 rows=10005 width=4) (actual time=0.031..2.005 rows=10005 loops=1) |
| Filter: ((status)::text = 'published'::text) |
| Rows Removed by Filter: 5026 |
| -> Hash (cost=1.19..1.19 rows=19 width=8) (actual time=0.025..0.026 rows=19 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on likes l (cost=0.00..1.19 rows=19 width=8) (actual time=0.013..0.016 rows=19 loops=1) |
| -> Hash (cost=1.18..1.18 rows=18 width=12) (actual time=59.895..59.896 rows=18 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on genre g (cost=0.00..1.18 rows=18 width=12) (actual time=0.017..0.020 rows=18 loops=1) |
|Planning Time: 8.265 ms |
|JIT: |
| Functions: 84 |
| Options: Inlining false, Optimization false, Expressions true, Deforming true |
| Timing: Generation 5.934 ms (Deform 3.054 ms), Inlining 0.000 ms, Optimization 2.243 ms, Emission 57.903 ms, Total 66.080 ms |
|Execution Time: 747.006 ms |
Average time without indexes is: 749 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';
After analisys we get:
ANALYZE genre; ANALYZE has_genre; ANALYZE status; ANALYZE story; ANALYZE chapter; ANALYZE likes; ANALYZE comment;
|QUERY PLAN |
|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort (cost=188601.52..189051.70 rows=180072 width=296) (actual time=731.664..731.683 rows=19 loops=1) |
| Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 27kB |
| -> WindowAgg (cost=123725.89..148260.34 rows=180072 width=296) (actual time=731.578..731.645 rows=19 loops=1) |
| -> Incremental Sort (cost=123725.80..138356.38 rows=180072 width=176) (actual time=731.558..731.576 rows=19 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: 27kB Peak Memory: 27kB |
| -> WindowAgg (cost=123663.61..127265.03 rows=180072 width=176) (actual time=731.509..731.556 rows=19 loops=1) |
| -> Sort (cost=123663.59..124113.77 rows=180072 width=168) (actual time=731.498..731.515 rows=19 loops=1) |
| Sort Key: with_metrics.year, with_metrics.engagement_rate DESC |
| Sort Method: quicksort Memory: 27kB |
| -> Subquery Scan on with_metrics (cost=81917.02..93171.41 rows=180072 width=168) (actual time=731.423..731.493 rows=19 loops=1) |
| -> WindowAgg (cost=81917.02..91370.69 rows=180072 width=180) (actual time=731.416..731.482 rows=19 loops=1) |
| -> Sort (cost=81916.91..82367.09 rows=180072 width=92) (actual time=731.353..731.369 rows=19 loops=1) |
| Sort Key: genre_annual.genre_id, genre_annual.year |
| Sort Method: quicksort Memory: 26kB |
| -> Subquery Scan on genre_annual (cost=45259.58..56965.23 rows=180072 width=92) (actual time=470.799..731.345 rows=19 loops=1) |
| -> GroupAggregate (cost=45259.58..55164.51 rows=180072 width=100) (actual time=470.793..731.331 rows=19 loops=1) |
| Group Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id |
| -> Sort (cost=45259.58..46009.99 rows=300163 width=46) (actual time=470.650..587.960 rows=330994 loops=1) |
| Sort Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id, s.story_id |
| Sort Method: external merge Disk: 17832kB |
| -> Hash Right Join (cost=2402.96..8715.17 rows=300163 width=46) (actual time=102.927..268.242 rows=330994 loops=1) |
| Hash Cond: (c.story_id = s.story_id) |
| -> Seq Scan on comment c (cost=0.00..1935.11 rows=100011 width=8) (actual time=0.008..8.499 rows=100011 loops=1) |
| -> Hash (cost=2027.61..2027.61 rows=30028 width=42) (actual time=102.841..102.853 rows=30561 loops=1) |
| Buckets: 32768 Batches: 1 Memory Usage: 2253kB |
| -> Hash Join (cost=1084.06..2027.61 rows=30028 width=42) (actual time=74.863..93.838 rows=30561 loops=1) |
| Hash Cond: (hg.genre_id = g.genre_id) |
| -> Hash Join (cost=1082.66..1928.82 rows=30028 width=34) (actual time=16.771..29.144 rows=30561 loops=1) |
| Hash Cond: (hg.story_id = s.story_id) |
| -> Seq Scan on has_genre hg (cost=0.00..433.28 rows=30028 width=8) (actual time=0.011..2.336 rows=30028 loops=1) |
| -> Hash (cost=957.59..957.59 rows=10005 width=34) (actual time=16.724..16.733 rows=10145 loops=1) |
| Buckets: 16384 Batches: 1 Memory Usage: 645kB |
| -> Hash Left Join (cost=812.87..957.59 rows=10005 width=34) (actual time=7.297..14.431 rows=10145 loops=1) |
| Hash Cond: (s.story_id = l.story_id) |
| -> Hash Join (cost=811.44..868.43 rows=10005 width=30) (actual time=7.250..12.492 rows=10111 loops=1) |
| Hash Cond: (s.story_id = st.story_id) |
| -> Hash Join (cost=416.49..447.21 rows=10005 width=26) (actual time=3.788..7.039 rows=10111 loops=1) |
| Hash Cond: (s.user_id = w.user_id) |
| -> Hash Right Join (cost=349.11..353.53 rows=10005 width=26) (actual time=3.743..5.203 rows=10111 loops=1) |
| Hash Cond: (ch.story_id = s.story_id) |
| -> Seq Scan on chapter ch (cost=0.00..4.12 rows=112 width=14) (actual time=0.011..0.028 rows=112 loops=1) |
| -> Hash (cost=224.05..224.05 rows=10005 width=16) (actual time=3.685..3.686 rows=10005 loops=1) |
| Buckets: 16384 Batches: 1 Memory Usage: 597kB |
| -> Seq Scan on story s (cost=0.00..224.05 rows=10005 width=16) (actual time=0.017..1.835 rows=10005 loops=1)|
| -> Hash (cost=35.50..35.50 rows=2550 width=4) (actual time=0.024..0.026 rows=5 loops=1) |
| Buckets: 4096 Batches: 1 Memory Usage: 33kB |
| -> Seq Scan on writer w (cost=0.00..35.50 rows=2550 width=4) (actual time=0.010..0.012 rows=5 loops=1) |
| -> Hash (cost=269.89..269.89 rows=10005 width=4) (actual time=3.426..3.427 rows=10005 loops=1) |
| Buckets: 16384 Batches: 1 Memory Usage: 480kB |
| -> Seq Scan on status st (cost=0.00..269.89 rows=10005 width=4) (actual time=0.029..1.999 rows=10005 loops=1) |
| Filter: ((status)::text = 'published'::text) |
| Rows Removed by Filter: 5026 |
| -> Hash (cost=1.19..1.19 rows=19 width=8) (actual time=0.025..0.026 rows=19 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on likes l (cost=0.00..1.19 rows=19 width=8) (actual time=0.012..0.016 rows=19 loops=1) |
| -> Hash (cost=1.18..1.18 rows=18 width=12) (actual time=58.071..58.072 rows=18 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on genre g (cost=0.00..1.18 rows=18 width=12) (actual time=0.015..0.018 rows=18 loops=1) |
|Planning Time: 8.416 ms |
|JIT: |
| Functions: 84 |
| Options: Inlining false, Optimization false, Expressions true, Deforming true |
| Timing: Generation 5.938 ms (Deform 3.089 ms), Inlining 0.000 ms, Optimization 2.169 ms, Emission 56.154 ms, Total 64.260 ms |
|Execution Time: 742.778 ms |
Average time: 746.354 ms
We conclude that the indexes added for this query provided no meaningful improvement, dropping from 748 ms to 746 ms (~0.We conclude that the indexes added for this query provided no meaningful improvement, dropping from 748 ms to 746 ms (~0.3%), which is within normal measurement variance. The primary bottleneck is the sort operation spilling to disk (external merge, 17.8 MB), which cannot be resolved through indexing. The indexes are therefore not kept, as they add write overhead without any measurable read benefit.
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=295447.54..295697.57 rows=100011 width=1144) (actual time=380.965..380.977 rows=14 loops=1) |
| Sort Key: with_growth.quarter DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 26kB |
| -> WindowAgg (cost=185071.50..187321.72 rows=100011 width=1144) (actual time=380.907..380.958 rows=14 loops=1) |
| -> Sort (cost=185071.48..185321.50 rows=100011 width=1104) (actual time=380.856..380.868 rows=14 loops=1) |
| Sort Key: with_growth.quarter, with_growth.total_views DESC |
| Sort Method: quicksort Memory: 26kB |
| -> Subquery Scan on with_growth (cost=28996.63..80361.66 rows=100011 width=1104) (actual time=160.911..380.850 rows=14 loops=1) |
| -> WindowAgg (cost=28996.63..79361.55 rows=100011 width=1132) (actual time=160.905..380.839 rows=14 loops=1) |
| -> GroupAggregate (cost=28995.22..73860.95 rows=100011 width=1044) (actual time=160.788..380.735 rows=14 loops=1) |
| Group Key: u.user_id, (date_trunc('quarter'::text, s.story_created_at)) |
| -> Incremental Sort (cost=28995.22..69860.51 rows=100011 width=994) (actual time=160.712..342.505 rows=110218 loops=1) |
| Sort Key: u.user_id, (date_trunc('quarter'::text, s.story_created_at)), s.story_id |
| Presorted Key: u.user_id |
| Full-sort Groups: 3 Sort Method: quicksort Average Memory: 30kB Peak Memory: 30kB |
| Pre-sorted Groups: 2 Sort Methods: quicksort, external merge Average Memory: 13kB Peak Memory: 26kB Average Disk: 3760kB Peak Disk: 7520kB |
| -> Merge Join (cost=15790.20..17540.59 rows=100011 width=994) (actual time=160.635..229.966 rows=110218 loops=1) |
| Merge Cond: (s.user_id = u.user_id) |
| -> Sort (cost=15778.73..16028.76 rows=100011 width=46) (actual time=160.547..175.009 rows=110218 loops=1) |
| Sort Key: s.user_id |
| Sort Method: external merge Disk: 4504kB |
| -> Hash Right Join (cost=1082.66..4392.92 rows=100011 width=46) (actual time=74.630..114.895 rows=110218 loops=1) |
| Hash Cond: (c.story_id = s.story_id) |
| -> Seq Scan on comment c (cost=0.00..1935.11 rows=100011 width=8) (actual time=0.011..8.173 rows=100011 loops=1) |
| -> Hash (cost=957.59..957.59 rows=10005 width=42) (actual time=74.569..74.577 rows=10145 loops=1) |
| Buckets: 16384 Batches: 1 Memory Usage: 726kB |
| -> Hash Left Join (cost=812.87..957.59 rows=10005 width=42) (actual time=64.682..71.998 rows=10145 loops=1) |
| Hash Cond: (s.story_id = l.story_id) |
| -> Hash Join (cost=811.44..868.43 rows=10005 width=38) (actual time=64.638..70.028 rows=10111 loops=1) |
| Hash Cond: (s.story_id = st.story_id) |
| -> Hash Join (cost=416.49..447.21 rows=10005 width=38) (actual time=3.787..7.183 rows=10111 loops=1) |
| Hash Cond: (s.user_id = w.user_id) |
| -> Hash Right Join (cost=349.11..353.53 rows=10005 width=34) (actual time=3.736..5.286 rows=10111 loops=1) |
| Hash Cond: (ch.story_id = s.story_id) |
| -> Seq Scan on chapter ch (cost=0.00..4.12 rows=112 width=22) (actual time=0.012..0.030 rows=112 loops=1) |
| -> Hash (cost=224.05..224.05 rows=10005 width=16) (actual time=3.677..3.678 rows=10005 loops=1) |
| Buckets: 16384 Batches: 1 Memory Usage: 597kB |
| -> Seq Scan on story s (cost=0.00..224.05 rows=10005 width=16) (actual time=0.017..1.823 rows=10005 loops=1)|
| -> Hash (cost=35.50..35.50 rows=2550 width=4) (actual time=0.025..0.026 rows=5 loops=1) |
| Buckets: 4096 Batches: 1 Memory Usage: 33kB |
| -> Seq Scan on writer w (cost=0.00..35.50 rows=2550 width=4) (actual time=0.010..0.012 rows=5 loops=1) |
| -> Hash (cost=269.89..269.89 rows=10005 width=4) (actual time=60.813..60.813 rows=10005 loops=1) |
| Buckets: 16384 Batches: 1 Memory Usage: 480kB |
| -> Seq Scan on status st (cost=0.00..269.89 rows=10005 width=4) (actual time=57.194..59.348 rows=10005 loops=1) |
| Filter: ((status)::text = 'published'::text) |
| Rows Removed by Filter: 5026 |
| -> Hash (cost=1.19..1.19 rows=19 width=8) (actual time=0.024..0.025 rows=19 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on likes l (cost=0.00..1.19 rows=19 width=8) (actual time=0.012..0.015 rows=19 loops=1) |
| -> Sort (cost=11.46..11.56 rows=40 width=956) (actual time=0.043..0.044 rows=9 loops=1) |
| Sort Key: u.user_id |
| Sort Method: quicksort Memory: 25kB |
| -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=956) (actual time=0.022..0.025 rows=10 loops=1) |
|Planning Time: 4.139 ms |
|JIT: |
| Functions: 75 |
| Options: Inlining false, Optimization false, Expressions true, Deforming true |
| Timing: Generation 5.977 ms (Deform 3.329 ms), Inlining 0.000 ms, Optimization 2.386 ms, Emission 55.005 ms, Total 63.368 ms |
|Execution Time: 390.828 ms |
We create indexes
CREATE INDEX idx_story_quarter
ON story(story_created_at, user_id, story_id);
ANALYZE story;
ANALYZE status;
ANALYZE chapter;
ANALYZE likes;
ANALYZE comment;
After indexes we get:
|QUERY PLAN |
|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort (cost=295447.54..295697.57 rows=100011 width=1144) (actual time=385.005..385.018 rows=14 loops=1) |
| Sort Key: with_growth.quarter DESC, (rank() OVER (?)) |
| Sort Method: quicksort Memory: 26kB |
| -> WindowAgg (cost=185071.50..187321.72 rows=100011 width=1144) (actual time=384.949..384.998 rows=14 loops=1) |
| -> Sort (cost=185071.48..185321.50 rows=100011 width=1104) (actual time=384.909..384.921 rows=14 loops=1) |
| Sort Key: with_growth.quarter, with_growth.total_views DESC |
| Sort Method: quicksort Memory: 26kB |
| -> Subquery Scan on with_growth (cost=28996.63..80361.66 rows=100011 width=1104) (actual time=164.271..384.904 rows=14 loops=1) |
| -> WindowAgg (cost=28996.63..79361.55 rows=100011 width=1132) (actual time=164.265..384.892 rows=14 loops=1) |
| -> GroupAggregate (cost=28995.22..73860.95 rows=100011 width=1044) (actual time=164.169..384.779 rows=14 loops=1) |
| Group Key: u.user_id, (date_trunc('quarter'::text, s.story_created_at)) |
| -> Incremental Sort (cost=28995.22..69860.51 rows=100011 width=994) (actual time=164.095..346.459 rows=110218 loops=1) |
| Sort Key: u.user_id, (date_trunc('quarter'::text, s.story_created_at)), s.story_id |
| Presorted Key: u.user_id |
| Full-sort Groups: 3 Sort Method: quicksort Average Memory: 30kB Peak Memory: 30kB |
| Pre-sorted Groups: 2 Sort Methods: quicksort, external merge Average Memory: 13kB Peak Memory: 26kB Average Disk: 3760kB Peak Disk: 7520kB |
| -> Merge Join (cost=15790.20..17540.59 rows=100011 width=994) (actual time=164.019..233.324 rows=110218 loops=1) |
| Merge Cond: (s.user_id = u.user_id) |
| -> Sort (cost=15778.73..16028.76 rows=100011 width=46) (actual time=163.944..178.489 rows=110218 loops=1) |
| Sort Key: s.user_id |
| Sort Method: external merge Disk: 4504kB |
| -> Hash Right Join (cost=1082.66..4392.92 rows=100011 width=46) (actual time=76.636..117.141 rows=110218 loops=1) |
| Hash Cond: (c.story_id = s.story_id) |
| -> Seq Scan on comment c (cost=0.00..1935.11 rows=100011 width=8) (actual time=0.010..8.251 rows=100011 loops=1) |
| -> Hash (cost=957.59..957.59 rows=10005 width=42) (actual time=76.575..76.583 rows=10145 loops=1) |
| Buckets: 16384 Batches: 1 Memory Usage: 726kB |
| -> Hash Left Join (cost=812.87..957.59 rows=10005 width=42) (actual time=66.657..73.978 rows=10145 loops=1) |
| Hash Cond: (s.story_id = l.story_id) |
| -> Hash Join (cost=811.44..868.43 rows=10005 width=38) (actual time=66.611..71.975 rows=10111 loops=1) |
| Hash Cond: (s.story_id = st.story_id) |
| -> Hash Join (cost=416.49..447.21 rows=10005 width=38) (actual time=3.840..7.192 rows=10111 loops=1) |
| Hash Cond: (s.user_id = w.user_id) |
| -> Hash Right Join (cost=349.11..353.53 rows=10005 width=34) (actual time=3.785..5.305 rows=10111 loops=1) |
| Hash Cond: (ch.story_id = s.story_id) |
| -> Seq Scan on chapter ch (cost=0.00..4.12 rows=112 width=22) (actual time=0.011..0.028 rows=112 loops=1) |
| -> Hash (cost=224.05..224.05 rows=10005 width=16) (actual time=3.725..3.726 rows=10005 loops=1) |
| Buckets: 16384 Batches: 1 Memory Usage: 597kB |
| -> Seq Scan on story s (cost=0.00..224.05 rows=10005 width=16) (actual time=0.018..1.860 rows=10005 loops=1)|
| -> Hash (cost=35.50..35.50 rows=2550 width=4) (actual time=0.029..0.030 rows=5 loops=1) |
| Buckets: 4096 Batches: 1 Memory Usage: 33kB |
| -> Seq Scan on writer w (cost=0.00..35.50 rows=2550 width=4) (actual time=0.012..0.013 rows=5 loops=1) |
| -> Hash (cost=269.89..269.89 rows=10005 width=4) (actual time=62.733..62.734 rows=10005 loops=1) |
| Buckets: 16384 Batches: 1 Memory Usage: 480kB |
| -> Seq Scan on status st (cost=0.00..269.89 rows=10005 width=4) (actual time=59.051..61.260 rows=10005 loops=1) |
| Filter: ((status)::text = 'published'::text) |
| Rows Removed by Filter: 5026 |
| -> Hash (cost=1.19..1.19 rows=19 width=8) (actual time=0.025..0.026 rows=19 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Seq Scan on likes l (cost=0.00..1.19 rows=19 width=8) (actual time=0.012..0.016 rows=19 loops=1) |
| -> Sort (cost=11.46..11.56 rows=40 width=956) (actual time=0.032..0.033 rows=9 loops=1) |
| Sort Key: u.user_id |
| Sort Method: quicksort Memory: 25kB |
| -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=956) (actual time=0.021..0.023 rows=10 loops=1) |
|Planning Time: 4.181 ms |
|JIT: |
| Functions: 75 |
| Options: Inlining false, Optimization false, Expressions true, Deforming true |
| Timing: Generation 5.996 ms (Deform 3.374 ms), Inlining 0.000 ms, Optimization 2.381 ms, Emission 56.875 ms, Total 65.252 ms |
|Execution Time: 394.812 ms |
Average time: 396.791 ms We conclude that the indexes added for this query provided no meaningful improvement, with execution time remaining virtually unchanged from 393 ms to 397 ms (~+1%), which is within normal measurement variance. The query plan reveals two core issues: sort operations spilling to disk (up to 7.5 MB), and several tables (chapter, likes, writer) being too small for index scans to be beneficial. The indexes are therefore not kept, as they introduce write overhead without any measurable read benefit. }}} }}}
Note:
See TracWiki
for help on using the wiki.
