On 26/09/2021 22:57, Rick Hillegas wrote:
The support for FETCH/OFFSET is pretty minimal. Probably, the optimizer
isn't smart enough to know that the subquery returns only 20 small rows.
What happens if you dump the results of the subquery into a temporary
table and then join that with system_log?
Thanks, I'll give it a whirl. Meanwhile,I've got it down to about 5 or 6
seconds by (a) eliminating the view in favour of a direct table access,
and (b) using a separate SELECT COUNT(*) to get the row count, again
using the table directly and without using an ORDER BY clause. (I
renamed the id and time columns to time and t_time respectively, so the
names match what the code expects to get back from the view, and then
recreated the index.)
It still doesn't seem to be using the index (see trace below) but it's
an improvement. Sorting by time ASC use the PK index and takes about
200ms, but time DESC doesn't use an index and takes about 3s. And I
still don't understand why it's ignoring the index.
I'll try the temporary table approach as soon as I get a minute, and
will let you know what happens.
Thanks for the help,
--
John English
----------------------------------------------------
Statement Text:
SELECT DateTimeFormat(t_time,null) AS
t_time,facility,event,details,name,username FROM system_log
ORDER BY time DESC NULLS LAST FETCH NEXT 20 ROWS ONLY
Parse Time: 19
Bind Time: 1
Optimize Time: 1
Generate Time: 0
Compile Time: 21
Execute Time: 4474
Begin Compilation Timestamp : 2021-09-27 17:52:23.802
End Compilation Timestamp : 2021-09-27 17:52:23.823
Begin Execution Timestamp : 2021-09-27 17:52:23.824
End Execution Timestamp : 2021-09-27 17:52:28.298
Statement Execution Plan Text:
Scroll Insensitive ResultSet:
Number of opens = 1
Rows seen = 20
Number of reads from hash table = 20
Number of writes to hash table = 20
constructor time (milliseconds) = 0
open time (milliseconds) = 4474
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 388236.00
optimizer estimated cost: 399685.32
Source result set:
Row Count (1):
Number of opens = 1
Rows seen = 20
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 4474
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 388236.00
optimizer estimated cost: 399685.32
Source result set:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 20
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 4474
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 388236.00
optimizer estimated cost: 399685.32
Source result set:
Sort ResultSet:
Number of opens = 1
Rows input = 388231
Rows returned = 20
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of rows input=388231
Number of rows output=388231
Sort type=internal
constructor time (milliseconds) = 0
open time (milliseconds) = 4474
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 388236.00
optimizer estimated cost: 399685.32
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 388231
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 4232
close time (milliseconds) = 3
restriction time (milliseconds) = 0
projection time (milliseconds) = 1807
optimizer estimated row count: 388236.00
optimizer estimated cost: 399685.32
Source result set:
Table Scan ResultSet for SYSTEM_LOG at read uncommitted isolation
level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 388231
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds)
= 0
open time (milliseconds) = 0
next time (milliseconds) = 2390
close time (milliseconds) = 3
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0,
1, 2, 3, 4, 7, 8}
Number of columns fetched=7
Number of pages visited=2666
Number of rows qualified=388231
Number of rows visited=388417
Scan type=heap
start position:
null
stop position:
null
qualifiers:
None
optimizer estimated row count:
388236.00
optimizer estimated cost:
399685.32
--
This email has been checked for viruses by AVG.
https://www.avg.com
