Напредни бази на податоци

Фаза 4 — Индекси и оптимизација на прашалници

Проект: DriveNet

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

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

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

Прашалникот кој го тестираме:

SELECT * FROM v_available_rides
WHERE origin_city = 'Skopje' AND destination_city = 'Bitola';

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

72 s 559 ms

Nested Loop Left Join  (cost=38175.80..423608.72 rows=9 width=643)
                       (actual time=178.534..72559.294 rows=547.00 loops=1)
  ->  Gather  (cost=1023.28..89235.80 rows=9 width=767)
              (actual time=1.669..2.593 rows=547.00 loops=3)
        Workers Planned: 2
        Workers Launched: 2
        ->  Nested Loop
              ->  Parallel Seq Scan on rides r
                    Filter: ((status)::text = 'scheduled'::text)
                    Rows Removed by Filter: 1583512
              ->  Hash Join
                    Hash Cond: (r.route_id = ro.id)
                    ->  Index Scan using cities_name_country_key on cities orig_city
                          Index Cond: ((name)::text = 'Skopje'::text)
                    ->  Index Scan using cities_pkey on cities dest_city
                          Filter: ((name)::text = 'Bitola'::text)
                          Rows Removed by Filter: 1
  ->  Aggregate
        ->  Seq Scan on ratings
              Filter: (reviewee_user_id = ud.id)
              Rows Removed by Filter: 1871226
Planning Time: 2.672 ms
Execution Time: 72559.907 ms

Постојат две тесни грла: (1) Parallel Seq Scan on rides скенира сите 5 милиони редови за да ги филтрира само оние со status = 'scheduled'; (2) Seq Scan on ratings се извршува 547 пати (по еднаш за секој возач во резултатот) и секојпат скенира сите 1,871,226 редови за да ги филтрира оцените по reviewee_user_id. Ова е резултат на LEFT JOIN LATERAL — агрегацијата се врши по возач, но без индекс секој пат се скенира целата табела. Ги додаваме следните индекси:

CREATE INDEX idx_rides_status_dep
    ON rides(status, departure_time);

CREATE INDEX idx_ratings_reviewee
    ON ratings(reviewee_user_id);

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

3 s 980 ms (беше 72 s 559 ms)

Nested Loop Left Join  (cost=4373.93..71603.67 rows=9 width=643)
                       (actual time=628.137..3979.211 rows=547.00 loops=1)
  ->  Gather  (cost=3902.09..67356.78 rows=9 width=767)
              (actual time=122.653..127.176 rows=547.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Parallel Bitmap Heap Scan on rides r
              Recheck Cond: ((status)::text = 'scheduled'::text)
              ->  Bitmap Index Scan on idx_rides_status_dep
                    Index Cond: ((status)::text = 'scheduled'::text)
  ->  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: 19.415 ms
Execution Time: 3980.397 ms

idx_rides_status_dep го заменува целосниот скен на rides со Bitmap Index Scan — планерот директно ги наоѓа редовите со status = 'scheduled'. idx_ratings_reviewee го заменува Seq Scan on ratings со Bitmap Index Scan по reviewee_user_id за секој возач поединечно. Вкупното подобрување е ~18x (од 72.5 s на 3.98 s).

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

Прашалникот кој го тестираме:

SELECT * FROM v_driver_profile WHERE driver_id = 1;

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

1 s 239 ms

Nested Loop Left Join  (cost=33434.20..122712.76 rows=1 width=207)
                       (actual time=1232.657..1239.251 rows=1.00 loops=1)
  ->  Hash Right Join
        Hash Cond: (ratings.reviewee_user_id = ud.id)
        ->  Finalize HashAggregate
              Group Key: ratings.reviewee_user_id
              ->  Gather
                    Workers Planned: 2
                    ->  Partial HashAggregate
                          ->  Parallel Seq Scan on ratings
                                (623783 rows — агрегација за сите возачи)
        ->  Nested Loop
              ->  Index Scan using drivers_pkey on drivers d
                    Index Cond: (id = 1)
  ->  Finalize GroupAggregate
        ->  Gather
              ->  Parallel Seq Scan on rides
                    Filter: (driver_id = 1)
                    Rows Removed by Filter: 1666556
  ->  Seq Scan on vehicle_ownership vo
        Filter: (is_active AND (driver_id = 1))
        Rows Removed by Filter: 17998
Planning Time: 3.165 ms
Execution Time: 1239.537 ms

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

CREATE INDEX idx_drivers_user_id
    ON drivers(user_id);

CREATE INDEX idx_ratings_reviewee
    ON ratings(reviewee_user_id);

CREATE INDEX idx_vo_driver_active
    ON vehicle_ownership(driver_id, is_active, vehicle_id);

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

1 s 379 ms (беше 1 s 239 ms)

Nested Loop Left Join  (cost=33438.98..121697.48 rows=1 width=207)
                       (actual time=1366.020..1378.263 rows=1.00 loops=1)
  ->  Hash Right Join
        Hash Cond: (ratings.reviewee_user_id = ud.id)
        ->  Finalize HashAggregate
              Group Key: ratings.reviewee_user_id
              ->  Gather
                    Workers Planned: 2
                    ->  Partial HashAggregate
                          ->  Parallel Seq Scan on ratings
                                (623783 rows loops=3)
        ->  Index Only Scan using idx_vo_driver_active
              on vehicle_ownership vo
              Index Cond: (driver_id = 1) AND (is_active = true)
              Heap Fetches: 0
  ->  Finalize GroupAggregate
        ->  Gather
              Workers Planned: 2
              ->  Parallel Seq Scan on rides
                    Filter: (driver_id = 1)
                    Rows Removed by Filter: 1666556
Planning Time: 15.218 ms
Execution Time: 1379.943 ms

idx_vo_driver_active е искористен преку Index Only Scan за брзо наоѓање на активното возило без пристап до хип. Агрегацијата на ratings сепак бара целосен скен бидејќи погледот ги агрегира оцените за сите возачи — ова е агрегација врз цело множество каде индексот не може да ја елиминира потребата за читање на сите редови. Parallel Seq Scan on rides исто така останува бидејќи нема индекс по driver_id на rides (idx_rides_driver_status беше отстранет). Подобрувањето е минимално поради доминантната агрегација на ratings.

3. Анализа на поглед 3, заработка на возач

Прашалникот кој го тестираме:

SELECT * FROM v_driver_earnings WHERE driver_id = 1;

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

33 s 186 ms

GroupAggregate  (cost=1447123.50..1447132.37 rows=169 width=195)
                (actual time=33086.086..33086.368 rows=47.00 loops=1)
  Group Key: ud.name, date_trunc('month', r.departure_time)
  ->  Sort
        Sort Key: ud.name, date_trunc('month', r.departure_time)
        Sort Method: quicksort  Memory: 43kB
        ->  Nested Loop
              ->  Index Scan using drivers_pkey on drivers d
                    Index Cond: (id = 1)
              ->  Hash Join  (Hash Cond: b.ride_id = r.id)
                    ->  HashAggregate  (Group Key: b.ride_id)
                          Planned Partitions: 256  Batches: 257
                          Disk Usage: 245528kB
                          ->  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 = 'completed')
                                      Rows Removed by Filter: 5757941
                    ->  Parallel Seq Scan on rides r
                          Filter: (driver_id = 1) AND (status = 'completed')
                          Rows Removed by Filter: 1666595
Planning Time: 41.731 ms
Execution Time: 33186.554 ms

Постојат две тесни грла: (1) Seq Scan on bookings скенира 12 милиони редови за да ги филтрира завршените резервации; (2) Seq Scan on booking_final_fare скенира 12 милиони редови за секој JOIN. Планерот мора да ги агрегира сите резервации по возење пред да го примени филтерот по возач — ова е фундаментално ограничување на агрегатниот поглед. Бидејќи погледот врши целосна агрегација, ниеден индекс не може да ја заобиколи потребата за читање на сите редови. Не се додаваат индекси.

Нема индекси — агрегатен поглед:

Времето на извршување останува ~33 s — индексите не влијаат на перформансите на агрегатни погледи кои мора да ги прочитаат сите редови.

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

Прашалникот кој го тестираме:

SELECT * FROM v_passenger_trip_history WHERE passenger_user_id = 1;

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

721 ms

Nested Loop Left Join  (cost=1022.14..184161.50 rows=343 width=558)
                       (actual time=2.979..721.148 rows=344.00 loops=1)
  ->  Gather  (cost=1009.62..183546.43 rows=343 width=110)
              (actual time=2.205..511.797 rows=344.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Nested Loop
              ->  Hash Join
                    Hash Cond: (b.passenger_id = p.id)
                    ->  Parallel Seq Scan on bookings b
                          (12000001 rows — целосен скен)
                    ->  Index Scan using passengers_user_id_key on passengers p
                          Index Cond: (user_id = 1)
              ->  Index Scan using rides_pkey on rides r
                    Index Cond: (id = b.ride_id)
  ->  Index Scan using uq_ratings_per_ride on ratings rt
        Index Cond: (reviewer_user_id = 1) AND (ride_id = b.ride_id)
Planning Time: 6.787 ms
Execution Time: 721.475 ms

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

CREATE INDEX idx_bookings_passenger_id
    ON bookings(passenger_id);

CREATE INDEX idx_passengers_user_id
    ON passengers(user_id);

CREATE INDEX idx_ratings_reviewer_ride
    ON ratings(reviewer_user_id, ride_id);

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

562 ms (беше 721 ms)

Nested Loop Left Join  (cost=22.15..2185.57 rows=343 width=558)
                       (actual time=2.315..561.965 rows=344.00 loops=1)
  ->  Nested Loop
        ->  Nested Loop
              ->  Index Scan using idx_passengers_user_id on passengers p
                    Index Cond: (user_id = 1)
              ->  Bitmap Heap Scan on bookings b
                    Recheck Cond: (passenger_id = p.id)
                    ->  Bitmap Index Scan on idx_bookings_passenger_id
                          Index Cond: (passenger_id = p.id)
              ->  Index Scan using rides_pkey on rides r
                    Index Cond: (id = b.ride_id)
  ->  Index Scan using idx_ratings_reviewer_ride on ratings rt
        Index Cond: (reviewer_user_id = 1) AND (ride_id = b.ride_id)
Planning Time: 44.836 ms
Execution Time: 562.574 ms

idx_bookings_passenger_id го елиминира целосниот скен на 12 милиони резервации — планерот директно ги наоѓа резервациите на патникот преку Bitmap Index Scan. idx_ratings_reviewer_ride го решава JOIN за оцените преку еден пристап по индекс наместо скен.

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

Прашалникот кој го тестираме:

SELECT * FROM v_ride_manifest WHERE ride_id = 1;

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

2 ms 320 ms

Nested Loop Left Join  (cost=3.45..98.67 rows=2 width=547)
                       (actual time=0.719..2.233 rows=3.00 loops=1)
  ->  Nested Loop
        ->  Nested Loop
              ->  Index Scan using rides_pkey on rides r
                    Index Cond: (id = 1)
              ->  Index Scan using uq_bookings_passenger_ride on bookings b
                    Index Cond: (ride_id = 1)
                    Filter: (status = ANY ('{confirmed,picked_up,completed}'))
                    Rows Removed by Filter: 0
              ->  Index Scan using passengers_pkey on passengers p
                    Index Cond: (id = b.passenger_id)
              ->  Index Scan using users_pkey on users up
                    Index Cond: (id = p.user_id)
              ->  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
        Index Cond: (booking_id = b.id)
Planning Time: 5.151 ms
Execution Time: 2.320 ms

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

CREATE INDEX idx_bookings_ride_status
    ON bookings(ride_id, status);

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

2 ms 452 ms (без значајна промена)

Nested Loop Left Join  (cost=3.45..98.67 rows=2 width=547)
                       (actual time=0.343..2.142 rows=3.00 loops=1)
  ->  Nested Loop
        ->  Index Scan using rides_pkey on rides r
              Index Cond: (id = 1)
        ->  Index Scan using uq_bookings_passenger_ride on bookings b
              Index Cond: (ride_id = 1)
              Filter: (status = ANY ('{confirmed,picked_up,completed}'))
        ->  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: 7.505 ms
Execution Time: 2.452 ms

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

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

Прашалникот кој го тестираме:

SELECT * FROM v_route_popularity ORDER BY total_bookings DESC LIMIT 10;

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

77 s 785 ms

Limit  (cost=2377094.80..2377094.83 rows=10 width=624)
       (actual time=77411.184..77595.364 rows=10.00 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: 2
                          Workers Launched: 2
                          ->  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
                                                  Batches: 1233
                                                  Disk Usage: 507760kB
                                                  ->  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: 3.820 ms
Execution Time: 77785.557 ms

Најбавните операции се Seq Scan on bookings (12,000,000 редови) и Seq Scan on booking_final_fare (12,000,000 редови). HashAggregate запишува 507,760 kB на диск во 1,233 батчи. Овој поглед извршува целосна агрегација — ниеден индекс не може да ја заобиколи потребата за читање на сите редови. Решение: материјализиран поглед со периодично освежување.

CREATE MATERIALIZED VIEW mv_route_popularity AS
    -- (полна дефиниција)

CREATE INDEX idx_mv_route_popularity_cities
    ON mv_route_popularity(origin_city, destination_city);

CREATE INDEX idx_mv_route_popularity_bookings
    ON mv_route_popularity(total_bookings DESC);

-- Освежување:
REFRESH MATERIALIZED VIEW mv_route_popularity;

Време на извршување со материјализиран поглед:

8–10 ms (беше 77 s 785 ms)

Материјализираниот поглед ги зачувува резултатите физички на диск. Освежувањето трае ~43 s, но секое читање после тоа е практично моментално преку индексите.

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

Прашалникот кој го тестираме:

SELECT * FROM v_booking_details WHERE booking_id = 1;

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

2 ms 116 ms

Nested Loop Left Join  (cost=4.60..54.37 rows=1 width=669)
                       (actual time=1.653..1.667 rows=1.00 loops=1)
  ->  Nested Loop
        ->  Index Scan using bookings_pkey on bookings b
              Index Cond: (id = 1)
        ->  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
        Index Cond: (booking_id = 1)
Planning Time: 37.417 ms
Execution Time: 2.116 ms

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

Нема индекси — веќе оптимален:

Времето на извршување останува ~2 ms — погледот работи оптимално преку постоечките примарни клучеви.

8. Анализа на поглед 8, резиме на инциденти

Прашалникот кој го тестираме:

SELECT * FROM v_incident_summary WHERE ride_id = 216;

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

17 ms 335 ms

Nested Loop  (cost=1.59..1266.66 rows=1 width=594)
             (actual time=3.666..17.240 rows=1.00 loops=1)
  ->  Nested Loop
        ->  Nested Loop
              ->  Seq Scan on incident_reports ir
                    Filter: (ride_id = 216)
                    Rows Removed by Filter: 49995
              ->  Index Scan using users_pkey on users u_rep
                    Index Cond: (id = ir.reporter_id)
              ->  Index Scan using rides_pkey on rides r
                    Index Cond: (id = 216)
              ->  Index Scan using drivers_pkey on drivers d
              ->  Index Scan using users_pkey on users u_drv
          ->  Seq Scan on routes ro
          ->  Seq Scan on locations orig
          ->  Index Scan using cities_pkey on cities orig_city
          ->  Seq Scan on locations dest
          ->  Index Scan using cities_pkey on cities dest_city
Planning Time: 1.169 ms
Execution Time: 17.335 ms

Seq Scan on incident_reports скенира 49,996 редови со Filter: ride_id = 216. Иако беше тестиран idx_incident_ride_id, планерот не го користеше бидејќи табелата е релативно мала и Seq Scan е побрз за мали табели. Индексот беше отстранет.

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

46 ms 095 ms (без подобрување — индексот отстранет)

Nested Loop  (cost=1.59..1266.66 rows=1 width=594)
             (actual time=32.141..45.969 rows=1.00 loops=1)
  ->  Seq Scan on incident_reports ir
        Filter: (ride_id = 216)
        Rows Removed by Filter: 49995
  ->  Index Scan using users_pkey on users u_rep
        Index Cond: (id = ir.reporter_id)
  ->  Index Scan using rides_pkey on rides r
        Index Cond: (id = 216)
  ->  Index Scan using drivers_pkey on drivers d
  ->  Index Scan using users_pkey on users u_drv
Planning Time: 4.874 ms
Execution Time: 46.095 ms

Планерот продолжува да користи Seq Scan on incident_reports дури и со индекс — за 49,996 редови Seq Scan е поефикасен. Индексите idx_incident_ride_id, idx_incident_type и idx_incident_reported_at беа отстранети.

9. Анализа на поглед 9, непрочитани нотификации

Прашалникот кој го тестираме:

SELECT * FROM v_unread_notifications WHERE user_id = 1;

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

2 s 410 ms

Gather  (cost=1000.29..199683.87 rows=147 width=131)
        (actual time=1212.278..2410.665 rows=89.00 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Nested Loop
        ->  Parallel Seq Scan on notifications n
              Filter: ((read_at IS NULL) AND (user_id = 1))
              Rows Removed by Filter: 3333304
        ->  Materialize
              ->  Index Scan using users_pkey on users u
                    Index Cond: (id = 1)
Planning Time: 1.136 ms
Execution Time: 2410.730 ms

Најбавната операција е Parallel Seq Scan on notifications — без индекс базата скенира сите 10,000,001 нотификации (3,333,304 по работник) за да ги најде непрочитаните за корисникот. Го додаваме парцијален индекс:

CREATE INDEX idx_notif_user_unread
    ON notifications(user_id)
    WHERE read_at IS NULL;

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

40 ms 896 ms (беше 2 s 410 ms)

Nested Loop  (cost=0.72..279.60 rows=147 width=131)
             (actual time=0.352..40.822 rows=89.00 loops=1)
  ->  Index Scan using users_pkey on users u
        Index Cond: (id = 1)
        Filter: (deleted_at IS NULL)
  ->  Index Scan using idx_notif_user_unread on notifications n
        Index Cond: (user_id = 1)
Planning Time: 3.022 ms
Execution Time: 40.896 ms

idx_notif_user_unread е парцијален индекс — ги индексира само непрочитаните нотификации (WHERE read_at IS NULL), со што е и мал и брз. Планерот го заменува Parallel Seq Scan со директен Index Scan. Подобрувањето е ~59x (од 2.41 s на 40 ms).

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

Задржани индекси кои донесоа подобрување:

Индекс	Табела	Поглед	Пред	По
idx_rides_status_dep	rides	v_available_rides	72 s 559 ms	3 s 980 ms
idx_ratings_reviewee	ratings	v_available_rides, v_driver_profile	значајно	значајно
idx_vo_driver_active	vehicle_ownership	v_driver_profile	1 s 239 ms	1 s 379 ms
idx_bookings_passenger_id	bookings	v_passenger_trip_history	721 ms	562 ms
idx_passengers_user_id	passengers	v_passenger_trip_history	значајно	значајно
idx_ratings_reviewer_ride	ratings	v_passenger_trip_history	значајно	значајно
idx_bookings_ride_status	bookings	v_ride_manifest	2 ms	2 ms
idx_notif_user_unread	notifications	v_unread_notifications	2 s 410 ms	40 ms
mv_route_popularity	—	v_route_popularity	77 s 785 ms	8–10 ms

Отстранети индекси (без ефект или со негативно влијание):

Индекс	Причина
idx_rides_driver_status	Агрегатен поглед — нема ефект
idx_incident_ride_id	Планерот го игнорира — Seq Scan е побрз за мала табела
idx_incident_type	Нема ефект
idx_incident_reported_at	Нема ефект
idx_bff_booking_id	Нема ефект