On 2/9/21 8:21 AM, John English wrote:
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)?
Yes.
Would it be a reasonable thing to do in terms of resource usage?
It would probably double the on-disk footprint of your table since the
index would have to contain most of the columns (including the big
DETAILS column). You have a space vs. time tradeoff here if the
subquery-based solution won't work for your app.
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.
