wiki:QueryOptimization

Version 17 (modified by 231088, 15 hours ago) ( diff )

--

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

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

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

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

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

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

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

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

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

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

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

Бидејќи индексите не можеа значително да го подобрат времето на извршување поради природата на query-от (full-table aggregation со GROUP BY и повеќе Hash Join операции), беше креиран Материјализиран Поглед (MATERIALIZED VIEW) кој ја зачувува пресметаната агрегација и овозможува директно пребарување врз резултатот.

За разлика од обичниот поглед кој го извршува query-от секој пат, Материјализираниот Поглед ги зачувува резултатите физички на диск, со што филтерот WHERE city = 'Skopje' работи врз само неколку стотици редици наместо врз милиони. 

CREATE MATERIALIZED VIEW mv_public_artist_profiles AS
SELECT
    b.bookable_id,
    b.display_name,
    b.bookable_type,
    l.city,
    g.genre_name,
    ROUND(AVG(r.rating), 2) AS average_rating,
    COUNT(r.review_id)      AS total_reviews
FROM Bookable b
LEFT JOIN Location l       ON b.location_id = l.location_id
LEFT JOIN BookableGenre bg ON b.bookable_id = bg.bookable_id
LEFT JOIN Genre g          ON bg.genre_id = g.genre_id
LEFT JOIN Offer o          ON b.bookable_id = o.bookable_id
LEFT JOIN Booking bk       ON bk.offer_id = o.offer_id
LEFT JOIN Review r         ON r.booking_id = bk.booking_id
GROUP BY b.bookable_id, b.display_name, b.bookable_type, l.city, g.genre_name;

CREATE UNIQUE INDEX idx_mv_profiles_bookable_id
ON mv_public_artist_profiles(bookable_id);

CREATE INDEX idx_mv_profiles_city
ON mv_public_artist_profiles(city);

CREATE INDEX idx_mv_profiles_rating
ON mv_public_artist_profiles(average_rating DESC);

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

3.1 - 0.147 ms 

Bitmap Heap Scan on mv_public_artist_profiles  (cost=4.35..24.46 rows=9 width=856) (actual time=0.040..0.114 rows=213 loops=1)
  Recheck Cond: ((city)::text = 'Skopje'::text)
  Heap Blocks: exact=16
  ->  Bitmap Index Scan on idx_mv_profiles_city  (cost=0.00..4.34 rows=9 width=0) (actual time=0.022..0.022 rows=213 loops=1)
        Index Cond: ((city)::text = 'Skopje'::text)
Planning Time: 0.275 ms
Execution Time: 0.147 ms

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

  • од ~15.2 s (со индекси)
  • на ~0.147 ms (со Материјализиран Поглед)

Наместо да ги скенира милионите редици од табелите Offer, Booking и Review, PostgreSQL сега користи Bitmap Index Scan директно врз зачуваниот резултат преку индексот на city. Ова претставува подобрување од околу 103,000 пати во споредба со оригиналниот query со индекси.

За да останат податоците ажурирани, Материјализираниот Поглед се освежува по секоја промена на релевантните табели: 

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_public_artist_profiles;

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

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

SELECT *
FROM vw_trending_bookables
ORDER BY total_bookings DESC
LIMIT 10;

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

4.1 - 23693.914 ms 

Limit  (cost=1149893.25..1149893.28 rows=10 width=61) (actual time=23691.798..23691.806 rows=10 loops=1)
  ->  Sort  (cost=1149893.25..1149894.75 rows=600 width=61) (actual time=23476.040..23476.046 rows=10 loops=1)
        Sort Key: (count(bk.booking_id)) DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  HashAggregate  (cost=1149871.28..1149880.28 rows=600 width=61) (actual time=23475.685..23475.836 rows=600 loops=1)
              Group Key: b.bookable_id
              Batches: 1  Memory Usage: 169kB
              ->  Hash Right Join  (cost=251298.36..999871.28 rows=20000000 width=29) (actual time=4249.553..20639.674 rows=20000595 loops=1)
                    Hash Cond: (o.bookable_id = b.bookable_id)
                    ->  Hash Left Join  (cost=251269.86..946971.95 rows=20000000 width=12) (actual time=4249.291..17174.982 rows=20000000 loops=1)
                          Hash Cond: (o.offer_id = bk.offer_id)
                          ->  Seq Scan on offer o  (cost=0.00..329899.00 rows=20000000 width=8) (actual time=0.037..2604.142 rows=20000000 loops=1)
                          ->  Hash  (cost=181713.23..181713.23 rows=4001410 width=12) (actual time=4247.656..4247.659 rows=4001410 loops=1)
                                ->  Hash Left Join  (cost=40503.35..181713.23 rows=4001410 width=12) (actual time=401.092..2863.158 rows=4001410 loops=1)
                                      Hash Cond: (bk.booking_id = r.booking_id)
                                      ->  Seq Scan on booking bk  (cost=0.00..78258.10 rows=4001410 width=8) (actual time=0.043..582.369 rows=4001410 loops=1)
                                      ->  Hash  (cost=20819.49..20819.49 rows=1199749 width=8) (actual time=397.674..397.675 rows=1199749 loops=1)
                                            ->  Seq Scan on review r  (cost=0.00..20819.49 rows=1199749 width=8) (actual time=0.022..133.592 rows=1199749 loops=1)
                    ->  Hash  (cost=21.00..21.00 rows=600 width=21) (actual time=0.235..0.236 rows=600 loops=1)
                          ->  Seq Scan on bookable b  (cost=0.00..21.00 rows=600 width=21) (actual time=0.029..0.119 rows=600 loops=1)
Planning Time: 84.544 ms
Execution Time: 23693.914 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 - 26028.630 ms 

Limit  (cost=1149893.25..1149893.28 rows=10 width=61) (actual time=26026.371..26026.380 rows=10 loops=1)
  ->  Sort  (cost=1149893.25..1149894.75 rows=600 width=61) (actual time=25818.135..25818.141 rows=10 loops=1)
        Sort Key: (count(bk.booking_id)) DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  HashAggregate  (cost=1149871.28..1149880.28 rows=600 width=61) (actual time=25817.826..25817.963 rows=600 loops=1)
              Group Key: b.bookable_id
              Batches: 1  Memory Usage: 169kB
              ->  Hash Right Join  (cost=251298.36..999871.28 rows=20000000 width=29) (actual time=3790.821..22958.347 rows=20000595 loops=1)
                    Hash Cond: (o.bookable_id = b.bookable_id)
                    ->  Hash Left Join  (cost=251269.86..946971.95 rows=20000000 width=12) (actual time=3790.550..19482.658 rows=20000000 loops=1)
                          Hash Cond: (o.offer_id = bk.offer_id)
                          ->  Seq Scan on offer o  (cost=0.00..329899.00 rows=20000000 width=8) (actual time=0.048..2551.431 rows=20000000 loops=1)
                          ->  Hash  (cost=181713.23..181713.23 rows=4001410 width=12) (actual time=3789.013..3789.016 rows=4001410 loops=1)
                                ->  Hash Left Join  (cost=40503.35..181713.23 rows=4001410 width=12) (actual time=393.530..2817.254 rows=4001410 loops=1)
                                      Hash Cond: (bk.booking_id = r.booking_id)
                                      ->  Seq Scan on booking bk  (cost=0.00..78258.10 rows=4001410 width=8) (actual time=0.038..571.295 rows=4001410 loops=1)
                                      ->  Hash  (cost=20819.49..20819.49 rows=1199749 width=8) (actual time=390.800..390.801 rows=1199749 loops=1)
                                            ->  Seq Scan on review r  (cost=0.00..20819.49 rows=1199749 width=8) (actual time=0.021..128.328 rows=1199749 loops=1)
                    ->  Hash  (cost=21.00..21.00 rows=600 width=21) (actual time=0.244..0.245 rows=600 loops=1)
                          ->  Seq Scan on bookable b  (cost=0.00..21.00 rows=600 width=21) (actual time=0.035..0.128 rows=600 loops=1)
Planning Time: 1.646 ms
Execution Time: 26028.630 ms

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

  • од ~23.7 s
  • на ~26.0 s

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

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

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

Бидејќи индексите не можеа значително да го подобрат времето на извршување поради природата на query-от (full-table aggregation со GROUP BY и ORDER BY), беше креиран Материјализиран Поглед (MATERIALIZED VIEW) кој ја зачувува пресметаната агрегација и овозможува директно пребарување врз резултатот.

За разлика од обичниот поглед кој го извршува query-от секој пат, Материјализираниот Поглед ги зачувува резултатите физички на диск, со што сортирањето и броењето работат врз само 600 редици наместо врз 20 милиони. 

CREATE MATERIALIZED VIEW mv_trending_bookables AS
SELECT
    b.bookable_id,
    b.display_name,
    b.bookable_type,
    COUNT(bk.booking_id)    AS total_bookings,
    ROUND(AVG(r.rating), 2) AS average_rating
FROM Bookable b
LEFT JOIN Offer o    ON b.bookable_id = o.bookable_id
LEFT JOIN Booking bk ON bk.offer_id = o.offer_id
LEFT JOIN Review r   ON r.booking_id = bk.booking_id
GROUP BY b.bookable_id, b.display_name, b.bookable_type;

CREATE UNIQUE INDEX idx_mv_trending_bookable_id
ON mv_trending_bookables(bookable_id);

CREATE INDEX idx_mv_trending_total_bookings
ON mv_trending_bookables(total_bookings DESC);

CREATE INDEX idx_mv_trending_avg_rating
ON mv_trending_bookables(average_rating DESC);

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

4.1 - 0.035 ms 

Limit  (cost=0.15..0.97 rows=10 width=420) (actual time=0.012..0.017 rows=10 loops=1)
  ->  Index Scan using idx_mv_trending_total_bookings on mv_trending_bookables  (cost=0.15..49.15 rows=600 width=420) (actual time=0.011..0.015 rows=10 loops=1)
Planning Time: 0.339 ms
Execution Time: 0.035 ms

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

  • од ~26.0 s (со индекси)
  • на ~0.035 ms (со Материјализиран Поглед)

Наместо да ги скенира 20 милиони редици од табелите Offer, Booking и Review, PostgreSQL сега користи Index Scan директно врз зачуваниот резултат од само 600 редици. Ова претставува подобрување од околу 742,000 пати во споредба со оригиналниот query со индекси.

За да останат податоците ажурирани, Материјализираниот Поглед се освежува по секоја промена на релевантните табели: 

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_trending_bookables;

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

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

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

SELECT *
FROM vw_artist_dashboard
WHERE average_rating >= 4.5;

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

5.1 - 66721.664 ms 

HashAggregate  (cost=1485307.72..1485320.22 rows=200 width=87) (actual time=66672.114..66672.131 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: 66721.664 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 - 31275.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: 31275.268 ms

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

  • од ~66.7 s
  • на ~31.3 s

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

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

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

Бидејќи индексите не можеа значително да го подобрат времето на извршување поради природата на query-от (full-table aggregation со GROUP BY), беше креиран Материјализиран Поглед (MATERIALIZED VIEW) кој ја зачувува пресметаната агрегација и овозможува директно пребарување врз резултатот.

За разлика од обичниот поглед кој го извршува query-от секој пат, Материјализираниот Поглед ги зачувува резултатите физички на диск, со што филтерот WHERE average_rating >= 4.5 работи врз само 600 редици наместо врз милиони. 

CREATE MATERIALIZED VIEW mv_artist_dashboard AS
SELECT
    b.bookable_id,
    b.display_name,
    COUNT(bk.booking_id) AS total_bookings,
    SUM(
        CASE
            WHEN p.payment_status = 'PAID' THEN p.amount
            ELSE 0
        END
    ) AS total_earnings,
    ROUND(AVG(r.rating), 2) AS average_rating
FROM Bookable b
LEFT JOIN Offer o    ON b.bookable_id = o.bookable_id
LEFT JOIN Booking bk ON bk.offer_id = o.offer_id
LEFT JOIN Payment p  ON p.booking_id = bk.booking_id
LEFT JOIN Review r   ON r.booking_id = bk.booking_id
GROUP BY b.bookable_id, b.display_name;

CREATE UNIQUE INDEX idx_mv_dashboard_bookable_id
ON mv_artist_dashboard(bookable_id);

CREATE INDEX idx_mv_dashboard_rating
ON mv_artist_dashboard(average_rating);

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

5.1 - 0.111 ms 

Seq Scan on mv_artist_dashboard  (cost=0.00..15.50 rows=200 width=394) (actual time=0.090..0.090 rows=0 loops=1)
  Filter: (average_rating >= 4.5)
  Rows Removed by Filter: 600
Planning Time: 0.321 ms
Execution Time: 0.111 ms

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

  • од ~31.3 s (со индекси)
  • на ~0.1 ms (со Материјализиран Поглед)

Наместо да ги скенира милионите редици од табелите Offer, Booking, Payment и Review, PostgreSQL сега скенира само 600 редици од зачуваниот резултат. Ова претставува подобрување од околу 281,000 пати во споредба со оригиналниот query со индекси.

За да останат податоците ажурирани, Материјализираниот Поглед се освежува по секоја промена на релевантните табели: 

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_artist_dashboard;

6. Анализа и оптимизација на vw_artist_schedule

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

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

-- 6.1
SELECT *
FROM vw_artist_schedule
WHERE status = 'AVAILABLE';

-- 6.2
SELECT *
FROM vw_artist_schedule
WHERE bookable_id = 150;

-- 6.3
SELECT *
FROM vw_artist_schedule
WHERE start_datetime >= '2026-07-01'
AND start_datetime < '2026-08-01';

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

6.1 - 192.437 ms 

Hash Join  (cost=28.50..17676.52 rows=199740 width=43) (actual time=37.987..185.463 rows=199835 loops=1)
  ->  Seq Scan on availabilityslot av
        Filter: ((status)::text = 'AVAILABLE'::text)
  ->  Seq Scan on bookable b
Planning Time: 0.401 ms
Execution Time: 192.437 ms

6.2 - 51.157 ms 

Nested Loop  (cost=1000.27..13156.88 rows=993 width=43) (actual time=5.494..51.071 rows=1017 loops=1)
  ->  Index Scan using bookable_pkey on bookable b
  ->  Parallel Seq Scan on availabilityslot av
        Filter: (bookable_id = 150)
Planning Time: 0.178 ms
Execution Time: 51.157 ms

6.3 - 96.327 ms 

Hash Join  (cost=28.50..18925.91 rows=104939 width=43) (actual time=5.043..92.669 rows=103896 loops=1)
  ->  Seq Scan on availabilityslot av
        Filter: ((start_datetime >= '2026-07-01 00:00:00'::timestamp without time zone)
              AND (start_datetime < '2026-08-01 00:00:00'::timestamp without time zone))
  ->  Seq Scan on bookable b
Planning Time: 0.345 ms
Execution Time: 96.327 ms

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

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

CREATE INDEX idx_availability_status
ON AvailabilitySlot(status);

CREATE INDEX idx_availability_bookable
ON AvailabilitySlot(bookable_id);

CREATE INDEX idx_availability_start
ON AvailabilitySlot(start_datetime);

CREATE INDEX idx_bookable_id
ON Bookable(bookable_id);

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

6.1 - 109.389 ms 

Hash Join  (cost=2260.91..14905.68 rows=199740 width=43) (actual time=9.156..102.463 rows=199835 loops=1)
  ->  Bitmap Heap Scan on availabilityslot av
        ->  Bitmap Index Scan on idx_availability_status
  ->  Seq Scan on bookable b
Planning Time: 0.846 ms
Execution Time: 109.389 ms

6.2 - 2.111 ms 

Nested Loop  (cost=12.40..2934.21 rows=993 width=43) (actual time=0.327..2.041 rows=1017 loops=1)
  ->  Index Scan using idx_bookable_id on bookable b
  ->  Bitmap Heap Scan on availabilityslot av
        ->  Bitmap Index Scan on idx_availability_bookable
Planning Time: 0.206 ms
Execution Time: 2.111 ms

6.3 - 73.602 ms 

Hash Join  (cost=2260.55..13732.05 rows=104939 width=43) (actual time=11.382..70.012 rows=103896 loops=1)
  ->  Bitmap Heap Scan on availabilityslot av
        ->  Bitmap Index Scan on idx_availability_start
  ->  Seq Scan on bookable b
Planning Time: 0.410 ms
Execution Time: 73.602 ms

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

  • Bitmap Index Scan
  • Bitmap Heap Scan
  • Index Scan

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

  • од ~51 ms
  • на ~2 ms

Кај query-от што пребарува според status = 'AVAILABLE' времето на извршување се намали:

  • од ~192 ms
  • на ~109 ms

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

  • од ~96 ms
  • на ~73 ms

По оптимизацијата PostgreSQL престана да користи Sequential Scan и започна да ги пронаоѓа потребните редици преку индексно пребарување, што значително го подобри времето на извршување и ја намали количината на прочитани податоци.

7. Анализа и оптимизација на vw_artist_reviews

Погледот vw_artist_reviews се користи за прикажување на reviews за артистите и бендовите, нивниот рејтинг и коментари. Овој поглед се користи за пребарување според рејтинг, артист и display name.

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

-- 7.1
SELECT *
FROM vw_artist_reviews
WHERE rating = 5;

-- 7.2
SELECT *
FROM vw_artist_reviews
WHERE bookable_id = 150;

-- 7.3
SELECT *
FROM vw_artist_reviews
WHERE display_name = 'Drake';

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

7.1 - 122018.425 ms 

Gather  (cost=17340.83..132489.41 rows=238310 width=40) (actual time=606.629..121857.202 rows=240417 loops=1)
  ->  Hash Join
        ->  Parallel Hash Join
              ->  Parallel Seq Scan on booking bk
              ->  Parallel Seq Scan on review r
                    Filter: (rating = 5)
        ->  Seq Scan on bookable b
Planning Time: 2.636 ms
Execution Time: 122018.425 ms

7.2 - 681.679 ms 

Nested Loop  (cost=193407.74..209111.43 rows=1 width=40) (actual time=646.913..680.325 rows=0 loops=1)
  ->  Parallel Hash Join
        ->  Parallel Seq Scan on review r
        ->  Parallel Seq Scan on offer o
              Filter: (bookable_id = 150)
Planning Time: 0.795 ms
Execution Time: 681.679 ms

7.3 - 1422.990 ms 

Gather  (cost=197989.27..213886.91 rows=2000 width=40) (actual time=1387.691..1421.437 rows=0 loops=1)
  ->  Parallel Hash Join
        ->  Parallel Seq Scan on offer o
        ->  Seq Scan on bookable b
              Filter: ((display_name)::text = 'Drake'::text)
Planning Time: 0.737 ms
Execution Time: 1422.990 ms

При почетната анализа со EXPLAIN ANALYZE беше забележано дека PostgreSQL користи Sequential Scan и Parallel Sequential Scan врз табелите Review и Offer. Ова предизвикуваше големо време на извршување бидејќи системот обработуваше милиони редици за да ги пронајде потребните reviews.

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

CREATE INDEX idx_review_rating
ON Review(rating);

CREATE INDEX idx_review_booking
ON Review(booking_id);

CREATE INDEX idx_booking_offer
ON Booking(offer_id);

CREATE INDEX idx_offer_bookable
ON Offer(bookable_id);

CREATE INDEX idx_bookable_id
ON Bookable(bookable_id);

CREATE INDEX idx_bookable_display
ON Bookable(display_name);

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

7.1 - 1257.316 ms 

Gather  (cost=14992.67..130141.25 rows=238310 width=40) (actual time=307.260..1241.990 rows=240417 loops=1)
  ->  Hash Join
        ->  Parallel Hash Join
              ->  Parallel Seq Scan on booking bk
              ->  Parallel Bitmap Heap Scan on review r
                    ->  Bitmap Index Scan on idx_review_rating
Planning Time: 1.856 ms
Execution Time: 1257.316 ms

7.2 - 0.144 ms 

Nested Loop  (cost=1.57..25.66 rows=1 width=40) (actual time=0.096..0.097 rows=0 loops=1)
  ->  Index Scan using idx_bookable_id on bookable b
  ->  Index Scan using idx_offer_bookable on offer o
  ->  Index Scan using idx_booking_offer on booking bk
  ->  Index Scan using idx_review_booking on review r
Planning Time: 0.900 ms
Execution Time: 0.144 ms

7.3 - 925.788 ms 

Gather  (cost=1009.16..198914.73 rows=2000 width=40) (actual time=890.434..924.427 rows=0 loops=1)
  ->  Nested Loop
        ->  Hash Join
              ->  Parallel Seq Scan on offer o
              ->  Index Scan using idx_bookable_display on bookable b
Planning Time: 0.866 ms
Execution Time: 925.788 ms

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

  • Bitmap Index Scan
  • Bitmap Heap Scan
  • Index Scan

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

  • од ~122 s
  • на ~1.2 s

Кај query-от што пребарува според bookable_id времето на извршување се намали:

  • од ~681 ms
  • на ~0.14 ms

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

  • од ~1422 ms
  • на ~925 ms

По оптимизацијата PostgreSQL престана да користи Sequential Scan за дел од пребарувањата и започна да користи индексно пребарување, што значително го намали времето на извршување и бројот на обработени редици.

8. Анализа и оптимизација на vw_artist_incoming_requests

Погледот vw_artist_incoming_requests се користи за прикажување на incoming requests за артистите и бендовите, вклучувајќи тип на настан, датум, град и понудена цена. Овој поглед се користи во artist dashboard делот од апликацијата.

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

-- 8.1
SELECT *
FROM vw_artist_incoming_requests
WHERE city = 'Skopje';

-- 8.2
SELECT *
FROM vw_artist_incoming_requests
WHERE event_date >= '2026-07-01'
AND event_date < '2026-08-01';

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

8.1 - 969.055 ms 

Gather  (cost=204886.61..536618.31 rows=800000 width=254) (actual time=957.362..966.909 rows=0 loops=1)
  ->  Hash Join
        ->  Parallel Hash Join
              ->  Parallel Seq Scan on bookingrequest br
              ->  Seq Scan on location l
                    Filter: ((city)::text = 'Skopje'::text)
        ->  Parallel Seq Scan on offer o
Planning Time: 0.612 ms
Execution Time: 969.055 ms

8.2 - 8618.837 ms 

Gather  (cost=209088.80..622293.48 rows=1677259 width=254) (actual time=7086.289..8549.231 rows=1701412 loops=1)
  ->  Hash Left Join
        ->  Parallel Hash Join
              ->  Parallel Seq Scan on offer o
              ->  Parallel Seq Scan on bookingrequest br
                    Filter: ((event_date >= '2026-07-01'::date)
                          AND (event_date < '2026-08-01'::date))
Planning Time: 0.637 ms
Execution Time: 8618.837 ms

При почетната анализа со EXPLAIN ANALYZE беше забележано дека PostgreSQL користи Sequential Scan и Parallel Sequential Scan врз табелите Offer и BookingRequest. Ова предизвикуваше дополнително време на извршување бидејќи системот обработуваше милиони редици за да ги пронајде потребните requests.

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

CREATE INDEX idx_offer_bookable
ON Offer(bookable_id);

CREATE INDEX idx_offer_request
ON Offer(request_id);

CREATE INDEX idx_bookingrequest_location
ON BookingRequest(location_id);

CREATE INDEX idx_bookingrequest_eventdate
ON BookingRequest(event_date);

CREATE INDEX idx_location_city
ON Location(city);

CREATE INDEX idx_bookable_id
ON Bookable(bookable_id);

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

8.1 - 617.537 ms 

Gather  (cost=1030.26..345626.04 rows=800000 width=254) (actual time=606.861..615.460 rows=0 loops=1)
  ->  Hash Join
        ->  Nested Loop
              ->  Hash Join
                    ->  Parallel Seq Scan on bookingrequest br
                    ->  Seq Scan on location l
              ->  Index Scan using idx_offer_request on offer o
Planning Time: 0.850 ms
Execution Time: 617.537 ms

8.2 - 3438.192 ms 

Gather  (cost=209088.80..622293.48 rows=1677259 width=254) (actual time=2096.594..3369.469 rows=1701412 loops=1)
  ->  Hash Left Join
        ->  Parallel Hash Join
              ->  Parallel Seq Scan on offer o
              ->  Parallel Seq Scan on bookingrequest br
                    Filter: ((event_date >= '2026-07-01'::date)
                          AND (event_date < '2026-08-01'::date))
Planning Time: 0.865 ms
Execution Time: 3438.192 ms

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

  • Index Scan
  • Nested Loop
  • Bitmap Heap Scan

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

  • од ~8.6 s
  • на ~3.4 s

Кај query-от што пребарува според град city = 'Skopje' времето на извршување се подобри:

  • од ~969 ms
  • на ~617 ms

По оптимизацијата PostgreSQL започна да користи индексно пребарување наместо Sequential Scan за дел од join операциите, што значително го намали времето на извршување и бројот на обработени редици.

9. Анализа и оптимизација на vw_pending_payments

Погледот vw_pending_payments се користи за прикажување на pending и unpaid payments поврзани со bookings, артистите и настаните. Овој поглед се користи за financial tracking, artist dashboard и admin payment management.

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

SELECT *
FROM vw_pending_payments
WHERE payment_status = 'PENDING';

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

9.1 - 12555.538 ms 

Gather  (cost=579119.54..1056337.52 rows=4001410 width=258) (actual time=9089.762..12399.923 rows=4001410 loops=1)
  ->  Hash Left Join
        ->  Hash Join
              ->  Parallel Hash Join
                    ->  Parallel Seq Scan on payment p
                          Filter: ((payment_status)::text = 'PENDING'::text)
                    ->  Parallel Seq Scan on booking bk
                    ->  Parallel Seq Scan on offer o
                    ->  Parallel Seq Scan on bookingrequest br
Planning Time: 2.158 ms
Execution Time: 12555.538 ms

При почетната анализа со EXPLAIN ANALYZE беше забележано дека PostgreSQL користи Sequential Scan и Parallel Sequential Scan врз табелите Payment, BookingRequest, Offer и Booking. Ова предизвикуваше долго време на извршување бидејќи системот обработуваше милиони редици за да ги пронајде потребните payments и requests.

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

CREATE INDEX idx_payment_status
ON Payment(payment_status);

CREATE INDEX idx_payment_booking
ON Payment(booking_id);

CREATE INDEX idx_booking_offer
ON Booking(offer_id);

CREATE INDEX idx_offer_bookable
ON Offer(bookable_id);

CREATE INDEX idx_offer_request
ON Offer(request_id);

CREATE INDEX idx_bookingrequest_eventdate
ON BookingRequest(event_date);

CREATE INDEX idx_bookingrequest_location
ON BookingRequest(location_id);

CREATE INDEX idx_location_city
ON Location(city);

CREATE INDEX idx_bookable_id
ON Bookable(bookable_id);

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

9.1 - 10043.475 ms 

Gather  (cost=579119.54..1056337.52 rows=4001410 width=258) (actual time=7499.460..9892.291 rows=4001410 loops=1)
  ->  Hash Left Join
        ->  Hash Join
              ->  Parallel Hash Join
                    ->  Parallel Seq Scan on payment p
                          Filter: ((payment_status)::text = 'PENDING'::text)
                    ->  Parallel Seq Scan on booking bk
                    ->  Parallel Seq Scan on offer o
                    ->  Parallel Seq Scan on bookingrequest br
Planning Time: 3.268 ms
Execution Time: 10043.475 ms

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

  • од ~12.5 s
  • на ~10.0 s

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

Во вакви ситуации planner-от проценува дека sequential processing е поефикасен за обработка на огромен број редици, па индексите имаат ограничено влијание врз aggregation и join операциите. Поради ова, беше одлучено да се примени дополнителна оптимизација преку Материјализиран Поглед.

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

Бидејќи индексите не можеа значително да го подобрат времето на извршување поради огромниот број редици кои се враќаат (4 милиони), беше креиран Материјализиран Поглед (MATERIALIZED VIEW) кој ги зачувува резултатите физички на диск и овозможува побрзо пребарување без да се поминува низ сите join операции секој пат. 

CREATE MATERIALIZED VIEW mv_pending_payments AS
SELECT
    p.payment_id,
    bk.booking_id,
    b.bookable_id,
    b.display_name,
    p.amount,
    p.payment_status,
    br.event_date,
    l.city
FROM Payment p
JOIN Booking bk        ON p.booking_id = bk.booking_id
JOIN Offer o           ON bk.offer_id = o.offer_id
JOIN Bookable b        ON o.bookable_id = b.bookable_id
JOIN BookingRequest br ON o.request_id = br.request_id
LEFT JOIN Location l   ON br.location_id = l.location_id;

CREATE UNIQUE INDEX idx_mv_payments_payment_id
ON mv_pending_payments(payment_id);

CREATE INDEX idx_mv_payments_status
ON mv_pending_payments(payment_status);

CREATE INDEX idx_mv_payments_city
ON mv_pending_payments(city);

CREATE INDEX idx_mv_payments_event_date
ON mv_pending_payments(event_date);

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

9.1 - 866.538 ms 

Seq Scan on mv_pending_payments  (cost=0.00..89763.15 rows=4001532 width=49) (actual time=0.059..731.011 rows=4001410 loops=1)
  Filter: ((payment_status)::text = 'PENDING'::text)
Planning Time: 0.468 ms
Execution Time: 866.538 ms

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

  • од ~10.0 s (со индекси)
  • на ~866 ms (со Материјализиран Поглед)

Подобрувањето е присутно, меѓутоа PostgreSQL сè уште користи Seq Scan врз Материјализираниот Поглед бидејќи query-от враќа речиси сите редици (4 милиони од вкупно 4 милиони). Во ваква ситуација index scan е поскап од sequential scan бидејќи нема селективност — скоро секоја редица го задоволува филтерот payment_status = 'PENDING'.

Главното подобрување доаѓа од тоа што сложените JOIN операции меѓу Payment, Booking, Offer, Bookable и BookingRequest се пресметани однапред и зачувани, па PostgreSQL скенира само еден објект наместо пет табели.

За да останат податоците ажурирани, Материјализираниот Поглед се освежува по секоја промена на статусот на плаќање: 

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_pending_payments;
Note: See TracWiki for help on using the wiki.