Changes between Version 8 and Version 9 of Indexing

Timestamp:

08/27/25 01:59:11 (7 days ago)

Author:

221007

Comment:

--

Indexing

@@ -95 +95 @@
 CREATE INDEX IF NOT EXISTS ix_ar_institution_id ON academicreport(institution_id);
 }}}
+
+=== 4. Пронаоѓање на слични дијагнози со селектиран пациент
+ Query кое се извршува со секое вклучување на панелот на пациентот:
+{{{
+ EXPLAIN (ANALYZE, BUFFERS)
+ with selected_person_diagnosis as(
+    select distinct d.diagnosis_id as diagnosis_id, d.short_description as label
+    from person p
+             join report r on r.person_id = p.person_id
+             join medicalreport_diagnosis mrd on mrd.report_id = r.report_id
+             join diagnosis d on mrd.diagnosis_id = d.diagnosis_id
+    where p.person_id = :person_id
+    )
+    select cast(p2.person_id as bigint),
+           p2.name || ' ' || p2.surname as full_name,
+           cast(count(distinct spd.diagnosis_id) as bigint) as matching_diagnoses_count,
+           string_agg(distinct spd.label, ', ') as matching_labels
+    from selected_person_diagnosis spd
+             join medicalreport_diagnosis mrd2 on mrd2.diagnosis_id = spd.diagnosis_id
+             join report r2 on r2.report_id = mrd2.report_id
+             join person p2 on p2.person_id = r2.person_id
+    where p2.person_id != :person_id
+    group by p2.person_id, p2.name, p2.surname
+    having count(distinct spd.diagnosis_id) >=1
+    order by matching_diagnoses_count desc;
+}}}
+Тука имаме појава на многу операции (join, grouping, ordering). Истото ова квери кога би имало многу дијагнози и многу персони со многу медицински извештаи, може значително да го успори побарувањето. За да го превентираме истото, ќе додадеме некои едноставни но корисни индекси.
+
+{{{
+-- Индекс на табелата medicalreport_diagnosis што убрзува процесот на report->diagnosis пребарувања
+CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS ux_mrd_report_diagnosis ON medicalreport_diagnosis (report_id, diagnosis_id);
+
+-- Слично со горното, но во обратна насока
+CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_mrd_diagnosis_report ON medicalreport_diagnosis (diagnosis_id, report_id);
+
+-- филтрирање на персони
+CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_report_person ON report (person_id);
+}}}