Преглед: v_contracts_revenue

Својство	Вредност
Шема	kbnteam
Категорија	Договори и приходи
Поврзани индекси	Индекси за договори и приходи

Опис

За секој договор ги прикажува бројот на компаниски и клиентски нарачки и вкупниот приход за парот компанија–ресторан.

Статистиката ги опфаќа сите нарачки, независно од датумот и статусот на договорот или нарачката. Целиот износ на нарачката се брои кај секој вклучен ресторан. Договорите за истиот пар компанија–ресторан ја прикажуваат истата статистика.

Еден ред за секој договор. Нарачките без јадења и пијалоци немаат поврзан ресторан и не влегуваат во статистиката. Договорите без соодветни нарачки имаат нулти броеви и приход.

Зависности

Табела / преглед	Употреба
kbnteam.order_meal	Јадења вклучени во нарачките.
kbnteam.meal	Јадења, цени и поврзаност со категорија и ресторан.
kbnteam.order_drink	Пијалоци вклучени во нарачките.
kbnteam.drink	Пијалоци, количини, цени и ресторан.
kbnteam.customer_order	Клиентски нарачки, датуми, статуси и износи.
kbnteam.company_order	Поврзување на компаниските нарачки со компанијата и доставата.
kbnteam.contract	Договори меѓу компаниите и рестораните.
kbnteam.company	Податоци за компаниите.
kbnteam.restaurant	Податоци за рестораните.
kbnteam.contract_status	Називи на статусите на договорите.

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

Колона	Извор / пресметка	Опис
contract_id	contract.contract_id	Идентификатор на договорот.
company_id	company.company_id	Идентификатор на компанијата.
company_name	company.company_name	Назив на компанијата.
rest_id	restaurant.rest_id	Идентификатор на ресторанот.
rest_name	restaurant.rest_name	Назив на ресторанот.
contract_status_name	contract_status.contract_status_name	Статус на договорот.
contract_start_date	contract.contract_start_date	Почетен датум на договорот.
contract_end_date	contract.contract_end_date	Краен датум на договорот.
company_order_count	COALESCE(contract_metrics.company_order_count, 0)	Број на различни компаниски нарачки.
customer_order_count	COALESCE(contract_metrics.customer_order_count, 0)	Број на клиентски нарачки.
total_revenue	COALESCE(contract_metrics.total_revenue, 0)::numeric(14,2)	Збир на износите за парот компанија–ресторан.

SQL дефиниција

CREATE OR REPLACE VIEW kbnteam.v_contracts_revenue AS
WITH order_restaurants AS (
    SELECT
        om.order_id,
        m.rest_id
    FROM kbnteam.order_meal om
    JOIN kbnteam.meal m ON m.meal_id = om.meal_id

    UNION

    SELECT
        od.order_id,
        d.rest_id
    FROM kbnteam.order_drink od
    JOIN kbnteam.drink d ON d.drink_id = od.drink_id
),
contract_metrics AS (
    SELECT
        co.company_id,
        orr.rest_id,
        COUNT(DISTINCT co.comp_order_id) AS company_order_count,
        COUNT(*) AS customer_order_count,
        SUM(o.order_total)::numeric(14,2) AS total_revenue
    FROM kbnteam.customer_order o
    JOIN kbnteam.company_order co ON co.comp_order_id = o.comp_order_id
    JOIN order_restaurants orr ON orr.order_id = o.order_id
    GROUP BY co.company_id, orr.rest_id
)
SELECT
    ct.contract_id,
    cmp.company_id,
    cmp.company_name,
    r.rest_id,
    r.rest_name,
    cs.contract_status_name,
    ct.contract_start_date,
    ct.contract_end_date,
    COALESCE(cm.company_order_count, 0) AS company_order_count,
    COALESCE(cm.customer_order_count, 0) AS customer_order_count,
    COALESCE(cm.total_revenue, 0)::numeric(14,2) AS total_revenue
FROM kbnteam.contract ct
JOIN kbnteam.company cmp ON cmp.company_id = ct.company_id
JOIN kbnteam.restaurant r ON r.rest_id = ct.rest_id
JOIN kbnteam.contract_status cs ON cs.contract_status_id = ct.contract_status_id
LEFT JOIN contract_metrics cm
    ON cm.company_id = ct.company_id
   AND cm.rest_id = ct.rest_id;

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

„Пред индексирање“ и „по индексирање“ ги означуваат групите наведени како „без индекси“ и „со индекси“ во евиденцијата. Точниот сет присутни индекси во секоја група не е евидентиран; „без индекси“ не потврдува отсуство на индекси од примарни и уникатни клучеви.

Наведените времиња се клиентски мерења од DBeaver, а не серверските Planning Time и Execution Time од EXPLAIN ANALYZE. Нема приложени планови, типови на скенирање или податоци за баферите. Вредностите се во милисекунди (ms).

Измерен прашалник

SELECT * FROM kbnteam.v_contracts_revenue
WHERE company_id = 250;

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

Метрика	Измерен резултат
Клиентско време (ms)	2555
Приказ на мерењето	Време прикажано со резултатот во DBeaver
Прикажани / преземени редови	0
Planning Time / Execution Time од EXPLAIN	Не се измерени
План, тип на скенирање и бафери	Не се приложени

Дополнителни и заеднички индекси

contract(company_id, rest_id) поддржува и услов само по company_id. Индексот со rest_id како прва колона ја поддржува другата насока на пребарување.

Индекс	Табела и колони	Намена
idx_company_order_company_id	company_order(company_id)	Пронаоѓање компаниски нарачки по компанија.
idx_customer_order_comp_order_id	customer_order(comp_order_id)	Пронаоѓање клиентски нарачки по компаниска нарачка.
idx_order_meal_order_id_meal_id	order_meal(order_id, meal_id)	Пристап до јадењата на нарачката и групирање по order_id.
idx_order_drink_order_id_drink_id	order_drink(order_id, drink_id)	Пристап до пијалоците на нарачката и групирање по order_id.
idx_contract_company_id_rest_id	contract(company_id, rest_id)	Пребарување договори по компанија и ресторан и групирање по компанија.
idx_contract_rest_id_company_id	contract(rest_id, company_id)	Пронаоѓање договори почнувајќи од ресторан.

CREATE INDEX IF NOT EXISTS idx_company_order_company_id
ON kbnteam.company_order (company_id);

CREATE INDEX IF NOT EXISTS idx_customer_order_comp_order_id
ON kbnteam.customer_order (comp_order_id);

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);

CREATE INDEX IF NOT EXISTS idx_contract_company_id_rest_id
ON kbnteam.contract (company_id, rest_id);

CREATE INDEX IF NOT EXISTS idx_contract_rest_id_company_id
ON kbnteam.contract (rest_id, company_id);

Проверка на индексите

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'kbnteam'
  AND indexname IN (
    'idx_company_order_company_id',
    'idx_customer_order_comp_order_id',
    'idx_order_meal_order_id_meal_id',
    'idx_order_drink_order_id_drink_id',
    'idx_contract_company_id_rest_id',
    'idx_contract_rest_id_company_id'
)
ORDER BY tablename, indexname;

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

Метрика	Измерен резултат
Клиентско време (ms)	1966
Приказ на мерењето	Време прикажано со резултатот во DBeaver
Прикажани / преземени редови	0
Planning Time / Execution Time од EXPLAIN	Не се измерени
План, тип на скенирање и бафери	Не се приложени

Анализа на резултатите

Метрика	Пред	По	Промена на времето
Клиентско време (ms)	2555	1966	-23,1%

Времето е пократко за 589 ms (23,1%), но двата теста враќаат празен резултат. Од ова не може да се оцени перформансата на извештај што враќа договори и приходи. Потребно е дополнително мерење за компанија со соодветни податоци.

Промената се пресметува како 100 × (време по - време пред) / време пред. Негативна вредност значи пократко време, а позитивна подолго време. Процентот ја опишува разликата меѓу прикажаните вредности.

Ова се поединечни набљудувања. Не се евидентирани контролирани повторувања, исти услови за кеширање и оптоварување или точните дефиниции на присутните индекси. Затоа промената на времето не е доказ дека индексите се единствената причина. Не се приложени мерења за INSERT, UPDATE и DELETE.

Дополнително тестирање - сè уште неизмерено

Следниот прашалник служи за ново мерење на серверскиот план со истиот филтер. Неговите резултати не се пополнети со клиентските времиња наведени погоре. За споредба се задржуваат исти податоци и поставки, се евидентираат присутните индекси, се ажурираат статистиките и се прават повеќе повторувања во двете состојби. Се споредуваат медијаната на времињата и плановите.

BEGIN READ ONLY;
SET LOCAL statement_timeout = '60s';

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM kbnteam.v_contracts_revenue
WHERE company_id = 250;

ROLLBACK;

Метрика за дополнителното тестирање	Пред	По
Planning Time (ms)	Не е измерено	Не е измерено
Execution Time (ms)	Не е измерено	Не е измерено
Тип на скенирање по табела	Не е евидентиран	Не е евидентиран
Shared Hit Blocks / Shared Read Blocks	Не се измерени	Не се измерени