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

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

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

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

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

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

-- 1.1
SELECT *
FROM vw_available_bookables
WHERE city = 'Skopje'
AND status = 'AVAILABLE';

-- 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'
AND status = 'AVAILABLE';

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

1.1 - 55.127 ms

Gather  (cost=1037.34..13879.22 rows=5448 width=481) (actual time=8.888..52.020 rows=71472 loops=1)
  Workers Planned: 3
  Workers Launched: 3
  ->  Hash Join  (cost=37.34..12334.42 rows=1757 width=481) (actual time=2.712..34.856 rows=17868 loops=4)
        Hash Cond: (av.bookable_id = b.bookable_id)
        ->  Parallel Seq Scan on availabilityslot av
              Filter: ((status)::text = 'AVAILABLE'::text)
Planning Time: 0.836 ms
Execution Time: 55.127 ms

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)
  ->  Hash Join  (cost=42.46..12329.88 rows=604 width=481)
        ->  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)
  ->  Hash Join  (cost=37.17..12319.22 rows=5 width=481)
        ->  Parallel Seq Scan on availabilityslot av
              Filter: ((status)::text = 'AVAILABLE'::text)
Planning Time: 1.040 ms
Execution Time: 13.899 ms

При почетната анализа беше забележано дека 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);

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

1.1 - 77.491 ms

Hash Left Join  (cost=58.20..2541.02 rows=23970 width=481) (actual time=0.959..74.797 rows=71472 loops=1)
  ->  Nested Loop
        ->  Index Scan using idx_availability_bookable on availabilityslot av
Planning Time: 1.861 ms
Execution Time: 77.491 ms

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

Првиот query и понатаму имаше поголемо време на извршување бидејќи враќа многу голем број резултати (~71,000 редици), но planner-от започна да користи индексно пребарување наместо Parallel Sequential Scan.

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
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_offer_bookable
ON Offer(bookable_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