On Tue, 28 Sep 2021, 17:52 Rick Hillegas, wrote:
> Glad that you have made progress on this puzzle. One comment inline...
>
> On 9/28/21 4:10 AM, John English wrote:
> > The WHERE clause seems to prevent the index from being used.
> The index is not usable in this query because username is not
Glad that you have made progress on this puzzle. One comment inline...
On 9/28/21 4:10 AM, John English wrote:
A couple more data points, from testing different variants of the
inner select:
1) SELECT time FROM system_log
ORDER BY time DESC NULLS LAST FETCH NEXT 20 ROWS ONLY;
419ms,
A couple more data points, from testing different variants of the inner
select:
1) SELECT time FROM system_log
ORDER BY time DESC NULLS LAST FETCH NEXT 20 ROWS ONLY;
419ms, "Index Scan ResultSet for SYSTEM_LOG using index LOG_INDEX at
read uncommitted isolation level using share row
On 27/09/2021 18:23, John English wrote:
I'll try the temporary table approach as soon as I get a minute, and
will let you know what happens.
I'm finally getting somewhere. Having (a) moved the row counting out to
a separate query, (b) renamed columns in the table to match what my code
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
On 9/25/21 11:59 AM, John English wrote:
On 25/09/2021 21:14, Rick Hillegas wrote:
On 9/25/21 7:39 AM, John English wrote:
SELECT id,DateTimeFormat(time,null) AS
t_time,name,username,facility,event,details
FROM system_log
ORDER BY id DESC
NULLS LAST
FETCH FIRST 20 ROWS ONLY;
I can remember
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
On 25/09/2021 21:14, Rick Hillegas wrote:
On 9/25/21 7:39 AM, John English wrote:
SELECT id,DateTimeFormat(time,null) AS
t_time,name,username,facility,event,details
FROM system_log
ORDER BY id DESC
NULLS LAST
FETCH FIRST 20 ROWS ONLY;
I can remember whether you tried to rewrite the query to
On 9/25/21 7:39 AM, John English wrote:
SELECT id,DateTimeFormat(time,null) AS
t_time,name,username,facility,event,details
FROM system_log
ORDER BY id DESC
NULLS LAST
FETCH FIRST 20 ROWS ONLY;
I can remember whether you tried to rewrite the query to use a subquery.
Something like this:
I asked something like this some months ago, but am still not getting
anywhere...
I have a table used for logging events, which current contains just
under 400,000 rows:
CREATE TABLE system_log (
id INTEGER GENERATED ALWAYS AS IDENTITY,
timeTIMESTAMP DEFAULT
10 matches
Mail list logo
