Changes between Version 31 and Version 32 of OtherTopics


Ignore:
Timestamp:
06/26/26 02:57:53 (3 days ago)
Author:
221181
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • OtherTopics

    v31 v32  
    481481Овие индекси се корисни Best-Practice за Foreign Keys и ќе бидат
    482482активирани автоматски кога табелите ќе пораснат.
     483
     484=== Сценарио 3: Анализа на тренд на продажба и доволност на залиха ===
     485
     486Цел: Да се анализира продажбата во последните 60 дена и врз основа на просечната дневна продажба да се пресмета дали моменталната залиха е доволна за наредните 30 дена.
     487
     488{{{
     489WITH recent_sales AS (
     490    SELECT
     491        si.product_id,
     492        SUM(si.quantity) AS sold_last_60_days
     493    FROM sale_item si
     494    JOIN sale s ON si.sale_id = s.sale_id
     495    WHERE s.date_time >= CURRENT_DATE - INTERVAL '60 days'
     496    GROUP BY si.product_id
     497),
     498current_stock AS (
     499    SELECT
     500        product_id,
     501        SUM(quantity_on_hand) AS total_stock
     502    FROM warehouse_stock
     503    GROUP BY product_id
     504)
     505SELECT
     506    p.product_id,
     507    p.name AS product_name,
     508    COALESCE(rs.sold_last_60_days, 0) AS sold_last_60_days,
     509    ROUND(COALESCE(rs.sold_last_60_days, 0)::numeric / 60, 2) AS avg_daily_sales,
     510    ROUND((COALESCE(rs.sold_last_60_days, 0)::numeric / 60) * 30, 2) AS projected_next_30_days,
     511    COALESCE(cs.total_stock, 0) AS current_total_stock,
     512    CASE
     513        WHEN COALESCE(cs.total_stock, 0) >= ((COALESCE(rs.sold_last_60_days, 0)::numeric / 60) * 30) THEN 'SUFFICIENT'
     514        ELSE 'INSUFFICIENT'
     515    END AS stock_status
     516FROM product p
     517LEFT JOIN recent_sales rs ON p.product_id = rs.product_id
     518LEFT JOIN current_stock cs ON p.product_id = cs.product_id
     519ORDER BY stock_status, projected_next_30_days DESC;
     520}}}
     521
     522==== 1. Предложени индекси ====
     523
     524За да се оптимизира временското пребарување и агрегацијата на залихата, предложени се следниве индекси:
     525{{{
     526CREATE INDEX idx_sale_recent_dates ON stock_management.sale(date_time DESC);
     527CREATE INDEX idx_wh_stock_cover ON stock_management.warehouse_stock(product_id, quantity_on_hand);
     528}}}
     529
     530==== 2. Анализа пред креирање на индексот (Без индекс) ====
     531
     532{{{
     533EXPLAIN ANALYZE
     534WITH recent_sales AS (
     535    SELECT
     536        si.product_id,
     537        SUM(si.quantity) AS sold_last_60_days
     538    FROM sale_item si
     539    JOIN sale s ON si.sale_id = s.sale_id
     540    WHERE s.date_time >= CURRENT_DATE - INTERVAL '60 days'
     541    GROUP BY si.product_id
     542),
     543current_stock AS (
     544    SELECT
     545        product_id,
     546        SUM(quantity_on_hand) AS total_stock
     547    FROM warehouse_stock
     548    GROUP BY product_id
     549)
     550SELECT
     551    p.product_id,
     552    p.name AS product_name,
     553    COALESCE(rs.sold_last_60_days, 0) AS sold_last_60_days,
     554    ROUND(COALESCE(rs.sold_last_60_days,0)::numeric / 60, 2) AS avg_daily_sales,
     555    ROUND((COALESCE(rs.sold_last_60_days,0)::numeric / 60) * 30, 2) AS projected_next_30_days,
     556    COALESCE(cs.total_stock,0) AS current_total_stock,
     557    CASE
     558        WHEN COALESCE(cs.total_stock,0) >=
     559             ((COALESCE(rs.sold_last_60_days,0)::numeric / 60) * 30)
     560        THEN 'SUFFICIENT'
     561        ELSE 'INSUFFICIENT'
     562    END AS stock_status
     563FROM product p
     564LEFT JOIN recent_sales rs ON p.product_id = rs.product_id
     565LEFT JOIN current_stock cs ON p.product_id = cs.product_id
     566ORDER BY stock_status, projected_next_30_days DESC;
     567}}}
     568
     569{{{
     570"Sort  (cost=6407.95..6432.95 rows=10000 width=128) (actual time=733.443..734.040 rows=10000.00 loops=1)"
     571"  Sort Key: (CASE WHEN ((COALESCE(cs.total_stock, '0'::bigint))::numeric >= (((COALESCE(rs.sold_last_60_days, '0'::bigint))::numeric / '60'::numeric) * '30'::numeric)) THEN 'SUFFICIENT'::text ELSE 'INSUFFICIENT'::text END), (round((((COALESCE(rs.sold_last_60_days, '0'::bigint))::numeric / '60'::numeric) * '30'::numeric), 2)) DESC"
     572"  Sort Method: quicksort  Memory: 1158kB"
     573"  Buffers: shared hit=1502"
     574"  ->  Hash Left Join  (cost=5177.04..5743.56 rows=10000 width=128) (actual time=681.340..719.364 rows=10000.00 loops=1)"
     575"        Hash Cond: (p.product_id = cs.product_id)"
     576"        Buffers: shared hit=1502"
     577"        ->  Hash Left Join  (cost=4638.04..4878.30 rows=10000 width=24) (actual time=652.140..661.568 rows=10000.00 loops=1)"
     578"              Hash Cond: (p.product_id = rs.product_id)"
     579"              Buffers: shared hit=1438"
     580"              ->  Seq Scan on product p  (cost=0.00..214.00 rows=10000 width=16) (actual time=0.116..5.126 rows=10000.00 loops=1)"
     581"                    Buffers: shared hit=114"
     582"              ->  Hash  (cost=4638.00..4638.00 rows=3 width=12) (actual time=651.951..651.957 rows=3.00 loops=1)"
     583"                    Buckets: 1024  Batches: 1  Memory Usage: 9kB"
     584"                    Buffers: shared hit=1324"
     585"                    ->  Subquery Scan on rs  (cost=4637.94..4638.00 rows=3 width=12) (actual time=651.929..651.937 rows=3.00 loops=1)"
     586"                          Buffers: shared hit=1324"
     587"                          ->  HashAggregate  (cost=4637.94..4637.97 rows=3 width=12) (actual time=651.926..651.931 rows=3.00 loops=1)"
     588"                                Group Key: si.product_id"
     589"                                Batches: 1  Memory Usage: 32kB"
     590"                                Buffers: shared hit=1324"
     591"                                ->  Hash Join  (cost=1490.80..4340.58 rows=59472 width=8) (actual time=220.253..612.386 rows=59442.00 loops=1)"
     592"                                      Hash Cond: (si.sale_id = s.sale_id)"
     593"                                      Buffers: shared hit=1324"
     594"                                      ->  Seq Scan on sale_item si  (cost=0.00..2456.00 rows=150000 width=12) (actual time=0.043..74.882 rows=150000.00 loops=1)"
     595"                                            Buffers: shared hit=956"
     596"                                      ->  Hash  (cost=1243.00..1243.00 rows=19824 width=4) (actual time=219.680..219.681 rows=19814.00 loops=1)"
     597"                                            Buckets: 32768  Batches: 1  Memory Usage: 953kB"
     598"                                            Buffers: shared hit=368"
     599"                                            ->  Seq Scan on sale s  (cost=0.00..1243.00 rows=19824 width=4) (actual time=0.074..98.152 rows=19814.00 loops=1)"
     600"                                                  Filter: (date_time >= (CURRENT_DATE - '60 days'::interval))"
     601"                                                  Rows Removed by Filter: 30186"
     602"                                                  Buffers: shared hit=368"
     603"        ->  Hash  (cost=414.00..414.00 rows=10000 width=12) (actual time=28.495..28.497 rows=10000.00 loops=1)"
     604"              Buckets: 16384  Batches: 1  Memory Usage: 558kB"
     605"              Buffers: shared hit=64"
     606"              ->  Subquery Scan on cs  (cost=214.00..414.00 rows=10000 width=12) (actual time=18.392..23.647 rows=10000.00 loops=1)"
     607"                    Buffers: shared hit=64"
     608"                    ->  HashAggregate  (cost=214.00..314.00 rows=10000 width=12) (actual time=18.390..22.371 rows=10000.00 loops=1)"
     609"                          Group Key: warehouse_stock.product_id"
     610"                          Batches: 1  Memory Usage: 793kB"
     611"                          Buffers: shared hit=64"
     612"                          ->  Seq Scan on warehouse_stock  (cost=0.00..164.00 rows=10000 width=8) (actual time=0.345..2.190 rows=10000.00 loops=1)"
     613"                                Buffers: shared hit=64"
     614"Planning:"
     615"  Buffers: shared hit=14"
     616"Planning Time: 21.363 ms"
     617"Execution Time: 747.335 ms"
     618}}}
     619
     620'''Времетраење: 747.335 ms'''
     621
     622==== 3. Анализа по креирање на индексот (Со индекс) ====
     623
     624{{{
     625"Sort  (cost=6257.80..6282.80 rows=10000 width=128) (actual time=188.971..189.554 rows=10000.00 loops=1)"
     626"  Sort Key: (CASE WHEN ((COALESCE(cs.total_stock, '0'::bigint))::numeric >= (((COALESCE(rs.sold_last_60_days, '0'::bigint))::numeric / '60'::numeric) * '30'::numeric)) THEN 'SUFFICIENT'::text ELSE 'INSUFFICIENT'::text END), (round((((COALESCE(rs.sold_last_60_days, '0'::bigint))::numeric / '60'::numeric) * '30'::numeric), 2)) DESC"
     627"  Sort Method: quicksort  Memory: 1158kB"
     628"  Buffers: shared hit=1558"
     629"  ->  Hash Left Join  (cost=5026.89..5593.41 rows=10000 width=128) (actual time=125.016..182.234 rows=10000.00 loops=1)"
     630"        Hash Cond: (p.product_id = cs.product_id)"
     631"        Buffers: shared hit=1558"
     632"        ->  Hash Left Join  (cost=4487.89..4728.15 rows=10000 width=24) (actual time=95.217..98.959 rows=10000.00 loops=1)"
     633"              Hash Cond: (p.product_id = rs.product_id)"
     634"              Buffers: shared hit=1494"
     635"              ->  Seq Scan on product p  (cost=0.00..214.00 rows=10000 width=16) (actual time=0.029..1.061 rows=10000.00 loops=1)"
     636"                    Buffers: shared hit=114"
     637"              ->  Hash  (cost=4487.85..4487.85 rows=3 width=12) (actual time=95.152..95.168 rows=3.00 loops=1)"
     638"                    Buckets: 1024  Batches: 1  Memory Usage: 9kB"
     639"                    Buffers: shared hit=1380"
     640"                    ->  Subquery Scan on rs  (cost=4487.79..4487.85 rows=3 width=12) (actual time=95.135..95.141 rows=3.00 loops=1)"
     641"                          Buffers: shared hit=1380"
     642"                          ->  HashAggregate  (cost=4487.79..4487.82 rows=3 width=12) (actual time=95.133..95.138 rows=3.00 loops=1)"
     643"                                Group Key: si.product_id"
     644"                                Batches: 1  Memory Usage: 32kB"
     645"                                Buffers: shared hit=1380"
     646"                                ->  Hash Join  (cost=1340.65..4190.43 rows=59472 width=8) (actual time=9.461..79.811 rows=59442.00 loops=1)"
     647"                                      Hash Cond: (si.sale_id = s.sale_id)"
     648"                                      Buffers: shared hit=1380"
     649"                                      ->  Seq Scan on sale_item si  (cost=0.00..2456.00 rows=150000 width=12) (actual time=0.013..14.517 rows=150000.00 loops=1)"
     650"                                            Buffers: shared hit=956"
     651"                                      ->  Hash  (cost=1092.85..1092.85 rows=19824 width=4) (actual time=9.311..9.313 rows=19814.00 loops=1)"
     652"                                            Buckets: 32768  Batches: 1  Memory Usage: 953kB"
     653"                                            Buffers: shared hit=424"
     654"                                            ->  Bitmap Heap Scan on sale s  (cost=377.93..1092.85 rows=19824 width=4) (actual time=1.562..5.500 rows=19814.00 loops=1)"
     655"                                                  Recheck Cond: (date_time >= (CURRENT_DATE - '60 days'::interval))"
     656"                                                  Heap Blocks: exact=368"
     657"                                                  Buffers: shared hit=424"
     658"                                                  ->  Bitmap Index Scan on idx_sale_recent_dates  (cost=0.00..372.98 rows=19824 width=0) (actual time=1.478..1.478 rows=19814.00 loops=1)"
     659"                                                        Index Cond: (date_time >= (CURRENT_DATE - '60 days'::interval))"
     660"                                                        Index Searches: 1"
     661"                                                        Buffers: shared hit=56"
     662"        ->  Hash  (cost=414.00..414.00 rows=10000 width=12) (actual time=29.688..29.691 rows=10000.00 loops=1)"
     663"              Buckets: 16384  Batches: 1  Memory Usage: 558kB"
     664"              Buffers: shared hit=64"
     665"              ->  Subquery Scan on cs  (cost=214.00..414.00 rows=10000 width=12) (actual time=24.584..27.744 rows=10000.00 loops=1)"
     666"                    Buffers: shared hit=64"
     667"                    ->  HashAggregate  (cost=214.00..314.00 rows=10000 width=12) (actual time=24.582..26.557 rows=10000.00 loops=1)"
     668"                          Group Key: warehouse_stock.product_id"
     669"                          Batches: 1  Memory Usage: 793kB"
     670"                          Buffers: shared hit=64"
     671"                          ->  Seq Scan on warehouse_stock  (cost=0.00..164.00 rows=10000 width=8) (actual time=0.028..0.841 rows=10000.00 loops=1)"
     672"                                Buffers: shared hit=64"
     673"Planning:"
     674"  Buffers: shared hit=14"
     675"Planning Time: 0.705 ms"
     676"Execution Time: 191.517 ms"
     677}}}
     678
     679'''Времетраење: 191.517 ms'''