Changes between Version 7 and Version 8 of Optimization


Ignore:
Timestamp:
06/14/26 21:12:21 (5 days ago)
Author:
231184
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Optimization

    v7 v8  
    55Rezervo е систем за резервации кај занаетчии и даватели на услуги, каде што корисниците можат да пребаруваат достапни термини, да закажуваат, менуваат или откажуваат резервации, како и да оставаат рецензии за услугите.
    66
     7За мерење на времињата се користи `EXPLAIN ANALYZE`. Кај секој поглед прво се брише индексот доколку постои, потоа се мери времето без индекс, па се креира индексот и повторно се мери времето со индекс. Како резултат во документацијата се зема вредноста `Execution Time`.
     8
    79== 1. Анализа на поглед customer_appointments_view, преглед на резервации за конкретен корисник ==
    810
    9 Овој поглед се користи за прикажување на сите активни или релевантни термини на одреден корисник. Во апликацијата ова би се користело на профилот на корисникот, каде што тој ги гледа своите закажани термини.
     11Овој поглед се користи за прикажување на резервациите на конкретен корисник. Во апликацијата ова би се користело на профилот на корисникот, каде што тој може да ги види своите закажани термини, услугата, бизнисот, вработениот и времето на терминот.
    1012
    1113Прашалникот кој го тестираме е:
    1214
    1315{{{
    14 EXPLAIN ANALYZE
    1516SELECT *
    1617FROM customer_appointments_view
    … …  
    1819}}}
    1920
    20 === Време на извршување без индекс:
    21 
    22 **644.544 ms**
    23 
    24 Бидејќи прашалникот филтрира според `customer_id`, а во погледот не се потребни откажаните термини, креираме парцијален индекс врз табелата `appointment`. Со овој индекс се индексираат само редиците чиј статус не е `cancelled`, со што индексот е помал и поефикасен.
    25 
    26 {{{
     21За точно да го измериме времето пред и после оптимизација, прво го бришеме индексот ако веќе постои, потоа го извршуваме прашалникот без индекс, па го креираме индексот и повторно го извршуваме истиот прашалник.
     22
     23{{{
     24-- 1. Бришење на индексот ако веќе постои
     25DROP INDEX IF EXISTS idx_appointment_customer_not_cancelled;
     26
     27-- 2. Освежување на статистиките
     28ANALYZE appointment;
     29
     30-- 3. Мерење без индекс
     31EXPLAIN ANALYZE
     32SELECT *
     33FROM customer_appointments_view
     34WHERE customer_id = 100;
     35
     36-- 4. Креирање на индекс
    2737CREATE INDEX IF NOT EXISTS idx_appointment_customer_not_cancelled
    2838ON appointment(customer_id)
    2939WHERE status <> 'cancelled';
    3040
     41-- 5. Освежување на статистиките
    3142ANALYZE appointment;
    32 }}}
     43
     44-- 6. Мерење со индекс
     45EXPLAIN ANALYZE
     46SELECT *
     47FROM customer_appointments_view
     48WHERE customer_id = 100;
     49}}}
     50
     51=== Време на извршување без индекс:
     52
     53**7.802 ms**
     54
     55Дел од добиениот `EXPLAIN ANALYZE` output пред додавање на индексот:
     56
     57{{{
     58Nested Loop  (cost=88.12..168.91 rows=5 width=151) (actual time=3.988..7.635 rows=6 loops=1)
     59->  Hash Join  (cost=87.68..126.58 rows=5 width=97) (actual time=3.625..4.537 rows=6 loops=1)
     60Hash Cond: (a.service_id = s.service_id)
     61->  Nested Loop  (cost=83.41..122.30 rows=5 width=86) (actual time=3.361..4.267 rows=6 loops=1)
     62->  Index Scan using pk_customer on customer c
     63Index Cond: (customer_id = 100)
     64->  Hash Join
     65Hash Cond: (e.employee_id = a.employee_id)
     66->  Seq Scan on employee e
     67->  Hash
     68->  Hash Right Join
     69->  Seq Scan on business_location bl
     70->  Hash
     71->  Hash Join
     72->  Seq Scan on business b
     73->  Hash
     74->  Index Scan using idx_appointment_customer_profile on appointment a
     75Index Cond: (customer_id = 100)
     76Filter: ((status)::text <> 'cancelled'::text)
     77Rows Removed by Filter: 1
     78->  Index Scan using pk_time_slot on time_slot ts
     79Index Cond: (slot_id = a.slot_id)
     80
     81Planning Time: 8.862 ms
     82Execution Time: 7.802 ms
     83}}}
     84
     85Во овој план се гледа дека за табелата `appointment` се користи индексот `idx_appointment_customer_profile`, но сепак дополнително се применува филтерот:
     86
     87{{{
     88Filter: ((status)::text <> 'cancelled'::text)
     89}}}
     90
     91Ова значи дека базата ги наоѓа резервациите според `customer_id`, но потоа мора дополнително да ги филтрира оние кои имаат статус различен од `cancelled`.
     92
     93За да го оптимизираме ова, креираме парцијален индекс:
     94
     95{{{
     96CREATE INDEX IF NOT EXISTS idx_appointment_customer_not_cancelled
     97ON appointment(customer_id)
     98WHERE status <> 'cancelled';
     99}}}
     100
     101Овој индекс ги содржи само резервациите кои не се откажани. Бидејќи погледот `customer_appointments_view` ги користи токму тие резервации, индексот е помал и поефикасен.
    33102
    34103=== Време на извршување со индекс:
    35104
    36 **2.756 ms**
    37 
    38 Со додавање на индексот, времето на извршување се намали од **644.544 ms** на **2.756 ms**. Ова е значително подобрување бидејќи планерот повеќе нема потреба да ја скенира целата табела `appointment`, туку директно ги наоѓа редиците за конкретниот корисник.
     105**0.803 ms**
     106
     107Дел од добиениот `EXPLAIN ANALYZE` output после додавање на индексот:
     108
     109{{{
     110Nested Loop  (cost=87.54..168.32 rows=5 width=151) (actual time=0.576..0.714 rows=6 loops=1)
     111->  Nested Loop  (cost=87.11..125.99 rows=5 width=97) (actual time=0.557..0.672 rows=6 loops=1)
     112->  Index Scan using pk_customer on customer c
     113Index Cond: (customer_id = 100)
     114->  Hash Join
     115Hash Cond: (e.employee_id = a.employee_id)
     116->  Seq Scan on employee e
     117->  Hash
     118->  Hash Right Join
     119->  Seq Scan on business_location bl
     120->  Hash
     121->  Hash Join
     122->  Seq Scan on business b
     123->  Hash
     124->  Hash Join
     125Hash Cond: (s.service_id = a.service_id)
     126->  Seq Scan on service s
     127->  Hash
     128->  Index Scan using idx_appointment_customer_not_cancelled on appointment a
     129Index Cond: (customer_id = 100)
     130->  Index Scan using pk_time_slot on time_slot ts
     131Index Cond: (slot_id = a.slot_id)
     132
     133Planning Time: 3.150 ms
     134Execution Time: 0.803 ms
     135}}}
     136
     137После додавање на индексот се гледа дека планерот го користи новиот индекс:
     138
     139{{{
     140Index Scan using idx_appointment_customer_not_cancelled on appointment a
     141Index Cond: (customer_id = 100)
     142}}}
     143
     144Овој пат нема дополнителен филтер за `status <> 'cancelled'`, бидејќи самиот индекс веќе ги содржи само редиците кои не се откажани.
     145
     146=== Споредба на времиња ===
     147
     148||= Поглед =||= Пред индекс =||= После индекс =||= Подобрување =||
     149|| customer_appointments_view || 7.802 ms || 0.803 ms || околу 9.7 пати побрзо ||
     150
     151Со додавање на парцијалниот индекс `idx_appointment_customer_not_cancelled`, времето на извршување се намали од **7.802 ms** на **0.803 ms**. Ова покажува дека индексот е соодветен за овој поглед, бидејќи прашалникот најчесто пребарува резервации за конкретен корисник и ги исклучува откажаните резервации.
    39152
    40153== 2. Анализа на поглед available_slots, пребарување на слободни термини за даден бизнис и датум ==
    41154
    42 Овој поглед се користи кога корисник пребарува достапни термини кај одреден бизнис за конкретен ден. Ова е еден од најчестите прашалници во системот, бидејќи секое закажување започнува со пребарување на слободни термини.
     155Овој поглед се користи кога корисник пребарува достапни термини кај одреден бизнис за конкретен ден. Ова е едно од најважните сценарија во апликацијата, бидејќи процесот на резервација започнува со избор на слободен термин.
    43156
    44157Прашалникот кој го тестираме е:
    45158
    46159{{{
    47 EXPLAIN ANALYZE
    48160SELECT *
    49161FROM available_slots
    … …  
    52164}}}
    53165
    54 === Време на извршување без индекс:
    55 
    56 **429.479 ms**
    57 
    58 Погледот пребарува според `business_id`, `date` и дополнително ги зема само термините кои се достапни. Затоа креираме парцијален индекс кој ги покрива само термините каде што `is_available = TRUE`.
    59 
    60 {{{
     166За точно мерење на времето пред и после оптимизација, прво го бришеме индексот доколку постои, потоа го извршуваме прашалникот без индекс, па го креираме индексот и повторно го извршуваме истиот прашалник.
     167
     168{{{
     169-- 1. Бришење на индексот ако веќе постои
     170DROP INDEX IF EXISTS idx_time_slot_available_business_date;
     171
     172-- 2. Освежување на статистиките
     173ANALYZE time_slot;
     174
     175-- 3. Мерење без индекс
     176EXPLAIN ANALYZE
     177SELECT *
     178FROM available_slots
     179WHERE business_id = 1
     180AND date = CURRENT_DATE;
     181
     182-- 4. Креирање на индекс
    61183CREATE INDEX IF NOT EXISTS idx_time_slot_available_business_date
    62184ON time_slot(business_id, date)
    63185WHERE is_available = TRUE;
    64186
     187-- 5. Освежување на статистиките
    65188ANALYZE time_slot;
    66 }}}
     189
     190-- 6. Мерење со индекс
     191EXPLAIN ANALYZE
     192SELECT *
     193FROM available_slots
     194WHERE business_id = 1
     195AND date = CURRENT_DATE;
     196}}}
     197
     198=== Време на извршување без индекс:
     199
     200**7157.628 ms**
     201
     202Дел од добиениот `EXPLAIN ANALYZE` output пред додавање на индексот:
     203
     204{{{
     205Nested Loop  (cost=1001.27..479269.43 rows=21 width=100) (actual time=7156.545..7157.282 rows=48 loops=1)
     206->  Nested Loop  (cost=1001.13..479265.89 rows=21 width=74) (actual time=7156.537..7157.258 rows=48 loops=1)
     207Join Filter: ((es.employee_id = ts.employee_id) AND (es.service_id = bs.service_id))
     208->  Nested Loop
     209->  Nested Loop
     210->  Nested Loop
     211->  Index Scan using uq_business_service_business_service on business_service bs
     212Index Cond: (business_id = 1)
     213Filter: is_active
     214->  Materialize
     215->  Index Scan using pk_business on business b
     216Index Cond: (business_id = 1)
     217->  Materialize
     218->  Gather
     219Workers Planned: 2
     220Workers Launched: 2
     221->  Parallel Seq Scan on time_slot ts
     222Filter: (is_available AND (business_id = 1) AND (date = CURRENT_DATE))
     223Rows Removed by Filter: 6666651
     224->  Memoize
     225Cache Key: ts.employee_id
     226->  Index Scan using pk_employee on employee e
     227Index Cond: (employee_id = ts.employee_id)
     228->  Index Only Scan using uq_employee_service_employee_service on employee_service es
     229Index Cond: (employee_id = e.employee_id)
     230Heap Fetches: 0
     231->  Index Scan using pk_service on service s
     232Index Cond: (service_id = es.service_id)
     233
     234Planning Time: 4.741 ms
     235Execution Time: 7157.628 ms
     236}}}
     237
     238Во овој план се гледа дека најбавниот дел е скенирањето на табелата `time_slot`:
     239
     240{{{
     241Parallel Seq Scan on time_slot ts
     242Filter: (is_available AND (business_id = 1) AND (date = CURRENT_DATE))
     243Rows Removed by Filter: 6666651
     244}}}
     245
     246Ова значи дека базата мора да помине низ многу голем број редици во `time_slot` за да ги најде термините кои се достапни, се за конкретниот бизнис и се за тековниот датум. Поради тоа времето на извршување е високо.
     247
     248За да го оптимизираме ова, креираме парцијален индекс:
     249
     250{{{
     251CREATE INDEX IF NOT EXISTS idx_time_slot_available_business_date
     252ON time_slot(business_id, date)
     253WHERE is_available = TRUE;
     254}}}
     255
     256Овој индекс ги содржи само достапните термини, односно редиците каде што `is_available = TRUE`. Бидејќи прашалникот секогаш бара достапни термини за конкретен `business_id` и `date`, индексот е многу погоден за овој поглед.
    67257
    68258=== Време на извршување со индекс:
    69259
    70 **1.090 ms**
    71 
    72 Со овој индекс времето се намали од **429.479 ms** на **1.090 ms**. Индексот е многу корисен бидејќи прашалникот најчесто бара само достапни термини, па нема потреба да се пребаруваат сите термини во табелата `time_slot`.
     260**0.580 ms**
     261
     262Дел од добиениот `EXPLAIN ANALYZE` output после додавање на индексот:
     263
     264{{{
     265Nested Loop  (cost=69.25..256.19 rows=35 width=100) (actual time=0.364..0.485 rows=48 loops=1)
     266->  Nested Loop  (cost=69.10..250.31 rows=35 width=74) (actual time=0.359..0.443 rows=48 loops=1)
     267->  Index Scan using pk_business on business b
     268Index Cond: (business_id = 1)
     269->  Hash Join
     270Hash Cond: (es.service_id = bs.service_id)
     271->  Nested Loop
     272Join Filter: (es.employee_id = ts.employee_id)
     273->  Hash Join
     274Hash Cond: (ts.employee_id = e.employee_id)
     275->  Index Scan using idx_time_slot_available_business_date on time_slot ts
     276Index Cond: ((business_id = 1) AND (date = CURRENT_DATE))
     277->  Hash
     278->  Seq Scan on employee e
     279->  Index Only Scan using uq_employee_service_employee_service on employee_service es
     280Index Cond: (employee_id = e.employee_id)
     281Heap Fetches: 0
     282->  Hash
     283->  Bitmap Heap Scan on business_service bs
     284Recheck Cond: (business_id = 1)
     285Filter: is_active
     286->  Bitmap Index Scan on uq_business_service_business_service
     287Index Cond: (business_id = 1)
     288->  Index Scan using pk_service on service s
     289Index Cond: (service_id = es.service_id)
     290
     291Planning Time: 2.741 ms
     292Execution Time: 0.580 ms
     293}}}
     294
     295После додавање на индексот, планерот го користи новиот индекс:
     296
     297{{{
     298Index Scan using idx_time_slot_available_business_date on time_slot ts
     299Index Cond: ((business_id = 1) AND (date = CURRENT_DATE))
     300}}}
     301
     302Ова значи дека наместо да ја скенира целата табела `time_slot`, базата директно ги наоѓа достапните термини за бараниот бизнис и датум. Со тоа се избегнува секвенцијалното скенирање на милиони редици.
     303
     304=== Споредба на времиња ===
     305
     306||= Поглед =||= Пред индекс =||= После индекс =||= Подобрување =||
     307|| available_slots || 7157.628 ms || 0.580 ms || над 12000 пати побрзо ||
     308
     309Со додавање на парцијалниот индекс `idx_time_slot_available_business_date`, времето на извршување се намали од **7157.628 ms** на **0.580 ms**. Ова е огромно подобрување, бидејќи прашалникот повеќе не прави `Parallel Seq Scan` врз табелата `time_slot`, туку користи директен `Index Scan` преку новиот индекс.
    73310
    74311== 3. Анализа на поглед review_details, детални рецензии според оценка ==
    75312
    76 Овој поглед се користи кога сакаме да ги прикажеме рецензиите кои имаат одредена оценка, на пример сите рецензии со оценка 5. Ова може да се користи за филтрирање на рецензии во корисничкиот интерфејс.
     313Овој поглед се користи за прикажување на детални информации за рецензии. Во апликацијата ова може да се користи кога корисникот сака да ги филтрира рецензиите според оценка, на пример да ги види сите рецензии со оценка 5.
    77314
    78315Прашалникот кој го тестираме е:
    79316
    80317{{{
    81 EXPLAIN ANALYZE
    82318SELECT *
    83319FROM review_details
    … …  
    85321}}}
    86322
    87 === Време на извршување без индекс:
    88 
    89 **800.553 ms**
    90 
    91 Бидејќи прашалникот филтрира според колоната `rating`, креираме индекс на оваа колона.
    92 
    93 {{{
     323За точно мерење на времето пред и после оптимизација, прво го бришеме индексот доколку постои, потоа го извршуваме прашалникот без индекс, па го креираме индексот и повторно го извршуваме истиот прашалник.
     324
     325{{{
     326-- 1. Бришење на индексот ако веќе постои
     327DROP INDEX IF EXISTS idx_review_rating_details;
     328
     329-- 2. Освежување на статистиките
     330ANALYZE review;
     331
     332-- 3. Мерење без индекс
     333EXPLAIN ANALYZE
     334SELECT *
     335FROM review_details
     336WHERE rating = 5;
     337
     338-- 4. Креирање на индекс
    94339CREATE INDEX IF NOT EXISTS idx_review_rating_details
    95340ON review(rating);
    96341
     342-- 5. Освежување на статистиките
    97343ANALYZE review;
    98 }}}
     344
     345-- 6. Мерење со индекс
     346EXPLAIN ANALYZE
     347SELECT *
     348FROM review_details
     349WHERE rating = 5;
     350}}}
     351
     352=== Време на извршување без индекс:
     353
     354**360.970 ms**
     355
     356Дел од добиениот `EXPLAIN ANALYZE` output пред додавање на индексот:
     357
     358{{{
     359Hash Join  (cost=39.52..111924.47 rows=1006495 width=35) (actual time=0.710..344.505 rows=1000001 loops=1)
     360Hash Cond: (r.business_id = b.business_id)
     361->  Seq Scan on review r  (cost=0.00..109231.71 rows=1006495 width=27) (actual time=0.169..259.457 rows=1000001 loops=1)
     362Filter: (rating = 5)
     363Rows Removed by Filter: 4000000
     364->  Hash  (cost=27.01..27.01 rows=1001 width=16) (actual time=0.528..0.528 rows=1001 loops=1)
     365Buckets: 1024  Batches: 1  Memory Usage: 59kB
     366->  Seq Scan on business b  (cost=0.00..27.01 rows=1001 width=16) (actual time=0.020..0.311 rows=1001 loops=1)
     367
     368Planning Time: 2.849 ms
     369Execution Time: 360.970 ms
     370}}}
     371
     372Во овој план се гледа дека табелата `review` се скенира секвенцијално:
     373
     374{{{
     375Seq Scan on review r
     376Filter: (rating = 5)
     377Rows Removed by Filter: 4000000
     378}}}
     379
     380Ова значи дека базата ја чита целата табела `review`, потоа ги задржува само рецензиите со `rating = 5`, а ги отфрла останатите. Во конкретниот случај се отстранети **4 000 000** редици, што покажува дека без индекс се чита многу поголем број податоци од потребното.
     381
     382За да го оптимизираме ова, креираме индекс на колоната `rating`:
     383
     384{{{
     385CREATE INDEX IF NOT EXISTS idx_review_rating_details
     386ON review(rating);
     387}}}
     388
     389Овој индекс му овозможува на планерот директно да ги најде редиците со конкретна оценка, наместо да ја скенира целата табела.
    99390
    100391=== Време на извршување со индекс:
    101392
    102 **340.962 ms**
    103 
    104 Со додавање на индексот времето се намали од **800.553 ms** на **340.962 ms**. Подобрувањето е забележливо, но не е толку големо како кај претходните прашалници, бидејќи вредноста `rating = 5` може да се појавува кај голем број редици. Во таков случај индексот помага, но сепак мора да се прочита голем дел од табелата.
     393**239.802 ms**
     394
     395Дел од добиениот `EXPLAIN ANALYZE` output после додавање на индексот:
     396
     397{{{
     398Hash Join  (cost=11246.93..73210.68 rows=1006319 width=35) (actual time=40.929..223.700 rows=1000001 loops=1)
     399Hash Cond: (r.business_id = b.business_id)
     400->  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)
     401Recheck Cond: (rating = 5)
     402Heap Blocks: exact=46732
     403->  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)
     404Index Cond: (rating = 5)
     405->  Hash  (cost=27.01..27.01 rows=1001 width=16) (actual time=0.220..0.221 rows=1001 loops=1)
     406Buckets: 1024  Batches: 1  Memory Usage: 59kB
     407->  Seq Scan on business b  (cost=0.00..27.01 rows=1001 width=16) (actual time=0.005..0.113 rows=1001 loops=1)
     408
     409Planning Time: 0.593 ms
     410Execution Time: 239.802 ms
     411}}}
     412
     413После додавање на индексот се гледа дека планерот веќе не прави `Seq Scan` врз `review`, туку користи:
     414
     415{{{
     416Bitmap Index Scan on idx_review_rating_details
     417Index Cond: (rating = 5)
     418}}}
     419
     420и потоа:
     421
     422{{{
     423Bitmap Heap Scan on review r
     424Recheck Cond: (rating = 5)
     425}}}
     426
     427Ова значи дека базата прво преку индексот ги наоѓа позициите на редиците кои имаат `rating = 5`, а потоа ги чита тие редици од табелата.
     428
     429Подобрувањето кај овој поглед е помало во споредба со `available_slots`, бидејќи `rating = 5` се појавува кај многу голем број редици. Во конкретниот резултат има **1 000 001** рецензија со оценка 5. Кога условот враќа голем дел од табелата, индексот помага, но сепак мора да се прочитаат многу редици.
     430
     431=== Споредба на времиња ===
     432
     433||= Поглед =||= Пред индекс =||= После индекс =||= Подобрување =||
     434|| review_details || 360.970 ms || 239.802 ms || околу 1.5 пати побрзо ||
     435
     436Со додавање на индексот `idx_review_rating_details`, времето на извршување се намали од **360.970 ms** на **239.802 ms**. Индексот е корисен затоа што го заменува секвенцијалното скенирање на целата табела со `Bitmap Index Scan`, но подобрувањето не е екстремно бидејќи условот `rating = 5` враќа голем број редици.
    105437
    106438== 4. Анализа на поглед customer_profile_view, профил на корисник со негови активности ==
    107439
    108 Овој поглед се користи за прикажување на профил на корисник, каде што можат да се прикажат неговите резервации, рецензии и други информации поврзани со активностите во системот.
     440Овој поглед се користи за прикажување на профил на конкретен корисник. Во апликацијата ова би се користело кога корисникот го отвора својот профил и сака да ги види своите информации, резервации и оставени рецензии.
    109441
    110442Прашалникот кој го тестираме е:
    111443
    112444{{{
    113 EXPLAIN ANALYZE
    114445SELECT *
    115446FROM customer_profile_view
    … …  
    117448}}}
    118449
    119 === Време на извршување без индекс:
    120 
    121 **708.985 ms**
    122 
    123 За оптимизација на овој поглед креираме индекс врз табелата `review`, бидејќи погледот користи информации за рецензии поврзани со конкретен корисник.
    124 
    125 {{{
     450За точно мерење на времето пред и после оптимизација, прво ги бришеме индексите доколку постојат, потоа го извршуваме прашалникот без индекс, па го креираме индексот и повторно го извршуваме истиот прашалник.
     451
     452{{{
     453-- 1. Бришење на индексот ако веќе постои
     454DROP INDEX IF EXISTS idx_review_customer_profile;
     455
     456-- Ако случајно е креиран и овој индекс, го бришеме и него
     457DROP INDEX IF EXISTS idx_appointment_customer_profile;
     458
     459-- 2. Освежување на статистиките
     460ANALYZE appointment;
     461ANALYZE review;
     462
     463-- 3. Мерење без индекс
     464EXPLAIN ANALYZE
     465SELECT *
     466FROM customer_profile_view
     467WHERE customer_id = 100;
     468
     469-- 4. Креирање на индекс
    126470CREATE INDEX IF NOT EXISTS idx_review_customer_profile
    127471ON review(customer_id, rating);
    128472
     473-- 5. Освежување на статистиките
    129474ANALYZE appointment;
    130475ANALYZE review;
    131 }}}
     476
     477-- 6. Мерење со индекс
     478EXPLAIN ANALYZE
     479SELECT *
     480FROM customer_profile_view
     481WHERE customer_id = 100;
     482}}}
     483
     484=== Време на извршување без индекс:
     485
     486**546.242 ms**
     487
     488Дел од добиениот `EXPLAIN ANALYZE` output пред додавање на индексот:
     489
     490{{{
     491GroupAggregate  (cost=147561.37..147562.12 rows=25 width=117) (actual time=545.746..546.153 rows=1 loops=1)
     492Group Key: u.user_id
     493->  Sort  (cost=147561.37..147561.43 rows=25 width=77) (actual time=545.501..545.909 rows=49 loops=1)
     494Sort Key: u.user_id, a.appointment_id
     495Sort Method: quicksort  Memory: 29kB
     496->  Nested Loop Left Join  (cost=2000.85..147560.79 rows=25 width=77) (actual time=4.673..545.848 rows=49 loops=1)
     497->  Nested Loop Left Join  (cost=1000.85..73786.27 rows=5 width=69) (actual time=4.562..382.328 rows=7 loops=1)
     498->  Nested Loop  (cost=0.85..16.89 rows=1 width=53) (actual time=1.813..1.815 rows=1 loops=1)
     499->  Index Scan using pk_customer on customer c
     500Index Cond: (customer_id = 100)
     501->  Index Scan using pk_user on "user" u
     502Index Cond: (user_id = c.user_id)
     503->  Gather
     504Workers Planned: 2
     505Workers Launched: 2
     506->  Parallel Seq Scan on appointment a
     507Filter: (customer_id = 100)
     508Rows Removed by Filter: 1666665
     509->  Materialize
     510->  Gather
     511Workers Planned: 2
     512Workers Launched: 2
     513->  Parallel Seq Scan on review r
     514Filter: (customer_id = 100)
     515Rows Removed by Filter: 1666665
     516
     517Planning Time: 7.447 ms
     518Execution Time: 546.242 ms
     519}}}
     520
     521Во овој план се гледа дека најголемиот проблем е што табелите `appointment` и `review` се скенираат секвенцијално. За `appointment` се користи:
     522
     523{{{
     524Parallel Seq Scan on appointment a
     525Filter: (customer_id = 100)
     526Rows Removed by Filter: 1666665
     527}}}
     528
     529а за `review` се користи:
     530
     531{{{
     532Parallel Seq Scan on review r
     533Filter: (customer_id = 100)
     534Rows Removed by Filter: 1666665
     535}}}
     536
     537Ова значи дека базата чита многу голем број редици за да ги најде записите кои припаѓаат на корисникот со `customer_id = 100`.
     538
     539За оптимизација креираме индекс на табелата `review`:
     540
     541{{{
     542CREATE INDEX IF NOT EXISTS idx_review_customer_profile
     543ON review(customer_id, rating);
     544}}}
     545
     546Овој индекс е корисен бидејќи погледот `customer_profile_view` ги користи рецензиите на конкретен корисник, а дополнително може да ја користи и колоната `rating` при пресметки или приказ на информации поврзани со оценките.
    132547
    133548=== Време на извршување со индекс:
    134549
    135 **0.767 ms**
    136 
    137 Со додавање на индексот времето се намали од **708.985 ms** на **0.767 ms**. Ова е многу големо подобрување, бидејќи податоците за конкретниот корисник се наоѓаат директно преку индексот, наместо да се пребарува целата табела `review`.
    138 
    139 Во кодот беше разгледан и индексот:
    140 
    141 {{{
    142 -- CREATE INDEX IF NOT EXISTS idx_appointment_customer_profile
    143 --     ON appointment(customer_id, created_at);
    144 }}}
    145 
    146 Овој индекс може да биде корисен доколку во погледот или во прашалниците често се користи сортирање или филтрирање според `created_at`. Во конкретниот тест, главното подобрување е постигнато со индексот врз `review(customer_id, rating)`.
     550**123.200 ms**
     551
     552Дел од добиениот `EXPLAIN ANALYZE` output после додавање на индексот:
     553
     554{{{
     555GroupAggregate  (cost=73811.09..73811.84 rows=25 width=117) (actual time=122.594..123.137 rows=1 loops=1)
     556Group Key: u.user_id
     557->  Sort  (cost=73811.09..73811.15 rows=25 width=77) (actual time=122.573..123.117 rows=49 loops=1)
     558Sort Key: u.user_id, a.appointment_id
     559Sort Method: quicksort  Memory: 29kB
     560->  Nested Loop Left Join  (cost=1001.28..73810.51 rows=25 width=77) (actual time=3.738..123.089 rows=49 loops=1)
     561->  Nested Loop Left Join  (cost=1000.85..73785.99 rows=5 width=69) (actual time=3.437..122.737 rows=7 loops=1)
     562->  Nested Loop  (cost=0.85..16.89 rows=1 width=53) (actual time=1.866..1.868 rows=1 loops=1)
     563->  Index Scan using pk_customer on customer c
     564Index Cond: (customer_id = 100)
     565->  Index Scan using pk_user on "user" u
     566Index Cond: (user_id = c.user_id)
     567->  Gather
     568Workers Planned: 2
     569Workers Launched: 2
     570->  Parallel Seq Scan on appointment a
     571Filter: (customer_id = 100)
     572Rows Removed by Filter: 1666665
     573->  Materialize
     574->  Index Scan using idx_review_customer_profile on review r
     575Index Cond: (customer_id = 100)
     576
     577Planning Time: 6.138 ms
     578Execution Time: 123.200 ms
     579}}}
     580
     581После додавање на индексот се гледа дека табелата `review` повеќе не се скенира секвенцијално, туку се користи новиот индекс:
     582
     583{{{
     584Index Scan using idx_review_customer_profile on review r
     585Index Cond: (customer_id = 100)
     586}}}
     587
     588Ова значи дека базата директно ги наоѓа рецензиите за конкретниот корисник, наместо да ја чита целата табела `review`.
     589
     590Сепак, во планот и понатаму се гледа дека табелата `appointment` се скенира секвенцијално:
     591
     592{{{
     593Parallel Seq Scan on appointment a
     594Filter: (customer_id = 100)
     595Rows Removed by Filter: 1666665
     596}}}
     597
     598Затоа времето се подобрува значително, но не паѓа под 1 ms како кај некои други погледи. За дополнителна оптимизација може да се разгледа и индекс на `appointment(customer_id)`, особено ако профилот секогаш ги прикажува резервациите на конкретниот корисник.
     599
     600=== Споредба на времиња ===
     601
     602||= Поглед =||= Пред индекс =||= После индекс =||= Подобрување =||
     603|| customer_profile_view || 546.242 ms || 123.200 ms || околу 4.4 пати побрзо ||
     604
     605Со додавање на индексот `idx_review_customer_profile`, времето на извршување се намали од **546.242 ms** на **123.200 ms**. Индексот е корисен бидејќи го заменува секвенцијалното скенирање на табелата `review` со директно индексно пребарување според `customer_id`.
     606
     607Иако `appointment` и понатаму се скенира секвенцијално, оваа оптимизација значително го намалува вкупното време на извршување на погледот.
    147608
    148609== 5. Анализа на поглед reschedule_overview, преглед на барања за промена на термин ==
    149610
    150 Овој поглед се користи за прикажување на барања за презакажување на термини. Во апликацијата ова би се користело од страна на бизнисот или вработениот за да ги види барањата кои се во статус `pending`.
     611Овој поглед се користи за прикажување на барања за презакажување на термини. Во апликацијата ова би се користело од страна на бизнисот или вработениот за да ги види барањата кои се во статус `pending`, односно барањата кои сè уште чекаат одобрување или одбивање.
    151612
    152613Прашалникот кој го тестираме е:
    153614
    154615{{{
    155 EXPLAIN ANALYZE
    156616SELECT *
    157617FROM reschedule_overview
    … …  
    159619}}}
    160620
    161 За оптимизација креираме индекси врз колоните кои најчесто се користат во филтрирање и спојување на табелата `reschedule_request`.
    162 
    163 {{{
     621За точно мерење на времето пред и после оптимизација, прво ги бришеме индексите доколку постојат, потоа го извршуваме прашалникот без индекси, па ги креираме индексите и повторно го извршуваме истиот прашалник.
     622
     623{{{
     624-- 1. Бришење на индексите ако постојат
     625DROP INDEX IF EXISTS idx_reschedule_request_status;
     626DROP INDEX IF EXISTS idx_reschedule_request_old_slot_id;
     627DROP INDEX IF EXISTS idx_reschedule_request_new_slot_id;
     628
     629-- 2. Освежување на статистиките
     630ANALYZE reschedule_request;
     631
     632-- 3. Мерење без индекси
     633EXPLAIN ANALYZE
     634SELECT *
     635FROM reschedule_overview
     636WHERE status = 'pending';
     637
     638-- 4. Креирање на индексите
    164639CREATE INDEX IF NOT EXISTS idx_reschedule_request_status
    165640ON reschedule_request(status);
    … …  
    171646ON reschedule_request(new_slot_id);
    172647
     648-- 5. Освежување на статистиките
    173649ANALYZE reschedule_request;
    174 }}}
    175 
    176 Потоа повторно го извршуваме прашалникот:
    177 
    178 {{{
     650
     651-- 6. Мерење со индекси
    179652EXPLAIN ANALYZE
    180653SELECT *
    … …  
    183656}}}
    184657
    185 Индексот `idx_reschedule_request_status` помага при филтрирање на барањата според статус, особено за чести прашалници како `status = 'pending'`.
    186 
    187 Индексите `idx_reschedule_request_old_slot_id` и `idx_reschedule_request_new_slot_id` помагаат при спојување со табелата за термини, бидејќи барањата за презакажување најчесто ги поврзуваат стариот и новиот термин преку овие колони.
    188 
    189 === Време на извршување:
    190 
    191 Во овој дел треба да се внесат резултатите од `EXPLAIN ANALYZE` пред и после додавање на индексите.
    192 
    193 {{{
    194 -- Пред оптимизација:   да се внесе измереното време
    195 -- После оптимизација:  да се внесе измереното време
    196 }}}
    197 
    198 == 6. Влијание на индексите врз insert/update операции ==
    199 
    200 Иако индексите значително го подобруваат читањето на податоците, тие можат да имаат мало влијание врз операциите за внесување и ажурирање. Причината е тоа што при секој `INSERT`, `UPDATE` или `DELETE`, базата мора да ги ажурира и индексите, не само главната табела.
    201 
    202 За тестирање на влијанието може да се користат следните прашалници:
    203 
    204 {{{
    205 EXPLAIN ANALYZE
    206 INSERT INTO review (customer_id, business_id, rating, comment, created_at)
    207 VALUES (100, 1, 5, 'Benchmark review', CURRENT_TIMESTAMP);
    208 }}}
    209 
    210 {{{
    211 EXPLAIN ANALYZE
    212 UPDATE review
    213 SET rating = 4
    214 WHERE customer_id = 100;
    215 }}}
    216 
    217 {{{
    218 EXPLAIN ANALYZE
    219 INSERT INTO time_slot (business_id, date, start_time, end_time, is_available)
    220 VALUES (1, CURRENT_DATE, TIME '10:00', TIME '10:30', TRUE);
    221 }}}
    222 
    223 {{{
    224 EXPLAIN ANALYZE
    225 UPDATE time_slot
    226 SET is_available = FALSE
    227 WHERE business_id = 1
    228 AND date = CURRENT_DATE;
    229 }}}
    230 
    231 {{{
    232 EXPLAIN ANALYZE
    233 UPDATE reschedule_request
    234 SET status = 'approved'
    235 WHERE status = 'pending';
    236 }}}
    237 
    238 Очекувано е индексите да внесат мал трошок при запишување, но бидејќи главните сценарија во апликацијата се пребарување на слободни термини, преглед на резервации и читање на рецензии, добивката кај `SELECT` прашалниците е многу поголема од потенцијалниот трошок кај `INSERT` и `UPDATE`.
    239 
    240 == 7. Заклучок ==
    241 
    242 Со додавање на соодветни индекси успеавме значително да ги подобриме перформансите на најважните прашалници во системот Rezervo.
     658=== Време на извршување без индекси:
     659
     660**5.051 ms**
     661
     662Дел од добиениот `EXPLAIN ANALYZE` output пред додавање на индексите:
     663
     664{{{
     665Merge Join  (cost=124.66..141.39 rows=667 width=64) (actual time=4.200..5.002 rows=667 loops=1)
     666Merge Cond: (ts_new.slot_id = rr.new_slot_id)
     667->  Index Scan using pk_time_slot on time_slot ts_new
     668->  Sort  (cost=124.22..125.89 rows=667 width=56) (actual time=2.050..2.087 rows=667 loops=1)
     669Sort Key: rr.new_slot_id
     670Sort Method: quicksort  Memory: 82kB
     671->  Merge Join  (cost=82.41..92.93 rows=667 width=56) (actual time=0.611..1.932 rows=667 loops=1)
     672Merge Cond: (ts_old.slot_id = rr.old_slot_id)
     673->  Index Scan using pk_time_slot on time_slot ts_old
     674->  Sort  (cost=81.31..82.98 rows=667 width=48) (actual time=0.575..0.605 rows=667 loops=1)
     675Sort Key: rr.old_slot_id
     676Sort Method: quicksort  Memory: 77kB
     677->  Seq Scan on reschedule_request rr
     678Filter: ((status)::text = 'pending'::text)
     679Rows Removed by Filter: 1335
     680
     681Planning Time: 1.921 ms
     682Execution Time: 5.051 ms
     683}}}
     684
     685Во овој план се гледа дека табелата `reschedule_request` се скенира секвенцијално:
     686
     687{{{
     688Seq Scan on reschedule_request rr
     689Filter: ((status)::text = 'pending'::text)
     690Rows Removed by Filter: 1335
     691}}}
     692
     693Ова значи дека базата ја чита табелата `reschedule_request`, па потоа ги задржува само барањата со статус `pending`. Иако табелата во овој тест не е многу голема, ова може да стане проблем ако бројот на барања за презакажување се зголеми.
     694
     695За оптимизација ги креираме следните индекси:
     696
     697{{{
     698CREATE INDEX IF NOT EXISTS idx_reschedule_request_status
     699ON reschedule_request(status);
     700
     701CREATE INDEX IF NOT EXISTS idx_reschedule_request_old_slot_id
     702ON reschedule_request(old_slot_id);
     703
     704CREATE INDEX IF NOT EXISTS idx_reschedule_request_new_slot_id
     705ON reschedule_request(new_slot_id);
     706}}}
     707
     708Индексот `idx_reschedule_request_status` е корисен за филтрирање според статус, додека индексите `idx_reschedule_request_old_slot_id` и `idx_reschedule_request_new_slot_id` можат да помогнат при спојувањата со табелата `time_slot`, бидејќи погледот ги поврзува старите и новите термини преку овие колони.
     709
     710=== Време на извршување со индекси:
     711
     712**3.988 ms**
     713
     714Дел од добиениот `EXPLAIN ANALYZE` output после додавање на индексите:
     715
     716{{{
     717Merge Join  (cost=121.42..138.15 rows=667 width=64) (actual time=3.341..3.903 rows=667 loops=1)
     718Merge Cond: (ts_new.slot_id = rr.new_slot_id)
     719->  Index Scan using pk_time_slot on time_slot ts_new
     720->  Sort  (cost=120.98..122.65 rows=667 width=56) (actual time=1.460..1.490 rows=667 loops=1)
     721Sort Key: rr.new_slot_id
     722Sort Method: quicksort  Memory: 82kB
     723->  Merge Join  (cost=79.17..89.69 rows=667 width=56) (actual time=0.712..1.359 rows=667 loops=1)
     724Merge Cond: (ts_old.slot_id = rr.old_slot_id)
     725->  Index Scan using pk_time_slot on time_slot ts_old
     726->  Sort  (cost=78.07..79.74 rows=667 width=48) (actual time=0.691..0.718 rows=667 loops=1)
     727Sort Key: rr.old_slot_id
     728Sort Method: quicksort  Memory: 77kB
     729->  Bitmap Heap Scan on reschedule_request rr
     730Recheck Cond: ((status)::text = 'pending'::text)
     731Heap Blocks: exact=25
     732->  Bitmap Index Scan on idx_reschedule_request_status
     733Index Cond: ((status)::text = 'pending'::text)
     734
     735Planning Time: 0.904 ms
     736Execution Time: 3.988 ms
     737}}}
     738
     739После додавање на индексите се гледа дека планерот повеќе не користи `Seq Scan` за `reschedule_request`, туку користи:
     740
     741{{{
     742Bitmap Index Scan on idx_reschedule_request_status
     743Index Cond: ((status)::text = 'pending'::text)
     744}}}
     745
     746и потоа:
     747
     748{{{
     749Bitmap Heap Scan on reschedule_request rr
     750Recheck Cond: ((status)::text = 'pending'::text)
     751}}}
     752
     753Ова значи дека базата преку индексот прво ги наоѓа барањата кои имаат статус `pending`, а потоа ги чита само тие редици од табелата.
     754
     755Во овој конкретен тест подобрувањето не е многу големо затоа што табелата `reschedule_request` има релативно мал број редици. Сепак, индексот е корисен затоа што во реална апликација бројот на барања може да расте, а пребарувањето по статус би било често сценарио.
     756
     757=== Споредба на времиња ===
     758
     759||= Поглед =||= Пред индекси =||= После индекси =||= Подобрување =||
     760|| reschedule_overview || 5.051 ms || 3.988 ms || околу 1.27 пати побрзо ||
     761
     762Со додавање на индексите, времето на извршување се намали од **5.051 ms** на **3.988 ms**. Најважниот индекс во овој случај е `idx_reschedule_request_status`, бидејќи директно се користи за условот `status = 'pending'`.
     763
     764Индексите на `old_slot_id` и `new_slot_id` се потенцијално корисни за спојувања со табелата `time_slot`, иако во конкретниот план најмногу се гледа користењето на индексот за `status`.
     765
     766== 6. Заклучок ==
     767
     768Со додавање на соодветни индекси успеавме да ги подобриме перформансите на тестираните прашалници во системот Rezervo.
    243769
    244770Најголеми подобрувања се забележуваат кај:
    245771
    246 ||= Поглед =||= Пред оптимизација =||= После оптимизација =||
    247 || customer_appointments_view || 644.544 ms || 2.756 ms ||
    248 || available_slots || 429.479 ms || 1.090 ms ||
    249 || review_details || 800.553 ms || 340.962 ms ||
    250 || customer_profile_view || 708.985 ms || 0.767 ms ||
    251 
    252 Најголемо забрзување има кај `customer_profile_view`, каде што времето се намали од **708.985 ms** на **0.767 ms**, како и кај `available_slots`, каде што пребарувањето на достапни термини се намали од **429.479 ms** на **1.090 ms**.
    253 
    254 Заклучуваме дека индексите се оправдани бидејќи ги оптимизираат најчестите кориснички сценарија во апликацијата:
    255 
    256 * преглед на резервации за корисник
    257 * пребарување на достапни термини
    258 * преглед и филтрирање на рецензии
    259 * приказ на кориснички профил
    260 
    261 Иако индексите можат да имаат мал трошок при внесување и ажурирање на податоци, во овој систем читањето и пребарувањето се многу почести операции, па добивката во перформанси е значително поголема.
     772||= Поглед =||= Пред индекс =||= После индекс =||= Подобрување =||
     773|| customer_appointments_view || 7.802 ms || 0.803 ms || околу 9.7 пати побрзо ||
     774|| available_slots || 7157.628 ms || 0.580 ms || над 12000 пати побрзо ||
     775|| review_details || 360.970 ms || 239.802 ms || околу 1.5 пати побрзо ||
     776|| customer_profile_view || 546.242 ms || 123.200 ms || околу 4.4 пати побрзо ||
     777|| reschedule_overview || 5.051 ms || 3.988 ms || околу 1.27 пати побрзо ||
     778
     779Најголемо подобрување има кај `available_slots`, бидејќи пред додавање на индексот базата правеше `Parallel Seq Scan` врз табелата `time_slot` и отстрануваше над 6 милиони редици преку филтер. После додавање на парцијалниот индекс, базата директно ги наоѓа достапните термини за конкретен бизнис и датум.
     780
     781Кај `customer_appointments_view` подобрувањето е исто така значајно, бидејќи парцијалниот индекс ги содржи само резервациите кои не се откажани. Со тоа се избегнува дополнително филтрирање според статус.
     782
     783Кај `customer_profile_view` подобрувањето е значително, бидејќи индексот `idx_review_customer_profile` го заменува секвенцијалното скенирање на табелата `review` со директно индексно пребарување според `customer_id`. Сепак, бидејќи табелата `appointment` и понатаму се скенира секвенцијално, прашалникот не се намалува под 1 ms.
     784
     785Кај `review_details` подобрувањето е помало. Причината е тоа што условот `rating = 5` враќа многу голем број редици. Индексот го заменува целосното секвенцијално скенирање со `Bitmap Index Scan`, но бидејќи сепак мора да се прочитаат околу 1 милион рецензии, разликата не е толку голема.
     786
     787Кај `reschedule_overview` подобрувањето е најмало, бидејќи табелата `reschedule_request` во тестот има релативно мал број редици. Индексот `idx_reschedule_request_status` се користи во планот, но разликата меѓу времето пред и после индексот е мала.
     788
     789== 7. Влијание на индексите и можност за бришење на некои индекси ==
     790
     791Индексите го подобруваат читањето на податоците, но имаат и цена. При секој `INSERT`, `UPDATE` или `DELETE`, базата мора да ги ажурира и индексите. Затоа не е секогаш оправдано да се задржи индекс кој дава многу мало подобрување.
     792
     793Во нашите тестови, индексите кои даваат најголема добивка и се оправдани за задржување се:
     794
     795{{{
     796idx_appointment_customer_not_cancelled
     797idx_time_slot_available_business_date
     798idx_review_customer_profile
     799}}}
     800
     801Овие индекси значително го намалуваат времето на извршување и директно одговараат на чести сценарија во апликацијата: преглед на резервации, пребарување достапни термини и приказ на кориснички профил.
     802
     803Од друга страна, кај следните индекси добивката е помала:
     804
     805{{{
     806idx_review_rating_details
     807idx_reschedule_request_status
     808idx_reschedule_request_old_slot_id
     809idx_reschedule_request_new_slot_id
     810}}}
     811
     812Индексот `idx_review_rating_details` го намалува времето од **360.970 ms** на **239.802 ms**, но бидејќи `rating = 5` враќа многу голем број редици, подобрувањето е ограничено. Доколку филтрирањето по оценка не е често сценарио во апликацијата, може да се размисли овој индекс да се избрише.
     813
     814Индексот `idx_reschedule_request_status` го намалува времето од **5.051 ms** на **3.988 ms**, што е мала разлика. Дополнително, индексите `idx_reschedule_request_old_slot_id` и `idx_reschedule_request_new_slot_id` не се директно искористени во прикажаниот план на извршување. Затоа, ако овие индекси не се користат во други прашалници, може да се размисли за нивно бришење.
     815
     816Бришењето би се направило со:
     817
     818{{{
     819DROP INDEX IF EXISTS idx_review_rating_details;
     820
     821DROP INDEX IF EXISTS idx_reschedule_request_status;
     822DROP INDEX IF EXISTS idx_reschedule_request_old_slot_id;
     823DROP INDEX IF EXISTS idx_reschedule_request_new_slot_id;
     824}}}
     825
     826Заклучуваме дека индексите треба да се задржат само ако носат значајно подобрување или ако се користат често во апликацијата. Во спротивно, подобро е да се избришат за да не создаваат непотребен трошок при внесување и ажурирање на податоците.
     827
     828Конечно, најоправдани индекси за задржување во оваа фаза се парцијалните индекси за `customer_appointments_view` и `available_slots`, како и индексот за `customer_profile_view`, бидејќи таму добивката е најголема и сценаријата се чести во реалната употреба на системот.