You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@jackrabbit.apache.org by "David G." <da...@gmail.com> on 2014/06/04 04:25:51 UTC
[Oak] Explain query command explanation
Can someone clarify how to interpret the results of an EXPLAIN query
command in Oak?
My understanding is there are 3 variations. Any pointers on other info that
can be deduced from the plans would be appreciated as well.
*Property*; denoted by "/* property" - This means the query will execute
against a property or ordered index.
Example: [nt:unstructured] as [a] /* property jcr:title where
([a].[jcr:content/jcr:title] is not null) and (isdescendantnode([a],
[/content])) */
*Traversal*; denoted by "/* traverse" - This means the query will traverse
the tree to execute
Example: [nt:unstructured] as [a] /* traverse jcr:title where
([a].[jcr:content/jcr:title] is not null) and (isdescendantnode([a],
[/content])) */
*Filter*; denoted by "/* Filter(" - Not sure what this means...
[slingevent:TimedEvent] as [a] /* Filter(query=explain select [jcr:path],
[jcr:score], * from [slingevent:TimedEvent] as a where [slingevent:created]
< cast('2014-06-03T09:58:41.069-04:00' as date) order by
[slingevent:created] /* xpath:
//element(*,slingevent:TimedEvent)[@slingevent:created <
xs:dateTime('2014-06-03T09:58:41.069-04:00')] order by @slingevent:created
ascending */, path=*,
property=[slingevent:created=..2014-06-03T09:58:41.069-04:00)]) where
[a].[slingevent:created] < cast('2014-06-03T09:58:41.069-04:00' as date) */
Re: [Oak] Explain query command explanation
Posted by Thomas Mueller <mu...@adobe.com>.
Hi,
> /* Filter( --> I see this quite a bit actually
This contains all the filter conditions for the given selector. It doesn't
mean an index will actually use all conditions in the filter; it's just
all conditions that are available.
>... Ive never seen /* ordered (even on ordered indices)
OK, I think we need to fix this in the ordered index implementation. It's
confusing if the ordered index says it's a property index, even if
internally the ordered index re-uses a part of the property index
implementation.
>Wrt to Filter( .. does this mean is will use MULTIPLE indexes?
No. I think there is some misunderstanding.
>Or can it
>also result in a traversal query?
I'm not sure what you mean. The query plan tells you which index is used.
There is one index used per selector. Traversal is an index (the traversal
index).
> Or a "mix" (get initial filter by
>property index and then "traverse" that result set checking for specific
>properties/attributes)
The query engine processes the results returned from the index, entry by
entry. First the query engine will do access rights checks (because the
index doesn't), and verifies all conditions of the query (because the
query engine doesn't know what the index already checked; except for
full-text conditions, where it trusts the full-text index). The traversal
index is not used for that however.
Regards,
Thomas
Re: [Oak] Explain query command explanation
Posted by "David G." <da...@gmail.com>.
Thanks Thomas for your valuable insight!
I already looked at the sql2_index.txt but wasnt sure how to interpret.
On Oak 1.0.0 my experience is I see Explain's in three variants
/* property
/* traverse
/* Filter( --> I see this quite a bit actually
... Ive never seen /* ordered (even on ordered indices)
Wrt to Filter( .. does this mean is will use MULTIPLE indexes? Or can it
also result in a traversal query? Or a "mix" (get initial filter by
property index and then "traverse" that result set checking for specific
properties/attributes)
On Wed, Jun 4, 2014 at 2:53 AM, Thomas Mueller <mu...@adobe.com> wrote:
> Hi,
>
> I will try... You will find examples in the test file "sql2_index.txt" in
> the source code.
>
> The plan contains the indexes that are used (one index per selector). The
> index implementation itself decides what format to use. The format is not
> yet stable; once we see more or different information is needed, the
> format will be changed.
>
> >*Property*; denoted by "/* property" - This means the query will execute
> >against a property or ordered index.
>
> The index that is in use is in the comment. The orderd index should use
> "/* ordered ... */", not "/* property ... */". I'm not sure if that's
> already the case.
>
> >Example: [nt:unstructured] as [a] /* property jcr:title where
> >([a].[jcr:content/jcr:title] is not null) and (isdescendantnode([a],
> >[/content])) */
>
> In this case it's using the property index on the property "jcr:title".
>
> >Example: [nt:unstructured] as [a] /* traverse jcr:title where
> >([a].[jcr:content/jcr:title] is not null) and (isdescendantnode([a],
> >[/content])) */
>
> This example is incorrect. A real-world example are:
>
> explain select * from [nt:base] where [jcr:uuid]=1 or [b]=2
>
> [nt:base] as [nt:base] /* traverse "*" */
>
> So, the conditions are not used here. It's traversing all nodes.
>
> explain select b.[jcr:uuid] from [nt:base] as a
> inner join [nt:base] as b on isdescendantnode(b, a)
> where (a.[jcr:uuid] = '1' or a.[jcr:uuid] = '2')
>
> [nt:base] as [a] /* property jcr:uuid
> where ([a].[jcr:uuid] is not null)
> and ([a].[jcr:uuid] in(cast('1' as string), cast('2' as string))) */
> inner join [nt:base] as [b] /* traverse "* && //path/from/join//*" */
> on isdescendantnode([b], [a])
>
> Here, the selector "a" uses a property index on "jcr:uuid". The selector
> "b" uses traversal, where the path is the one from selector "a".
>
>
>
> >*Filter*; denoted by "/* Filter(" - Not sure what this means...
>
> The filter is the list of conditions used by an index. I think it
> shouldn't be included in the query plan - is it? If yes, probably we
> should change the format as it is very verbose (it includes the query).
>
> Regards,
> Thomas
>
>
Re: [Oak] Explain query command explanation
Posted by Thomas Mueller <mu...@adobe.com>.
Hi,
I will try... You will find examples in the test file "sql2_index.txt" in
the source code.
The plan contains the indexes that are used (one index per selector). The
index implementation itself decides what format to use. The format is not
yet stable; once we see more or different information is needed, the
format will be changed.
>*Property*; denoted by "/* property" - This means the query will execute
>against a property or ordered index.
The index that is in use is in the comment. The orderd index should use
"/* ordered ... */", not "/* property ... */". I'm not sure if that's
already the case.
>Example: [nt:unstructured] as [a] /* property jcr:title where
>([a].[jcr:content/jcr:title] is not null) and (isdescendantnode([a],
>[/content])) */
In this case it's using the property index on the property "jcr:title".
>Example: [nt:unstructured] as [a] /* traverse jcr:title where
>([a].[jcr:content/jcr:title] is not null) and (isdescendantnode([a],
>[/content])) */
This example is incorrect. A real-world example are:
explain select * from [nt:base] where [jcr:uuid]=1 or [b]=2
[nt:base] as [nt:base] /* traverse "*" */
So, the conditions are not used here. It's traversing all nodes.
explain select b.[jcr:uuid] from [nt:base] as a
inner join [nt:base] as b on isdescendantnode(b, a)
where (a.[jcr:uuid] = '1' or a.[jcr:uuid] = '2')
[nt:base] as [a] /* property jcr:uuid
where ([a].[jcr:uuid] is not null)
and ([a].[jcr:uuid] in(cast('1' as string), cast('2' as string))) */
inner join [nt:base] as [b] /* traverse "* && //path/from/join//*" */
on isdescendantnode([b], [a])
Here, the selector "a" uses a property index on "jcr:uuid". The selector
"b" uses traversal, where the path is the one from selector "a".
>*Filter*; denoted by "/* Filter(" - Not sure what this means...
The filter is the list of conditions used by an index. I think it
shouldn't be included in the query plan - is it? If yes, probably we
should change the format as it is very verbose (it includes the query).
Regards,
Thomas