Changes between Version 1 and Version 2 of OtherTopics


Ignore:
Timestamp:
09/24/26 02:38:28 (7 days ago)
Author:
201178
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • OtherTopics

    v1 v2  
    8181|| Табела || Број на редови ||
    8282|| app_user || 4 ||
    83 || orders || ~50 ||
    84 || order_item || ~50 ||
    85 || payment || ~50 ||
     83|| orders || 10.088 ||
     84|| order_item || 30.048 ||
     85|| payment || 5.046 ||
    8686|| product || ~330 ||
    8787|| inventory || ~370 ||
    … …  
    144144}}}
    145145
    146 '''Пред додавање на индексите:'''
    147 
    148 {{{
    149 HashAggregate  (cost=45.20..47.30 rows=180 width=68) (actual time=0.350..0.360 rows=50 loops=1)
    150   Group Key: p.product_id, p.name, c.name
    151   Buffers: shared hit=25
    152   ->  Hash Join  (cost=8.50..42.00 rows=200 width=44) (actual time=0.150..0.280 rows=50 loops=1)
    153         Hash Cond: (oi.product_id = p.product_id)
    154         ->  Hash Join  (cost=5.00..30.00 rows=50 width=20) (actual time=0.080..0.180 rows=50 loops=1)
    155               Hash Cond: (oi.order_id = o.order_id)
    156               ->  Seq Scan on order_item oi  (actual time=0.010..0.020 rows=50 loops=1)
    157               ->  Hash  (actual time=0.030..0.030 rows=46 loops=1)
    158                     ->  Seq Scan on orders o  (actual time=0.010..0.020 rows=46 loops=1)
    159                           Filter: (status = 'ПЛАТЕНА' AND created_at >= ...)
    160         ->  Hash
    161               ->  Seq Scan on product p
    162         ->  Hash
    163               ->  Seq Scan on category c
    164 Planning Time: 0.350 ms
    165 Execution Time: 0.480 ms
    166 }}}
    167 
    168 '''По додавање на индексите:'''
    169 
    170 {{{
    171 HashAggregate  (cost=40.20..42.30 rows=180 width=68) (actual time=0.250..0.260 rows=50 loops=1)
    172   Group Key: p.product_id, p.name, c.name
    173   Buffers: shared hit=30
    174   ->  Hash Join  (cost=7.50..38.00 rows=200 width=44) (actual time=0.120..0.220 rows=50 loops=1)
    175         Hash Cond: (oi.product_id = p.product_id)
    176         ->  Hash Join  (cost=4.50..28.00 rows=50 width=20) (actual time=0.070..0.150 rows=50 loops=1)
    177               Hash Cond: (oi.order_id = o.order_id)
    178               ->  Seq Scan on order_item oi  (actual time=0.010..0.020 rows=50 loops=1)
    179               ->  Hash  (actual time=0.025..0.025 rows=46 loops=1)
    180                     ->  Index Scan using idx_orders_status on orders o 
    181                           Index Cond: (status = 'ПЛАТЕНА')
    182                           Filter: (created_at >= ...)
    183         ->  Hash
    184               ->  Seq Scan on product p
    185         ->  Hash
    186               ->  Seq Scan on category c
    187 Planning Time: 0.400 ms
    188 Execution Time: 0.450 ms
     146Резултат од EXPLAIN (ANALYZE, BUFFERS):
     147
     148{{{
     149Sort  (cost=916.67..916.77 rows=40 width=329) (actual time=110.670..110.675 rows=24 loops=1)
     150  Sort Key: (sum(((oi.quantity)::numeric * oi.unit_price))) DESC
     151  Sort Method: quicksort  Memory: 27kB
     152  Buffers: shared hit=42572
     153  ->  GroupAggregate  (cost=914.00..915.60 rows=40 width=329) (actual time=98.576..110.651 rows=24 loops=1)
     154        Group Key: p.product_id, c.name
     155        Buffers: shared hit=42572
     156        ->  Sort  (cost=914.00..914.10 rows=40 width=273) (actual time=98.557..99.821 rows=20940 loops=1)
     157              Sort Key: p.product_id, c.name, o.order_id
     158              Sort Method: quicksort  Memory: 2078kB
     159              Buffers: shared hit=42572
     160              ->  Nested Loop  (cost=97.89..912.94 rows=40 width=273) (actual time=5.056..56.400 rows=20940 loops=1)
     161                    Buffers: shared hit=42572
     162                    ->  Nested Loop  (cost=97.74..908.87 rows=40 width=59) (actual time=5.043..46.378 rows=20940 loops=1)
     163                          Buffers: shared hit=42562
     164                          ->  Hash Join  (cost=97.60..902.23 rows=40 width=28) (actual time=5.030..17.485 rows=20940 loops=1)
     165                                Hash Cond: (oi.order_id = o.order_id)
     166                                Buffers: shared hit=682
     167                                ->  Seq Scan on order_item oi  (cost=0.00..741.48 rows=24048 width=28) (actual time=0.019..4.361 rows=30048 loops=1)
     168                                      Buffers: shared hit=501
     169                                ->  Hash  (cost=97.43..97.43 rows=13 width=4) (actual time=4.999..5.000 rows=7010 loops=1)
     170                                      Buckets: 8192 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 311kB
     171                                      Buffers: shared hit=181
     172                                      ->  Bitmap Heap Scan on orders o  (cost=4.58..97.43 rows=13 width=4) (actual time=0.485..3.863 rows=7010 loops=1)
     173                                            Recheck Cond: ((status)::text = 'ПЛАТЕНА'::text)
     174                                            Filter: (created_at >= (now() - '1 year'::interval))
     175                                            Heap Blocks: exact=168
     176                                            Buffers: shared hit=181
     177                                            ->  Bitmap Index Scan on idx_orders_status  (cost=0.00..4.58 rows=39 width=0) (actual time=0.443..0.443 rows=13992 loops=1)
     178                                                  Index Cond: ((status)::text = 'ПЛАТЕНА'::text)
     179                                                  Buffers: shared hit=13
     180                          ->  Index Scan using product_pkey on product p  (cost=0.15..0.17 rows=1 width=35) (actual time=0.001..0.001 rows=1 loops=20940)
     181                                Index Cond: (product_id = oi.product_id)
     182                                Buffers: shared hit=41880
     183                    ->  Memoize  (cost=0.15..0.20 rows=1 width=222) (actual time=0.000..0.000 rows=1 loops=20940)
     184                          Cache Key: p.category_id
     185                          Cache Mode: logical
     186                          Hits: 20935  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB
     187                          Buffers: shared hit=10
     188                          ->  Index Scan using category_pkey on category c  (cost=0.14..0.19 rows=1 width=222) (actual time=0.002..0.002 rows=1 loops=5)
     189                                Index Cond: (category_id = p.category_id)
     190                                Buffers: shared hit=10
     191Planning:
     192  Buffers: shared hit=11
     193Planning Time: 0.559 ms
     194Execution Time: 110.770 ms
    189195}}}
    190196
    191197'''Споредба:'''
    192198
    193 || Метрика || Пред индекси || По индекси ||
    194 || Planning Time || 0.350 ms || 0.400 ms ||
    195 || Execution Time || 0.480 ms || 0.450 ms ||
    196 || Подобрување || / || ~6% ||
    197 || Тип на скенирање || Seq Scan на orders || Index Scan со idx_orders_status ||
    198 || Дали индексите се користат || Не || Да ||
    199 
    200 '''Заклучок:''' По додавање на индексите, PostgreSQL го користи `idx_orders_status` за побрзо да ги најде платените нарачки. Подобрувањето е мало (6%) поради малиот број на податоци, но со растот на базата разликата значително ќе се зголеми.
     199|| Метрика || Вредност ||
     200|| Planning Time || 0.559 ms ||
     201|| Execution Time || 110.770 ms ||
     202|| Вкупно вратени редови || 24 ||
     203|| Тип на скенирање на orders || '''Bitmap Index Scan (idx_orders_status)''' ||
     204|| Индекси искористени || idx_orders_status, product_pkey, category_pkey ||
     205|| Memoize оптимизација || Да (20.935 hits, 5 misses) ||
     206
     207'''Заклучок:''' Со растот на базата од 46 на 10.000+ нарачки, PostgreSQL почна да го користи индексот `idx_orders_status` преку `Bitmap Index Scan` за филтрирање на платените нарачки, наместо Seq Scan. Дополнително, PostgreSQL користи `Memoize` оптимизација за категориите, намалувајќи 20.935 повторени читања на само 5.
    201208
    202209=== Сценарио 2: Месечни приходи и расходи ===
    … …  
    222229}}}
    223230
    224 '''Пред додавање на индексите:'''
    225 
    226 {{{
    227 GroupAggregate  (cost=35.20..37.30 rows=12 width=68) (actual time=0.280..0.290 rows=12 loops=1)
    228   Group Key: (to_char(payment_date, 'YYYY-MM'))
    229   Buffers: shared hit=18
    230   ->  Sort  (cost=35.20..35.30 rows=50 width=36) (actual time=0.270..0.275 rows=50 loops=1)
    231         Sort Key: (to_char(p.payment_date, 'YYYY-MM'))
    232         ->  Hash Join  (cost=15.00..33.00 rows=50 width=36) (actual time=0.100..0.250 rows=50 loops=1)
    233               Hash Cond: (p.order_id = o.order_id)
    234               ->  Seq Scan on payment p  (actual time=0.010..0.020 rows=50 loops=1)
    235               ->  Hash  (actual time=0.030..0.030 rows=46 loops=1)
    236                     ->  Seq Scan on orders o  (actual time=0.010..0.020 rows=46 loops=1)
    237                           Filter: (status = 'ПЛАТЕНА')
    238 Planning Time: 0.380 ms
    239 Execution Time: 0.520 ms
    240 }}}
    241 
    242 '''По додавање на индексите:'''
    243 
    244 {{{
    245 GroupAggregate  (cost=28.20..30.30 rows=12 width=68) (actual time=0.180..0.190 rows=12 loops=1)
    246   Group Key: (to_char(payment_date, 'YYYY-MM'))
    247   Buffers: shared hit=20
    248   ->  Sort  (cost=28.20..28.30 rows=50 width=36) (actual time=0.170..0.175 rows=50 loops=1)
    249         Sort Key: (to_char(p.payment_date, 'YYYY-MM'))
    250         ->  Hash Join  (cost=10.00..26.00 rows=50 width=36) (actual time=0.070..0.150 rows=50 loops=1)
    251               Hash Cond: (p.order_id = o.order_id)
    252               ->  Seq Scan on payment p  (actual time=0.010..0.015 rows=50 loops=1)
    253               ->  Hash  (actual time=0.020..0.020 rows=46 loops=1)
    254                     ->  Index Scan using idx_orders_status on orders o 
    255                           Index Cond: (status = 'ПЛАТЕНА')
    256 Planning Time: 0.420 ms
    257 Execution Time: 0.490 ms
     231Резултат од EXPLAIN (ANALYZE, BUFFERS):
     232
     233{{{
     234GroupAggregate  (cost=681.74..804.98 rows=3521 width=120) (actual time=15.977..18.246 rows=13 loops=1)
     235  Group Key: (to_char(p.payment_date, 'YYYY-MM'::text))
     236  Buffers: shared hit=208
     237  ->  Sort  (cost=681.74..690.55 rows=3521 width=50) (actual time=15.791..16.167 rows=5046 loops=1)
     238        Sort Key: (to_char(p.payment_date, 'YYYY-MM'::text)) DESC, o.order_id
     239        Sort Method: quicksort  Memory: 429kB
     240        Buffers: shared hit=208
     241        ->  Hash Join  (cost=153.54..474.32 rows=3521 width=50) (actual time=1.766..8.294 rows=5046 loops=1)
     242              Hash Cond: (o.order_id = p.order_id)
     243              Buffers: shared hit=208
     244              ->  Seq Scan on orders o  (cost=0.00..293.57 rows=7010 width=12) (actual time=0.018..2.539 rows=7010 loops=1)
     245                    Filter: ((status)::text = 'ПЛАТЕНА'::text)
     246                    Rows Removed by Filter: 3036
     247                    Buffers: shared hit=168
     248              ->  Hash  (cost=90.46..90.46 rows=5046 width=18) (actual time=1.719..1.720 rows=5046 loops=1)
     249                    Buckets: 8192  Batches: 1  Memory Usage: 321kB
     250                    Buffers: shared hit=40
     251                    ->  Seq Scan on payment p  (cost=0.00..90.46 rows=5046 width=18) (actual time=0.007..0.750 rows=5046 loops=1)
     252                          Buffers: shared hit=40
     253Planning:
     254  Buffers: shared hit=93
     255Planning Time: 0.793 ms
     256Execution Time: 18.309 ms
    258257}}}
    259258
    260259'''Споредба:'''
    261260
    262 || Метрика || Пред индекси || По индекси ||
    263 || Planning Time || 0.380 ms || 0.420 ms ||
    264 || Execution Time || 0.520 ms || 0.490 ms ||
    265 || Подобрување || / || ~6% ||
    266 || Тип на скенирање || Seq Scan на orders || Index Scan со idx_orders_status ||
    267 || Дали индексите се користат || Не || Да ||
     261|| Метрика || Вредност ||
     262|| Planning Time || 0.793 ms ||
     263|| Execution Time || 18.309 ms ||
     264|| Вкупно вратени редови || 13 (месеци) ||
     265|| Тип на скенирање на orders || Seq Scan (со Filter на status) ||
     266|| Индекси искористени || (не се користат поради мал број на вратени редови) ||
     267
     268'''Заклучок:''' Овој извештај враќа само 13 редови (месеци) и PostgreSQL избира Seq Scan на двете табели, бидејќи агрегацијата бара читање на сите 5.046 плаќања. Индексот `idx_payment_payment_date` не се користи бидејќи нема филтер по датум (сите плаќања се во опсегот).
    268269
    269270=== Сценарио 3: Најпрометни часови во денот ===
    … …  
    287288}}}
    288289
    289 '''Пред додавање на индексите:'''
    290 
    291 {{{
    292 Sort  (cost=42.00..42.50 rows=200 width=52) (actual time=0.350..0.355 rows=12 loops=1)
    293   Sort Key: (sum((oi.quantity * oi.unit_price))) DESC
    294   Buffers: shared hit=22
    295   ->  HashAggregate  (cost=35.00..37.00 rows=200 width=52) (actual time=0.320..0.330 rows=12 loops=1)
    296         Group Key: (date_part('hour', o.created_at))
    297         ->  Hash Join  (cost=15.00..32.00 rows=300 width=36) (actual time=0.100..0.250 rows=50 loops=1)
    298               Hash Cond: (oi.order_id = o.order_id)
    299               ->  Seq Scan on order_item oi  (actual time=0.010..0.020 rows=50 loops=1)
    300               ->  Hash  (actual time=0.030..0.030 rows=46 loops=1)
    301                     ->  Seq Scan on orders o  (actual time=0.010..0.020 rows=46 loops=1)
    302                           Filter: (status = 'ПЛАТЕНА')
    303 Planning Time: 0.400 ms
    304 Execution Time: 0.550 ms
    305 }}}
    306 
    307 '''По додавање на индексите:'''
    308 
    309 {{{
    310 Sort  (cost=35.00..35.50 rows=200 width=52) (actual time=0.250..0.255 rows=12 loops=1)
    311   Sort Key: (sum((oi.quantity * oi.unit_price))) DESC
    312   Buffers: shared hit=25
    313   ->  HashAggregate  (cost=28.00..30.00 rows=200 width=52) (actual time=0.220..0.230 rows=12 loops=1)
    314         Group Key: (date_part('hour', o.created_at))
    315         ->  Hash Join  (cost=10.00..25.00 rows=300 width=36) (actual time=0.070..0.150 rows=50 loops=1)
    316               Hash Cond: (oi.order_id = o.order_id)
    317               ->  Seq Scan on order_item oi  (actual time=0.010..0.020 rows=50 loops=1)
    318               ->  Hash  (actual time=0.020..0.020 rows=46 loops=1)
    319                     ->  Index Scan using idx_orders_status on orders o 
    320                           Index Cond: (status = 'ПЛАТЕНА')
    321 Planning Time: 0.420 ms
    322 Execution Time: 0.500 ms
     290Резултат од EXPLAIN (ANALYZE, BUFFERS):
     291
     292{{{
     293Sort  (cost=3738.84..3756.37 rows=7010 width=80) (actual time=61.237..61.241 rows=24 loops=1)
     294  Sort Key: (sum(((oi.quantity)::numeric * oi.unit_price))) DESC
     295  Sort Method: quicksort  Memory: 26kB
     296  Buffers: shared hit=669
     297  ->  GroupAggregate  (cost=2819.00..3291.07 rows=7010 width=80) (actual time=50.305..61.219 rows=24 loops=1)
     298        Group Key: (EXTRACT(hour FROM o.created_at))
     299        Buffers: shared hit=669
     300        ->  Sort  (cost=2819.00..2871.42 rows=20967 width=45) (actual time=49.774..51.274 rows=20940 loops=1)
     301              Sort Key: (EXTRACT(hour FROM o.created_at)), o.order_id
     302              Sort Method: quicksort  Memory: 1586kB
     303              Buffers: shared hit=669
     304              ->  Hash Join  (cost=381.20..1314.01 rows=20967 width=45) (actual time=3.765..20.296 rows=20940 loops=1)
     305                    Hash Cond: (oi.order_id = o.order_id)
     306                    Buffers: shared hit=669
     307                    ->  Seq Scan on order_item oi  (cost=0.00..801.48 rows=30048 width=13) (actual time=0.014..2.618 rows=30048 loops=1)
     308                          Buffers: shared hit=501
     309                    ->  Hash  (cost=293.57..293.57 rows=7010 width=12) (actual time=3.724..3.725 rows=7010 loops=1)
     310                          Buckets: 8192  Batches: 1  Memory Usage: 366kB
     311                          Buffers: shared hit=168
     312                          ->  Seq Scan on orders o  (cost=0.00..293.57 rows=7010 width=12) (actual time=0.011..2.333 rows=7010 loops=1)
     313                                Filter: ((status)::text = 'ПЛАТЕНА'::text)
     314                                Rows Removed by Filter: 3036
     315                                Buffers: shared hit=168
     316Planning:
     317  Buffers: shared hit=46
     318Planning Time: 0.582 ms
     319Execution Time: 61.307 ms
    323320}}}
    324321
    325322'''Споредба:'''
    326323
    327 || Метрика || Пред индекси || По индекси ||
    328 || Planning Time || 0.400 ms || 0.420 ms ||
    329 || Execution Time || 0.550 ms || 0.500 ms ||
    330 || Подобрување || / || ~9% ||
    331 || Тип на скенирање || Seq Scan на orders || Index Scan со idx_orders_status ||
    332 || Дали индексите се користат || Не || Да ||
     324|| Метрика || Вредност ||
     325|| Planning Time || 0.582 ms ||
     326|| Execution Time || 61.307 ms ||
     327|| Вкупно вратени редови || 24 (часови) ||
     328|| Тип на скенирање на orders || Seq Scan (со Filter на status) ||
     329|| Тип на скенирање на order_item || Seq Scan ||
     330|| Индекси искористени || (не се користат поради голем опсег на податоци) ||
     331
     332'''Заклучок:''' Овој извештај враќа само 24 редови (часови), но мора да ги прочита сите 30.048 ставки и 7.010 платени нарачки. PostgreSQL избира Seq Scan бидејќи индексот не помага при агрегација на сите податоци.
    333333
    334334=== Финален заклучок од анализата ===
    335335
    336 || Сценарио || Execution Time пред индекси || Execution Time по индекси || Подобрување || Индекси искористени ||
    337 || Најпрофитабилни производи || 0.480 ms || 0.450 ms || ~6% || Да ||
    338 || Месечни приходи || 0.520 ms || 0.490 ms || ~6% || Да ||
    339 || Најпрометни часови || 0.550 ms || 0.500 ms || ~9% || Да ||
    340 
    341 Со оглед на малиот број на податоци во тест-базата, подобрувањето е мало. Меѓутоа, со растот на базата (илјадници нарачки, десетици илјади ставки), овие индекси значително ќе го намалат времето на извршување на аналитичките извештаи.
    342 
    343 Најзначаен е индексот `idx_orders_status`, кој се користи во сите три сценарија за филтрирање на платените нарачки.
     336|| Сценарио || Execution Time || Вратени редови || Индекси искористени ||
     337|| Најпрофитабилни производи || 110.770 ms || 24 || idx_orders_status ||
     338|| Месечни приходи || 18.309 ms || 13 || (Seq Scan) ||
     339|| Најпрометни часови || 61.307 ms || 24 || (Seq Scan) ||
     340
     341'''Клучни наоди:'''
     342
     343 * '''`idx_orders_status` се користи''' во Сценарио 1 преку `Bitmap Index Scan`, што покажува дека индексот е ефективен кога има филтер по статус.
     344 * '''Memoize оптимизација''' се користи за категориите во Сценарио 1, намалувајќи 20.935 повторени читања на само 5.
     345 * Во Сценарија 2 и 3, PostgreSQL избира Seq Scan бидејќи агрегацијата бара читање на сите податоци (нема селективен филтер).
     346 * Со растот на базата, индексите ќе имаат сè поголемо влијание, особено `idx_orders_status` и `idx_orders_created_at`.
    344347
    345348== 3. Интегритет и конзистентност ==