Евалуација

Примери на индекси

Индекс врз колона што често се филтрира: s_date:

-- За филтрирање по датум
CREATE INDEX idx_policy_sdate ON project.policy(s_date);

-- За пребарување според email
CREATE INDEX idx_customer_email ON project.customer(email);

Во кодот, не морашаме да пишуваме USE INDEX (како во MySQL), бидејќи PostgreSQL автоматски ќе го користи индексот ако query planner процени дека е корисен.

WHERE p.s_date BETWEEN '{0}' AND '{1}' {2}
➡️ Овој дел директно ќе го искористи idx_policy_sdate

А ако во dropdown_params ставаме филтер:

AND c.email = 'test@example.com'


➡️ Овој дел ќе го искористи idx_customer_email.

Final:

string query = @"
    SELECT p.p_id, 
           CASE 
               WHEN v.pol_id IS NOT NULL THEN 'Auto Policy'
               WHEN t.pol_id IS NOT NULL THEN 'Travel Health'
               ELSE 'Property Policy'
           END AS PolicyType,
           c.name AS CustomerName,
           p.s_date AS StartDate,
           p.e_date AS EndDate,
           p.package AS PackageCode,
           pkg.title AS PackageTitle,
           pkg.total AS PackageTotal
    FROM project.policy p
    LEFT JOIN project.Auto_pol v ON p.p_id = v.pol_id
    LEFT JOIN project.Travel_pol t ON p.p_id = t.pol_id
    LEFT JOIN project.property_pol pp ON p.p_id = pp.pr_id 
    LEFT JOIN project.pol_dog pd ON p.p_id = pd.policy
    LEFT JOIN project.customer c ON pd.c_id = c.c_id
    LEFT JOIN project.package pkg ON p.package = pkg.code
    WHERE p.sdate BETWEEN @datef AND @datem
      AND (@dropdown IS NULL OR c.email = @dropdown);";

using var cmd = new NpgsqlCommand(query, sqlcon);
cmd.Parameters.AddWithValue("datef", datef);
cmd.Parameters.AddWithValue("datem", datem);
cmd.Parameters.AddWithValue("dropdown", (object)dropdown ?? DBNull.Value);

Имаме создаден индекс врз policy.sdate и customer.email.

Го користиме тој индекс во реален query (бидејќи филтрираме по тие полиња).

Што е важно за индекси (TL;DR):

Примарните клучеви → автоматски се индексирани

Надворешните клучеви → создај индекс за подобар JOIN

Филтрирање по колони (WHERE) → индекс помага

Сортирање и групирање (ORDER BY, GROUP BY) → индекс помага

-- Индекси за JOIN перформанси (врз странски клучеви и ID полиња)

CREATE INDEX IF NOT EXISTS idx_policy_d_embg ON project_2425.policy(d_embg);

CREATE INDEX IF NOT EXISTS idx_pol_dog_c_id ON project_2425.pol_dog(c_id);

CREATE INDEX IF NOT EXISTS idx_payment_policy_id ON project_2425.payment(policy_id);

CREATE INDEX IF NOT EXISTS idx_auto_pol_pol_id ON project_2425.auto_pol(pol_id);
CREATE INDEX IF NOT EXISTS idx_auto_pol_v_id ON project_2425.auto_pol(v_id);

CREATE INDEX IF NOT EXISTS idx_property_pol_pol_id ON project_2425.property_pol(pol_id);
CREATE INDEX IF NOT EXISTS idx_property_pol_prop_id ON project_2425.property_pol(prop_id);

CREATE INDEX IF NOT EXISTS idx_travel_pol_pol_id ON project_2425.travel_pol(pol_id);
CREATE INDEX IF NOT EXISTS idx_travel_pol_o_embg ON project_2425.travel_pol(o_embg);

CREATE INDEX IF NOT EXISTS idx_covers_package_code ON project_2425.covers(package_code);

-- Индекси за WHERE филтри (датуми, email, суми, итн.)

CREATE INDEX IF NOT EXISTS idx_policy_e_date ON project_2425.policy(e_date);

CREATE INDEX IF NOT EXISTS idx_customer_email ON project_2425.customer(email);
CREATE INDEX IF NOT EXISTS idx_customer_type ON project_2425.customer(type);

CREATE INDEX IF NOT EXISTS idx_payment_date ON project_2425.payment(p_date);
CREATE INDEX IF NOT EXISTS idx_payment_amount ON project_2425.payment(p_amount);

CREATE INDEX IF NOT EXISTS idx_package_type_pol ON project_2425.package(type_pol);

-- Индекси за уникатни вредности

CREATE UNIQUE INDEX IF NOT EXISTS idx_vehicle_license_plate_unique ON project_2425.vehicle(license_plate);

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

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

Сега ке направиме мало тестирање со опцијата во прилог, доколку Dbeaver користи Seq Scan (Sequence Scan), значи дека треба подобар индекс. EXPLAIN ANALYZE SELECT * FROM project_2425."policy" WHERE s_date BETWEEN '2023-01-01' AND '2025-12-31';

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

Примери за тригери

1. Лог за бришење на клиенти (Customer)

Ако се избрише клиент, ќе се внесе запис во посебна лог табела.

-- Табела за логирање
CREATE TABLE project_2425.Customer_log (
  log_id BIGSERIAL PRIMARY KEY,
  c_id BIGINT,
  name VARCHAR(100),
  email VARCHAR(255),
  deleted_at TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION log_customer_delete()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO project_2425.Customer_log(c_id, name, email)
  VALUES (OLD.c_id, OLD.name, OLD.email);
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_log_customer_delete
AFTER DELETE ON project_2425.Customer
FOR EACH ROW
EXECUTE FUNCTION log_customer_delete();

2. Да спречи внесување на плаќање (Payment) кое е поголемо од осигурената вредност

   CREATE OR REPLACE FUNCTION project.validate_payment_amount()
   RETURNS TRIGGER AS $$
   DECLARE
       max_amount NUMERIC;
   BEGIN
       -- земи ја максималната дозволена вредност од пакетот
       SELECT pkg.total INTO max_amount
       FROM project.policy pol
       JOIN project.package pkg ON pol.package = pkg.code
       WHERE pol.p_id = NEW.policy;
   
       -- ако не постои поврзана полиса или пакет, кревај грешка
       IF max_amount IS NULL THEN
           RAISE EXCEPTION 'Cannot validate payment. No associated policy/package found for policy ID: %', NEW.policy;
       END IF;
   
       -- ако износот на плаќање е поголем од дозволениот, кревај грешка
       IF NEW.p_amount > max_amount THEN
           RAISE EXCEPTION 'Payment amount (%.2f) exceeds maximum allowed (%.2f) for policy %', NEW.p_amount, max_amount, NEW.policy;
       END IF;
   
       RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;
   
   CREATE TRIGGER trigger_validate_payment_amount
   BEFORE INSERT OR UPDATE ON project.payment
   FOR EACH ROW
   EXECUTE FUNCTION project.validate_payment_amount();
   
   

3. Проверка дали возило е веќе осигурано (Auto_pol)

Не дозволува едно возило да има повеќе активни полиси во истиот период.

CREATE OR REPLACE FUNCTION check_vehicle_policy()
RETURNS TRIGGER AS $$
DECLARE
  active_count INT;
BEGIN
  SELECT COUNT(*) INTO active_count
  FROM project_2425.Auto_pol ap
  JOIN project_2425.Policy pol ON pol.p_id = ap.pol_id
  WHERE ap.v_id = NEW.v_id
    AND pol.s_date <= CURRENT_DATE
    AND pol.e_date >= CURRENT_DATE;

  IF active_count > 0 THEN
    RAISE EXCEPTION 'Возилото веќе има активна полиса';
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_vehicle_policy
BEFORE INSERT ON project_2425.Auto_pol
FOR EACH ROW
EXECUTE FUNCTION check_vehicle_policy();

Погледи

--View
-- project.packageview source

CREATE OR REPLACE VIEW project.packageview
AS SELECT c.cov_amount,
    c.cov_type,
    p.title AS packagetitle,
    p.total AS packagetotal,
    p.valuet AS packagevalue,
        CASE
            WHEN p.type_pol = 3 THEN 'Auto Policy'::text
            WHEN p.type_pol = 1 THEN 'Travel Health'::text
            ELSE 'Property Policy'::text
        END AS policytype
   FROM project.covers c
     JOIN project.package p ON c.package::integer = p.code;
    
    

Трансакции

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

Како што може да се види, имаме :

-> Почеток на трансакција со BEGIN

-> Потврда на трансакцијата со COMMIT

-> Доколку дојде до грешка, го скокнува COMMIT и доаѓа до CATCH

-> После CATCH доаѓа ROLLBACK односно враќање назад

Прашалници со релациска алгебра

Неделен извештај - број на полиси и приход по ден од неделата (последни 7 дена)

  σ_s_date ≥ (CURRENT_DATE - 7) (Policy) 
  ⨝ Policy.policy_id = Payment.policy_id Payment
)

Топ клиенти по вкупна потрошувачка

  Customer
  ⨝ Customer.customer_id = Pol_dog.c_id
  ⨝ Pol_dog.d_embg = Policy.d_embg
  ⨝ Policy.policy_id = Payment.policy_id Payment
)
τ_↓SUM(p_amount) (
  π_name, SUM(p_amount) (...)
)

Најпопуларни пакети (број полиси по пакет)

  Package ⨝ Package.package_code = Policy.package_code Policy
)
τ_↓COUNT(policy_id) (...)

Пример - Предвидување на пакети со најмногу продадени покритија (covers)

  Package ⨝ Package.package_code = Covers.package_code ⨝ Covers.package_code = Policy.package_code Policy
)
τ_↓COUNT(policy_id) (...)