= Други теми =

== Безбедност == 

=== Спречување на SQL Injection ===

Користиме Entity Framework Core (ORM). EF Core автоматски ги параметризира сите LINQ прашања (queries). Ова спречува напаѓачите да вметнат злонамерни SQL команди преку полињата за внес.

* EF Core го третира **username** како параметар (@p0), а не како извршлив код.
* Ова спречува SQL Injection напади (на пр., ' OR 1=1 --).

{{{
public async Task<User> AuthenticateAsync(string username, string password)
{
    var user = await _context.Users
        .FirstOrDefaultAsync(u => u.Username == username && u.IsActive);

    if (user == null)
        return null;

    bool isHashed = user.Password.StartsWith("$2") && user.Password.Length == 60;

    if (isHashed)
    {
        if (BCrypt.Net.BCrypt.Verify(password, user.Password))
            return user;
    }
    else
    {
        if (user.Password == password)
        {
            user.Password = BCrypt.Net.BCrypt.HashPassword(password);
            await _context.SaveChangesAsync();

            return user;
        }
    }

    return null;
}        
}}}

=== Хеширање на лозинки (Заштита на податоци) ===

Лозинките се зачувуваат како хеш вредности со користење на алгоритмот BCrypt, а не како обичен текст.

{{{
public async Task<bool> CreateUserAsync(User user, string password)
{
    using var transaction = await _context.Database.BeginTransactionAsync();
    try
    {
        user.Password = BCrypt.Net.BCrypt.HashPassword(password);

        _context.Users.Add(user);
        await _context.SaveChangesAsync();

        await transaction.CommitAsync();
        return true;
    }
    catch
    {
        await transaction.RollbackAsync();
        return false;
    }
}     
}}}

=== Безбедност на Database Context (Row-Level идентификација) ===

Го пренесуваме идентитетот на моментално најавениот корисник од Application Layer до Database Layer (PostgreSQL) користејќи Session Variables. Ова и овозможува на базата на податоци да знае кој ја извршува операцијата.

{{{
public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
{
    var username = _httpContextAccessor.HttpContext?.User?.Identity?.Name ?? "system";

    await Database.ExecuteSqlRawAsync("SELECT set_config('app.current_user', {0}, false)", new[] { username }, cancellationToken);

    return await base.SaveChangesAsync(cancellationToken);
}
}}}

=== Авторизација (Role-Based Access Control) ===

Го ограничуваме пристапот до Controllers и Actions со користење на атрибутот ** [Authorize] **. Само автентицирани корисници со валидни cookies можат да пристапат до овие ресурси.

* Овој атрибут осигурува дека само најавени корисници можат да пристапат до било која акција во овој контролер.
* Неавтентицираните барања се пренасочуваат кон страницата за најава (Login page).

{{{
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;

namespace StockMaster.Controllers
{
   
    [Authorize] 
    public class ReportController : Controller
    {
        private readonly IReportService _reportService;

        public ReportController(IReportService reportService)
        {
            _reportService = reportService;
        }

        public IActionResult Index()
        {
            return View();
        }
        
        // ... други акции
    }
}
}}}

=== Безбедност на база на податоци базирана на логика (Triggers) ===

Користиме Database Triggers за да спроведеме безбедносни правила што не можат да бидат заобиколени од апликацијата. Поточно, спречуваме корисник да ја избрише сопствената account за да обезбедиме стабилност на системот и следење на активности.

{{{
CREATE OR REPLACE FUNCTION stock_management.prevent_self_delete()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.username = current_setting('app.current_user', true) THEN
        RAISE EXCEPTION 'You cannot delete your own account.';
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_prevent_self_delete
BEFORE DELETE ON stock_management.users
FOR EACH ROW EXECUTE FUNCTION stock_management.prevent_self_delete();
}}}

== Пеформанси - Индекси == 

За да се зголеми перформансата на базата на податоци, се применуваат стратегии за индексирање и се анализира споредбата на перформансите во состојби со индекс и без индекс. Тестовите се извршени со користење на командата EXPLAIN ANALYZE за реални мерења во реално време.

=== Сценарио 1: Тековен залиха по складиште ===

Цел: Прикажува вкупниот број производи и вредност на залихата по складишта.

{{{
SELECT
    w.warehouse_id,
    w.name AS warehouse_name,
    SUM(ws.quantity_on_hand) AS total_units,
    SUM(ws.quantity_on_hand * p.unit_price) AS total_stock_value
FROM warehouse_stock ws
JOIN warehouse w ON ws.warehouse_id = w.warehouse_id
JOIN product p ON ws.product_id = p.product_id
GROUP BY w.warehouse_id, w.name
ORDER BY total_stock_value DESC;
}}}

==== 1.1. Без индекс ====

Кога се поврзува табелата **warehouse_stock** со табелата **product** преку колоната **product_id** (JOIN), ако нема индекс, базата на податоци ќе ги скенира сите редови.

{{{
EXPLAIN ANALYZE
SELECT w.name, SUM(ws.quantity_on_hand), SUM(ws.quantity_on_hand * p.unit_price)
FROM stock_management.warehouse_stock ws
JOIN stock_management.warehouse w ON ws.warehouse_id = w.warehouse_id
JOIN stock_management.product p ON ws.product_id = p.product_id
GROUP BY w.warehouse_id, w.name;
}}}

{{{
"HashAggregate  (cost=705.70..706.95 rows=100 width=262) (actual time=102.460..102.484 rows=3.00 loops=1)"
"  Group Key: w.warehouse_id"
"  Batches: 1  Memory Usage: 32kB"
"  Buffers: shared hit=164 dirtied=1"
"  ->  Hash Join  (cost=191.75..518.20 rows=15000 width=232) (actual time=4.965..78.081 rows=15000.00 loops=1)"
"        Hash Cond: (ws.product_id = p.product_id)"
"        Buffers: shared hit=164 dirtied=1"
"        ->  Hash Join  (cost=12.25..299.29 rows=15000 width=230) (actual time=0.282..37.808 rows=15000.00 loops=1)"
"              Hash Cond: (ws.warehouse_id = w.warehouse_id)"
"              Buffers: shared hit=97 dirtied=1"
"              ->  Seq Scan on warehouse_stock ws  (cost=0.00..246.00 rows=15000 width=12) (actual time=0.103..20.064 rows=15000.00 loops=1)"
"                    Buffers: shared hit=96"
"              ->  Hash  (cost=11.00..11.00 rows=100 width=222) (actual time=0.068..0.069 rows=3.00 loops=1)"
"                    Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"                    Buffers: shared hit=1 dirtied=1"
"                    ->  Seq Scan on warehouse w  (cost=0.00..11.00 rows=100 width=222) (actual time=0.029..0.032 rows=3.00 loops=1)"
"                          Buffers: shared hit=1 dirtied=1"
"        ->  Hash  (cost=117.00..117.00 rows=5000 width=10) (actual time=4.433..4.444 rows=5000.00 loops=1)"
"              Buckets: 8192  Batches: 1  Memory Usage: 279kB"
"              Buffers: shared hit=67"
"              ->  Seq Scan on product p  (cost=0.00..117.00 rows=5000 width=10) (actual time=0.044..1.930 rows=5000.00 loops=1)"
"                    Buffers: shared hit=67"
"Planning:"
"  Buffers: shared hit=86"
"Planning Time: 26.724 ms"
"Execution Time: 103.080 ms"
}}}

'''Времетраење: 103.080 ms'''

'''Анализа:''' Многу бавно.

==== 1.2. Со индекс ====

За пребарувањето по product_id во табелата warehouse_stock да биде побрзо

'''Применет индекс:'''
{{{
CREATE INDEX idx_wh_stock_product_id ON stock_management.warehouse_stock(product_id);
}}}

{{{
"HashAggregate  (cost=705.70..706.95 rows=100 width=262) (actual time=29.541..29.548 rows=3.00 loops=1)"
"  Group Key: w.warehouse_id"
"  Batches: 1  Memory Usage: 32kB"
"  Buffers: shared hit=164"
"  ->  Hash Join  (cost=191.75..518.20 rows=15000 width=232) (actual time=2.793..18.549 rows=15000.00 loops=1)"
"        Hash Cond: (ws.product_id = p.product_id)"
"        Buffers: shared hit=164"
"        ->  Hash Join  (cost=12.25..299.29 rows=15000 width=230) (actual time=0.060..9.059 rows=15000.00 loops=1)"
"              Hash Cond: (ws.warehouse_id = w.warehouse_id)"
"              Buffers: shared hit=97"
"              ->  Seq Scan on warehouse_stock ws  (cost=0.00..246.00 rows=15000 width=12) (actual time=0.025..1.711 rows=15000.00 loops=1)"
"                    Buffers: shared hit=96"
"              ->  Hash  (cost=11.00..11.00 rows=100 width=222) (actual time=0.019..0.021 rows=3.00 loops=1)"
"                    Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"                    Buffers: shared hit=1"
"                    ->  Seq Scan on warehouse w  (cost=0.00..11.00 rows=100 width=222) (actual time=0.011..0.013 rows=3.00 loops=1)"
"                          Buffers: shared hit=1"
"        ->  Hash  (cost=117.00..117.00 rows=5000 width=10) (actual time=2.710..2.710 rows=5000.00 loops=1)"
"              Buckets: 8192  Batches: 1  Memory Usage: 279kB"
"              Buffers: shared hit=67"
"              ->  Seq Scan on product p  (cost=0.00..117.00 rows=5000 width=10) (actual time=0.010..1.133 rows=5000.00 loops=1)"
"                    Buffers: shared hit=67"
"Planning:"
"  Buffers: shared hit=12"
"Planning Time: 0.761 ms"
"Execution Time: 29.767 ms"
}}}


'''Времетраење: 29.767 ms '''

'''Анализа:''' Многу по брзо.

=== Сценарио 2: Годишен извештај за продажба (последни 12 месеци) ===

Цел: Прикажува детална анализа на продажбата за последните 12 месеци, групирана по месец, складиште, категорија и добавувач.

{{{
SELECT 
    TO_CHAR(date_trunc('month', s.date_time), 'YYYY-MM') AS sales_month,
    w.name AS warehouse_name,
    c.name AS category_name,
    sup.name AS supplier_name,
    COUNT(DISTINCT s.sale_id) AS total_order_count,
    SUM(si.quantity) AS total_units_sold,
    SUM(si.quantity * si.unit_price_at_sale) AS total_gross_revenue
FROM sale s
JOIN sale_item si ON s.sale_id = si.sale_id
JOIN product p ON si.product_id = p.product_id
LEFT JOIN category c ON p.category_id = c.category_id
LEFT JOIN supplier sup ON p.supplier_id = sup.supplier_id
JOIN warehouse w ON s.warehouse_id = w.warehouse_id
WHERE s.date_time >= date_trunc('month', CURRENT_DATE) - INTERVAL '11 months'
GROUP BY sales_month, w.name, c.name, sup.name
ORDER BY sales_month DESC, total_gross_revenue DESC;
}}}

==== 1.1. Без индекс ====
WHERE s.date_time >= Кога во базата има илјадници продажби, за да се најде само последната 1 година потребен е индекс по датум. Во спротивно, ќе се проверуваат податоците од 5 години еден по еден.

{{{
EXPLAIN ANALYZE
SELECT 
    TO_CHAR(date_trunc('month', s.date_time), 'YYYY-MM') AS sales_month,
    w.name AS warehouse_name,
    c.name AS category_name,
    sup.name AS supplier_name,
    COUNT(DISTINCT s.sale_id) AS total_order_count,
    SUM(si.quantity) AS total_units_sold,
    SUM(si.quantity * si.unit_price_at_sale) AS total_gross_revenue
FROM sale s
JOIN sale_item si ON s.sale_id = si.sale_id
JOIN product p ON si.product_id = p.product_id
LEFT JOIN category c ON p.category_id = c.category_id
LEFT JOIN supplier sup ON p.supplier_id = sup.supplier_id
JOIN warehouse w ON s.warehouse_id = w.warehouse_id
WHERE s.date_time >= date_trunc('month', CURRENT_DATE) - INTERVAL '11 months'
GROUP BY sales_month, w.name, c.name, sup.name
ORDER BY sales_month DESC, total_gross_revenue DESC;
}}}

{{{
"Incremental Sort  (cost=18709.65..51808.79 rows=139071 width=321) (actual time=2444.202..7628.612 rows=54435.00 loops=1)"
"  Sort Key: (to_char(date_trunc('month'::text, s.date_time), 'YYYY-MM'::text)) DESC, (sum(((si.quantity)::numeric * si.unit_price_at_sale))) DESC"
"  Presorted Key: (to_char(date_trunc('month'::text, s.date_time), 'YYYY-MM'::text))"
"  Full-sort Groups: 12  Sort Method: quicksort  Average Memory: 30kB  Peak Memory: 30kB"
"  Pre-sorted Groups: 12  Sort Method: quicksort  Average Memory: 568kB  Peak Memory: 615kB"
"  Buffers: shared hit=3632, temp read=1176 written=1179"
"  ->  GroupAggregate  (cost=18708.99..48041.17 rows=139071 width=321) (actual time=2116.416..7458.422 rows=54435.00 loops=1)"
"        Group Key: (to_char(date_trunc('month'::text, s.date_time), 'YYYY-MM'::text)), w.name, c.name, sup.name"
"        Buffers: shared hit=3632, temp read=1176 written=1179"
"        ->  Incremental Sort  (cost=18708.99..42478.33 rows=139071 width=287) (actual time=2116.373..6967.664 rows=137806.00 loops=1)"
"              Sort Key: (to_char(date_trunc('month'::text, s.date_time), 'YYYY-MM'::text)) DESC, w.name, c.name, sup.name, s.sale_id"
"              Presorted Key: (to_char(date_trunc('month'::text, s.date_time), 'YYYY-MM'::text))"
"              Full-sort Groups: 12  Sort Method: quicksort  Average Memory: 30kB  Peak Memory: 30kB"
"              Pre-sorted Groups: 12  Sort Method: quicksort  Average Memory: 1262kB  Peak Memory: 1387kB"
"              Buffers: shared hit=3632, temp read=1176 written=1179"
"              ->  Nested Loop  (cost=18708.54..38710.70 rows=139071 width=287) (actual time=2093.902..2972.887 rows=137806.00 loops=1)"
"                    Buffers: shared hit=3632, temp read=1176 written=1179"
"                    ->  Gather Merge  (cost=18708.38..34558.39 rows=139071 width=49) (actual time=2093.791..2485.802 rows=137806.00 loops=1)"
"                          Workers Planned: 1"
"                          Workers Launched: 1"
"                          Buffers: shared hit=3626, temp read=1176 written=1179"
"                          ->  Sort  (cost=17708.37..17912.89 rows=81806 width=49) (actual time=1937.274..1979.269 rows=68903.00 loops=2)"
"                                Sort Key: (to_char(date_trunc('month'::text, s.date_time), 'YYYY-MM'::text)) DESC"
"                                Sort Method: external merge  Disk: 3672kB"
"                                Buffers: shared hit=3626, temp read=1176 written=1179"
"                                Worker 0:  Sort Method: external merge  Disk: 5736kB"
"                                ->  Hash Left Join  (cost=3417.20..8236.54 rows=81806 width=49) (actual time=374.832..1517.447 rows=68903.00 loops=2)"
"                                      Hash Cond: (p.supplier_id = sup.supplier_id)"
"                                      Buffers: shared hit=3618"
"                                      ->  Hash Left Join  (cost=3415.08..8001.47 rows=81806 width=41) (actual time=372.663..1142.797 rows=68903.00 loops=2)"
"                                            Hash Cond: (p.category_id = c.category_id)"
"                                            Buffers: shared hit=3616"
"                                            ->  Hash Join  (cost=3412.95..7766.40 rows=81806 width=34) (actual time=368.131..1095.264 rows=68903.00 loops=2)"
"                                                  Hash Cond: (si.product_id = p.product_id)"
"                                                  Buffers: shared hit=3614"
"                                                  ->  Parallel Hash Join  (cost=3233.45..7371.99 rows=81806 width=30) (actual time=364.282..986.295 rows=68903.00 loops=2)"
"                                                        Hash Cond: (si.sale_id = s.sale_id)"
"                                                        Buffers: shared hit=3480"
"                                                        ->  Parallel Seq Scan on sale_item si  (cost=0.00..3675.36 rows=176436 width=18) (actual time=0.035..126.327 rows=149971.00 loops=2)"
"                                                              Buffers: shared hit=1911"
"                                                        ->  Parallel Hash  (cost=2892.53..2892.53 rows=27274 width=16) (actual time=358.216..358.220 rows=22972.00 loops=2)"
"                                                              Buckets: 65536  Batches: 1  Memory Usage: 2688kB"
"                                                              Buffers: shared hit=1569"
"                                                              ->  Parallel Seq Scan on sale s  (cost=0.00..2892.53 rows=27274 width=16) (actual time=1.149..298.091 rows=22972.00 loops=2)"
"                                                                    Filter: (date_time >= (date_trunc('month'::text, (CURRENT_DATE)::timestamp with time zone) - '11 mons'::interval))"
"                                                                    Rows Removed by Filter: 27028"
"                                                                    Buffers: shared hit=1569"
"                                                  ->  Hash  (cost=117.00..117.00 rows=5000 width=12) (actual time=3.779..3.780 rows=5000.00 loops=2)"
"                                                        Buckets: 8192  Batches: 1  Memory Usage: 279kB"
"                                                        Buffers: shared hit=134"
"                                                        ->  Seq Scan on product p  (cost=0.00..117.00 rows=5000 width=12) (actual time=0.463..1.723 rows=5000.00 loops=2)"
"                                                              Buffers: shared hit=134"
"                                            ->  Hash  (cost=1.50..1.50 rows=50 width=15) (actual time=4.508..4.509 rows=50.00 loops=2)"
"                                                  Buckets: 1024  Batches: 1  Memory Usage: 11kB"
"                                                  Buffers: shared hit=2"
"                                                  ->  Seq Scan on category c  (cost=0.00..1.50 rows=50 width=15) (actual time=4.441..4.453 rows=50.00 loops=2)"
"                                                        Buffers: shared hit=2"
"                                      ->  Hash  (cost=1.50..1.50 rows=50 width=16) (actual time=1.980..1.982 rows=50.00 loops=2)"
"                                            Buckets: 1024  Batches: 1  Memory Usage: 11kB"
"                                            Buffers: shared hit=2"
"                                            ->  Seq Scan on supplier sup  (cost=0.00..1.50 rows=50 width=16) (actual time=1.936..1.946 rows=50.00 loops=2)"
"                                                  Buffers: shared hit=2"
"                    ->  Memoize  (cost=0.15..0.17 rows=1 width=222) (actual time=0.001..0.001 rows=1.00 loops=137806)"
"                          Cache Key: s.warehouse_id"
"                          Cache Mode: logical"
"                          Hits: 137803  Misses: 3  Evictions: 0  Overflows: 0  Memory Usage: 1kB"
"                          Buffers: shared hit=6"
"                          ->  Index Scan using warehouse_pkey on warehouse w  (cost=0.14..0.16 rows=1 width=222) (actual time=0.026..0.026 rows=1.00 loops=3)"
"                                Index Cond: (warehouse_id = s.warehouse_id)"
"                                Index Searches: 3"
"                                Buffers: shared hit=6"
"Planning:"
"  Buffers: shared hit=36"
"Planning Time: 3.208 ms"
"Execution Time: 7656.576 ms"
}}}

'''Времетраење: 7656.576 ms'''

'''Анализа:''' Многу бавно.

==== 1.2. Со индекс ====

За да се забрзаат прашањата со временски опсег (WHERE date_time > ...)
'''Применет индекс:'''
{{{CREATE INDEX idx_sale_date_time ON stock_management.sale(date_time DESC);}}}

Индекси на Foreign Key за подобрување на перформансите на JOIN
'''Применет индекс:'''
{{{
CREATE INDEX idx_sale_warehouse_id ON stock_management.sale(warehouse_id);
CREATE INDEX idx_product_supplier_id ON stock_management.product(supplier_id);
}}}

{{{
"Incremental Sort  (cost=18709.65..51808.79 rows=139071 width=321) (actual time=1599.321..4115.097 rows=54435.00 loops=1)"
"  Sort Key: (to_char(date_trunc('month'::text, s.date_time), 'YYYY-MM'::text)) DESC, (sum(((si.quantity)::numeric * si.unit_price_at_sale))) DESC"
"  Presorted Key: (to_char(date_trunc('month'::text, s.date_time), 'YYYY-MM'::text))"
"  Full-sort Groups: 12  Sort Method: quicksort  Average Memory: 30kB  Peak Memory: 30kB"
"  Pre-sorted Groups: 12  Sort Method: quicksort  Average Memory: 568kB  Peak Memory: 615kB"
"  Buffers: shared hit=3632, temp read=1176 written=1178"
"  ->  GroupAggregate  (cost=18708.99..48041.17 rows=139071 width=321) (actual time=1209.156..4037.823 rows=54435.00 loops=1)"
"        Group Key: (to_char(date_trunc('month'::text, s.date_time), 'YYYY-MM'::text)), w.name, c.name, sup.name"
"        Buffers: shared hit=3632, temp read=1176 written=1178"
"        ->  Incremental Sort  (cost=18708.99..42478.33 rows=139071 width=287) (actual time=1208.198..3762.995 rows=137806.00 loops=1)"
"              Sort Key: (to_char(date_trunc('month'::text, s.date_time), 'YYYY-MM'::text)) DESC, w.name, c.name, sup.name, s.sale_id"
"              Presorted Key: (to_char(date_trunc('month'::text, s.date_time), 'YYYY-MM'::text))"
"              Full-sort Groups: 12  Sort Method: quicksort  Average Memory: 30kB  Peak Memory: 30kB"
"              Pre-sorted Groups: 12  Sort Method: quicksort  Average Memory: 1262kB  Peak Memory: 1387kB"
"              Buffers: shared hit=3632, temp read=1176 written=1178"
"              ->  Nested Loop  (cost=18708.54..38710.70 rows=139071 width=287) (actual time=1201.290..1877.549 rows=137806.00 loops=1)"
"                    Buffers: shared hit=3632, temp read=1176 written=1178"
"                    ->  Gather Merge  (cost=18708.38..34558.39 rows=139071 width=49) (actual time=1200.687..1491.543 rows=137806.00 loops=1)"
"                          Workers Planned: 1"
"                          Workers Launched: 1"
"                          Buffers: shared hit=3626, temp read=1176 written=1178"
"                          ->  Sort  (cost=17708.37..17912.89 rows=81806 width=49) (actual time=1024.503..1057.136 rows=68903.00 loops=2)"
"                                Sort Key: (to_char(date_trunc('month'::text, s.date_time), 'YYYY-MM'::text)) DESC"
"                                Sort Method: external merge  Disk: 4016kB"
"                                Buffers: shared hit=3626, temp read=1176 written=1178"
"                                Worker 0:  Sort Method: external merge  Disk: 5392kB"
"                                ->  Hash Left Join  (cost=3417.20..8236.54 rows=81806 width=49) (actual time=255.672..807.691 rows=68903.00 loops=2)"
"                                      Hash Cond: (p.supplier_id = sup.supplier_id)"
"                                      Buffers: shared hit=3618"
"                                      ->  Hash Left Join  (cost=3415.08..8001.47 rows=81806 width=41) (actual time=252.428..677.672 rows=68903.00 loops=2)"
"                                            Hash Cond: (p.category_id = c.category_id)"
"                                            Buffers: shared hit=3616"
"                                            ->  Hash Join  (cost=3412.95..7766.40 rows=81806 width=34) (actual time=244.604..579.219 rows=68903.00 loops=2)"
"                                                  Hash Cond: (si.product_id = p.product_id)"
"                                                  Buffers: shared hit=3614"
"                                                  ->  Parallel Hash Join  (cost=3233.45..7371.99 rows=81806 width=30) (actual time=233.780..521.518 rows=68903.00 loops=2)"
"                                                        Hash Cond: (si.sale_id = s.sale_id)"
"                                                        Buffers: shared hit=3480"
"                                                        ->  Parallel Seq Scan on sale_item si  (cost=0.00..3675.36 rows=176436 width=18) (actual time=0.059..150.696 rows=149971.00 loops=2)"
"                                                              Buffers: shared hit=1911"
"                                                        ->  Parallel Hash  (cost=2892.53..2892.53 rows=27274 width=16) (actual time=233.129..233.132 rows=22972.00 loops=2)"
"                                                              Buckets: 65536  Batches: 1  Memory Usage: 2688kB"
"                                                              Buffers: shared hit=1569"
"                                                              ->  Parallel Seq Scan on sale s  (cost=0.00..2892.53 rows=27274 width=16) (actual time=43.247..214.762 rows=22972.00 loops=2)"
"                                                                    Filter: (date_time >= (date_trunc('month'::text, (CURRENT_DATE)::timestamp with time zone) - '11 mons'::interval))"
"                                                                    Rows Removed by Filter: 27028"
"                                                                    Buffers: shared hit=1569"
"                                                  ->  Hash  (cost=117.00..117.00 rows=5000 width=12) (actual time=10.740..10.741 rows=5000.00 loops=2)"
"                                                        Buckets: 8192  Batches: 1  Memory Usage: 279kB"
"                                                        Buffers: shared hit=134"
"                                                        ->  Seq Scan on product p  (cost=0.00..117.00 rows=5000 width=12) (actual time=0.680..9.123 rows=5000.00 loops=2)"
"                                                              Buffers: shared hit=134"
"                                            ->  Hash  (cost=1.50..1.50 rows=50 width=15) (actual time=7.694..7.695 rows=50.00 loops=2)"
"                                                  Buckets: 1024  Batches: 1  Memory Usage: 11kB"
"                                                  Buffers: shared hit=2"
"                                                  ->  Seq Scan on category c  (cost=0.00..1.50 rows=50 width=15) (actual time=7.644..7.651 rows=50.00 loops=2)"
"                                                        Buffers: shared hit=2"
"                                      ->  Hash  (cost=1.50..1.50 rows=50 width=16) (actual time=2.823..2.824 rows=50.00 loops=2)"
"                                            Buckets: 1024  Batches: 1  Memory Usage: 11kB"
"                                            Buffers: shared hit=2"
"                                            ->  Seq Scan on supplier sup  (cost=0.00..1.50 rows=50 width=16) (actual time=2.792..2.798 rows=50.00 loops=2)"
"                                                  Buffers: shared hit=2"
"                    ->  Memoize  (cost=0.15..0.17 rows=1 width=222) (actual time=0.000..0.000 rows=1.00 loops=137806)"
"                          Cache Key: s.warehouse_id"
"                          Cache Mode: logical"
"                          Hits: 137803  Misses: 3  Evictions: 0  Overflows: 0  Memory Usage: 1kB"
"                          Buffers: shared hit=6"
"                          ->  Index Scan using warehouse_pkey on warehouse w  (cost=0.14..0.16 rows=1 width=222) (actual time=0.035..0.035 rows=1.00 loops=3)"
"                                Index Cond: (warehouse_id = s.warehouse_id)"
"                                Index Searches: 3"
"                                Buffers: shared hit=6"
"Planning:"
"  Buffers: shared hit=36"
"Planning Time: 20.237 ms"
"Execution Time: 4141.771 ms"
}}}

'''Времетраење: 4141.771 ms'''

'''Анализа:''' По добро.