Changes between Version 19 and Version 20 of OtherTopics


Ignore:
Timestamp:
03/03/26 07:34:16 (3 weeks ago)
Author:
221181
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • OtherTopics

    v19 v20  
    245245
    246246'''Анализа:''' Многу по брзо.
     247
     248=== Сценарио 2: Продажба по категорија ===
     249
     250Цел: Цел: Прикажува кои категории носат најголем приход.
     251
     252{{{
     253SELECT
     254    c.category_id,
     255    c.name AS category_name,
     256    SUM(si.quantity * si.unit_price_at_sale) AS total_category_revenue
     257FROM sale_item si
     258JOIN product p ON si.product_id = p.product_id
     259JOIN category c ON p.category_id = c.category_id
     260GROUP BY c.category_id, c.name
     261ORDER BY total_category_revenue DESC;
     262}}}
     263
     264==== 1.1. Без индекс ====
     265
     266Се спојува табелата sale_item (многу голема) со табелата product, а потоа се спојува и со табелата category.
     267
     268{{{
     269EXPLAIN ANALYZE
     270SELECT c.name, SUM(si.quantity * si.unit_price_at_sale)
     271FROM stock_management.sale_item si
     272JOIN stock_management.product p ON si.product_id = p.product_id
     273JOIN stock_management.category c ON p.category_id = c.category_id
     274GROUP BY c.category_id, c.name;
     275}}}
     276
     277{{{
     278"Finalize GroupAggregate  (cost=7589.33..7600.28 rows=50 width=47) (actual time=624.660..643.857 rows=50.00 loops=1)"
     279"  Group Key: c.category_id"
     280"  Buffers: shared hit=2054"
     281"  ->  Gather Merge  (cost=7589.33..7599.02 rows=85 width=47) (actual time=624.642..643.771 rows=100.00 loops=1)"
     282"        Workers Planned: 1"
     283"        Workers Launched: 1"
     284"        Buffers: shared hit=2054"
     285"        ->  Sort  (cost=6589.32..6589.45 rows=50 width=47) (actual time=507.965..507.972 rows=50.00 loops=2)"
     286"              Sort Key: c.category_id"
     287"              Sort Method: quicksort  Memory: 30kB"
     288"              Buffers: shared hit=2054"
     289"              Worker 0:  Sort Method: quicksort  Memory: 30kB"
     290"              ->  Partial HashAggregate  (cost=6587.29..6587.91 rows=50 width=47) (actual time=507.781..507.904 rows=50.00 loops=2)"
     291"                    Group Key: c.category_id"
     292"                    Batches: 1  Memory Usage: 40kB"
     293"                    Buffers: shared hit=2047"
     294"                    Worker 0:  Batches: 1  Memory Usage: 40kB"
     295"                    ->  Hash Join  (cost=181.62..4822.93 rows=176436 width=25) (actual time=5.394..368.931 rows=149971.00 loops=2)"
     296"                          Hash Cond: (p.category_id = c.category_id)"
     297"                          Buffers: shared hit=2047"
     298"                          ->  Hash Join  (cost=179.50..4318.40 rows=176436 width=14) (actual time=4.975..314.892 rows=149971.00 loops=2)"
     299"                                Hash Cond: (si.product_id = p.product_id)"
     300"                                Buffers: shared hit=2045"
     301"                                ->  Parallel Seq Scan on sale_item si  (cost=0.00..3675.36 rows=176436 width=14) (actual time=0.704..156.201 rows=149971.00 loops=2)"
     302"                                      Buffers: shared hit=1911"
     303"                                ->  Hash  (cost=117.00..117.00 rows=5000 width=8) (actual time=4.200..4.200 rows=5000.00 loops=2)"
     304"                                      Buckets: 8192  Batches: 1  Memory Usage: 260kB"
     305"                                      Buffers: shared hit=134"
     306"                                      ->  Seq Scan on product p  (cost=0.00..117.00 rows=5000 width=8) (actual time=0.610..3.091 rows=5000.00 loops=2)"
     307"                                            Buffers: shared hit=134"
     308"                          ->  Hash  (cost=1.50..1.50 rows=50 width=15) (actual time=0.403..0.404 rows=50.00 loops=2)"
     309"                                Buckets: 1024  Batches: 1  Memory Usage: 11kB"
     310"                                Buffers: shared hit=2"
     311"                                ->  Seq Scan on category c  (cost=0.00..1.50 rows=50 width=15) (actual time=0.371..0.378 rows=50.00 loops=2)"
     312"                                      Buffers: shared hit=2"
     313"Planning:"
     314"  Buffers: shared hit=162 dirtied=5"
     315"Planning Time: 148.428 ms"
     316"Execution Time: 645.128 ms"
     317}}}
     318
     319'''Времетраење: 103.080 ms'''
     320
     321'''Анализа:''' Многу бавно.
     322
     323==== 1.2. Со индекс ====
     324
     325За брзо совпаѓање на продажните ставки со производот.
     326'''Применет индекс:'''
     327{{{CREATE INDEX idx_sale_item_product_id ON stock_management.sale_item(product_id);}}}
     328
     329За брзо совпаѓање на производите по категорија
     330'''Применет индекс:'''
     331{{{CREATE INDEX idx_product_category_id ON stock_management.product(category_id);}}}
     332
     333{{{
     334"Finalize GroupAggregate  (cost=7589.33..7600.28 rows=50 width=47) (actual time=969.751..977.654 rows=50.00 loops=1)"
     335"  Group Key: c.category_id"
     336"  Buffers: shared hit=2054"
     337"  ->  Gather Merge  (cost=7589.33..7599.02 rows=85 width=47) (actual time=969.729..977.531 rows=100.00 loops=1)"
     338"        Workers Planned: 1"
     339"        Workers Launched: 1"
     340"        Buffers: shared hit=2054"
     341"        ->  Sort  (cost=6589.32..6589.45 rows=50 width=47) (actual time=792.046..792.057 rows=50.00 loops=2)"
     342"              Sort Key: c.category_id"
     343"              Sort Method: quicksort  Memory: 30kB"
     344"              Buffers: shared hit=2054"
     345"              Worker 0:  Sort Method: quicksort  Memory: 30kB"
     346"              ->  Partial HashAggregate  (cost=6587.29..6587.91 rows=50 width=47) (actual time=790.873..790.904 rows=50.00 loops=2)"
     347"                    Group Key: c.category_id"
     348"                    Batches: 1  Memory Usage: 40kB"
     349"                    Buffers: shared hit=2047"
     350"                    Worker 0:  Batches: 1  Memory Usage: 40kB"
     351"                    ->  Hash Join  (cost=181.62..4822.93 rows=176436 width=25) (actual time=3.506..493.595 rows=149971.00 loops=2)"
     352"                          Hash Cond: (p.category_id = c.category_id)"
     353"                          Buffers: shared hit=2047"
     354"                          ->  Hash Join  (cost=179.50..4318.40 rows=176436 width=14) (actual time=3.070..303.720 rows=149971.00 loops=2)"
     355"                                Hash Cond: (si.product_id = p.product_id)"
     356"                                Buffers: shared hit=2045"
     357"                                ->  Parallel Seq Scan on sale_item si  (cost=0.00..3675.36 rows=176436 width=14) (actual time=0.055..93.742 rows=149971.00 loops=2)"
     358"                                      Buffers: shared hit=1911"
     359"                                ->  Hash  (cost=117.00..117.00 rows=5000 width=8) (actual time=2.952..2.954 rows=5000.00 loops=2)"
     360"                                      Buckets: 8192  Batches: 1  Memory Usage: 260kB"
     361"                                      Buffers: shared hit=134"
     362"                                      ->  Seq Scan on product p  (cost=0.00..117.00 rows=5000 width=8) (actual time=0.279..1.620 rows=5000.00 loops=2)"
     363"                                            Buffers: shared hit=134"
     364"                          ->  Hash  (cost=1.50..1.50 rows=50 width=15) (actual time=0.418..0.419 rows=50.00 loops=2)"
     365"                                Buckets: 1024  Batches: 1  Memory Usage: 11kB"
     366"                                Buffers: shared hit=2"
     367"                                ->  Seq Scan on category c  (cost=0.00..1.50 rows=50 width=15) (actual time=0.377..0.387 rows=50.00 loops=2)"
     368"                                      Buffers: shared hit=2"
     369"Planning:"
     370"  Buffers: shared hit=16"
     371"Planning Time: 1.649 ms"
     372"Execution Time: 977.996 ms"
     373}}}
     374
     375
     376'''Времетраење: 29.767 ms '''
     377
     378'''Анализа:''' Многу по брзо.