Changes between Version 6 and Version 7 of OtherTopics


Ignore:
Timestamp:
09/15/26 02:52:48 (13 days ago)
Author:
232012
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • OtherTopics

    v6 v7  
    11= Other topics (Performance, Security, …)
    22
     3{{{
     4WITH new_orders AS (
     5    INSERT INTO project.orders
     6    (
     7        user_id,
     8        payment_method,
     9        purchase_date,
     10        points_earned,
     11        points_used,
     12        status
     13    )
     14    SELECT
     15        (ARRAY[1,2,3,5,6,7,8,10,11,12,13,14])
     16            [1 + floor(random() * 12)::int],
     17
     18        (ARRAY[
     19            'CARD'::project.payment_method_type,
     20            'PAYPAL'::project.payment_method_type,
     21            'CASH'::project.payment_method_type
     22        ])
     23            [1 + floor(random() * 3)::int],
     24
     25        CURRENT_DATE - floor(random() * 365)::int,
     26
     27        floor(random() * 100)::bigint,
     28
     29        NULL,
     30
     31        (ARRAY[
     32            'PAID'::project.order_status_type,
     33            'SHIPPED'::project.order_status_type,
     34            'DELIVERED'::project.order_status_type
     35        ])
     36            [1 + floor(random() * 3)::int]
     37
     38    FROM generate_series(1, 5000)
     39
     40    RETURNING order_id
     41)
     42INSERT INTO project.order_products
     43(
     44    order_id,
     45    product_id,
     46    price_at_purchase,
     47    quantity
     48)
     49SELECT
     50    no.order_id,
     51    p.product_id,
     52    p.price,
     53    1 + floor(random() * 4)::bigint
     54FROM new_orders no
     55CROSS JOIN LATERAL (
     56    SELECT
     57        product_id,
     58        price
     59    FROM project.products
     60    ORDER BY random()
     61    LIMIT 3
     62) p;
     63}}}
     64
    365== Scenario 1 - Top Selling Products and Restock Plan
    466
    567{{{#!div style="text-align: justify; width: 100%;"
    668
     69==== Query Used
     70
     71{{{
     72SET search_path TO project;
     73
     74EXPLAIN (ANALYZE, BUFFERS)
     75WITH product_sales_yearly AS (
     76    SELECT
     77        op.product_id,
     78        SUM(op.quantity) AS total_sold_yearly,
     79        SUM(op.quantity) / 365.0 AS daily_sales_velocity
     80    FROM order_products op
     81    JOIN orders o ON op.order_id = o.order_id
     82    WHERE o.purchase_date >= CURRENT_DATE - INTERVAL '1 year'
     83      AND o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
     84    GROUP BY op.product_id
     85),
     86inventory_velocity AS (
     87    SELECT
     88        p.product_id,
     89        p.format,
     90        p.stock,
     91        p.price,
     92        r.title AS release_title,
     93        psy.total_sold_yearly,
     94        psy.daily_sales_velocity,
     95        CASE
     96            WHEN psy.daily_sales_velocity > 0
     97                THEN p.stock / psy.daily_sales_velocity
     98            ELSE 9999
     99        END AS days_until_out_of_stock
     100    FROM products p
     101    JOIN releases r
     102        ON p.release_id = r.release_id
     103    JOIN product_sales_yearly psy
     104        ON p.product_id = psy.product_id
     105)
     106SELECT
     107    product_id,
     108    release_title,
     109    format,
     110    stock AS current_stock,
     111    total_sold_yearly,
     112    ROUND(CAST(daily_sales_velocity AS NUMERIC), 2) AS daily_velocity,
     113    ROUND(CAST(days_until_out_of_stock AS NUMERIC), 1) AS days_left,
     114    CEIL((daily_sales_velocity * 90) - stock) AS recommended_restock_quantity
     115FROM inventory_velocity
     116WHERE days_until_out_of_stock < 30
     117ORDER BY daily_velocity DESC, days_left ASC;
     118}}}
     119
    7120==== Without indexes
    8121
    … …  
    10123
    11124{{{
    12 Seq Scan on order_products
    13 rows=75021
    14 
    15 Seq Scan on orders
    16 rows=5009
    17 Rows Removed by Filter: 20008
     125Sort  (cost=75.74..75.74 rows=2 width=180) (actual time=0.185..0.189 rows=0 loops=1)
     126  Sort Key: (round(psy.daily_sales_velocity, 2)) DESC, (round(CASE WHEN (psy.daily_sales_velocity > '0'::numeric) THEN ((p.stock)::numeric / psy.daily_sales_velocity) ELSE '9999'::numeric END, 1))
     127  Sort Method: quicksort  Memory: 25kB
     128  Buffers: shared hit=3
     129  ->  Nested Loop  (cost=56.78..75.73 rows=2 width=180) (actual time=0.163..0.166 rows=0 loops=1)
     130        Buffers: shared hit=3
     131        ->  Hash Join  (cost=56.63..75.21 rows=2 width=92) (actual time=0.162..0.165 rows=0 loops=1)
     132              Hash Cond: (p.product_id = psy.product_id)
     133              Join Filter: (CASE WHEN (psy.daily_sales_velocity > '0'::numeric) THEN ((p.stock)::numeric / psy.daily_sales_velocity) ELSE '9999'::numeric END < '30'::numeric)
     134              Rows Removed by Join Filter: 5
     135              Buffers: shared hit=3
     136              ->  Seq Scan on products p  (cost=0.00..16.80 rows=680 width=28) (actual time=0.018..0.020 rows=17 loops=1)
     137                    Buffers: shared hit=1
     138              ->  Hash  (cost=56.57..56.57 rows=5 width=72) (actual time=0.117..0.119 rows=5 loops=1)
     139                    Buckets: 1024  Batches: 1  Memory Usage: 9kB
     140                    Buffers: shared hit=2
     141                    ->  Subquery Scan on psy  (cost=56.40..56.57 rows=5 width=72) (actual time=0.084..0.093 rows=5 loops=1)
     142                          Buffers: shared hit=2
     143                          ->  GroupAggregate  (cost=56.40..56.52 rows=5 width=72) (actual time=0.083..0.091 rows=5 loops=1)
     144                                Group Key: op.product_id
     145                                Buffers: shared hit=2
     146                                ->  Sort  (cost=56.40..56.42 rows=5 width=16) (actual time=0.073..0.076 rows=12 loops=1)
     147                                      Sort Key: op.product_id
     148                                      Sort Method: quicksort  Memory: 25kB
     149                                      Buffers: shared hit=2
     150                                      ->  Hash Join  (cost=34.09..56.34 rows=5 width=16) (actual time=0.050..0.059 rows=12 loops=1)
     151                                            Hash Cond: (op.order_id = o.order_id)
     152                                            Buffers: shared hit=2
     153                                            ->  Seq Scan on order_products op  (cost=0.00..19.70 rows=970 width=24) (actual time=0.007..0.009 rows=21 loops=1)
     154                                                  Buffers: shared hit=1
     155                                            ->  Hash  (cost=34.01..34.01 rows=6 width=8) (actual time=0.029..0.029 rows=9 loops=1)
     156                                                  Buckets: 1024  Batches: 1  Memory Usage: 9kB
     157                                                  Buffers: shared hit=1
     158                                                  ->  Seq Scan on orders o  (cost=0.00..34.01 rows=6 width=8) (actual time=0.013..0.019 rows=9 loops=1)
     159                                                        Filter: ((status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[])) AND (purchase_date >= (CURRENT_DATE - '1 year'::interval)))
     160                                                        Rows Removed by Filter: 8
     161                                                        Buffers: shared hit=1
     162        ->  Index Scan using releases_pkey on releases r  (cost=0.15..0.24 rows=1 width=40) (never executed)
     163              Index Cond: (release_id = p.release_id)
     164Planning Time: 0.555 ms
     165Execution Time: 0.272 ms
    18166}}}
    19167