Индекс: v_driver_deliveries_index

Датотека	indexes/v_driver_deliveries_index.sql
Шема	kbnteam
Поврзани прегледи	v_driver_deliveries, v_orders_full, v_driver_lunch_timers

Опис

Пет индекси наменети главно за прегледот v_driver_deliveries. Покриваат: достави по возач и датум, возачи по ресторан, нарачки по компаниска нарачка, и агрегации на ставки (оброци/пијачи). Повеќето се споделени со v_orders_full_index.sql.

DDL

-- indexes/v_driver_deliveries_index.sql
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_driver_rest_id
ON kbnteam.driver (rest_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);

Поддржани прегледи

Индекс	Прегледи	Цел
idx_delivery_driver_user_id_delivery_date	v_driver_deliveries, v_orders_full	WHERE driver_user_id + датумски опсег
idx_driver_rest_id	v_driver_deliveries, v_driver_lunch_timers	JOIN driver ON drv.rest_id = r.rest_id
idx_customer_order_comp_order_id	v_driver_deliveries, v_orders_full, v_reviews_full, др.	FK JOIN кон company_order
idx_order_meal_order_id_meal_id	v_driver_deliveries, v_orders_full, v_contracts_revenue	CTE order_meals GROUP BY
idx_order_drink_order_id_drink_id	v_driver_deliveries, v_orders_full, v_contracts_revenue	CTE order_drinks GROUP BY

Колонски план

Индекс	Колона 1	Колона 2	Причина
idx_delivery_driver_user_id_delivery_date	driver_user_id	delivery_date	Филтрирање по возач во датумски опсег
idx_driver_rest_id	rest_id	—	JOIN driver.rest_id → restaurant.rest_id
idx_customer_order_comp_order_id	comp_order_id	—	FK JOIN кон company_order
idx_order_meal_order_id_meal_id	order_id	meal_id	CTE агрегација
idx_order_drink_order_id_drink_id	order_id	drink_id	CTE агрегација

Верификација

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'kbnteam'
  AND indexname IN (
      'idx_delivery_driver_user_id_delivery_date',
      'idx_driver_rest_id',
      'idx_customer_order_comp_order_id',
      'idx_order_meal_order_id_meal_id',
      'idx_order_drink_order_id_drink_id'
  );