Hi, It sounds like "function-based indexes" would be the best way to support it. I guess it makes sense to implement this when the R-tree index of the MVStore is ready. It's a fairly significant feature I think.
Regards, Thomas On Sat, May 25, 2013 at 9:14 AM, Lukas Eder <[email protected]> wrote: > Hello, > > This probably doesn't answer all of the questions in this thread, but it > might answer some of those related to indexing with respect to spatial > functions. > > First off, the useful Postgres notation WHERE st_intersects(x, y) is > equivalent to WHERE st_intersects(x, y) = true. In other words, we can > consider Postgres' ability to parse "predicate functions" as syntactic > sugar and deal with them as regular functions. > > Some databases (most importantly, Oracle) support function-based indexes. > Some authoritative information can be seen here [1]. A function-based index > is applicable to be chosen by the optimiser if the function call signature > matches a query predicate. Assume that a user specifies: > > CREATE [UNIQUE] INDEX my_index ON my_table(lower(my_column)); > > This index is now applicable every time I query my_table using > lower(my_column), e.g. > > SELECT * FROM my_table t WHERE lower(t.my_column) = 'abc' > > In other words, given that the expression e = lower(my_table.my_column) > matches, and e is indexed, e can be substituted in the above query, > "transparently", as if it were a regular column: > > SELECT * FROM my_table t WHERE e = 'abc' > > Function-based indexes are very predictable and give users full control. I > think they might be quite a low-hanging fruit and would clearly pull their > own weight. The manual already references function based index "emulation" > [2]. > > Cheers > Lukas > > [1]: > http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_indexes.htm > [2]: http://www.h2database.com/html/features.html#computed_columns > > Am Dienstag, 21. Mai 2013 19:22:40 UTC+2 schrieb Thomas Mueller: >> >> Hi, >> >> It's a good question. The optimizer currently only knows a few ways to >> use an index: range conditions (<=, >=, >, <, =, between), in(list), >> in(select), like (which is converted to a range condition), is null, is not >> null, min(x), max(x), count(*). >> >> I wonder if there is a "generic" way to support index usage. It's won't >> be that easy I guess. For the MVStore it would be nice to have a good >> solution (so that the r-tree can be used), but we might end up "hardcoding" >> it within H2. >> >> Could you tell me what are possible operations that could use a (spatial >> or other) index, and how such an index could be used? >> >> Regards, >> Thomas >> >> >> >> On Tue, May 21, 2013 at 1:27 PM, Nicolas Fortin (OrbisGIS) < >> [email protected]> wrote: >> >>> I know function table but this lead to modifying the sql request and it >>> is not in standard. >>> >>> Le mardi 21 mai 2013 11:52:15 UTC+2, Noel Grandin a écrit : >>>> >>>> See the section title "Using a function as a table" here >>>> http://h2database.com/html/**fea**tures.html#user_defined_**functi**ons<http://h2database.com/html/features.html#user_defined_functions> >>>> >>>> On 2013-05-21 10:07, Nicolas Fortin (OrbisGIS) wrote: >>>> >>>> Discussion about user defined index on OTHER type. >>>> >>>> In spatial SQL the following request on an indexed geometry column >>>> would use spatial index: >>>> >>>> select * from spatialTable where ST_Intersects(the_geom, >>>>> ST_PolyFromText('POLYGON ((67 13, 67 18, 59 18, 59 13, 67 13))',1)); >>>>> >>>> >>>> Usage of ST_Intersects filter the_geom field by using BoundingBox >>>> stored index of the spatialTable, then the method is applied on filtered >>>> rows. >>>> >>>> Can I do this by creating my own TableEngine ? There is an easier way >>>> without modifying the sql request ? Maybe a way to let special kind of >>>> function to alter the execution plan ? >>>> >>>> Thanks for your support. >>>> >>>> -- >>>> 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 h2-database...@**googlegroups.**com. >>>> To post to this group, send email to [email protected]. >>>> >>>> Visit this group at http://groups.google.com/**group** >>>> /h2-database?hl=en <http://groups.google.com/group/h2-database?hl=en>. >>>> For more options, visit >>>> https://groups.google.com/**grou**ps/opt_out<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 h2-database...@**googlegroups.com. >>> To post to this group, send email to [email protected]. >>> Visit this group at >>> http://groups.google.com/**group/h2-database?hl=en<http://groups.google.com/group/h2-database?hl=en> >>> . >>> For more options, visit >>> https://groups.google.com/**groups/opt_out<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?hl=en. > 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?hl=en-US. For more options, visit https://groups.google.com/groups/opt_out.
