Changes between Initial Version and Version 1 of OrdersFullView

Timestamp:

05/10/26 14:28:03 (2 weeks ago)

Author:

185022

Comment:

--

OrdersFullView

@@ +1 @@
+= Преглед: v_orders_full =
+
+||= Датотека ||= `views/02_orders_full_view.sql` ||
+||= Шема ||= `kbnteam` ||
+||= Категорија ||= Нарачки и Достави ||
+||= Поврзани индекси ||= `indexes/v_orders_full_index.sql` ||
+||= Сложеност ||= Висока — 2 CTE + 12 табели ||
+
+== Опис ==
+Примарен оперативен преглед за нарачки во веб апликацијата. Ги прикажува целосните детали за нарачка: контекст на купувач/компанија, статус на нарачка, контекст на достава и агрегирани листи на нарачани оброци и пијачи. Двете CTE (`order_meals`, `order_drinks`) ги собираат ставките по нарачка во читливи стрингови.
+
+'''Важно:''' Овој преглед е еден од најтешките во шемата. Никогаш не го извршувајте со `SELECT *` без WHERE клаузула при бенчмарк тестирање.
+
+== Зависности ==
+||= Табела ||= Тип на употреба ||
+|| `kbnteam.customer_order` || Главна табела (нарачка) ||
+|| `kbnteam.order_status` || JOIN — статус на нарачката ||
+|| `kbnteam.customer` || JOIN — купувач ||
+|| `kbnteam.api_user` || JOIN (×2) — детали за купувач и возач ||
+|| `kbnteam.company_order` || JOIN — компаниска нарачка ||
+|| `kbnteam.company` || JOIN — компанија ||
+|| `kbnteam.delivery` || LEFT JOIN — достава ||
+|| `kbnteam.delivery_status` || LEFT JOIN — статус на достава ||
+|| `kbnteam.driver` || LEFT JOIN — возач ||
+|| `kbnteam.order_meal` || LEFT JOIN (CTE) — оброци во нарачката ||
+|| `kbnteam.meal` || JOIN (CTE) — назив на оброк ||
+|| `kbnteam.order_drink` || LEFT JOIN (CTE) — пијачи во нарачката ||
+|| `kbnteam.drink` || JOIN (CTE) — назив на пијач ||
+
+== Излезни колони ==
+||= Колона ||= Извор ||= Опис ||
+|| `order_id` || `customer_order` || Примарен клуч на нарачката ||
+|| `order_datetime` || `customer_order` || Датум и час на нарачката ||
+|| `order_total` || `customer_order` || Вкупна цена ||
+|| `o_status_name` || `order_status` || Статус на нарачката ||
+|| `customer_user_id` || `customer` || ID на купувачот ||
+|| `customer_company_id` || `customer` || ID на компанијата на купувачот ||
+|| `customer_first_name` || `api_user` || Ime на купувачот ||
+|| `customer_last_name` || `api_user` || Презиме на купувачот ||
+|| `customer_email` || `api_user` || Е-пошта на купувачот ||
+|| `comp_order_id` || `company_order` || ID на компаниската нарачка ||
+|| `order_company_id` || `company_order` || ID на компанијата ||
+|| `company_name` || `company` || Назив на компанијата ||
+|| `delivery_id` || `delivery` || ID на доставата (NULL ако нема) ||
+|| `delivery_date` || `delivery` || Датум на достава ||
+|| `d_status_name` || `delivery_status` || Статус на достава ||
+|| `driver_user_id` || `driver` || ID на возачот ||
+|| `driver_first_name` || `api_user` || Ime на возачот ||
+|| `driver_last_name` || `api_user` || Презиме на возачот ||
+|| `driver_phone` || `api_user` || Телефон на возачот ||
+|| `meals` || CTE `order_meals` || Оброци одвоени со запирка ||
+|| `drinks` || CTE `order_drinks` || Пијачи одвоени со запирка ||
+
+== SQL Дефиниција ==
+{{{
+#!sql
+CREATE OR REPLACE VIEW kbnteam.v_orders_full AS
+WITH order_meals AS (
+    SELECT
+        x.order_id,
+        string_agg(x.meal_name, ', ' ORDER BY x.meal_name) AS meals
+    FROM (
+        SELECT DISTINCT
+            om.order_id,
+            m.meal_name
+        FROM kbnteam.order_meal om
+        JOIN kbnteam.meal m ON m.meal_id = om.meal_id
+    ) x
+    GROUP BY x.order_id
+),
+order_drinks AS (
+    SELECT
+        x.order_id,
+        string_agg(x.drink_name, ', ' ORDER BY x.drink_name) AS drinks
+    FROM (
+        SELECT DISTINCT
+            od.order_id,
+            d.drink_name
+        FROM kbnteam.order_drink od
+        JOIN kbnteam.drink d ON d.drink_id = od.drink_id
+    ) x
+    GROUP BY x.order_id
+)
+SELECT
+    o.order_id,
+    o.order_datetime,
+    o.order_total,
+    os.o_status_name,
+    cu.user_id AS customer_user_id,
+    cu.company_id AS customer_company_id,
+    au.user_first_name AS customer_first_name,
+    au.user_last_name AS customer_last_name,
+    au.user_email AS customer_email,
+    co.comp_order_id,
+    co.company_id AS order_company_id,
+    cmp.company_name,
+    d.delivery_id,
+    d.delivery_date,
+    ds.d_status_name,
+    drv.user_id AS driver_user_id,
+    du.user_first_name AS driver_first_name,
+    du.user_last_name AS driver_last_name,
+    du.user_phone_no AS driver_phone,
+    COALESCE(om.meals, '') AS meals,
+    COALESCE(od.drinks, '') AS drinks
+FROM kbnteam.customer_order o
+JOIN kbnteam.order_status os ON os.o_status_id = o.o_status_id
+JOIN kbnteam.customer cu ON cu.user_id = o.customer_user_id
+JOIN kbnteam.api_user au ON au.user_id = cu.user_id
+JOIN kbnteam.company_order co ON co.comp_order_id = o.comp_order_id
+JOIN kbnteam.company cmp ON cmp.company_id = co.company_id
+LEFT JOIN kbnteam.delivery d ON d.delivery_id = co.delivery_id
+LEFT JOIN kbnteam.delivery_status ds ON ds.d_status_id = d.d_status_id
+LEFT JOIN kbnteam.driver drv ON drv.user_id = d.driver_user_id
+LEFT JOIN kbnteam.api_user du ON du.user_id = drv.user_id
+LEFT JOIN order_meals om ON om.order_id = o.order_id
+LEFT JOIN order_drinks od ON od.order_id = o.order_id;
+}}}
+
+== Тестирање на перформанси ==
+
+=== Препорачано тест прашање ===
+{{{
+#!sql
+SET search_path TO kbnteam;
+SET statement_timeout = '60s';
+
+-- Тест 1: последни 30 дена (временски опсег — тестира idx по datetime)
+EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
+SELECT * FROM kbnteam.v_orders_full
+WHERE order_datetime >= CURRENT_DATE - INTERVAL '30 days';
+
+-- Тест 2: по купувач (тестира idx по customer_user_id)
+EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
+SELECT * FROM kbnteam.v_orders_full
+WHERE customer_user_id = 1;
+
+-- Тест 3: по компаниска нарачка (тестира idx по comp_order_id)
+EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
+SELECT * FROM kbnteam.v_orders_full
+WHERE comp_order_id = 1;
+}}}
+
+=== Резултати пред индексирање ===
+Извршете ги горните прашања '''пред''' да ја примените датотеката `indexes/v_orders_full_index.sql`.
+
+||= Метрика ||= Тест 1 (datetime) ||= Тест 2 (customer) ||= Тест 3 (comp_order) ||
+|| Planning Time || ___ ms || ___ ms || ___ ms ||
+|| Execution Time || ___ ms || ___ ms || ___ ms ||
+|| Rows Returned || ___ || ___ || ___ ||
+|| customer_order scan || ___ || ___ || ___ ||
+|| order_meal scan || ___ || ___ || ___ ||
+|| delivery scan || ___ || ___ || ___ ||
+
+{{{
+-- Излезот од EXPLAIN ANALYZE овде (пред индексирање)
+}}}
+
+=== Применети индекси ===
+{{{
+#!sql
+-- indexes/v_orders_full_index.sql
+CREATE INDEX IF NOT EXISTS idx_customer_order_comp_order_id
+ON kbnteam.customer_order (comp_order_id);
+
+CREATE INDEX IF NOT EXISTS idx_customer_order_customer_user_id_order_datetime
+ON kbnteam.customer_order (customer_user_id, order_datetime DESC);
+
+CREATE INDEX IF NOT EXISTS idx_delivery_driver_user_id_delivery_date
+ON kbnteam.delivery (driver_user_id, delivery_date);
+
+CREATE INDEX IF NOT EXISTS idx_order_meal_order_id_meal_id
+ON kbnteam.order_meal (order_id, meal_id);
+
+CREATE INDEX IF NOT EXISTS idx_order_drink_order_id_drink_id
+ON kbnteam.order_drink (order_id, drink_id);
+}}}
+
+=== Резултати по индексирање ===
+Извршете ги истите прашања '''по''' примена на `indexes/v_orders_full_index.sql`.
+
+||= Метрика ||= Тест 1 (datetime) ||= Тест 2 (customer) ||= Тест 3 (comp_order) ||
+|| Planning Time || ___ ms || ___ ms || ___ ms ||
+|| Execution Time || ___ ms || ___ ms || ___ ms ||
+|| Rows Returned || ___ || ___ || ___ ||
+|| customer_order scan || ___ || ___ || ___ ||
+|| order_meal scan || ___ || ___ || ___ ||
+|| delivery scan || ___ || ___ || ___ ||
+
+{{{
+-- Излезот од EXPLAIN ANALYZE овде (по индексирање)
+}}}
+
+=== Анализа на подобрување ===
+||= Индекс ||= Помага на ||= Очекувана промена ||
+|| `idx_customer_order_customer_user_id_order_datetime` || Тест 1 и Тест 2 || Seq Scan → Index Scan на `customer_order` ||
+|| `idx_customer_order_comp_order_id` || Тест 3 и JOIN со `company_order` || Seq Scan → Index Scan на `customer_order` ||
+|| `idx_order_meal_order_id_meal_id` || CTE `order_meals` || Seq Scan → Index Scan на `order_meal` ||
+|| `idx_order_drink_order_id_drink_id` || CTE `order_drinks` || Seq Scan → Index Scan на `order_drink` ||
+|| `idx_delivery_driver_user_id_delivery_date` || LEFT JOIN на `delivery` || Seq Scan → Index Scan на `delivery` ||
+
+||= Метрика ||= Пред ||= По ||= Δ Подобрување ||
+|| Execution Time (Тест 1) || ___ ms || ___ ms || ___ % ||
+|| Execution Time (Тест 2) || ___ ms || ___ ms || ___ % ||
+|| Execution Time (Тест 3) || ___ ms || ___ ms || ___ % ||