| 91 | | |QUERY PLAN | |
| 92 | | |-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |
| 93 | | |Sort (cost=7281598552.60..7281599952.60 rows=560000 width=1539) (actual time=1362.133..1362.145 rows=5 loops=1) | |
| 94 | | | Sort Key: with_engagement.quarter DESC, (rank() OVER (?)) | |
| 95 | | | Sort Method: quicksort Memory: 26kB | |
| 96 | | | -> WindowAgg (cost=7280655711.84..7280794770.41 rows=560000 width=1539) (actual time=1362.094..1362.129 rows=5 loops=1) | |
| 97 | | | -> Incremental Sort (cost=7280655711.65..7280754170.41 rows=560000 width=1363) (actual time=1362.074..1362.085 rows=5 loops=1) | |
| 98 | | | Sort Key: with_engagement.quarter, with_engagement.total_views DESC | |
| 99 | | | Presorted Key: with_engagement.quarter | |
| 100 | | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | |
| 101 | | | -> WindowAgg (cost=7280655252.07..7280715103.02 rows=560000 width=1363) (actual time=1362.044..1362.075 rows=5 loops=1) | |
| 102 | | | -> Incremental Sort (cost=7280655251.97..7280705303.02 rows=560000 width=1355) (actual time=1362.034..1362.045 rows=5 loops=1) | |
| 103 | | | Sort Key: with_engagement.quarter, with_engagement.engagement_rate DESC | |
| 104 | | | Presorted Key: with_engagement.quarter | |
| 105 | | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | |
| 106 | | | -> WindowAgg (cost=7280655035.65..7280666235.63 rows=560000 width=1355) (actual time=1362.001..1362.033 rows=5 loops=1) | |
| 107 | | | -> Sort (cost=7280655035.63..7280656435.63 rows=560000 width=1347) (actual time=1361.987..1361.998 rows=5 loops=1) | |
| 108 | | | Sort Key: with_engagement.quarter, with_engagement.avg_rating DESC | |
| 109 | | | Sort Method: quicksort Memory: 26kB | |
| 110 | | | -> Subquery Scan on with_engagement (cost=37078922.01..7279943128.44 rows=560000 width=1347) (actual time=1361.945..1361.985 rows=5 loops=1) | |
| 111 | | | -> WindowAgg (cost=37078922.01..7279937528.44 rows=560000 width=1351) (actual time=1361.938..1361.976 rows=5 loops=1) | |
| 112 | | | -> Incremental Sort (cost=36897846.44..7279908128.44 rows=560000 width=1259) (actual time=1361.905..1361.914 rows=5 loops=1) | |
| 113 | | | Sort Key: quarterly_story_stats.story_id, quarterly_story_stats.quarter | |
| 114 | | | Presorted Key: quarterly_story_stats.story_id | |
| 115 | | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | |
| 116 | | | -> 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) | |
| 117 | | | -> GroupAggregate (cost=500845.04..7279863461.05 rows=560000 width=1263) (actual time=1361.063..1361.894 rows=5 loops=1) | |
| 118 | | | Group Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id | |
| 119 | | | -> Incremental Sort (cost=500845.04..7083585393.76 rows=6039023609 width=1211) (actual time=1360.317..1361.324 rows=939 loops=1) | |
| 120 | | | Sort Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id, ch.chapter_id | |
| 121 | | | Presorted Key: s.story_id, st.status | |
| 122 | | | Full-sort Groups: 5 Sort Method: quicksort Average Memory: 38kB Peak Memory: 38kB | |
| 123 | | | Pre-sorted Groups: 4 Sort Method: quicksort Average Memory: 107kB Peak Memory: 107kB | |
| 124 | | | -> Nested Loop Left Join (cost=266.68..44077127.06 rows=6039023609 width=1211) (actual time=1359.651..1360.436 rows=939 loops=1) | |
| 125 | | | -> Nested Loop Left Join (cost=266.52..922084.11 rows=187049404 width=1207) (actual time=1359.606..1359.872 rows=313 loops=1) | |
| 126 | | | -> Nested Loop Left Join (cost=266.35..36339.98 rows=7077545 width=1203) (actual time=1359.567..1359.708 rows=120 loops=1) | |
| 127 | | | -> Merge Left Join (cost=266.19..1814.11 rows=267799 width=1199) (actual time=1359.535..1359.600 rows=119 loops=1) | |
| 128 | | | Merge Cond: (s.story_id = l.story_id) | |
| 129 | | | -> Merge Left Join (cost=137.30..253.93 rows=10133 width=1195) (actual time=1359.484..1359.515 rows=27 loops=1) | |
| 130 | | | Merge Cond: (s.story_id = c.story_id) | |
| 131 | | | -> Merge Join (cost=72.81..134.31 rows=771 width=1191) (actual time=1359.441..1359.460 rows=11 loops=1) | |
| 132 | | | Merge Cond: (st.story_id = s.story_id) | |
| 133 | | | -> 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) | |
| 134 | | | Heap Fetches: 5 | |
| 135 | | | -> Sort (cost=72.66..72.91 rows=100 width=1073) (actual time=0.236..0.240 rows=11 loops=1) | |
| 136 | | | Sort Key: s.story_id | |
| 137 | | | Sort Method: quicksort Memory: 26kB | |
| 138 | | | -> Hash Left Join (cost=23.30..69.34 rows=100 width=1073) (actual time=0.185..0.204 rows=11 loops=1) | |
| 139 | | | Hash Cond: (s.story_id = ch.story_id) | |
| 140 | | | -> Nested Loop (cost=11.05..56.12 rows=70 width=1049) (actual time=0.119..0.136 rows=5 loops=1) | |
| 141 | | | -> Hash Join (cost=10.90..21.79 rows=40 width=1053) (actual time=0.086..0.092 rows=5 loops=1) | |
| 142 | | | Hash Cond: (s.user_id = u.user_id) | |
| 143 | | | -> Seq Scan on story s (cost=0.00..10.70 rows=70 width=533) (actual time=0.019..0.020 rows=5 loops=1) | |
| 144 | | | -> Hash (cost=10.40..10.40 rows=40 width=520) (actual time=0.038..0.039 rows=10 loops=1) | |
| 145 | | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| 146 | | | -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=520) (actual time=0.023..0.025 rows=10 loops=1) | |
| 147 | | | -> 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)| |
| 148 | | | Index Cond: (user_id = s.user_id) | |
| 149 | | | Heap Fetches: 5 | |
| 150 | | | -> Hash (cost=11.00..11.00 rows=100 width=28) (actual time=0.033..0.034 rows=11 loops=1) | |
| 151 | | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| 152 | | | -> Seq Scan on chapter ch (cost=0.00..11.00 rows=100 width=28) (actual time=0.019..0.023 rows=11 loops=1) | |
| 153 | | | -> Sort (cost=64.49..66.79 rows=920 width=8) (actual time=0.024..0.027 rows=27 loops=1) | |
| 154 | | | Sort Key: c.story_id | |
| 155 | | | Sort Method: quicksort Memory: 25kB | |
| 156 | | | -> Seq Scan on comment c (cost=0.00..19.20 rows=920 width=8) (actual time=0.016..0.018 rows=11 loops=1) | |
| 157 | | | -> Sort (cost=128.89..133.52 rows=1850 width=8) (actual time=0.031..0.040 rows=119 loops=1) | |
| 158 | | | Sort Key: l.story_id | |
| 159 | | | Sort Method: quicksort Memory: 25kB | |
| 160 | | | -> Seq Scan on likes l (cost=0.00..28.50 rows=1850 width=8) (actual time=0.016..0.018 rows=18 loops=1) | |
| 161 | | | -> Memoize (cost=0.16..14.81 rows=9 width=8) (actual time=0.001..0.001 rows=0 loops=119) | |
| 162 | | | Cache Key: s.story_id | |
| 163 | | | Cache Mode: logical | |
| 164 | | | Hits: 114 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| 165 | | | -> 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) | |
| 166 | | | Index Cond: (story_id = s.story_id) | |
| 167 | | | Heap Fetches: 2 | |
| 168 | | | -> Memoize (cost=0.16..14.81 rows=9 width=8) (actual time=0.001..0.001 rows=3 loops=120) | |
| 169 | | | Cache Key: s.story_id | |
| 170 | | | Cache Mode: logical | |
| 171 | | | Hits: 115 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| 172 | | | -> 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) | |
| 173 | | | Index Cond: (story_id = s.story_id) | |
| 174 | | | Heap Fetches: 13 | |
| 175 | | | -> Memoize (cost=0.17..1.04 rows=11 width=8) (actual time=0.000..0.001 rows=3 loops=313) | |
| 176 | | | Cache Key: s.story_id | |
| 177 | | | Cache Mode: logical | |
| 178 | | | Hits: 308 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| 179 | | | -> 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) | |
| 180 | | | Index Cond: (story_id = s.story_id) | |
| 181 | | | Heap Fetches: 15 | |
| 182 | | |Planning Time: 6.145 ms | |
| 183 | | |JIT: | |
| 184 | | | Functions: 92 | |
| 185 | | | Options: Inlining true, Optimization true, Expressions true, Deforming true | |
| 186 | | | 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 | |
| 187 | | |Execution Time: 1371.545 ms | |
| 188 | | |
| 189 | | }}} |
| 190 | | Average time without indexes is: 1397.98 ms |
| | 91 | |QUERY PLAN | |
| | 92 | |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |
| | 93 | |Sort (cost=3720.58..3723.08 rows=1000 width=486) (actual time=71.545..71.555 rows=50 loops=1) | |
| | 94 | | Sort Key: with_engagement.quarter DESC, (rank() OVER (?)) | |
| | 95 | | Sort Method: quicksort Memory: 38kB | |
| | 96 | | -> WindowAgg (cost=3505.27..3670.75 rows=1000 width=486) (actual time=71.359..71.463 rows=50 loops=1) | |
| | 97 | | -> Incremental Sort (cost=3505.16..3598.25 rows=1000 width=310) (actual time=71.348..71.383 rows=50 loops=1) | |
| | 98 | | Sort Key: with_engagement.quarter, with_engagement.total_views DESC | |
| | 99 | | Presorted Key: with_engagement.quarter | |
| | 100 | | Full-sort Groups: 2 Sort Method: quicksort Average Memory: 32kB Peak Memory: 32kB | |
| | 101 | | -> WindowAgg (cost=3504.78..3570.14 rows=1000 width=310) (actual time=71.263..71.339 rows=50 loops=1) | |
| | 102 | | -> Incremental Sort (cost=3504.71..3552.64 rows=1000 width=302) (actual time=71.260..71.294 rows=50 loops=1) | |
| | 103 | | Sort Key: with_engagement.quarter, with_engagement.engagement_rate DESC | |
| | 104 | | Presorted Key: with_engagement.quarter | |
| | 105 | | Full-sort Groups: 2 Sort Method: quicksort Average Memory: 32kB Peak Memory: 32kB | |
| | 106 | | -> WindowAgg (cost=3504.55..3524.53 rows=1000 width=302) (actual time=71.199..71.254 rows=50 loops=1) | |
| | 107 | | -> Sort (cost=3504.53..3507.03 rows=1000 width=294) (actual time=71.196..71.205 rows=50 loops=1) | |
| | 108 | | Sort Key: with_engagement.quarter, with_engagement.avg_rating DESC | |
| | 109 | | Sort Method: quicksort Memory: 35kB | |
| | 110 | | -> Subquery Scan on with_engagement (cost=100.81..3454.70 rows=1000 width=294) (actual time=47.476..71.153 rows=50 loops=1) | |
| | 111 | | -> WindowAgg (cost=100.81..3444.70 rows=1000 width=298) (actual time=47.475..71.142 rows=50 loops=1) | |
| | 112 | | -> Incremental Sort (cost=97.49..3392.20 rows=1000 width=206) (actual time=47.464..71.031 rows=50 loops=1) | |
| | 113 | | Sort Key: quarterly_story_stats.story_id, quarterly_story_stats.quarter | |
| | 114 | | Presorted Key: quarterly_story_stats.story_id | |
| | 115 | | Full-sort Groups: 2 Sort Method: quicksort Average Memory: 31kB Peak Memory: 31kB | |
| | 116 | | -> 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) | |
| | 117 | | -> GroupAggregate (cost=81.02..3354.10 rows=1000 width=210) (actual time=1.961..70.960 rows=50 loops=1) | |
| | 118 | | Group Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id | |
| | 119 | | -> Incremental Sort (cost=81.02..2405.34 rows=28654 width=153) (actual time=1.178..52.207 rows=31539 loops=1) | |
| | 120 | | Sort Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id, ch.chapter_id | |
| | 121 | | Presorted Key: s.story_id, st.status | |
| | 122 | | Full-sort Groups: 49 Sort Method: quicksort Average Memory: 38kB Peak Memory: 38kB | |
| | 123 | | Pre-sorted Groups: 50 Sort Method: quicksort Average Memory: 255kB Peak Memory: 272kB | |
| | 124 | | -> Merge Left Join (cost=61.18..875.71 rows=28654 width=153) (actual time=0.524..23.446 rows=31539 loops=1) | |
| | 125 | | Merge Cond: (s.story_id = hg.story_id) | |
| | 126 | | -> Merge Left Join (cost=61.03..336.01 rows=9551 width=149) (actual time=0.498..5.824 rows=10513 loops=1) | |
| | 127 | | Merge Cond: (s.story_id = rli.story_id) | |
| | 128 | | -> Merge Left Join (cost=56.11..174.84 rows=5191 width=145) (actual time=0.457..2.666 rows=5520 loops=1) | |
| | 129 | | Merge Cond: (s.story_id = l.story_id) | |
| | 130 | | -> Merge Left Join (cost=39.47..78.01 rows=902 width=141) (actual time=0.365..1.020 rows=928 loops=1) | |
| | 131 | | Merge Cond: (s.story_id = ch.story_id) | |
| | 132 | | -> Merge Left Join (cost=20.80..45.34 rows=191 width=122) (actual time=0.229..0.541 rows=192 loops=1) | |
| | 133 | | Merge Cond: (s.story_id = c.story_id) | |
| | 134 | | -> Merge Left Join (cost=8.66..30.20 rows=50 width=118) (actual time=0.156..0.376 rows=51 loops=1) | |
| | 135 | | Merge Cond: (s.story_id = col.story_id) | |
| | 136 | | -> Nested Loop (cost=7.63..29.01 rows=50 width=114) (actual time=0.139..0.341 rows=50 loops=1) | |
| | 137 | | -> Merge Join (cost=7.46..21.09 rows=50 width=118) (actual time=0.123..0.230 rows=50 loops=1) | |
| | 138 | | Merge Cond: (st.story_id = s.story_id) | |
| | 139 | | -> 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)| |
| | 140 | | Heap Fetches: 50 | |
| | 141 | | -> Sort (cost=7.32..7.45 rows=50 width=109) (actual time=0.108..0.117 rows=50 loops=1) | |
| | 142 | | Sort Key: s.story_id | |
| | 143 | | Sort Method: quicksort Memory: 31kB | |
| | 144 | | -> Hash Join (cost=1.23..5.91 rows=50 width=109) (actual time=0.054..0.088 rows=50 loops=1) | |
| | 145 | | Hash Cond: (s.user_id = u.user_id) | |
| | 146 | | -> Seq Scan on story s (cost=0.00..4.50 rows=50 width=92) (actual time=0.025..0.035 rows=50 loops=1) | |
| | 147 | | -> Hash (cost=1.10..1.10 rows=10 width=17) (actual time=0.016..0.017 rows=10 loops=1) | |
| | 148 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 149 | | -> Seq Scan on users u (cost=0.00..1.10 rows=10 width=17) (actual time=0.008..0.010 rows=10 loops=1) | |
| | 150 | | -> Memoize (cost=0.17..1.14 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=50) | |
| | 151 | | Cache Key: s.user_id | |
| | 152 | | Cache Mode: logical | |
| | 153 | | Hits: 45 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| | 154 | | -> 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) | |
| | 155 | | Index Cond: (user_id = s.user_id) | |
| | 156 | | Heap Fetches: 5 | |
| | 157 | | -> Sort (cost=1.03..1.03 rows=2 width=8) (actual time=0.015..0.016 rows=2 loops=1) | |
| | 158 | | Sort Key: col.story_id | |
| | 159 | | Sort Method: quicksort Memory: 25kB | |
| | 160 | | -> Seq Scan on collaboration col (cost=0.00..1.02 rows=2 width=8) (actual time=0.009..0.010 rows=2 loops=1) | |
| | 161 | | -> Sort (cost=12.15..12.62 rows=191 width=8) (actual time=0.072..0.093 rows=192 loops=1) | |
| | 162 | | Sort Key: c.story_id | |
| | 163 | | Sort Method: quicksort Memory: 29kB | |
| | 164 | | -> Seq Scan on comment c (cost=0.00..4.91 rows=191 width=8) (actual time=0.011..0.043 rows=191 loops=1) | |
| | 165 | | -> Sort (cost=18.66..19.25 rows=236 width=23) (actual time=0.134..0.199 rows=925 loops=1) | |
| | 166 | | Sort Key: ch.story_id | |
| | 167 | | Sort Method: quicksort Memory: 34kB | |
| | 168 | | -> Seq Scan on chapter ch (cost=0.00..9.36 rows=236 width=23) (actual time=0.009..0.076 rows=236 loops=1) | |
| | 169 | | -> Sort (cost=16.64..17.36 rows=288 width=8) (actual time=0.091..0.440 rows=5501 loops=1) | |
| | 170 | | Sort Key: l.story_id | |
| | 171 | | Sort Method: quicksort Memory: 31kB | |
| | 172 | | -> Seq Scan on likes l (cost=0.00..4.88 rows=288 width=8) (actual time=0.014..0.049 rows=288 loops=1) | |
| | 173 | | -> Sort (cost=4.92..5.15 rows=92 width=8) (actual time=0.039..0.639 rows=9674 loops=1) | |
| | 174 | | Sort Key: rli.story_id | |
| | 175 | | Sort Method: quicksort Memory: 27kB | |
| | 176 | | -> 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) | |
| | 177 | | -> Materialize (cost=0.14..14.77 rows=150 width=8) (actual time=0.018..1.851 rows=31300 loops=1) | |
| | 178 | | -> 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) | |
| | 179 | | Heap Fetches: 150 | |
| | 180 | |Planning Time: 6.234 ms | |
| | 181 | |Execution Time: 71.861 ms | |
| | 182 | }}} |
| | 183 | Average time without indexes is: 72.384 ms |
| 213 | | }}} |
| 214 | | {{{ |
| 215 | | |QUERY PLAN | |
| 216 | | |--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |
| 217 | | |Sort (cost=333.92..334.92 rows=400 width=1003) (actual time=2.387..2.393 rows=5 loops=1) | |
| 218 | | | Sort Key: with_engagement.quarter DESC, (rank() OVER (?)) | |
| 219 | | | Sort Method: quicksort Memory: 26kB | |
| 220 | | | -> WindowAgg (cost=251.01..316.63 rows=400 width=1003) (actual time=2.367..2.381 rows=5 loops=1) | |
| 221 | | | -> Incremental Sort (cost=250.90..287.63 rows=400 width=827) (actual time=2.356..2.361 rows=5 loops=1) | |
| 222 | | | Sort Key: with_engagement.quarter, with_engagement.total_views DESC | |
| 223 | | | Presorted Key: with_engagement.quarter | |
| 224 | | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | |
| 225 | | | -> WindowAgg (cost=250.76..276.63 rows=400 width=827) (actual time=2.343..2.354 rows=5 loops=1) | |
| 226 | | | -> Incremental Sort (cost=250.70..269.63 rows=400 width=819) (actual time=2.341..2.347 rows=5 loops=1) | |
| 227 | | | Sort Key: with_engagement.quarter, with_engagement.engagement_rate DESC | |
| 228 | | | Presorted Key: with_engagement.quarter | |
| 229 | | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | |
| 230 | | | -> WindowAgg (cost=250.65..258.63 rows=400 width=819) (actual time=2.326..2.338 rows=5 loops=1) | |
| 231 | | | -> Sort (cost=250.63..251.63 rows=400 width=811) (actual time=2.323..2.328 rows=5 loops=1) | |
| 232 | | | Sort Key: with_engagement.quarter, with_engagement.avg_rating DESC | |
| 233 | | | Sort Method: quicksort Memory: 26kB | |
| 234 | | | -> Subquery Scan on with_engagement (cost=18.18..233.34 rows=400 width=811) (actual time=2.302..2.319 rows=5 loops=1) | |
| 235 | | | -> WindowAgg (cost=18.18..229.34 rows=400 width=815) (actual time=2.301..2.316 rows=5 loops=1) | |
| 236 | | | -> Incremental Sort (cost=17.68..208.34 rows=400 width=723) (actual time=2.290..2.294 rows=5 loops=1) | |
| 237 | | | Sort Key: quarterly_story_stats.story_id, quarterly_story_stats.quarter | |
| 238 | | | Presorted Key: quarterly_story_stats.story_id | |
| 239 | | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | |
| 240 | | | -> 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) | |
| 241 | | | -> GroupAggregate (cost=16.76..193.34 rows=400 width=727) (actual time=1.459..2.283 rows=5 loops=1) | |
| 242 | | | Group Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id | |
| 243 | | | -> Incremental Sort (cost=16.76..164.05 rows=686 width=670) (actual time=0.731..1.638 rows=939 loops=1) | |
| 244 | | | Sort Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id, ch.chapter_id | |
| 245 | | | Presorted Key: s.story_id, st.status | |
| 246 | | | Full-sort Groups: 5 Sort Method: quicksort Average Memory: 38kB Peak Memory: 38kB | |
| 247 | | | Pre-sorted Groups: 4 Sort Method: quicksort Average Memory: 107kB Peak Memory: 107kB | |
| 248 | | | -> Merge Left Join (cost=1.38..134.35 rows=686 width=670) (actual time=0.118..0.878 rows=939 loops=1) | |
| 249 | | | Merge Cond: (s.story_id = hg.story_id) | |
| 250 | | | -> Merge Left Join (cost=1.24..109.39 rows=229 width=666) (actual time=0.098..0.328 rows=313 loops=1) | |
| 251 | | | Merge Cond: (s.story_id = rli.story_id) | |
| 252 | | | -> Merge Left Join (cost=1.10..93.41 rows=88 width=662) (actual time=0.090..0.226 rows=120 loops=1) | |
| 253 | | | Merge Cond: (s.story_id = l.story_id) | |
| 254 | | | -> Merge Left Join (cost=0.97..79.65 rows=24 width=658) (actual time=0.082..0.176 rows=28 loops=1) | |
| 255 | | | Merge Cond: (s.story_id = c.story_id) | |
| 256 | | | -> Merge Left Join (cost=0.83..66.96 rows=11 width=654) (actual time=0.070..0.143 rows=12 loops=1) | |
| 257 | | | Merge Cond: (s.story_id = ch.story_id) | |
| 258 | | | -> Merge Left Join (cost=0.70..53.51 rows=5 width=635) (actual time=0.062..0.118 rows=6 loops=1) | |
| 259 | | | Merge Cond: (s.story_id = col.story_id) | |
| 260 | | | -> Nested Loop (cost=0.57..41.32 rows=5 width=631) (actual time=0.054..0.105 rows=5 loops=1) | |
| 261 | | | Join Filter: (s.user_id = u.user_id) | |
| 262 | | | -> Nested Loop (cost=0.43..40.38 rows=5 width=119) (actual time=0.045..0.085 rows=5 loops=1) | |
| 263 | | | -> Nested Loop (cost=0.27..23.35 rows=5 width=115) (actual time=0.031..0.056 rows=5 loops=1) | |
| 264 | | | -> 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)| |
| 265 | | | Heap Fetches: 5 | |
| 266 | | | -> 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) | |
| 267 | | | Index Cond: (story_id = st.story_id) | |
| 268 | | | -> Memoize (cost=0.17..4.98 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=5) | |
| 269 | | | Cache Key: s.user_id | |
| 270 | | | Cache Mode: logical | |
| 271 | | | Hits: 2 Misses: 3 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| 272 | | | -> 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) | |
| 273 | | | Index Cond: (user_id = s.user_id) | |
| 274 | | | Heap Fetches: 3 | |
| 275 | | | -> 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) | |
| 276 | | | Index Cond: (user_id = w.user_id) | |
| 277 | | | -> 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) | |
| 278 | | | -> 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) | |
| 279 | | | -> Materialize (cost=0.14..12.33 rows=11 width=8) (actual time=0.009..0.018 rows=27 loops=1) | |
| 280 | | | -> 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) | |
| 281 | | | -> Materialize (cost=0.14..12.45 rows=18 width=8) (actual time=0.007..0.021 rows=119 loops=1) | |
| 282 | | | -> 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) | |
| 283 | | | -> Materialize (cost=0.14..12.36 rows=13 width=8) (actual time=0.007..0.029 rows=312 loops=1) | |
| 284 | | | -> 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) | |
| 285 | | | -> Materialize (cost=0.14..12.40 rows=15 width=8) (actual time=0.007..0.069 rows=936 loops=1) | |
| 286 | | | -> 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) | |
| 287 | | |Planning Time: 5.542 ms | |
| 288 | | |Execution Time: 2.635 ms | |
| 289 | | }}} |
| 290 | | Average time: 2.74 ms |
| 291 | | |
| 292 | | 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. |
| | 218 | ANALYZE users; |
| | 219 | }}} |
| | 220 | {{{ |
| | 221 | |QUERY PLAN | |
| | 222 | |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |
| | 223 | |Sort (cost=3720.58..3723.08 rows=1000 width=486) (actual time=74.567..74.583 rows=50 loops=1) | |
| | 224 | | Sort Key: with_engagement.quarter DESC, (rank() OVER (?)) | |
| | 225 | | Sort Method: quicksort Memory: 38kB | |
| | 226 | | -> WindowAgg (cost=3505.27..3670.75 rows=1000 width=486) (actual time=74.380..74.490 rows=50 loops=1) | |
| | 227 | | -> Incremental Sort (cost=3505.16..3598.25 rows=1000 width=310) (actual time=74.364..74.404 rows=50 loops=1) | |
| | 228 | | Sort Key: with_engagement.quarter, with_engagement.total_views DESC | |
| | 229 | | Presorted Key: with_engagement.quarter | |
| | 230 | | Full-sort Groups: 2 Sort Method: quicksort Average Memory: 32kB Peak Memory: 32kB | |
| | 231 | | -> WindowAgg (cost=3504.78..3570.14 rows=1000 width=310) (actual time=74.278..74.359 rows=50 loops=1) | |
| | 232 | | -> Incremental Sort (cost=3504.71..3552.64 rows=1000 width=302) (actual time=74.274..74.311 rows=50 loops=1) | |
| | 233 | | Sort Key: with_engagement.quarter, with_engagement.engagement_rate DESC | |
| | 234 | | Presorted Key: with_engagement.quarter | |
| | 235 | | Full-sort Groups: 2 Sort Method: quicksort Average Memory: 32kB Peak Memory: 32kB | |
| | 236 | | -> WindowAgg (cost=3504.55..3524.53 rows=1000 width=302) (actual time=74.209..74.271 rows=50 loops=1) | |
| | 237 | | -> Sort (cost=3504.53..3507.03 rows=1000 width=294) (actual time=74.202..74.216 rows=50 loops=1) | |
| | 238 | | Sort Key: with_engagement.quarter, with_engagement.avg_rating DESC | |
| | 239 | | Sort Method: quicksort Memory: 35kB | |
| | 240 | | -> Subquery Scan on with_engagement (cost=100.81..3454.70 rows=1000 width=294) (actual time=49.439..74.159 rows=50 loops=1) | |
| | 241 | | -> WindowAgg (cost=100.81..3444.70 rows=1000 width=298) (actual time=49.437..74.147 rows=50 loops=1) | |
| | 242 | | -> Incremental Sort (cost=97.49..3392.20 rows=1000 width=206) (actual time=49.423..74.026 rows=50 loops=1) | |
| | 243 | | Sort Key: quarterly_story_stats.story_id, quarterly_story_stats.quarter | |
| | 244 | | Presorted Key: quarterly_story_stats.story_id | |
| | 245 | | Full-sort Groups: 2 Sort Method: quicksort Average Memory: 31kB Peak Memory: 31kB | |
| | 246 | | -> 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) | |
| | 247 | | -> GroupAggregate (cost=81.02..3354.10 rows=1000 width=210) (actual time=2.133..73.936 rows=50 loops=1) | |
| | 248 | | Group Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id | |
| | 249 | | -> Incremental Sort (cost=81.02..2405.34 rows=28654 width=153) (actual time=1.210..53.754 rows=31539 loops=1) | |
| | 250 | | Sort Key: s.story_id, st.status, (date_trunc('quarter'::text, s.story_created_at)), u.user_id, ch.chapter_id | |
| | 251 | | Presorted Key: s.story_id, st.status | |
| | 252 | | Full-sort Groups: 49 Sort Method: quicksort Average Memory: 38kB Peak Memory: 38kB | |
| | 253 | | Pre-sorted Groups: 50 Sort Method: quicksort Average Memory: 255kB Peak Memory: 272kB | |
| | 254 | | -> Merge Left Join (cost=61.18..875.71 rows=28654 width=153) (actual time=0.533..24.135 rows=31539 loops=1) | |
| | 255 | | Merge Cond: (s.story_id = hg.story_id) | |
| | 256 | | -> Merge Left Join (cost=61.03..336.01 rows=9551 width=149) (actual time=0.507..6.219 rows=10513 loops=1) | |
| | 257 | | Merge Cond: (s.story_id = rli.story_id) | |
| | 258 | | -> Merge Left Join (cost=56.11..174.84 rows=5191 width=145) (actual time=0.467..2.947 rows=5520 loops=1) | |
| | 259 | | Merge Cond: (s.story_id = l.story_id) | |
| | 260 | | -> Merge Left Join (cost=39.47..78.01 rows=902 width=141) (actual time=0.374..1.145 rows=928 loops=1) | |
| | 261 | | Merge Cond: (s.story_id = ch.story_id) | |
| | 262 | | -> Merge Left Join (cost=20.80..45.34 rows=191 width=122) (actual time=0.240..0.645 rows=192 loops=1) | |
| | 263 | | Merge Cond: (s.story_id = c.story_id) | |
| | 264 | | -> Merge Left Join (cost=8.66..30.20 rows=50 width=118) (actual time=0.167..0.471 rows=51 loops=1) | |
| | 265 | | Merge Cond: (s.story_id = col.story_id) | |
| | 266 | | -> Nested Loop (cost=7.63..29.01 rows=50 width=114) (actual time=0.150..0.432 rows=50 loops=1) | |
| | 267 | | -> Merge Join (cost=7.46..21.09 rows=50 width=118) (actual time=0.133..0.291 rows=50 loops=1) | |
| | 268 | | Merge Cond: (st.story_id = s.story_id) | |
| | 269 | | -> 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)| |
| | 270 | | Heap Fetches: 50 | |
| | 271 | | -> Sort (cost=7.32..7.45 rows=50 width=109) (actual time=0.116..0.130 rows=50 loops=1) | |
| | 272 | | Sort Key: s.story_id | |
| | 273 | | Sort Method: quicksort Memory: 31kB | |
| | 274 | | -> Hash Join (cost=1.23..5.91 rows=50 width=109) (actual time=0.045..0.098 rows=50 loops=1) | |
| | 275 | | Hash Cond: (s.user_id = u.user_id) | |
| | 276 | | -> Seq Scan on story s (cost=0.00..4.50 rows=50 width=92) (actual time=0.016..0.027 rows=50 loops=1) | |
| | 277 | | -> Hash (cost=1.10..1.10 rows=10 width=17) (actual time=0.017..0.018 rows=10 loops=1) | |
| | 278 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 279 | | -> Seq Scan on users u (cost=0.00..1.10 rows=10 width=17) (actual time=0.008..0.010 rows=10 loops=1) | |
| | 280 | | -> Memoize (cost=0.17..1.14 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=50) | |
| | 281 | | Cache Key: s.user_id | |
| | 282 | | Cache Mode: logical | |
| | 283 | | Hits: 45 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| | 284 | | -> 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) | |
| | 285 | | Index Cond: (user_id = s.user_id) | |
| | 286 | | Heap Fetches: 5 | |
| | 287 | | -> Sort (cost=1.03..1.03 rows=2 width=8) (actual time=0.015..0.016 rows=2 loops=1) | |
| | 288 | | Sort Key: col.story_id | |
| | 289 | | Sort Method: quicksort Memory: 25kB | |
| | 290 | | -> Seq Scan on collaboration col (cost=0.00..1.02 rows=2 width=8) (actual time=0.009..0.010 rows=2 loops=1) | |
| | 291 | | -> Sort (cost=12.15..12.62 rows=191 width=8) (actual time=0.071..0.094 rows=192 loops=1) | |
| | 292 | | Sort Key: c.story_id | |
| | 293 | | Sort Method: quicksort Memory: 29kB | |
| | 294 | | -> Seq Scan on comment c (cost=0.00..4.91 rows=191 width=8) (actual time=0.011..0.043 rows=191 loops=1) | |
| | 295 | | -> Sort (cost=18.66..19.25 rows=236 width=23) (actual time=0.133..0.217 rows=925 loops=1) | |
| | 296 | | Sort Key: ch.story_id | |
| | 297 | | Sort Method: quicksort Memory: 34kB | |
| | 298 | | -> Seq Scan on chapter ch (cost=0.00..9.36 rows=236 width=23) (actual time=0.009..0.077 rows=236 loops=1) | |
| | 299 | | -> Sort (cost=16.64..17.36 rows=288 width=8) (actual time=0.091..0.464 rows=5501 loops=1) | |
| | 300 | | Sort Key: l.story_id | |
| | 301 | | Sort Method: quicksort Memory: 31kB | |
| | 302 | | -> Seq Scan on likes l (cost=0.00..4.88 rows=288 width=8) (actual time=0.013..0.049 rows=288 loops=1) | |
| | 303 | | -> Sort (cost=4.92..5.15 rows=92 width=8) (actual time=0.039..0.657 rows=9674 loops=1) | |
| | 304 | | Sort Key: rli.story_id | |
| | 305 | | Sort Method: quicksort Memory: 27kB | |
| | 306 | | -> 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) | |
| | 307 | | -> Materialize (cost=0.14..14.77 rows=150 width=8) (actual time=0.018..1.898 rows=31300 loops=1) | |
| | 308 | | -> 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) | |
| | 309 | | Heap Fetches: 150 | |
| | 310 | |Planning Time: 8.520 ms | |
| | 311 | |Execution Time: 74.902 ms | |
| | 312 | }}} |
| | 313 | Average time: 72.240 ms |
| | 314 | |
| | 315 | 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. |
