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) <
> nico....@gmail.com <javascript:>> 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/**features.html#user_defined_**functions<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 h2-da...@googlegroups.com.
>>>
>>> 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 h2-database...@googlegroups.com <javascript:>.
>> To post to this group, send email to h2-da...@googlegroups.com<javascript:>
>> .
>> 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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
