Hi, I did some more analysis and have a better understanding, so let me rephrase and simplify my question.
My query was crossing the default result set limit of 40,000 rows, due to which h2 is storing the result set to disk, which was the cause for the slow down. Increasing the limit (h2.maxMemoryRows) helped the performance a lot. But this can't scale when the database grows. On the other hand, adding a WHERE clause helps limit the number of rows in the result, which prevents h2 from writing the result set to disk in a scalable way. But, currently my custom function has to be invoked twice (once in the SELECT column list and once in the WHERE clause). Since this is an expensive function, it would be better to avoid repeating the call. Using the below CTE seems to be one way to avoid the duplicate call. It works fine in Postgresql and hsqldb. On Saturday, 4 April 2015 00:54:59 UTC+5:30, Harshad RJ wrote: > > > WITH cte(name, siml) AS ( > SELECT name, SIMILARITY(name, 'alex') AS siml > FROM Person > ) > SELECT * FROM cte > WHERE cte.siml > 0.1 > ORDER BY cte.siml DESC > LIMIT 10; > Is support for this simple CTE possible in the near future? It would help tremendously for the kind of queries I am encountering. Thanks, Harshad -- 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/d/optout.
