wiki:OtherTopics

Context Navigation

Version 22 (modified by 221181, 3 weeks ago) ( diff )
--

Други теми

Безбедност

Спречување на 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"

Времетраење: 103.080 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=1502.222..5150.389 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=1177 written=1180"
"  ->  GroupAggregate  (cost=18708.99..48041.17 rows=139071 width=321) (actual time=1244.690..4960.802 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=1177 written=1180"
"        ->  Incremental Sort  (cost=18708.99..42478.33 rows=139071 width=287) (actual time=1244.650..4576.663 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=1177 written=1180"
"              ->  Nested Loop  (cost=18708.54..38710.70 rows=139071 width=287) (actual time=1218.041..1921.274 rows=137806.00 loops=1)"
"                    Buffers: shared hit=3632, temp read=1177 written=1180"
"                    ->  Gather Merge  (cost=18708.38..34558.39 rows=139071 width=49) (actual time=1217.896..1527.876 rows=137806.00 loops=1)"
"                          Workers Planned: 1"
"                          Workers Launched: 1"
"                          Buffers: shared hit=3626, temp read=1177 written=1180"
"                          ->  Sort  (cost=17708.37..17912.89 rows=81806 width=49) (actual time=1035.053..1072.735 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: 3808kB"
"                                Buffers: shared hit=3626, temp read=1177 written=1180"
"                                Worker 0:  Sort Method: external merge  Disk: 5608kB"
"                                ->  Hash Left Join  (cost=3417.20..8236.54 rows=81806 width=49) (actual time=187.902..755.631 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=187.231..600.377 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=186.809..561.244 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=178.945..485.528 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.033..63.853 rows=149971.00 loops=2)"
"                                                              Buffers: shared hit=1911"
"                                                        ->  Parallel Hash  (cost=2892.53..2892.53 rows=27274 width=16) (actual time=177.467..177.471 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=0.572..152.678 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=7.722..7.723 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=5.217..6.503 rows=5000.00 loops=2)"
"                                                              Buffers: shared hit=134"
"                                            ->  Hash  (cost=1.50..1.50 rows=50 width=15) (actual time=0.402..0.403 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=0.367..0.375 rows=50.00 loops=2)"
"                                                        Buffers: shared hit=2"
"                                      ->  Hash  (cost=1.50..1.50 rows=50 width=16) (actual time=0.511..0.512 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=0.479..0.486 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.029..0.029 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.051 ms"
"Execution Time: 5241.032 ms"

Attachments (4)

with_index_1.png (26.7 KB ) - added by 221181 3 weeks ago.
with_index_2.png (48.8 KB ) - added by 221181 3 weeks ago.
without_index_1.png (34.6 KB ) - added by 221181 3 weeks ago.
without_index_2.png (32.8 KB ) - added by 221181 3 weeks ago.

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text