Домашнее задание - Практическое занятие №8: поиск по одному и нескольким полям

Выполняйте только если преподаватель назначил это задание. Срок сдачи сообщит преподаватель.

Данная работа представляет собой резервное дистанционное практическое задание по дисциплине «ОП.08 Основы проектирования баз данных» (СИЭУиП). Все поля сохраняются локально в вашем браузере. После заполнения экспортируйте результат в PDF или передайте преподавателю.

Кейс: Проектирование поискового фильтра агрегатора коммерческой недвижимости

Вы являетесь инженером баз данных федерального портала коммерческой недвижимости. В базе данных развернута таблица commercial_realty объемом 2 500 000 объектов. 

CREATE TABLE commercial_realty (
    object_id BIGSERIAL PRIMARY KEY,
    cadastral_number VARCHAR(30) NOT NULL UNIQUE,
    city VARCHAR(80) NOT NULL,
    district VARCHAR(80) NOT NULL,
    deal_type VARCHAR(10) NOT NULL, -- 'RENT' (аренда) или 'SALE' (продажа)
    area_sqm NUMERIC(10,2) NOT NULL,
    price_rub NUMERIC(14,2) NOT NULL,
    status VARCHAR(20) NOT NULL,    -- 'ACTIVE', 'RESERVED', 'ARCHIVED'
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    description TEXT
);

Бизнес-требование: Пользователь сайта открывает форму расширенного поиска, где может указать город, район, тип сделки ('RENT' или 'SALE'), а также задать диапазоны цены (от и до) и площади (от и до). Запрос должен выполняться строго быстрее 5 миллисекунд даже под высокой нагрузкой.

Задание 1. Разработка комплексного поискового фильтра и составного индекса (2 балла)

Напишите SQL-скрипт создания оптимального индекса для обслуживания формы расширенного фильтра активных объявлений. Обоснуйте порядок колонок с точки зрения правила «Равенства вперед, диапазоны в конец» и концепции частичного индекса (Partial Index). Напишите пример поискового SELECT-запроса.

Задание 2. Оценка производительности: Sequential Scan vs Index Scan (1 балл)

Смоделируйте выполнение запроса через команду EXPLAIN (ANALYZE, BUFFERS). Сравните поведение базы данных в двух сценариях:

  1. При отсутствии составного индекса (вынужденный Sequential Scan).
  2. При использовании спроектированного вами составного частичного B-Tree индекса.

Заполните сравнительную таблицу показателей:

Показатель оптимизатора Сценарий 1: Без индекса (Heap Scan) Сценарий 2: Составной B-Tree индекс
Основной узел плана Sequential Scan Index Scan using idx_realty_...
Объем прочитанных страниц (8 КБ) ~220 000 блоков (1.7 ГБ данных) 3–5 блоков (24–40 КБ)
Время выполнения (Execution Time) 1 850–3 500 мс < 0.7 мс
Строк отсеяно фильтром (Rows Removed) 2 499 985 строк 0 строк (отсечение на уровне дерева)

Задание 3. Полнотекстовый поиск по описанию и блок самопроверки (1 балл)

Пользователи часто ищут объекты по ключевым словам в описании: например, «панорамные окна первая линия отдельный вход».

  1. Почему конструкция WHERE description LIKE '%первая линия%' приведет к деградации производительности сервера?
  2. Напишите DDL-команду создания инвертированного индекса GIN с использованием русской конфигурации стемминга.
  3. Напишите поисковый запрос с оператором @@, находящий объявления со словами «панорамный» и «вход».
Данные сохранены локально