Changes between Initial Version and Version 1 of QueryOptimization

Timestamp:

05/20/26 16:03:20 (5 days ago)

Author:

231198

Comment:

--

QueryOptimization

@@ +1 @@
+=== VIEW 1: vw_active_driver_status ===
+Опис на погледот (View): vw_active_driver_status
+Овој поглед (View) служи како централизиран извор на информации за сите моментално активни возачи во системот. Тој ги спојува динамичките податоци за нивната моментална локација и достапност со статичките податоци за нивниот профил, возило и ценовна политика.
+
+Што прави овој поглед?
+
+Консолидација на податоци: Ги поврзува табелите active_drivers, drivers, locations, status, vehicle_ownership, vehicle_types, vehicles_model и pricing_rules.
+
+Филтрирање: Ги прикажува исклучиво возачите чиј статус е поставен како 'active'.
+
+Детализација: Обезбедува целосна слика за возачот – од неговиот рејтинг и контакт податоци, преку моменталната улица и населба каде што се наоѓа, па сè до спецификите на неговото возило (бренд, модел, боја, таблички) и соодветната тарифа (почетна цена и цена по километар) која се применува за тој тип на возило.
+
+Организација: Резултатите се подредени по идентификатор на возачот, што овозможува брз преглед и анализа на достапноста во реално време.
+{{{#!sql
+CREATE OR REPLACE VIEW vw_active_driver_status
+            (driver_id, driver_name, driver_phone, driver_rating, is_available, working_start,
+             current_location_id, grad, naselba, current_street, vehicle_plate, vehicle_color,
+             vehicle_model, vehicle_class, vehicle_brand, base_price, price_per_km, driver_status)
+AS
+                        SELECT d.driver_id,
+                                 d.name         AS driver_name,
+                                 d.phone        AS driver_phone,
+                                 d.rating       AS driver_rating,
+                                 ad.is_available,
+                                 ad.working_start,
+                                 l.location_id  AS current_location_id,
+                                 l.grad,
+                                 l.naselba,
+                                 l.ulica        AS current_street,
+                                 vo.plate       AS vehicle_plate,
+                                 vo.color       AS vehicle_color,
+                                 vt.type_name   AS vehicle_model,
+                                 vt.description AS vehicle_class,
+                                 vm.model       AS vehicle_brand,
+                                 pr.base_price,
+                                 pr.price_per_km,
+                                 s.status_text  AS driver_status
+FROM active_drivers ad
+         JOIN drivers d ON ad.driver_id = d.driver_id
+         JOIN locations l ON ad.current_location_id = l.location_id
+         JOIN status s ON d.status_id = s.status_id
+         LEFT JOIN vehicle_ownership vo ON ad.vehicle_ownership_id = vo.vehicle_ownership_id
+         LEFT JOIN vehicle_types vt ON vo.vehicle_type_id = vt.vehicle_type_id
+         LEFT JOIN vehicles_model vm ON vt.vehicles_model_id = vm.vehicles_model_id
+         LEFT JOIN pricing_rules pr ON vt.vehicle_type_id = pr.vehicle_type_id
+WHERE s.status_text::text = 'active'::text
+ORDER BY d.driver_id, pr.base_price DESC;
+
+select * from vw_active_driver_status;
+}}}
+
+=== VIEW 2: vw_ride_details ===
+Овој поглед овозможува целосна транспарентност и историски преглед на секое возење во системот. Тој ги агрегира сите клучни информации за едно возење во една единствена, лесно читлива табела, елиминирајќи ја потребата од комплексни JOIN операции при секое генерирање на извештај или пребарување.
+
+Што прави овој поглед?
+
+Релациска синтеза: Ги поврзува податоците за корисникот (Users), возачот (Drivers), возилото (Vehicle_ownership), локациите на поаѓање и пристигнување (Locations), како и статусот на возењето.
+
+Хронологија и перформанси: Обезбедува целосна временска секвенца на возењето (request, pickup, start, end_time), што е клучно за пресметка на времето на чекање и времетраењето на самото возење.
+
+Финансиска транспарентност: Ги вклучува деталите за поминатата дистанца, евентуалните попусти и конечната цена, олеснувајќи ја финансиската анализа.
+
+Географска прецизност: Ги детално ги прикажува координатите и адресните податоци за почетната и крајната дестинација.
+
+Оптимизација на перформансите:
+Со цел да се обезбеди брзо пребарување на историјата на возењата, имплементирани се специфични индекси:
+
+idx_rides_user_id: Оптимизирање на пребарувањата за корисници (кога корисникот ја проверува својата лична историја на возења).
+
+idx_rides_driver_id: Оптимизирање на пребарувањата за возачи (кога возачот ја проверува својата работна историја).
+
+{{{#!sql
+
+CREATE OR REPLACE VIEW vw_ride_details AS
+SELECT
+    r.ride_id,
+    -- Korisnik
+    u.user_id,
+    u.name                                  AS user_name,
+    u.phone                                 AS user_phone,
+
+    -- Vozac
+    d.driver_id,
+    d.name                                  AS driver_name,
+    d.phone                                 AS driver_phone,
+    d.rating                                AS driver_rating,
+
+    -- Avto
+    vo.plate       AS vehicle_plate,
+    vo.color       AS vehicle_color,
+    vt.type_name   AS vehicle_model,
+    vt.description AS vehicle_class,
+    vm.model       AS vehicle_brand,
+
+    -- Pickup lokacija
+    lp.grad                                 AS pickup_grad,
+    lp.naselba                              AS pickup_naselba,
+    lp.ulica                                AS pickup_ulica,
+    lp.broj                                 AS pickup_broj,
+    lp.latitude                             AS pickup_lat,
+    lp.longitude                            AS pickup_lng,
+
+    -- Dropoff lokacija
+    ld.grad                                 AS dropoff_grad,
+    ld.naselba                              AS dropoff_naselba,
+    ld.ulica                                AS dropoff_ulica,
+    ld.broj                                 AS dropoff_broj,
+    ld.latitude                             AS dropoff_lat,
+    ld.longitude                            AS dropoff_lng,
+
+    -- Vremijna sekvenca
+    r.request_time,
+    r.pickup_time,
+    r.start_time,
+    r.end_time,
+
+    -- Cena i distanca
+    r.distance_km,
+    r.discount_percentage,
+    r.final_price,
+
+    -- Status na voznata
+    s.status_text                           AS ride_status
+
+FROM Rides r
+         LEFT JOIN Users u        ON r.user_id                = u.user_id
+         LEFT JOIN Drivers d      ON r.driver_id              = d.driver_id
+         JOIN Locations lp   ON r.pickup_location_id     = lp.location_id
+         JOIN Locations ld   ON r.dropoff_location_id    = ld.location_id
+         JOIN Status s       ON r.status_id              = s.status_id
+         LEFT JOIN vehicle_ownership vo ON r.vehicle_ownership_id = vo.vehicle_ownership_id
+         LEFT JOIN vehicle_types vt ON vo.vehicle_type_id = vt.vehicle_type_id
+         LEFT JOIN vehicles_model vm ON vt.vehicles_model_id = vm.vehicles_model_id
+;
+
+select * from vw_ride_details;
+
+
+select * from vw_ride_details where user_id = 1;
+CREATE INDEX idx_rides_user_id 
+    ON rides(user_id DESC NULLS LAST);
+
+   
+select * from vw_ride_details where driver_id=56;
+CREATE INDEX idx_rides_driver_id 
+        ON rides(driver_id DESC NULLS LAST);
+   
+DROP INDEX idx_rides_user_id;
+DROP INDEX idx_rides_driver_id;
+}}}
+
+
+== VIEW 3: vw_payment_history ==
+Овој поглед претставува централен регистар на сите финансиски трансакции во рамките на такси апликацијата. Тој овозможува брз преглед на тоа кој, колку и на кој начин платил за конкретно возење, вклучувајќи ги и пресметките за попусти.
+
+Што прави овој поглед?
+
+Финансиска анализа: Ги поврзува табелите Payments, Rides и Payment_methods за да даде јасен увид во начинот на плаќање (готовина или картичка) и статусот на трансакцијата (дали е успешна, неуспешна или рефундирана).
+
+Пресметка на вредност: Покрај конечниот износ (amount), погледот динамички го пресметува износот пред попустот (amount_before_discount), што е од суштинско значење за сметководствени извештаи и евалуација на маркетинг кампањите.
+
+Контекстуални информации: За секоја уплата, погледот автоматски ги повлекува деталите за корисникот, возачот, траекторијата на возењето (адреси) и брендот на користеното возило.
+
+Статусна транспарентност: Ги комбинира статусот на плаќањето (payment_status) и статусот на самото возење (ride_status), што овозможува откривање на неконзистентности во системот.
+
+Оптимизација на перформансите
+За да се забрза пребарувањето на големиот број трансакции во базата, дефинирани се индекси за оптимизација:
+
+idx_payments_ride_id: Оптимизира пристап до плаќањето преку идентификаторот на возењето (честа операција при проверка на детали за конкретен налог).
+
+idx_payments_date: Оптимизира филтрирање на уплати по временски периоди (на пр. дневни, месечни или годишни финансиски извештаи).
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_payment_history AS
+SELECT
+    p.payment_id,
+
+    -- Korisnik
+    u.user_id,
+    u.name                                  AS user_name,
+    u.phone                                 AS user_phone,
+
+    -- Ride info
+    r.ride_id,
+    r.request_time                          AS ride_requested_at,
+    r.start_time                            AS ride_started_at,
+    r.end_time                              AS ride_ended_at,
+    r.distance_km,
+
+    -- Adresa kratko
+    lp.naselba  || ' - ' ||
+    lp.ulica    || ' '   || lp.broj         AS pickup_address,
+
+    ld.naselba  || ' - ' ||
+    ld.ulica    || ' '   || ld.broj         AS dropoff_address,
+
+    -- Vozac
+    d.driver_id,
+    d.name                                  AS driver_name,
+
+    -- Avto
+    vo.plate       AS vehicle_plate,
+    vt.type_name   AS vehicle_model,
+    vt.description AS vehicle_class,
+    vm.model       AS vehicle_brand,
+
+    -- Plakjanje
+    p.amount,
+    p.payment_time,
+    pm.payment_tip                          AS payment_method,  -- CASH / CARD
+
+    -- Originalna cena pred popust
+    ROUND(
+            p.amount / NULLIF(1.0 - r.discount_percentage / 100.0, 0)
+                ::numeric, 2)                           AS amount_before_discount,
+
+    r.discount_percentage,
+
+    -- Statusi
+    sp.status_text                          AS payment_status,
+    sr.status_text                          AS ride_status
+
+FROM Payments p
+         JOIN Rides r            ON p.ride_id                    = r.ride_id
+         JOIN Users u            ON r.user_id                    = u.user_id
+         JOIN Drivers d          ON r.driver_id                  = d.driver_id
+         JOIN Payment_methods pm ON p.payment_method_id          = pm.payment_method_id
+         JOIN Status sp          ON p.status_id                  = sp.status_id
+         JOIN Status sr          ON r.status_id                  = sr.status_id
+         JOIN Locations lp       ON r.pickup_location_id         = lp.location_id
+         JOIN Locations ld       ON r.dropoff_location_id        = ld.location_id
+         LEFT JOIN vehicle_ownership vo ON r.vehicle_ownership_id = vo.vehicle_ownership_id
+         LEFT JOIN vehicle_types vt ON vo.vehicle_type_id = vt.vehicle_type_id
+         LEFT JOIN vehicles_model vm ON vt.vehicles_model_id = vm.vehicles_model_id
+
+;
+
+select * from vw_payment_history;
+
+select * from vw_payment_history where user_id = 2267;
+CREATE INDEX idx_payments_ride_id 
+    ON payments(ride_id DESC NULLS LAST);
+
+select * from vw_payment_history where DATE(payment_time) = '2025-10-02';
+CREATE INDEX idx_payments_date 
+    ON payments(DATE(payment_time));
+    
+DROP INDEX idx_payments_ride_id;
+DROP INDEX idx_payments_date;
+}}}
+
+== VIEW 4: vw_driver_profile ==
+Овој поглед е оптимизиран за апликации од типот „Барање на возило во близина“. Неговата главна улога е да креира профил на возачите кои се во моментов достапни за примање на нови патници, филтрирајќи ги само оние што се активни и слободни во системот.
+
+Што прави овој поглед?
+
+Реална достапност: Погледот ги поврзува табелите Drivers со Active_drivers и применува филтрирање (WHERE s.status_text = 'active' AND ad.is_available IS TRUE), обезбедувајќи листа што содржи само возачи кои можат веднаш да се ангажираат.
+
+Локациска свесност: Ги вклучува моменталните географски координати и адресата (град, населба, улица) на возачот, што е неопходно за алгоритмите кои пресметуваат кој возач е најблиску до корисникот.
+
+Профилирање на возило: Обезбедува детални технички спецификации за возилото што возачот го управува во моментот (таблички, боја, година, бренд и класа), овозможувајќи му на корисникот да го препознае возилото при пристигнувањето.
+
+Економски параметри: Ја прикажува тарифата (базна цена и цена по километар) која е специфична за типот на возило на возачот, овозможувајќи транспарентна пресметка на потенцијалното возење уште пред неговото започнување.
+
+Технички прибелешки
+Релациска структура: Користи LEFT JOIN за поголемиот дел од поврзувањата (возила, локации), што осигурува дека профилот на возачот ќе биде генериран дури и ако одредени податоци (како моментална локација) привремено недостасуваат во системот.
+
+{{{#!sql
+
+CREATE OR REPLACE VIEW vw_driver_profile AS
+SELECT
+    d.driver_id,
+    d.name                                  AS driver_name,
+    d.phone                                 AS driver_phone,
+    d.rating                                AS driver_rating,
+    d.registration_date,
+
+    -- Dali e momentalno aktiven/dostapen
+    ad.is_available,
+    ad.working_start,
+    ad.working_end,
+
+    -- Momentalna lokacija
+    l.grad                                  AS current_grad,
+    l.naselba                               AS current_naselba,
+    l.ulica                                 AS current_ulica,
+    l.latitude                              AS current_lat,
+    l.longitude                             AS current_lng,
+
+    -- Vozilo
+    vo.Vehicle_ownership_id,
+    vo.Plate                                AS vehicle_plate,
+    vo.Color                                AS vehicle_color,
+    vo.year                                 AS vehicle_year,
+
+    -- Tip i brend
+    vm.Model                                AS vehicle_brand,
+    vt.type_name                            AS vehicle_model,
+    vt.description                          AS vehicle_class,   -- Economy / Luxury
+
+    -- Cena za ovoj vozac
+    pr.base_price,
+    pr.price_per_km,
+
+    -- Status na vozacot
+    s.status_text                           AS driver_status
+
+FROM Drivers d
+         JOIN Status s                       ON d.status_id              = s.status_id
+         LEFT JOIN Active_drivers ad         ON d.driver_id              = ad.driver_id
+         LEFT JOIN Locations l               ON ad.current_location_id   = l.location_id
+         LEFT JOIN Drivers_Vehicle_ownership dvo ON d.driver_id          = dvo.driver_id
+         LEFT JOIN Vehicle_ownership vo      ON dvo.Vehicle_ownership_id = vo.Vehicle_ownership_id
+         LEFT JOIN Vehicle_types vt          ON vo.vehicle_type_id       = vt.vehicle_type_id
+         LEFT JOIN Vehicles_model vm         ON vt.Vehicles_model_id     = vm.Vehicles_model_id
+         LEFT JOIN Pricing_rules pr          ON vt.vehicle_type_id       = pr.vehicle_type_id
+WHERE s.status_text = 'active' and ad.is_available is true;
+
+
+select * from vw_driver_profile;
+}}}
+
+
+== VIEW 5: vw_cancellation_details ==
+Овој поглед е наменет за анализа на откажаните возења и следење на оперативната ефикасност на системот. Тој овозможува лесна идентификација на причините за откажувањата, што е критично за подобрување на квалитетот на услугите и корисничкото искуство.
+
+Што прави овој поглед?
+
+Идентификација на одговорноста: Јасно го дефинира изворот на откажувањето преку cancelled_by_type (корисник, возач или администратор) и ја наведува причината (reason), што овозможува сегментирана анализа на проблемите.
+
+Финансиски увид: Ја прикажува „пенал-таксата“ (cancellation_fee) која се наплаќа, овозможувајќи следење на приходите остварени од откажани возења.
+
+Контекстуална анализа: Ги поврзува податоците за откажувањето со деталите за самото возење (корисник, возач, локација на поаѓање, дистанца), што помага да се утврди дали постојат специфични шеми (на пр. одредена локација или тип на возило каде откажувањата се почести).
+
+Временска рамка: Ги споредува времето на креирање на барањето (request_time) и времето на откажување (cancelled_at), што е клучен параметар за мерење на времето на одзив на возачите.
+
+{{{#!sql
+
+CREATE OR REPLACE VIEW vw_cancellation_details AS
+SELECT
+    c.cancellation_id,
+    c.ride_id,
+
+    -- Koj otkazal
+    c.cancelled_by_type,                    -- USER / DRIVER / ADMIN
+    c.cancelled_by_id,
+    c.reason,
+    c.cancellation_fee,
+    c.created_at                            AS cancelled_at,
+
+    -- Korisnik na voznata
+    u.user_id,
+    u.name                                  AS user_name,
+    u.phone                                 AS user_phone,
+
+    -- Vozac na voznata
+    d.driver_id,
+    d.name                                  AS driver_name,
+    d.phone                                 AS driver_phone,
+
+    -- Vozilo (za da se vidi dali e Economy ili Luxury)
+    vt.description                          AS vehicle_class,
+    pr.base_price                           AS tariff_base_price,
+
+    -- Pickup lokacija
+    lp.naselba                              AS pickup_naselba,
+    lp.ulica || ' ' || lp.broj             AS pickup_address,
+
+    -- Vreme na baranjeto
+    r.request_time,
+    r.distance_km,
+
+    -- Status na voznata
+    s.status_text                           AS ride_status
+
+FROM Cancellations c
+         JOIN Rides r            ON c.ride_id                    = r.ride_id
+         JOIN Users u            ON r.user_id                    = u.user_id
+         JOIN Drivers d          ON r.driver_id                  = d.driver_id
+         JOIN Status s           ON r.status_id                  = s.status_id
+         JOIN Locations lp       ON r.pickup_location_id         = lp.location_id
+         LEFT JOIN Vehicle_ownership vo      ON r.Vehicle_ownership_id = vo.Vehicle_ownership_id
+         LEFT JOIN Vehicle_types vt          ON vo.vehicle_type_id       = vt.vehicle_type_id
+         LEFT JOIN Pricing_rules pr          ON vt.vehicle_type_id       = pr.vehicle_type_id;
+
+
+select * from vw_cancellation_details;
+
+select * from vw_cancellation_details where user_id = 19020;
+
+CREATE INDEX idx_cancellations_ride_id 
+    ON cancellations(ride_id DESC NULLS LAST);
+
+DROP INDEX idx_cancellations_ride_id;
+
+}}}
+
+
+==  VIEW 6: vw_user_notifications ==
+
+Еве го описот за шестиот поглед (vw_user_notifications), подготвен за вашата техничка документација:
+
+Опис на погледот (View): vw_user_notifications
+Овој поглед е дизајниран за централизирано следење на комуникацијата со корисниците. Тој ги обединува сите системски известувања испратени до корисниците, овозможувајќи лесен увид во историјата на известувањата и контекстот во кој биле испратени.
+
+Што прави овој поглед?
+
+Контекстуална историја: Ги поврзува пораките (Messages) со конкретни возења (Rides), што му овозможува на администраторот или на самиот корисник точно да знае на што се однесува секоја нотификација (на пр. „Возачот пристигна“ за возењето од вчера).
+
+Следење на испораката: Ги прикажува статусите на нотификациите (notification_status), што е важно за дијагностицирање на проблеми доколку корисникот тврди дека не добил известување за одреден настан.
+
+Адресна контекстуализација: Ги повлекува информациите за почетната и крајната локација на возењето (pickup_address, dropoff_address), со што се добива јасна слика за рутата без потреба од дополнителни пребарувања во други табели.
+
+Оперативна транспарентност: Ги вклучува времето на испраќање (sent_time) и статусот на возењето во тој момент, што помага во анализата на однесувањето на системот за нотификации во реално време.
+
+Оптимизација на перформансите
+За да се обезбеди брзо пребарување на известувањата кои се однесуваат на одредени корисници или возења, дефинирани се индекси:
+
+idx_notifications_user_id: Оптимизира пристап до историјата на нотификации за конкретен корисник (важно за личниот профил на корисникот во апликацијата).
+
+idx_notifications_ride_id: Оптимизира пребарување на сите известувања поврзани со едно единствено возење (важно за корисничка поддршка при истражување на конкретен случај).
+{{{#!sql
+CREATE OR REPLACE VIEW vw_user_notifications AS
+SELECT
+    n.notification_id,
+
+    -- Korisnik
+    u.user_id,
+    u.name                                  AS user_name,
+
+    -- Poraka
+    m.message_id,
+    m.message_text,
+    n.sent_time,
+
+    -- Za koja voznja se odnesuva
+    n.ride_id,
+    r.request_time                          AS ride_request_time,
+
+    -- Kratka adresa za kontekst
+    lp.naselba                              AS pickup_naselba,
+    lp.ulica  ' ' 
+ lp.broj             AS pickup_address,
+    ld.ulica  ' ' 
+ ld.broj             AS dropoff_address,
+
+    -- Status na voznata (za kontekst vo notifikacijata)
+    sr.status_text                          AS ride_status,
+
+    -- Status na notifikacijata (sent / fail)
+    sn.status_text                          AS notification_status
+
+FROM Notifications n
+
+         JOIN Users u            ON n.user_id                    = u.user_id
+         JOIN Messages m         ON n.message_id                 = m.message_id
+         JOIN Rides r            ON n.ride_id                    = r.ride_id
+         JOIN Status sn          ON n.status_id                  = sn.status_id
+         JOIN Status sr          ON r.status_id                  = sr.status_id
+         JOIN Locations lp       ON r.pickup_location_id         = lp.location_id
+         JOIN Locations ld       ON r.dropoff_location_id        = ld.location_id;
+
+
+select * from vw_user_notifications;
+
+
+select * from vw_user_notifications where user_id = 16095;
+CREATE INDEX idx_notifications_user_id ON notifications(user_id DESC NULLS LAST);
+
+select * from vw_user_notifications where ride_id = 3005327;
+CREATE INDEX idx_notifications_ride_id ON notifications(ride_id DESC NULLS LAST);
+
+DROP INDEX idx_notifications_user_id;
+DROP INDEX idx_notifications_ride_id;
+}}}