Оптимизација на прашалници и погледи

Оваа страница ги опишува деветте погледи од Views 2.0 и индексите од Index 2.0, усогласени со schema7.0_dll.sql.

Погледи

Погледите се креираат по нумеричкиот редослед на SQL датотеките. v_orders_full се креира пред v_driver_deliveries, бидејќи вториот го користи првиот.

Мени и каталог

Овие погледи го прикажуваат менито на рестораните. Самите погледи не ја проверуваат активноста на договорите и не го ограничуваат менито според компанијата на клиентот.

Поглед	Цел	Изворни табели / погледи	Забелешки
v_menu_meal	Ги прикажува јадењата со категорија, ресторан, состојки и алергени.	meal_ingredient, ingredient, alergen_ingredient, alergen, meal, category, restaurant	Двата CTE изрази користат string_agg(DISTINCT ... ORDER BY ...), без вгнездени SELECT DISTINCT прашалници. Јадењата без состојки или алергени остануваат во резултатот со празни листи.
v_menu_drink	Ги прикажува пијалоците, нивната количина и цена, како и ресторанот.	drink, restaurant	Едноставно спојување на drink и restaurant, без агрегации.

Нарачки и достави

Овие погледи служат за преглед и следење на нарачките и доставите.

Поглед	Цел	Изворни табели / погледи	Забелешки
v_orders_full	Прикажува нарачки со податоци за клиентот, компанијата, статусот, доставата и нарачаните јадења и пијалоци.	order_meal, meal, order_drink, drink, customer_order, order_status, customer, api_user, company_order, company, delivery, delivery_status	Списоците се добиваат со string_agg(DISTINCT ... ORDER BY ...). Податоците за доставувачот се земаат директно од api_user преку delivery.driver_user_id; нема посебно спојување со driver. Се задржуваат нарачките без достава.
v_driver_deliveries	Ги прикажува доставите, доставувачите, рестораните, компаниите и поврзаните нарачки.	delivery, delivery_status, driver, api_user, restaurant, company_order, company, v_orders_full	Го користи v_orders_full за деталите за клиентот и списоците со јадења и пијалоци. LEFT JOIN ги задржува и доставите без доставувач, компаниска нарачка или клиентски нарачки.

Рецензии

Оцените за нарачки и достави се обединуваат во заеднички извештај.

Поглед	Цел	Изворни табели / погледи	Забелешки
v_reviews_full	Ги обединува оцените за нарачки и достави, со ознака за типот на рецензијата.	review, order_review, customer_order, api_user, company_order, company, delivery_review, delivery	Користи UNION ALL. Отстранети се посредните спојувања со customer и driver; корисничките податоци се земаат директно од api_user. Ако една рецензија има записи во двата поттипа, се прикажуваат двата записа.

Договори, фактурирање и лојалност

Овие погледи обезбедуваат статистика за компаниите, договорите, фактурите и клиентите.

Поглед	Цел	Изворни табели / погледи	Забелешки
v_contracts_revenue	За секој договор ги прикажува бројот на компаниски и клиентски нарачки и вкупниот приход за парот компанија–ресторан.	order_meal, meal, order_drink, drink, customer_order, company_order, contract, company, restaurant, contract_status	UNION ги отстранува повторувањата на парот нарачка–ресторан. Нема филтер за активен договор, датум или статус на нарачка. Целиот износ на нарачката се брои кај секој вклучен ресторан, а истата статистика се повторува кај договорите за истиот пар компанија–ресторан.
v_company_billing_overview	За секоја фактура ги прикажува компанијата, доставата, бројот на клиентски нарачки, нивниот вкупен износ и бројот на договори на компанијата.	contract, invoice, company_order, company, customer_order	Износот и бројот на нарачки се пресметуваат во главниот прашалник. Договорите се бројат во посебен CTE за да не го умножат износот. Фактурите без клиентски нарачки имаат број и износ еднакви на нула.
v_customer_loyalty_full_v2	Ги прикажува клиентот, компанијата, поените, статусот и нивото на лојалност, бројот на нарачки, вкупната потрошувачка и датумот на последната нарачка.	customer_order, customer_loyalty, customer, company, api_user, customer_loyalty_status, loyalty_tier	CTE изразот order_stats пресметува COUNT, SUM и MAX по клиент. Статистиката ги опфаќа сите статуси на нарачки. Се прикажуваат клиентите со запис во customer_loyalty, вклучувајќи ги и оние без нарачки.

Термини за ручек

Погледот го поврзува распоредот за ручек со доставувачите на соодветниот ресторан.

Поглед	Цел	Изворни табели / погледи	Забелешки
v_driver_lunch_timers	Ги поврзува термините за ручек со договорите, компаниите, рестораните и доставувачите.	lunch_time, contract, company, restaurant, driver, api_user	Се добива ред за секој термин и секој доставувач на ресторанот. Погледот не го ограничува резултатот на назначениот доставувач и не филтрира активни договори или денови. Термините за ресторани без доставувачи не се прикажуваат.

Индекси

Индексите се поставуваат врз изворните табели, бидејќи овие погледи не се материјализирани. Деветте датотеки во Index 2.0 содржат вкупно 14 различни дефиниции на индекси. Заедничките индекси се дефинирани само еднаш. Датотеките за менито со пијалоци, рецензиите и фактурирањето содржат објаснувања за постојните и заедничките индекси, без нови CREATE INDEX наредби.

Дефинициите користат CREATE INDEX IF NOT EXISTS. Оваа проверка е по име и не ја менува дефиницијата на веќе постоен индекс со исто име. Индексите од PRIMARY KEY и UNIQUE ограничувањата остануваат дел од шемата.

Индекси за мени

Датотека на индекс	Индекси дефинирани во датотеката	Поддржани погледи	Забелешки
Index 2.0/02_v_menu_meal_index.sql	meal(cat_id, meal_id), meal_ingredient(meal_id, ingr_id), alergen_ingredient(ingr_id, alergen_id)	v_menu_meal	Ги поддржува пребарувањето по категорија и врските јадење–состојки–алергени. meal(rest_id, meal_name) веќе е покриен со UNIQUE.
Index 2.0/01_v_menu_drink_index.sql	Нема нови дефиниции	v_menu_drink	Нема дополнителен индекс. restaurant(rest_id) е примарен клуч, а drink(rest_id, drink_name) веќе има UNIQUE индекс.

Индекси за нарачки, достави и рецензии

Датотека на индекс	Индекси дефинирани во датотеката	Поддржани погледи	Забелешки
Index 2.0/03_v_orders_full_index.sql	company_order(company_id), customer_order(comp_order_id), order_meal(order_id, meal_id), order_drink(order_id, drink_id)	v_orders_full, v_driver_deliveries, v_contracts_revenue, v_company_billing_overview, v_reviews_full	Заеднички индекси за пронаоѓање компаниски и клиентски нарачки и за поврзување на јадењата и пијалоците по нарачка. Индексите по доставувач и клиент се дефинирани во датотеките 05 и 07.
Index 2.0/05_v_driver_deliveries_index.sql	delivery(driver_user_id, delivery_date)	v_driver_deliveries, v_orders_full, v_reviews_full	Пребарување по доставувач и датум. Ги користи и индексите од датотека 03; индексот за driver(rest_id) е во датотека 08.
Index 2.0/04_v_reviews_full_index.sql	Нема нови дефиниции	v_reviews_full	Нема дополнителен индекс. Врските преку review_id, order_id и delivery_id се покриени со примарни и уникатни клучеви. Се користат и заедничките индекси од датотеките 03, 05 и 07.

Индекси за договори, фактурирање, лојалност и термини

Датотека на индекс	Индекси дефинирани во датотеката	Поддржани погледи	Забелешки
Index 2.0/06_v_contracts_revenue_index.sql	contract(company_id, rest_id)	v_contracts_revenue, v_company_billing_overview	Водечката колона company_id поддржува и групирање на договорите за фактурирање. Не се додава посебен индекс само на contract(company_id). Индексите за нарачките се во датотека 03.
Index 2.0/09_v_company_billing_overview_index.sql	Нема нови дефиниции	v_company_billing_overview	Нема дополнителен индекс. Ги користи company_order(company_id) и customer_order(comp_order_id) од датотека 03 и contract(company_id, rest_id) од датотека 06. invoice(comp_order_id) веќе има UNIQUE индекс.
Index 2.0/07_v_customer_loyalty_full_v2_index.sql	customer_order(customer_user_id, order_datetime DESC), customer(company_id)	v_customer_loyalty_full_v2, v_orders_full	Пребарување на нарачки по клиент и датум и на клиенти по компанија. customer_loyalty(user_id) веќе има UNIQUE индекс. За сумата на потрошувачката и натаму се чита order_total.
Index 2.0/08_v_driver_lunch_timers_index.sql	driver(rest_id), contract(rest_id, company_id), lunch_time(contract_id)	v_driver_lunch_timers, v_driver_deliveries; пребарување договори по ресторан во v_contracts_revenue	Ги поддржува врските ресторан–доставувач, ресторан–договор и договор–термин. Индексот на lunch_time започнува со contract_id, па не бара филтер по ден.

Тестирање на базата пред користење на индексите

Овој дел е оставен за мерењата пред додавање на дополнителните индекси од Index 2.0. Во основната состојба остануваат индексите создадени со PRIMARY KEY и UNIQUE. Ако претходно се применети дополнителните индекси од index/, тие треба да се евидентираат; таквото мерење не претставува основна состојба без дополнителни индекси.

Податоци и услови за тестирање

[Да се внесат верзијата на PostgreSQL, бројот на записи, постојните индекси, условите за кеширање, состојбата на статистиките и бројот на повторувања.]

Прашалници и планови за извршување

[Простор за SQL прашалници со конкретни филтри, излез од EXPLAIN (ANALYZE, BUFFERS) и слики од тестирањето.]

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

Поглед	Прашалник / филтер	Време на извршување (ms)	План / buffers / забелешки
v_menu_drink
v_menu_meal
v_orders_full
v_reviews_full
v_driver_deliveries
v_contracts_revenue
v_customer_loyalty_full_v2
v_driver_lunch_timers
v_company_billing_overview

Тестирање на базата по користење на индексите

Овој дел е оставен за мерењата по примената на индексите од Index 2.0. Се користат истите податоци, прашалници, филтри, услови за кеширање и број на повторувања како во претходниот дел, со ажурирани статистики за табелите во двете состојби.

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

[Простор за список на индексите што навистина се присутни во базата и потврда за применетите SQL датотеки.]

Прашалници и планови за извршување

[Простор за истиот сет SQL прашалници, излез од EXPLAIN (ANALYZE, BUFFERS) и слики од тестирањето.]

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

Поглед	Прашалник / филтер	Време на извршување (ms)	План / buffers / забелешки
v_menu_drink
v_menu_meal
v_orders_full
v_reviews_full
v_driver_deliveries
v_contracts_revenue
v_customer_loyalty_full_v2
v_driver_lunch_timers
v_company_billing_overview

Споредба и заклучок

[Простор за споредба на времињата, плановите и прочитаните блокови пред и по индексите. Да се евидентираат и прашалниците кај кои нема подобрување. Изборот на индекс зависи од податоците и филтрите; целосните прегледи и агрегации можат да користат секвенцијално читање.]