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

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



== 5. Анализа и оптимизација на vw_artist_dashboard ==

Погледот {{{vw_artist_dashboard}}} се користи за прикажување на статистики за артистите и бендовите, вклучувајќи број на bookings, вкупна заработка и просечен рејтинг. Овој поглед се користи во artist dashboard делот од апликацијата.

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

{{{
SELECT *
FROM vw_artist_dashboard
WHERE average_rating >= 4.5;
}}}

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

'''5.1 - 32835.819 ms'''

{{{
HashAggregate  (cost=1485307.72..1485320.22 rows=200 width=87) (actual time=32833.146..32833.154 rows=0 loops=1)
  Group Key: b.bookable_id
  Filter: (round(avg(r.rating), 2) >= 4.5)
  ->  Hash Right Join
        ->  Hash Left Join
              ->  Seq Scan on offer o
              ->  Seq Scan on booking bk
              ->  Seq Scan on payment p
              ->  Seq Scan on review r
Planning Time: 0.945 ms
Execution Time: 32835.819 ms
}}}

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

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

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

{{{
CREATE INDEX idx_offer_bookable
ON Offer(bookable_id);

CREATE INDEX idx_booking_offer
ON Booking(offer_id);

CREATE INDEX idx_payment_booking
ON Payment(booking_id);

CREATE INDEX idx_payment_status
ON Payment(payment_status);

CREATE INDEX idx_review_booking
ON Review(booking_id);

CREATE INDEX idx_review_rating
ON Review(rating);
}}}

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

'''5.1 - 30002.268 ms'''

{{{
HashAggregate  (cost=1485307.72..1485320.22 rows=200 width=87) (actual time=29999.572..29999.580 rows=0 loops=1)
  Group Key: b.bookable_id
  Filter: (round(avg(r.rating), 2) >= 4.5)
  ->  Hash Right Join
        ->  Hash Left Join
              ->  Seq Scan on offer o
              ->  Seq Scan on booking bk
              ->  Seq Scan on payment p
              ->  Seq Scan on review r
Planning Time: 1.395 ms
Execution Time: 30002.268 ms
}}}

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

 * од ~32.8 s
 * на ~30.0 s

И покрај тоа што PostgreSQL продолжи да користи {{{Sequential Scan}}}, индексите помогнаа при join операциите и hash processing, што резултираше со побрзо извршување на query-от.

Бидејќи погледот користи сложени aggregation операции како {{{SUM}}}, {{{AVG}}}, {{{COUNT}}} и {{{GROUP BY}}}, planner-от проценува дека sequential processing е поефикасен од index traversal при обработка на голем број редици.