Hi, Did you run "analyze"?
Regards, Thomas On Wed, Jul 11, 2012 at 10:23 AM, Bartosz Skorupa <[email protected]> wrote: > I have an index on nanotime > > CREATE INDEX nanotime ON LOGENTRY (nanotime) > > > Pozdrawiam > Bartosz Skorupa > > > > 2012/7/11 Ryan How <[email protected]> >> >> I'd put an index on nanotime >> >> /* scanCount: 2244038 */ indicates it is reading over 2 million records >> to find the ones you want! >> >> >> >> On 11/07/2012 4:07 PM, Bartosz Skorupa wrote: >> >> For >> >> EXPLAIN ANALYZE SELECT logentry.date , logentry.nanotime FROM logentry, >> log_categories >> WHERE logentry.category = log_categories.id >> AND logentry.nanotime >= 10089487311026 >> AND logentry.nanotime <= 10090280303361 >> ORDER BY 2 ASC NULLS LAST LIMIT 165 OFFSET 0; >> >> i got >> >> SELECT >> LOGENTRY.DATE, >> LOGENTRY.NANOTIME >> FROM PUBLIC.LOG_CATEGORIES >> /* PUBLIC.LOG_CATEGORIES.tableScan */ >> /* scanCount: 9 */ >> INNER JOIN PUBLIC.LOGENTRY >> /* PUBLIC.CONSTRAINT_INDEX_A6: CATEGORY = LOG_CATEGORIES.ID */ >> ON 1=1 >> /* scanCount: 2244038 */ >> WHERE (LOGENTRY.NANOTIME <= 10090280303361) >> AND ((LOGENTRY.NANOTIME >= 10089487311026) >> AND (LOGENTRY.CATEGORY = LOG_CATEGORIES.ID)) >> ORDER BY 2 NULLS LAST >> LIMIT 165 OFFSET 0 >> /* >> total: 165276 >> LOGENTRY.CONSTRAINT_INDEX_A6 read: 10216 (6%) >> LOGENTRY.LOGENTRY_DATA read: 155060 (93%) >> */ >> >> and for >> >> EXPLAIN SELECT logentry.date , logentry.nanotime FROM logentry, >> log_categories >> WHERE logentry.category = log_categories.id >> AND logentry.nanotime >= 10089487311026 >> AND logentry.nanotime <= 10090280303361 >> ORDER BY 2 ASC NULLS LAST LIMIT 165 OFFSET 0; >> >> i got >> >> SELECT >> LOGENTRY.DATE, >> LOGENTRY.NANOTIME >> FROM PUBLIC.LOG_CATEGORIES >> /* PUBLIC.LOG_CATEGORIES.tableScan */ >> INNER JOIN PUBLIC.LOGENTRY >> /* PUBLIC.CONSTRAINT_INDEX_A6: CATEGORY = LOG_CATEGORIES.ID */ >> ON 1=1 >> WHERE (LOGENTRY.NANOTIME <= 10090280303361) >> AND ((LOGENTRY.NANOTIME >= 10089487311026) >> AND (LOGENTRY.CATEGORY = LOG_CATEGORIES.ID)) >> ORDER BY 2 NULLS LAST >> LIMIT 165 OFFSET 0 >> >> Pozdrawiam >> Bartosz Skorupa >> >> >> >> 2012/7/11 Ryan How <[email protected]> >>> >>> Do you have an index on nanotime? >>> >>> If you run EXPLAIN on your query it can tell you if it is using an index >>> or table scan. >>> >>> >>> http://www.h2database.com/html/grammar.html?highlight=Explain&search=explain#explain >>> >>> >>> >>> >>> On 11/07/2012 3:32 PM, Bartosz Skorupa wrote: >>>> >>>> I have a simple DB with table defined : >>>> >>>> CREATE TABLE "LOGENTRY"( >>>> AUTOID BIGINT default '(NEXT VALUE FOR >>>> PUBLIC.SYSTEM_SEQUENCE_10A53561_6D15_428A_820B_7EA1722BCB5D)' not null, >>>> DATE BIGINT not null, >>>> NANOTIME BIGINT not null, >>>> MESSAGE CLOB, >>>> ID BIGINT not null, >>>> LEVEL SMALLINT not null, >>>> CATEGORY INTEGER not null, >>>> REQUESTERCLASS VARCHAR, >>>> REQUESTERMETHOD VARCHAR, >>>> LINENUMBER INTEGER, >>>> THREADID INTEGER not null, >>>> THREADNAME VARCHAR not null, >>>> THREADGROUP VARCHAR not null, >>>> THREADPRIORITY SMALLINT not null, >>>> STACKTRACE CLOB, >>>> FOREIGN KEY (CATEGORY) REFERENCES LOG_CATEGORIES(ID)) >>>> >>>> CREATE TABLE "LOGGINGDB20120711_081524"."PUBLIC"."LOG_CATEGORIES"( >>>> ID INTEGER PRIMARY KEY not null, >>>> CATEGORY VARCHAR not null) >>>> >>>> I run a example query >>>> >>>> SELECT logentry.date , logentry.nanotime FROM logentry, log_categories >>>> WHERE logentry.category = log_categories.id >>>> AND logentry.nanotime >= 4351362368770 >>>> AND logentry.nanotime <= 4352180803886 >>>> ORDER BY 2 ASC NULLS LAST LIMIT 165 OFFSET 0; >>>> >>>> This query run about ~1300ms. >>>> >>>> The same DB and the same query on Derby run ~3ms, what is wrong with >>>> this? >>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "H2 Database" group. >>>> To view this discussion on the web visit >>>> https://groups.google.com/d/msg/h2-database/-/GPd5Wv0qYtcJ. >>>> 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. > > > -- > 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.
