== Индексирање

//некој вовед

=== 1. Брзо пребарување при најава на апликацијата
Ова е едно од покористиените сценарија каде соодветно најавата на корисникот наложува проверка за негово постоење, кое се врши преку прецизна споредба со email адресата.

Доколку би имале гооолем број корисници, ќе треба некако да се пребаруваат побрзо корисниците
за да може најавата да се одвива без многу чекање. Затоа, ќе поставиме индекс за лесно пребарување по email.
Овој е BTREE индекс и е доста погоден за конкретни пребарувања (where email = :param)

''Напомена: Login акцијата оди преку email, бидејќи истиот е поставен како уникатен, и 1 електронска адреса може да припаѓа на само 1 корисник во базата.''

{{{
-- Појаснување:креира индекс со назив idx_user_email на табелата reportiumuser за колоната email
CREATE INDEX IF NOT EXISTS idx_user_email ON reportiumuser (email);
}}}

За да се провери кога би имало многу податоци:
{{{
EXPLAIN (ANALYZE, BUFFERS)
SELECT password_hash
FROM reportiumuser
WHERE email = :parameter_email ;
}}}

=== 2. Ефикасно и ефективно сортирање по created_at за Report
Многу важно е сортирањето по датумот кога е креиран некој извештај да биде ефикасно и ефективно исто-времено. За да постигнеме таква состојба, воведуваме индексирање, каде додаваме и соодветен tiebreaker, кој игра улога во случај 2 записи да имаат ист датум на креирањем, тогаш сортирањето се одвива според report_id кое е уникатно во самата табела..

{{{
-- се креира индекс со име idx_report_created_with_id врз Report табелата кој ги зема во обзир колоните created_at и report_id
CREATE INDEX idx_report_created_with_id ON report (created_at, report_id);
}}}
Ова би овозможило сортирањето да е брзо и ефикасно кога во апликацијата би се ракувало со масовна бројка на Report записи.

За тестирање:
{{{
EXPLAIN (ANALYZE, BUFFERS)
SELECT report_id, report_type, person_id, summary
FROM report
ORDER BY created_at, report_id
LIMIT 20;
}}}

или

{{{
EXPLAIN (ANALYZE, BUFFERS)
SELECT report_id, report_type, person_id, summary
FROM report
ORDER BY created_at DESC, report_id DESC
LIMIT 20;
}}}

Фотографии кои покажуваат некаква позитивна промена на само 100тина записи, а скалабилно ако го зголемиме бројот на записи, би добиле значително пократко време на сортирање.
==== Пред индексирањето
[[Image(before_indexing_2.png, align=center)]]

==== После индексирањето
[[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);
}}}