As Bryan points out, please consult the Tuning Guide for information on
how to view your query plan. In any event, your descending index is not
a covering index. That is, it does not contain all of the columns in
your query. That may be what forces the optimizer to choose a full table
scan.
You might be able to trick the optimizer into generating a more
efficient plan by re-writing your query as follows:
SELECT time as t_time,
facility,event,details,name,username,sector,item
FROM system_log
WHERE id IN
(
SELECT id
FROM system_log
ORDER BY time DESC
NULLS LAST
FETCH NEXT 20 ROWS ONLY
)
ORDER BY t_time
;
On 2/9/21 7:17 AM, Bryan Pendleton wrote:
I think Rick was suggesting that if you got the right query plan,
using your index as efficiently as possible, then that would be the
best solution? You can find a lot of information about this in
http://db.apache.org/derby/docs/10.15/tuning/
Perhaps your statistics are inaccurate, so Derby is not choosing the
index-based plan? I think there are ways to force Derby to choose that
plan, and there are also ways to ensure that your statistics are
accurate.
Specifically, see:
http://db.apache.org/derby/docs/10.15/tuning/ctunperfstatistics.html
bryan
On Tue, Feb 9, 2021 at 4:04 AM John English <john.fore...@gmail.com> wrote:
On 08/02/2021 18:43, Rick Hillegas wrote:
I would recommend throwing more memory at your JVM or adding a
descending index to system_log.time.
So, there is already a descending index to system_log.time, and I've
upped the heap to 2GB. I also added the time taken to service the
request as an HTML comment at the end of the page.
The disk is an SSD, and the table contains about 261,000 rows. Accessing
the first 20 rows ordered by time desc, it takes about 13 seconds, which
seems a bit excessive to me. Ordering by event also takes about the same
(12690ms as opposed to 13151ms), so the index doesn't seem to have much
effect.
Not sure what to try next... any ideas?
--
John English
