optimize_query/not_optimize_query/query.sql - master - Faykrill/DBA

optimize_query/not_optimize_query/query.sql
141 строка5 KB
Faykrill
create: generate_table.sql, query.sql, new_query.sql, Readme.md
16 май 2026, 07:24
16 май 2026, 07:247ddd390
EXPLAIN (ANALYZE, BUFFERS, TIMING)
WITH 
-- Добавляем оконные функции для усложнения
windowed_data AS (
    SELECT 
        *,
        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
)
SELECT 
    t1.id,
    t2.id,
    
    -- Множество вычислений с разными типами
    t1.field_int_1,
    t2.field_int_2,
    t1.field_int_1 + t2.field_int_2 as sum_int,
    t1.field_int_1 * t2.field_int_2 as product_int,
    (t1.field_int_1 % 100) + (t2.field_int_2 % 100) as mod_sum,
    
    t1.field_bigint_1,
    t2.field_bigint_1,
    t1.field_bigint_1 - t2.field_bigint_1 as diff_bigint,
    
    t1.field_numeric_1,
    t2.field_numeric_1,
    t1.field_numeric_1 * t2.field_numeric_1 as product_numeric,
    
    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,
    
    t1.field_bool_1,
    t2.field_bool_2,
    t1.field_bool_1 AND t2.field_bool_2 as and_bool,
    t1.field_bool_1 OR t2.field_bool_2 as or_bool,
    NOT t1.field_bool_1 as not_bool,
    
    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,
    
    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,
    
    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,
    -- Убираем ROUND для float
    (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,
    
    -- Функции из CTE
    t1.rn,
    t2.rnk,
    t1.lag_int,
    t2.lead_int,
    t1.running_sum as running_sum_t1,
    t2.running_sum as running_sum_t2,
    
    -- Случайные значения
    random() as rand_val,
    
    -- Математические комбинации
    (t1.field_int_1 + 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.field_int_2, 2)) as distance,
    tanh(t1.field_float_1 / 100) as tanh_val,
    
    -- Условные выражения
    CASE 
        WHEN t1.field_int_1 > t2.field_int_2 THEN 'Greater'
        WHEN t1.field_int_1 < 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.field_int_2, t1.field_int_1 % 100] as int_array,
    ARRAY[t1.field_float_1, t2.field_float_2, random()] as float_array,
    
    ('{"id1":' || t1.id || ',"id2":' || t2.id || ',"diff":' || ABS(t1.field_float_1 - t2.field_float_2) || '}')::jsonb as json_data
    
FROM windowed_data t1
CROSS JOIN windowed_data t2
WHERE t1.id <> t2.id
  AND t1.field_bool_1 = t2.field_bool_2
  AND t1.field_bool_2 = t2.field_bool_3
  AND t1.field_int_1 % 3 = t2.field_int_2 % 3
  AND t1.field_int_2 % 5 = t2.field_int_1 % 5
  AND t1.field_int_1 % 7 = t2.field_int_2 % 7
  AND t1.field_float_1 > 10
  AND t2.field_float_2 < 9000
  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.id % 100 < 50
  AND t2.id % 100 > 20
  AND t1.rn < 1000
  AND t2.rnk < 1000
  AND ABS(t1.field_float_1 - t2.field_float_2) > 0.01
  AND (t1.field_int_1 + t2.field_int_2) % 10 = 0
  AND t1.lag_int IS NOT NULL
  AND t2.lead_int IS NOT NULL
ORDER BY complex_calc DESC, abs_diff ASC, rand_val
LIMIT 100000;