Оптимизација на прашалници и погледи

Во оваа фаза беа анализирани и оптимизирани погледите дефинирани во системот за booking на артисти и бендови. Анализата беше направена со користење на EXPLAIN ANALYZE врз query-и базирани на реални сценарија кои би се користеле во самата апликација.

Целта беше да се намали времето на извршување на query-ите преку соодветно индексирање и подобрување на execution plan-от на PostgreSQL.

1. Анализа и оптимизација на vw_available_bookables

Погледот vw_available_bookables се користи за пребарување на достапни артисти и бендови според град, жанр и статус на достапност.

Прашалниците кои беа тестирани се следните:

-- 1.2
SELECT *
FROM vw_available_bookables
WHERE genre_name = 'Pop'
AND status = 'AVAILABLE';

-- 1.3
SELECT *
FROM vw_available_bookables
WHERE city = 'Ohrid'
AND genre_name = 'Rock';

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

1.2 - 13.717 ms

Gather  (cost=1042.46..13517.18 rows=1873 width=481) (actual time=9.626..13.638 rows=0 loops=1)
  Workers Planned: 3
  Workers Launched: 3
  ->  Hash Join
        ->  Parallel Seq Scan on availabilityslot av
              Filter: ((status)::text = 'AVAILABLE'::text)
Planning Time: 1.078 ms
Execution Time: 13.717 ms

1.3 - 13.899 ms

Gather  (cost=1037.17..13320.92 rows=17 width=481) (actual time=9.876..13.817 rows=0 loops=1)
  Workers Planned: 3
  Workers Launched: 3
  ->  Hash Join
        ->  Parallel Seq Scan on availabilityslot av
              Filter: ((status)::text = 'AVAILABLE'::text)
Planning Time: 1.040 ms
Execution Time: 13.899 ms

Во почетната анализа со EXPLAIN ANALYZE беше забележано дека PostgreSQL користи Parallel Sequential Scan врз табелата AvailabilitySlot, како и Sequential Scan врз Bookable и Location. Ова значеше дека системот обработува голем број редици за да ги пронајде потребните достапни артисти и бендови.

За оптимизација беа додадени следните индекси:

CREATE INDEX idx_availability_status
ON AvailabilitySlot(status);

CREATE INDEX idx_availability_bookable
ON AvailabilitySlot(bookable_id);

CREATE INDEX idx_bookable_location
ON Bookable(location_id);

CREATE INDEX idx_location_city
ON Location(city);

CREATE INDEX idx_bookablegenre_bookable
ON BookableGenre(bookable_id);

CREATE INDEX idx_bookablegenre_genre
ON BookableGenre(genre_id);

CREATE INDEX idx_genre_name
ON Genre(genre_name);

CREATE INDEX idx_bookable_active
ON Bookable(is_active);

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

1.2 - 0.426 ms

Nested Loop  (cost=38.10..8550.23 rows=59922 width=481) (actual time=0.355..0.358 rows=0 loops=1)
  ->  Index Scan using idx_availability_bookable on availabilityslot av
Planning Time: 1.240 ms
Execution Time: 0.426 ms

1.3 - 0.753 ms

Nested Loop  (cost=3.17..365.38 rows=2397 width=481) (actual time=0.684..0.687 rows=0 loops=1)
  ->  Index Scan using idx_availability_bookable on availabilityslot av
Planning Time: 1.272 ms
Execution Time: 0.753 ms

По оптимизацијата PostgreSQL започна да користи Index Scan и Index Only Scan, со што значително се намали времето на извршување кај query-ите што филтрираат по жанр и град.

Најголемо подобрување беше забележано кај query-ите со филтрирање по жанр:

• од ~13 ms на ~0.4 ms
• од ~13 ms на ~0.7 ms

Со додавањето на индексите planner-от започна да користи индексно пребарување наместо Parallel Sequential Scan, што овозможи побрзо извршување на query-ите и подобар execution plan.

2. Анализа и оптимизација на vw_client_booking_history

Погледот vw_client_booking_history се користи за прикажување на историјата на booking-ите на клиентите, нивниот статус и информациите за плаќање.

Прашалниците кои беа тестирани се следните:

-- 2.1
SELECT *
FROM vw_client_booking_history
WHERE client_id = 1500;

-- 2.2
SELECT *
FROM vw_client_booking_history
WHERE payment_status = 'PAID';

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

2.1 - 478.399 ms

Nested Loop  (cost=394513.00..438192.78 rows=3976 width=58) (actual time=434.407..443.008 rows=0 loops=1)
  ->  Gather
        ->  Parallel Hash Right Join
              ->  Parallel Seq Scan on payment p
              ->  Parallel Seq Scan on bookingrequest br
                    Filter: (client_id = 1500)
Planning Time: 44.765 ms
Execution Time: 478.399 ms

2.2 - 159.853 ms

Nested Loop  (cost=1001.86..42632.01 rows=1 width=58) (actual time=142.971..159.779 rows=0 loops=1)
  ->  Gather
        ->  Parallel Seq Scan on payment p
              Filter: ((payment_status)::text = 'PAID'::text)
Planning Time: 1.571 ms
Execution Time: 159.853 ms

При почетната анализа беше забележано дека PostgreSQL користи Parallel Sequential Scan врз табелите BookingRequest, Booking и Payment. Ова предизвикуваше значително време на извршување, особено кај query-ите што пребаруваат според клиент или статус на плаќање.

За оптимизација беа додадени следните индекси:

CREATE INDEX idx_bookingrequest_client
ON BookingRequest(client_id);

CREATE INDEX idx_offer_request
ON Offer(request_id);

CREATE INDEX idx_booking_offer
ON Booking(offer_id);

CREATE INDEX idx_booking_status
ON Booking(booking_status);

CREATE INDEX idx_payment_booking
ON Payment(booking_id);

CREATE INDEX idx_payment_status
ON Payment(payment_status);

CREATE INDEX idx_bookable_id
ON Bookable(bookable_id);

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

2.1 - 44.997 ms

Nested Loop  (cost=1115.31..63410.15 rows=3975 width=58) (actual time=38.480..44.907 rows=0 loops=1)
  ->  Parallel Bitmap Heap Scan on bookingrequest br
        ->  Bitmap Index Scan on idx_bookingrequest_client
  ->  Index Scan using idx_offer_request on offer o
  ->  Index Scan using idx_booking_offer on booking bk
Planning Time: 2.529 ms
Execution Time: 44.997 ms

2.2 - 0.114 ms

Nested Loop  (cost=2.29..14.64 rows=1 width=58) (actual time=0.047..0.049 rows=0 loops=1)
  ->  Index Scan using idx_payment_status on payment p
Planning Time: 2.188 ms
Execution Time: 0.114 ms

По оптимизацијата PostgreSQL започна да користи:

• Bitmap Index Scan
• Index Scan
• Index Only Scan

Најголемо подобрување беше забележано кај query-от што пребарува според payment_status, каде времето на извршување се намали:

• од ~159 ms
• на ~0.1 ms

Исто така, query-от што пребарува според client_id се подобри:

• од ~478 ms
• на ~44 ms

Со додавањето на индексите planner-от започна да користи индексно пребарување наместо Parallel Sequential Scan, што овозможи значително побрзо извршување на query-ите и подобар execution plan.

3. Анализа и оптимизација на vw_public_artist_profiles

Погледот vw_public_artist_profiles се користи за прикажување на јавните профили на артистите и бендовите, нивниот град, жанр, просечен рејтинг и бројот на reviews.

Прашалникот кој беше тестиран е следниот:

SELECT *
FROM vw_public_artist_profiles
WHERE city = 'Skopje';

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

3.1 - 15423.388 ms

HashAggregate  (cost=761290.87..761380.87 rows=6000 width=497) (actual time=15419.314..15419.430 rows=213 loops=1)
  Group Key: b.bookable_id, g.genre_name
  ->  Hash Left Join
        ->  Hash Left Join
              ->  Hash Right Join
                    ->  Seq Scan on booking bk
                    ->  Seq Scan on offer o
              ->  Seq Scan on review r
Planning Time: 1.979 ms
Execution Time: 15423.388 ms

При почетната анализа со EXPLAIN ANALYZE беше забележано дека PostgreSQL користи Sequential Scan врз табелите Offer, Booking и Review, како и голем број Hash Join операции.

Поради огромниот број редици и aggregation операции, query-от имаше долго време на извршување.

За оптимизација беа додадени следните индекси:

CREATE INDEX idx_bookable_location
ON Bookable(location_id);

CREATE INDEX idx_location_city
ON Location(city);

CREATE INDEX idx_review_booking
ON Review(booking_id);

CREATE INDEX idx_offer_bookable
ON Offer(bookable_id);

CREATE INDEX idx_booking_offer
ON Booking(offer_id);

CREATE INDEX idx_bookable_rating
ON Bookable(average_rating);

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

3.1 - 15251.957 ms

HashAggregate  (cost=761290.87..761380.87 rows=6000 width=497) (actual time=15247.945..15248.063 rows=213 loops=1)
  Group Key: b.bookable_id, g.genre_name
  ->  Hash Left Join
        ->  Hash Left Join
              ->  Hash Right Join
                    ->  Seq Scan on booking bk
                    ->  Seq Scan on offer o
              ->  Seq Scan on review r
Planning Time: 1.898 ms
Execution Time: 15251.957 ms

По оптимизацијата беше забележано умерено подобрување:

• од ~15.4 s
• на ~15.2 s

И покрај додадените индекси, PostgreSQL продолжи да користи Sequential Scan бидејќи query-от обработува огромен број редици и користи сложени aggregation операции како AVG, COUNT и GROUP BY.

Во вакви ситуации planner-от проценува дека sequential processing е поефикасен од index traversal, па индексите имаат ограничено влијание врз вкупното време на извршување.

4. Анализа и оптимизација на vw_trending_bookables

Погледот vw_trending_bookables се користи за прикажување на најпопуларните артисти и бендови според бројот на bookings и просечниот рејтинг. Овој поглед се користи за homepage recommendations и analytics.

Прашалникот кој беше тестиран е следниот:

SELECT *
FROM vw_trending_bookables
ORDER BY total_bookings DESC
LIMIT 10;

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

4.1 - 22675.664 ms

Limit  (cost=1149893.25..1149893.28 rows=10 width=61) (actual time=22673.555..22673.564 rows=10 loops=1)
  ->  Sort
        Sort Key: (count(bk.booking_id)) DESC
        ->  HashAggregate
              Group Key: b.bookable_id
              ->  Hash Right Join
                    ->  Hash Left Join
                          ->  Seq Scan on offer o
                          ->  Seq Scan on booking bk
                          ->  Seq Scan on review r
Planning Time: 1.190 ms
Execution Time: 22675.664 ms

При почетната анализа со EXPLAIN ANALYZE беше забележано дека PostgreSQL користи Sequential Scan врз табелите Offer, Booking и Review, како и HashAggregate и Sort операции за пресметување на бројот на bookings и просечниот рејтинг.

Поради огромниот број редици и aggregation операции, query-от имаше многу долго време на извршување.

За оптимизација беа додадени следните индекси:

CREATE INDEX idx_offer_bookable
ON Offer(bookable_id);

CREATE INDEX idx_booking_offer
ON Booking(offer_id);

CREATE INDEX idx_review_booking
ON Review(booking_id);

CREATE INDEX idx_review_rating
ON Review(rating);

CREATE INDEX idx_bookable_id
ON Bookable(bookable_id);

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

4.1 - 22582.888 ms

Limit  (cost=1149893.25..1149893.28 rows=10 width=61) (actual time=22580.768..22580.777 rows=10 loops=1)
  ->  Sort
        Sort Key: (count(bk.booking_id)) DESC
        ->  HashAggregate
              Group Key: b.bookable_id
              ->  Hash Right Join
                    ->  Hash Left Join
                          ->  Seq Scan on offer o
                          ->  Seq Scan on booking bk
                          ->  Seq Scan on review r
Planning Time: 1.633 ms
Execution Time: 22582.888 ms

По оптимизацијата беше забележано мало подобрување кај query-от што ги прикажува најпопуларните артисти:

• од ~22.6 s
• на ~22.5 s

И покрај додадените индекси, PostgreSQL продолжи да користи Sequential Scan бидејќи query-от обработува огромен број редици и користи сложени aggregation операции како COUNT, AVG, GROUP BY и ORDER BY.

Во вакви ситуации planner-от проценува дека sequential processing е поефикасен од index traversal, па индексите имаат ограничено влијание врз вкупното време на извршување.