Bug 3001

Summary: Rewrite BETWEEN in column first notation, with constant arguments last
Product: SQL Reporter: Stefan de Konink <stefan>
Component: allAssignee: SQL devs <bugs-sql>
Status: NEW ---    
Severity: enhancement CC: y.zhang
Priority: Normal Keywords: NONEEDTOTEST
Version: -- development   
Hardware: Other   
OS: Linux   

Description Stefan de Konink 2012-02-09 00:49:31 CET
User-Agent:       Mozilla/5.0 (X11; Linux i686) AppleWebKit/535.2 (KHTML, like Gecko) Chrome/15.0.874.83 Safari/535.2
Build Identifier: 

A SQL BETWEEN statement can be written in the form:

Y between X-10 and X+10

Typically column X will require recalculation, while Y is a constant argument. As example which takes 349ms (hot).
select count(*) from pujo where '1970-01-01 11:00' BETWEEN pujo.departuretime - interval '600' second and  pujo.departuretime + interval '600' second;

The same operation could be rewritten:
Y between X-10 and X+10 = X between Y-10 and Y+10

The performance of the following query is 2ms (hot), while 4ms cold. It doesn't matter if the intervals are presubstracted manually, performance is similar.

select count(*) from pujo where pujo.departuretime between cast('1970-01-01 11:00' as timestamp) - interval '600' second and cast('1970-01-01 11:00' as timestamp) + interval '600' second;

select count(*) from pujo where pujo.departuretime between cast('1970-01-01 10:50' as timestamp) and cast('1970-01-01 11:10' as timestamp);

Reproducible: Always
Comment 1 Ying Zhang cwiconfidential 2012-11-27 15:44:32 CET
No test for feature request