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 Sun, 26 Sep 2021 14:47:28 GMT
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


Mime
View raw message