Анализата е направена со 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 услови во аналитичките извештаи:

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:

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;

Релевантни индекси за ова сценарио:

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);

План пред додавање на индексите:

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

План по додавање на индексите:

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:

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;

Релевантни индекси за ова сценарио:

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);

План пред додавање на индексите:

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

План по додавање на индексите:

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:

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;

Релевантни индекси за ова сценарио:

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);

План пред додавање на индексите:

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

План по додавање на индексите:

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