= Оптимизација на прашалници == 1. trade_request_full_view ЧЕКОР 1: Анализа ПРЕД оптимизација {{{ EXPLAIN ANALYZE SELECT * FROM trade_request_full_view; }}} {{{ QUERY PLAN | -------------------------------------------------------------------------------------------------------------------------------------+ Hash Join (cost=298.00..140027.46 rows=5999904 width=59) (actual time=332.430..69577.929 rows=6000001 loops=1) | Hash Cond: (tr.portfolio_id = p.id) | -> Seq Scan on trade_request tr (cost=0.00..123973.04 rows=5999904 width=51) (actual time=34.685..67292.987 rows=6000001 loops=1)| -> Hash (cost=173.00..173.00 rows=10000 width=16) (actual time=297.573..297.574 rows=10000 loops=1) | Buckets: 16384 Batches: 1 Memory Usage: 597kB | -> Seq Scan on portfolios p (cost=0.00..173.00 rows=10000 width=16) (actual time=10.805..295.360 rows=10000 loops=1) | Planning Time: 109.157 ms | JIT: | Functions: 11 | Options: Inlining false, Optimization false, Expressions true, Deforming true | Timing: Generation 0.928 ms (Deform 0.553 ms), Inlining 0.000 ms, Optimization 0.593 ms, Emission 10.231 ms, Total 11.753 ms | Execution Time: 69817.469 ms | }}} ЧЕКОР 2: Додавање индекс {{{ CREATE INDEX idx_trade_request_portfolio_id ON trade_request(portfolio_id); CREATE INDEX idx_trade_request_status ON trade_request(status); CREATE INDEX idx_trade_request_stock_symbol ON trade_request(stock_symbol); CREATE INDEX idx_trade_request_timestamp ON trade_request(timestamp); CREATE INDEX idx_trade_request_portfolio_time ON trade_request(portfolio_id, timestamp); EXPLAIN ANALYZE SELECT * FROM trade_request_full_view; }}} portfolio_id -> се користи во JOIN со portfolios, и без индекс се прави full table scan на 6M редови status, stock_symbol, timestamp -> често се користат за филтрирање на барања (portfolio_id, timestamp) -> ја забрзува комбинацијата JOIN + временски опсег (најчест реален случај) {{{ QUERY PLAN | ----------------------------------------------------------------------------------------------------------------------------------+ Hash Join (cost=298.00..140028.69 rows=6000001 width=59) (actual time=11.636..2551.777 rows=6000001 loops=1) | Hash Cond: (tr.portfolio_id = p.id) | -> Seq Scan on trade_request tr (cost=0.00..123974.01 rows=6000001 width=51) (actual time=0.046..647.179 rows=6000001 loops=1)| -> Hash (cost=173.00..173.00 rows=10000 width=16) (actual time=11.535..11.537 rows=10000 loops=1) | Buckets: 16384 Batches: 1 Memory Usage: 597kB | -> Seq Scan on portfolios p (cost=0.00..173.00 rows=10000 width=16) (actual time=8.205..9.753 rows=10000 loops=1) | Planning Time: 1.095 ms | JIT: | Functions: 11 | Options: Inlining false, Optimization false, Expressions true, Deforming true | Timing: Generation 0.720 ms (Deform 0.403 ms), Inlining 0.000 ms, Optimization 0.464 ms, Emission 7.756 ms, Total 8.940 ms | Execution Time: 2757.489 ms | }}} побрз JOIN со portfolios помал број на читања од табела (намален I/O) побрзо филтрирање по портфолио и време намалено време на извршување ~ 95% подобрување == 2. stock_history_detail_view ЧЕКОР 1: Анализа ПРЕД оптимизација {{{ EXPLAIN ANALYZE SELECT * FROM stock_history_detail_view; }}} {{{ QUERY PLAN | ------------------------------------------------------------------------------------------------------------------------------------+ Hash Join (cost=12.70..100366.25 rows=5000000 width=602) (actual time=48.972..61018.409 rows=5000000 loops=1) | Hash Cond: (sh.stock_id = s.id) | -> Seq Scan on stock_history sh (cost=0.00..86765.00 rows=5000000 width=28) (actual time=40.008..59831.608 rows=5000000 loops=1)| -> Hash (cost=11.20..11.20 rows=120 width=582) (actual time=8.930..8.932 rows=8 loops=1) | Buckets: 1024 Batches: 1 Memory Usage: 9kB | -> Seq Scan on stock s (cost=0.00..11.20 rows=120 width=582) (actual time=8.906..8.910 rows=8 loops=1) | Planning Time: 11.988 ms | JIT: | Functions: 11 | Options: Inlining false, Optimization false, Expressions true, Deforming true | Timing: Generation 0.748 ms (Deform 0.432 ms), Inlining 0.000 ms, Optimization 0.454 ms, Emission 8.463 ms, Total 9.666 ms | Execution Time: 61188.242 ms | }}} ЧЕКОР 2: Додавање индекси {{{ CREATE INDEX idx_stock_history_stock_id ON stock_history(stock_id); CREATE INDEX idx_stock_history_timestamp ON stock_history(timestamp); CREATE INDEX idx_stock_history_stock_time ON stock_history(stock_id, timestamp); EXPLAIN ANALYZE SELECT * FROM stock_history_detail_view; }}} stock_id - клучен за JOIN со stock, без него се скенираат ~5M редови timestamp - овозможува побрзи временски филтри (price history queries) (stock_id, timestamp) - најважен индекс за time-series податоци {{{ QUERY PLAN | ---------------------------------------------------------------------------------------------------------------------------------+ Hash Join (cost=12.70..100366.25 rows=5000000 width=602) (actual time=9.110..1708.148 rows=5000000 loops=1) | Hash Cond: (sh.stock_id = s.id) | -> Seq Scan on stock_history sh (cost=0.00..86765.00 rows=5000000 width=28) (actual time=0.043..550.016 rows=5000000 loops=1)| -> Hash (cost=11.20..11.20 rows=120 width=582) (actual time=9.039..9.041 rows=8 loops=1) | Buckets: 1024 Batches: 1 Memory Usage: 9kB | -> Seq Scan on stock s (cost=0.00..11.20 rows=120 width=582) (actual time=9.014..9.017 rows=8 loops=1) | Planning Time: 0.676 ms | JIT: | Functions: 11 | Options: Inlining false, Optimization false, Expressions true, Deforming true | Timing: Generation 0.753 ms (Deform 0.421 ms), Inlining 0.000 ms, Optimization 0.481 ms, Emission 8.545 ms, Total 9.779 ms | Execution Time: 1877.318 ms | }}} РЕЗУЛТАТ: побрз JOIN со stock табела значително намалено време за читање на историски податоци подобра перформанса за филтрирање по акција + време ~ 96-97% подобрување == 3. trade_transaction_detail_view ЧЕКОР 1: Анализа ПРЕД оптимизација {{{ EXPLAIN ANALYZE SELECT * FROM trade_transaction_detail_view; }}} {{{ QUERY PLAN | -------------------------------------------------------------------------------------------------------------------------------------+ Hash Join (cost=12.70..48189.20 rows=2000557 width=631) (actual time=0.057..771.499 rows=2000557 loops=1) | Hash Cond: (tt.stock_id = s.id) | -> Seq Scan on trade_transaction tt (cost=0.00..42739.57 rows=2000557 width=57) (actual time=0.026..221.291 rows=2000557 loops=1)| -> Hash (cost=11.20..11.20 rows=120 width=582) (actual time=0.018..0.019 rows=8 loops=1) | Buckets: 1024 Batches: 1 Memory Usage: 9kB | -> Seq Scan on stock s (cost=0.00..11.20 rows=120 width=582) (actual time=0.009..0.012 rows=8 loops=1) | Planning Time: 0.220 ms | Execution Time: 842.067 ms | }}} {{{ CREATE INDEX idx_trade_transaction_stock_id ON trade_transaction(stock_id); CREATE INDEX idx_trade_transaction_user_id ON trade_transaction(user_id); CREATE INDEX idx_trade_transaction_timestamp ON trade_transaction(timestamp); CREATE INDEX idx_trade_transaction_stock_time ON trade_transaction(stock_id, timestamp); EXPLAIN ANALYZE SELECT * FROM trade_transaction_detail_view; }}} stock_id - JOIN со stock, ја избегнува целосна проверка на 2M редови user_id - овозможува брзи user-based query филтри timestamp - подобрува временски опсег на пребарување (stock_id, timestamp) - најдобар за комбинирани филтри (акција + време) {{{ QUERY PLAN | -------------------------------------------------------------------------------------------------------------------------------------+ Hash Join (cost=12.70..48189.20 rows=2000557 width=631) (actual time=0.046..766.811 rows=2000557 loops=1) | Hash Cond: (tt.stock_id = s.id) | -> Seq Scan on trade_transaction tt (cost=0.00..42739.57 rows=2000557 width=57) (actual time=0.018..214.890 rows=2000557 loops=1)| -> Hash (cost=11.20..11.20 rows=120 width=582) (actual time=0.017..0.018 rows=8 loops=1) | Buckets: 1024 Batches: 1 Memory Usage: 9kB | -> Seq Scan on stock s (cost=0.00..11.20 rows=120 width=582) (actual time=0.009..0.011 rows=8 loops=1) | Planning Time: 0.645 ms | Execution Time: 837.658 ms | }}} РЕЗУЛТАТ: многу мала разлика во EXPLAIN