Changes between Version 1 and Version 2 of MenuMealView
- Timestamp:
- 09/14/26 00:37:05 (2 weeks ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
MenuMealView
v1 v2 1 1 = Преглед: v_menu_meal = 2 2 3 ||= Датотека ||= `views/01_menu_meal_view.sql`||4 || = Шема ||=`kbnteam` ||5 || = Категорија ||= Мени и Каталог ||6 || = Поврзани индекси ||= `indexes/v_menu_meal_index.sql`||3 ||= Својство ||= Вредност || 4 || Шема || `kbnteam` || 5 || Категорија || Мени и каталог || 6 || Поврзани индекси || [wiki:MenuMealIndex Индекси за мени и каталог] || 7 7 8 8 == Опис == 9 Ги наведува сите оброци со нивната категорија, ресторан, состојки и алергени. Употребува две CTE за агрегирање на состојките и алергените во читливи низи преку `string_agg`. Се користи за прикажување на мени во веб апликацијата и во договорни погледи. 9 10 Ги прикажува јадењата со категорија, ресторан, состојки и алергени. 11 12 Состојките и алергените се прикажуваат како азбучно подредени листи без повторувања. Се прикажуваат и јадењата без наведени состојки или алергени. 13 14 Еден ред за секое јадење. Состојките и алергените се азбучно подредени, без повторување на називите. 10 15 11 16 == Зависности == 12 ||= Табела ||= Тип на употреба || 13 || `kbnteam.meal` || Главна табела || 14 || `kbnteam.category` || JOIN — категорија на оброкот || 15 || `kbnteam.restaurant` || JOIN — ресторан на оброкот || 16 || `kbnteam.meal_ingredient` || LEFT JOIN (CTE) — врска оброк-состојка || 17 || `kbnteam.ingredient` || JOIN (CTE) — назив на состојката || 18 || `kbnteam.alergen_ingredient` || JOIN (CTE) — врска состојка-алерген || 19 || `kbnteam.alergen` || JOIN (CTE) — назив на алергенот || 17 18 ||= Табела / преглед ||= Употреба || 19 || `kbnteam.meal_ingredient` || Врски меѓу јадењата и состојките. || 20 || `kbnteam.ingredient` || Називи на состојките. || 21 || `kbnteam.alergen_ingredient` || Врски меѓу состојките и алергените. || 22 || `kbnteam.alergen` || Називи на алергените. || 23 || `kbnteam.meal` || Јадења, цени и поврзаност со категорија и ресторан. || 24 || `kbnteam.category` || Категории на јадења. || 25 || `kbnteam.restaurant` || Податоци за рестораните. || 20 26 21 27 == Излезни колони == 22 ||= Колона ||= Извор ||= Опис ||23 || `meal_id` || `meal.meal_id` || Примарен клуч на оброкот ||24 || `meal_name` || `meal.meal_name` || Назив на оброкот ||25 || `meal_description` || `meal.meal_description` || Опис ||26 || `meal_price` || `meal.meal_price` || Цена ||27 || `meal_weight` || `meal.meal_weight` || Тежина во грами ||28 || `cat_id` || `category.cat_id` || Примарен клуч на категоријата ||29 || `cat_name` || `category.cat_name` || Назив на категоријата ||30 || `rest_id` || `restaurant.rest_id` || Примарен клуч на ресторанот ||31 || `rest_name` || `restaurant.rest_name` || Назив на ресторанот ||32 || `ingredients` || CTE `meal_ingredients` || Состојки одвоени со запирка ||33 || `allergens` || CTE `meal_allergens` || Алергени одвоени со запирка ||34 28 35 == SQL Дефиниција == 29 ||= Колона ||= Извор / пресметка ||= Опис || 30 || `meal_id` || `meal.meal_id` || Идентификатор на јадењето. || 31 || `meal_name` || `meal.meal_name` || Назив на јадењето. || 32 || `meal_description` || `meal.meal_description` || Опис на јадењето. || 33 || `meal_price` || `meal.meal_price` || Цена на јадењето. || 34 || `meal_weight` || `meal.meal_weight` || Тежина на јадењето. || 35 || `cat_id` || `category.cat_id` || Идентификатор на категоријата. || 36 || `cat_name` || `category.cat_name` || Назив на категоријата. || 37 || `rest_id` || `restaurant.rest_id` || Идентификатор на ресторанот. || 38 || `rest_name` || `restaurant.rest_name` || Назив на ресторанот. || 39 || `ingredients` || `COALESCE(meal_ingredients.ingredients, '')` || Подредена листа со називи на состојки. || 40 || `allergens` || `COALESCE(meal_allergens.allergens, '')` || Подредена листа со називи на алергени. || 41 42 == SQL дефиниција == 43 36 44 {{{ 37 45 #!sql … … 39 47 WITH meal_ingredients AS ( 40 48 SELECT 41 x.meal_id, 42 string_agg(x.ingr_name, ', ' ORDER BY x.ingr_name) AS ingredients 43 FROM ( 44 SELECT DISTINCT 45 mi.meal_id, 46 i.ingr_name 47 FROM kbnteam.meal_ingredient mi 48 JOIN kbnteam.ingredient i ON i.ingr_id = mi.ingr_id 49 ) x 50 GROUP BY x.meal_id 49 mi.meal_id, 50 string_agg(DISTINCT i.ingr_name, ', ' ORDER BY i.ingr_name) AS ingredients 51 FROM kbnteam.meal_ingredient mi 52 JOIN kbnteam.ingredient i ON i.ingr_id = mi.ingr_id 53 GROUP BY mi.meal_id 51 54 ), 52 55 meal_allergens AS ( 53 56 SELECT 54 x.meal_id, 55 string_agg(x.alergen_name, ', ' ORDER BY x.alergen_name) AS allergens 56 FROM ( 57 SELECT DISTINCT 58 mi.meal_id, 59 a.alergen_name 60 FROM kbnteam.meal_ingredient mi 61 JOIN kbnteam.alergen_ingredient ai ON ai.ingr_id = mi.ingr_id 62 JOIN kbnteam.alergen a ON a.alergen_id = ai.alergen_id 63 ) x 64 GROUP BY x.meal_id 57 mi.meal_id, 58 string_agg(DISTINCT a.alergen_name, ', ' ORDER BY a.alergen_name) AS allergens 59 FROM kbnteam.meal_ingredient mi 60 JOIN kbnteam.alergen_ingredient ai ON ai.ingr_id = mi.ingr_id 61 JOIN kbnteam.alergen a ON a.alergen_id = ai.alergen_id 62 GROUP BY mi.meal_id 65 63 ) 66 64 SELECT … … 85 83 == Тестирање на перформанси == 86 84 87 === Препорачано тест прашање === 85 Примерите користат илустративни идентификатори и датуми. За споредливи мерења се избираат вредности што постојат во базата и се задржуваат истите податоци, филтри, услови за кеширање и број на повторувања. Статистиките за табелите треба да бидат ажурирани во двете состојби. 86 87 === Тест прашалници === 88 88 89 {{{ 89 90 #!sql 90 SET search_path TO kbnteam;91 SET statement_timeout = '60s';91 BEGIN READ ONLY; 92 SET LOCAL statement_timeout = '60s'; 92 93 93 -- Тест 1: филтрирањепо категорија94 -- Тест 1: по категорија 94 95 EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 95 96 SELECT * FROM kbnteam.v_menu_meal 96 97 WHERE cat_id = 1; 97 98 98 -- Тест 2: филтрирањепо ресторан99 -- Тест 2: по ресторан 99 100 EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 100 101 SELECT * FROM kbnteam.v_menu_meal 101 102 WHERE rest_id = 1; 103 104 ROLLBACK; 102 105 }}} 103 106 104 107 === Резултати пред индексирање === 105 Извршете ги горните прашања '''пред''' да ја примените датотеката `indexes/v_menu_meal_index.sql`.106 108 107 ||= Метрика ||= Тест 1 (по cat_id) ||= Тест 2 (по rest_id) || 109 Основната состојба ги задржува индексите на примарните клучеви и уникатните ограничувања. Останатите присутни индекси се евидентираат одделно. При споредба на заеднички индекси се користи истата почетна состојба за секој преглед. 110 111 ||= Метрика ||= Тест 1 (по категорија) ||= Тест 2 (по ресторан) || 108 112 || Planning Time || ___ ms || ___ ms || 109 113 || Execution Time || ___ ms || ___ ms || 110 114 || Rows Returned || ___ || ___ || 111 || Scan Type (meal) || ___ || ___ || 112 || Scan Type (meal_ingredient) || ___ || ___ || 115 || Начин на читање по табела || ___ || ___ || 116 || Shared Hit Blocks || ___ || ___ || 117 || Shared Read Blocks || ___ || ___ || 113 118 114 119 {{{ 115 -- Излезот од EXPLAIN ANALYZE овде (пред индексирање) 120 -- Планови и резултати пред дополнителното индексирање: 121 116 122 }}} 117 123 118 === Применети индекси === 124 === Дополнителни и заеднички индекси === 125 126 Уникатното ограничување `uq_meal_restaurant_name` веќе го покрива пребарувањето по ресторан. Обратниот редослед на колоните во индексите за состојки и алергени овозможува пристап од јадење кон состојка и од состојка кон алерген. 127 128 ||= Индекс ||= Табела и колони ||= Намена || 129 || `idx_meal_cat_id_meal_id` || `meal(cat_id, meal_id)` || Пребарување јадења по категорија. || 130 || `idx_meal_ingredient_meal_id_ingr_id` || `meal_ingredient(meal_id, ingr_id)` || Пристап до состојките на јадењето и групирање по meal_id. || 131 || `idx_alergen_ingredient_ingr_id_alergen_id` || `alergen_ingredient(ingr_id, alergen_id)` || Пристап до алергените поврзани со состојката. || 132 119 133 {{{ 120 134 #!sql 121 -- indexes/v_menu_meal_index.sql122 -- Note: uq_meal_restaurant_name already supports (rest_id, meal_name).123 135 CREATE INDEX IF NOT EXISTS idx_meal_cat_id_meal_id 124 136 ON kbnteam.meal (cat_id, meal_id); … … 131 143 }}} 132 144 145 === Проверка на индексите === 146 147 {{{ 148 #!sql 149 SELECT tablename, indexname, indexdef 150 FROM pg_indexes 151 WHERE schemaname = 'kbnteam' 152 AND indexname IN ( 153 'idx_meal_cat_id_meal_id', 154 'idx_meal_ingredient_meal_id_ingr_id', 155 'idx_alergen_ingredient_ingr_id_alergen_id' 156 ) 157 ORDER BY tablename, indexname; 158 }}} 159 133 160 === Резултати по индексирање === 134 Извршете ги истите прашања '''по''' примена на `indexes/v_menu_meal_index.sql`.135 161 136 ||= Метрика ||= Тест 1 (по cat_id) ||= Тест 2 (по rest_id) || 162 Мерењето ги користи истите тест прашалници и услови како почетното мерење. 163 164 ||= Метрика ||= Тест 1 (по категорија) ||= Тест 2 (по ресторан) || 137 165 || Planning Time || ___ ms || ___ ms || 138 166 || Execution Time || ___ ms || ___ ms || 139 167 || Rows Returned || ___ || ___ || 140 || Scan Type (meal) || ___ || ___ || 141 || Scan Type (meal_ingredient) || ___ || ___ || 168 || Начин на читање по табела || ___ || ___ || 169 || Shared Hit Blocks || ___ || ___ || 170 || Shared Read Blocks || ___ || ___ || 142 171 143 172 {{{ 144 -- Излезот од EXPLAIN ANALYZE овде (по индексирање) 173 -- Планови и резултати по дополнителното индексирање: 174 145 175 }}} 146 176 147 177 === Анализа на подобрување === 148 ||= Метрика ||= Пред ||= По ||= Δ Подобрување ||149 || Execution Time || ___ ms || ___ ms || ___ % ||150 || meal scan (Тест 1) || Seq Scan || Index Scan (cat_id) || — ||151 || meal_ingredient scan || Seq Scan || Index Scan (meal_id) || — ||152 || alergen_ingredient scan || Seq Scan || Index Scan (ingr_id) || — ||153 178 154 '''Очекувано:''' Трите индекси заедно ги забрзуваат CTE агрегациите. `idx_meal_cat_id_meal_id` го помага JOIN-от по категорија, `idx_meal_ingredient_meal_id_ingr_id` го поддржува развивањето на состојки, а `idx_alergen_ingredient_ingr_id_alergen_id` го забрзува поврзувањето алерген-состојка. 179 ||= Метрика ||= Пред ||= По ||= Промена (%) || 180 || Execution Time (Тест 1) || ___ ms || ___ ms || ___ % || 181 || Execution Time (Тест 2) || ___ ms || ___ ms || ___ % || 182 183 Промената се пресметува како 100 × (време пред − време по) / време пред, кога почетното време е поголемо од нула. Негативна вредност означува побавно извршување. 184 185 Изборот меѓу секвенцијално читање, индексно читање и други планови зависи од обемот на податоците, селективноста на филтрите и статистиките. Индексите не гарантираат забрзување или отстранување на сортирањата и спојувањата. Плановите и времињата се утврдуваат со мерење.
