Changes between Version 1 and Version 2 of OrdersFullView


Ignore:
Timestamp:
09/14/26 00:39:48 (2 weeks ago)
Author:
223235
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • OrdersFullView

    v1 v2  
    11= Преглед: v_orders_full =
    22
    3 ||= Датотека ||= `views/02_orders_full_view.sql` ||
    4 ||= Шема ||= `kbnteam` ||
    5 ||= Категорија ||= Нарачки и Достави ||
    6 ||= Поврзани индекси ||= `indexes/v_orders_full_index.sql` ||
    7 ||= Сложеност ||= Висока — 2 CTE + 12 табели ||
     3||= Својство ||= Вредност ||
     4|| Шема || `kbnteam` ||
     5|| Категорија || Нарачки и достави ||
     6|| Поврзани индекси || [wiki:OrdersFullIndex Индекси за нарачки и достави] ||
    87
    98== Опис ==
    10 Примарен оперативен преглед за нарачки во веб апликацијата. Ги прикажува целосните детали за нарачка: контекст на купувач/компанија, статус на нарачка, контекст на достава и агрегирани листи на нарачани оброци и пијачи. Двете CTE (`order_meals`, `order_drinks`) ги собираат ставките по нарачка во читливи стрингови.
    11 
    12 '''Важно:''' Овој преглед е еден од најтешките во шемата. Никогаш не го извршувајте со `SELECT *` без WHERE клаузула при бенчмарк тестирање.
     9
     10Прикажува нарачки со податоци за клиентот, компанијата, статусот, доставата и нарачаните јадења и пијалоци.
     11
     12Јадењата и пијалоците се прикажуваат во подредени листи без повторувања. Податоците за доставувачот се поврзани со доставата. Се прикажуваат и нарачките за кои сè уште нема достава.
     13
     14Еден ред за секоја клиентска нарачка. Недостасувачките податоци за достава се NULL, а празните листи со производи се празен текст.
    1315
    1416== Зависности ==
    15 ||= Табела ||= Тип на употреба ||
    16 || `kbnteam.customer_order` || Главна табела (нарачка) ||
    17 || `kbnteam.order_status` || JOIN — статус на нарачката ||
    18 || `kbnteam.customer` || JOIN — купувач ||
    19 || `kbnteam.api_user` || JOIN (×2) — детали за купувач и возач ||
    20 || `kbnteam.company_order` || JOIN — компаниска нарачка ||
    21 || `kbnteam.company` || JOIN — компанија ||
    22 || `kbnteam.delivery` || LEFT JOIN — достава ||
    23 || `kbnteam.delivery_status` || LEFT JOIN — статус на достава ||
    24 || `kbnteam.driver` || LEFT JOIN — возач ||
    25 || `kbnteam.order_meal` || LEFT JOIN (CTE) — оброци во нарачката ||
    26 || `kbnteam.meal` || JOIN (CTE) — назив на оброк ||
    27 || `kbnteam.order_drink` || LEFT JOIN (CTE) — пијачи во нарачката ||
    28 || `kbnteam.drink` || JOIN (CTE) — назив на пијач ||
     17
     18||= Табела / преглед ||= Употреба ||
     19|| `kbnteam.order_meal` || Јадења вклучени во нарачките. ||
     20|| `kbnteam.meal` || Јадења, цени и поврзаност со категорија и ресторан. ||
     21|| `kbnteam.order_drink` || Пијалоци вклучени во нарачките. ||
     22|| `kbnteam.drink` || Пијалоци, количини, цени и ресторан. ||
     23|| `kbnteam.customer_order` || Клиентски нарачки, датуми, статуси и износи. ||
     24|| `kbnteam.order_status` || Називи на статусите на нарачките. ||
     25|| `kbnteam.customer` || Клиенти и компаниите на кои припаѓаат. ||
     26|| `kbnteam.api_user` || Лични и контактни податоци за корисниците. ||
     27|| `kbnteam.company_order` || Поврзување на компаниските нарачки со компанијата и доставата. ||
     28|| `kbnteam.company` || Податоци за компаниите. ||
     29|| `kbnteam.delivery` || Достави, датуми, забелешки и назначени доставувачи. ||
     30|| `kbnteam.delivery_status` || Називи на статусите на доставите. ||
    2931
    3032== Излезни колони ==
    31 ||= Колона ||= Извор ||= Опис ||
    32 || `order_id` || `customer_order` || Примарен клуч на нарачката ||
    33 || `order_datetime` || `customer_order` || Датум и час на нарачката ||
    34 || `order_total` || `customer_order` || Вкупна цена ||
    35 || `o_status_name` || `order_status` || Статус на нарачката ||
    36 || `customer_user_id` || `customer` || ID на купувачот ||
    37 || `customer_company_id` || `customer` || ID на компанијата на купувачот ||
    38 || `customer_first_name` || `api_user` || Ime на купувачот ||
    39 || `customer_last_name` || `api_user` || Презиме на купувачот ||
    40 || `customer_email` || `api_user` || Е-пошта на купувачот ||
    41 || `comp_order_id` || `company_order` || ID на компаниската нарачка ||
    42 || `order_company_id` || `company_order` || ID на компанијата ||
    43 || `company_name` || `company` || Назив на компанијата ||
    44 || `delivery_id` || `delivery` || ID на доставата (NULL ако нема) ||
    45 || `delivery_date` || `delivery` || Датум на достава ||
    46 || `d_status_name` || `delivery_status` || Статус на достава ||
    47 || `driver_user_id` || `driver` || ID на возачот ||
    48 || `driver_first_name` || `api_user` || Ime на возачот ||
    49 || `driver_last_name` || `api_user` || Презиме на возачот ||
    50 || `driver_phone` || `api_user` || Телефон на возачот ||
    51 || `meals` || CTE `order_meals` || Оброци одвоени со запирка ||
    52 || `drinks` || CTE `order_drinks` || Пијачи одвоени со запирка ||
    53 
    54 == SQL Дефиниција ==
     33
     34||= Колона ||= Извор / пресметка ||= Опис ||
     35|| `order_id` || `customer_order.order_id` || Идентификатор на клиентската нарачка. ||
     36|| `order_datetime` || `customer_order.order_datetime` || Датум и време на нарачката. ||
     37|| `order_total` || `customer_order.order_total` || Вкупен износ на нарачката. ||
     38|| `o_status_name` || `order_status.o_status_name` || Статус на нарачката. ||
     39|| `customer_user_id` || `customer.user_id` || Идентификатор на клиентот. ||
     40|| `customer_company_id` || `customer.company_id` || Компанија на клиентот. ||
     41|| `customer_first_name` || `api_user.user_first_name` || Име на клиентот. ||
     42|| `customer_last_name` || `api_user.user_last_name` || Презиме на клиентот. ||
     43|| `customer_email` || `api_user.user_email` || Е-пошта на клиентот. ||
     44|| `comp_order_id` || `company_order.comp_order_id` || Идентификатор на компаниската нарачка. ||
     45|| `order_company_id` || `company_order.company_id` || Компанија на компаниската нарачка. ||
     46|| `company_name` || `company.company_name` || Назив на компанијата. ||
     47|| `delivery_id` || `delivery.delivery_id` || Идентификатор на доставата. ||
     48|| `delivery_date` || `delivery.delivery_date` || Датум на доставата. ||
     49|| `d_status_name` || `delivery_status.d_status_name` || Статус на доставата. ||
     50|| `driver_user_id` || `delivery.driver_user_id` || Идентификатор на доставувачот. ||
     51|| `driver_first_name` || `api_user.user_first_name` || Име на доставувачот. ||
     52|| `driver_last_name` || `api_user.user_last_name` || Презиме на доставувачот. ||
     53|| `driver_phone` || `api_user.user_phone_no` || Телефон на доставувачот. ||
     54|| `meals` || `COALESCE(order_meals.meals, '')` || Подредена листа со називи на нарачаните јадења. ||
     55|| `drinks` || `COALESCE(order_drinks.drinks, '')` || Подредена листа со називи на нарачаните пијалоци. ||
     56
     57== SQL дефиниција ==
     58
    5559{{{
    5660#!sql
    … …  
    5862WITH order_meals AS (
    5963    SELECT
    60         x.order_id,
    61         string_agg(x.meal_name, ', ' ORDER BY x.meal_name) AS meals
    62     FROM (
    63         SELECT DISTINCT
    64             om.order_id,
    65             m.meal_name
    66         FROM kbnteam.order_meal om
    67         JOIN kbnteam.meal m ON m.meal_id = om.meal_id
    68     ) x
    69     GROUP BY x.order_id
     64        om.order_id,
     65        string_agg(DISTINCT m.meal_name, ', ' ORDER BY m.meal_name) AS meals
     66    FROM kbnteam.order_meal om
     67    JOIN kbnteam.meal m ON m.meal_id = om.meal_id
     68    GROUP BY om.order_id
    7069),
    7170order_drinks AS (
    7271    SELECT
    73         x.order_id,
    74         string_agg(x.drink_name, ', ' ORDER BY x.drink_name) AS drinks
    75     FROM (
    76         SELECT DISTINCT
    77             od.order_id,
    78             d.drink_name
    79         FROM kbnteam.order_drink od
    80         JOIN kbnteam.drink d ON d.drink_id = od.drink_id
    81     ) x
    82     GROUP BY x.order_id
     72        od.order_id,
     73        string_agg(DISTINCT d.drink_name, ', ' ORDER BY d.drink_name) AS drinks
     74    FROM kbnteam.order_drink od
     75    JOIN kbnteam.drink d ON d.drink_id = od.drink_id
     76    GROUP BY od.order_id
    8377)
    8478SELECT
    … …  
    9892    d.delivery_date,
    9993    ds.d_status_name,
    100     drv.user_id AS driver_user_id,
     94    d.driver_user_id,
    10195    du.user_first_name AS driver_first_name,
    10296    du.user_last_name AS driver_last_name,
    … …  
    112106LEFT JOIN kbnteam.delivery d ON d.delivery_id = co.delivery_id
    113107LEFT JOIN kbnteam.delivery_status ds ON ds.d_status_id = d.d_status_id
    114 LEFT JOIN kbnteam.driver drv ON drv.user_id = d.driver_user_id
    115 LEFT JOIN kbnteam.api_user du ON du.user_id = drv.user_id
     108LEFT JOIN kbnteam.api_user du ON du.user_id = d.driver_user_id
    116109LEFT JOIN order_meals om ON om.order_id = o.order_id
    117110LEFT JOIN order_drinks od ON od.order_id = o.order_id;
    … …  
    120113== Тестирање на перформанси ==
    121114
    122 === Препорачано тест прашање ===
    123 {{{
    124 #!sql
    125 SET search_path TO kbnteam;
    126 SET statement_timeout = '60s';
    127 
    128 -- Тест 1: последни 30 дена (временски опсег — тестира idx по datetime)
     115Примерите користат илустративни идентификатори и датуми. За споредливи мерења се избираат вредности што постојат во базата и се задржуваат истите податоци, филтри, услови за кеширање и број на повторувања. Статистиките за табелите треба да бидат ажурирани во двете состојби.
     116
     117=== Тест прашалници ===
     118
     119{{{
     120#!sql
     121BEGIN READ ONLY;
     122SET LOCAL statement_timeout = '60s';
     123
     124-- Тест 1: по клиент и датум
    129125EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
    130126SELECT * FROM kbnteam.v_orders_full
    131 WHERE order_datetime >= CURRENT_DATE - INTERVAL '30 days';
    132 
    133 -- Тест 2: по купувач (тестира idx по customer_user_id)
    134 EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
    135 SELECT * FROM kbnteam.v_orders_full
    136 WHERE customer_user_id = 1;
    137 
    138 -- Тест 3: по компаниска нарачка (тестира idx по comp_order_id)
     127WHERE customer_user_id = 1
     128  AND order_datetime >= DATE '2026-09-01'
     129  AND order_datetime < DATE '2026-10-01';
     130
     131-- Тест 2: по компаниска нарачка
    139132EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
    140133SELECT * FROM kbnteam.v_orders_full
    141134WHERE comp_order_id = 1;
    142 }}}
     135
     136-- Тест 3: по компанија
     137EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
     138SELECT * FROM kbnteam.v_orders_full
     139WHERE order_company_id = 1;
     140
     141ROLLBACK;
     142}}}
     143
     144Индексот по клиент и датум е наменет за пристап во рамки на конкретен клиент. Самостоен услов по датум нема ист начин на пристап како услов што ја задава и водечката колона customer_user_id.
    143145
    144146=== Резултати пред индексирање ===
    145 Извршете ги горните прашања '''пред''' да ја примените датотеката `indexes/v_orders_full_index.sql`.
    146 
    147 ||= Метрика ||= Тест 1 (datetime) ||= Тест 2 (customer) ||= Тест 3 (comp_order) ||
     147
     148Основната состојба ги задржува индексите на примарните клучеви и уникатните ограничувања. Останатите присутни индекси се евидентираат одделно. При споредба на заеднички индекси се користи истата почетна состојба за секој преглед.
     149
     150||= Метрика ||= Тест 1 (по клиент и датум) ||= Тест 2 (по компаниска нарачка) ||= Тест 3 (по компанија) ||
    148151|| Planning Time || ___ ms || ___ ms || ___ ms ||
    149152|| Execution Time || ___ ms || ___ ms || ___ ms ||
    150153|| Rows Returned || ___ || ___ || ___ ||
    151 || customer_order scan || ___ || ___ || ___ ||
    152 || order_meal scan || ___ || ___ || ___ ||
    153 || delivery scan || ___ || ___ || ___ ||
    154 
    155 {{{
    156 -- Излезот од EXPLAIN ANALYZE овде (пред индексирање)
    157 }}}
    158 
    159 === Применети индекси ===
    160 {{{
    161 #!sql
    162 -- indexes/v_orders_full_index.sql
     154|| Начин на читање по табела || ___ || ___ || ___ ||
     155|| Shared Hit Blocks || ___ || ___ || ___ ||
     156|| Shared Read Blocks || ___ || ___ || ___ ||
     157
     158{{{
     159-- Планови и резултати пред дополнителното индексирање:
     160
     161}}}
     162
     163=== Дополнителни и заеднички индекси ===
     164
     165Примарните клучеви ги покриваат идентификаторите, а `company_order(delivery_id)` има уникатен индекс.
     166
     167||= Индекс ||= Табела и колони ||= Намена ||
     168|| `idx_company_order_company_id` || `company_order(company_id)` || Пронаоѓање компаниски нарачки по компанија. ||
     169|| `idx_customer_order_comp_order_id` || `customer_order(comp_order_id)` || Пронаоѓање клиентски нарачки по компаниска нарачка. ||
     170|| `idx_order_meal_order_id_meal_id` || `order_meal(order_id, meal_id)` || Пристап до јадењата на нарачката и групирање по order_id. ||
     171|| `idx_order_drink_order_id_drink_id` || `order_drink(order_id, drink_id)` || Пристап до пијалоците на нарачката и групирање по order_id. ||
     172|| `idx_delivery_driver_user_id_delivery_date` || `delivery(driver_user_id, delivery_date)` || Пронаоѓање достави по доставувач; во прегледите за нарачки и достави и по датум. ||
     173|| `idx_customer_order_customer_user_id_order_datetime` || `customer_order(customer_user_id, order_datetime DESC)` || Пронаоѓање нарачки по клиент; поддршка за пристап по датум во рамки на клиентот. ||
     174|| `idx_customer_company_id` || `customer(company_id)` || Пронаоѓање клиенти по компанијата на која припаѓаат. ||
     175
     176{{{
     177#!sql
     178CREATE INDEX IF NOT EXISTS idx_company_order_company_id
     179ON kbnteam.company_order (company_id);
     180
    163181CREATE INDEX IF NOT EXISTS idx_customer_order_comp_order_id
    164182ON kbnteam.customer_order (comp_order_id);
    165183
     184CREATE INDEX IF NOT EXISTS idx_order_meal_order_id_meal_id
     185ON kbnteam.order_meal (order_id, meal_id);
     186
     187CREATE INDEX IF NOT EXISTS idx_order_drink_order_id_drink_id
     188ON kbnteam.order_drink (order_id, drink_id);
     189
     190CREATE INDEX IF NOT EXISTS idx_delivery_driver_user_id_delivery_date
     191ON kbnteam.delivery (driver_user_id, delivery_date);
     192
    166193CREATE INDEX IF NOT EXISTS idx_customer_order_customer_user_id_order_datetime
    167194ON kbnteam.customer_order (customer_user_id, order_datetime DESC);
    168195
    169 CREATE INDEX IF NOT EXISTS idx_delivery_driver_user_id_delivery_date
    170 ON kbnteam.delivery (driver_user_id, delivery_date);
    171 
    172 CREATE INDEX IF NOT EXISTS idx_order_meal_order_id_meal_id
    173 ON kbnteam.order_meal (order_id, meal_id);
    174 
    175 CREATE INDEX IF NOT EXISTS idx_order_drink_order_id_drink_id
    176 ON kbnteam.order_drink (order_id, drink_id);
     196CREATE INDEX IF NOT EXISTS idx_customer_company_id
     197ON kbnteam.customer (company_id);
     198}}}
     199
     200=== Проверка на индексите ===
     201
     202{{{
     203#!sql
     204SELECT tablename, indexname, indexdef
     205FROM pg_indexes
     206WHERE schemaname = 'kbnteam'
     207  AND indexname IN (
     208    'idx_company_order_company_id',
     209    'idx_customer_order_comp_order_id',
     210    'idx_order_meal_order_id_meal_id',
     211    'idx_order_drink_order_id_drink_id',
     212    'idx_delivery_driver_user_id_delivery_date',
     213    'idx_customer_order_customer_user_id_order_datetime',
     214    'idx_customer_company_id'
     215)
     216ORDER BY tablename, indexname;
    177217}}}
    178218
    179219=== Резултати по индексирање ===
    180 Извршете ги истите прашања '''по''' примена на `indexes/v_orders_full_index.sql`.
    181 
    182 ||= Метрика ||= Тест 1 (datetime) ||= Тест 2 (customer) ||= Тест 3 (comp_order) ||
     220
     221Мерењето ги користи истите тест прашалници и услови како почетното мерење.
     222
     223||= Метрика ||= Тест 1 (по клиент и датум) ||= Тест 2 (по компаниска нарачка) ||= Тест 3 (по компанија) ||
    183224|| Planning Time || ___ ms || ___ ms || ___ ms ||
    184225|| Execution Time || ___ ms || ___ ms || ___ ms ||
    185226|| Rows Returned || ___ || ___ || ___ ||
    186 || customer_order scan || ___ || ___ || ___ ||
    187 || order_meal scan || ___ || ___ || ___ ||
    188 || delivery scan || ___ || ___ || ___ ||
    189 
    190 {{{
    191 -- Излезот од EXPLAIN ANALYZE овде (по индексирање)
     227|| Начин на читање по табела || ___ || ___ || ___ ||
     228|| Shared Hit Blocks || ___ || ___ || ___ ||
     229|| Shared Read Blocks || ___ || ___ || ___ ||
     230
     231{{{
     232-- Планови и резултати по дополнителното индексирање:
     233
    192234}}}
    193235
    194236=== Анализа на подобрување ===
    195 ||= Индекс ||= Помага на ||= Очекувана промена ||
    196 || `idx_customer_order_customer_user_id_order_datetime` || Тест 1 и Тест 2 || Seq Scan → Index Scan на `customer_order` ||
    197 || `idx_customer_order_comp_order_id` || Тест 3 и JOIN со `company_order` || Seq Scan → Index Scan на `customer_order` ||
    198 || `idx_order_meal_order_id_meal_id` || CTE `order_meals` || Seq Scan → Index Scan на `order_meal` ||
    199 || `idx_order_drink_order_id_drink_id` || CTE `order_drinks` || Seq Scan → Index Scan на `order_drink` ||
    200 || `idx_delivery_driver_user_id_delivery_date` || LEFT JOIN на `delivery` || Seq Scan → Index Scan на `delivery` ||
    201 
    202 ||= Метрика ||= Пред ||= По ||= Δ Подобрување ||
     237
     238||= Метрика ||= Пред ||= По ||= Промена (%) ||
    203239|| Execution Time (Тест 1) || ___ ms || ___ ms || ___ % ||
    204240|| Execution Time (Тест 2) || ___ ms || ___ ms || ___ % ||
    205241|| Execution Time (Тест 3) || ___ ms || ___ ms || ___ % ||
     242
     243Промената се пресметува како 100 × (време пред − време по) / време пред, кога почетното време е поголемо од нула. Негативна вредност означува побавно извршување.
     244
     245Изборот меѓу секвенцијално читање, индексно читање и други планови зависи од обемот на податоците, селективноста на филтрите и статистиките. Индексите не гарантираат забрзување или отстранување на сортирањата и спојувањата. Плановите и времињата се утврдуваат со мерење.