Changes between Version 6 and Version 7 of Indexing

Timestamp:

08/27/25 01:17:26 (7 days ago)

Author:

221007

Comment:

--

Indexing

@@ -59 +59 @@
 ==== После индексирањето
 [[Image(after_indexing_2.png, align=center)]]
+
+
+=== 3. Статистика за топ 3 најдобри институции во изминатите години
+Кверито кое успорува со многу податоци е следното:
+{{{
+-- EXPLAIN (ANALYZE, BUFFERS)
+WITH top_3_institutions AS (
+  SELECT ar.institution_id, COUNT(*) AS total_reports
+  FROM report r
+  JOIN academicreport ar ON ar.report_id = r.report_id
+  WHERE r.created_at >= date_trunc('year', now()) - interval '1 year'
+  GROUP BY ar.institution_id
+  ORDER BY COUNT(*) DESC
+  LIMIT 3
+)
+SELECT i.name, a.total_reports
+FROM top_3_institutions a
+JOIN institution i ON i.institution_id = a.institution_id
+ORDER BY a.total_reports DESC;
+}}}
+
+Ова квери се користи во функција во апликацијата, каде функционалноста е реализирана во панелот на корисникот и се прикажува како статистика „најдобрите 3 институции во изминатата 1 година во кои се издавале академски извештаи по вкупниот број на извештаи“.
+
+Прво прави секвенцијални пребарувања на report, потоа на academicreport и на крај прави на institution. Со ова ги наоѓа табелите. Потоа почнува со Hash Joins (спојувања join операции) и HashAggregate (group by операции), и како за крај прави сортирање (Sort) и тоа по функција count(*) (по бројот на записи).
+Ова би било доста спора операција во случај на многу записи низ кои треба да бара, што совршено би се решило со поставување на неколку индекси:
+
+{{{
+-- Овој индекс овозможува побрзо филтрирање по датум (created_at), со цел да спречи скенирање на целата табела, туку само последната година.
+CREATE INDEX IF NOT EXISTS ix_report_created_at          ON report(created_at);
+
+-- Овој индекс ја забрзува join операцијата меѓу academicreport и report, бидејќи пребарувањето по report_id станува директно преку индекс.
+CREATE INDEX IF NOT EXISTS ix_ar_report_id               ON academicreport(report_id);
+
+-- Овој индекс помага при групирање и пребројување по institution_id, а исто така и при join со institution табелата.
+CREATE INDEX IF NOT EXISTS ix_ar_institution_id          ON academicreport(institution_id);
+}}}