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 t...@telensa.com [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 > > >
