Context Navigation

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

Фаза 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 наместо уникатниот клуч, но разликата е незначителна бидејќи погледот веќе работеше оптимално преку примарните клучеви.

Last modified 6 days ago Last modified on 05/19/26 17:40:47

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text