On 25/09/2021 21:14, Rick Hillegas wrote:
SELECT id, time AS t_time,name,username,facility,event,details
FROM
system_log s,
(
SELECT id AS log_id
FROM system_log
ORDER BY id DESC
NULLS LAST
FETCH FIRST 20 ROWS ONLY
) t
WHERE s.id = t.log_id
;
Ideally I want to access the table through a view, defined like this:
CREATE VIEW system_log_view AS
SELECT time AS t_time,
facility,
event,
details,
name,
username,
id AS time
FROM system_log
(The renamings may seem strange but they serve a purpose!)
Selecting the ids directly from the table takes 0.4s:
SELECT id FROM system_log
ORDER BY id DESC NULLS LAST
FETCH FIRST 20 ROWS ONLY;
Going through the view takes much longer (2.9s):
SELECT time FROM system_log_view
ORDER BY time DESC NULLS LAST
FETCH FIRST 20 ROWS ONLY;
It seems like the view is materializing all the rows, and then 20 rows
are being selected from the 400,000:
Source result set:
Sort ResultSet:
Number of opens = 1
Rows input = 388226
Rows returned = 20
In any case I want to know the total number of rows, even though I
display them in pages of 20 at a time. I thought I might get away with
using a separate query for this, but no:
SELECT COUNT(*) FROM system_log; -- 0.271s
SELECT COUNT(*) FROM system_log_view; -- 2.184s
I don't understand why the view is so much slower than accessing the
table directly. The direct table access says this:
Index Scan ResultSet for SYSTEM_LOG using index LOG_INDEX at read
uncommitted isolation level using share row locking chosen by the optimizer
The corresponding line from executing the view doesn't mention the index:
Table Scan ResultSet for SYSTEM_LOG at read uncommitted isolation level
using share row locking chosen by the optimizer
What am I not understanding about views here?
--
John English
--
This email has been checked for viruses by AVG.
https://www.avg.com
