expression_num_or_1_10000.sql

--make sure parallism be available, make memory be big.
SET max_parallel_workers_per_gather = 10;
alter system set work_mem to '4GB';
select pg_reload_conf();

--main test table
drop table if exists test_10000;
create table test_10000 as (
    select (random()*10000)::int x, (random()*1000) y from generate_series(1,1000000) i
    );
vacuum analyze test_10000;

--performance table.
CREATE TABLE perf_10000(QUERY TEXT, num_or bigint, time numeric, or_or_any text, enable_or_transformation bool);
/*
test_query
--or expression query, like:
select * from test where x = 1 or x = 2 or x = 3 or x = 4 or x = 5 or x = 6 or x = 7 or x = 8 or x = 9 or x = 10 or x = 11
*/
DROP TABLE IF EXISTS test_query;
CREATE TABLE test_query(QUERY TEXT, num_or bigint);

--test_query is mainly or expresion.
insert into test_query(num_or, query)
select g1, query
from generate_series(11,500) g1,
lateral (select 'select * from test where x = 1 ' || string_agg('or x = ' || g,' ') as query from generate_series(2,g1) g);

create or replace procedure execute_explain_time_10000(bool) as $$
DECLARE
    rec     record;
    ln      text;
    temp text;
BEGIN
-- $1 refer to enable_or_transformation 
    if $1 is null then
        RAISE EXCEPTION '$1 should not be null';
    end if;

    FOR rec IN SELECT * from test_query
    LOOP
        for ln in execute ('explain (analyze, costs off) '  || rec.query)
        loop
            temp := substring(ln,'(?:Execution Time:\s)([0-9.]*)');
            if temp is not null then
                -- then raise notice 'ln %', temp;
                exit;
            end if;
        end loop;
        insert into perf_10000(query, num_or, time, or_or_any, enable_or_transformation)
        select rec.query, rec.num_or, temp::numeric, 'or'::text, $1;
   END LOOP;
END; $$ LANGUAGE plpgsql;

set enable_or_transformation to on;
call execute_explain_time_10000(true) \watch i=0.1 c=1
call execute_explain_time_10000(true) \watch i=0.1 c=9

set enable_or_transformation to off;
call execute_explain_time_10000(false) \watch i=0.1 c=1
call execute_explain_time_10000(false) \watch i=0.1 c=9

----------------------------------------------
--the results where enable_or_transformation decrease the performance.
select *, (sub2_avg - sub1avg)/sub1avg * 100 as performance_degradation
from
(
    select  sub1.num_or,
            sub1.enable_or_transformation as sub1_enable,
            sub1.avg as sub1avg,
            sub2.enable_or_transformation as sub2_enable,
            sub2.avg as sub2_avg
    from
    (
        select  num_or, enable_or_transformation, avg(time) 
        from    perf_10000 
        group by 1,2
        order by 1,2
    ) sub1
    join
    (
        select  num_or, enable_or_transformation, avg(time) 
        from    perf_10000
        group by 1,2
        order by 1,2
    ) sub2
    on sub1.num_or = sub2.num_or and sub1.avg < sub2.avg
) sub3
where sub3.sub1_enable is false and sub3.sub2_enable is true;

/*
 num_or | sub1_enable | sub1avg | sub2_enable | sub2_avg | performance_degradation
--------+-------------+---------+-------------+----------+-------------------------
(0 rows)
*/