Преглед: 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 Дефиниција

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;

Тестирање на перформанси

Препорачано тест прашање

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 овде (пред индексирање)

Применети индекси

-- 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	_ %