Thanks for you comments and for creating a JIRA item. I think that the
first case is a separate thing, because the following is slow as well:
SELECT MIN(Id), MIN(Id) + 1 FROM Customer
This case could be optimized to do only one MIN-traversal. I think that
this is a more fundamental thing, perhaps requiring more thought, and
some kind of global optimizations.
Sorry for the format of this message, I am using the digest.
--
Harri
Jeffrey Lichtman wrote:
1) "SELECT MIN(Id) FROM Customer" and "SELECT MAX(Id) FROM Customer"
are both fast, but "SELECT MIN(Id), MAX(Id) FROM Customer" is slow,
taking 5 seconds. Why?
Derby knows how to use an index to quickly find a minimum or a maximum
(by traversing down one side of the B-tree or the other). It doesn't
know how to do both in the same query, which would take two traversals.
Is the work to fix this the same as making IN list use multiple probes,
and/or makeing OR lists do multiple probes? There are existing JIRA
items for those, or is it different enough to have a separate JIRA?
2) "SELECT * FROM Customer ORDER BY Id" is fast but "SELECT * FROM
Customer ORDER BY Id DESC" is slow. Why? Can't it scroll the index
backwards?
The structure could support this, the work just has not been done. With
row level locking, concurrent tree splitting, and current assumptions
throughout the access method that scans go top/down, left to right the
work to do a backward scan is harder than doing a forward scan. Also
once the store portion is done, there would be necessary changes to:
optimizer, execution engine, and scan interface to allow the backward
scan. This would be a hard first project.
The current workaround is that you can create both an ascending and
a descending index if you need ordering in both directions to go fast.
You should check out the access/btree white papers Dibyendo (sp?)
produced, on the web site.
I will go ahead and log an enhancement in JIRA (DERBY-884)
