You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Gavin Matthews <gj...@araxis.com> on 2009/09/23 16:18:41 UTC
Badly performing WHERE caluse
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.
Re: Badly performing WHERE caluse
Posted by Brett Wooldridge <br...@gmail.com>.
That'll teach me to post too early in the morning (I'm in Tokyo) and before
coffee. :-)
On Thu, Sep 24, 2009 at 10:19 AM, Matt Doran <ma...@papercut.com>wrote:
> Brett Wooldridge wrote:
>
>> One could argue that in the first case (2>1), the optimizer is free to
>> choose whatever index it wants because the query can provably return no
>> rows. However, in the second case (1=1), were the DateTimestamp condition
>> must be evaluated, it would appear that the optimizer indeed selected the
>> wrong index.
>>
>>
>> Ummm, last time I checked, 2 was greater than 1. :)
>
Re: Badly performing WHERE caluse
Posted by Matt Doran <ma...@papercut.com>.
Brett Wooldridge wrote:
> One could argue that in the first case (2>1), the optimizer is free to
> choose whatever index it wants because the query can provably return
> no rows. However, in the second case (1=1), were the DateTimestamp
> condition must be evaluated, it would appear that the optimizer indeed
> selected the wrong index.
>
>
Ummm, last time I checked, 2 was greater than 1. :)
Re: Badly performing WHERE caluse
Posted by Brett Wooldridge <br...@gmail.com>.
One could argue that in the first case (2>1), the optimizer is free to
choose whatever index it wants because the query can provably return no
rows. However, in the second case (1=1), were the DateTimestamp condition
must be evaluated, it would appear that the optimizer indeed selected the
wrong index.
However, I don't think you can point to 2ms vs. 6000ms as proof of a bad
query plan. In the first case, the optimizer can prove (to itself) that the
query can return no rows (because of the 2>1 condition along with all AND
predicates) -- and therefore it probably doesn't do any index scan at all.
Therefore, 2ms.
In the second case, because the 1=1 condition is satisfied, Derby must
*actually* do the index scan as per plan. Therefore, 6000ms. Whether using
the DESC index actually provides significant improvement over ASC remains to
be seen, as the comparison currently isn't apples-to-apples.
I would be interested in seeing the result of the DESC vs. the ASC index in
the (1=1) scenario. You can override the optimizer with a hint, like so:
SELECT * FROM table --DERBY-PROPERTIES index=TBLEVENTS_DATETIMESTAMP_DESC
WHERE
1=1 AND
1=1 AND
DateTimestamp >= ? AND
DateTimestamp <= ? 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 that
will be taken as a comment.
Let us know the result. Either way it may be considered a bug in 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 <gj...@araxis.com> wrote:
>
> 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.
>
>
Re: Badly performing WHERE caluse
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Bryan Pendleton <bp...@amberpoint.com> writes:
>> 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?
>
> I don't know of any reason why the 1=1 versus 2>1 should have affected the
> query plan chosen.
Sorry for the late response. I just came across some code in the engine
that made me remember this thread. It looks like Derby doesn't treat 1=1
and 2>1 as boolean constants during optimization, so it takes the
selectivity from the = operator and the > operator, respectively.
The = operator has selectivity 0.1, and > has selectivity 0.33. This
means that the optimizer thinks 1=1 will match 10% of the rows and
2>1 will match 33% of the rows. This is apparently enough to make the
optimizer choose another plan in this case. Of course, both of the
predicates will match 100% of the rows, so the the information that's
given to the optimizer is inaccurate in any case.
Logged here: https://issues.apache.org/jira/browse/DERBY-4416
--
Knut Anders
Re: Badly performing WHERE caluse
Posted by Bryan Pendleton <bp...@amberpoint.com>.
> 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?
I don't know of any reason why the 1=1 versus 2>1 should have affected the
query plan chosen.
If you can provide a reproducible test case that demonstrates it, I'd be
very interested in pursuing this further.
thanks,
bryan