without a "where" clause firebird has to read the entire table (to determine if each row is valid for the current transaction) and sort the results

Nick Upson, Telensa Ltd, Senior Operations Network Engineer

direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

On 9 September 2015 at 13:00, Tim Ward tdw@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

 

SELECT FIRST 1 <several columns> FROM MYTAB ORDER BY MYKEY1, MYKEY2;

PLAN (MYTAB ORDER PK_MYTAB)

Current memory = 3437420
Delta memory = 152
Max memory = 3586952
Elapsed time= 0.04 sec
Buffers = 150
Reads = 232
Writes 0
Fetches = 132082

There may be several thousand records in the table, but the primary key
is defined as (MYKEY1, MYKEY2) and the plan says it's using it, so why
the 232 disk reads? - one might have guessed 2 would be enough, one for
the index and one for the record. (The key fields are both 32 bit
integers. Yes, I do have plans to replace them by a generator-generated
key, but I can't see why that would make a difference to this particular
performance issue.)

This table is being used as a FIFO queue. Would the most likely
explanation be lack of garbage collection, so it's having to scan lots
of deleted records before finding the first real one, or are there other
possibilities? I must say that

Oldest transaction 390433636
Oldest active 390433637
Oldest snapshot 390433637
Next transaction 391092651

doesn't look too encouraging, so I'd better investigate that.

--
Tim Ward

-- 
Tim Ward