Индекси

Анализа на извештаи и потребни индекси

1. Индекси за извештај за процент на изнајмени недвижности во однос на објавени огласи за секоја година.

Овој извештај користи и извршува операции со:

• EXTRACT(YEAR FROM available_from) на Listing табела
• EXTRACT(YEAR FROM l.start_date) на Lease табела
• JOIN помеѓу Lease и Listing преку listing_id

Потребни индекси:

-- За брзо филтрирање по година на објавување
CREATE INDEX IF NOT EXISTS idx_listing_available_from_year ON Listing(EXTRACT(YEAR FROM available_from));

-- За брзо филтрирање по година на започнување на изнајмувањето
CREATE INDEX IF NOT EXISTS idx_lease_start_date_year ON Lease(EXTRACT(YEAR FROM start_date));

-- За оптимизација на JOIN операциите
CREATE INDEX IF NOT EXISTS idx_lease_listing_id ON Lease(listing_id);

-- Композитен индекс за подобра оптимизација на временски операции
CREATE INDEX IF NOT EXISTS idx_listing_available_from ON Listing(available_from);
CREATE INDEX IF NOT EXISTS idx_lease_start_date ON Lease(start_date);

2. Извештај за раст на приходи по град во однос на претходната година

Овој извештај користи повеќе JOIN операции и агрегатни функции со временски филтрирања.

Критични операции:

• JOIN низа: Payment → Lease → Listing → Unit → Property → Address
• WHERE услов: p.status = 'завршено'
• GROUP BY со EXTRACT(YEAR FROM p.payment_date)

Потребни индекси:

-- За брзо филтрирање на успешни плаќања
CREATE INDEX IF NOT EXISTS idx_payment_status ON Payment(status);

-- За временски анализи на плаќања
CREATE INDEX IF NOT EXISTS idx_payment_date_year ON Payment(EXTRACT(YEAR FROM payment_date));
CREATE INDEX IF NOT EXISTS idx_payment_date ON Payment(payment_date);

-- За оптимизација на JOIN операциите
CREATE INDEX IF NOT EXISTS idx_payment_lease_id ON Payment(lease_id);
CREATE INDEX IF NOT EXISTS idx_listing_unit_id ON Listing(unit_id);
CREATE INDEX IF NOT EXISTS idx_unit_property_id ON Unit(property_id);
CREATE INDEX IF NOT EXISTS idx_property_address_id ON Property(address_id);

-- За групирање по град
CREATE INDEX IF NOT EXISTS idx_address_city ON Address(city);

-- Композитен индекс за плаќања со статус и датум
CREATE INDEX IF NOT EXISTS idx_payment_status_date ON Payment(status, payment_date);

3. Детален месечен извештај за издавачите

Овој извештај користи повеќе CTE изрази со различни временски периоди.

Клучни операции:

• Филтрирање по landlord_id
• Временски операции со start_date и end_date
• Проверка на статус на плаќања

Потребни индекси:

-- За брза идентификација на изадавачите
CREATE INDEX IF NOT EXISTS idx_lease_landlord_id ON Lease(landlord_id);

-- За временски филтрирања на изнајмувања
CREATE INDEX IF NOT EXISTS idx_lease_start_date ON Lease(start_date);
CREATE INDEX IF NOT EXISTS idx_lease_end_date ON Lease(end_date);

-- Композитен индекс за временски период на изнајмувањето
CREATE INDEX IF NOT EXISTS idx_lease_dates ON Lease(start_date, end_date);

-- За филтрирање активни изнајмувања
CREATE INDEX IF NOT EXISTS idx_lease_landlord_dates ON Lease(landlord_id, start_date, end_date);

-- За сервисни барања
CREATE INDEX IF NOT EXISTS idx_service_request_status ON ServiceRequest(status);
CREATE INDEX IF NOT EXISTS idx_service_request_lease_id ON ServiceRequest(lease_id);

-- За UserD табела (landlord профили)
CREATE INDEX IF NOT EXISTS idx_userd_id ON UserD(id);

4. Извештај за платежна историја на станари

Овој извештај е фокусиран на tenant_id и различни статуси на плаќања.

Потребни индекси:

-- За идентификација на станари
CREATE INDEX IF NOT EXISTS idx_lease_tenant_id ON Lease(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_profile_id ON TenantProfile(id);

-- За анализа на плаќања по статус
CREATE INDEX IF NOT EXISTS idx_payment_status ON Payment(status);
CREATE INDEX IF NOT EXISTS idx_payment_lease_status ON Payment(lease_id, status);

-- За пресметка на просечна кирија
CREATE INDEX IF NOT EXISTS idx_lease_rent_amount ON Lease(rent_amount);

-- За временска анализа на изнајмувања
CREATE INDEX IF NOT EXISTS idx_lease_tenant_dates ON Lease(tenant_id, start_date, end_date);

5. Извештај за сервисни барања по општина

Овој извештај анализира сервисни барања со географска и категориска сегментација.

Потребни индекси:

-- За категоризација на сервисни барања
CREATE INDEX IF NOT EXISTS idx_service_request_category ON ServiceRequest(service_category_id);

-- За временска анализа на барања
CREATE INDEX IF NOT EXISTS idx_service_request_date ON ServiceRequest(request_date);

-- За анализа на одржување
CREATE INDEX IF NOT EXISTS idx_maintenance_log_service_request ON MaintenanceLog(service_request_id);
CREATE INDEX IF NOT EXISTS idx_maintenance_log_date ON MaintenanceLog(maintenance_date);

-- За географска анализа
CREATE INDEX IF NOT EXISTS idx_address_municipality ON Address(municipality);
CREATE INDEX IF NOT EXISTS idx_address_municipality_city ON Address(municipality, city);

-- Композитен индекс за временска анализа на сервиси
CREATE INDEX IF NOT EXISTS idx_service_request_status_date ON ServiceRequest(status, request_date);

6. Анализа на пазарни трендови

Оовој извептај анализира понуда и побарувачка по регион и тип на недвижност.

Потребни индекси:

-- За анализа по тип на недвижност
CREATE INDEX IF NOT EXISTS idx_property_type_id ON Property(property_type_id);

-- За статус на огласи
CREATE INDEX IF NOT EXISTS idx_listing_status ON Listing(status);

-- За пресметка на цена по квадратен метар
CREATE INDEX IF NOT EXISTS idx_unit_rent_area ON Unit(rent_amount, area_sq_m);

-- За заинтересираност
CREATE INDEX IF NOT EXISTS idx_interested_listing_id ON Interested(listing_id);
CREATE INDEX IF NOT EXISTS idx_interested_tenant_profile ON Interested(tenant_profile_id);

-- Композитен индекс за пазарна анализа
CREATE INDEX IF NOT EXISTS idx_listing_unit_status ON Listing(unit_id, status);

Тестирање на индексите со EXPLAIN ANALYZE

За да се провери ефикасноста на индексите, можеме да користиме EXPLAIN ANALYZE:

-- Пример за тестирање на Извештај 4
EXPLAIN ANALYZE
WITH TenantPaymentStats AS (
    SELECT 
        t.id as tenant_id,
        CONCAT(u.first_name, ' ', u.last_name) as tenant_name,
        u.rating as tenant_rating,
        COUNT(DISTINCT le.id) as total_leases,
        COUNT(DISTINCT pay.id) as total_payments,
        SUM(CASE WHEN pay.status = 'завршено' THEN pay.amount ELSE 0 END) as total_paid,
        SUM(CASE WHEN pay.status = 'доцнење' THEN pay.amount ELSE 0 END) as late_payments_amount,
        COUNT(CASE WHEN pay.status = 'доцнење' THEN 1 END) as late_payment_count,
        COUNT(CASE WHEN pay.status = 'во тек' THEN 1 END) as pending_payment_count,
        AVG(le.rent_amount) as avg_rent_amount,
        MIN(le.start_date) as first_lease_date,
        MAX(le.end_date) as latest_lease_end
    FROM TenantProfile t
    JOIN UserD u ON t.id = u.id
    LEFT JOIN Lease le ON t.id = le.tenant_id
    LEFT JOIN Payment pay ON le.id = pay.lease_id
    GROUP BY t.id, u.first_name, u.last_name, u.rating
)
SELECT 
    tenant_id,
    tenant_name,
    tenant_rating,
    total_leases,
    total_payments,
    total_paid,
    late_payments_amount,
    late_payment_count,
    pending_payment_count,
    ROUND((late_payment_count * 100.0 / NULLIF(total_payments, 0)), 2) as late_payment_percentage,
    avg_rent_amount,
    first_lease_date,
    latest_lease_end,
    CASE 
        WHEN late_payment_count = 0 THEN 'Одличен'
        WHEN late_payment_count <= 2 THEN 'Добар' 
        WHEN late_payment_count <= 5 THEN 'Среден'
        ELSE 'Ризичен'
    END as payment_reliability_score
FROM TenantPaymentStats
WHERE total_leases > 0
ORDER BY late_payment_percentage ASC, total_paid DESC;

Што да бараме во резултатот:

• Index Scan наместо Seq Scan - значи дека индексот се користи
• Nested Loop со Index Scan на внатрешната страна на JOIN
• Execution time треба да биде значително намален

Споредба на извршување на извештај 4 со и без индекси

Без индекси

Со индекси

• Може да забележиме дека времето на извршување е намалено
• Исто така може да се види дека повтроно се користи и Seq Scan, иако имаме индекси, тоа е највероајно поради големината на базата или пак од потреба од целата табела