Hello, I noticed something odd when executing queries against a table holding timestamped data on 1.3.176. For simplicity lets assume the table just has 3 columns: generated ID, objectID and timestamp and there is a combined index over objectID and timestamp for performance reasons.
When executing a query like: select * from table where objectID=? and timestamp <=? and timestamp >=? everything is really fast but performance drops quite a bit when using select * from table where objectID in (?,?) and timestamp <=? and timestamp >=? Even if there is no result at all (no data with timestamp or objectID does not exist) the "in"-query is at least several hundret times slower on my test-table (~2 Million entries). In fact it looks like the index does not help at all (even though its being listed with explain) because the query is just as "fast" without it. Executing multiple queries with "objectID=?" instead of one with "objectID in(?,?,...)' is also several hundret times faster. Based on the nature of the data the selectivity of objectID is very low (1) and that of timestamp very high (98) so maybe because of that a different strategy is chosen when using "in" on objectID that favours the timestamp but can of course not use an index built over objectID and timestamp in that order. Replacing the index with one just over timestamp accelerates both queries but the "=" query gets a little slower than with the combined index and that one is my main use-case. Adding the timestamp-index in addition to the objectID_timestamp index does NOT help so one could speculate that the index gets chosen before the query-plan and if its not suitable a full table-scan is required or the unsuitable index gets picked because it covers more of the used columns. So to summarize: no index: "=" -> slow "in" -> slow objectID_timestamp index "=" -> fastest "in" -> slow timestamp index "=" -> fast "in" -> fast objectID_timestamp index timestamp index "=" -> fastest "in" -> slow timestamp_objectID index "=" -> fast "in" -> fast There are slight differences in the "fast"-category but nothing gets as fast as "fastest". Any comments on that? Wolfgang -- 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 https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
