gendesign/data/sql/157_analyze_hotpath_indexes.sql

-- 157_analyze_hotpath_indexes.sql
-- Perf/index аудит горячего analyze-пути Site Finder (POST /parcels/{cad}/analyze).
-- Прецедент: migration 153 (#70) — partial covering index objective_lots(district,
--   price_per_m2_rub) → cold 5.3s→87ms Index Only Scan. Этот файл — аналогичные wins,
--   найденные EXPLAIN (ANALYZE, BUFFERS) на проде (2026-06-14, district='Академический',
--   centroid POINT(60.4869 56.7992), objective_lots ~1.12M / 2.1 ГБ, cad_buildings 38k / 88 МБ).
--
-- КОРНЕВАЯ ПРИЧИНА (3 из 4 индексов): запросы фильтруют по `X.geom::geography` для
--   точного метрового ST_DWithin (radius в метрах). Существующие GIST построены на ПЛОСКОМ
--   `geom` (geometry, SRID 4326) → планировщик НЕ может ими воспользоваться для
--   geography-предиката → Seq Scan всей таблицы на КАЖДЫЙ analyze. Функциональный GIST на
--   выражении `(geom::geography)` закрывает именно этот предикат (Index Cond через `&&`
--   + _st_expand). Плоские geom-GIST НЕ трогаем — они обслуживают ST_Intersects(geom,…)
--   (geometry) ветки (например overlap_rows в _neighbors_summary) и остаются нужны.
--
-- ИЗМЕРЕНИЯ (EXPLAIN ANALYZE до/после; «после» — warm 2-й прогон с индексом в BEGIN…ROLLBACK):
--
--   1) osm_poi_ekb (POI в 1км, parcels.py:1488 «сек.3», 1× на analyze):
--        ДО:    Seq Scan, Rows Removed by Filter 4824, 538 buf,  ~36 ms
--        ПОСЛЕ: Index Scan на (geom::geography), 12 buf,       ~0.2 ms  (≈190x)
--      Индекс ~384 кБ, build 28 мс.
--
--   2) osm_noise_sources_ekb (шум/гидрология/инж.сети, parcels.py:1773/1840/1889, 4× на analyze):
--        ДО:    Parallel Seq Scan, Rows Removed 5122, 1701 buf, ~92 ms КАЖДЫЙ
--        ПОСЛЕ: Index Scan на (geom::geography), 176 buf,        ~3.8 ms  (≈24x)
--      Индекс ~816 кБ, build 55 мс. Кумулятивно ~4×88 ≈ 350 мс с одного analyze.
--      NB: geotech-запрос (parcels.py:779, source_type='industrial') уже идёт по
--        osm_noise_sources_ekb_source_idx (селективный source_type) — этот индекс его не
--        ухудшает (планировщик выберет дешевле из двух).
--
--   3) cad_buildings (_neighbors_summary соседи в 100м, parcels.py:612, 1× на analyze):
--        ДО:    Parallel Seq Scan, Rows Removed 12670, 11235 buf, ~314 ms
--        ПОСЛЕ: Index Scan на (geom::geography), 18 buf,           ~0.3 ms  (≈1000x)
--      Индекс ~2.9 МБ, build 338 мс. САМЫЙ крупный единичный выигрыш analyze-пути.
--      overlap_rows CTE (ST_Intersects(b.geom, wkt), geometry) по-прежнему обслуживается
--        существующим cad_buildings_geom_gist — без регрессии.
--
--   4) objective_lots covering (obj_pricing CTE в competitor-блоке, parcels.py:1614, 1× на analyze):
--        Запрос агрегирует AVG(price)/AVG(area)/COUNT(is_sold) по ol.project_name =
--        cm.objective_complex_name (objective_complex_mapping, 286 строк).
--        ДО:    Parallel Seq Scan ВСЕХ objective_lots, 215761 buf (~1.7 ГБ), Hash Join,
--               warm ~493 ms (cold ~681 ms).
--        ПОСЛЕ: Nested Loop + Index Only Scan на (project_name) INCLUDE(price,area,is_sold),
--               286 проб по имени, 16586 buf, warm ~355 ms  (≈1.4x, −13x buffers).
--      Индекс ~74 МБ, build 1.75 c. Существующий objective_lots_project_idx
--        (project_name, snapshot_date DESC) НЕ покрывает payload-колонки → не давал
--        Index Only Scan; этот — covering, поэтому не дубль.
--      ОГРАНИЧЕНИЕ: запрос всё равно агрегирует ВСЕ лоты ВСЕХ маппингов (не только
--        ~245 конкурентов в 3км). Полноценный фикс — переписать obj_pricing с
--        пушдауном domrf_obj_id IN (nearby) — это backend-engineer (правка SQL в
--        parcels.py), вне scope index-аудита. Индекс — выигрыш «здесь и сейчас» без
--        правки кода. Heap Fetches ~64k остаются (visibility map тёплый после VACUUM;
--        objective_lots dead-tup 1.25% — well-vacuumed).
--
-- ЧТО НЕ ДОБАВЛЕНО (доказанно не нужно):
--   • rosreestr_deals (market_trend, parcels.py:2211): warm ~254 ms, уже partition-pruned
--     (5 subplans removed) + per-partition BitmapAnd по 3 индексам. Композитный
--     (region_code,doc_type,realestate_type_code,period_start_date) пришлось бы строить на
--     ~20+ партициях, каждая ACCESS EXCLUSIVE на ПИШУЩЕЙСЯ таблице → высокий lock-риск,
--     несоразмерный ~2x-выигрышу. Партиционированную таблицу под индекс-аудит не трогаем.
--   • pipeline-блок (parcels.py:1663): warm ~37 мс, корр. подзапрос уже идёт по
--     objective_lots_project_idx (Bitmap Index Scan). Индекс не нужен.
--   • ekburg_construction_permits (LEFT(cadastral_number,…) prefix): уже покрыт
--     idx_ekburg_permits_cad_cpat (text_pattern_ops).
--   • geom-resolution UNION (parcels.py:1334): 15 мс, всё по PK index scan.
--   • district_price_block (parcels.py:2123): уже покрыт idx_objective_lots_district_price (153).
--
-- LOCK-ПРОФИЛЬ: обычный (НЕ CONCURRENTLY) CREATE INDEX берёт SHARE на таблицу — блокирует
--   ТОЛЬКО writes (loaders/objective_etl/NSPD on-demand fetch), читателей analyze НЕ
--   блокирует. CONCURRENTLY здесь невозможен: deploy.yml гоняет файл в транзакции
--   (BEGIN/COMMIT), а CREATE INDEX CONCURRENTLY в tx запрещён. Суммарный build ~2.2 c
--   (74 МБ objective_lots — доминирует, как и в 153 на той же таблице). Приемлемо в
--   deploy-окне (тот же класс риска, что 153/87/97). Не запускать одновременно с тяжёлым ETL.
--
-- Dependencies: osm_poi_ekb, osm_noise_sources_ekb, cad_buildings, objective_lots,
--   objective_complex_mapping (все существуют). PostgreSQL 16.4 + PostGIS 3.4.
-- Idempotent: CREATE INDEX IF NOT EXISTS (re-apply безопасен).
-- Applied: автоматически через deploy.yml в NN-порядке (трекер _schema_migrations).
-- Backend code change НЕ требуется — индексы прозрачны для существующих запросов.

BEGIN;

-- 1) POI в 1км (analyze сек.3) — geography-предикат ST_DWithin(geom::geography,…,1000).
CREATE INDEX IF NOT EXISTS idx_osm_poi_ekb_geom_geog
    ON osm_poi_ekb USING gist ((geom::geography));

COMMENT ON INDEX idx_osm_poi_ekb_geom_geog IS
    'Geography GIST для analyze POI-блока (#157): ST_DWithin(geom::geography,…,1000м). '
    'Плоский osm_poi_ekb_geom_gist не обслуживает geography-cast → был Seq Scan ~36мс, '
    'стал Index Scan ~0.2мс. См. parcels.py:1488.';

-- 2) Шумовые источники в 2км (analyze сек.7/9c/9d) — geography ST_DWithin, 4× на analyze.
CREATE INDEX IF NOT EXISTS idx_osm_noise_sources_ekb_geom_geog
    ON osm_noise_sources_ekb USING gist ((geom::geography));

COMMENT ON INDEX idx_osm_noise_sources_ekb_geom_geog IS
    'Geography GIST для analyze noise/hydro/utility-блоков (#157): '
    'ST_DWithin(geom::geography,…,2000м), 4 запроса на analyze. Был Parallel Seq Scan '
    '~92мс каждый, стал Index Scan ~3.8мс. См. parcels.py:1773/1840/1889.';

-- 3) Соседние здания в 100м (_neighbors_summary) — geography ST_DWithin на 88МБ-таблице.
CREATE INDEX IF NOT EXISTS idx_cad_buildings_geom_geog
    ON cad_buildings USING gist ((geom::geography));

COMMENT ON INDEX idx_cad_buildings_geom_geog IS
    'Geography GIST для analyze _neighbors_summary (#157): ST_DWithin(geom::geography,…,100м). '
    'Плоский cad_buildings_geom_gist обслуживает ST_Intersects(geom,…) (overlap_rows), но не '
    'geography-cast → был Parallel Seq Scan ~314мс, стал Index Scan ~0.3мс. См. parcels.py:612.';

-- 4) Covering-index для obj_pricing competitor-агрегации (analyze сек.5).
CREATE INDEX IF NOT EXISTS idx_objective_lots_pname_pricing
    ON objective_lots (project_name)
    INCLUDE (price_per_m2_rub, area_pd, is_sold);

COMMENT ON INDEX idx_objective_lots_pname_pricing IS
    'Covering-index для analyze competitor obj_pricing (#157): агрегаты по '
    'project_name = objective_complex_name через Index Only Scan (286 проб), без '
    'Parallel Seq Scan ~1.7ГБ. Существующий objective_lots_project_idx без payload не '
    'давал Index Only Scan. Long-term: пушдаун domrf_obj_id IN(nearby) в SQL (backend). '
    'См. parcels.py:1614.';

COMMIT;