Changes between Version 8 and Version 9 of otherdevelopment

Timestamp:

06/12/26 18:32:51 (3 days ago)

Author:

233062

Comment:

--

otherdevelopment

@@ -3 +3 @@
 == Анализа на перформанси ==
 
+=== Извештај за годишна тренинг активност на корисници ===
+
+Анализата на перформанси се врши врз основа на моменталната состојба во базата, која има податоци кои служат за тестирање. Базата содржи: ~1000 корисници, ~1000 тренинг сесии (training_sessions), ~900 тренинг профили.
+
+SQL:
+
+{{{
+CREATE OR REPLACE FUNCTION test_training_report()
+RETURNS void AS $$
+DECLARE
+  start_time timestamptz;
+  end_time   timestamptz;
+  duration   int;
+BEGIN
+  start_time := clock_timestamp();
+
+  PERFORM *
+  FROM (
+      WITH params AS (SELECT 2026::int AS report_year),
+      months AS (SELECT generate_series(1,12) AS month_no),
+      training_base AS (
+          SELECT tu.user_id FROM training_users tu
+      ),
+      monthly_sessions AS (
+          SELECT
+              tb.user_id,
+              m.month_no,
+              COUNT(ts.training_id)            AS sessions_count,
+              COALESCE(SUM(ts.duration), 0)    AS total_duration_minutes,
+              COALESCE(SUM(ts.calories), 0)    AS total_calories
+          FROM training_base tb
+          CROSS JOIN months m
+          LEFT JOIN training_sessions ts
+              ON ts.training_user_id = tb.user_id
+             AND EXTRACT(YEAR  FROM ts.date)::int = (SELECT report_year FROM params)
+             AND EXTRACT(MONTH FROM ts.date)::int = m.month_no
+          GROUP BY tb.user_id, m.month_no
+      )
+      SELECT user_id, SUM(sessions_count), SUM(total_calories)
+      FROM monthly_sessions
+      GROUP BY user_id
+  ) t;
+
+  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;
+}}}
+
+Индекси:
+
+1.
+
+{{{
+CREATE INDEX idx_training_sessions_user_date
+    ON training_sessions(training_user_id, date);
+}}}
+
+Подобрување:
+Index Scan наместо Seq Scan при LEFT JOIN по training_user_id + филтрирање по датум
+
+2.
+
+{{{
+CREATE INDEX idx_training_sessions_year
+    ON training_sessions ((EXTRACT(YEAR FROM date)));
+}}}
+
+Подобрување:
+Избегнува full table scan за годишен филтер со expression index
+
+3.
+
+{{{
+CREATE INDEX idx_training_sessions_covering
+    ON training_sessions(training_user_id, date, duration, calories);
+}}}
+
+Подобрување:
+Covering index — сите потребни колони за агрегатите (SUM duration, SUM calories, COUNT) се читаат директно од индексот без посета на heap страниците
+
+
+== SQL за анализа на брзината ==
+
+{{{
+CREATE OR REPLACE FUNCTION test_training_report()
+RETURNS void AS $$
+DECLARE
+  start_time timestamptz;
+  end_time   timestamptz;
+  duration   int;
+BEGIN
+  start_time := clock_timestamp();
+
+  PERFORM *
+  FROM (
+      WITH params AS (SELECT 2026::int AS report_year),
+      months AS (SELECT generate_series(1,12) AS month_no),
+      training_base AS (
+          SELECT tu.user_id FROM training_users tu
+      ),
+      monthly_sessions AS (
+          SELECT
+              tb.user_id,
+              m.month_no,
+              COUNT(ts.training_id)            AS sessions_count,
+              COALESCE(SUM(ts.duration), 0)    AS total_duration_minutes,
+              COALESCE(SUM(ts.calories), 0)    AS total_calories
+          FROM training_base tb
+          CROSS JOIN months m
+          LEFT JOIN training_sessions ts
+              ON ts.training_user_id = tb.user_id
+             AND EXTRACT(YEAR  FROM ts.date)::int = (SELECT report_year FROM params)
+             AND EXTRACT(MONTH FROM ts.date)::int = m.month_no
+          GROUP BY tb.user_id, m.month_no
+      )
+      SELECT user_id, SUM(sessions_count), SUM(total_calories)
+      FROM monthly_sessions
+      GROUP BY user_id
+  ) t;
+
+  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;
+
+
+-- 4. PERFORMANCE RUNS
+-- run 1: no indexes
+SELECT test_training_report();
+
+-- run 2: add idx_training_sessions_user_date
+CREATE INDEX idx_training_sessions_user_date ON training_sessions(training_user_id, date);
+ANALYZE training_sessions;
+SELECT test_training_report();
+
+-- run 3: add idx_training_sessions_year (Expression Index)
+CREATE INDEX idx_training_sessions_year ON training_sessions ((EXTRACT(YEAR FROM date)));
+ANALYZE training_sessions;
+SELECT test_training_report();
+
+-- run 4: add covering index
+CREATE INDEX idx_training_sessions_covering ON training_sessions(training_user_id, date, duration, calories);
+ANALYZE training_sessions;
+SELECT test_training_report();
+
+-- Cleanup
+DROP FUNCTION test_training_report();
+}}}
+
+Сумарно:
+Без индекси: ~67ms
+Со индекси: ~24ms
+Забрзување: ~2.8x
 
 === Извештај за годишна финансиска активност на корисници ===