| 12 | | Во моментот на тестирањето, базата ја има следната количина на податоци: |
| | 12 | Првично тестирање беше направено со мал број редови во табелите: |
| | 13 | |
| | 14 | [wiki:SmallDatabasePerformance Перформанси со мала датабаза] |
| | 15 | |
| | 16 | Во оваа состојба PostgreSQL користеше `Seq Scan` и по додавање на индексите. Тоа е очекувано однесување, бидејќи за табели со многу мал број редови целосното читање на табелата е поевтино од пристап преку индекс. |
| | 17 | |
| | 18 | Поради тоа, за пореална анализа беше генериран поголем сет на тест-податоци, при што податоците се логички поврзани преку постоечките релации во базата. |
| | 19 | |
| | 20 | По генерирање на тест-податоците, базата ја има следната состојба: |
| 15 | | || `user` || |
| 16 | | || `transaction` || 1 |
| 17 | | || `transaction_account` || 5 |
| 18 | | || `transaction_breakdown` || 11 |
| 19 | | || `tag` || 11 || |
| 20 | | || `tag_assigned_to_transaction` || 1 |
| 21 | | |
| 22 | | Поради малата количина на податоци, очекувано е PostgreSQL во повеќе случаи да избере `Seq Scan`, бидејќи целосното читање на мала табела е поевтино од пристап преку индекс. Затоа, во анализата не се гледа само времето на извршување, туку и дали индексот навистина се користи во execution plan-от. |
| | 23 | || `user` || 103 || |
| | 24 | || `transaction` || 100012 || |
| | 25 | || `transaction_account` || 505 || |
| | 26 | || `transaction_breakdown` || 110011 || |
| | 27 | || `tag` || 31 || |
| | 28 | || `tag_assigned_to_transaction` || 125016 || |
| | 29 | |
| | 30 | Оваа количина на податоци е доволна за PostgreSQL optimizer-от да започне да прави различни планови за извршување и да користи дел од предложените индекси кога тие се поисплатливи од `Seq Scan`. |
| 144 | | Sort Method: quicksort Memory: 25kB |
| 145 | | -> Hash Join (cost=4.99..6.40 rows=9 width=25) (actual time=0.100..0.122 rows=16 loops=1) |
| 146 | | Hash Cond: (tat.tag_id = tg.tag_id) |
| 147 | | -> Hash Join (cost=3.74..5.10 rows=9 width=16) (actual time=0.068..0.081 rows=16 loops=1) |
| 148 | | Hash Cond: (tat.transaction_id = t.transaction_id) |
| 149 | | -> Hash Join (cost=2.35..3.67 rows=10 width=16) (actual time=0.040..0.049 rows=16 loops=1) |
| 150 | | Hash Cond: (tat.transaction_id = tb.transaction_id) |
| 151 | | -> Seq Scan on tag_assigned_to_transaction tat |
| 152 | | -> Hash |
| 153 | | -> Hash Join |
| 154 | | Hash Cond: (tb.transaction_account_id = ta.transaction_account_id) |
| 155 | | -> Seq Scan on transaction_breakdown tb |
| 156 | | -> Hash |
| 157 | | -> Seq Scan on transaction_account ta |
| 158 | | Filter: (user_id = 1) |
| 159 | | Rows Removed by Filter: 2 |
| 160 | | -> Hash |
| 161 | | -> Seq Scan on transaction t |
| | 153 | Sort Method: quicksort Memory: 28kB |
| | 154 | -> Nested Loop (cost=16.95..2486.96 rows=97 width=26) (actual time=0.155..6.835 rows=81 loops=1) |
| | 155 | -> Nested Loop (cost=16.80..2480.67 rows=97 width=18) (actual time=0.135..6.624 rows=81 loops=1) |
| | 156 | -> Nested Loop (cost=16.38..2443.39 rows=77 width=22) (actual time=0.104..5.104 rows=81 loops=1) |
| | 157 | -> Nested Loop (cost=16.09..2050.95 rows=1089 width=10) (actual time=0.087..0.458 rows=1000 loops=1) |
| | 158 | -> Bitmap Heap Scan on transaction_account ta |
| | 159 | Recheck Cond: (user_id = 5) |
| | 160 | -> Bitmap Index Scan on idx_transaction_account_user_id |
| | 161 | Index Cond: (user_id = 5) |
| | 162 | -> Bitmap Heap Scan on transaction_breakdown tb |
| | 163 | Recheck Cond: (transaction_account_id = ta.transaction_account_id) |
| | 164 | -> Bitmap Index Scan on idx_transaction_breakdown_account_id |
| | 165 | Index Cond: (transaction_account_id = ta.transaction_account_id) |
| | 166 | -> Index Scan using transaction_pkey on transaction t |
| | 167 | Index Cond: (transaction_id = tb.transaction_id) |
| 172 | | || Planning Time || 0.733 ms || 0.921 ms || |
| 173 | | || Execution Time || 0.296 ms || 0.242 ms || |
| 174 | | || Тип на скенирање || `Seq Scan` || `Seq Scan` || |
| 175 | | || Дали новите индекси се користат || Не || Не || |
| | 182 | || Planning Time || 1.392 ms || 1.738 ms || |
| | 183 | || Execution Time || 51.744 ms || 7.055 ms || |
| | 184 | || Подобрување на Execution Time || / || ~86.36% || |
| | 185 | || Забрзување || / || ~7.33x || |
| | 186 | || Тип на скенирање пред индекси || `Seq Scan` на големите табели || / || |
| | 187 | || Тип на скенирање по индекси || / || `Bitmap Index Scan`, `Bitmap Heap Scan`, `Index Scan` || |
| | 188 | || Дали новите индекси се користат || Не || Да || |
| | 189 | |
| | 190 | По додавање на индексите, PostgreSQL експлицитно ги користи: |
| | 191 | |
| | 192 | * `idx_transaction_account_user_id` преку `Bitmap Index Scan` |
| | 193 | * `idx_transaction_breakdown_account_id` преку `Bitmap Index Scan` |
| | 194 | * `idx_tag_assigned_transaction_id` преку `Index Scan` |
| | 195 | |
| | 196 | Индексот `idx_transaction_date` не е директно искористен во овој план, затоа што optimizer-от прво ги ограничува податоците преку корисничките сметки и breakdown записите, а потоа пристапува до трансакциите преку примарниот клуч `transaction_pkey`. |
| 239 | | Planning Time: 0.403 ms |
| 240 | | Execution Time: 0.191 ms |
| 241 | | }}} |
| 242 | | |
| 243 | | '''План по додавање на индексите:''' |
| 244 | | |
| 245 | | {{{#!sql |
| 246 | | Limit (cost=3.85..3.86 rows=5 width=40) (actual time=0.100..0.104 rows=3 loops=1) |
| 247 | | Buffers: shared hit=3 |
| 248 | | -> Sort (cost=3.85..3.86 rows=5 width=40) (actual time=0.098..0.101 rows=3 loops=1) |
| | 263 | Planning Time: 0.538 ms |
| | 264 | Execution Time: 73.792 ms |
| | 265 | }}} |
| | 266 | |
| | 267 | '''По додавање на индексите:''' |
| | 268 | |
| | 269 | {{{#!sql |
| | 270 | Limit (cost=3675.91..3675.93 rows=10 width=49) (actual time=47.820..47.826 rows=10 loops=1) |
| | 271 | Buffers: shared hit=1540 read=36 |
| | 272 | -> Sort (cost=3675.91..3677.17 rows=505 width=49) (actual time=47.818..47.822 rows=10 loops=1) |
| 270 | | || Planning Time || 0.403 ms || 0.451 ms || |
| 271 | | || Execution Time || 0.191 ms || 0.166 ms || |
| 272 | | || Тип на скенирање || `Seq Scan` || `Seq Scan` || |
| 273 | | || Дали новите индекси се користат || Не || Не || |
| | 298 | || Planning Time || 0.538 ms || 0.654 ms || |
| | 299 | || Execution Time || 73.792 ms || 47.902 ms || |
| | 300 | || Подобрување на Execution Time || / || ~35.08% || |
| | 301 | || Забрзување || / || ~1.54x || |
| | 302 | || Тип на скенирање пред индекси || `Seq Scan` на `transaction` || / || |
| | 303 | || Тип на скенирање по индекси || / || `Bitmap Index Scan` + `Bitmap Heap Scan` на `transaction` || |
| | 304 | || Дали новите индекси се користат || Не || Да || |
| | 305 | |
| | 306 | По додавање на индексите, PostgreSQL го користи `idx_transaction_date` преку: |
| | 307 | |
| | 308 | {{{#!sql |
| | 309 | Bitmap Index Scan on idx_transaction_date |
| | 310 | Index Cond: (date >= (now() - '1 year'::interval)) |
| | 311 | }}} |
| | 312 | |
| | 313 | Останатите табели, како `transaction_breakdown` и `transaction_account`, сè уште се читаат со `Seq Scan`, затоа што query-то пресметува агрегат за сите сметки, а не за еден конкретен корисник или една конкретна сметка. |
| 276 | | Времето на извршување се намали од 0.191 ms на 0.166 ms, што претставува намалување од приближно 13.1%. Сепак, планот за извршување не користи ниту еден од новите индекси. PostgreSQL продолжува со `Seq Scan` на `transaction`, `transaction_breakdown` и `transaction_account`, затоа што табелите имаат многу мал број редови и читањето на целата табела е поевтино од пристап преку индекс. Поради тоа, малото подобрување не се смета како директен ефект од индексите. Индексот на `transaction.date` сепак е оправдан за идно зголемување на бројот на трансакции, бидејќи извештајот филтрира според временски период. |
| | 316 | |
| | 317 | Времето на извршување се намали од 73.792 ms на 47.902 ms, што претставува приближно 35.08% подобрување. Ова подобрување е директно поврзано со индексот `idx_transaction_date`, бидејќи извештајот филтрира трансакции само од последната година. Пред индексот, PostgreSQL мораше да ја чита целата табела `transaction` и да отфрли 87672 редови преку filter. По додавање на индексот, PostgreSQL користи `Bitmap Index Scan` за побрзо да ги најде трансакциите од последната година. |
| 331 | | Sort Method: quicksort Memory: 25kB |
| 332 | | Buffers: shared hit=3 |
| 333 | | -> HashAggregate (cost=4.08..4.14 rows=3 width=136) (actual time=0.126..0.131 rows=3 loops=1) |
| 334 | | Group Key: ta.account_name |
| 335 | | -> Hash Join (cost=2.35..3.85 rows=7 width=20) (actual time=0.070..0.089 rows=11 loops=1) |
| 336 | | Hash Cond: (t.transaction_id = tb.transaction_id) |
| 337 | | -> Seq Scan on transaction t |
| | 375 | Sort Method: quicksort Memory: 28kB |
| | 376 | -> Nested Loop (cost=11.67..2509.73 rows=67 width=31) (actual time=0.322..26.308 rows=70 loops=1) |
| | 377 | -> Hash Join (cost=11.38..2103.69 rows=1089 width=27) (actual time=0.301..21.727 rows=1000 loops=1) |
| | 378 | Hash Cond: (tb.transaction_account_id = ta.transaction_account_id) |
| | 379 | -> Seq Scan on transaction_breakdown tb |
| | 380 | (actual time=0.019..8.175 rows=110011 loops=1) |
| | 381 | -> Hash |
| | 382 | -> Seq Scan on transaction_account ta |
| | 383 | Filter: (user_id = 5) |
| | 384 | Rows Removed by Filter: 500 |
| | 385 | -> Index Scan using transaction_pkey on transaction t |
| | 386 | Index Cond: (transaction_id = tb.transaction_id) |
| 340 | | -> Hash |
| 341 | | -> Hash Join |
| 342 | | Hash Cond: (tb.transaction_account_id = ta.transaction_account_id) |
| 343 | | -> Seq Scan on transaction_breakdown tb |
| 344 | | -> Hash |
| 345 | | -> Seq Scan on transaction_account ta |
| 346 | | Filter: (user_id = 1) |
| 347 | | Rows Removed by Filter: 2 |
| 348 | | Planning Time: 0.402 ms |
| 349 | | Execution Time: 0.242 ms |
| 350 | | }}} |
| 351 | | |
| 352 | | '''План по додавање на индексите:''' |
| 353 | | |
| 354 | | {{{#!sql |
| 355 | | Sort (cost=4.16..4.17 rows=3 width=136) (actual time=0.131..0.135 rows=3 loops=1) |
| | 389 | Rows Removed by Filter: 1 |
| | 390 | Planning Time: 0.495 ms |
| | 391 | Execution Time: 26.571 ms |
| | 392 | }}} |
| | 393 | |
| | 394 | '''По додавање на индексите:''' |
| | 395 | |
| | 396 | {{{#!sql |
| | 397 | GroupAggregate (cost=2458.99..2461.40 rows=5 width=145) (actual time=5.086..5.088 rows=1 loops=1) |
| | 398 | Group Key: ta.account_name |
| | 399 | Buffers: shared hit=3023 |
| | 400 | -> Sort (cost=2458.99..2459.16 rows=66 width=31) (actual time=4.954..4.960 rows=70 loops=1) |
| 357 | | Sort Method: quicksort Memory: 25kB |
| 358 | | Buffers: shared hit=3 |
| 359 | | -> HashAggregate (cost=4.08..4.14 rows=3 width=136) (actual time=0.117..0.122 rows=3 loops=1) |
| 360 | | Group Key: ta.account_name |
| 361 | | -> Hash Join (cost=2.35..3.85 rows=7 width=20) (actual time=0.063..0.082 rows=11 loops=1) |
| 362 | | Hash Cond: (t.transaction_id = tb.transaction_id) |
| 363 | | -> Seq Scan on transaction t |
| | 402 | Sort Method: quicksort Memory: 28kB |
| | 403 | -> Nested Loop (cost=16.38..2457.00 rows=66 width=31) (actual time=0.104..4.917 rows=70 loops=1) |
| | 404 | -> Nested Loop (cost=16.09..2050.95 rows=1089 width=27) (actual time=0.086..0.420 rows=1000 loops=1) |
| | 405 | -> Bitmap Heap Scan on transaction_account ta |
| | 406 | Recheck Cond: (user_id = 5) |
| | 407 | -> Bitmap Index Scan on idx_transaction_account_user_id |
| | 408 | Index Cond: (user_id = 5) |
| | 409 | -> Bitmap Heap Scan on transaction_breakdown tb |
| | 410 | Recheck Cond: (transaction_account_id = ta.transaction_account_id) |
| | 411 | -> Bitmap Index Scan on idx_transaction_breakdown_account_id |
| | 412 | Index Cond: (transaction_account_id = ta.transaction_account_id) |
| | 413 | -> Index Scan using transaction_pkey on transaction t |
| | 414 | Index Cond: (transaction_id = tb.transaction_id) |
| 381 | | || Planning Time || 0.402 ms || 0.500 ms || |
| 382 | | || Execution Time || 0.242 ms || 0.212 ms || |
| 383 | | || Тип на скенирање || `Seq Scan` || `Seq Scan` || |
| 384 | | || Дали новите индекси се користат || Не || Не || |
| | 425 | || Planning Time || 0.495 ms || 0.724 ms || |
| | 426 | || Execution Time || 26.571 ms || 5.169 ms || |
| | 427 | || Подобрување на Execution Time || / || ~80.55% || |
| | 428 | || Забрзување || / || ~5.14x || |
| | 429 | || Тип на скенирање пред индекси || `Seq Scan` на `transaction_breakdown` и `transaction_account` || / || |
| | 430 | || Тип на скенирање по индекси || / || `Bitmap Index Scan`, `Bitmap Heap Scan`, `Index Scan` || |
| | 431 | || Дали новите индекси се користат || Не || Да || |
| | 432 | |
| | 433 | По додавање на индексите, PostgreSQL ги користи: |
| | 434 | |
| | 435 | * `idx_transaction_account_user_id` преку `Bitmap Index Scan` |
| | 436 | * `idx_transaction_breakdown_account_id` преку `Bitmap Index Scan` |
| | 437 | |
| | 438 | Индексот `idx_transaction_date` не е директно искористен во овој план. Причината е што optimizer-от прво ги ограничува податоците преку конкретниот `user_id` и неговите сметки, а потоа пристапува до трансакциите преку `transaction_pkey`. Во овој случај филтрирањето по корисник е поселективно од самото филтрирање по датум. |
| 387 | | Времето на извршување се намали од 0.242 ms на 0.212 ms, што претставува намалување од приближно 12.4%. Сепак, планот останува ист во однос на пристапот до табелите: PostgreSQL користи `Seq Scan`, а не новите индекси. Причината е малата количина на податоци: `transaction` има 12 редови, `transaction_breakdown` има 11 редови, а `transaction_account` има 5 редови. За ваква големина, секвенцијалното читање е поевтино од пристап преку индекс. Индексите сепак се соодветни за идно зголемување на базата, особено затоа што извештајот филтрира по `user_id` и по временски период. |
| | 441 | |
| | 442 | Времето на извршување се намали од 26.571 ms на 5.169 ms, што претставува приближно 80.55% подобрување, односно околу 5.14 пати побрзо извршување. Ова подобрување е резултат на тоа што PostgreSQL повеќе не ја чита целата табела `transaction_breakdown` и целата табела `transaction_account`, туку директно ги наоѓа сметките на конкретниот корисник и breakdown записите поврзани со тие сметки. |
| 391 | | Во сите три сценарија, PostgreSQL продолжи да користи `Seq Scan` и по додавање на предложените индекси. Тоа значи дека индексите не беа реално искористени во моменталниот execution plan. |
| 392 | | |
| 393 | | Ова однесување е очекувано поради малата количина на податоци во тест базата. Во моментот на тестирањето, најголемите релевантни табели имаат само 12 трансакции, 11 breakdown записи и 16 tag-assignment записи. За вакви табели, PostgreSQL правилно проценува дека целосно скенирање на табелата е поевтино од користење индекс. |
| 394 | | |
| 395 | | Иако execution time се намали во сите три сценарија, тоа не може директно да се припише на индексите, бидејќи execution plan-от не покажува `Index Scan`, `Bitmap Index Scan` или `Index Only Scan`. Малите разлики во времето најверојатно се резултат на кеширање, повторно користење на податоци во shared buffers и нормална варијација при извршување. |
| 396 | | |
| 397 | | Сепак, предложените индекси остануваат оправдани како подготовка за поголема количина на податоци. Со раст на бројот на корисници, трансакции, сметки, тагови и 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` ќе овозможат поефикасно филтрирање и поврзување на податоците во аналитичките извештаи. |
| | 446 | Првичната анализа со мал број податоци не покажа реална употреба на индексите, затоа што табелите беа премногу мали и PostgreSQL правилно избра `Seq Scan`. Поради тоа беше генериран поголем, логички поврзан сет на тест-податоци, кој содржи 100012 трансакции, 110011 breakdown записи и 125016 записи за доделени тагови. |
| | 447 | |
| | 448 | По зголемување на количината на податоци, индексите почнаа да имаат значајно влијание врз execution plan-от и времето на извршување. |
| | 449 | |
| | 450 | ||= Сценарио =||= Execution Time пред индекси =||= Execution Time по индекси =||= Подобрување =||= Индекси искористени =|| |
| | 451 | || Трендови на трошење според тагови за корисник || 51.744 ms || 7.055 ms || ~86.36% || Да || |
| | 452 | || Сметки со највисоко трошење во изминатата година || 73.792 ms || 47.902 ms || ~35.08% || Да || |
| | 453 | || Годишни трендови на трансакции за корисник || 26.571 ms || 5.169 ms || ~80.55% || Да || |
| | 454 | |
| | 455 | Најголемо подобрување се забележува кај извештаите кои филтрираат по конкретен корисник, затоа што индексите `idx_transaction_account_user_id` и `idx_transaction_breakdown_account_id` овозможуваат PostgreSQL директно да ги најде релевантните сметки и breakdown записи, наместо да ги чита целите табели. |
| | 456 | |
| | 457 | Кај извештајот за сметки со највисоко трошење во последната година, најважен е индексот `idx_transaction_date`, затоа што условот по датум ја намалува количината на трансакции кои треба да се обработат. |
| | 458 | |
| | 459 | Заклучокот е дека предложените индекси се оправдани и корисни за FEiN, особено кога базата содржи поголем број трансакции. Тие значително го намалуваат времето на извршување на аналитичките извештаи и ја подобруваат скалабилноста на системот. |
| | 460 | |
| | 461 | |
