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

Во оваа фаза ќе ги анализираме погледите дефинирани во претходните фази преку прашалници базирани на реални сценарија кои ќе бидат присутни во апликацијата Rezervo. Целта е да се провери времето на извршување на прашалниците пред и после додавање на соодветни индекси, како и да се објасни зошто тие индекси ги подобруваат перформансите.

Rezervo е систем за резервации кај занаетчии и даватели на услуги, каде што корисниците можат да пребаруваат достапни термини, да закажуваат, менуваат или откажуваат резервации, како и да оставаат рецензии за услугите.

1. Анализа на поглед customer_appointments_view, преглед на резервации за конкретен корисник

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

Прашалникот кој го тестираме е:

EXPLAIN ANALYZE
SELECT *
FROM customer_appointments_view
WHERE customer_id = 100;

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

644.544 ms

Бидејќи прашалникот филтрира според customer_id, а во погледот не се потребни откажаните термини, креираме парцијален индекс врз табелата appointment. Со овој индекс се индексираат само редиците чиј статус не е cancelled, со што индексот е помал и поефикасен.

CREATE INDEX IF NOT EXISTS idx_appointment_customer_not_cancelled
ON appointment(customer_id)
WHERE status <> 'cancelled';

ANALYZE appointment;

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

2.756 ms

Со додавање на индексот, времето на извршување се намали од 644.544 ms на 2.756 ms. Ова е значително подобрување бидејќи планерот повеќе нема потреба да ја скенира целата табела appointment, туку директно ги наоѓа редиците за конкретниот корисник.

2. Анализа на поглед available_slots, пребарување на слободни термини за даден бизнис и датум

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

Прашалникот кој го тестираме е:

EXPLAIN ANALYZE
SELECT *
FROM available_slots
WHERE business_id = 1
AND date = CURRENT_DATE;

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

429.479 ms

Погледот пребарува според business_id, date и дополнително ги зема само термините кои се достапни. Затоа креираме парцијален индекс кој ги покрива само термините каде што is_available = TRUE.

CREATE INDEX IF NOT EXISTS idx_time_slot_available_business_date
ON time_slot(business_id, date)
WHERE is_available = TRUE;

ANALYZE time_slot;

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

1.090 ms

Со овој индекс времето се намали од 429.479 ms на 1.090 ms. Индексот е многу корисен бидејќи прашалникот најчесто бара само достапни термини, па нема потреба да се пребаруваат сите термини во табелата time_slot.

3. Анализа на поглед review_summary, краток преглед на рецензии за бизнис

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

Прашалникот кој го тестираме е:

EXPLAIN ANALYZE
SELECT *
FROM review_summary
WHERE business_id = 1;

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

627.643 ms

Бидејќи прашалникот филтрира според business_id, а во погледот се користи и rating, креираме сложен индекс врз двете колони.

CREATE INDEX IF NOT EXISTS idx_review_business_rating_summary
ON review(business_id, rating);

ANALYZE review;

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

29.151 ms

Со додавање на индексот времето се намали од 627.643 ms на 29.151 ms. Индексот овозможува побрзо групирање и филтрирање на рецензиите за конкретен бизнис, бидејќи податоците се организирани според business_id и rating.

4. Анализа на поглед review_details, детални рецензии според оценка

Овој поглед се користи кога сакаме да ги прикажеме рецензиите кои имаат одредена оценка, на пример сите рецензии со оценка 5. Ова може да се користи за филтрирање на рецензии во корисничкиот интерфејс.

Прашалникот кој го тестираме е:

EXPLAIN ANALYZE
SELECT *
FROM review_details
WHERE rating = 5;

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

800.553 ms

Бидејќи прашалникот филтрира според колоната rating, креираме индекс на оваа колона.

CREATE INDEX IF NOT EXISTS idx_review_rating_details
ON review(rating);

ANALYZE review;

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

340.962 ms

Со додавање на индексот времето се намали од 800.553 ms на 340.962 ms. Подобрувањето е забележливо, но не е толку големо како кај претходните прашалници, бидејќи вредноста rating = 5 може да се појавува кај голем број редици. Во таков случај индексот помага, но сепак мора да се прочита голем дел од табелата.

5. Анализа на поглед customer_profile_view, профил на корисник со негови активности

Овој поглед се користи за прикажување на профил на корисник, каде што можат да се прикажат неговите резервации, рецензии и други информации поврзани со активностите во системот.

Прашалникот кој го тестираме е:

EXPLAIN ANALYZE
SELECT *
FROM customer_profile_view
WHERE customer_id = 100;

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

708.985 ms

За оптимизација на овој поглед креираме индекс врз табелата review, бидејќи погледот користи информации за рецензии поврзани со конкретен корисник.

CREATE INDEX IF NOT EXISTS idx_review_customer_profile
ON review(customer_id, rating);

ANALYZE appointment;
ANALYZE review;

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

0.767 ms

Со додавање на индексот времето се намали од 708.985 ms на 0.767 ms. Ова е многу големо подобрување, бидејќи податоците за конкретниот корисник се наоѓаат директно преку индексот, наместо да се пребарува целата табела review.

Во кодот беше разгледан и индексот:

-- CREATE INDEX IF NOT EXISTS idx_appointment_customer_profile
--     ON appointment(customer_id, created_at);

Овој индекс може да биде корисен доколку во погледот или во прашалниците често се користи сортирање или филтрирање според created_at. Во конкретниот тест, главното подобрување е постигнато со индексот врз review(customer_id, rating).

6. Анализа на поглед reschedule_overview, преглед на барања за промена на термин

Овој поглед се користи за прикажување на барања за презакажување на термини. Во апликацијата ова би се користело од страна на бизнисот или вработениот за да ги види барањата кои се во статус pending.

Прашалникот кој го тестираме е:

EXPLAIN ANALYZE
SELECT *
FROM reschedule_overview
WHERE status = 'pending';

За оптимизација креираме индекси врз колоните кои најчесто се користат во филтрирање и спојување на табелата reschedule_request.

CREATE INDEX IF NOT EXISTS idx_reschedule_request_status
ON reschedule_request(status);

CREATE INDEX IF NOT EXISTS idx_reschedule_request_old_slot_id
ON reschedule_request(old_slot_id);

CREATE INDEX IF NOT EXISTS idx_reschedule_request_new_slot_id
ON reschedule_request(new_slot_id);

ANALYZE reschedule_request;

Потоа повторно го извршуваме прашалникот:

EXPLAIN ANALYZE
SELECT *
FROM reschedule_overview
WHERE status = 'pending';

Индексот idx_reschedule_request_status помага при филтрирање на барањата според статус, особено за чести прашалници како status = 'pending'.

Индексите idx_reschedule_request_old_slot_id и idx_reschedule_request_new_slot_id помагаат при спојување со табелата за термини, бидејќи барањата за презакажување најчесто ги поврзуваат стариот и новиот термин преку овие колони.

Време на извршување:

Во овој дел треба да се внесат резултатите од EXPLAIN ANALYZE пред и после додавање на индексите.

-- Пред оптимизација:   да се внесе измереното време
-- После оптимизација:  да се внесе измереното време

7. Влијание на индексите врз insert/update операции

Иако индексите значително го подобруваат читањето на податоците, тие можат да имаат мало влијание врз операциите за внесување и ажурирање. Причината е тоа што при секој INSERT, UPDATE или DELETE, базата мора да ги ажурира и индексите, не само главната табела.

За тестирање на влијанието може да се користат следните прашалници:

EXPLAIN ANALYZE
INSERT INTO review (customer_id, business_id, rating, comment, created_at)
VALUES (100, 1, 5, 'Benchmark review', CURRENT_TIMESTAMP);

EXPLAIN ANALYZE
UPDATE review
SET rating = 4
WHERE customer_id = 100;

EXPLAIN ANALYZE
INSERT INTO time_slot (business_id, date, start_time, end_time, is_available)
VALUES (1, CURRENT_DATE, TIME '10:00', TIME '10:30', TRUE);

EXPLAIN ANALYZE
UPDATE time_slot
SET is_available = FALSE
WHERE business_id = 1
AND date = CURRENT_DATE;

EXPLAIN ANALYZE
UPDATE reschedule_request
SET status = 'approved'
WHERE status = 'pending';

Очекувано е индексите да внесат мал трошок при запишување, но бидејќи главните сценарија во апликацијата се пребарување на слободни термини, преглед на резервации и читање на рецензии, добивката кај SELECT прашалниците е многу поголема од потенцијалниот трошок кај INSERT и UPDATE.

8. Заклучок

Со додавање на соодветни индекси успеавме значително да ги подобриме перформансите на најважните прашалници во системот Rezervo.

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

Поглед	Пред оптимизација	После оптимизација
customer_appointments_view	644.544 ms	2.756 ms
available_slots	429.479 ms	1.090 ms
review_summary	627.643 ms	29.151 ms
review_details	800.553 ms	340.962 ms
customer_profile_view	708.985 ms	0.767 ms
reschedule_overview	да се внесе	да се внесе

Најголемо забрзување има кај customer_profile_view, каде што времето се намали од 708.985 ms на 0.767 ms, како и кај available_slots, каде што пребарувањето на достапни термини се намали од 429.479 ms на 1.090 ms.

Заклучуваме дека индексите се оправдани бидејќи ги оптимизираат најчестите кориснички сценарија во апликацијата:

• преглед на резервации за корисник,
• пребарување на достапни термини,
• преглед и филтрирање на рецензии,
• приказ на кориснички профил,
• обработка на барања за презакажување.

Иако индексите можат да имаат мал трошок при внесување и ажурирање на податоци, во овој систем читањето и пребарувањето се многу почести операции, па добивката во перформанси е значително поголема.