Евалуација

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

Индекс врз колона што често се филтрира: 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. Проверка на датумска логика

   CREATE OR REPLACE FUNCTION project.validate_policy_dates()
   RETURNS TRIGGER AS $$
   BEGIN
       IF NEW.e_date <= NEW.s_date THEN
           RAISE EXCEPTION 'End date must be after start date. Start: %, End: %', NEW.s_date, NEW.e_date;
       END IF;
       RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;
   
   CREATE TRIGGER trigger_validate_policy_dates
   BEFORE INSERT OR UPDATE ON project.policy
   FOR EACH ROW EXECUTE FUNCTION project.validate_policy_dates();
   
   

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. Формат на табели за колата

        CREATE OR REPLACE FUNCTION validate_license_plate() 
        RETURNS TRIGGER AS $$
        BEGIN
            -- Check if the license plate matches the expected format (XX-1234-XX)
            IF NEW.license_plate !~ '^[A-Z]{2}-\d{4}-[A-Z]{2}$' THEN
                RAISE EXCEPTION 'Invalid license plate format: %', NEW.license_plate;
            END IF;
            RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;


CREATE TRIGGER trigger_validate_license_plate
BEFORE INSERT OR UPDATE ON vehicles
FOR EACH ROW
EXECUTE FUNCTION validate_license_plate_format();

Погледи

--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;
    
    

Трансакции

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