However, I don't think you can point to 2ms vs. 6000ms a= s proof of a bad query plan. =A0In the first case, the optimizer can prove = (to itself) that the query can return no rows (because of the 2>1 condit= ion along with all AND predicates) -- and therefore it probably doesn't= do any index scan at all. =A0Therefore, 2ms.

In the second case, because the 1=3D1 condition is sati= sfied, Derby must *actually* do the index scan as per plan. =A0Therefore, 6= 000ms. =A0Whether using the DESC index actually provides significant improv= ement over ASC remains to be seen, as the comparison currently isn't ap= ples-to-apples.

I would be interested in seeing the result of the DESC = vs. the ASC index in the (1=3D1) scenario. =A0You can override the optimize= r with a hint, like so:

SELECT * FROM table --DERB= Y-PROPERTIES index=3DTBLEVENTS_DATETIMESTAMP_DESC
WHERE
1=3D1 AND
1=3D1 AND
DateTimestamp >=3D ? AND
Date= Timestamp <=3D ? AND
ORDER BY DateTimestamp DESC

=
Note, the --DERBY-PROPERTIES must come at the end of a literal line (i= .e. there must be a linefeed after that cause) otherwise everything after t= hat will be taken as a comment.

Let us know the result. =A0Either way it may be considered a bug i= n the optimizer, but depending on the number of rows returned, may not make= a significant difference in performance.

-Brett

On Wed, Sep 23, 2009 at 11:18 PM, Gavin= Matthews wrote:

Hi,

We have a WHERE clause:

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

which reasonably takes 2ms to execute on a table that has 357254 rows, and<= br> 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=3D1 AND
1=3D1 AND
DateTimestamp >=3D ? AND
DateTimestamp <=3D ? 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<= br> 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);<= br>
Do people agree that this appears to be a bug with the query planner, as th= e
selection of the most appropriate index should not be influenced by static<= br> conditions such as 1=3D1 or 2>1?

Regards,

Gavin

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

--000e0cd3b27c24aaff0474486910--