db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Re: Using indexes
Date Mon, 27 Sep 2021 15:23:31 GMT
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


Mime
View raw message