Hi, Hm, I don't know what it could be. Could you add "analyze" before runnning the query (to ensure the statistics are updated), and then also run "explain select..." and "explain analyze select..." against version 1.3.171 and 1.3.173 and then post the result?
Regards, Thomas On Thu, Aug 15, 2013 at 2:30 PM, Max Sidnin <[email protected]> wrote: > Greetings, > There is the notable performance issue in version 1.3.172 and newer for > requests like: > > > > > +-----------------------------------------+------------------------------------+ > > > | STATUS is NOT indexed | STATUS is indexed | > > +-----------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+-----------------+-----------------+ > | > SQL > > | 1.3.171 | 1.3.173 | 1.3.171 | 1.3.173 | > > +-----------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+-----------------+-----------------+ > | SELECT DISTINCT NAME FROM T1 WHERE STATUS = 0 ORDER BY NAME ASC LIMIT > 400 OFFSET 1200 | ~70 ms | ~3200 ms | ~3700 > ms | > > +-----------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+-----------------+-----------------+ > | SELECT NAME FROM T1 WHERE STATUS = 0 ORDER BY NAME ASC LIMIT 400 OFFSET > 1200 | ~50 ms | ~570 ms | ~800 > ms | > > +-----------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+---------------------+-----------------+-----------------+ > > > The degradation is related to the non-indexed column condition with > sorting and limits. > > - If the STATUS column is not indexed 1.3.171 works very fast comparable > to 1.3.173 > - If we remove sorting both versions regardless of whether the column is > indexed or not work equally fast > - If we remove limit-offset both versions regardless of whether the column > is indexed or not work equally slow > > The attached file contains java source code that reproduces the issue. > It creates the table with 250.000 rows. The 'STATUS' column is 0 for all > rows. The usage of 'SELECTIVITY 1' for STATUS column doesn't make any > difference. The 'NAME' is indexed and unique. > > Connection URL: > jdbc:h2:/tmp/h2_test/test;LOG=1;MVCC=TRUE;PAGE_SIZE=16384;CACHE_TYPE=TQ;ALIAS_COLUMN_NAME=TRUE;IGNORECASE=TRUE;DB_CLOSE_DELAY=0;CACHE_SIZE=400000;MAX_OPERATION_MEMORY=75000000 > > Thank you! > > Regards, > Max Sidnin > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/groups/opt_out. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
