Други теми

Скрипти: other_topics.sql​ (индекси, оптимизации и безбедносни мерки) и perf_test_data.sql​ (тест-податоци за анализа на перформансите). Редослед на извршување: schema_creation.sql, advanced_db.sql, other_topics.sql, data_load.sql.

Перформанси на SQL

Методологија

Со примерните податоци (72 пријави) секоја табела зафаќа неколку страници, па PostgreSQL секогаш ја чита целата табела и индексите не се користат. Затоа анализата е направена во посебна шема perf, креирана со perf_test_data.sql, со иста структура, примарни клучеви и единствени ограничувања како project, и со синтетички податоци за период 2021-2026:

• 100 000 пријави (околу 4 % отворени), 385 453 записи во историјата на статуси, 99 042 доделувања, 49 513 коментари, 100 000 фотографии, 5 000 граѓани и 40 работници.

Секој прашалник е извршен со EXPLAIN (ANALYZE, BUFFERS) три пати, пред и по креирањето на индексите, и е земено најкраткото време. Паралелното извршување е исклучено (max_parallel_workers_per_gather = 0), за плановите да бидат споредливи. Во плановите подолу се прикажани главните чворови; бројките се на PostgreSQL 16.

Предложени индекси

-- историјата на пријава и последниот статус (тригери, UC0004, погледи, извештаи)
CREATE INDEX idx_status_logs_report_changed ON status_logs (report_id, changed_at, log_id);

-- активните пријави на работник (UC0005) и оптовареност (UC0007)
CREATE INDEX idx_assignments_worker ON assignments (worker_id, report_id);

-- пријавите на граѓанин (UC0004)
CREATE INDEX idx_reports_citizen ON reports (citizen_id, created_at);

-- извештаи за даден период
CREATE INDEX idx_reports_created ON reports (created_at);

-- слични активни пријави во близина; делумен индекс само за отворените пријави
CREATE INDEX idx_reports_active_location ON reports (category_id, latitude, longitude)
    WHERE status NOT IN ('resolved', 'rejected');

-- коментари и фотографии на пријава (UC0004)
CREATE INDEX idx_comments_report ON comments (report_id);
CREATE INDEX idx_photos_report   ON photos (report_id);

Надворешните клучеви во PostgreSQL не креираат индекси автоматски, па колоните report_id и worker_id во табелите-деца немаа индекси. Делумниот