expression_num_or_1_100.sql
application/sql
Filename: expression_num_or_1_100.sql
Type: application/sql
Part: 0
set max_parallel_workers_per_gather to 10;
-- already set the work_mem to 4GB.
--main test table
drop table if exists test_1_100;
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)
*/