Индекси

Индексите ги забрзуваат операциите за пребарување и филтрирање, што значително го намалува времето на извршување на SQL барања. Индексирањето има смисла ако ќе ја подобри брзината на наредбите SELECT, JOIN, WHERE, ORDER BY и GROUP BY. Направивме измени во db.js така што Client го заменивме за Pool
бидејки client секогаш отвара нова конекција а со pool користи збир конекции за подобра оптимизација.
Кога зборуваме за поставување на барања до база, на старата верзија тоа беше веројатно многу по лимитирано,
а сега имаме овозможено повеќе истовремени конекции т.е. max: 20.
Во нашиот код рачно се отвора и затвора конекција, додека новиот код користи getClient() за подобра контрола над конекциите. Освен тоа при пад на конекција потребно е restart а сега имаме момент каде pool.on("error") ја фаќа грешката. Да не заборавиме и на Timeout моментот, сега имаме поставено idleTimeoutMillis и connectionTimeoutMillis за подобро управување.Употребуваме и try-catch блок за да регистрира евентуални грешки при извршување на CREATE INDEX.

module.exports = {
  query: (text, params) => pool.query(text, params),
  getClient: async () => {
    const client = await pool.connect();
    return client;
  },
};

Што е важно за индекси?

-> Примарни клучеви да се индексирани автоматски
-> Најчесто пребарувани колони (особено во WHERE, JOIN, ORDER BY или GROUP BY) треба да имаат индекси
-> Надворешните клучеви треба да имаат индекси за побрзи JOIN операции
-> Колони што се често филтрирани или сортирани треба да имаат индекси

Индекси за подобрување на JOIN перформанси

CREATE INDEX idx_customers_email ON customers(email);
CREATE INDEX idx_orders_customer_id ON orders(id_customer);
CREATE INDEX idx_delivery_order_id ON delivery(id_order);
CREATE INDEX idx_products_category_id ON products(id_category);
CREATE INDEX idx_stock_product_id ON stock(id_product);
CREATE INDEX idx_contains_order_id ON contains(id_order);
CREATE INDEX idx_contains_stock_id ON contains(id_stock);

Индекси за оптимизирање на пребарувања со WHERE

CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_stock_price ON stock(price);
CREATE INDEX idx_products_name ON products(product_name);

Тестирање на CREATE INDEX наредби


После направените измени, кодот додава индекси автоматски ако не постојат со опцијата
CREATE INDEX IF NOT EXISTS и ги подобривме перформансите на JOIN, WHERE, ORDER BY со индексирање притоа сега се избегнуваат грешки ако индексите веќе постојат.

->

Додатни чекори за оптимизација на индекси со EXPLAIN ANALYZE

Сега ке направиме мало тестирање со опцијата во прилог,
доколку Dbeaver користи Seq Scan (Sequence Scan), значи дека треба подобар индекс.

EXPLAIN ANALYZE SELECT * FROM orders WHERE id_customer = 5;

Бидејки на ова барање добивме одговор со Seq Scan наместо со Index Scan,
ова во наш случај не би требало да е проблем бидејки базата нема премногу податоци и табелите се мали.
Алтернативно пробано е и со опција ANALYZE my_table;, па од прилог може да се провери во база дека индексот постои, но веројатно е поради мала табела.