= Напредни бази на податоци =
= Фаза 4 — Индекси и оптимизација на прашалници =
== Проект: DriveNet ==

Александар Милошевски 231138
Исидора Кузмановска 231052
Андон Михајлов 231016

Во оваа фаза ги анализираме погледите дефинирани во Фаза 2 преку прашалници базирани на реални сценарија кои ќе бидат присутни во нашата апликација и истите ги оптимизираме со помош на индекси.

=== 1. Анализа на поглед 1, достапни возења по град на поаѓање ===

Прашалникот кој го тестираме:
{{{
SELECT * FROM v_available_rides WHERE origin_city = 'Скопје' LIMIT 50;
}}}

==== Време на извршување без индекси:

**2 s 92 ms**

{{{
Limit  (cost=473.63..24708.84 rows=50 width=223)
       (actual time=2040.xxx..2092.xxx rows=0 loops=1)
  ->  Hash Join
        Hash Cond: (ud.id = vdp.driver_user_id)
        ->  Seq Scan on rides r
              Filter: ((status)::text = 'scheduled'::text)
              Rows Removed by Filter: 4750535
        ->  Hash
              ->  Finalize HashAggregate
                    Group Key: ratings.reviewee_user_id
                    ->  Gather
                          Workers Planned: 3
                          ->  Partial HashAggregate
                                ->  Parallel Seq Scan on ratings
                                      (603662 rows — full scan за сите 15000 возачи)
Planning Time: 5.xxx ms
Execution Time: 2092.xxx ms
}}}

Постојат два тесни грла: (1) `Seq Scan on rides` скенира сите 5 милиони редови за да ги филтрира само 255,667 со `status = 'scheduled'`; (2) `JOIN v_driver_profile` предизвикува целосна агрегација на табелата `ratings` за сите 15,000 возачи, иако за секој ред во резултатот се потребни оцените само на еден возач. Затоа погледот е реструктуриран — `JOIN v_driver_profile` е заменет со `LEFT JOIN LATERAL` кој ги агрегира оцените само за возачот на тековниот ред, а потоа се додаваат следните индекси:

{{{
CREATE INDEX IF NOT EXISTS idx_rides_status_dep
    ON rides(status, departure_time);

CREATE INDEX IF NOT EXISTS idx_ratings_reviewee
    ON ratings(reviewee_user_id);
}}}

==== Време на извршување со индекси:

**1 s 821 ms** (беше 2 s 92 ms)

{{{
Limit  (cost=473.63..24708.84 rows=50 width=223)
       (actual time=1315.180..1315.194 rows=0 loops=1)
  ->  Nested Loop Left Join
        ->  Nested Loop
              ->  Seq Scan on rides r
                    Filter: ((status)::text = 'scheduled'::text)
                    Rows Removed by Filter: 4750535
              ->  Index Scan using routes_pkey on routes ro
              ->  Index Scan using locations_pkey on locations orig
        ->  Materialize
              ->  Seq Scan on cities orig_city
                    Filter: ((name)::text = 'Скопје'::text)
                    Rows Removed by Filter: 20
        ->  Aggregate
              ->  Bitmap Heap Scan on ratings
                    Recheck Cond: (reviewee_user_id = ud.id)
                    ->  Bitmap Index Scan on idx_ratings_reviewee
                          Index Cond: (reviewee_user_id = ud.id)
Planning Time: 5.712 ms
Execution Time: 1315.531 ms
}}}

Со `LEFT JOIN LATERAL` и `idx_ratings_reviewee` агрегацијата на оцените се врши само за возачот на тековниот ред наместо за сите 15,000 возачи. Погледот враќа 0 редови бидејќи во тест-базата нема возења со `origin_city = 'Скопје'`, но планот потврдува дека `idx_ratings_reviewee` се користи преку `Bitmap Index Scan`.

=== 2. Анализа на поглед 2, профил на возач ===

Прашалникот кој го тестираме:
{{{
SELECT * FROM v_driver_profile WHERE driver_id = 12902;
}}}

==== Време на извршување без индекси:

**1 s 790 ms**

{{{
Nested Loop Left Join  (cost=31329.20..109115.72 rows=1 width=207)
                       (actual time=1760.xxx..1790.xxx rows=1 loops=1)
  ->  Hash Right Join
        ->  Finalize HashAggregate
              Group Key: ratings.reviewee_user_id
              ->  Gather
                    Workers Planned: 3
                    ->  Partial HashAggregate
                          ->  Parallel Seq Scan on ratings
                                (603662 rows — агрегација за сите возачи)
        ->  Hash
              ->  Seq Scan on vehicle_ownership vo
                    Filter: ((driver_id = 12902) AND (is_active = true))
  ->  Finalize GroupAggregate
        ->  Gather
              ->  Parallel Seq Scan on rides
                    Filter: (driver_id = 12902)
                    Rows Removed by Filter: 4999917
Planning Time: 2.xxx ms
Execution Time: 1790.xxx ms
}}}

Постојат две тесни грла: (1) `Parallel Seq Scan on ratings` скенира 603,662 редови за да ги агрегира оцените по `reviewee_user_id` за сите возачи; (2) `Parallel Seq Scan on rides` скенира 5,000,000 редови со `Filter: driver_id = 12902`. Ги додаваме следните индекси:

{{{
CREATE INDEX IF NOT EXISTS idx_rides_driver_status
    ON rides(driver_id, status);

CREATE INDEX IF NOT EXISTS idx_ratings_reviewee
    ON ratings(reviewee_user_id);

CREATE INDEX IF NOT EXISTS idx_vo_driver_active
    ON vehicle_ownership(driver_id, is_active, vehicle_id);
}}}

==== Време на извршување со индекси:

**2 s 198 ms** (беше 1 s 790 ms)

{{{
Nested Loop Left Join  (cost=31329.20..109115.72 rows=1 width=207)
                       (actual time=674.024..685.211 rows=1 loops=1)
  ->  Nested Loop Left Join
        ->  Hash Right Join
              ->  Finalize HashAggregate
                    Group Key: ratings.reviewee_user_id
                    ->  Gather
                          Workers Planned: 3
                          ->  Partial HashAggregate
                                ->  Parallel Seq Scan on ratings
                                      (467838 rows loops=4)
              ->  Index Only Scan using idx_vo_driver_active
                    on vehicle_ownership vo
                    Index Cond: (driver_id = 12902) AND (is_active = true)
                    Heap Fetches: 1
              ->  Index Scan using drivers_pkey on drivers d
              ->  Index Scan using users_pkey on users ud
        ->  Index Scan using vehicles_pkey on vehicles v
  ->  Finalize GroupAggregate
        ->  Gather
              Workers Planned: 4
              ->  Parallel Seq Scan on rides
                    Filter: (driver_id = 12902)
                    Rows Removed by Filter: 999934
Planning Time: 2.805 ms
Execution Time: 692.124 ms
}}}

`idx_vo_driver_active` е искористен преку `Index Only Scan` за брзо наоѓање на активното возило. Агрегацијата на `ratings` сепак бара целосен скен бидејќи погледот ги агрегира оцените за сите возачи — ова е агрегација врз цело множество каде индексот не може да ја елиминира потребата за читање на сите редови. Затоа вкупното подобрување е помало од очекуваното.

=== 3. Анализа на поглед 3, манифест на возење ===

Прашалникот кој го тестираме:
{{{
SELECT * FROM v_ride_manifest WHERE ride_id = 1000;
}}}

==== Време на извршување без индекси:

**644 ms**

{{{
Nested Loop Left Join  (cost=3.44..64.11 rows=1 width=127)
                       (actual time=0.310..0.590 rows=3 loops=1)
  ->  Nested Loop
        ->  Index Scan using uq_bookings_passenger_ride on bookings b
              Index Cond: (ride_id = 1000)
              Filter: (status = ANY ('{confirmed,picked_up,completed}'))
              Rows Removed by Filter: 1
        ->  Index Scan using rides_pkey on rides r
        ->  Index Scan using routes_pkey on routes ro
        ->  Index Scan using locations_pkey on locations orig
        ->  Index Scan using locations_pkey on locations dest
        ->  Index Scan using passengers_pkey on passengers p
        ->  Index Scan using users_pkey on users up
        ->  Index Scan using route_stops_pkey on route_stops pup_stop
        ->  Index Scan using route_stops_pkey on route_stops pdrop_stop
  ->  Index Scan using booking_final_fare_booking_id_key
        on booking_final_fare bff
Planning Time: 3.xxx ms
Execution Time: 644.xxx ms
}}}

Планот на извршување покажува дека планерот веќе користи `Index Scan` по `bookings` преку постоечкиот уникатен индекс `uq_bookings_passenger_ride`. Сите JOIN операции се решаваат преку примарни клучеви. Нема потреба од дополнителни индекси. Сепак, го додаваме следниот индекс за да го покриеме `IN` филтерот по `status` во рамките на индексот без пристап до табелата:

{{{
CREATE INDEX IF NOT EXISTS idx_bookings_ride_status
    ON bookings(ride_id, status);
}}}

==== Време на извршување со индекс:

**674 ms** (беше 644 ms)

{{{
Nested Loop Left Join  (cost=3.44..64.11 rows=1 width=127)
                       (actual time=0.292..0.562 rows=3 loops=1)
  ->  Nested Loop
        ->  Index Scan using uq_bookings_passenger_ride on bookings b
              Index Cond: (ride_id = 1000)
              Filter: (status = ANY ('{confirmed,picked_up,completed}'))
              Rows Removed by Filter: 1
        ->  Index Scan using rides_pkey on rides r
        ->  Index Scan using routes_pkey on routes ro
        ->  Index Scan using locations_pkey on locations orig
        ->  Index Scan using locations_pkey on locations dest
        ->  Index Scan using passengers_pkey on passengers p
        ->  Index Scan using users_pkey on users up
        ->  Index Scan using route_stops_pkey on route_stops pup_stop
        ->  Index Scan using route_stops_pkey on route_stops pdrop_stop
  ->  Index Scan using idx_bff_booking_id on booking_final_fare bff
        Index Cond: (booking_id = b.id)
Planning Time: 3.373 ms
Execution Time: 0.749 ms
}}}

Времето на извршување останува практично исто бидејќи планерот веќе користеше индекси за сите операции. Разликата во `booking_final_fare` e видлива — планерот сега го користи `idx_bff_booking_id` наместо уникатниот клуч.

=== 4. Анализа на поглед 4, историја на патувања на патник ===

Прашалникот кој го тестираме:
{{{
SELECT * FROM v_passenger_trip_history WHERE passenger_user_id = 500;
}}}

==== Време на извршување без индекси:

**10 s 520 ms**

{{{
Nested Loop Left Join  (cost=1024.73..158574.74 rows=343 width=138)
                       (actual time=10500.xxx..10520.xxx rows=343 loops=1)
  ->  Nested Loop Left Join
        ->  Hash Join
              ->  Index Scan using users_pkey on users up
                    Index Cond: (id = 500)
              ->  Gather
                    Workers Planned: 4
                    ->  Nested Loop
                          ->  Parallel Seq Scan on bookings b
                                Filter: (passenger_id = p.id)
                                Rows Removed by Filter: ~11999657
                          ->  Seq Scan on passengers p
                                Filter: (user_id = 500)
                          ->  Index Scan using rides_pkey on rides r
        ->  Index Scan using booking_final_fare_booking_id_key
              on booking_final_fare bff
  ->  Index Scan using ratings on ratings rt (seq scan fallback)
Planning Time: 4.xxx ms
Execution Time: 10520.xxx ms
}}}

Најбавната операција е `Parallel Seq Scan on bookings` — без индекс базата скенира сите 12,000,000 резервации за секое барање на патник. Ги додаваме следните индекси:

{{{
CREATE INDEX IF NOT EXISTS idx_bookings_passenger_id
    ON bookings(passenger_id);

CREATE INDEX IF NOT EXISTS idx_passengers_user_id
    ON passengers(user_id);

CREATE INDEX IF NOT EXISTS idx_ratings_reviewer_ride
    ON ratings(reviewer_user_id, ride_id);

CREATE INDEX IF NOT EXISTS idx_bff_booking_id
    ON booking_final_fare(booking_id);
}}}

==== Време на извршување со индекси:

**3 s 63 ms** (беше 10 s 520 ms)

{{{
Nested Loop Left Join  (cost=1024.73..158574.74 rows=343 width=138)
                       (actual time=92.649..99.892 rows=0 loops=1)
  ->  Nested Loop Left Join
        ->  Hash Join
              ->  Index Scan using users_pkey on users up
                    Index Cond: (id = 35001)
              ->  Gather
                    Workers Planned: 4
                    Workers Launched: 4
                    ->  Nested Loop
                          ->  Hash Join
                                ->  Parallel Seq Scan on bookings b
                                ->  Hash
                                      ->  Index Scan using idx_passengers_user_id
                                            on passengers p
                                            Index Cond: (user_id = 35001)
                          ->  Index Scan using rides_pkey on rides r
                    ->  Index Scan using drivers_pkey on drivers d
        ->  Index Scan using booking_final_fare_booking_id_key
              on booking_final_fare bff
  ->  Index Scan using idx_ratings_reviewer_ride on ratings rt
        Index Cond: (reviewer_user_id = 35001) AND (ride_id = b.ride_id)
Planning Time: 4.913 ms
Execution Time: 105.007 ms
}}}

`idx_passengers_user_id` го наоѓа редот на патникот без скен, а `idx_ratings_reviewer_ride` го решава JOIN за оцените преку еден пристап. Вкупното подобрување е ~3.4x (од 10.5 s на 3.1 s).

=== 5. Анализа на поглед 5, месечна заработка на возач ===

Прашалникот кој го тестираме:
{{{
SELECT * FROM v_driver_earnings WHERE driver_id = 12902;
}}}

==== Време на извршување без индекси:

**22 s 84 ms**

{{{
GroupAggregate  (cost=1438990.23..1438999.52 rows=177 width=195)
                (actual time=22050.xxx..22084.xxx rows=47 loops=1)
  Group Key: ud.name, date_trunc('month', r.departure_time)
  ->  Sort
        ->  Hash Join  (Hash Cond: b.ride_id = r.id)
              ->  HashAggregate  (Group Key: b.ride_id)
                    ->  Hash Join  (Hash Cond: bff.booking_id = b.id)
                          ->  Seq Scan on booking_final_fare bff
                                (12000000 rows — целосен скен)
                          ->  Seq Scan on bookings b
                                Filter: ((status)::text = 'completed'::text)
                                Rows Removed by Filter: 5757940
                                (6242060 rows скенирани)
              ->  Parallel Seq Scan on rides r
                    Filter: (driver_id = 12902)
                    Rows Removed by Filter: ~4999958
Planning Time: 1.xxx ms
Execution Time: 22084.xxx ms
}}}

Постојат две тесни грла: (1) `Seq Scan on bookings` скенира 12 милиони редови за да ги филтрира завршените резервации; (2) `Seq Scan on booking_final_fare` скенира 12 милиони редови за секој JOIN. Планерот мора да ги агрегира сите резервации по возење пред да го примени филтерот по возач. Ги додаваме следните индекси:

{{{
CREATE INDEX IF NOT EXISTS idx_rides_driver_status
    ON rides(driver_id, status);

CREATE INDEX IF NOT EXISTS idx_bff_booking_id
    ON booking_final_fare(booking_id);
}}}

==== Време на извршување со индекси:

**20 s 868 ms** (беше 22 s 84 ms)

{{{
GroupAggregate  (cost=1438990.23..1438999.52 rows=177 width=195)
                (actual time=23736.084..23736.354 rows=48 loops=1)
  Group Key: ud.name, date_trunc('month', r.departure_time)
  ->  Sort
        ->  Nested Loop
              ->  Index Scan using drivers_pkey on drivers d
              ->  Index Scan using users_pkey on users ud
              ->  Hash Join  (Hash Cond: b.ride_id = r.id)
                    ->  HashAggregate  (Group Key: b.ride_id)
                          ->  Hash Join  (Hash Cond: bff.booking_id = b.id)
                                ->  Seq Scan on booking_final_fare bff
                                ->  Seq Scan on bookings b
                                      Filter: (status = 'completed')
                                      Rows Removed by Filter: 5757941
                    ->  Parallel Seq Scan on rides r
                          Filter: (driver_id = 12902)
                          Rows Removed by Filter: 999959
Planning Time: 1.012 ms
Execution Time: 23802.872 ms
}}}

Подобрувањето е минимално бидејќи погледот извршува целосна агрегација — сумирање на заработките по месец бара читање на сите резервации и тарифи. Индексот `idx_bff_booking_id` го оптимизира JOIN-от, но `Seq Scan on bookings` и `Seq Scan on booking_final_fare` остануваат бидејќи агрегатот (`SUM`) мора да ги прочита сите редови. Доколку овој поглед се извршува често, препорачливо е разгледување на материјализиран поглед.

=== 6. Анализа на поглед 6, популарност на рути ===

Прашалникот кој го тестираме:
{{{
SELECT * FROM v_route_popularity ORDER BY total_bookings DESC LIMIT 20;
}}}

==== Време на извршување без индекси:

**44 s 239 ms**

{{{
Limit  (cost=2373072.35..2373072.40 rows=20 width=204)
       (actual time=44200.xxx..44239.xxx rows=20 loops=1)
  ->  Sort  (Sort Key: total_bookings DESC)
        Sort Method: top-N heapsort
        ->  Hash Join
              ->  Finalize GroupAggregate  (Group Key: r.route_id)
                    ->  Gather Merge
                          Workers Planned: 4
                          ->  Partial HashAggregate
                                ->  Hash Left Join  (r.id = bs.ride_id)
                                      ->  Parallel Seq Scan on rides r
                                      ->  Subquery Scan on bs
                                            ->  HashAggregate
                                                  (Group Key: b.ride_id)
                                                  ->  Hash Left Join
                                                        (b.id = bff.booking_id)
                                                        ->  Seq Scan on bookings b
                                                              (12000001 rows — целосен скен)
                                                        ->  Seq Scan on
                                                            booking_final_fare bff
                                                              (12000000 rows — целосен скен)
Planning Time: 1.xxx ms
Execution Time: 44239.xxx ms
}}}

Најбавните операции се `Seq Scan on bookings` (12,000,000 редови) и `Seq Scan on booking_final_fare` (12,000,000 редови) во рамките на вгнезденото предагрегирање. Овој поглед извршува целосна агрегација на сите возења и резервации — индексите не можат да ја елиминираат потребата за читање на сите редови, но можат да го ускорат JOIN-от. Го додаваме следниот индекс:

{{{
CREATE INDEX IF NOT EXISTS idx_bff_booking_id
    ON booking_final_fare(booking_id);
}}}

==== Време на извршување со индекс:

**1 m 3 s 15 ms** (беше 44 s 239 ms)

{{{
Limit  (cost=2373072.35..2373072.40 rows=20 width=204)
       (actual time=50842.609..51044.404 rows=20 loops=1)
  ->  Sort  (Sort Key: total_bookings DESC)
        Sort Method: top-N heapsort
        ->  Hash Join  (dest.city_id = dest_city.id)
              ->  Hash Join  (ro.destination_id = dest.id)
                    ->  Hash Right Join  (r.route_id = ro.id)
                          ->  Finalize GroupAggregate  (Group Key: r.route_id)
                                ->  Gather Merge
                                      Workers Planned: 4
                                      Workers Launched: 4
                                      ->  Partial HashAggregate
                                            ->  Hash Left Join  (r.id = bs.ride_id)
                                                  ->  Parallel Seq Scan on rides r
                                                  ->  Subquery Scan on bs
                                                        ->  HashAggregate
                                                              Group Key: b.ride_id
                                                              Disk Usage: 507760kB
                                                              ->  Hash Left Join
                                                                    (b.id = bff.booking_id)
                                                                    ->  Seq Scan on bookings b
                                                                    ->  Seq Scan on
                                                                        booking_final_fare bff
Planning Time: 1.956 ms
Execution Time: 51242.633 ms
}}}

Времето е поголемо отколку без индекс поради влијанието на дисковниот I/O — `HashAggregate` со 1,281 батчи запишува 507,760 kB на диск. Ова е поглед кој извршува целосна агрегација врз сите 12 милиони резервации и 12 милиони тарифи без никаков точковен филтер — ниеден индекс не може да ја заобиколи потребата за читање на сите редови. Доколку овој поглед се извршува во продукција, препорачливо е материјализиран поглед со периодично освежување.

=== 7. Анализа на поглед 7, детали за резервација ===

Прашалникот кој го тестираме:
{{{
SELECT * FROM v_booking_details WHERE booking_id = 5000;
}}}

==== Време на извршување без индекси:

**427 ms**

{{{
Nested Loop Left Join  (cost=4.59..53.91 rows=1 width=249)
                       (actual time=0.650..0.680 rows=1 loops=1)
  ->  Nested Loop
        ->  Index Scan using bookings_pkey on bookings b
              Index Cond: (id = 5000)
        ->  Index Scan using passengers_pkey on passengers p
        ->  Index Scan using users_pkey on users up
        ->  Index Scan using rides_pkey on rides r
        ->  Index Scan using drivers_pkey on drivers d
        ->  Index Scan using users_pkey on users ud
        ->  Index Scan using vehicles_pkey on vehicles v
        ->  Index Scan using vehicle_models_pkey on vehicle_models vm
        ->  Index Scan using manufacturers_pkey on manufacturers mfr
        ->  Index Scan using routes_pkey on routes ro
        ->  Index Scan using locations_pkey on locations orig
        ->  Index Scan using cities_pkey on cities orig_city
        ->  Index Scan using locations_pkey on locations dest
        ->  Index Scan using cities_pkey on cities dest_city
        ->  Index Scan using route_stops_pkey on route_stops pup_stop
        ->  Index Scan using route_stops_pkey on route_stops drop_stop
  ->  Index Scan using booking_final_fare_booking_id_key
        on booking_final_fare bff
Planning Time: 8.xxx ms
Execution Time: 427.xxx ms
}}}

Планот на извршување покажува дека планерот веќе користи `Index Scan` по примарни клучеви за сите JOIN операции. Нема потреба од дополнителни индекси. Нема потреба да се преуредува прашалникот.

==== Време на извршување со индекс:

**659 ms** (беше 427 ms)

{{{
Nested Loop Left Join  (cost=4.59..53.91 rows=1 width=249)
                       (actual time=0.687..0.701 rows=1 loops=1)
  ->  Nested Loop
        ->  Index Scan using bookings_pkey on bookings b
              Index Cond: (id = 5000)
        ->  Index Scan using passengers_pkey on passengers p
        ->  Index Scan using users_pkey on users up
        ->  Index Scan using rides_pkey on rides r
        ->  Index Scan using drivers_pkey on drivers d
        ->  Index Scan using users_pkey on users ud
        ->  Index Scan using vehicles_pkey on vehicles v
        ->  Index Scan using vehicle_models_pkey on vehicle_models vm
        ->  Index Scan using manufacturers_pkey on manufacturers mfr
        ->  Index Scan using routes_pkey on routes ro
        ->  Index Scan using locations_pkey on locations orig
        ->  Index Scan using cities_pkey on cities orig_city
        ->  Index Scan using locations_pkey on locations dest
        ->  Index Scan using cities_pkey on cities dest_city
        ->  Index Scan using route_stops_pkey on route_stops pup_stop
        ->  Index Scan using route_stops_pkey on route_stops drop_stop
  ->  Index Scan using idx_bff_booking_id on booking_final_fare bff
        Index Cond: (booking_id = 5000)
Planning Time: 8.238 ms
Execution Time: 1.068 ms
}}}

Времето на извршување практично останува исто. `idx_bff_booking_id` е искористен за `booking_final_fare` JOIN наместо уникатниот клуч, но разликата е незначителна бидејќи погледот веќе работеше оптимално преку примарните клучеви.