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.
