Changes between Initial Version and Version 1 of Indexes

Timestamp:

08/18/25 20:43:47 (2 months ago)

Author:

221071

Comment:

--

Indexes

@@ +1 @@
+= Индекси за оптимизација на напредни извештаи 
+
+
+== Анализа на извештаи и потребни индекси == #анализа
+
+=== 1. Индекси за извештај за процент на изнајмени недвижности во однос на објавени огласи за секоја година.=== #извештај1
+
+Овој извештај користи и извршува операции со:
+ * `EXTRACT(YEAR FROM available_from)` на Listing табела
+ * `EXTRACT(YEAR FROM l.start_date)` на Lease табела  
+ * JOIN помеѓу Lease и Listing преку listing_id
+
+'''Потребни индекси:'''
+
+{{{#!sql
+-- За брзо филтрирање по година на објавување
+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. Извештај за раст на приходи по град во однос на претходната година  === #извештај2
+
+Овој извештај е особено сложен бидејќи користи повеќе JOIN операции и агрегатни функции со временски филтрирања.
+
+'''Критични операции:'''
+ * JOIN низа: Payment → Lease → Listing → Unit → Property → Address
+ * WHERE услов: `p.status = 'завршено'`
+ * GROUP BY со `EXTRACT(YEAR FROM p.payment_date)`
+
+'''Потребни индекси:'''
+
+{{{#!sql
+-- За брзо филтрирање на успешни плаќања
+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. Детален месечен извештај за издавачите  === #извештај3
+
+Овој извештај е најсложен и користи повеќе CTE изрази со различни временски периоди.
+
+'''Клучни операции:'''
+ * Филтрирање по landlord_id
+ * Временски операции со start_date и end_date
+ * Проверка на статус на плаќања
+
+'''Потребни индекси:'''
+
+{{{#!sql
+-- За брза идентификација на изадавачите
+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. Извештај за платежна историја на станари  === #извештај4
+
+Фокусиран на tenant_id и различни статуси на плаќања.
+
+'''Потребни индекси:'''
+
+{{{#!sql
+-- За идентификација на станари
+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. Извештај за сервисни барања по општина  === #извештај5
+
+Анализира сервисни барања со географска и категориска сегментација.
+
+'''Потребни индекси:'''
+
+{{{#!sql
+-- За категоризација на сервисни барања
+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. Анализа на пазарни трендови === #извештај6
+
+Анализира понуда и побарувачка по регион и тип на недвижност.
+
+'''Потребни индекси:'''
+
+{{{#!sql
+-- За анализа по тип на недвижност
+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:
+
+{{{#!sql
+-- Пример за тестирање на Извештај 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''' треба да биде значително намален
+
+
+
+