Changes between Version 7 and Version 8 of otherdevelopment

Timestamp:

03/17/26 01:57:07 (2 days ago)

Author:

231020

Comment:

--

otherdevelopment

@@ -249 +249 @@
 '''SQL:'''
 {{{
-CREATE OR REPLACE FUNCTION test_stock_volatility()
-RETURNS void AS $$
-DECLARE
-  start_time timestamptz;
-  end_time   timestamptz;
-  duration   int;
-BEGIN
-  start_time := clock_timestamp();
-
-  PERFORM
-    sh.stock_id,
-    sh.price,
-    (sh.price - LAG(sh.price) OVER (
-        PARTITION BY sh.stock_id ORDER BY sh.timestamp))
-    / NULLIF(LAG(sh.price) OVER (
-        PARTITION BY sh.stock_id ORDER BY sh.timestamp), 0) AS daily_return
-  FROM stock_history sh
-  WHERE sh.timestamp >= '2024-01-01'
-    AND sh.timestamp <= '2024-12-31';
-
-  end_time := clock_timestamp();
-  duration := round(1000 * (extract(epoch FROM end_time)
-                           - extract(epoch FROM start_time)));
-  RAISE NOTICE 'Query executed in: % ms', duration;
-END;
-$$ LANGUAGE plpgsql;
-
 
 DROP INDEX IF EXISTS idx_sh_stock_id_timestamp;
@@ -282 +255 @@
 DROP INDEX IF EXISTS idx_tr_symbol_status;
 
--- run 1: no index
-SELECT test_stock_volatility();
-
--- run 2: + idx_sh_stock_id_timestamp (composite)
-CREATE INDEX idx_sh_stock_id_timestamp ON stock_history(stock_id, timestamp);
-ANALYZE stock_history;
-SELECT test_stock_volatility();
-
--- run 3: + idx_tx_stock_id_timestamp (composite)
-CREATE INDEX idx_tx_stock_id_timestamp ON transactions(stock_id, timestamp);
+EXPLAIN ANALYZE
+SELECT
+  sh.stock_id,
+  sh.price,
+  (sh.price - LAG(sh.price) OVER (
+      PARTITION BY sh.stock_id ORDER BY sh.timestamp))
+  / NULLIF(LAG(sh.price) OVER (
+      PARTITION BY sh.stock_id ORDER BY sh.timestamp), 0) AS daily_return
+FROM stock_history sh
+WHERE sh.timestamp >= '2024-01-01'
+  AND sh.timestamp <= '2024-12-31';
+
+QUERY PLAN                                                                                                                       |
+---------------------------------------------------------------------------------------------------------------------------------+
+WindowAgg  (cost=8773.08..9784.81 rows=36791 width=28) (actual time=41.592..66.876 rows=36600 loops=1)                           |
+  ->  Sort  (cost=8773.06..8865.03 rows=36791 width=20) (actual time=41.543..46.298 rows=36600 loops=1)                          |
+        Sort Key: stock_id, "timestamp"                                                                                          |
+        Sort Method: external merge  Disk: 1256kB                                                                                |
+        ->  Seq Scan on stock_history sh  (cost=0.00..4471.00 rows=36791 width=20) (actual time=0.060..22.078 rows=36600 loops=1)|
+              Filter: (("timestamp" >= '2024-01-01'::date) AND ("timestamp" <= '2024-12-31'::date))                              |
+              Rows Removed by Filter: 163400                                                                                     |
+Planning Time: 0.526 ms                                                                                                          |
+Execution Time: 69.235 ms                                                                                                        |
+
+}}}
+
+
+
+'''Индекси:'''
+
+
+---
+
+**1. transactions - Composite Index (stock_id, timestamp)**
+{{{
+CREATE INDEX idx_tx_stock_id_timestamp
+ON transactions(stock_id, timestamp);
+
 ANALYZE transactions;
-SELECT test_stock_volatility();
-
--- run 4: + idx_watchlist_stock_id
+EXPLAIN ANALYZE
+SELECT ...
+
+
+QUERY PLAN                                                                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
+WindowAgg  (cost=0.66..9098.75 rows=36545 width=28) (actual time=0.078..41.712 rows=36600 loops=1)                                                           |
+  ->  Index Scan using idx_sh_stock_id_timestamp on stock_history sh  (cost=0.42..8185.12 rows=36545 width=20) (actual time=0.066..17.591 rows=36600 loops=1)|
+        Index Cond: (("timestamp" >= '2024-01-01'::date) AND ("timestamp" <= '2024-12-31'::date))                                                            |
+Planning Time: 0.163 ms                                                                                                                                      |
+Execution Time: 43.629 ms                                                                                                                                    |
+
+}}}
+
+
+---
+
+**2. watchlist - Index (stock_id)**
+{{{
 CREATE INDEX idx_watchlist_stock_id ON watchlist(stock_id);
 ANALYZE watchlist;
-SELECT test_stock_volatility();
-
--- run 5: + idx_tr_symbol_status (composite)
-CREATE INDEX idx_tr_symbol_status ON trade_request(stock_symbol, status);
-ANALYZE trade_request;
-SELECT test_stock_volatility();
-
-DROP FUNCTION test_stock_volatility();
-}}}
-
-
-
-'''Индекси:'''
-
-**1. stock_history - Composite Index (stock_id, timestamp)**
-{{{
-CREATE INDEX idx_sh_stock_id_timestamp
-ON stock_history(stock_id, timestamp);
-}}}
-Користење: `PARTITION BY sh.stock_id ORDER BY sh.timestamp` во LAG прозорската функција и WHERE филтерот за дати
-
-Подобрување: Composite индексот ги покрива и партиционирањето и сортирањето -= елиминира Sort чекор
-
----
-
-**2. transactions - Composite Index (stock_id, timestamp)**
-{{{
-CREATE INDEX idx_tx_stock_id_timestamp
-ON transactions(stock_id, timestamp);
-}}}
-Користење: GROUP BY `t.stock_id` и WHERE на `timestamp` во `txn_stats` 
-
-Подобрување: Директен Index Scan наместо Seq Scan + Sort при временски филтрирани агрегации
-
----
-
-**3. watchlist - Index (stock_id)**
-{{{
-CREATE INDEX idx_watchlist_stock_id
-ON watchlist(stock_id);
-}}}
-Користење: GROUP BY `stock_id` во `watchlist_stats` 
-
-Подобрување: Index Scan при агрегација по акција наместо Seq Scan
-
----
-
-**4. trade_request - Composite Index (stock_symbol, status)**
-{{{
-CREATE INDEX idx_tr_symbol_status
-ON trade_request(stock_symbol, status);
-}}}
-Користење: JOIN `tr.stock_symbol = s.symbol` и WHERE `tr.status = 'PENDING'` во `pending_stats` 
-
-Подобрување: Composite индексот ги покрива и JOIN условот и WHERE филтерот истовремено
-
----
+EXPLAIN ANALYZE
+SELECT ...
+
+QUERY PLAN                                                                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
+WindowAgg  (cost=0.66..9098.75 rows=36545 width=28) (actual time=0.083..42.003 rows=36600 loops=1)                                                           |
+  ->  Index Scan using idx_sh_stock_id_timestamp on stock_history sh  (cost=0.42..8185.12 rows=36545 width=20) (actual time=0.070..17.564 rows=36600 loops=1)|
+        Index Cond: (("timestamp" >= '2024-01-01'::date) AND ("timestamp" <= '2024-12-31'::date))                                                            |
+Planning Time: 0.162 ms                                                                                                                                      |
+Execution Time: 43.946 ms                                                                                                                                    |
+}}}
+
+
 
 
 '''Заклучок:'''
 
-Без индекси: 102ms
-
-Со индекси: 40ms
-
-Имаме забрзување.
+Имаме забрзување од ~60%
 
 ----