Changes between Version 1 and Version 2 of otherdevelopment

Timestamp:

05/07/26 16:06:03 (3 days ago)

Author:

233062

Comment:

--

otherdevelopment

@@ -2 +2 @@
 
 == Анализа на перформанси ==
+
+
+=== Извештај за годишна финансиска активност на корисници ===
+
+Анализата на перформанси се врши врз основа на моменталната состојба во базата, која има податоци кои служат за тестирање. Базата содржи: ~1000 корисници, ~2000 приходи (incomes), ~1000 finance профили.
+
+SQL:
+
+{{{
+SELECT
+    fb.user_id,
+    fb.username,
+    fb.email,
+    COALESCE(ai.income_count, 0) AS income_count,
+    COALESCE(ai.annual_total_income, 0) AS annual_total_income,
+    COALESCE(ma.active_months, 0) AS active_months,
+    ROUND((COALESCE(ma.active_months, 0) / 12.0)::numeric, 4) AS activity_ratio,
+    ROUND((COALESCE(fb.spending_budget, 0) / 100.0) * COALESCE(ai.annual_total_income, 0), 2) AS spending_amount,
+    ROUND((COALESCE(fb.saving_budget, 0) / 100.0) * COALESCE(ai.annual_total_income, 0), 2) AS saving_amount,
+    ROUND((COALESCE(fb.investing_budget, 0) / 100.0) * COALESCE(ai.annual_total_income, 0), 2) AS investing_amount,
+    ROUND((COALESCE(fb.donation_budget, 0) / 100.0) * COALESCE(ai.annual_total_income, 0), 2) AS donation_amount,
+    ROUND((COALESCE(fb.credit, 0) / 100.0) * COALESCE(ai.annual_total_income, 0), 2) AS credit_amount
+FROM finance_users fb
+LEFT JOIN (
+    SELECT user_id, COUNT(*) AS income_count, SUM(amount) AS annual_total_income
+    FROM incomes
+    WHERE EXTRACT(YEAR FROM date) = 2026
+    GROUP BY user_id
+) ai ON ai.user_id = fb.user_id
+LEFT JOIN (
+    SELECT user_id, COUNT(DISTINCT EXTRACT(MONTH FROM date)) AS active_months
+    FROM incomes
+    WHERE EXTRACT(YEAR FROM date) = 2026
+    GROUP BY user_id
+) ma ON ma.user_id = fb.user_id
+ORDER BY annual_total_income DESC;
+}}}
+Индекси:
+
+1. 
+
+{{{
+idx_incomes_user_date
+CREATE INDEX idx_incomes_user_date ON incomes(user_id, date);
+}}}
+
+Подобрување:
+Index Scan наместо Seq Scan + подобро филтрирање по година
+
+2. 
+{{{
+idx_incomes_year
+CREATE INDEX idx_incomes_year ON incomes ((EXTRACT(YEAR FROM date)));
+}}}
+
+Подобрување:
+Избегнува full table scan за годишен филтер
+
+3.
+{{{
+
+idx_incomes_covering
+CREATE INDEX idx_incomes_covering ON incomes(user_id, date, amount);
+
+}}}
+
+Подобрување:
+Direct lookup
+
+
+4.
+{{{
+CREATE UNIQUE INDEX users_pkey 
+ON users(user_id);
+
+}}}
+
+Подобрување:
+Fast join
+
+5.
+{{{
+CREATE INDEX idx_incomes_covering 
+ON incomes(user_id, date, amount);
+
+}}}
+
+Подобрување:
+Index-only scan (без пристап до табелата)
+
+==SQL за анализа на брзината
+
+{{{
+CREATE OR REPLACE FUNCTION test_finance_report()
+RETURNS void AS $$
+DECLARE
+  start_time timestamptz;
+  end_time timestamptz;
+  duration int;
+BEGIN
+  start_time := clock_timestamp();
+
+  PERFORM *
+  FROM (
+      SELECT
+          fb.user_id,
+          COALESCE(ai.income_count, 0),
+          COALESCE(ai.annual_total_income, 0),
+          COALESCE(ma.active_months, 0)
+      FROM finance_users fb
+      LEFT JOIN (
+          SELECT user_id, COUNT(*), SUM(amount)
+          FROM incomes
+          WHERE EXTRACT(YEAR FROM date) = 2026
+          GROUP BY user_id
+      ) ai ON ai.user_id = fb.user_id
+      LEFT JOIN (
+          SELECT user_id, COUNT(DISTINCT EXTRACT(MONTH FROM date))
+          FROM incomes
+          WHERE EXTRACT(YEAR FROM date) = 2026
+          GROUP BY user_id
+      ) ma ON ma.user_id = fb.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;
+
+-- run 1: no indexes
+SELECT test_finance_report();
+
+-- run 2: add idx_incomes_user_date
+CREATE INDEX idx_incomes_user_date ON incomes(user_id, date);
+ANALYZE incomes;
+SELECT test_finance_report();
+
+-- run 3: add idx_incomes_year
+CREATE INDEX idx_incomes_year ON incomes ((EXTRACT(YEAR FROM date)));
+ANALYZE incomes;
+SELECT test_finance_report();
+
+-- run 4: add covering index
+CREATE INDEX idx_incomes_covering ON incomes(user_id, date, amount);
+ANALYZE incomes;
+SELECT test_finance_report();
+
+DROP FUNCTION test_finance_report();
+}}}
+
+Сумарно:
+Без индекси: ~61ms
+Со индекси: ~25ms
+Забрзување: ~2.4x
 
 == Безбедност и заштита ==