On 09/02/2021 18:05, Rick Hillegas wrote:
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.
OK. The concept of a "covering index" is a new one on me, so I need to
read up on that. Would it be possible to create a covering index for
this table just by specifying "time DESC" followed by the other columns
(i.e. everything except id)? Would it be a reasonable thing to do in
terms of resource usage?
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
;
That's not so easy, since all the tables in the webapp are generated by
a showTable() method, which displays results from a view as a table
which allows for sorting, filtering, selecting the numbers of rows per
page and so on, using a ridiculously long list of parameters to specify
all the different possibilities. But I'll think about it.
--
John English
