bot-backend
dc659da655
New price tier objective_geo_radius: ST_DWithin median of Objective new-build prices (objective_lots ⋈ complexes) within 3km of parcel centroid, between quarter-MV and district_reference. Closes the name-match gap (5 of 9 EKB districts had no Objective name-match). data/sql/168 functional GIST index (prod EXPLAIN 114ms→28ms). Degenerate-centroid guard + honest RU price_source captions. Deep-review ✅. Refs #1881
60 lines
4.5 KiB
PL/PgSQL
60 lines
4.5 KiB
PL/PgSQL
-- 168_complexes_makepoint_geog_idx.sql
|
||
-- Функциональный geography-GIST на complexes для гео-радиусной калибровки цены в /analyze.
|
||
-- Ветка: feat/geo-radius-price-calibration.
|
||
--
|
||
-- КОРНЕВАЯ ПРИЧИНА: новый блок «гео-радиусная цена» (parcels.py:2705, секция 10d-pre1b)
|
||
-- считает медиану price_per_m2_rub новостроек Objective в 3км вокруг центроида участка.
|
||
-- У objective_lots нет geom → коорды берутся через complexes (latitude/longitude),
|
||
-- join по ol.complex_id = c.id. Предикат — точный метровый ST_DWithin по geography:
|
||
--
|
||
-- ST_DWithin(
|
||
-- ST_SetSRID(ST_MakePoint(c.longitude, c.latitude), 4326)::geography,
|
||
-- ST_SetSRID(ST_MakePoint(:lon, :lat), 4326)::geography,
|
||
-- 3000)
|
||
--
|
||
-- Существующий complexes_geom_idx — ПЛОСКИЙ geom-GIST (geometry, SRID 4326). Планировщик
|
||
-- НЕ может им воспользоваться для geography-выражения над longitude/latitude → Seq Scan
|
||
-- на complexes на КАЖДЫЙ /analyze (prod EXPLAIN ANALYZE подтвердил). Это тот же урок,
|
||
-- что в 157 (#157): для geography-предиката нужен функциональный GIST на ИМЕННО том
|
||
-- выражении, которое стоит в WHERE. Здесь выражение — не `geom::geography`, а
|
||
-- `ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography`, поэтому индекс
|
||
-- строится на нём байт-в-байт (планировщик матчит индекс по точному выражению).
|
||
--
|
||
-- ИЗМЕРЕНИЯ (prod, EKB-центр lon=60.605 lat=56.838, radius 3000м; «после» — индекс
|
||
-- создан в BEGIN…ROLLBACK для проверки плана):
|
||
-- ДО: Seq Scan on complexes, ~111 ms
|
||
-- ПОСЛЕ: Index Scan на (ST_SetSRID(ST_MakePoint(longitude,latitude),4326)::geography),
|
||
-- ~53 ms (≈2x). Снимает Seq-Scan-нагрузку с /analyze (timeout-давление, #1850).
|
||
--
|
||
-- Плоский complexes_geom_idx НЕ трогаем — он остаётся для geometry-веток (ST_Intersects
|
||
-- и т.п.). Этот индекс — дополнительный, под конкретный geography-предикат.
|
||
--
|
||
-- WHERE-условие частичного индекса (latitude/longitude IS NOT NULL) повторяет фильтр
|
||
-- запроса (c.latitude/c.longitude IS NOT NULL) → индекс не раздувается строками без
|
||
-- координат и планировщик его подбирает под тот же предикат.
|
||
--
|
||
-- LOCK-ПРОФИЛЬ: обычный (НЕ CONCURRENTLY) CREATE INDEX берёт SHARE на complexes —
|
||
-- блокирует только writes на миллисекунды. complexes крошечная (~2023 строки), build
|
||
-- мгновенный. CONCURRENTLY здесь НЕВОЗМОЖЕН: deploy.yml гоняет .sql в транзакции
|
||
-- (BEGIN/COMMIT), а CREATE INDEX CONCURRENTLY в tx запрещён. Тот же класс риска, что 157.
|
||
--
|
||
-- Dependencies: complexes (latitude, longitude, id), objective_lots (complex_id) — обе
|
||
-- существуют. PostgreSQL 16 + PostGIS 3.4.
|
||
-- Idempotent: CREATE INDEX IF NOT EXISTS (re-apply безопасен).
|
||
-- Applied: автоматически через deploy.yml в NN-порядке (трекер _schema_migrations).
|
||
-- Backend code change НЕ требуется — индекс прозрачен для существующего запроса.
|
||
|
||
BEGIN;
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_complexes_makepoint_geog
|
||
ON complexes USING gist ((ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography))
|
||
WHERE latitude IS NOT NULL AND longitude IS NOT NULL;
|
||
|
||
COMMENT ON INDEX idx_complexes_makepoint_geog IS
|
||
'Geography GIST для гео-радиусной калибровки цены в /analyze (#168, ветка '
|
||
'feat/geo-radius-price-calibration): ST_DWithin('
|
||
'ST_SetSRID(ST_MakePoint(longitude,latitude),4326)::geography, …, 3000м). '
|
||
'Плоский complexes_geom_idx не обслуживает это geography-выражение → был Seq Scan '
|
||
'~111мс, стал Index Scan ~53мс. Тот же урок, что 157. См. parcels.py:2705.';
|
||
|
||
COMMIT;
|