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

Во оваа фаза беа анализирани и оптимизирани погледите дефинирани во системот за 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.