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

1. Анализа на поглед 1: Активен каталог на производи (v_active_product_catalog)

1. Примарен филтер: Примарниот филтер за овој поглед е според variant_id на производот (варијантата).
2. Случај на употреба: Се користи за брзо пребарување на производи и приказ на нивните варијанти во реално време на почетната продавница (Catalog Listing). Перформансите се критични бидејќи ова е најчесто повикуваниот поглед од корисниците.

Тест прашалникот кој се извршува е следниот:

SET search_path TO myschema;
SELECT * FROM v_active_product_catalog WHERE variant_id = 234;

А. Состојба ПРЕД индексирање

• Време на извршување на прашалникот (Без индекси): 1 s 194 ms (Execution: 445 ms, Fetching: 749 ms)

• Најбавни операции (Bottleneck Analysis): Од визуелниот план во DataGrip, идентификувано е дека базата троши најмногу време на:
  • Access Subquery Scan — поради потребата да се пресмета агрегацијата SUM(ii.quantity) од табелата inventory_items без соодветна поддршка за брзо филтрирање во подпрашалникот.
  • Unknown (Incremental Sort) — етапно сортирање во RAM меморијата за да се подредат податоците за потребите на GROUP BY клаузулата.

• Време на операциите INSERT и UPDATE (Без индекси): Со цел да се измери иницијалната брзина на запишување пред модификациите, извршени се следните трансакции:
• INSERT: 12 ms (Вкупно со fetching: 387 ms)

  BEGIN;
  EXPLAIN (ANALYZE, BUFFERS)
  INSERT INTO inventory_items (variant_id, warehouse_id, quantity) VALUES (12, 50, 152);
   ROLLBACK;
  

• UPDATE: 13 ms (Вкупно со fetching: 393 ms)

  BEGIN;
  EXPLAIN (ANALYZE, BUFFERS)
  UPDATE inventory_items SET quantity = 19 WHERE variant_id = 504;
  ROLLBACK;
  

Б. Воведување на индекси

За елиминирање на скенирањата на цели подпрашалници и мемориското сортирање, воведени се следните 1-2 карактеристични индекси на надворешните клучеви:

CREATE INDEX idx_v1_products_category_id ON products(category_id, product_id);
CREATE INDEX idx_v1_inventory_items_variant_id ON inventory_items(variant_id);
ANALYZE products;
ANALYZE inventory_items;

В. Состојба ПОСЛЕ индексирање

• Ново време на извршување на прашалникот (Со индекси): [18ms] *(Времето е драстично намалено во рамките на прифатливото оперативни време под 2 секунди).*

• Најбавни операции сега: Скапите операции Access Subquery Scan и меморискиот Incremental Sort целосно исчезнуваат. Базата сега ги извршува операциите преку директен, инстантен Index Scan користејќи ја однапред подредената B-Tree структура.

• Ново време на операциите INSERT и UPDATE (Со индекси):
  • INSERT: 26 ms
  • UPDATE: 38 ms

Заклучок за Write-Impact: Иако времето за запишување се зголеми (од 12ms на 26ms за внесување и од 13ms на 38ms за ажурирање) поради потребата од дополнително запишување во самите индексни табели, ова зголемување е во рамки на милисекунди и е целосно прифатливо. Придобивката од забрзувањето на главниот SELECT прашалник го оправдува овој минимален трошок.