Postgresql 另类的SQL

min和MAX

测试表请参数,数据量为一千六百万.

常规方法

explain (analyze,verbose,costs,buffers,timing)
select min(objectid),max(objectid) from test;
--Execution time: 0.190 m

explain (analyze,verbose,costs,buffers,timing)
select min(objectid),max(objectid) from test where num=20;
--Execution time: 0.122 ms

另类的方法

explain (analyze,verbose,costs,buffers,timing)
(select objectid from test order by objectid limit 1)
union all
(select objectid from test order by objectid desc limit 1);
--Execution time: 0.156 ms

explain (analyze,verbose,costs,buffers,timing)
(select objectid from test where num=20 order by objectid limit 1)
union all
(select objectid from test where num=20 order by objectid desc limit 1);
--Execution time: 0.094 ms


explain (analyze,verbose,costs,buffers,timing)
select objectid as min,(select objectid from test order by objectid desc limit 1) as max from test order by objectid limit 1;
--Execution time: 0.157 ms

explain (analyze,verbose,costs,buffers,timing)
select objectid as min,(select objectid from test where num=20 order by objectid desc limit 1) as max from test where num=20 order by objectid limit 1;
-- Execution time: 0.095 ms

虽然提升不是很多,但性能就是从细节中扣出来的

补位

select  f2,(f2 / f1 * f1) + 
(case f2 % f1 when 0 then
	0
else
 	f1
end)
from (select 16 as f1,f2 from generate_series(1,1000) as f2) as tmp