Changes between Initial Version and Version 1 of SmallDatabasePerformance

Timestamp:

06/27/26 12:57:22 (35 hours ago)

Author:

211101

Comment:

--

SmallDatabasePerformance

@@ +1 @@
+Анализата е направена со `EXPLAIN (ANALYZE, BUFFERS)`, при што се споредува планот за извршување пред и по додавање на дополнителните индекси. Во почетната состојба базата ги содржи само индексите кои PostgreSQL автоматски ги креира за `PRIMARY KEY` и `UNIQUE` ограничувања. Овие индекси не се третираат како дополнителна оптимизација, туку како дел од основната релациска структура на базата.
+
+Во моментот на тестирањето, базата ја има следната количина на податоци:
+
+||= Табела =||= Број на редови =||
+|| `user` || 3 ||
+|| `transaction` || 12 ||
+|| `transaction_account` || 5 ||
+|| `transaction_breakdown` || 11 ||
+|| `tag` || 11 ||
+|| `tag_assigned_to_transaction` || 16 ||
+
+Поради малата количина на податоци, очекувано е PostgreSQL во повеќе случаи да избере `Seq Scan`, бидејќи целосното читање на мала табела е поевтино од пристап преку индекс. Затоа, во анализата не се гледа само времето на извршување, туку и дали индексот навистина се користи во execution plan-от.
+
+==== Предложени дополнителни индекси ====
+
+Следните индекси се предложени затоа што колоните често се користат во `JOIN` и `WHERE` услови во аналитичките извештаи:
+
+{{{#!sql
+CREATE INDEX IF NOT EXISTS idx_transaction_date
+ON "transaction"(date);
+
+CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id
+ON transaction_account(user_id);
+
+CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
+ON transaction_breakdown(transaction_id);
+
+CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
+ON transaction_breakdown(transaction_account_id);
+
+CREATE INDEX IF NOT EXISTS idx_tag_assigned_transaction_id
+ON tag_assigned_to_transaction(transaction_id);
+
+CREATE INDEX IF NOT EXISTS idx_tag_assigned_tag_id
+ON tag_assigned_to_transaction(tag_id);
+}}}
+
+Индексот `idx_transaction_date` е наменет за извештаи кои филтрираат трансакции според временски период. Индексот `idx_transaction_account_user_id` е наменет за извештаи кои ги ограничуваат резултатите на конкретен корисник. Индексите на `transaction_breakdown` и `tag_assigned_to_transaction` се наменети за побрзо поврзување на трансакциите со сметки и тагови.
+
+==== Сценарио 1: Трендови на трошење според тагови за корисник ====
+
+'''Цел:'''
+Овој извештај ја прикажува потрошувачката по тагови за конкретен корисник во последните шест месеци. Прашалникот е комплексен затоа што ги поврзува табелите `tag_assigned_to_transaction`, `transaction`, `transaction_breakdown`, `transaction_account` и `tag`, а дополнително врши филтрирање по корисник и датум, групирање по таг и месец, и сумирање на потрошената сума.
+
+'''Анализиран SQL:'''
+
+{{{#!sql
+EXPLAIN (ANALYZE, BUFFERS)
+SELECT
+tg.tag_name,
+DATE_TRUNC('month', t.date) AS month,
+SUM(tb.spent_amount) AS total_spent
+FROM tag_assigned_to_transaction tat
+JOIN "transaction" t
+ON tat.transaction_id = t.transaction_id
+JOIN transaction_breakdown tb
+ON t.transaction_id = tb.transaction_id
+JOIN transaction_account ta
+ON tb.transaction_account_id = ta.transaction_account_id
+JOIN tag tg
+ON tat.tag_id = tg.tag_id
+WHERE
+ta.user_id = 1
+AND t.date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 MONTHS')
+GROUP BY
+tg.tag_name, month
+ORDER BY
+tg.tag_name, month;
+}}}
+
+'''Релевантни индекси за ова сценарио:'''
+
+{{{#!sql
+CREATE INDEX IF NOT EXISTS idx_transaction_date
+ON "transaction"(date);
+
+CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id
+ON transaction_account(user_id);
+
+CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
+ON transaction_breakdown(transaction_id);
+
+CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
+ON transaction_breakdown(transaction_account_id);
+
+CREATE INDEX IF NOT EXISTS idx_tag_assigned_transaction_id
+ON tag_assigned_to_transaction(transaction_id);
+
+CREATE INDEX IF NOT EXISTS idx_tag_assigned_tag_id
+ON tag_assigned_to_transaction(tag_id);
+}}}
+
+'''План пред додавање на индексите:'''
+
+{{{#!sql
+GroupAggregate  (cost=6.54..6.77 rows=9 width=53) (actual time=0.176..0.189 rows=7 loops=1)
+Group Key: tg.tag_name, (date_trunc('month'::text, t.date))
+Buffers: shared hit=5
+->  Sort  (cost=6.54..6.57 rows=9 width=25) (actual time=0.162..0.167 rows=16 loops=1)
+Sort Key: tg.tag_name, (date_trunc('month'::text, t.date))
+Sort Method: quicksort  Memory: 25kB
+->  Hash Join  (cost=4.99..6.40 rows=9 width=25) (actual time=0.119..0.142 rows=16 loops=1)
+Hash Cond: (tat.tag_id = tg.tag_id)
+->  Hash Join  (cost=3.74..5.10 rows=9 width=16) (actual time=0.086..0.099 rows=16 loops=1)
+Hash Cond: (tat.transaction_id = t.transaction_id)
+->  Hash Join  (cost=2.35..3.67 rows=10 width=16) (actual time=0.057..0.066 rows=16 loops=1)
+Hash Cond: (tat.transaction_id = tb.transaction_id)
+->  Seq Scan on tag_assigned_to_transaction tat
+->  Hash
+->  Hash Join
+Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
+->  Seq Scan on transaction_breakdown tb
+->  Hash
+->  Seq Scan on transaction_account ta
+Filter: (user_id = 1)
+Rows Removed by Filter: 2
+->  Hash
+->  Seq Scan on transaction t
+Filter: (date >= date_trunc('month'::text, (CURRENT_DATE - '6 mons'::interval)))
+->  Hash
+->  Seq Scan on tag tg
+Planning Time: 0.733 ms
+Execution Time: 0.296 ms
+}}}
+
+'''План по додавање на индексите:'''
+
+{{{#!sql
+GroupAggregate  (cost=6.54..6.77 rows=9 width=53) (actual time=0.154..0.166 rows=7 loops=1)
+Group Key: tg.tag_name, (date_trunc('month'::text, t.date))
+Buffers: shared hit=5
+->  Sort  (cost=6.54..6.57 rows=9 width=25) (actual time=0.142..0.146 rows=16 loops=1)
+Sort Key: tg.tag_name, (date_trunc('month'::text, t.date))
+Sort Method: quicksort  Memory: 25kB
+->  Hash Join  (cost=4.99..6.40 rows=9 width=25) (actual time=0.100..0.122 rows=16 loops=1)
+Hash Cond: (tat.tag_id = tg.tag_id)
+->  Hash Join  (cost=3.74..5.10 rows=9 width=16) (actual time=0.068..0.081 rows=16 loops=1)
+Hash Cond: (tat.transaction_id = t.transaction_id)
+->  Hash Join  (cost=2.35..3.67 rows=10 width=16) (actual time=0.040..0.049 rows=16 loops=1)
+Hash Cond: (tat.transaction_id = tb.transaction_id)
+->  Seq Scan on tag_assigned_to_transaction tat
+->  Hash
+->  Hash Join
+Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
+->  Seq Scan on transaction_breakdown tb
+->  Hash
+->  Seq Scan on transaction_account ta
+Filter: (user_id = 1)
+Rows Removed by Filter: 2
+->  Hash
+->  Seq Scan on transaction t
+Filter: (date >= date_trunc('month'::text, (CURRENT_DATE - '6 mons'::interval)))
+->  Hash
+->  Seq Scan on tag tg
+Planning Time: 0.921 ms
+Execution Time: 0.242 ms
+}}}
+
+'''Споредба:'''
+
+||= Метрика =||= Пред индекси =||= По индекси =||
+|| Planning Time || 0.733 ms || 0.921 ms ||
+|| Execution Time || 0.296 ms || 0.242 ms ||
+|| Тип на скенирање || `Seq Scan` || `Seq Scan` ||
+|| Дали новите индекси се користат || Не || Не ||
+
+'''Заклучок:'''
+Времето на извршување се намали од 0.296 ms на 0.242 ms, што претставува намалување од приближно 18.2%. Сепак, execution plan-от и по додавање на индексите користи `Seq Scan` над сите релевантни табели, а не `Index Scan` или `Bitmap Index Scan`. Затоа подобрувањето не може да се припише директно на индексите. Најверојатната причина е малата количина на податоци и фактот што сите блокови се веќе во shared buffers. Индексите остануваат логични за идно зголемување на бројот на трансакции, тагови и breakdown записи.
+
+==== Сценарио 2: Сметки со највисоко вкупно трошење во изминатата година ====
+
+'''Цел:'''
+Овој извештај ги прикажува сметките со најголема вкупна потрошувачка во последната година. Прашалникот користи `JOIN` помеѓу `transaction_breakdown`, `transaction` и `transaction_account`, филтрирање по датум, `SUM` агрегација, `GROUP BY`, `ORDER BY` и `LIMIT`.
+
+'''Анализиран SQL:'''
+
+{{{#!sql
+EXPLAIN (ANALYZE, BUFFERS)
+SELECT
+ta.account_name,
+COALESCE(SUM(tb.spent_amount), 0) AS total_spent
+FROM transaction_breakdown tb
+JOIN "transaction" t
+ON tb.transaction_id = t.transaction_id
+JOIN transaction_account ta
+ON tb.transaction_account_id = ta.transaction_account_id
+WHERE
+t.date >= NOW() - INTERVAL '1 YEAR'
+GROUP BY
+ta.account_name
+ORDER BY
+total_spent DESC
+LIMIT 10;
+}}}
+
+'''Релевантни индекси за ова сценарио:'''
+
+{{{#!sql
+CREATE INDEX IF NOT EXISTS idx_transaction_date
+ON "transaction"(date);
+
+CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
+ON transaction_breakdown(transaction_id);
+
+CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
+ON transaction_breakdown(transaction_account_id);
+}}}
+
+'''План пред додавање на индексите:'''
+
+{{{#!sql
+Limit  (cost=3.85..3.86 rows=5 width=40) (actual time=0.110..0.114 rows=3 loops=1)
+Buffers: shared hit=3
+->  Sort  (cost=3.85..3.86 rows=5 width=40) (actual time=0.108..0.111 rows=3 loops=1)
+Sort Key: (COALESCE(sum(tb.spent_amount), '0'::numeric)) DESC
+Sort Method: quicksort  Memory: 25kB
+->  HashAggregate  (cost=3.73..3.79 rows=5 width=40) (actual time=0.097..0.101 rows=3 loops=1)
+Group Key: ta.account_name
+->  Hash Join  (cost=2.47..3.67 rows=11 width=12) (actual time=0.059..0.069 rows=11 loops=1)
+Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
+->  Hash Join  (cost=1.36..2.51 rows=11 width=8) (actual time=0.032..0.038 rows=11 loops=1)
+Hash Cond: (tb.transaction_id = t.transaction_id)
+->  Seq Scan on transaction_breakdown tb
+->  Hash
+->  Seq Scan on transaction t
+Filter: (date >= (now() - '1 year'::interval))
+->  Hash
+->  Seq Scan on transaction_account ta
+Planning Time: 0.403 ms
+Execution Time: 0.191 ms
+}}}
+
+'''План по додавање на индексите:'''
+
+{{{#!sql
+Limit  (cost=3.85..3.86 rows=5 width=40) (actual time=0.100..0.104 rows=3 loops=1)
+Buffers: shared hit=3
+->  Sort  (cost=3.85..3.86 rows=5 width=40) (actual time=0.098..0.101 rows=3 loops=1)
+Sort Key: (COALESCE(sum(tb.spent_amount), '0'::numeric)) DESC
+Sort Method: quicksort  Memory: 25kB
+->  HashAggregate  (cost=3.73..3.79 rows=5 width=40) (actual time=0.086..0.090 rows=3 loops=1)
+Group Key: ta.account_name
+->  Hash Join  (cost=2.47..3.67 rows=11 width=12) (actual time=0.060..0.070 rows=11 loops=1)
+Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
+->  Hash Join  (cost=1.36..2.51 rows=11 width=8) (actual time=0.030..0.036 rows=11 loops=1)
+Hash Cond: (tb.transaction_id = t.transaction_id)
+->  Seq Scan on transaction_breakdown tb
+->  Hash
+->  Seq Scan on transaction t
+Filter: (date >= (now() - '1 year'::interval))
+->  Hash
+->  Seq Scan on transaction_account ta
+Planning Time: 0.451 ms
+Execution Time: 0.166 ms
+}}}
+
+'''Споредба:'''
+
+||= Метрика =||= Пред индекси =||= По индекси =||
+|| Planning Time || 0.403 ms || 0.451 ms ||
+|| Execution Time || 0.191 ms || 0.166 ms ||
+|| Тип на скенирање || `Seq Scan` || `Seq Scan` ||
+|| Дали новите индекси се користат || Не || Не ||
+
+'''Заклучок:'''
+Времето на извршување се намали од 0.191 ms на 0.166 ms, што претставува намалување од приближно 13.1%. Сепак, планот за извршување не користи ниту еден од новите индекси. PostgreSQL продолжува со `Seq Scan` на `transaction`, `transaction_breakdown` и `transaction_account`, затоа што табелите имаат многу мал број редови и читањето на целата табела е поевтино од пристап преку индекс. Поради тоа, малото подобрување не се смета како директен ефект од индексите. Индексот на `transaction.date` сепак е оправдан за идно зголемување на бројот на трансакции, бидејќи извештајот филтрира според временски период.
+
+==== Сценарио 3: Годишни трендови на трансакции за корисник ====
+
+'''Цел:'''
+Овој извештај ја прикажува потрошувачката по квартали за конкретен корисник во тековната година. Прашалникот користи `JOIN` помеѓу `transaction_breakdown`, `transaction` и `transaction_account`, филтрирање по корисник и временски период, `SUM` агрегации со `CASE WHEN`, `GROUP BY` и `ORDER BY`.
+
+Во анализата условот за година е напишан како временски опсег, наместо само со `EXTRACT(YEAR FROM t.date)`, за B-Tree индексот на `date` да може поефикасно да се користи кога табелата ќе порасне.
+
+'''Анализиран SQL:'''
+
+{{{#!sql
+EXPLAIN (ANALYZE, BUFFERS)
+SELECT
+ta.account_name,
+SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
+SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
+SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
+SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
+FROM transaction_breakdown tb
+JOIN "transaction" t
+ON tb.transaction_id = t.transaction_id
+JOIN transaction_account ta
+ON tb.transaction_account_id = ta.transaction_account_id
+WHERE
+ta.user_id = 1
+AND t.date >= DATE_TRUNC('year', CURRENT_DATE)
+AND t.date < DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 YEAR'
+GROUP BY
+ta.account_name
+ORDER BY
+ta.account_name;
+}}}
+
+'''Релевантни индекси за ова сценарио:'''
+
+{{{#!sql
+CREATE INDEX IF NOT EXISTS idx_transaction_date
+ON "transaction"(date);
+
+CREATE INDEX IF NOT EXISTS idx_transaction_account_user_id
+ON transaction_account(user_id);
+
+CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_transaction_id
+ON transaction_breakdown(transaction_id);
+
+CREATE INDEX IF NOT EXISTS idx_transaction_breakdown_account_id
+ON transaction_breakdown(transaction_account_id);
+}}}
+
+'''План пред додавање на индексите:'''
+
+{{{#!sql
+Sort  (cost=4.16..4.17 rows=3 width=136) (actual time=0.141..0.144 rows=3 loops=1)
+Sort Key: ta.account_name
+Sort Method: quicksort  Memory: 25kB
+Buffers: shared hit=3
+->  HashAggregate  (cost=4.08..4.14 rows=3 width=136) (actual time=0.126..0.131 rows=3 loops=1)
+Group Key: ta.account_name
+->  Hash Join  (cost=2.35..3.85 rows=7 width=20) (actual time=0.070..0.089 rows=11 loops=1)
+Hash Cond: (t.transaction_id = tb.transaction_id)
+->  Seq Scan on transaction t
+Filter: ((date >= date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone))
+AND (date < (date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) + '1 year'::interval)))
+->  Hash
+->  Hash Join
+Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
+->  Seq Scan on transaction_breakdown tb
+->  Hash
+->  Seq Scan on transaction_account ta
+Filter: (user_id = 1)
+Rows Removed by Filter: 2
+Planning Time: 0.402 ms
+Execution Time: 0.242 ms
+}}}
+
+'''План по додавање на индексите:'''
+
+{{{#!sql
+Sort  (cost=4.16..4.17 rows=3 width=136) (actual time=0.131..0.135 rows=3 loops=1)
+Sort Key: ta.account_name
+Sort Method: quicksort  Memory: 25kB
+Buffers: shared hit=3
+->  HashAggregate  (cost=4.08..4.14 rows=3 width=136) (actual time=0.117..0.122 rows=3 loops=1)
+Group Key: ta.account_name
+->  Hash Join  (cost=2.35..3.85 rows=7 width=20) (actual time=0.063..0.082 rows=11 loops=1)
+Hash Cond: (t.transaction_id = tb.transaction_id)
+->  Seq Scan on transaction t
+Filter: ((date >= date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone))
+AND (date < (date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone) + '1 year'::interval)))
+->  Hash
+->  Hash Join
+Hash Cond: (tb.transaction_account_id = ta.transaction_account_id)
+->  Seq Scan on transaction_breakdown tb
+->  Hash
+->  Seq Scan on transaction_account ta
+Filter: (user_id = 1)
+Rows Removed by Filter: 2
+Planning Time: 0.500 ms
+Execution Time: 0.212 ms
+}}}
+
+'''Споредба:'''
+
+||= Метрика =||= Пред индекси =||= По индекси =||
+|| Planning Time || 0.402 ms || 0.500 ms ||
+|| Execution Time || 0.242 ms || 0.212 ms ||
+|| Тип на скенирање || `Seq Scan` || `Seq Scan` ||
+|| Дали новите индекси се користат || Не || Не ||
+
+'''Заклучок:'''
+Времето на извршување се намали од 0.242 ms на 0.212 ms, што претставува намалување од приближно 12.4%. Сепак, планот останува ист во однос на пристапот до табелите: PostgreSQL користи `Seq Scan`, а не новите индекси. Причината е малата количина на податоци: `transaction` има 12 редови, `transaction_breakdown` има 11 редови, а `transaction_account` има 5 редови. За ваква големина, секвенцијалното читање е поевтино од пристап преку индекс. Индексите сепак се соодветни за идно зголемување на базата, особено затоа што извештајот филтрира по `user_id` и по временски период.
+
+==== Финален заклучок од анализата ====
+
+Во сите три сценарија, PostgreSQL продолжи да користи `Seq Scan` и по додавање на предложените индекси. Тоа значи дека индексите не беа реално искористени во моменталниот execution plan.
+
+Ова однесување е очекувано поради малата количина на податоци во тест базата. Во моментот на тестирањето, најголемите релевантни табели имаат само 12 трансакции, 11 breakdown записи и 16 tag-assignment записи. За вакви табели, PostgreSQL правилно проценува дека целосно скенирање на табелата е поевтино од користење индекс.
+
+Иако execution time се намали во сите три сценарија, тоа не може директно да се припише на индексите, бидејќи execution plan-от не покажува `Index Scan`, `Bitmap Index Scan` или `Index Only Scan`. Малите разлики во времето најверојатно се резултат на кеширање, повторно користење на податоци во shared buffers и нормална варијација при извршување.
+
+Сепак, предложените индекси остануваат оправдани како подготовка за поголема количина на податоци. Со раст на бројот на корисници, трансакции, сметки, тагови и breakdown записи, индексите на `transaction.date`, `transaction_account.user_id`, `transaction_breakdown.transaction_id`, `transaction_breakdown.transaction_account_id`, `tag_assigned_to_transaction.transaction_id` и `tag_assigned_to_transaction.tag_id` ќе овозможат поефикасно филтрирање и поврзување на податоците во аналитичките извештаи.