Анализата е направена со `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` ќе овозможат поефикасно филтрирање и поврзување на податоците во аналитичките извештаи.