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

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

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

За мерење на времињата се користи EXPLAIN ANALYZE. Кај секој поглед прво се мери времето без индекс, потоа се креира индексот и повторно се мери времето со индекс. Како резултат во документацијата се зема вредноста Execution Time.

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

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

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

SELECT *
FROM customer_appointments_view
WHERE customer_id = 100;

За оптимизација го тестираме истиот прашалник пред и после додавање на индексот.

-- 1. Мерење без индекс
EXPLAIN ANALYZE
SELECT *
FROM customer_appointments_view
WHERE customer_id = 100;

-- 2. Креирање на индекс
CREATE INDEX IF NOT EXISTS idx_appointment_customer_not_cancelled
ON appointment(customer_id)
WHERE status <> 'cancelled';

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

7.802 ms

Дел од добиениот EXPLAIN ANALYZE output пред додавање на индексот:

Nested Loop  (cost=88.12..168.91 rows=5 width=151) (actual time=3.988..7.635 rows=6 loops=1)
->  Hash Join  (cost=87.68..126.58 rows=5 width=97) (actual time=3.625..4.537 rows=6 loops=1)
Hash Cond: (a.service_id = s.service_id)
->  Index Scan using idx_appointment_customer_profile on appointment a
Index Cond: (customer_id = 100)
Filter: ((status)::text <> 'cancelled'::text)
Rows Removed by Filter: 1

Planning Time: 8.862 ms
Execution Time: 7.802 ms

Во овој план се гледа дека за табелата appointment се користи индекс според customer_id, но сепак дополнително се применува филтерот:

Filter: ((status)::text <> 'cancelled'::text)

Ова значи дека базата ги наоѓа резервациите според customer_id, но потоа мора дополнително да ги филтрира оние кои имаат статус различен од cancelled.

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

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

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

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

0.803 ms

Дел од добиениот EXPLAIN ANALYZE output после додавање на индексот:

Nested Loop  (cost=87.54..168.32 rows=5 width=151) (actual time=0.576..0.714 rows=6 loops=1)
->  Index Scan using idx_appointment_customer_not_cancelled on appointment a
Index Cond: (customer_id = 100)

Planning Time: 3.150 ms
Execution Time: 0.803 ms

После додавање на индексот се гледа дека планерот го користи новиот индекс:

Index Scan using idx_appointment_customer_not_cancelled on appointment a
Index Cond: (customer_id = 100)

Овој пат нема дополнителен филтер за status <> 'cancelled', бидејќи самиот индекс веќе ги содржи само редиците кои не се откажани.

Споредба на времиња

Поглед	Пред индекс	После индекс	Подобрување
customer_appointments_view	7.802 ms	0.803 ms	околу 9.7 пати побрзо

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

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

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

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

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

За оптимизација го тестираме истиот прашалник пред и после додавање на индексот.

-- 1. Мерење без индекс
EXPLAIN ANALYZE
SELECT *
FROM available_slots
WHERE business_id = 1
AND date = CURRENT_DATE;

-- 2. Креирање на индекс
CREATE INDEX IF NOT EXISTS idx_time_slot_available_business_date
ON time_slot(business_id, date)
WHERE is_available = TRUE;

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

7157.628 ms

Дел од добиениот EXPLAIN ANALYZE output пред додавање на индексот:

Nested Loop  (cost=1001.27..479269.43 rows=21 width=100) (actual time=7156.545..7157.282 rows=48 loops=1)
->  Gather
Workers Planned: 2
Workers Launched: 2
->  Parallel Seq Scan on time_slot ts
Filter: (is_available AND (business_id = 1) AND (date = CURRENT_DATE))
Rows Removed by Filter: 6666651

Planning Time: 4.741 ms
Execution Time: 7157.628 ms

Во овој план се гледа дека најбавниот дел е скенирањето на табелата time_slot:

Parallel Seq Scan on time_slot ts
Filter: (is_available AND (business_id = 1) AND (date = CURRENT_DATE))
Rows Removed by Filter: 6666651

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

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

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

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

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

0.580 ms

Дел од добиениот EXPLAIN ANALYZE output после додавање на индексот:

Nested Loop  (cost=69.25..256.19 rows=35 width=100) (actual time=0.364..0.485 rows=48 loops=1)
->  Index Scan using idx_time_slot_available_business_date on time_slot ts
Index Cond: ((business_id = 1) AND (date = CURRENT_DATE))

Planning Time: 2.741 ms
Execution Time: 0.580 ms

После додавање на индексот, планерот го користи новиот индекс:

Index Scan using idx_time_slot_available_business_date on time_slot ts
Index Cond: ((business_id = 1) AND (date = CURRENT_DATE))

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

Споредба на времиња

Поглед	Пред индекс	После индекс	Подобрување
available_slots	7157.628 ms	0.580 ms	над 12000 пати побрзо

Со додавање на парцијалниот индекс idx_time_slot_available_business_date, времето на извршување се намали од 7157.628 ms на 0.580 ms. Ова е огромно подобрување, бидејќи прашалникот повеќе не прави Parallel Seq Scan врз табелата time_slot, туку користи директен Index Scan преку новиот индекс.

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

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

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

SELECT *
FROM review_details
WHERE rating = 5;

За оптимизација го тестираме истиот прашалник пред и после додавање на индексот.

-- 1. Мерење без индекс
EXPLAIN ANALYZE
SELECT *
FROM review_details
WHERE rating = 5;

-- 2. Креирање на индекс
CREATE INDEX IF NOT EXISTS idx_review_rating_details
ON review(rating);

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

360.970 ms

Дел од добиениот EXPLAIN ANALYZE output пред додавање на индексот:

Hash Join  (cost=39.52..111924.47 rows=1006495 width=35) (actual time=0.710..344.505 rows=1000001 loops=1)
->  Seq Scan on review r  (cost=0.00..109231.71 rows=1006495 width=27) (actual time=0.169..259.457 rows=1000001 loops=1)
Filter: (rating = 5)
Rows Removed by Filter: 4000000

Planning Time: 2.849 ms
Execution Time: 360.970 ms

Во овој план се гледа дека табелата review се скенира секвенцијално:

Seq Scan on review r
Filter: (rating = 5)
Rows Removed by Filter: 4000000

Ова значи дека базата ја чита целата табела review, потоа ги задржува само рецензиите со rating = 5, а ги отфрла останатите. Во конкретниот случај се отстранети 4 000 000 редици, што покажува дека без индекс се чита многу поголем број податоци од потребното.

За да го оптимизираме ова, креираме индекс на колоната rating:

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

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

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

239.802 ms

Дел од добиениот EXPLAIN ANALYZE output после додавање на индексот:

Hash Join  (cost=11246.93..73210.68 rows=1006319 width=35) (actual time=40.929..223.700 rows=1000001 loops=1)
->  Bitmap Heap Scan on review r
Recheck Cond: (rating = 5)
->  Bitmap Index Scan on idx_review_rating_details
Index Cond: (rating = 5)

Planning Time: 0.593 ms
Execution Time: 239.802 ms

После додавање на индексот се гледа дека планерот веќе не прави Seq Scan врз review, туку користи:

Bitmap Index Scan on idx_review_rating_details
Index Cond: (rating = 5)

и потоа:

Bitmap Heap Scan on review r
Recheck Cond: (rating = 5)

Ова значи дека базата прво преку индексот ги наоѓа позициите на редиците кои имаат rating = 5, а потоа ги чита тие редици од табелата.

Подобрувањето кај овој поглед е помало во споредба со available_slots, бидејќи rating = 5 се појавува кај многу голем број редици. Во конкретниот резултат има 1 000 001 рецензија со оценка 5. Кога условот враќа голем дел од табелата, индексот помага, но сепак мора да се прочитаат многу редици.

Споредба на времиња

Поглед	Пред индекс	После индекс	Подобрување
review_details	360.970 ms	239.802 ms	околу 1.5 пати побрзо

Со додавање на индексот idx_review_rating_details, времето на извршување се намали од 360.970 ms на 239.802 ms. Индексот е корисен затоа што го заменува секвенцијалното скенирање на целата табела со Bitmap Index Scan, но подобрувањето не е екстремно бидејќи условот rating = 5 враќа голем број редици.

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

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

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

SELECT *
FROM customer_profile_view
WHERE customer_id = 100;

За оптимизација го тестираме истиот прашалник пред и после додавање на индексот.

-- 1. Мерење без индекс
EXPLAIN ANALYZE
SELECT *
FROM customer_profile_view
WHERE customer_id = 100;

-- 2. Креирање на индекс
CREATE INDEX IF NOT EXISTS idx_review_customer_profile
ON review(customer_id, rating);

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

546.242 ms

Дел од добиениот EXPLAIN ANALYZE output пред додавање на индексот:

GroupAggregate  (cost=147561.37..147562.12 rows=25 width=117) (actual time=545.746..546.153 rows=1 loops=1)
->  Parallel Seq Scan on appointment a
Filter: (customer_id = 100)
Rows Removed by Filter: 1666665
->  Parallel Seq Scan on review r
Filter: (customer_id = 100)
Rows Removed by Filter: 1666665

Planning Time: 7.447 ms
Execution Time: 546.242 ms

Во овој план се гледа дека најголемиот проблем е што табелите appointment и review се скенираат секвенцијално. За appointment се користи:

Parallel Seq Scan on appointment a
Filter: (customer_id = 100)
Rows Removed by Filter: 1666665

а за review се користи:

Parallel Seq Scan on review r
Filter: (customer_id = 100)
Rows Removed by Filter: 1666665

Ова значи дека базата чита многу голем број редици за да ги најде записите кои припаѓаат на корисникот со customer_id = 100.

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

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

Овој индекс е корисен бидејќи погледот customer_profile_view ги користи рецензиите на конкретен корисник, а дополнително може да ја користи и колоната rating при пресметки или приказ на информации поврзани со оценките.

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

123.200 ms

Дел од добиениот EXPLAIN ANALYZE output после додавање на индексот:

GroupAggregate  (cost=73811.09..73811.84 rows=25 width=117) (actual time=122.594..123.137 rows=1 loops=1)
->  Parallel Seq Scan on appointment a
Filter: (customer_id = 100)
Rows Removed by Filter: 1666665
->  Index Scan using idx_review_customer_profile on review r
Index Cond: (customer_id = 100)

Planning Time: 6.138 ms
Execution Time: 123.200 ms

После додавање на индексот се гледа дека табелата review повеќе не се скенира секвенцијално, туку се користи новиот индекс:

Index Scan using idx_review_customer_profile on review r
Index Cond: (customer_id = 100)

Ова значи дека базата директно ги наоѓа рецензиите за конкретниот корисник, наместо да ја чита целата табела review.

Сепак, во планот и понатаму се гледа дека табелата appointment се скенира секвенцијално:

Parallel Seq Scan on appointment a
Filter: (customer_id = 100)
Rows Removed by Filter: 1666665

Затоа времето се подобрува значително, но не паѓа под 1 ms како кај некои други погледи.

Споредба на времиња

Поглед	Пред индекс	После индекс	Подобрување
customer_profile_view	546.242 ms	123.200 ms	околу 4.4 пати побрзо

Со додавање на индексот idx_review_customer_profile, времето на извршување се намали од 546.242 ms на 123.200 ms. Индексот е корисен бидејќи го заменува секвенцијалното скенирање на табелата review со директно индексно пребарување според customer_id.

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

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

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

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

SELECT *
FROM reschedule_overview
WHERE status = 'pending';

За оптимизација го тестираме истиот прашалник пред и после додавање на индексите.

-- 1. Мерење без индекси
EXPLAIN ANALYZE
SELECT *
FROM reschedule_overview
WHERE status = 'pending';

-- 2. Креирање на индексите
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);

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

5.051 ms

Дел од добиениот EXPLAIN ANALYZE output пред додавање на индексите:

Merge Join  (cost=124.66..141.39 rows=667 width=64) (actual time=4.200..5.002 rows=667 loops=1)
->  Seq Scan on reschedule_request rr
Filter: ((status)::text = 'pending'::text)
Rows Removed by Filter: 1335

Planning Time: 1.921 ms
Execution Time: 5.051 ms

Во овој план се гледа дека табелата reschedule_request се скенира секвенцијално:

Seq Scan on reschedule_request rr
Filter: ((status)::text = 'pending'::text)
Rows Removed by Filter: 1335

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

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

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);

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

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

3.988 ms

Дел од добиениот EXPLAIN ANALYZE output после додавање на индексите:

Merge Join  (cost=121.42..138.15 rows=667 width=64) (actual time=3.341..3.903 rows=667 loops=1)
->  Bitmap Heap Scan on reschedule_request rr
Recheck Cond: ((status)::text = 'pending'::text)
->  Bitmap Index Scan on idx_reschedule_request_status
Index Cond: ((status)::text = 'pending'::text)

Planning Time: 0.904 ms
Execution Time: 3.988 ms

После додавање на индексите се гледа дека планерот повеќе не користи Seq Scan за reschedule_request, туку користи:

Bitmap Index Scan on idx_reschedule_request_status
Index Cond: ((status)::text = 'pending'::text)

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

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

Споредба на времиња

Поглед	Пред индекси	После индекси	Подобрување
reschedule_overview	5.051 ms	3.988 ms	околу 1.27 пати побрзо

Со додавање на индексите, времето на извршување се намали од 5.051 ms на 3.988 ms. Најважниот индекс во овој случај е idx_reschedule_request_status, бидејќи директно се користи за условот status = 'pending'.

Индексите на old_slot_id и new_slot_id се потенцијално корисни за спојувања со табелата time_slot, иако во конкретниот план најмногу се гледа користењето на индексот за status.

6. Заклучок

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

Поглед	Пред индекс	После индекс	Подобрување
customer_appointments_view	7.802 ms	0.803 ms	околу 9.7 пати побрзо
available_slots	7157.628 ms	0.580 ms	над 12000 пати побрзо
review_details	360.970 ms	239.802 ms	околу 1.5 пати побрзо
customer_profile_view	546.242 ms	123.200 ms	околу 4.4 пати побрзо
reschedule_overview	5.051 ms	3.988 ms	околу 1.27 пати побрзо

Најголемо подобрување има кај available_slots, бидејќи пред додавање на индексот базата правеше Parallel Seq Scan врз табелата time_slot и отстрануваше над 6 милиони редици преку филтер. После додавање на парцијалниот индекс, базата директно ги наоѓа достапните термини за конкретен бизнис и датум.

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

Кај customer_profile_view подобрувањето е значително, бидејќи индексот idx_review_customer_profile го заменува секвенцијалното скенирање на табелата review со директно индексно пребарување според customer_id. Иако табелата appointment и понатаму се скенира секвенцијално, вкупното време се намалува од 546.242 ms на 123.200 ms.

Кај review_details подобрувањето е помало, бидејќи условот rating = 5 враќа многу голем број редици. Индексот го заменува целосното секвенцијално скенирање со Bitmap Index Scan, но бидејќи сепак мора да се прочитаат околу 1 милион рецензии, разликата не е толку голема.

Кај reschedule_overview подобрувањето е најмало, бидејќи табелата reschedule_request во тестот има релативно мал број редици. Индексот idx_reschedule_request_status се користи во планот, но разликата меѓу времето пред и после индексот е мала.

7. Одлука кои индекси се задржуваат, а кои се бришат

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

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

-- Индекси кои ги задржуваме

idx_appointment_customer_not_cancelled
idx_time_slot_available_business_date
idx_review_customer_profile

Овие индекси се оправдани затоа што носат значајно подобрување:

Индекс	Поглед	Причина за задржување
idx_appointment_customer_not_cancelled	customer_appointments_view	времето се намалува од 7.802 ms на 0.803 ms
idx_time_slot_available_business_date	available_slots	времето се намалува од 7157.628 ms на 0.580 ms
idx_review_customer_profile	customer_profile_view	времето се намалува од 546.242 ms на 123.200 ms

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

-- Индекси кои ги бришеме

DROP INDEX IF EXISTS idx_review_rating_details;

DROP INDEX IF EXISTS idx_reschedule_request_status;
DROP INDEX IF EXISTS idx_reschedule_request_old_slot_id;
DROP INDEX IF EXISTS idx_reschedule_request_new_slot_id;

Индексот idx_review_rating_details го намалува времето од 360.970 ms на 239.802 ms, што е подобрување, но не е доволно големо во споредба со бројот на редици кои сепак мора да се прочитаат. Бидејќи rating = 5 враќа околу 1 милион редици, индексот има ограничен ефект.

Индексот idx_reschedule_request_status го намалува времето од 5.051 ms на 3.988 ms, што е многу мала разлика. Дополнително, индексите idx_reschedule_request_old_slot_id и idx_reschedule_request_new_slot_id не се директно искористени во прикажаниот план на извршување. Затоа тие индекси ги бришеме за да не создаваат непотребен трошок при внесување и ажурирање на податоци.

Заклучуваме дека најоправдани индекси за задржување во оваа фаза се парцијалните индекси за customer_appointments_view и available_slots, како и индексот за customer_profile_view, бидејќи таму добивката е најголема и сценаријата се чести во реалната употреба на системот.