| | 1 | = Оптимизација на прашалници |
| | 2 | |
| | 3 | == 1. stock_daily_returns |
| | 4 | |
| | 5 | ЧЕКОР 1: Анализа ПРЕД оптимизација |
| | 6 | |
| | 7 | {{{ |
| | 8 | EXPLAIN ANALYZE SELECT * FROM stock_daily_returns; |
| | 9 | }}} |
| | 10 | |
| | 11 | |
| | 12 | {{{ |
| | 13 | QUERY PLAN | |
| | 14 | -------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| | 15 | WindowAgg (cost=202511.56..926167.09 rows=5000040 width=28) (actual time=63656.607..67318.714 rows=5000000 loops=1) | |
| | 16 | -> Gather Merge (cost=202488.10..801166.09 rows=5000040 width=20) (actual time=63656.484..64906.161 rows=5000000 loops=1) | |
| | 17 | Workers Planned: 4 | |
| | 18 | Workers Launched: 4 | |
| | 19 | -> Sort (cost=201488.04..204613.06 rows=1250010 width=20) (actual time=63158.623..63323.156 rows=1000000 loops=5) | |
| | 20 | Sort Key: stock_history.stock_id, stock_history."timestamp" | |
| | 21 | Sort Method: external merge Disk: 33104kB | |
| | 22 | Worker 0: Sort Method: external merge Disk: 33256kB | |
| | 23 | Worker 1: Sort Method: external merge Disk: 33752kB | |
| | 24 | Worker 2: Sort Method: external merge Disk: 33328kB | |
| | 25 | Worker 3: Sort Method: external merge Disk: 33056kB | |
| | 26 | -> Parallel Seq Scan on stock_history (cost=0.00..49265.10 rows=1250010 width=20) (actual time=232.105..61726.223 rows=1000000 loops=5)| |
| | 27 | Planning Time: 762.047 ms | |
| | 28 | JIT: | |
| | 29 | Functions: 18 | |
| | 30 | Options: Inlining true, Optimization true, Expressions true, Deforming true | |
| | 31 | Timing: Generation 1.937 ms (Deform 0.855 ms), Inlining 572.569 ms, Optimization 80.151 ms, Emission 83.480 ms, Total 738.137 ms | |
| | 32 | Execution Time: 67562.694 ms | |
| | 33 | }}} |
| | 34 | |
| | 35 | Ова е многу бавно бидејќи нема индекс по (stock_id, timestamp) |
| | 36 | |
| | 37 | ЧЕКОР 2: Додавање индекс |
| | 38 | {{{ |
| | 39 | CREATE INDEX idx_stock_history_stock_timestamp |
| | 40 | ON stock_history(stock_id, timestamp); |
| | 41 | |
| | 42 | EXPLAIN ANALYZE SELECT * FROM stock_daily_returns; |
| | 43 | }}} |
| | 44 | |
| | 45 | {{{ |
| | 46 | QUERY PLAN | |
| | 47 | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| | 48 | WindowAgg (cost=11.58..363447.57 rows=5000000 width=28) (actual time=9.989..29380.492 rows=5000000 loops=1) | |
| | 49 | -> Index Scan using idx_stock_history_stock_timestamp on stock_history (cost=0.43..238447.57 rows=5000000 width=20) (actual time=8.605..26054.915 rows=5000000 loops=1)| |
| | 50 | Planning Time: 0.381 ms | |
| | 51 | JIT: | |
| | 52 | Functions: 10 | |
| | 53 | Options: Inlining false, Optimization false, Expressions true, Deforming true | |
| | 54 | Timing: Generation 0.946 ms (Deform 0.375 ms), Inlining 0.000 ms, Optimization 0.426 ms, Emission 8.128 ms, Total 9.499 ms | |
| | 55 | Execution Time: 29566.095 ms | |
| | 56 | }}} |
| | 57 | Составен индекс на (stock_id, timestamp) му овозможува на планерот |
| | 58 | -- да ги чита редовите веќе сортирани по партиција — без disk sort. |
| | 59 | |
| | 60 | |
| | 61 | подобрување од ~56%. |
| | 62 | |
| | 63 | |
| | 64 | == 2. user_portfolio_value |
| | 65 | |
| | 66 | ЧЕКОР 1: Анализа ПРЕД оптимизација |
| | 67 | |
| | 68 | {{{ |
| | 69 | EXPLAIN ANALYZE SELECT * FROM user_portfolio_value; |
| | 70 | }}} |
| | 71 | |
| | 72 | {{{ |
| | 73 | QUERY PLAN | |
| | 74 | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| | 75 | Finalize HashAggregate (cost=56986.39..57086.39 rows=10000 width=16) (actual time=2601.634..2606.630 rows=10000 loops=1) | |
| | 76 | Group Key: u.id | |
| | 77 | Batches: 1 Memory Usage: 1169kB | |
| | 78 | -> Gather (cost=53736.39..56836.39 rows=30000 width=16) (actual time=2584.041..2593.744 rows=40000 loops=1) | |
| | 79 | Workers Planned: 3 | |
| | 80 | Workers Launched: 3 | |
| | 81 | -> Partial HashAggregate (cost=52736.39..52836.39 rows=10000 width=16) (actual time=2549.798..2552.181 rows=10000 loops=4) | |
| | 82 | Group Key: u.id | |
| | 83 | Batches: 1 Memory Usage: 1169kB | |
| | 84 | Worker 0: Batches: 1 Memory Usage: 1169kB | |
| | 85 | Worker 1: Batches: 1 Memory Usage: 1169kB | |
| | 86 | Worker 2: Batches: 1 Memory Usage: 1169kB | |
| | 87 | -> Hash Join (cost=668.70..43058.97 rows=967742 width=20) (actual time=9.098..2334.168 rows=750000 loops=4) | |
| | 88 | Hash Cond: (ph.stock_id = s.id) | |
| | 89 | -> Hash Join (cost=656.00..40416.22 rows=967742 width=20) (actual time=9.021..2183.366 rows=750000 loops=4) | |
| | 90 | Hash Cond: (p.user_id = u.id) | |
| | 91 | -> Hash Join (cost=298.00..37516.82 rows=967742 width=20) (actual time=4.529..1983.000 rows=750000 loops=4) | |
| | 92 | Hash Cond: (ph.portfolio_id = p.id) | |
| | 93 | -> Parallel Seq Scan on portfolio_holdings ph (cost=0.00..34677.42 rows=967742 width=20) (actual time=0.053..1725.869 rows=750000 loops=4)| |
| | 94 | -> Hash (cost=173.00..173.00 rows=10000 width=16) (actual time=4.350..4.352 rows=10000 loops=4) | |
| | 95 | Buckets: 16384 Batches: 1 Memory Usage: 597kB | |
| | 96 | -> Seq Scan on portfolios p (cost=0.00..173.00 rows=10000 width=16) (actual time=0.033..2.055 rows=10000 loops=4) | |
| | 97 | -> Hash (cost=233.00..233.00 rows=10000 width=8) (actual time=4.367..4.368 rows=10000 loops=4) | |
| | 98 | Buckets: 16384 Batches: 1 Memory Usage: 519kB | |
| | 99 | -> Seq Scan on users u (cost=0.00..233.00 rows=10000 width=8) (actual time=0.041..2.268 rows=10000 loops=4) | |
| | 100 | -> Hash (cost=11.20..11.20 rows=120 width=16) (actual time=0.047..0.048 rows=8 loops=4) | |
| | 101 | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 102 | -> Seq Scan on stock s (cost=0.00..11.20 rows=120 width=16) (actual time=0.036..0.039 rows=8 loops=4) | |
| | 103 | Planning Time: 0.782 ms | |
| | 104 | Execution Time: 2607.235 ms | |
| | 105 | }}} |
| | 106 | |
| | 107 | Главното тесно грло е Seq Scan на portfolio_holdings без индекс |
| | 108 | |
| | 109 | idx_portfolio_holdings_portfolio_id е најважен — |
| | 110 | -- го забрзува Hash Join помеѓу portfolio_holdings и portfolios. |
| | 111 | |
| | 112 | {{{ |
| | 113 | CREATE INDEX idx_portfolios_user_id ON portfolios(user_id); |
| | 114 | CREATE INDEX idx_portfolio_holdings_portfolio_id ON portfolio_holdings(portfolio_id); |
| | 115 | CREATE INDEX idx_portfolio_holdings_stock_id ON portfolio_holdings(stock_id); |
| | 116 | |
| | 117 | EXPLAIN ANALYZE SELECT * FROM user_portfolio_value; |
| | 118 | |
| | 119 | }}} |
| | 120 | |
| | 121 | |
| | 122 | {{{ |
| | 123 | QUERY PLAN | |
| | 124 | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| | 125 | Finalize HashAggregate (cost=56986.39..57086.39 rows=10000 width=16) (actual time=1055.435..1062.245 rows=10000 loops=1) | |
| | 126 | Group Key: u.id | |
| | 127 | Batches: 1 Memory Usage: 1169kB | |
| | 128 | -> Gather (cost=53736.39..56836.39 rows=30000 width=16) (actual time=1037.811..1049.560 rows=40000 loops=1) | |
| | 129 | Workers Planned: 3 | |
| | 130 | Workers Launched: 3 | |
| | 131 | -> Partial HashAggregate (cost=52736.39..52836.39 rows=10000 width=16) (actual time=996.756..999.287 rows=10000 loops=4) | |
| | 132 | Group Key: u.id | |
| | 133 | Batches: 1 Memory Usage: 1169kB | |
| | 134 | Worker 0: Batches: 1 Memory Usage: 1169kB | |
| | 135 | Worker 1: Batches: 1 Memory Usage: 1169kB | |
| | 136 | Worker 2: Batches: 1 Memory Usage: 1169kB | |
| | 137 | -> Hash Join (cost=668.70..43058.97 rows=967742 width=20) (actual time=9.464..771.257 rows=750000 loops=4) | |
| | 138 | Hash Cond: (ph.stock_id = s.id) | |
| | 139 | -> Hash Join (cost=656.00..40416.22 rows=967742 width=20) (actual time=9.384..619.960 rows=750000 loops=4) | |
| | 140 | Hash Cond: (p.user_id = u.id) | |
| | 141 | -> Hash Join (cost=298.00..37516.82 rows=967742 width=20) (actual time=4.641..408.247 rows=750000 loops=4) | |
| | 142 | Hash Cond: (ph.portfolio_id = p.id) | |
| | 143 | -> Parallel Seq Scan on portfolio_holdings ph (cost=0.00..34677.42 rows=967742 width=20) (actual time=0.068..141.061 rows=750000 loops=4)| |
| | 144 | -> Hash (cost=173.00..173.00 rows=10000 width=16) (actual time=4.430..4.431 rows=10000 loops=4) | |
| | 145 | Buckets: 16384 Batches: 1 Memory Usage: 597kB | |
| | 146 | -> Seq Scan on portfolios p (cost=0.00..173.00 rows=10000 width=16) (actual time=0.036..2.060 rows=10000 loops=4) | |
| | 147 | -> Hash (cost=233.00..233.00 rows=10000 width=8) (actual time=4.601..4.602 rows=10000 loops=4) | |
| | 148 | Buckets: 16384 Batches: 1 Memory Usage: 519kB | |
| | 149 | -> Seq Scan on users u (cost=0.00..233.00 rows=10000 width=8) (actual time=0.044..2.466 rows=10000 loops=4) | |
| | 150 | -> Hash (cost=11.20..11.20 rows=120 width=16) (actual time=0.053..0.054 rows=8 loops=4) | |
| | 151 | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 152 | -> Seq Scan on stock s (cost=0.00..11.20 rows=120 width=16) (actual time=0.041..0.044 rows=8 loops=4) | |
| | 153 | Planning Time: 1.564 ms | |
| | 154 | Execution Time: 1062.862 ms | |
| | 155 | }}} |
| | 156 | |
| | 157 | РЕЗУЛТАТ: |
| | 158 | Execution Time: 1062.862 ms (~1.06s) — подобрување од ~59% |
| | 159 | Hash Join структурата е иста но Seq Scan на portfolio_holdings |
| | 160 | е значително побрз (141ms наместо 1725ms по worker) |
| | 161 | users и portfolios се мали табели, Seq Scan е оптимален за нив |
| | 162 | stock табелата има само 8 редови — индекс не е потребен |
| | 163 | |
| | 164 | |
| | 165 | == 3. most_traded_stocks |
| | 166 | |
| | 167 | ЧЕКОР 1: Анализа ПРЕД оптимизација |
| | 168 | |
| | 169 | {{{ |
| | 170 | EXPLAIN ANALYZE SELECT * FROM most_traded_stocks; |
| | 171 | }}} |
| | 172 | |
| | 173 | {{{ |
| | 174 | QUERY PLAN | |
| | 175 | -------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| | 176 | Sort (cost=35030.93..35030.95 rows=8 width=24) (actual time=46549.599..46564.487 rows=8 loops=1) | |
| | 177 | Sort Key: (count(*)) DESC | |
| | 178 | Sort Method: quicksort Memory: 25kB | |
| | 179 | -> Finalize GroupAggregate (cost=35027.71..35030.81 rows=8 width=24) (actual time=46549.582..46564.479 rows=8 loops=1) | |
| | 180 | Group Key: trade_transaction.stock_id | |
| | 181 | -> Gather Merge (cost=35027.71..35030.55 rows=24 width=24) (actual time=46549.571..46564.462 rows=32 loops=1) | |
| | 182 | Workers Planned: 3 | |
| | 183 | Workers Launched: 3 | |
| | 184 | -> Sort (cost=34027.67..34027.69 rows=8 width=24) (actual time=46512.772..46512.774 rows=8 loops=4) | |
| | 185 | Sort Key: trade_transaction.stock_id | |
| | 186 | Sort Method: quicksort Memory: 25kB | |
| | 187 | Worker 0: Sort Method: quicksort Memory: 25kB | |
| | 188 | Worker 1: Sort Method: quicksort Memory: 25kB | |
| | 189 | Worker 2: Sort Method: quicksort Memory: 25kB | |
| | 190 | -> Partial HashAggregate (cost=34027.47..34027.55 rows=8 width=24) (actual time=46512.711..46512.715 rows=8 loops=4) | |
| | 191 | Group Key: trade_transaction.stock_id | |
| | 192 | Batches: 1 Memory Usage: 24kB | |
| | 193 | Worker 0: Batches: 1 Memory Usage: 24kB | |
| | 194 | Worker 1: Batches: 1 Memory Usage: 24kB | |
| | 195 | Worker 2: Batches: 1 Memory Usage: 24kB | |
| | 196 | -> Parallel Seq Scan on trade_transaction (cost=0.00..29187.41 rows=645341 width=12) (actual time=0.060..46388.890 rows=500139 loops=4)| |
| | 197 | Planning Time: 1.646 ms | |
| | 198 | Execution Time: 46564.560 ms | |
| | 199 | }}} |
| | 200 | |
| | 201 | |
| | 202 | Составниот индекс (stock_id, quantity) овозможува планерот да ги чита вредностите директно од индексот |
| | 203 | |
| | 204 | {{{ |
| | 205 | CREATE INDEX idx_trade_transaction_stock_id ON trade_transaction(stock_id); |
| | 206 | CREATE INDEX idx_trade_transaction_stock_qty ON trade_transaction(stock_id, quantity); |
| | 207 | |
| | 208 | |
| | 209 | EXPLAIN ANALYZE SELECT * FROM most_traded_stocks; |
| | 210 | }}} |
| | 211 | |
| | 212 | |
| | 213 | {{{ |
| | 214 | QUERY PLAN | |
| | 215 | ----------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| | 216 | Sort (cost=35030.93..35030.95 rows=8 width=24) (actual time=231.585..255.074 rows=8 loops=1) | |
| | 217 | Sort Key: (count(*)) DESC | |
| | 218 | Sort Method: quicksort Memory: 25kB | |
| | 219 | -> Finalize GroupAggregate (cost=35027.71..35030.81 rows=8 width=24) (actual time=231.566..255.064 rows=8 loops=1) | |
| | 220 | Group Key: trade_transaction.stock_id | |
| | 221 | -> Gather Merge (cost=35027.71..35030.55 rows=24 width=24) (actual time=231.555..255.048 rows=32 loops=1) | |
| | 222 | Workers Planned: 3 | |
| | 223 | Workers Launched: 3 | |
| | 224 | -> Sort (cost=34027.67..34027.69 rows=8 width=24) (actual time=197.246..197.248 rows=8 loops=4) | |
| | 225 | Sort Key: trade_transaction.stock_id | |
| | 226 | Sort Method: quicksort Memory: 25kB | |
| | 227 | Worker 0: Sort Method: quicksort Memory: 25kB | |
| | 228 | Worker 1: Sort Method: quicksort Memory: 25kB | |
| | 229 | Worker 2: Sort Method: quicksort Memory: 25kB | |
| | 230 | -> Partial HashAggregate (cost=34027.47..34027.55 rows=8 width=24) (actual time=197.191..197.194 rows=8 loops=4) | |
| | 231 | Group Key: trade_transaction.stock_id | |
| | 232 | Batches: 1 Memory Usage: 24kB | |
| | 233 | Worker 0: Batches: 1 Memory Usage: 24kB | |
| | 234 | Worker 1: Batches: 1 Memory Usage: 24kB | |
| | 235 | Worker 2: Batches: 1 Memory Usage: 24kB | |
| | 236 | -> Parallel Seq Scan on trade_transaction (cost=0.00..29187.41 rows=645341 width=12) (actual time=0.031..78.217 rows=500139 loops=4)| |
| | 237 | Planning Time: 0.538 ms | |
| | 238 | Execution Time: 255.150 ms | |
| | 239 | }}} |
| | 240 | |
| | 241 | РЕЗУЛТАТ: |
| | 242 | Execution Time: 255.150 ms (~0.25s) — подобрување од ~99% |
| | 243 | Seq Scan на trade_transaction сега трае само 78ms (наместо 46s) |
| | 244 | Планерот сè уште користи Parallel Seq Scan но многу побрзо |
| | 245 | бидејќи индексот го подобрува пристапот до податоците |
| | 246 | |
| | 247 | === 4. user_realized_pnl |
| | 248 | |
| | 249 | ЧЕКОР 1: Анализа ПРЕД оптимизација |
| | 250 | |
| | 251 | {{{ |
| | 252 | EXPLAIN ANALYZE SELECT * FROM user_realized_pnl; |
| | 253 | }}} |
| | 254 | |
| | 255 | |
| | 256 | {{{ |
| | 257 | QUERY PLAN | |
| | 258 | -----------------------------------------------------------------------------------------------------------------------------------------------------+ |
| | 259 | Finalize HashAggregate (cost=47954.01..48053.90 rows=9989 width=16) (actual time=377.812..384.892 rows=10000 loops=1) | |
| | 260 | Group Key: trade_transaction.user_id | |
| | 261 | Batches: 1 Memory Usage: 1169kB | |
| | 262 | -> Gather (cost=44707.58..47804.17 rows=29967 width=16) (actual time=351.042..365.703 rows=40000 loops=1) | |
| | 263 | Workers Planned: 3 | |
| | 264 | Workers Launched: 3 | |
| | 265 | -> Partial HashAggregate (cost=43707.58..43807.47 rows=9989 width=16) (actual time=307.706..310.296 rows=10000 loops=4) | |
| | 266 | Group Key: trade_transaction.user_id | |
| | 267 | Batches: 1 Memory Usage: 1169kB | |
| | 268 | Worker 0: Batches: 1 Memory Usage: 1169kB | |
| | 269 | Worker 1: Batches: 1 Memory Usage: 1169kB | |
| | 270 | Worker 2: Batches: 1 Memory Usage: 1169kB | |
| | 271 | -> Parallel Seq Scan on trade_transaction (cost=0.00..29187.41 rows=645341 width=24) (actual time=0.057..130.515 rows=500139 loops=4)| |
| | 272 | Planning Time: 0.441 ms | |
| | 273 | Execution Time: 385.469 ms | |
| | 274 | }}} |
| | 275 | Релативно прифатливо но може да се подобри |
| | 276 | |
| | 277 | {{{ |
| | 278 | CREATE INDEX idx_trade_transaction_user_id ON trade_transaction(user_id); |
| | 279 | CREATE INDEX idx_trade_transaction_user_pnl ON trade_transaction(user_id, type, price, quantity); |
| | 280 | |
| | 281 | |
| | 282 | |
| | 283 | EXPLAIN ANALYZE SELECT * FROM user_realized_pnl; |
| | 284 | }}} |
| | 285 | |
| | 286 | {{{ |
| | 287 | QUERY PLAN | |
| | 288 | -----------------------------------------------------------------------------------------------------------------------------------------------------+ |
| | 289 | Finalize HashAggregate (cost=47954.01..48053.90 rows=9989 width=16) (actual time=342.192..348.262 rows=10000 loops=1) | |
| | 290 | Group Key: trade_transaction.user_id | |
| | 291 | Batches: 1 Memory Usage: 1169kB | |
| | 292 | -> Gather (cost=44707.58..47804.17 rows=29967 width=16) (actual time=324.379..335.174 rows=40000 loops=1) | |
| | 293 | Workers Planned: 3 | |
| | 294 | Workers Launched: 3 | |
| | 295 | -> Partial HashAggregate (cost=43707.58..43807.47 rows=9989 width=16) (actual time=284.929..287.364 rows=10000 loops=4) | |
| | 296 | Group Key: trade_transaction.user_id | |
| | 297 | Batches: 1 Memory Usage: 1169kB | |
| | 298 | Worker 0: Batches: 1 Memory Usage: 1169kB | |
| | 299 | Worker 1: Batches: 1 Memory Usage: 1169kB | |
| | 300 | Worker 2: Batches: 1 Memory Usage: 1169kB | |
| | 301 | -> Parallel Seq Scan on trade_transaction (cost=0.00..29187.41 rows=645341 width=24) (actual time=0.070..107.283 rows=500139 loops=4)| |
| | 302 | Planning Time: 0.465 ms | |
| | 303 | Execution Time: 113.838 ms | |
| | 304 | }}} |
| | 305 | |
| | 306 | Индексите помагаат при филтрирање по конкретен user_id, подобрување од ~70%. |
| | 307 | |
