Hi, Excuse the long wait, I was a little busy lately. I've attached a test case. Executing the test case on a new database yields two explain statements which are exactly the same except for the scanCount values. The second explain statement contains a much smaller scanCount when joining the table.
In the given test case the values don't differ by much, but with increasing number of entries the gap only grows bigger... Thanks a lot for having a look at it! Regards, Dario On Mon, Sep 27, 2010 at 7:15 PM, Thomas Mueller <[email protected]> wrote: > Hi, > > I don't know what the problem could be. There is a difference between > a primary key that is created when there are no records in a table, > and a primary key that is created after inserting data. But I couldn't > reproduce the problem you describe. My test case is: > > drop table test; > set cache_size 0; > create table test(id int primary key, name varchar); > insert into test select x, space(10000) from system_range(1, 5000); > explain analyze select * from test where id between 10 and 20; > alter table test drop primary key; > alter table test add primary key(id); > explain analyze select * from test where id between 10 and 20; > > Could you try to change the test case to show the problem you observed? > > Regards, > Thomas > > > On Fri, Sep 24, 2010 at 10:58 AM, Dario Simone <[email protected]> wrote: >> I forgot to mention: >> >> I'm using version 1.2.139 >> >> On Fri, Sep 24, 2010 at 10:15 AM, Dario <[email protected]> wrote: >>> Hi all, >>> >>> All in all I'm really happy with H2 and (almost) everything works as >>> it should. >>> >>> While looking at a query, with bad performance I came upon strange >>> behaviour, though: >>> H2 didn't use the primary key index to do a join which had only the >>> table's primary key in the on-clause (explain showed a scanCount for >>> that join of 106757553). After dropping the Index and recreating the >>> primary key, the index was used (the scanCount was down to 35003). >>> >>> The only difference in the index before and after the recreation is, >>> that the one before had IS_GENERATED = true and the one after had >>> IS_GENERATED = false. >>> >>> It seems as if the index was there and if I understand the explain >>> syntax correctly the optimiser decided to use it, but then scanned the >>> whole table anyways in spite of the existing Index. >>> >>> Has somebody come across such a behaviour before? >>> >>> -- >>> You received this message because you are subscribed to the Google Groups >>> "H2 Database" group. >>> To post to this group, send email to [email protected]. >>> To unsubscribe from this group, send email to >>> [email protected]. >>> For more options, visit this group at >>> http://groups.google.com/group/h2-database?hl=en. >>> >>> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To post to this group, send email to [email protected]. >> To unsubscribe from this group, send email to >> [email protected]. >> For more options, visit this group at >> http://groups.google.com/group/h2-database?hl=en. >> >> > > -- > You received this message because you are subscribed to the Google Groups "H2 > Database" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
h2TestCase.sql
Description: Binary data
