Преглед: v_menu_meal

Датотека	views/01_menu_meal_view.sql
Шема	kbnteam
Категорија	Мени и Каталог
Поврзани индекси	indexes/v_menu_meal_index.sql

Опис

Ги наведува сите оброци со нивната категорија, ресторан, состојки и алергени. Употребува две CTE за агрегирање на состојките и алергените во читливи низи преку string_agg. Се користи за прикажување на мени во веб апликацијата и во договорни погледи.

Зависности

Табела	Тип на употреба
kbnteam.meal	Главна табела
kbnteam.category	JOIN — категорија на оброкот
kbnteam.restaurant	JOIN — ресторан на оброкот
kbnteam.meal_ingredient	LEFT JOIN (CTE) — врска оброк-состојка
kbnteam.ingredient	JOIN (CTE) — назив на состојката
kbnteam.alergen_ingredient	JOIN (CTE) — врска состојка-алерген
kbnteam.alergen	JOIN (CTE) — назив на алергенот

Излезни колони

Колона	Извор	Опис
meal_id	meal.meal_id	Примарен клуч на оброкот
meal_name	meal.meal_name	Назив на оброкот
meal_description	meal.meal_description	Опис
meal_price	meal.meal_price	Цена
meal_weight	meal.meal_weight	Тежина во грами
cat_id	category.cat_id	Примарен клуч на категоријата
cat_name	category.cat_name	Назив на категоријата
rest_id	restaurant.rest_id	Примарен клуч на ресторанот
rest_name	restaurant.rest_name	Назив на ресторанот
ingredients	CTE meal_ingredients	Состојки одвоени со запирка
allergens	CTE meal_allergens	Алергени одвоени со запирка

SQL Дефиниција

CREATE OR REPLACE VIEW kbnteam.v_menu_meal AS
WITH meal_ingredients AS (
    SELECT
        x.meal_id,
        string_agg(x.ingr_name, ', ' ORDER BY x.ingr_name) AS ingredients
    FROM (
        SELECT DISTINCT
            mi.meal_id,
            i.ingr_name
        FROM kbnteam.meal_ingredient mi
        JOIN kbnteam.ingredient i ON i.ingr_id = mi.ingr_id
    ) x
    GROUP BY x.meal_id
),
meal_allergens AS (
    SELECT
        x.meal_id,
        string_agg(x.alergen_name, ', ' ORDER BY x.alergen_name) AS allergens
    FROM (
        SELECT DISTINCT
            mi.meal_id,
            a.alergen_name
        FROM kbnteam.meal_ingredient mi
        JOIN kbnteam.alergen_ingredient ai ON ai.ingr_id = mi.ingr_id
        JOIN kbnteam.alergen a ON a.alergen_id = ai.alergen_id
    ) x
    GROUP BY x.meal_id
)
SELECT
    m.meal_id,
    m.meal_name,
    m.meal_description,
    m.meal_price,
    m.meal_weight,
    c.cat_id,
    c.cat_name,
    r.rest_id,
    r.rest_name,
    COALESCE(mi.ingredients, '') AS ingredients,
    COALESCE(ma.allergens, '') AS allergens
FROM kbnteam.meal m
JOIN kbnteam.category c ON c.cat_id = m.cat_id
JOIN kbnteam.restaurant r ON r.rest_id = m.rest_id
LEFT JOIN meal_ingredients mi ON mi.meal_id = m.meal_id
LEFT JOIN meal_allergens ma ON ma.meal_id = m.meal_id;

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

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

SET search_path TO kbnteam;
SET statement_timeout = '60s';

-- Тест 1: филтрирање по категорија
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM kbnteam.v_menu_meal
WHERE cat_id = 1;

-- Тест 2: филтрирање по ресторан
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM kbnteam.v_menu_meal
WHERE rest_id = 1;

Резултати пред индексирање

Извршете ги горните прашања пред да ја примените датотеката indexes/v_menu_meal_index.sql.

Метрика	Тест 1 (по cat_id)	Тест 2 (по rest_id)
Planning Time	_ ms	_ ms
Execution Time	_ ms	_ ms
Rows Returned	_	_
Scan Type (meal)	_	_
Scan Type (meal_ingredient)	_	_

-- Излезот од EXPLAIN ANALYZE овде (пред индексирање)

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

-- indexes/v_menu_meal_index.sql
-- Note: uq_meal_restaurant_name already supports (rest_id, meal_name).
CREATE INDEX IF NOT EXISTS idx_meal_cat_id_meal_id
ON kbnteam.meal (cat_id, meal_id);

CREATE INDEX IF NOT EXISTS idx_meal_ingredient_meal_id_ingr_id
ON kbnteam.meal_ingredient (meal_id, ingr_id);

CREATE INDEX IF NOT EXISTS idx_alergen_ingredient_ingr_id_alergen_id
ON kbnteam.alergen_ingredient (ingr_id, alergen_id);

Резултати по индексирање

Извршете ги истите прашања по примена на indexes/v_menu_meal_index.sql.

Метрика	Тест 1 (по cat_id)	Тест 2 (по rest_id)
Planning Time	_ ms	_ ms
Execution Time	_ ms	_ ms
Rows Returned	_	_
Scan Type (meal)	_	_
Scan Type (meal_ingredient)	_	_

-- Излезот од EXPLAIN ANALYZE овде (по индексирање)

Анализа на подобрување

Метрика	Пред	По	Δ Подобрување
Execution Time	_ ms	_ ms	_ %
meal scan (Тест 1)	Seq Scan	Index Scan (cat_id)	—
meal_ingredient scan	Seq Scan	Index Scan (meal_id)	—
alergen_ingredient scan	Seq Scan	Index Scan (ingr_id)	—

Очекувано: Трите индекси заедно ги забрзуваат CTE агрегациите. idx_meal_cat_id_meal_id го помага JOIN-от по категорија, idx_meal_ingredient_meal_id_ingr_id го поддржува развивањето на состојки, а idx_alergen_ingredient_ingr_id_alergen_id го забрзува поврзувањето алерген-состојка.