Changes between Version 2 and Version 3 of QueryOptimization


Ignore:
Timestamp:
09/21/26 16:44:20 (8 days ago)
Author:
231139
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v2 v3  
    1414Кај составните B-tree индекси прва е колоната со точен услов, а втора е колоната со временски опсег. `INCLUDE` колоните не служат за пребарување, туку овозможуваат потребните вредности да се прочитаат директно од индексот. За точки, полигони и растојанија се користат GiST индекси.
    1515
    16 [attachment:"Telecom_GIS_Before_After_Index_Benchmark.sql"]
    1716
    1817== 1. Претплати по корисничка сметка ==
    … …  
    4443Планот може да користи `Index Only Scan` со `Index Cond: account_id = ...`. Времето се намалило на **0.051 ms**, односно прашалникот е **6.80 пати побрз**.
    4544
    46 [attachment:"01 idx_bench_subscriptions_account.sql"]
    47 [attachment:"01 Customer subscription overview.sql"]
    4845
    4946== 2. Историја на повици по претплата и време ==
    … …  
    7673Времето се намалило на **0.093 ms**, односно подобрување од **9.7%**. Разликата е мала поради малиот резултат, но индексот станува поважен со растењето на CDR табелата.
    7774
    78 [attachment:"02 idx_bench_calls_subscription_time.sql"]
    79 [attachment:"02 Customer call history.sql"]
    8075
    8176== 3. Интернет сесии по претплата и време ==
    … …  
    109104Времето се намалило на **0.141 ms**, односно подобрување од **7.2%**.
    110105
    111 [attachment:"04 idx_bench_data_subscription_time.sql"]
    112 [attachment:"04 Customer data usage history.sql"]
    113106
    114107== 4. Дневна потрошувачка по претплата и датум ==
    … …  
    141134Времето се намалило на **0.162 ms**, односно подобрување од **10.0%**.
    142135
    143 [attachment:"05 idx_bench_daily_subscription_date.sql"]
    144 [attachment:"05 Customer daily usage summary.sql"]
    145136
    146137== 5. CRM тикети по корисник и датум ==
    … …  
    175166Времето се намалило на **0.079 ms**, што е подобрување од **83.3%** и забрзување од **5.99 пати**.
    176167
    177 [attachment:"14 idx_bench_tickets_customer_date.sql"]
    178 [attachment:"12 Customer support ticket timeline.sql"]
    179168
    180169== 6. Мрежни прекини по локација и време ==
    … …  
    208197Времето се намалило на **0.059 ms**, односно подобрување од **16.9%**.
    209198
    210 [attachment:"17 idx_bench_outages_site_date.sql"]
    211 [attachment:"15 Network outage operations.sql"]
    212199
    213200== 7. Сообраќај на повици по сектор и време ==
    … …  
    242229Времето се намалило на **0.627 ms**. Прашалникот е **79.81 пати побрз**, односно времето е намалено за **98.7%**. Ова е најголемото Telecom подобрување.
    243230
    244 [attachment:"22 idx_bench_calls_sector_time.sql"]
    245 [attachment:"06 Sector traffic map.sql"]
    246231
    247232== 8. Кориснички адреси покриени со GIS полигон ==
    … …  
    281266Времето се намалило на **149.056 ms**, односно подобрување од **2.5%**. Добивката е мала бидејќи ST_Covers сè уште мора да провери голем број адреси.
    282267
    283 [attachment:"04 Coverage area spatial index.sql"]
    284 [attachment:"04 Customer coverage detail.sql"]
    285268
    286269== 9. Најблиска активна мрежна локација ==
    … …  
    308291Планот може да користи `Index Scan` подреден по растојание. Времето се намалило на **52.625 ms**, што е подобрување од **74.8%** и забрзување од **3.97 пати**.
    309292
    310 [attachment:"03 Network site geography index.sql"]
    311 [attachment:"01 Network site points.sql"]
    312 [attachment:"06 Find nearest network sites.sql"]
    313293
    314294== 10. Кориснички адреси во радиус од 2 km ==
    … …  
    347327Времето се намалило на **1.149 ms**. Прашалникот е **34.46 пати побрз**, односно времето е намалено за **97.1%**. Ова е најголемото GIS подобрување.
    348328
    349 [attachment:"01 Customer address spatial index.sql"]
    350329
    351330== Вкупен резултат ==
    … …  
    357336Индексите се избрани според реалните услови во прашалниците. Колоните во `Index Cond` се клучеви на индексот, колоните потребни само за резултатот се ставаат во INCLUDE, а за просторни операции се користи GiST. Така секој задржан индекс има конкретна намена и измерено подобрување.
    358337
     338== SQL дефиниции на поврзаните погледи и функции ==
     339
     340Подолу е вметнат SQL кодот што претходно беше во посебните датотеки.
     341
     342=== Customer Subscription Overview ===
     343
     344{{{
     345DROP VIEW IF EXISTS public.v_customer_subscription_overview;
     346
     347CREATE VIEW public.v_customer_subscription_overview AS
     348SELECT
     349    c.customer_id,
     350    CASE
     351        WHEN c.customer_type = 'business' THEN c.company_name || ' (' || c.first_name || ' ' || c.last_name || ')'
     352        ELSE c.first_name || ' ' || c.last_name
     353    END AS customer_name,
     354    c.customer_type,
     355    c.email,
     356    a.account_id,
     357    a.account_number,
     358    a.account_status,
     359    a.current_balance,
     360    bc.cycle_name AS billing_cycle,
     361    s.subscription_id,
     362    s.subscription_number,
     363    s.status AS subscription_status,
     364    s.activation_date,
     365    s.end_date,
     366    p.plan_name,
     367    p.monthly_fee,
     368    con.contract_number,
     369    con.contract_type,
     370    con.status AS contract_status,
     371    current_sim.msisdn,
     372    current_sim.sim_type,
     373    current_device.manufacturer AS device_manufacturer,
     374    current_device.model AS device_model,
     375    current_device.device_type,
     376    COALESCE(active_addons.recurring_addon_charge, 0) AS recurring_addon_charge,
     377    p.monthly_fee + COALESCE(active_addons.recurring_addon_charge, 0) AS total_monthly_recurring_charge
     378FROM public.customers c
     379JOIN public.accounts a ON a.customer_id = c.customer_id
     380LEFT JOIN public.billing_cycles bc ON bc.billing_cycle_id = a.billing_cycle_id
     381JOIN public.subscriptions s ON s.account_id = a.account_id
     382JOIN public.plans p ON p.plan_id = s.plan_id
     383LEFT JOIN public.contracts con ON con.contract_id = s.contract_id
     384LEFT JOIN LATERAL (
     385    SELECT sc.msisdn, sc.sim_type
     386    FROM public.sim_card_subscription_history ssh
     387    JOIN public.sim_cards sc ON sc.sim_id = ssh.sim_id
     388    WHERE ssh.subscription_id = s.subscription_id
     389      AND ssh.end_date IS NULL
     390    ORDER BY ssh.start_date DESC
     391    LIMIT 1
     392) current_sim ON TRUE
     393LEFT JOIN LATERAL (
     394    SELECT d.manufacturer, d.model, d.device_type
     395    FROM public.device_assignments da
     396    JOIN public.devices d ON d.device_id = da.device_id
     397    WHERE da.subscription_id = s.subscription_id
     398      AND da.assigned_to IS NULL
     399    ORDER BY da.assigned_from DESC
     400    LIMIT 1
     401) current_device ON TRUE
     402LEFT JOIN LATERAL (
     403    SELECT SUM(sa.price_at_activation) AS recurring_addon_charge
     404    FROM public.subscription_addons sa
     405    JOIN public.addons ad ON ad.addon_id = sa.addon_id
     406    WHERE sa.subscription_id = s.subscription_id
     407      AND sa.status = 'active'
     408      AND sa.deactivation_date IS NULL
     409      AND ad.is_recurring
     410) active_addons ON TRUE;
     411}}}
     412
     413=== Customer Call History ===
     414
     415{{{
     416DROP VIEW IF EXISTS public.v_customer_call_history;
     417
     418CREATE VIEW public.v_customer_call_history AS
     419SELECT
     420    c.customer_id,
     421    CASE WHEN c.customer_type = 'business' THEN c.company_name ELSE c.first_name || ' ' || c.last_name END AS customer_name,
     422    a.account_number,
     423    s.subscription_number,
     424    p.plan_name,
     425    cdr.call_cdr_id,
     426    cdr.originating_msisdn AS from_number,
     427    cdr.destination_msisdn AS to_number,
     428    cdr.event_start_time AS call_started_at,
     429    cdr.event_end_time AS call_ended_at,
     430    ROUND(cdr.duration_seconds::numeric / 60.0, 2) AS duration_minutes,
     431    cdr.call_type,
     432    cdr.direction,
     433    cdr.charge_amount,
     434    cdr.fraud_score,
     435    cdr.roaming_partner_id IS NOT NULL AS is_roaming,
     436    COALESCE(rp.country, 'North Macedonia') AS network_country,
     437    ns.region AS network_region,
     438    ns.site_code,
     439    ct.tower_code,
     440    ts.sector_label,
     441    ts.frequency_band,
     442    nt.generation AS network_generation
     443FROM public.customers c
     444JOIN public.accounts a ON a.customer_id = c.customer_id
     445JOIN public.subscriptions s ON s.account_id = a.account_id
     446JOIN public.plans p ON p.plan_id = s.plan_id
     447JOIN public.usage_cdr_calls cdr ON cdr.subscription_id = s.subscription_id
     448LEFT JOIN public.roaming_partners rp ON rp.roaming_partner_id = cdr.roaming_partner_id
     449LEFT JOIN public.tower_sectors ts ON ts.sector_id = cdr.sector_id
     450LEFT JOIN public.cell_towers ct ON ct.tower_id = ts.tower_id
     451LEFT JOIN public.network_sites ns ON ns.site_id = ct.site_id
     452LEFT JOIN public.network_technologies nt ON nt.technology_id = ts.technology_id;
     453}}}
     454
     455=== Customer Data Usage History ===
     456
     457{{{
     458DROP VIEW IF EXISTS public.v_customer_data_usage_history;
     459
     460CREATE VIEW public.v_customer_data_usage_history AS
     461SELECT
     462    c.customer_id,
     463    CASE WHEN c.customer_type = 'business' THEN c.company_name ELSE c.first_name || ' ' || c.last_name END AS customer_name,
     464    a.account_number,
     465    s.subscription_number,
     466    p.plan_name,
     467    d.data_cdr_id,
     468    d.session_start,
     469    d.session_end,
     470    ROUND(EXTRACT(EPOCH FROM (d.session_end - d.session_start))::numeric / 60.0, 2) AS session_duration_minutes,
     471    d.data_used_mb,
     472    ROUND(d.data_used_mb / 1024.0, 4) AS data_used_gb,
     473    d.apn,
     474    d.ip_address,
     475    d.charge_amount,
     476    d.roaming_partner_id IS NOT NULL AS is_roaming,
     477    COALESCE(rp.country, 'North Macedonia') AS network_country,
     478    ns.region AS network_region,
     479    ns.site_code,
     480    ts.sector_label,
     481    ts.frequency_band,
     482    nt.generation AS network_generation
     483FROM public.customers c
     484JOIN public.accounts a ON a.customer_id = c.customer_id
     485JOIN public.subscriptions s ON s.account_id = a.account_id
     486JOIN public.plans p ON p.plan_id = s.plan_id
     487JOIN public.usage_cdr_data d ON d.subscription_id = s.subscription_id
     488LEFT JOIN public.roaming_partners rp ON rp.roaming_partner_id = d.roaming_partner_id
     489LEFT JOIN public.tower_sectors ts ON ts.sector_id = d.sector_id
     490LEFT JOIN public.cell_towers ct ON ct.tower_id = ts.tower_id
     491LEFT JOIN public.network_sites ns ON ns.site_id = ct.site_id
     492LEFT JOIN public.network_technologies nt ON nt.technology_id = ts.technology_id;
     493}}}
     494
     495=== Customer Daily Usage Summary ===
     496
     497{{{
     498DROP VIEW IF EXISTS public.v_customer_daily_usage_summary;
     499
     500CREATE VIEW public.v_customer_daily_usage_summary AS
     501SELECT
     502    c.customer_id,
     503    CASE WHEN c.customer_type = 'business' THEN c.company_name ELSE c.first_name || ' ' || c.last_name END AS customer_name,
     504    a.account_number,
     505    s.subscription_id,
     506    s.subscription_number,
     507    p.plan_name,
     508    uad.usage_date,
     509    ROUND(uad.total_call_seconds::numeric / 60.0, 2) AS total_call_minutes,
     510    uad.total_sms_count,
     511    ROUND(uad.total_data_mb / 1024.0, 4) AS total_data_gb,
     512    uad.total_charge_amount,
     513    SUM(uad.total_data_mb) OVER (
     514        PARTITION BY s.subscription_id, date_trunc('month', uad.usage_date::timestamp)
     515        ORDER BY uad.usage_date
     516    ) AS month_to_date_data_mb
     517FROM public.customers c
     518JOIN public.accounts a ON a.customer_id = c.customer_id
     519JOIN public.subscriptions s ON s.account_id = a.account_id
     520JOIN public.plans p ON p.plan_id = s.plan_id
     521JOIN public.usage_aggregates_daily uad ON uad.subscription_id = s.subscription_id;
     522}}}
     523
     524=== Customer Support Ticket Timeline ===
     525
     526{{{
     527DROP VIEW IF EXISTS public.v_customer_support_ticket_timeline;
     528
     529CREATE VIEW public.v_customer_support_ticket_timeline AS
     530SELECT
     531    c.customer_id,
     532    CASE WHEN c.customer_type = 'business' THEN c.company_name ELSE c.first_name || ' ' || c.last_name END AS customer_name,
     533    a.account_number,
     534    s.subscription_number,
     535    p.plan_name,
     536    t.ticket_id,
     537    t.ticket_type,
     538    t.subject,
     539    t.priority,
     540    t.status AS ticket_status,
     541    t.created_at AS ticket_created_at,
     542    t.closed_at AS ticket_closed_at,
     543    ROUND(EXTRACT(EPOCH FROM (t.closed_at - t.created_at))::numeric / 3600.0, 2) AS resolution_hours,
     544    owner.first_name || ' ' || owner.last_name AS assigned_employee,
     545    ci.interaction_id,
     546    ci.interaction_type,
     547    ci.channel,
     548    ci.interaction_time,
     549    ci.notes,
     550    ci.old_status,
     551    ci.new_status,
     552    actor.first_name || ' ' || actor.last_name AS interaction_employee,
     553    ROW_NUMBER() OVER (PARTITION BY t.ticket_id ORDER BY ci.interaction_time NULLS LAST, ci.interaction_id) AS interaction_sequence
     554FROM public.customers c
     555JOIN public.crm_tickets t ON t.customer_id = c.customer_id
     556LEFT JOIN public.accounts a ON a.account_id = t.account_id
     557LEFT JOIN public.subscriptions s ON s.subscription_id = t.subscription_id
     558LEFT JOIN public.plans p ON p.plan_id = s.plan_id
     559LEFT JOIN public.employees owner ON owner.employee_id = t.assigned_employee_id
     560LEFT JOIN public.crm_interactions ci ON ci.ticket_id = t.ticket_id
     561LEFT JOIN public.employees actor ON actor.employee_id = ci.employee_id;
     562}}}
     563
     564=== Network Outage Operations ===
     565
     566{{{
     567DROP VIEW IF EXISTS public.v_network_outage_operations;
     568
     569CREATE VIEW public.v_network_outage_operations AS
     570WITH assignment_summary AS (
     571    SELECT outage_id,
     572           COUNT(*) AS assignment_count,
     573           COUNT(*) FILTER (WHERE assignment_type = 'outage_lead') AS lead_assignments,
     574           COUNT(*) FILTER (WHERE assignment_type = 'field_support') AS field_assignments,
     575           MIN(start_time) AS first_assignment_at,
     576           MAX(end_time) AS last_assignment_end
     577    FROM public.employee_assignments
     578    WHERE outage_id IS NOT NULL
     579    GROUP BY outage_id
     580)
     581SELECT
     582    o.outage_id,
     583    ns.site_id,
     584    ns.site_code,
     585    ns.site_name,
     586    ns.region,
     587    o.outage_type,
     588    o.status,
     589    o.start_time,
     590    o.end_time,
     591    CASE WHEN o.end_time IS NOT NULL
     592         THEN ROUND(EXTRACT(EPOCH FROM (o.end_time - o.start_time))::numeric / 60.0, 2)
     593    END AS outage_duration_minutes,
     594    CASE
     595        WHEN o.end_time IS NULL THEN 'open'
     596        WHEN o.end_time - o.start_time <= INTERVAL '60 minutes' THEN 'under_1_hour'
     597        WHEN o.end_time - o.start_time <= INTERVAL '4 hours' THEN 'one_to_four_hours'
     598        ELSE 'over_4_hours'
     599    END AS duration_band,
     600    o.root_cause,
     601    na.alarm_id,
     602    na.alarm_type,
     603    na.severity AS alarm_severity,
     604    na.raised_at AS alarm_raised_at,
     605    COALESCE(ass.assignment_count, 0) AS assignment_count,
     606    COALESCE(ass.lead_assignments, 0) AS lead_assignments,
     607    COALESCE(ass.field_assignments, 0) AS field_assignments,
     608    ass.first_assignment_at,
     609    ass.last_assignment_end
     610FROM public.outages o
     611JOIN public.network_sites ns ON ns.site_id = o.site_id
     612LEFT JOIN public.network_alarms na ON na.alarm_id = o.alarm_id
     613LEFT JOIN assignment_summary ass ON ass.outage_id = o.outage_id;
     614}}}
     615
     616=== Sector Traffic Map ===
     617
     618{{{
     619-- CDR activity summarized per map polygon without multiplying event rows.
     620-- QGIS polygon layer. Unique key: coverage_zone_id; geometry: coverage_area.
     621CREATE OR REPLACE VIEW public.v_sector_traffic_map AS
     622WITH call_stats AS
     623(
     624    SELECT
     625        sector_id,
     626        count(*) AS call_count,
     627        coalesce(sum(duration_seconds), 0) AS call_seconds,
     628        max(event_start_time) AS last_call_at
     629    FROM public.usage_cdr_calls
     630    WHERE sector_id IS NOT NULL
     631    GROUP BY sector_id
     632),
     633sms_stats AS
     634(
     635    SELECT
     636        sector_id,
     637        count(*) AS sms_count,
     638        max(event_time) AS last_sms_at
     639    FROM public.usage_cdr_sms
     640    WHERE sector_id IS NOT NULL
     641    GROUP BY sector_id
     642),
     643data_stats AS
     644(
     645    SELECT
     646        sector_id,
     647        count(*) AS data_session_count,
     648        coalesce(sum(data_used_mb), 0) AS data_used_mb,
     649        max(session_start) AS last_data_at
     650    FROM public.usage_cdr_data
     651    WHERE sector_id IS NOT NULL
     652    GROUP BY sector_id
     653)
     654SELECT
     655    coverage.coverage_zone_id,
     656    coverage.site_id,
     657    coverage.site_code,
     658    coverage.site_name,
     659    coverage.region,
     660    coverage.tower_id,
     661    coverage.tower_code,
     662    coverage.sector_id,
     663    coverage.sector_label,
     664    coverage.technology_name,
     665    coverage.generation,
     666    coalesce(calls.call_count, 0) AS call_count,
     667    coalesce(calls.call_seconds, 0) AS call_seconds,
     668    coalesce(messages.sms_count, 0) AS sms_count,
     669    coalesce(data_usage.data_session_count, 0) AS data_session_count,
     670    coalesce(data_usage.data_used_mb, 0) AS data_used_mb,
     671    coalesce(calls.call_count, 0)
     672      + coalesce(messages.sms_count, 0)
     673      + coalesce(data_usage.data_session_count, 0) AS total_events,
     674    greatest(calls.last_call_at, messages.last_sms_at, data_usage.last_data_at)
     675        AS last_event_at,
     676    coverage.coverage_area
     677FROM public.v_network_coverage_map coverage
     678LEFT JOIN call_stats calls ON calls.sector_id = coverage.sector_id
     679LEFT JOIN sms_stats messages ON messages.sector_id = coverage.sector_id
     680LEFT JOIN data_stats data_usage ON data_usage.sector_id = coverage.sector_id;
     681}}}
     682
     683=== Customer Coverage Detail ===
     684
     685{{{
     686-- One row for every active sector covering an active customer's primary address.
     687-- QGIS point layer. Unique key can be customer_id + coverage_zone_id.
     688CREATE OR REPLACE VIEW public.v_customer_coverage_detail AS
     689SELECT
     690    c.customer_id,
     691    concat_ws(' ', c.first_name, c.last_name) AS customer_name,
     692    ca.address_id,
     693    concat_ws(', ', ca.street, ca.city, ca.country) AS full_address,
     694    cz.coverage_zone_id,
     695    ns.site_id,
     696    ns.site_code,
     697    ns.site_name,
     698    ct.tower_code,
     699    ts.sector_id,
     700    ts.sector_label,
     701    nt.technology_name,
     702    nt.generation,
     703    cz.signal_quality_score,
     704    round(
     705        ST_Distance(ca.location::geography, ns.location::geography)::numeric,
     706        1
     707    ) AS distance_to_site_m,
     708    ca.location AS customer_location
     709FROM public.customers c
     710JOIN public.customer_addresses ca
     711  ON ca.customer_id = c.customer_id
     712 AND ca.is_primary
     713JOIN public.coverage_zones cz
     714  ON ca.location IS NOT NULL
     715 AND cz.coverage_area IS NOT NULL
     716 AND ST_Covers(cz.coverage_area, ca.location)
     717JOIN public.tower_sectors ts ON ts.sector_id = cz.sector_id
     718JOIN public.cell_towers ct ON ct.tower_id = ts.tower_id
     719JOIN public.network_sites ns ON ns.site_id = ct.site_id
     720JOIN public.network_technologies nt
     721  ON nt.technology_id = ts.technology_id
     722WHERE c.status = 'active'
     723  AND ns.status = 'active'
     724  AND ct.status = 'active'
     725  AND ts.status = 'active';
     726}}}
     727
     728=== Network Site Points ===
     729
     730{{{
     731-- QGIS point layer. Unique key: site_id; geometry: location.
     732CREATE OR REPLACE VIEW public.v_network_site_points AS
     733SELECT
     734    ns.site_id,
     735    ns.site_code,
     736    ns.site_name,
     737    ns.address,
     738    ns.region,
     739    ns.site_type,
     740    ns.status,
     741    ns.opened_at,
     742    ns.location
     743FROM public.network_sites ns
     744WHERE ns.location IS NOT NULL;
     745}}}
     746
     747=== Find Nearest Network Sites ===
     748
     749{{{
     750CREATE OR REPLACE FUNCTION public.find_nearest_network_sites(
     751    p_latitude double precision,
     752    p_longitude double precision,
     753    p_max_distance_m double precision DEFAULT 10000,
     754    p_limit integer DEFAULT 5
     755)
     756RETURNS TABLE
     757(
     758    site_id bigint,
     759    site_code text,
     760    site_name text,
     761    region text,
     762    distance_m numeric,
     763    active_technologies text
     764)
     765LANGUAGE plpgsql
     766STABLE
     767AS $$
     768BEGIN
     769    IF p_latitude < -90 OR p_latitude > 90 THEN
     770        RAISE EXCEPTION 'Latitude must be between -90 and 90';
     771    END IF;
     772
     773    IF p_longitude < -180 OR p_longitude > 180 THEN
     774        RAISE EXCEPTION 'Longitude must be between -180 and 180';
     775    END IF;
     776
     777    IF p_max_distance_m <= 0 THEN
     778        RAISE EXCEPTION 'Maximum distance must be positive';
     779    END IF;
     780
     781    IF p_limit < 1 OR p_limit > 100 THEN
     782        RAISE EXCEPTION 'Limit must be between 1 and 100';
     783    END IF;
     784
     785    RETURN QUERY
     786    WITH search_point AS
     787    (
     788        SELECT ST_SetSRID(ST_MakePoint(p_longitude, p_latitude), 4326) AS location
     789    )
     790    SELECT
     791        ns.site_id,
     792        ns.site_code,
     793        ns.site_name,
     794        ns.region,
     795        round(
     796            ST_Distance(ns.location::geography, sp.location::geography)::numeric,
     797            1
     798        ) AS distance_m,
     799        coalesce(technologies.names, 'No active sectors') AS active_technologies
     800    FROM public.network_sites ns
     801    CROSS JOIN search_point sp
     802    LEFT JOIN LATERAL
     803    (
     804        SELECT string_agg(
     805                   DISTINCT concat(nt.generation, ' ', nt.technology_name),
     806                   ', '
     807                   ORDER BY concat(nt.generation, ' ', nt.technology_name)
     808               ) AS names
     809        FROM public.cell_towers ct
     810        JOIN public.tower_sectors ts ON ts.tower_id = ct.tower_id
     811        JOIN public.network_technologies nt
     812          ON nt.technology_id = ts.technology_id
     813        WHERE ct.site_id = ns.site_id
     814          AND ct.status = 'active'
     815          AND ts.status = 'active'
     816          AND nt.status = 'active'
     817    ) technologies ON true
     818    WHERE ns.status = 'active'
     819      AND ns.location IS NOT NULL
     820      AND ST_DWithin(
     821              ns.location::geography,
     822              sp.location::geography,
     823              p_max_distance_m
     824          )
     825    ORDER BY ns.location::geography <-> sp.location::geography
     826    LIMIT p_limit;
     827END;
     828$$;
     829}}}