Changes between Version 3 and Version 4 of otherdevelopment


Ignore:
Timestamp:
05/07/26 16:20:18 (3 days ago)
Author:
233062
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • otherdevelopment

    v3 v4  
    99
    1010SQL:
    11 
    12 {{{
    13 SELECT
    14     fb.user_id,
    15     fb.username,
    16     fb.email,
    17     COALESCE(ai.income_count, 0) AS income_count,
    18     COALESCE(ai.annual_total_income, 0) AS annual_total_income,
    19     COALESCE(ma.active_months, 0) AS active_months,
    20     ROUND((COALESCE(ma.active_months, 0) / 12.0)::numeric, 4) AS activity_ratio,
    21     ROUND((COALESCE(fb.spending_budget, 0) / 100.0) * COALESCE(ai.annual_total_income, 0), 2) AS spending_amount,
    22     ROUND((COALESCE(fb.saving_budget, 0) / 100.0) * COALESCE(ai.annual_total_income, 0), 2) AS saving_amount,
    23     ROUND((COALESCE(fb.investing_budget, 0) / 100.0) * COALESCE(ai.annual_total_income, 0), 2) AS investing_amount,
    24     ROUND((COALESCE(fb.donation_budget, 0) / 100.0) * COALESCE(ai.annual_total_income, 0), 2) AS donation_amount,
    25     ROUND((COALESCE(fb.credit, 0) / 100.0) * COALESCE(ai.annual_total_income, 0), 2) AS credit_amount
    26 FROM finance_users fb
    27 LEFT JOIN (
    28     SELECT user_id, COUNT(*) AS income_count, SUM(amount) AS annual_total_income
    29     FROM incomes
    30     WHERE EXTRACT(YEAR FROM date) = 2026
    31     GROUP BY user_id
    32 ) ai ON ai.user_id = fb.user_id
    33 LEFT JOIN (
    34     SELECT user_id, COUNT(DISTINCT EXTRACT(MONTH FROM date)) AS active_months
    35     FROM incomes
    36     WHERE EXTRACT(YEAR FROM date) = 2026
    37     GROUP BY user_id
    38 ) ma ON ma.user_id = fb.user_id
    39 ORDER BY annual_total_income DESC;
    40 }}}
    41 Индекси:
    42 
    43 1.
    44 
    45 {{{
    46 idx_incomes_user_date
    47 CREATE INDEX idx_incomes_user_date ON incomes(user_id, date);
    48 }}}
    49 
    50 Подобрување:
    51 Index Scan наместо Seq Scan + подобро филтрирање по година
    52 
    53 2.
    54 {{{
    55 idx_incomes_year
    56 CREATE INDEX idx_incomes_year ON incomes ((EXTRACT(YEAR FROM date)));
    57 }}}
    58 
    59 Подобрување:
    60 Избегнува full table scan за годишен филтер
    61 
    62 3.
    63 {{{
    64 
    65 idx_incomes_covering
    66 CREATE INDEX idx_incomes_covering ON incomes(user_id, date, amount);
    67 
    68 }}}
    69 
    70 Подобрување:
    71 Direct lookup
    72 
    73 
    74 4.
    75 {{{
    76 CREATE UNIQUE INDEX users_pkey
    77 ON users(user_id);
    78 
    79 }}}
    80 
    81 Подобрување:
    82 Fast join
    83 
    84 5.
    85 {{{
    86 CREATE INDEX idx_incomes_covering
    87 ON incomes(user_id, date, amount);
    88 
    89 }}}
    90 
    91 Подобрување:
    92 Index-only scan (без пристап до табелата)
    93 
    94 ==SQL за анализа на брзината
    9511
    9612{{{
    … …  
    10824      SELECT
    10925          fb.user_id,
    110           COALESCE(ai.income_count, 0),
    111           COALESCE(ai.annual_total_income, 0),
    112           COALESCE(ma.active_months, 0)
     26          COALESCE(stats.income_count, 0),
     27          COALESCE(stats.annual_total_income, 0),
     28          COALESCE(stats.active_months, 0)
    11329      FROM finance_users fb
    11430      LEFT JOIN (
    115           SELECT user_id, COUNT(*), SUM(amount)
     31          -- Combined logic into one subquery for efficiency
     32          SELECT
     33              user_id,
     34              COUNT(*) AS income_count,
     35              SUM(amount) AS annual_total_income,
     36              COUNT(DISTINCT EXTRACT(MONTH FROM date)) AS active_months
    11637          FROM incomes
    11738          WHERE EXTRACT(YEAR FROM date) = 2026
    11839          GROUP BY user_id
    119       ) ai ON ai.user_id = fb.user_id
     40      ) stats ON stats.user_id = fb.user_id
     41  ) t;
     42
     43  end_time := clock_timestamp();
     44  duration := round(1000 * (extract(epoch FROM end_time) - extract(epoch FROM start_time)));
     45
     46  RAISE NOTICE 'Query executed in: % ms', duration;
     47END;
     48
     49}}}
     50Индекси:
     51
     521.
     53
     54{{{
     55idx_incomes_user_date
     56CREATE INDEX idx_incomes_user_date ON incomes(user_id, date);
     57}}}
     58
     59Подобрување:
     60Index Scan наместо Seq Scan + подобро филтрирање по година
     61
     622.
     63{{{
     64idx_incomes_year
     65CREATE INDEX idx_incomes_year ON incomes ((EXTRACT(YEAR FROM date)));
     66}}}
     67
     68Подобрување:
     69Избегнува full table scan за годишен филтер
     70
     713.
     72{{{
     73
     74idx_incomes_covering
     75CREATE INDEX idx_incomes_covering ON incomes(user_id, date, amount);
     76
     77}}}
     78
     79Подобрување:
     80Direct lookup
     81
     82
     834.
     84{{{
     85CREATE UNIQUE INDEX users_pkey
     86ON users(user_id);
     87
     88}}}
     89
     90Подобрување:
     91Fast join
     92
     935.
     94{{{
     95CREATE INDEX idx_incomes_covering
     96ON incomes(user_id, date, amount);
     97
     98}}}
     99
     100Подобрување:
     101Index-only scan (без пристап до табелата)
     102
     103==SQL за анализа на брзината
     104
     105{{{
     106CREATE OR REPLACE FUNCTION test_finance_report()
     107RETURNS void AS $$
     108DECLARE
     109  start_time timestamptz;
     110  end_time timestamptz;
     111  duration int;
     112BEGIN
     113  start_time := clock_timestamp();
     114
     115  PERFORM *
     116  FROM (
     117      SELECT
     118          fb.user_id,
     119          COALESCE(stats.income_count, 0),
     120          COALESCE(stats.annual_total_income, 0),
     121          COALESCE(stats.active_months, 0)
     122      FROM finance_users fb
    120123      LEFT JOIN (
    121           SELECT user_id, COUNT(DISTINCT EXTRACT(MONTH FROM date))
     124          -- Combined logic into one subquery for efficiency
     125          SELECT
     126              user_id,
     127              COUNT(*) AS income_count,
     128              SUM(amount) AS annual_total_income,
     129              COUNT(DISTINCT EXTRACT(MONTH FROM date)) AS active_months
    122130          FROM incomes
    123131          WHERE EXTRACT(YEAR FROM date) = 2026
    124132          GROUP BY user_id
    125       ) ma ON ma.user_id = fb.user_id
     133      ) stats ON stats.user_id = fb.user_id
    126134  ) t;
    127135
    … …  
    133141$$ LANGUAGE plpgsql;
    134142
     143
     144-- 4. PERFORMANCE RUNS
    135145-- run 1: no indexes
    136146SELECT test_finance_report();
    … …  
    141151SELECT test_finance_report();
    142152
    143 -- run 3: add idx_incomes_year
     153-- run 3: add idx_incomes_year (Expression Index)
    144154CREATE INDEX idx_incomes_year ON incomes ((EXTRACT(YEAR FROM date)));
    145155ANALYZE incomes;
    … …  
    151161SELECT test_finance_report();
    152162
     163-- Cleanup
    153164DROP FUNCTION test_finance_report();
    154165}}}