Try with tmp1(MYKEY1) as (SELECT MIN(MYKEY1) FROM MYTAB), tmp2(MYKEY1, MYKEY2) as (SELECT t.MYKEY1, MIN(mt.MYKEY2) FROM tmp1 t JOIN MYTAB mt ON t.MYKEY1 = mt.MYKEY1 GROUP BY 1) SELECT mt2.<several columns> FROM tmp2 t2 JOIN MYTAB mt2 ON t2.MYKEY1 = mt2.MYKEY1 AND t2.MYKEY2 = mt2.MYKEY2
and see if that gets the same or a different disk read (I don't know, I'm just curious). Set 2015-09-09 14:00 GMT+02:00 Tim Ward [email protected] [firebird-support] < [email protected]>: > 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 > > > > ------------------------------------ > Posted by: Tim Ward <[email protected]> > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > ------------------------------------ > > Yahoo Groups Links > > > >
