db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gavin Matthews <...@araxis.com>
Subject Badly performing WHERE caluse
Date Wed, 23 Sep 2009 14:18:41 GMT

Hi,

We have a WHERE clause:

WHERE
2>1 AND
2>1 AND
DateTimestamp >= ? AND
DateTimestamp <= ? AND
ORDER BY DateTimestamp DESC

which reasonably takes 2ms to execute on a table that has 357254 rows, and
does so by selecting the most appropriate index:

Index Scan ResultSet for TBLEVENTS using index TBLEVENTS_DATETIMESTAMP_DESC
at serializable isolation level using share row locking chosen by the
optimizer

However, when we change the WHERE clause to:

WHERE
1=1 AND
1=1 AND 
DateTimestamp >= ? AND 
DateTimestamp <= ? AND 
ORDER BY DateTimestamp DESC

the execution time increases to 6000ms, and appears to do so because the
query planner hasn't selected the most appropriate index:

Index Scan ResultSet for TBLEVENTS using index TBLEVENTS_DATETIMESTAMP_ASC
at serializable isolation level using share row locking chosen by the
optimizer

The indexes are specified as follows:

CREATE INDEX tblEventsDateTimestampASC ON tblEvents (DateTimestamp ASC);
CREATE INDEX tblEventsDateTimestampDESC ON tblEvents (DateTimestamp DESC);

Do people agree that this appears to be a bug with the query planner, as the
selection of the most appropriate index should not be influenced by static
conditions such as 1=1 or 2>1?

Regards,

Gavin

-- 
View this message in context: http://www.nabble.com/Badly-performing-WHERE-caluse-tp25531166p25531166.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message