| Version 8 (modified by , 22 hours ago) ( diff ) |
|---|
Оптимизација на прашалници и погледи
Во оваа фаза ќе ги анализираме погледите дефинирани во претходните фази преку прашалници базирани на реални сценарија кои ќе бидат присутни во апликацијата Rezervo. Целта е да се провери времето на извршување на прашалниците пред и после додавање на соодветни индекси, како и да се објасни зошто тие индекси ги подобруваат перформансите.
Rezervo е систем за резервации кај занаетчии и даватели на услуги, каде што корисниците можат да пребаруваат достапни термини, да закажуваат, менуваат или откажуваат резервации, како и да оставаат рецензии за услугите.
За мерење на времињата се користи EXPLAIN ANALYZE. Кај секој поглед прво се брише индексот доколку постои, потоа се мери времето без индекс, па се креира индексот и повторно се мери времето со индекс. Како резултат во документацијата се зема вредноста Execution Time.
1. Анализа на поглед customer_appointments_view, преглед на резервации за конкретен корисник
Овој поглед се користи за прикажување на резервациите на конкретен корисник. Во апликацијата ова би се користело на профилот на корисникот, каде што тој може да ги види своите закажани термини, услугата, бизнисот, вработениот и времето на терминот.
Прашалникот кој го тестираме е:
SELECT * FROM customer_appointments_view WHERE customer_id = 100;
За точно да го измериме времето пред и после оптимизација, прво го бришеме индексот ако веќе постои, потоа го извршуваме прашалникот без индекс, па го креираме индексот и повторно го извршуваме истиот прашалник.
-- 1. Бришење на индексот ако веќе постои DROP INDEX IF EXISTS idx_appointment_customer_not_cancelled; -- 2. Освежување на статистиките ANALYZE appointment; -- 3. Мерење без индекс EXPLAIN ANALYZE SELECT * FROM customer_appointments_view WHERE customer_id = 100; -- 4. Креирање на индекс CREATE INDEX IF NOT EXISTS idx_appointment_customer_not_cancelled ON appointment(customer_id) WHERE status <> 'cancelled'; -- 5. Освежување на статистиките ANALYZE appointment; -- 6. Мерење со индекс EXPLAIN ANALYZE SELECT * FROM customer_appointments_view WHERE customer_id = 100;
Време на извршување без индекс:
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) -> Nested Loop (cost=83.41..122.30 rows=5 width=86) (actual time=3.361..4.267 rows=6 loops=1) -> Index Scan using pk_customer on customer c Index Cond: (customer_id = 100) -> Hash Join Hash Cond: (e.employee_id = a.employee_id) -> Seq Scan on employee e -> Hash -> Hash Right Join -> Seq Scan on business_location bl -> Hash -> Hash Join -> Seq Scan on business b -> Hash -> 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 -> Index Scan using pk_time_slot on time_slot ts Index Cond: (slot_id = a.slot_id) Planning Time: 8.862 ms Execution Time: 7.802 ms
Во овој план се гледа дека за табелата appointment се користи индексот idx_appointment_customer_profile, но сепак дополнително се применува филтерот:
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) -> Nested Loop (cost=87.11..125.99 rows=5 width=97) (actual time=0.557..0.672 rows=6 loops=1) -> Index Scan using pk_customer on customer c Index Cond: (customer_id = 100) -> Hash Join Hash Cond: (e.employee_id = a.employee_id) -> Seq Scan on employee e -> Hash -> Hash Right Join -> Seq Scan on business_location bl -> Hash -> Hash Join -> Seq Scan on business b -> Hash -> Hash Join Hash Cond: (s.service_id = a.service_id) -> Seq Scan on service s -> Hash -> Index Scan using idx_appointment_customer_not_cancelled on appointment a Index Cond: (customer_id = 100) -> Index Scan using pk_time_slot on time_slot ts Index Cond: (slot_id = a.slot_id) 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. Бришење на индексот ако веќе постои DROP INDEX IF EXISTS idx_time_slot_available_business_date; -- 2. Освежување на статистиките ANALYZE time_slot; -- 3. Мерење без индекс EXPLAIN ANALYZE SELECT * FROM available_slots WHERE business_id = 1 AND date = CURRENT_DATE; -- 4. Креирање на индекс CREATE INDEX IF NOT EXISTS idx_time_slot_available_business_date ON time_slot(business_id, date) WHERE is_available = TRUE; -- 5. Освежување на статистиките ANALYZE time_slot; -- 6. Мерење со индекс EXPLAIN ANALYZE SELECT * FROM available_slots WHERE business_id = 1 AND date = CURRENT_DATE;
Време на извршување без индекс:
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) -> Nested Loop (cost=1001.13..479265.89 rows=21 width=74) (actual time=7156.537..7157.258 rows=48 loops=1) Join Filter: ((es.employee_id = ts.employee_id) AND (es.service_id = bs.service_id)) -> Nested Loop -> Nested Loop -> Nested Loop -> Index Scan using uq_business_service_business_service on business_service bs Index Cond: (business_id = 1) Filter: is_active -> Materialize -> Index Scan using pk_business on business b Index Cond: (business_id = 1) -> Materialize -> 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 -> Memoize Cache Key: ts.employee_id -> Index Scan using pk_employee on employee e Index Cond: (employee_id = ts.employee_id) -> Index Only Scan using uq_employee_service_employee_service on employee_service es Index Cond: (employee_id = e.employee_id) Heap Fetches: 0 -> Index Scan using pk_service on service s Index Cond: (service_id = es.service_id) 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) -> Nested Loop (cost=69.10..250.31 rows=35 width=74) (actual time=0.359..0.443 rows=48 loops=1) -> Index Scan using pk_business on business b Index Cond: (business_id = 1) -> Hash Join Hash Cond: (es.service_id = bs.service_id) -> Nested Loop Join Filter: (es.employee_id = ts.employee_id) -> Hash Join Hash Cond: (ts.employee_id = e.employee_id) -> Index Scan using idx_time_slot_available_business_date on time_slot ts Index Cond: ((business_id = 1) AND (date = CURRENT_DATE)) -> Hash -> Seq Scan on employee e -> Index Only Scan using uq_employee_service_employee_service on employee_service es Index Cond: (employee_id = e.employee_id) Heap Fetches: 0 -> Hash -> Bitmap Heap Scan on business_service bs Recheck Cond: (business_id = 1) Filter: is_active -> Bitmap Index Scan on uq_business_service_business_service Index Cond: (business_id = 1) -> Index Scan using pk_service on service s Index Cond: (service_id = es.service_id) 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. Бришење на индексот ако веќе постои DROP INDEX IF EXISTS idx_review_rating_details; -- 2. Освежување на статистиките ANALYZE review; -- 3. Мерење без индекс EXPLAIN ANALYZE SELECT * FROM review_details WHERE rating = 5; -- 4. Креирање на индекс CREATE INDEX IF NOT EXISTS idx_review_rating_details ON review(rating); -- 5. Освежување на статистиките ANALYZE review; -- 6. Мерење со индекс EXPLAIN ANALYZE SELECT * FROM review_details WHERE rating = 5;
Време на извршување без индекс:
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) Hash Cond: (r.business_id = b.business_id) -> 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 -> Hash (cost=27.01..27.01 rows=1001 width=16) (actual time=0.528..0.528 rows=1001 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 59kB -> Seq Scan on business b (cost=0.00..27.01 rows=1001 width=16) (actual time=0.020..0.311 rows=1001 loops=1) 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) Hash Cond: (r.business_id = b.business_id) -> Bitmap Heap Scan on review r (cost=11207.40..70518.39 rows=1006319 width=27) (actual time=40.699..142.119 rows=1000001 loops=1) Recheck Cond: (rating = 5) Heap Blocks: exact=46732 -> Bitmap Index Scan on idx_review_rating_details (cost=0.00..10955.83 rows=1006319 width=0) (actual time=33.958..33.958 rows=1000001 loops=1) Index Cond: (rating = 5) -> Hash (cost=27.01..27.01 rows=1001 width=16) (actual time=0.220..0.221 rows=1001 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 59kB -> Seq Scan on business b (cost=0.00..27.01 rows=1001 width=16) (actual time=0.005..0.113 rows=1001 loops=1) 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. Бришење на индексот ако веќе постои DROP INDEX IF EXISTS idx_review_customer_profile; -- Ако случајно е креиран и овој индекс, го бришеме и него DROP INDEX IF EXISTS idx_appointment_customer_profile; -- 2. Освежување на статистиките ANALYZE appointment; ANALYZE review; -- 3. Мерење без индекс EXPLAIN ANALYZE SELECT * FROM customer_profile_view WHERE customer_id = 100; -- 4. Креирање на индекс CREATE INDEX IF NOT EXISTS idx_review_customer_profile ON review(customer_id, rating); -- 5. Освежување на статистиките ANALYZE appointment; ANALYZE review; -- 6. Мерење со индекс EXPLAIN ANALYZE SELECT * FROM customer_profile_view WHERE customer_id = 100;
Време на извршување без индекс:
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) Group Key: u.user_id -> Sort (cost=147561.37..147561.43 rows=25 width=77) (actual time=545.501..545.909 rows=49 loops=1) Sort Key: u.user_id, a.appointment_id Sort Method: quicksort Memory: 29kB -> Nested Loop Left Join (cost=2000.85..147560.79 rows=25 width=77) (actual time=4.673..545.848 rows=49 loops=1) -> Nested Loop Left Join (cost=1000.85..73786.27 rows=5 width=69) (actual time=4.562..382.328 rows=7 loops=1) -> Nested Loop (cost=0.85..16.89 rows=1 width=53) (actual time=1.813..1.815 rows=1 loops=1) -> Index Scan using pk_customer on customer c Index Cond: (customer_id = 100) -> Index Scan using pk_user on "user" u Index Cond: (user_id = c.user_id) -> Gather Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on appointment a Filter: (customer_id = 100) Rows Removed by Filter: 1666665 -> Materialize -> Gather Workers Planned: 2 Workers Launched: 2 -> 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) Group Key: u.user_id -> Sort (cost=73811.09..73811.15 rows=25 width=77) (actual time=122.573..123.117 rows=49 loops=1) Sort Key: u.user_id, a.appointment_id Sort Method: quicksort Memory: 29kB -> Nested Loop Left Join (cost=1001.28..73810.51 rows=25 width=77) (actual time=3.738..123.089 rows=49 loops=1) -> Nested Loop Left Join (cost=1000.85..73785.99 rows=5 width=69) (actual time=3.437..122.737 rows=7 loops=1) -> Nested Loop (cost=0.85..16.89 rows=1 width=53) (actual time=1.866..1.868 rows=1 loops=1) -> Index Scan using pk_customer on customer c Index Cond: (customer_id = 100) -> Index Scan using pk_user on "user" u Index Cond: (user_id = c.user_id) -> Gather Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on appointment a Filter: (customer_id = 100) Rows Removed by Filter: 1666665 -> Materialize -> 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 како кај некои други погледи. За дополнителна оптимизација може да се разгледа и индекс на appointment(customer_id), особено ако профилот секогаш ги прикажува резервациите на конкретниот корисник.
Споредба на времиња
| Поглед | Пред индекс | После индекс | Подобрување |
|---|---|---|---|
| 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. Бришење на индексите ако постојат 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; -- 2. Освежување на статистиките ANALYZE reschedule_request; -- 3. Мерење без индекси EXPLAIN ANALYZE SELECT * FROM reschedule_overview WHERE status = 'pending'; -- 4. Креирање на индексите 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. Освежување на статистиките ANALYZE reschedule_request; -- 6. Мерење со индекси EXPLAIN ANALYZE SELECT * FROM reschedule_overview WHERE status = 'pending';
Време на извршување без индекси:
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) Merge Cond: (ts_new.slot_id = rr.new_slot_id) -> Index Scan using pk_time_slot on time_slot ts_new -> Sort (cost=124.22..125.89 rows=667 width=56) (actual time=2.050..2.087 rows=667 loops=1) Sort Key: rr.new_slot_id Sort Method: quicksort Memory: 82kB -> Merge Join (cost=82.41..92.93 rows=667 width=56) (actual time=0.611..1.932 rows=667 loops=1) Merge Cond: (ts_old.slot_id = rr.old_slot_id) -> Index Scan using pk_time_slot on time_slot ts_old -> Sort (cost=81.31..82.98 rows=667 width=48) (actual time=0.575..0.605 rows=667 loops=1) Sort Key: rr.old_slot_id Sort Method: quicksort Memory: 77kB -> 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) Merge Cond: (ts_new.slot_id = rr.new_slot_id) -> Index Scan using pk_time_slot on time_slot ts_new -> Sort (cost=120.98..122.65 rows=667 width=56) (actual time=1.460..1.490 rows=667 loops=1) Sort Key: rr.new_slot_id Sort Method: quicksort Memory: 82kB -> Merge Join (cost=79.17..89.69 rows=667 width=56) (actual time=0.712..1.359 rows=667 loops=1) Merge Cond: (ts_old.slot_id = rr.old_slot_id) -> Index Scan using pk_time_slot on time_slot ts_old -> Sort (cost=78.07..79.74 rows=667 width=48) (actual time=0.691..0.718 rows=667 loops=1) Sort Key: rr.old_slot_id Sort Method: quicksort Memory: 77kB -> Bitmap Heap Scan on reschedule_request rr Recheck Cond: ((status)::text = 'pending'::text) Heap Blocks: exact=25 -> 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)
и потоа:
Bitmap Heap Scan on reschedule_request rr Recheck 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 и понатаму се скенира секвенцијално, прашалникот не се намалува под 1 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_review_rating_details idx_reschedule_request_status idx_reschedule_request_old_slot_id idx_reschedule_request_new_slot_id
Индексот idx_review_rating_details го намалува времето од 360.970 ms на 239.802 ms, но бидејќи rating = 5 враќа многу голем број редици, подобрувањето е ограничено. Доколку филтрирањето по оценка не е често сценарио во апликацијата, може да се размисли овој индекс да се избрише.
Индексот idx_reschedule_request_status го намалува времето од 5.051 ms на 3.988 ms, што е мала разлика. Дополнително, индексите idx_reschedule_request_old_slot_id и idx_reschedule_request_new_slot_id не се директно искористени во прикажаниот план на извршување. Затоа, ако овие индекси не се користат во други прашалници, може да се размисли за нивно бришење.
Бришењето би се направило со:
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;
Заклучуваме дека индексите треба да се задржат само ако носат значајно подобрување или ако се користат често во апликацијата. Во спротивно, подобро е да се избришат за да не создаваат непотребен трошок при внесување и ажурирање на податоците.
Конечно, најоправдани индекси за задржување во оваа фаза се парцијалните индекси за customer_appointments_view и available_slots, како и индексот за customer_profile_view, бидејќи таму добивката е најголема и сценаријата се чести во реалната употреба на системот.
