
set max_parallel_workers_per_gather to 10;
-- already set the work_mem to 4GB.

--main test table
CREATE TABLE test_or(query text, num_or bigint);
create table test_1_100 as (select (random()*1000)::int x, (random()*1000) y from generate_series(1,1_000_000) i);
vacuum analyze test_1_100;

--query_or store the dynamic query.
create table query_or(query text,num_or bigint);
create table perf_or(query text,num_or bigint,time numeric,or_or_any  text,enable_or_transformation  boolean);
insert into query_or(num_or,query)
select g1, query
from generate_series(9,200) g1,
lateral (select 'select * from test_1_100 where x = 1 ' || string_agg('or x = ' || g,' ') as query from generate_series(2,g1) g);

create or replace procedure execute_explain_time_or(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 query_or
    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_or(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;

truncate perf_or;
set enable_or_transformation to on;
call execute_explain_time_or(true) \watch i=0.1 c=1
call execute_explain_time_or(true) \watch i=0.1 c=9
set enable_or_transformation to off;
call execute_explain_time_or(false) \watch i=0.1 c=1
call execute_explain_time_or(false) \watch i=0.1 c=9

-----------------------the results where enable_or_transformation do increase the performance.
select *, (sub2_avg - sub1avg)/sub1avg * 100 performance_improve
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_or 
        group by 1,2
        order by 1,2
    ) sub1
    join
    (
        select  num_or, enable_or_transformation, avg(time) 
        from    perf_or 
        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 true and sub3.sub2_enable is false;
/*
 num_or | sub1_enable |       sub1avg        | sub2_enable |       sub2_avg       |   performance_improve
--------+-------------+----------------------+-------------+----------------------+-------------------------
     29 | t           | 104.8803000000000000 | f           | 107.0481000000000000 |  2.06692772617927294300
     30 | t           | 105.0153000000000000 | f           | 109.2153000000000000 |  3.99941722777538130200
     31 | t           | 105.8235000000000000 | f           | 111.8992000000000000 |  5.74135234612349808900
     32 | t           | 106.1155000000000000 | f           | 113.7442000000000000 |  7.18905343705679189200
     33 | t           | 106.6637000000000000 | f           | 116.0247000000000000 |  8.77618158755040374600
     34 | t           | 107.0717000000000000 | f           | 118.4950000000000000 | 10.66883219375427867500
     35 | t           | 108.5851000000000000 | f           | 120.6030000000000000 | 11.06772476150042685400
     36 | t           | 109.6530000000000000 | f           | 123.1990000000000000 | 12.35351517970324569300
     37 | t           | 109.7074000000000000 | f           | 125.2044000000000000 | 14.12575633001967050500
     38 | t           | 109.9169000000000000 | f           | 127.6556000000000000 | 16.13828264807322622800
     39 | t           | 110.3708000000000000 | f           | 130.6223000000000000 | 18.34860307255179812100
     40 | t           | 110.8382000000000000 | f           | 133.5625000000000000 | 20.50222757136077633900
     41 | t           | 111.2164000000000000 | f           | 134.3731000000000000 | 20.82129973636981596200
     42 | t           | 111.9909000000000000 | f           | 136.3067000000000000 | 21.71229983864760440400
     43 | t           | 113.4651000000000000 | f           | 138.5305000000000000 | 22.09084555515308231300
     44 | t           | 116.8828000000000000 | f           | 140.6611000000000000 | 20.34371182072982509000
     45 | t           | 117.6902000000000000 | f           | 142.4127000000000000 | 21.00642194507274182600
     46 | t           | 118.2206000000000000 | f           | 144.2619000000000000 | 22.02771767356958093600
     47 | t           | 118.6385000000000000 | f           | 146.8260000000000000 | 23.75915069728629407800
     48 | t           | 119.7119000000000000 | f           | 147.8081000000000000 | 23.46984719146551011200
     49 | t           | 121.2223000000000000 | f           | 149.9498000000000000 | 23.69819744386965104600
     50 | t           | 122.7193000000000000 | f           | 151.9349000000000000 | 23.80685026723587895300
     51 | t           | 122.5698000000000000 | f           | 153.9458000000000000 | 25.59847531773732191800
     52 | t           | 123.5920000000000000 | f           | 155.7861000000000000 | 26.04869247200466049600
     53 | t           | 124.7999000000000000 | f           | 157.7298000000000000 | 26.38615896326840005500
     54 | t           | 125.1378000000000000 | f           | 159.2426000000000000 | 27.25379541593347493700
     55 | t           | 126.6164000000000000 | f           | 161.1430000000000000 | 27.26866345907797094200
     56 | t           | 137.8181000000000000 | f           | 162.8298000000000000 | 18.14834190864625183500
     57 | t           | 138.6178000000000000 | f           | 165.1144000000000000 | 19.11486115058816400200
     58 | t           | 116.6434000000000000 | f           | 166.7604000000000000 | 42.96599721887393543100
     59 | t           | 117.0680000000000000 | f           | 168.7118000000000000 | 44.11436088427238869700
     60 | t           | 117.4713000000000000 | f           | 170.6845000000000000 | 45.29889428311425854700
     61 | t           | 117.8611000000000000 | f           | 171.1954000000000000 | 45.25182609020278955500
     62 | t           | 118.4923000000000000 | f           | 172.5180000000000000 | 45.59427068256755924200
     63 | t           | 118.8348000000000000 | f           | 173.8333000000000000 | 46.28147646985563151500
     64 | t           | 119.8678000000000000 | f           | 176.0294000000000000 | 46.85294966621561420200
     65 | t           | 120.6754000000000000 | f           | 177.6367000000000000 | 47.20208095436186662700
     66 | t           | 121.4349000000000000 | f           | 178.9513000000000000 | 47.36397855970565298800
     67 | t           | 121.7418000000000000 | f           | 181.0783000000000000 | 48.73962763816536308800
     68 | t           | 121.8158000000000000 | f           | 181.8336000000000000 | 49.26930660883070997400
     69 | t           | 122.4417000000000000 | f           | 183.3739000000000000 | 49.76425515163543139300
     70 | t           | 123.2422000000000000 | f           | 185.1953000000000000 | 50.26938824526014628100
     71 | t           | 123.9890000000000000 | f           | 185.3904000000000000 | 49.52165111421174459000
     72 | t           | 123.8889000000000000 | f           | 186.7944000000000000 | 50.77573535643629090300
     73 | t           | 124.5242000000000000 | f           | 187.9980000000000000 | 50.97306387031597071100
     74 | t           | 124.7615000000000000 | f           | 189.0951000000000000 | 51.56526652853644754200
     75 | t           | 125.5594000000000000 | f           | 189.8713000000000000 | 51.22029891828090927500
     76 | t           | 126.9260000000000000 | f           | 191.3792000000000000 | 50.78013960890597671100
     77 | t           | 127.1324000000000000 | f           | 192.5075000000000000 | 51.42284736227743675100
     78 | t           | 127.6679000000000000 | f           | 193.1642000000000000 | 51.30208924874616093800
     79 | t           | 128.6513000000000000 | f           | 194.4885000000000000 | 51.17492011351614791300
     80 | t           | 128.6342000000000000 | f           | 195.1276000000000000 | 51.69185177814298219300
     81 | t           | 129.0957000000000000 | f           | 196.3499000000000000 | 52.09639050719737373100
     82 | t           | 129.6066000000000000 | f           | 197.1698000000000000 | 52.12944402522711034800
     83 | t           | 129.9561000000000000 | f           | 198.3253000000000000 | 52.60945811701028270300
     84 | t           | 130.3332000000000000 | f           | 198.8981000000000000 | 52.60739397175853888300
     85 | t           | 130.5332000000000000 | f           | 200.2831000000000000 | 53.43460514259973707800
     86 | t           | 130.8151000000000000 | f           | 201.0401000000000000 | 53.68264061258983099000
     87 | t           | 131.4846000000000000 | f           | 202.4797000000000000 | 53.99499256947201421300
     88 | t           | 131.8765000000000000 | f           | 202.6478000000000000 | 53.66483035264053868600
     89 | t           | 132.9200000000000000 | f           | 203.7019000000000000 | 53.25150466445982545900
     90 | t           | 133.5424000000000000 | f           | 204.4664000000000000 | 53.10972395284194383200
     91 | t           | 133.5083000000000000 | f           | 205.0827000000000000 | 53.61044968739771235200
     92 | t           | 134.3553000000000000 | f           | 205.9682000000000000 | 53.30113512455407416000
     93 | t           | 134.4928000000000000 | f           | 206.8636000000000000 | 53.81016678959765875900
     94 | t           | 134.9325000000000000 | f           | 207.2891000000000000 | 53.62429362829562929600
     95 | t           | 135.9267000000000000 | f           | 208.1018000000000000 | 53.09854502463460085500
     96 | t           | 136.1931000000000000 | f           | 208.8677000000000000 | 53.36144048413612730700
     97 | t           | 136.3021000000000000 | f           | 209.2304000000000000 | 53.50489831044422646500
     98 | t           | 136.5311000000000000 | f           | 210.1090000000000000 | 53.89094499348500085300
     99 | t           | 137.1572000000000000 | f           | 210.6797000000000000 | 53.60455010746792731300
    100 | t           | 137.4744000000000000 | f           | 211.0338000000000000 | 53.50770761683629824900
    101 | t           | 137.7685000000000000 | f           | 211.0508000000000000 | 53.19234803311352014400
    102 | t           | 138.1559000000000000 | f           | 211.0382000000000000 | 52.75366451957534929700
    103 | t           | 137.6172000000000000 | f           | 211.3757000000000000 | 53.59686143883177393500
    104 | t           | 137.5061000000000000 | f           | 211.4132000000000000 | 53.74823371472247413000
    105 | t           | 137.6102000000000000 | f           | 210.9451000000000000 | 53.29176180254080002800
    106 | t           | 137.7702000000000000 | f           | 211.0282000000000000 | 53.17405360520635086500
    107 | t           | 138.5430000000000000 | f           | 210.9996000000000000 | 52.29899742318269418200
    108 | t           | 139.9975000000000000 | f           | 210.5098000000000000 | 50.36682797907105484000
    109 | t           | 140.4437000000000000 | f           | 210.9867000000000000 | 50.22866814246562857600
    110 | t           | 140.6887000000000000 | f           | 210.4801000000000000 | 49.60696914535424664500
    111 | t           | 141.8664000000000000 | f           | 210.8893000000000000 | 48.65345141626206064300
    112 | t           | 141.0273000000000000 | f           | 210.9451000000000000 | 49.57749315203510242300
    113 | t           | 141.7035000000000000 | f           | 211.1341000000000000 | 48.99709604914486939300
    114 | t           | 141.9327000000000000 | f           | 210.6114000000000000 | 48.38821497794377194300
    115 | t           | 141.6777000000000000 | f           | 210.6870000000000000 | 48.70865351427924084000
    116 | t           | 125.7308000000000000 | f           | 211.4057000000000000 | 68.14153731623436739400
    117 | t           | 125.7181000000000000 | f           | 212.0755000000000000 | 68.69130220708076243600
    118 | t           | 125.6494000000000000 | f           | 211.3810000000000000 | 68.23080730986379560900
    119 | t           | 125.8776000000000000 | f           | 210.8965000000000000 | 67.54092864814708891800
    120 | t           | 125.9351000000000000 | f           | 210.8203000000000000 | 67.40392471995496092800
    121 | t           | 125.7963000000000000 | f           | 210.9549000000000000 | 67.69563174751562645300
    122 | t           | 125.9797000000000000 | f           | 210.9408000000000000 | 67.44030982769446188600
    123 | t           | 126.1968000000000000 | f           | 211.6298000000000000 | 67.69823006605555766900
    124 | t           | 125.8345000000000000 | f           | 211.4115000000000000 | 68.00758138666264021400
    125 | t           | 126.6186000000000000 | f           | 211.2693000000000000 | 66.85486966369869829600
    126 | t           | 126.4777000000000000 | f           | 211.2701000000000000 | 67.04138357987218300100
    127 | t           | 126.1641000000000000 | f           | 211.0209000000000000 | 67.25906973536846060000
    128 | t           | 126.4665000000000000 | f           | 210.8173000000000000 | 66.69813745141994124900
    129 | t           | 126.2587000000000000 | f           | 211.0595000000000000 | 67.16432214176132021000
    130 | t           | 126.6869000000000000 | f           | 210.9789000000000000 | 66.53568758885093881100
    131 | t           | 126.5791000000000000 | f           | 211.3714000000000000 | 66.98759905861236175600
    132 | t           | 126.3191000000000000 | f           | 210.9578000000000000 | 67.00388143994059489000
    133 | t           | 126.4271000000000000 | f           | 210.6811000000000000 | 66.64235753252269489700
    134 | t           | 126.8345000000000000 | f           | 211.2571000000000000 | 66.56122742629174238900
    135 | t           | 126.5900000000000000 | f           | 210.7062000000000000 | 66.44774468757405798200
    136 | t           | 126.8002000000000000 | f           | 211.0857000000000000 | 66.47110966701945265100
    137 | t           | 126.4405000000000000 | f           | 211.2132000000000000 | 67.04552734290041561100
    138 | t           | 126.8615000000000000 | f           | 211.5757000000000000 | 66.77691813513161991600
    139 | t           | 126.4344000000000000 | f           | 211.1311000000000000 | 66.98865182260524034600
    140 | t           | 127.6021000000000000 | f           | 211.0301000000000000 | 65.38136911539857102700
    141 | t           | 128.2537000000000000 | f           | 211.6408000000000000 | 65.01730554362174346600
    142 | t           | 128.5873000000000000 | f           | 211.6673000000000000 | 64.60980205665722820200
    143 | t           | 128.2464000000000000 | f           | 210.9402000000000000 | 64.48040646754996631500
    144 | t           | 128.0114000000000000 | f           | 211.9285000000000000 | 65.55439593661189550300
    145 | t           | 128.0438000000000000 | f           | 211.4330000000000000 | 65.12552735860697667500
    146 | t           | 128.1741000000000000 | f           | 211.5466000000000000 | 65.04629250371174831700
    147 | t           | 127.9691000000000000 | f           | 211.9141000000000000 | 65.59786698507686621200
    148 | t           | 128.4205000000000000 | f           | 211.8358000000000000 | 64.95481640392304966900
    149 | t           | 128.0198000000000000 | f           | 211.4768000000000000 | 65.19069706404790509000
    150 | t           | 128.3217000000000000 | f           | 211.9521000000000000 | 65.17245329511688202400
    151 | t           | 129.4140000000000000 | f           | 211.6589000000000000 | 63.55177956017123340600
    152 | t           | 129.5033000000000000 | f           | 211.8222000000000000 | 63.56509834112335361300
    153 | t           | 129.6249000000000000 | f           | 211.6448000000000000 | 63.27480291209482128800
    154 | t           | 131.0059000000000000 | f           | 211.8529000000000000 | 61.71248775818493670900
    155 | t           | 130.9267000000000000 | f           | 212.0879000000000000 | 61.98980039976567040900
    156 | t           | 131.3226000000000000 | f           | 212.0395000000000000 | 61.46459177628222408000
    157 | t           | 131.6310000000000000 | f           | 211.7176000000000000 | 60.84174700488486754600
    158 | t           | 131.5908000000000000 | f           | 211.8411000000000000 | 60.98473449511667988900
    159 | t           | 131.8878000000000000 | f           | 211.5440000000000000 | 60.39694346254922744900
    160 | t           | 131.8061000000000000 | f           | 211.6154000000000000 | 60.55053597671124477500
    161 | t           | 131.9751000000000000 | f           | 211.2571000000000000 | 60.07345324989335109400
    162 | t           | 132.3957000000000000 | f           | 211.2196000000000000 | 59.53660126424045493900
    163 | t           | 132.0296000000000000 | f           | 212.0999000000000000 | 60.64571883880584353800
    164 | t           | 132.3022000000000000 | f           | 211.3153000000000000 | 59.72168263263951771000
    165 | t           | 132.3874000000000000 | f           | 211.6975000000000000 | 59.90758939294827151200
    166 | t           | 132.2236000000000000 | f           | 212.5744000000000000 | 60.76887938310558780700
    167 | t           | 132.2942000000000000 | f           | 211.7470000000000000 | 60.05765936828674272900
    168 | t           | 132.7088000000000000 | f           | 211.6001000000000000 | 59.44692439386084419400
    169 | t           | 132.5946000000000000 | f           | 211.9970000000000000 | 59.88358500270750090900
    170 | t           | 132.3898000000000000 | f           | 212.6378000000000000 | 60.61494163447637204700
    171 | t           | 132.4256000000000000 | f           | 212.2411000000000000 | 60.27195647971389217800
    172 | t           | 132.6427000000000000 | f           | 212.2438000000000000 | 60.01167045001345720500
    173 | t           | 133.1197000000000000 | f           | 211.9267000000000000 | 59.20010336561756073700
    174 | t           | 133.6110000000000000 | f           | 212.0075000000000000 | 58.67518392946688521200
    175 | t           | 133.3438000000000000 | f           | 211.5083000000000000 | 58.61877342628603654600
    176 | t           | 133.3035000000000000 | f           | 211.8346000000000000 | 58.91150644956809086000
    177 | t           | 133.3758000000000000 | f           | 211.8599000000000000 | 58.84433307991404737600
    178 | t           | 133.6156000000000000 | f           | 211.7348000000000000 | 58.46562826496307317400
    179 | t           | 133.2390000000000000 | f           | 212.0820000000000000 | 59.17411568684844527500
    180 | t           | 133.4435000000000000 | f           | 211.6663000000000000 | 58.61866632694735974400
    181 | t           | 133.6885000000000000 | f           | 211.6618000000000000 | 58.32461281262038245600
    182 | t           | 134.0952000000000000 | f           | 211.9474000000000000 | 58.05740995949146576500
    183 | t           | 134.0326000000000000 | f           | 211.7349000000000000 | 57.97268724176058660400
    184 | t           | 133.8835000000000000 | f           | 211.8314000000000000 | 58.22069187017070811600
    185 | t           | 134.1082000000000000 | f           | 211.9250000000000000 | 58.02538547232756833700
    186 | t           | 133.9144000000000000 | f           | 212.1726000000000000 | 58.43897295585836922700
    187 | t           | 133.9785000000000000 | f           | 212.0887000000000000 | 58.30054822228939717900
    188 | t           | 133.9325000000000000 | f           | 212.2544000000000000 | 58.47863662665895133700
    189 | t           | 134.0626000000000000 | f           | 212.1272000000000000 | 58.22996122706854857400
    190 | t           | 134.2097000000000000 | f           | 212.1555000000000000 | 58.07762032103491774400
    191 | t           | 134.0349000000000000 | f           | 212.3048000000000000 | 58.39516424453631106500
    192 | t           | 133.8296000000000000 | f           | 212.0195000000000000 | 58.42496727181430714900
    193 | t           | 134.1170000000000000 | f           | 212.2617000000000000 | 58.26606619593340143300
    194 | t           | 134.6806000000000000 | f           | 212.5297000000000000 | 57.80275704147442170600
    195 | t           | 134.2376000000000000 | f           | 212.1181000000000000 | 58.01690435466665077400
    196 | t           | 134.2987000000000000 | f           | 212.0126000000000000 | 57.86645738194040597600
    197 | t           | 134.0816000000000000 | f           | 212.5140000000000000 | 58.49602033388623047500
    198 | t           | 134.0579000000000000 | f           | 213.0738000000000000 | 58.94162149339949380100
    199 | t           | 134.2430000000000000 | f           | 212.2037000000000000 | 58.07431299956049849900
    200 | t           | 134.7254000000000000 | f           | 212.4797000000000000 | 57.71317064191310621500
(172 rows)
*/

-----------------------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_or 
        group by 1,2
        order by 1,2
    ) sub1
    join
    (
        select  num_or, enable_or_transformation, avg(time) 
        from    perf_or 
        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;

--results;
/*
 num_or | sub1_enable |       sub1avg        | sub2_enable |       sub2_avg       | performance_degradation
--------+-------------+----------------------+-------------+----------------------+-------------------------
      9 | f           |  51.9093000000000000 | t           |  94.6670000000000000 | 82.37001847453153866500
     10 | f           |  55.8407000000000000 | t           |  95.9888000000000000 | 71.89755859077697808200
     11 | f           |  58.5258000000000000 | t           |  99.9164000000000000 | 70.72197219004268203100
     12 | f           |  61.4825000000000000 | t           | 102.0862000000000000 | 66.04106859675517423700
     13 | f           |  64.4812000000000000 | t           | 106.3283000000000000 | 64.89814085345806219500
     14 | f           |  67.4634000000000000 | t           | 108.1267000000000000 | 60.27460815790487879400
     15 | f           |  70.0785000000000000 | t           |  99.2710000000000000 | 41.65685623978823747700
     16 | f           |  72.8436000000000000 | t           | 100.3448000000000000 | 37.75376285631133002800
     17 | f           |  76.0539000000000000 | t           | 101.6879000000000000 | 33.70504339685407323000
     18 | f           |  79.8323000000000000 | t           | 102.3518000000000000 | 28.20850708297268148400
     19 | f           |  81.7916000000000000 | t           | 102.9759000000000000 | 25.90033695391702815400
     20 | f           |  84.1702000000000000 | t           | 103.6554000000000000 | 23.14976084172308014000
     21 | f           |  86.9600000000000000 | t           | 103.7771000000000000 | 19.33889144434222631100
     22 | f           |  89.4361000000000000 | t           | 104.8956000000000000 | 17.28552564344822728200
     23 | f           |  91.7129000000000000 | t           | 105.6862000000000000 | 15.23591555822572397100
     24 | f           |  94.5709000000000000 | t           | 106.2428000000000000 | 12.34195719825020170100
     25 | f           |  97.1644000000000000 | t           | 110.9981000000000000 | 14.23741617300163434300
     26 | f           |  99.2604000000000000 | t           | 112.0795000000000000 | 12.91461650366107732800
     27 | f           | 101.9149000000000000 | t           | 114.6598000000000000 | 12.50543345477452266500
     28 | f           | 104.8335000000000000 | t           | 115.9476000000000000 | 10.60166835982772682400
(20 rows)
*/