sql_labs/lab5_indexes.sql - master - nmktth/web-development-sem-4

sql_labs/lab5_indexes.sql
187 строк9 KB
artem
Небольшие исправления SQL-лабораторных
15 июн 2026, 01:49
15 июн 2026, 01:49c75995d
-- =========================================================================
-- ЛАБОРАТОРНАЯ №5: МАССИВНЫЙ ПОСЕВ ДАННЫХ, ИНДЕКСЫ И КУРСОРЫ
-- =========================================================================

-- СЛУЖЕБНЫЙ БЛОК 1: Подготовка тестовых данных
-- Тут мы наваливаем кучу данных, чтобы индексы реально начали работать.
-- На 10 строчках EXPLAIN ничего не покажет, поэтому делаем красиво.
CREATE OR REPLACE PROCEDURE lab5_seed_benchmark_data()
LANGUAGE plpgsql
AS $$
BEGIN
    IF EXISTS (SELECT 1 FROM realty_advertisement WHERE title LIKE 'BENCH-AD-%') THEN
        RAISE NOTICE 'Тестовые данные уже в базе, не дублируем';
        RETURN;
    END IF;

    -- Регаем 1000 тестовых юзеров
    INSERT INTO realty_user (username, password, email, first_name, last_name, middle_name, is_active, is_staff, is_superuser, date_joined, role)
    SELECT
        'bench_user_' || gs, 'pbkdf2_sha256$260000$dummy$hash', 'bench' || gs || '@example.com', 'Имя ' || gs, 'Фамилия ' || gs, 'Отчество ' || gs, true, false, false, NOW() - MAKE_INTERVAL(days => (gs % 365)), 'user'
    FROM generate_series(1, 1000) AS gs;

    -- Заливаем 5000 объявлений для плотности
    INSERT INTO realty_advertisement (user_id, title, description, type, deal_type, price, deposit, address, city, is_active, views_count, created_at, updated_at)
    SELECT
        (SELECT id FROM realty_user WHERE username = 'bench_user_' || ((gs % 1000) + 1)),
        'BENCH-AD-' || LPAD(gs::TEXT, 5, '0'), 'Описание тестового объявления ' || gs, (ARRAY['flat', 'room', 'house', 'bed'])[1 + (gs % 4)], (ARRAY['rent_out', 'look_for'])[1 + (gs % 2)], (15000 + (gs % 100) * 500)::NUMERIC(10, 2), (5000 + (gs % 50) * 500)::NUMERIC(10, 2), 'Улица Тестовая, ' || gs, (ARRAY['Москва', 'Санкт-Петербург', 'Казань', 'Екатеринбург'])[1 + (gs % 4)], CASE WHEN gs % 10 = 0 THEN false ELSE true END, gs % 500, NOW() - MAKE_INTERVAL(days => (gs % 365), hours => (gs % 24)), NOW() - MAKE_INTERVAL(days => (gs % 30))
    FROM generate_series(1, 5000) AS gs;

    RAISE NOTICE 'Ок, 5000 объявлений залито. Можно тестить индексы.';
END;
$$;

CALL lab5_seed_benchmark_data();

-- СЛУЖЕБНЫЙ БЛОК 2: Таблица для массивов и JSONB
-- Делаем отдельную табличку под поисковые профили (теги и атрибуты).
CREATE TABLE IF NOT EXISTS realty_search_profiles (
    profile_id SERIAL PRIMARY KEY,
    advertisement_id BIGINT REFERENCES realty_advertisement(id) ON DELETE CASCADE,
    search_tags TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
    attributes JSONB NOT NULL DEFAULT '{}'::JSONB,
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Повторный запуск лабораторной не должен плодить одинаковые профили
-- для одного объявления, поэтому оставляем одну запись и фиксируем уникальность.
DELETE FROM realty_search_profiles p
USING realty_search_profiles d
WHERE p.advertisement_id = d.advertisement_id
  AND p.profile_id > d.profile_id;

CREATE UNIQUE INDEX IF NOT EXISTS ux_realty_search_profiles_advertisement_id
ON realty_search_profiles (advertisement_id);

-- Переливает данные из объявлений в поисковые профили.
CREATE OR REPLACE PROCEDURE lab5_sync_search_profiles()
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO realty_search_profiles (advertisement_id, search_tags, attributes, updated_at)
    SELECT
        a.id,
        ARRAY_REMOVE(ARRAY[LOWER(a.type), LOWER(a.deal_type), LOWER(a.city), CASE WHEN a.price >= 100000 THEN 'premium' WHEN a.price >= 40000 THEN 'middle' ELSE 'budget' END, CASE WHEN a.is_active THEN 'active' ELSE 'archived' END], NULL),
        jsonb_build_object('city', a.city, 'type', a.type, 'price_band', CASE WHEN a.price >= 100000 THEN 'high' WHEN a.price >= 40000 THEN 'middle' ELSE 'low' END, 'views', a.views_count, 'is_benchmark', (a.title LIKE 'BENCH-AD-%')),
        NOW()
    FROM realty_advertisement a
    ON CONFLICT (advertisement_id) DO UPDATE SET
        search_tags = EXCLUDED.search_tags,
        attributes = EXCLUDED.attributes,
        updated_at = EXCLUDED.updated_at;
END;
$$;

CALL lab5_sync_search_profiles();


-- =========================================================================
-- ЧАСТЬ 1: КУРСОРЫ (Сложные, с фильтрацией и параметрами)
-- =========================================================================

-- 1. Курсор для премиум хат. Читает данные потихоньку, чтобы не сожрать оперативку.
CREATE OR REPLACE FUNCTION lab5_open_expensive_ads_cursor(
    p_cursor_name TEXT DEFAULT 'lab5_expensive_ads_cursor',
    p_from_timestamp TIMESTAMP DEFAULT NOW() - INTERVAL '30 days',
    p_types TEXT[] DEFAULT ARRAY['flat', 'house']::TEXT[]
)
RETURNS REFCURSOR
LANGUAGE plpgsql
AS $$
DECLARE v_cursor REFCURSOR := p_cursor_name;
BEGIN
    OPEN v_cursor FOR
    SELECT id, title, type, city, price, created_at
    FROM realty_advertisement
    WHERE created_at >= p_from_timestamp AND type = ANY(p_types) AND price > 50000
    ORDER BY price DESC, created_at DESC;
    RETURN v_cursor;
END;
$$;

-- 2. Полнотекстовый поиск через курсор. Помогает искать умным способом (TSVECTOR).
CREATE OR REPLACE FUNCTION lab5_open_fts_ads_cursor(
    p_cursor_name TEXT DEFAULT 'lab5_fts_ads_cursor',
    p_search_text TEXT DEFAULT 'центр'
)
RETURNS REFCURSOR
LANGUAGE plpgsql
AS $$
DECLARE v_cursor REFCURSOR := p_cursor_name;
BEGIN
    OPEN v_cursor FOR
    SELECT id, title, description, ts_rank(to_tsvector('russian', COALESCE(title, '') || ' ' || COALESCE(description, '')), websearch_to_tsquery('russian', p_search_text)) AS rank_value
    FROM realty_advertisement
    WHERE to_tsvector('russian', COALESCE(title, '') || ' ' || COALESCE(description, '')) @@ websearch_to_tsquery('russian', p_search_text)
    ORDER BY rank_value DESC, id;
    RETURN v_cursor;
END;
$$;

-- 3. Курсор для поиска по JSON и массивам тегов.
CREATE OR REPLACE FUNCTION lab5_open_profile_filter_cursor(
    p_cursor_name TEXT DEFAULT 'lab5_profile_filter_cursor',
    p_tags TEXT[] DEFAULT ARRAY['middle', 'moscow']::TEXT[],
    p_json_filter JSONB DEFAULT '{"price_band":"middle"}'::JSONB
)
RETURNS REFCURSOR
LANGUAGE plpgsql
AS $$
DECLARE v_cursor REFCURSOR := p_cursor_name;
BEGIN
    OPEN v_cursor FOR
    SELECT p.advertisement_id, a.title, p.search_tags, p.attributes
    FROM realty_search_profiles p
    JOIN realty_advertisement a ON a.id = p.advertisement_id
    WHERE p.search_tags && p_tags AND p.attributes @> p_json_filter
    ORDER BY p.advertisement_id;
    RETURN v_cursor;
END;
$$;


-- =========================================================================
-- ЧАСТЬ 2: ИНДЕКСЫ 
-- =========================================================================

-- Служебная процедура: убивает все индексы.
-- Помогает для тестов: удаляем -> делаем EXPLAIN (смотрим как тупит) -> создаем -> снова EXPLAIN (смотрим как летает).
CREATE OR REPLACE PROCEDURE lab5_drop_indexes()
LANGUAGE plpgsql
AS $$
BEGIN
    DROP INDEX IF EXISTS idx_lab5_ads_price_filter;
    DROP INDEX IF EXISTS idx_lab5_ads_city_type;
    DROP INDEX IF EXISTS idx_lab5_ads_fts;
    DROP INDEX IF EXISTS idx_lab5_profiles_tags_gin;
    DROP INDEX IF EXISTS idx_lab5_profiles_attr_gin;
    DROP INDEX IF EXISTS idx_lab5_users_date;
END;
$$;

-- Поднимаем все нужные индексы, в том числе жирные GIN-индексы для JSON'а.
CREATE OR REPLACE PROCEDURE lab5_create_indexes()
LANGUAGE plpgsql
AS $$
BEGIN
    -- Обычные B-Tree для быстрых сортировок
    CREATE INDEX IF NOT EXISTS idx_lab5_ads_price_filter ON realty_advertisement (price, created_at) WHERE is_active = true;
    CREATE INDEX IF NOT EXISTS idx_lab5_ads_city_type ON realty_advertisement (city, type);
    CREATE INDEX IF NOT EXISTS idx_lab5_users_date ON realty_user (date_joined);

    -- GIN для крутого полнотекстового поиска
    CREATE INDEX IF NOT EXISTS idx_lab5_ads_fts ON realty_advertisement USING GIN (to_tsvector('russian', COALESCE(title, '') || ' ' || COALESCE(description, '')));

    -- GIN для массивов и JSONB (иначе поиск по json будет сканировать всю таблицу)
    CREATE INDEX IF NOT EXISTS idx_lab5_profiles_tags_gin ON realty_search_profiles USING GIN (search_tags);
    CREATE INDEX IF NOT EXISTS idx_lab5_profiles_attr_gin ON realty_search_profiles USING GIN (attributes jsonb_path_ops);

    -- Дергаем статистику, чтобы оптимизатор Postgres очнулся
    ANALYZE realty_advertisement;
    ANALYZE realty_user;
    ANALYZE realty_search_profiles;
END;
$$;

CALL lab5_drop_indexes();
CALL lab5_create_indexes();