optimize_query/optimized_query/new_query.sql - master - Faykrill/DBA

optimize_query/optimized_query/new_query.sql
219 строк8 KB
Faykrill
create: generate_table.sql, query.sql, new_query.sql, Readme.md
16 май 2026, 07:24
16 май 2026, 07:247ddd390
-- ============================================
-- ОПТИМИЗАЦИЯ С ВРЕМЕННЫМИ ТАБЛИЦАМИ
-- ============================================

-- Шаг 1: Создаем индексы
DROP INDEX IF EXISTS idx_float1;
DROP INDEX IF EXISTS idx_int1;
DROP INDEX IF EXISTS idx_int2;
DROP INDEX IF EXISTS idx_bool1;
DROP INDEX IF EXISTS idx_bool2;

CREATE INDEX idx_float1 ON test_data_33_fields(field_float_1);
CREATE INDEX idx_int1 ON test_data_33_fields(field_int_1);
CREATE INDEX idx_int2 ON test_data_33_fields(field_int_2);
CREATE INDEX idx_bool1 ON test_data_33_fields(field_bool_1);
CREATE INDEX idx_bool2 ON test_data_33_fields(field_bool_2);

-- Шаг 2: Создаем временные таблицы
-- Временная таблица t1 с предварительной фильтрацией
DROP TABLE IF EXISTS temp_filtered_t1;
CREATE TEMP TABLE temp_filtered_t1 AS
SELECT 
    id as id1,
    field_int_1,
    field_int_2,
    field_bigint_1,
    field_numeric_1,
    field_text_1,
    field_bool_1,
    field_bool_2,
    field_date_1,
    field_ts_1,
    field_float_1,
    (field_int_1 % 3) as mod3,
    (field_int_1 % 7) as mod7,
    (field_int_1 % 10) as mod10,
    ROW_NUMBER() OVER (PARTITION BY field_bool_1 ORDER BY id) as rn,
    RANK() OVER (ORDER BY field_float_1) as rnk,
    LAG(field_int_1, 1) OVER (ORDER BY id) as lag_int,
    LEAD(field_int_2, 1) OVER (ORDER BY id) as lead_int,
    SUM(field_int_1) OVER (ORDER BY id) as running_sum
FROM test_data_33_fields
WHERE field_float_1 > 10
  AND field_int_1 > 100
  AND id % 100 < 50;

-- Временная таблица t2 с предварительной фильтрацией
DROP TABLE IF EXISTS temp_filtered_t2;
CREATE TEMP TABLE temp_filtered_t2 AS
SELECT 
    id as id2,
    field_int_1 as t2_field_int_1,
    field_int_2 as t2_field_int_2,
    field_bigint_1 as t2_field_bigint_1,
    field_numeric_1 as t2_field_numeric_1,
    field_text_2,
    field_bool_2 as t2_field_bool_2,
    field_bool_3,
    field_date_2,
    field_ts_2,
    field_float_2,
    (field_int_2 % 3) as mod3,
    (field_int_2 % 5) as mod5,
    (field_int_2 % 7) as mod7,
    (field_int_2 % 10) as mod10,
    ROW_NUMBER() OVER (PARTITION BY field_bool_2 ORDER BY id) as rn2,
    RANK() OVER (ORDER BY field_float_2) as rnk2,
    LAG(field_int_1, 1) OVER (ORDER BY id) as lag_int2,
    LEAD(field_int_2, 1) OVER (ORDER BY id) as lead_int2,
    SUM(field_int_1) OVER (ORDER BY id) as running_sum2
FROM test_data_33_fields
WHERE field_float_2 < 9000
  AND field_int_2 < 900
  AND id % 100 > 20;

-- Временная таблица с партициями
DROP TABLE IF EXISTS temp_partitions;
CREATE TEMP TABLE temp_partitions AS
SELECT generate_series(0, 9) as mod_val;

-- Шаг 4: Анализируем временные таблицы для оптимизатора
ANALYZE temp_filtered_t1;
ANALYZE temp_filtered_t2;
ANALYZE temp_partitions;

-- Шаг 5: Выполняем основной запрос с использованием временных таблиц
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT 
    t1.id1,
    t2.id2,
    
    -- INT поля
    t1.field_int_1,
    t2.t2_field_int_2,
    t1.field_int_1 + t2.t2_field_int_2 as sum_int,
    t1.field_int_1 * t2.t2_field_int_2 as product_int,
    (t1.field_int_1 % 100) + (t2.t2_field_int_2 % 100) as mod_sum,
    
    -- BIGINT поля
    t1.field_bigint_1,
    t2.t2_field_bigint_1,
    t1.field_bigint_1 - t2.t2_field_bigint_1 as diff_bigint,
    
    -- NUMERIC поля
    t1.field_numeric_1,
    t2.t2_field_numeric_1,
    t1.field_numeric_1 * t2.t2_field_numeric_1 as product_numeric,
    
    -- TEXT поля
    t1.field_text_1,
    t2.field_text_2,
    UPPER(t1.field_text_1) as upper_text,
    LOWER(t2.field_text_2) as lower_text,
    LENGTH(t1.field_text_1) as len1,
    LENGTH(t2.field_text_2) as len2,
    POSITION('_' IN t1.field_text_1) as underscore_pos,
    SUBSTRING(t2.field_text_2 FROM 1 FOR 5) as substring,
    REPLACE(t1.field_text_1, 'Text', 'Data') as replaced_text,
    
    -- BOOLEAN поля
    t1.field_bool_1,
    t2.t2_field_bool_2,
    t1.field_bool_1 AND t2.t2_field_bool_2 as and_bool,
    t1.field_bool_1 OR t2.t2_field_bool_2 as or_bool,
    NOT t1.field_bool_1 as not_bool,
    
    -- DATE поля
    t1.field_date_1,
    t2.field_date_2,
    (t2.field_date_2 - t1.field_date_1) as days_diff,
    EXTRACT(YEAR FROM t1.field_date_1) as year1,
    EXTRACT(MONTH FROM t2.field_date_2) as month2,
    EXTRACT(DAY FROM t1.field_date_1) as day1,
    AGE(t2.field_date_2, t1.field_date_1) as age_diff,
    DATE_TRUNC('month', t1.field_date_1) as month_trunc,
    
    -- TIMESTAMP поля
    t1.field_ts_1,
    t2.field_ts_2,
    EXTRACT(EPOCH FROM (t2.field_ts_2 - t1.field_ts_1)) as seconds_diff,
    t1.field_ts_1 + interval '1 day' as plus_day,
    t2.field_ts_2 - interval '1 hour' as minus_hour,
    
    -- FLOAT поля
    t1.field_float_1,
    t2.field_float_2,
    ABS(t1.field_float_1 - t2.field_float_2) as abs_diff,
    POWER(t1.field_float_1, 2) as power2,
    SQRT(ABS(t2.field_float_2)) as sqrt_val,
    SIN(t1.field_float_1) as sin_val,
    COS(t2.field_float_2) as cos_val,
    LOG(ABS(t1.field_float_1) + 1) as log_val,
    EXP(t1.field_float_1 / 100) as exp_val,
    (t1.field_float_1)::numeric(10,2) as rounded1,
    CEIL(t2.field_float_2) as ceil_val,
    FLOOR(t1.field_float_1) as floor_val,
    TRUNC(t2.field_float_2) as trunc_val,
    
    -- Оконные функции
    t1.rn,
    t2.rnk2,
    t1.lag_int,
    t2.lead_int2,
    t1.running_sum as running_sum_t1,
    t2.running_sum2 as running_sum_t2,
    
    -- Случайные значения
    random() as rand_val,
    
    -- Математические комбинации
    (t1.field_int_1 + t2.t2_field_int_2) * t1.field_float_1 / (t2.field_float_2 + 1) as complex_calc,
    sqrt(power(t1.field_int_1, 2) + power(t2.t2_field_int_2, 2)) as distance,
    tanh(t1.field_float_1 / 100) as tanh_val,
    
    -- Условные выражения
    CASE 
        WHEN t1.field_int_1 > t2.t2_field_int_2 THEN 'Greater'
        WHEN t1.field_int_1 < t2.t2_field_int_2 THEN 'Less'
        ELSE 'Equal'
    END as comparison,
    
    CASE
        WHEN t1.field_float_1 > 1000 THEN 'High'
        WHEN t1.field_float_1 > 100 THEN 'Medium'
        ELSE 'Low'
    END as category,
    
    COALESCE(t1.field_text_1, 'NULL1') as coalesced_text1,
    NULLIF(t2.field_text_2, 'Text_0') as null_if_text,
    
    -- Массивы
    ARRAY[t1.field_int_1, t2.t2_field_int_2, t1.field_int_1 % 100] as int_array,
    ARRAY[t1.field_float_1, t2.field_float_2, random()] as float_array,
    
    -- JSON
    ('{"id1":' || t1.id1 || ',"id2":' || t2.id2 || ',"diff":' || ABS(t1.field_float_1 - t2.field_float_2) || '}')::jsonb as json_data
    
FROM temp_partitions p
INNER JOIN temp_filtered_t1 t1 ON t1.mod10 = p.mod_val
INNER JOIN temp_filtered_t2 t2 ON t2.mod10 = p.mod_val
    AND t1.field_bool_1 = t2.t2_field_bool_2
    AND t1.field_bool_2 = t2.field_bool_3
    AND t1.mod3 = t2.mod3
    AND t1.mod7 = t2.mod7
WHERE t1.id1 <> t2.id2
  AND t1.field_int_2 % 5 = t2.mod5
  AND t1.field_float_1 * t2.field_float_2 < 1000000
  AND t1.field_date_1 > current_date - interval '90 days'
  AND t2.field_date_2 < current_date
  AND t1.field_text_1 ~ '.*[0-9]{3}.*'
  AND t2.field_text_2 ILIKE '%value%'
  AND t1.rn < 1000
  AND t2.rnk2 < 1000
  AND ABS(t1.field_float_1 - t2.field_float_2) > 0.01
  AND (t1.field_int_1 + t2.t2_field_int_2) % 10 = 0
  AND t1.lag_int IS NOT NULL
  AND t2.lead_int2 IS NOT NULL
ORDER BY complex_calc DESC, abs_diff ASC, rand_val
LIMIT 100000;