Changes between Version 1 and Version 2 of QueryOptimization

Timestamp:

09/21/26 16:40:41 (8 days ago)

Author:

231139

Comment:

--

QueryOptimization

@@ -1 @@
-= Querry optimisation
+= Индексирање и оптимизација на прашалници =
+
+Во оваа фаза се оптимизирани десет прашалници што ги претставуваат најважните начини на користење на Telecom и GIS погледите. Тестовите се извршени директно врз основните табели за јасно да се измери влијанието на конкретниот индекс. Наведениот поглед ја користи истата пристапна шема, но не бил директен предмет на мерењето.
+
+За секој прашалник прво е измерено времето без опционалниот индекс. Потоа планот е проверен со:
+
+{{{
+EXPLAIN (ANALYZE, BUFFERS)
+SELECT ...;
+}}}
+
+`Seq Scan` значи дека PostgreSQL чита голем дел или цела табела. `Rows Removed by Filter` покажува колку непотребни редови биле прочитани. По оптимизацијата, `Index Scan`, `Index Only Scan` или `Bitmap Index Scan` покажува дека базата пристапува само до потребните записи. `Index Cond` покажува кои услови навистина го користат индексот.
+
+Кај составните B-tree индекси прва е колоната со точен услов, а втора е колоната со временски опсег. `INCLUDE` колоните не служат за пребарување, туку овозможуваат потребните вредности да се прочитаат директно од индексот. За точки, полигони и растојанија се користат GiST индекси.
+
+[attachment:"Telecom_GIS_Before_After_Index_Benchmark.sql"]
+
+== 1. Претплати по корисничка сметка ==
+
+Поврзан поглед: `v_customer_subscription_overview`
+
+{{{
+SELECT count(*), max(subscription_id), max(plan_id), max(status)
+FROM public.subscriptions
+WHERE account_id = :account_id;
+}}}
+
+=== Пред оптимизацијата
+
+Времето било **0.347 ms**. Без индекс, планот користи `Seq Scan` и го применува `account_id = ...` како Filter. Така се проверуваат и претплатите од другите сметки.
+
+=== Избран индекс
+
+{{{
+CREATE INDEX idx_bench_subscriptions_account
+ON public.subscriptions (account_id, subscription_id)
+INCLUDE (plan_id, contract_id, subscription_number, status);
+}}}
+
+`account_id` е прв бидејќи е условот за пребарување. `subscription_id` е втор за организирање на претплатите на сметката. Другите колони се во INCLUDE бидејќи ги користи погледот, но не се филтри.
+
+=== По оптимизацијата
+
+Планот може да користи `Index Only Scan` со `Index Cond: account_id = ...`. Времето се намалило на **0.051 ms**, односно прашалникот е **6.80 пати побрз**.
+
+[attachment:"01 idx_bench_subscriptions_account.sql"]
+[attachment:"01 Customer subscription overview.sql"]
+
+== 2. Историја на повици по претплата и време ==
+
+Поврзан поглед: `v_customer_call_history`
+
+{{{
+SELECT count(*), max(event_start_time), sum(duration_seconds)
+FROM public.usage_cdr_calls
+WHERE subscription_id = :subscription_id
+  AND event_start_time >= TIMESTAMPTZ '2026-05-20 00:00:00+02'
+  AND event_start_time <  TIMESTAMPTZ '2026-08-21 00:00:00+02';
+}}}
+
+=== Пред оптимизацијата
+
+Времето било **0.103 ms**. PostgreSQL ги проверува повиците и дополнително ги филтрира според претплатата и периодот.
+
+=== Избран индекс
+
+{{{
+CREATE INDEX idx_bench_calls_subscription_time
+ON public.usage_cdr_calls (subscription_id, event_start_time DESC);
+}}}
+
+`subscription_id` е прв бидејќи се бара една претплата. `event_start_time` е втор бидејќи потоа се ограничува временскиот период. По креирањето, двата услови може да се појават во `Index Cond`.
+
+=== По оптимизацијата
+
+Времето се намалило на **0.093 ms**, односно подобрување од **9.7%**. Разликата е мала поради малиот резултат, но индексот станува поважен со растењето на CDR табелата.
+
+[attachment:"02 idx_bench_calls_subscription_time.sql"]
+[attachment:"02 Customer call history.sql"]
+
+== 3. Интернет сесии по претплата и време ==
+
+Поврзан поглед: `v_customer_data_usage_history`
+
+{{{
+SELECT count(*), max(session_start),
+       sum(data_used_mb), sum(charge_amount)
+FROM public.usage_cdr_data
+WHERE subscription_id = :subscription_id
+  AND session_start >= TIMESTAMPTZ '2026-05-20 00:00:00+02'
+  AND session_start <  TIMESTAMPTZ '2026-08-21 00:00:00+02';
+}}}
+
+=== Пред оптимизацијата
+
+Времето било **0.152 ms**. Планот без индекс ги филтрира интернет сесиите по читањето на табелата.
+
+=== Избран индекс
+
+{{{
+CREATE INDEX idx_bench_data_subscription_time
+ON public.usage_cdr_data (subscription_id, session_start DESC);
+}}}
+
+Редоследот го следи прашалникот: точна претплата, а потоа временски опсег. Со индексот, двата услови може заедно да се користат во `Index Cond`.
+
+=== По оптимизацијата
+
+Времето се намалило на **0.141 ms**, односно подобрување од **7.2%**.
+
+[attachment:"04 idx_bench_data_subscription_time.sql"]
+[attachment:"04 Customer data usage history.sql"]
+
+== 4. Дневна потрошувачка по претплата и датум ==
+
+Поврзан поглед: `v_customer_daily_usage_summary`
+
+{{{
+SELECT count(*), sum(total_call_seconds), sum(total_sms_count),
+       sum(total_data_mb), sum(total_charge_amount)
+FROM public.usage_aggregates_daily
+WHERE subscription_id = :subscription_id
+  AND usage_date BETWEEN DATE '2026-05-20' AND DATE '2026-08-20';
+}}}
+
+=== Пред оптимизацијата
+
+Времето било **0.180 ms**. Без индекс се проверуваат и дневни записи што не припаѓаат на избраната претплата или период.
+
+=== Избран индекс
+
+{{{
+CREATE INDEX idx_bench_daily_subscription_date
+ON public.usage_aggregates_daily (subscription_id, usage_date DESC);
+}}}
+
+`subscription_id` е колоната со еднаквост, а `usage_date` е временскиот опсег. Индексот го намалува бројот на редови што стигнуваат до агрегатните функции.
+
+=== По оптимизацијата
+
+Времето се намалило на **0.162 ms**, односно подобрување од **10.0%**.
+
+[attachment:"05 idx_bench_daily_subscription_date.sql"]
+[attachment:"05 Customer daily usage summary.sql"]
+
+== 5. CRM тикети по корисник и датум ==
+
+Поврзан поглед: `v_customer_support_ticket_timeline`
+
+{{{
+SELECT count(*), max(created_at), max(priority), max(status)
+FROM public.crm_tickets
+WHERE customer_id = :customer_id
+  AND created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+01'
+  AND created_at <  TIMESTAMPTZ '2026-08-21 00:00:00+02';
+}}}
+
+=== Пред оптимизацијата
+
+Времето било **0.473 ms**. Планот чита повеќе тикети и потоа ги отстранува тие што не припаѓаат на избраниот корисник и период.
+
+=== Избран индекс
+
+{{{
+CREATE INDEX idx_bench_tickets_customer_date
+ON public.crm_tickets (customer_id, created_at DESC)
+INCLUDE (account_id, subscription_id,
+         assigned_employee_id, status, priority);
+}}}
+
+`customer_id` и `created_at` се индексни клучеви бидејќи се користат во WHERE. Другите колони се во INCLUDE бидејќи се потребни за timeline погледот. Така е можен `Index Only Scan`.
+
+=== По оптимизацијата
+
+Времето се намалило на **0.079 ms**, што е подобрување од **83.3%** и забрзување од **5.99 пати**.
+
+[attachment:"14 idx_bench_tickets_customer_date.sql"]
+[attachment:"12 Customer support ticket timeline.sql"]
+
+== 6. Мрежни прекини по локација и време ==
+
+Поврзан поглед: `v_network_outage_operations`
+
+{{{
+SELECT count(*), max(start_time), max(status)
+FROM public.outages
+WHERE site_id = :site_id
+  AND start_time >= TIMESTAMPTZ '2025-01-01 00:00:00+01'
+  AND start_time <  TIMESTAMPTZ '2026-08-21 00:00:00+02';
+}}}
+
+=== Пред оптимизацијата
+
+Времето било **0.071 ms**. Условите за локација и време се применуваат врз веќе прочитаните прекини.
+
+=== Избран индекс
+
+{{{
+CREATE INDEX idx_bench_outages_site_date
+ON public.outages (site_id, start_time DESC)
+INCLUDE (alarm_id, status, outage_type, end_time);
+}}}
+
+`site_id` е прв затоа што се анализира една локација. `start_time` е втор за периодот. INCLUDE колоните ги содржат главните податоци од оперативниот поглед.
+
+=== По оптимизацијата
+
+Времето се намалило на **0.059 ms**, односно подобрување од **16.9%**.
+
+[attachment:"17 idx_bench_outages_site_date.sql"]
+[attachment:"15 Network outage operations.sql"]
+
+== 7. Сообраќај на повици по сектор и време ==
+
+Поврзан поглед: `v_sector_traffic_map`
+
+{{{
+SELECT count(*), max(event_start_time),
+       sum(duration_seconds), sum(charge_amount)
+FROM public.usage_cdr_calls
+WHERE sector_id = :sector_id
+  AND event_start_time >= TIMESTAMPTZ '2026-05-20 00:00:00+02'
+  AND event_start_time <  TIMESTAMPTZ '2026-08-21 00:00:00+02';
+}}}
+
+=== Пред оптимизацијата
+
+Времето било **50.042 ms**. EXPLAIN ANALYZE покажува `Seq Scan` врз големата CDR табела. Се читаат многу повици, а потоа се отстрануваат тие што не припаѓаат на избраниот сектор или период.
+
+=== Избран индекс
+
+{{{
+CREATE INDEX idx_bench_calls_sector_time
+ON public.usage_cdr_calls (sector_id, event_start_time)
+INCLUDE (duration_seconds, charge_amount);
+}}}
+
+`sector_id` е прв поради точниот услов, а `event_start_time` е втор поради временскиот опсег. Вредностите за SUM се во INCLUDE. По додавањето, планот може да користи `Index Only Scan` и да ги чита само потребните повици.
+
+=== По оптимизацијата
+
+Времето се намалило на **0.627 ms**. Прашалникот е **79.81 пати побрз**, односно времето е намалено за **98.7%**. Ова е најголемото Telecom подобрување.
+
+[attachment:"22 idx_bench_calls_sector_time.sql"]
+[attachment:"06 Sector traffic map.sql"]
+
+== 8. Кориснички адреси покриени со GIS полигон ==
+
+Поврзан поглед: `v_customer_coverage_detail`
+
+{{{
+SELECT count(*)
+FROM public.customer_addresses address
+WHERE address.is_primary
+  AND address.location IS NOT NULL
+  AND EXISTS (
+      SELECT 1
+      FROM public.coverage_zones coverage
+      WHERE coverage.coverage_area IS NOT NULL
+        AND coverage.coverage_area && address.location
+        AND ST_Covers(coverage.coverage_area, address.location)
+  );
+}}}
+
+=== Пред оптимизацијата
+
+Времето било **152.839 ms**. Без просторен индекс се проверуваат многу комбинации од адресни точки и полигони.
+
+=== Избран индекс
+
+{{{
+CREATE INDEX idx_coverage_zones_area_gist
+ON public.coverage_zones
+USING gist (coverage_area);
+}}}
+
+GiST е избран затоа што прашалникот работи со полигони. Операторот `&&` преку индексот прво избира можни полигони, а `ST_Covers` ја прави точната проверка само врз тие кандидати.
+
+=== По оптимизацијата
+
+Времето се намалило на **149.056 ms**, односно подобрување од **2.5%**. Добивката е мала бидејќи ST_Covers сè уште мора да провери голем број адреси.
+
+[attachment:"04 Coverage area spatial index.sql"]
+[attachment:"04 Customer coverage detail.sql"]
+
+== 9. Најблиска активна мрежна локација ==
+
+Поврзан поглед и функција: `v_network_site_points` и `find_nearest_network_sites`
+
+Прашалникот ја наоѓа најблиската активна локација за 2 000 адреси. Растојанието се пресметува во метри со geography.
+
+=== Пред оптимизацијата
+
+Времето било **208.732 ms**. Без соодветен индекс PostgreSQL ги споредува и сортира мрежните локации за секоја адреса.
+
+=== Избран индекс
+
+{{{
+CREATE INDEX idx_network_sites_location_geography_gist
+ON public.network_sites
+USING gist ((location::geography));
+}}}
+
+Индексот е врз `location::geography` бидејќи прашалникот го користи истиот израз. GiST го поддржува операторот `<->`, со кој директно се бара најблиската локација.
+
+=== По оптимизацијата
+
+Планот може да користи `Index Scan` подреден по растојание. Времето се намалило на **52.625 ms**, што е подобрување од **74.8%** и забрзување од **3.97 пати**.
+
+[attachment:"03 Network site geography index.sql"]
+[attachment:"01 Network site points.sql"]
+[attachment:"06 Find nearest network sites.sql"]
+
+== 10. Кориснички адреси во радиус од 2 km ==
+
+{{{
+SELECT count(*), max(address.address_id)
+FROM public.customer_addresses address
+JOIN public.network_sites site ON site.site_id = :site_id
+WHERE address.location IS NOT NULL
+  AND address.location && ST_Envelope(
+      ST_Buffer(site.location::geography, 2000)::geometry
+  )
+  AND ST_DWithin(
+      address.location::geography,
+      site.location::geography,
+      2000
+  );
+}}}
+
+=== Пред оптимизацијата
+
+Времето било **39.591 ms**. Без индекс PostgreSQL ја чита customer_addresses и ја проверува оддалеченоста за голем број адреси.
+
+=== Избран индекс
+
+{{{
+CREATE INDEX idx_customer_addresses_location_gist
+ON public.customer_addresses
+USING gist (location);
+}}}
+
+GiST индексот го користи условот `location && ...` за да ги избере точките во приближната област. `ST_DWithin` потоа го проверува точното растојание само за тие кандидати. Во планот ова може да се појави како `Bitmap Index Scan`.
+
+=== По оптимизацијата
+
+Времето се намалило на **1.149 ms**. Прашалникот е **34.46 пати побрз**, односно времето е намалено за **97.1%**. Ова е најголемото GIS подобрување.
+
+[attachment:"01 Customer address spatial index.sql"]
+
+== Вкупен резултат ==
+
+За десетте прашалници, збирот на медијаните се намалил од **452.530 ms** на **204.042 ms**. Вкупно се заштедени **248.488 ms**, што е намалување од **54.9%** и забрзување од **2.22 пати**.
+
+Најголемо подобрување има кај сообраќајот по сектор и GIS пребарувањето на адреси во радиус од 2 km. Во двата случаи EXPLAIN ANALYZE покажува дека проблемот бил читање голем број непотребни редови, а индексот овозможува директен пристап до релевантното множество.
+
+Индексите се избрани според реалните услови во прашалниците. Колоните во `Index Cond` се клучеви на индексот, колоните потребни само за резултатот се ставаат во INCLUDE, а за просторни операции се користи GiST. Така секој задржан индекс има конкретна намена и измерено подобрување.
+