Context Navigation

← Previous Version
View Latest Version
Next Version →

Version 4 (modified by 231139, 8 days ago) ( diff )
--

Индексирање и оптимизација на прашалници

Во оваа фаза се оптимизирани десет прашалници што ги претставуваат најважните начини на користење на 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 индекси.

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 пати побрз.

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 табелата.

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%.

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%.

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 пати.

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%.

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 подобрување.

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 сè уште мора да провери голем број адреси.

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 пати.

SQL на поврзаната функција

CREATE OR REPLACE FUNCTION public.find_nearest_network_sites(
    p_latitude double precision,
    p_longitude double precision,
    p_max_distance_m double precision DEFAULT 10000,
    p_limit integer DEFAULT 5
)
RETURNS TABLE
(
    site_id bigint,
    site_code text,
    site_name text,
    region text,
    distance_m numeric,
    active_technologies text
)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    IF p_latitude < -90 OR p_latitude > 90 THEN
        RAISE EXCEPTION 'Latitude must be between -90 and 90';
    END IF;

    IF p_longitude < -180 OR p_longitude > 180 THEN
        RAISE EXCEPTION 'Longitude must be between -180 and 180';
    END IF;

    IF p_max_distance_m <= 0 THEN
        RAISE EXCEPTION 'Maximum distance must be positive';
    END IF;

    IF p_limit < 1 OR p_limit > 100 THEN
        RAISE EXCEPTION 'Limit must be between 1 and 100';
    END IF;

    RETURN QUERY
    WITH search_point AS
    (
        SELECT ST_SetSRID(ST_MakePoint(p_longitude, p_latitude), 4326) AS location
    )
    SELECT
        ns.site_id,
        ns.site_code,
        ns.site_name,
        ns.region,
        round(
            ST_Distance(ns.location::geography, sp.location::geography)::numeric,
            1
        ) AS distance_m,
        coalesce(technologies.names, 'No active sectors') AS active_technologies
    FROM public.network_sites ns
    CROSS JOIN search_point sp
    LEFT JOIN LATERAL
    (
        SELECT string_agg(
                   DISTINCT concat(nt.generation, ' ', nt.technology_name),
                   ', '
                   ORDER BY concat(nt.generation, ' ', nt.technology_name)
               ) AS names
        FROM public.cell_towers ct
        JOIN public.tower_sectors ts ON ts.tower_id = ct.tower_id
        JOIN public.network_technologies nt
          ON nt.technology_id = ts.technology_id
        WHERE ct.site_id = ns.site_id
          AND ct.status = 'active'
          AND ts.status = 'active'
          AND nt.status = 'active'
    ) technologies ON true
    WHERE ns.status = 'active'
      AND ns.location IS NOT NULL
      AND ST_DWithin(
              ns.location::geography,
              sp.location::geography,
              p_max_distance_m
          )
    ORDER BY ns.location::geography <-> sp.location::geography
    LIMIT p_limit;
END;
$$;

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 подобрување.

Вкупен резултат

За десетте прашалници, збирот на медијаните се намалил од 452.530 ms на 204.042 ms. Вкупно се заштедени 248.488 ms, што е намалување од 54.9% и забрзување од 2.22 пати.

Најголемо подобрување има кај сообраќајот по сектор и GIS пребарувањето на адреси во радиус од 2 km. Во двата случаи EXPLAIN ANALYZE покажува дека проблемот бил читање голем број непотребни редови, а индексот овозможува директен пристап до релевантното множество.

Индексите се избрани според реалните услови во прашалниците. Колоните во Index Cond се клучеви на индексот, колоните потребни само за резултатот се ставаат во INCLUDE, а за просторни операции се користи GiST. Така секој задржан индекс има конкретна намена и измерено подобрување.

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text