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, "Index Scan ResultSet for SYSTEM_LOG using index LOG_INDEX at
read uncommitted isolation level using share row locking chosen by the
optimizer"
2) SELECT time FROM system_log
WHERE username='foo'
ORDER BY time DESC NULLS LAST FETCH NEXT 20 ROWS ONLY;
2245ms, "Table Scan ResultSet for SYSTEM_LOG at read uncommitted
isolation level using share row locking chosen by the optimizer"
Removing "FETCH NEXT 20 ROWS ONLY":
1) 388,237 rows in 3859ms, otherwise same as above (uses index).
2) 23,211 rows in 2199ms, otherwise same as above (doesn't use index).
The WHERE clause seems to prevent the index from being used.
The index is not usable in this query because username is not the
leading column in the index.
