You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@jackrabbit.apache.org by David Johnson <db...@gmail.com> on 2007/02/28 06:49:59 UTC
Query Performance and Optimization
We are exploring using Jackrabbit in a production environment. I have a
repository that we have created from our content that has > 100K nodes.
Several of our use case need to use date range queries and also use 'order
by' frequently. We have noticed that the query time is significantly slower
than necessary. After warming up the repository (i.e., running the suite of
queries once), as an example:
"select * from Column where jcr:path like 'Gossip/ColumnName/Columns/%' and
status <> 'hidden' order by publishDate desc" takes 500 ms to execute - this
is just the execution time, I am not actually using or accessing the
NodeIterator.
Whereas: "select * from Column where jcr:path like
'Gossip/ColumnName/Columns/%' and status <> 'hidden'" takes only 33 ms to
execute.
/jcr:root/Gossip/ColumnName/Columns//element(*,Column)[@publishDate >
xs:dateTime("way in the past") and @publishDate < xs:dateTime("way in the
future") and (@status != 'hidden')] order by @publishDate descending takes
1096 ms to execute.
Clearly dates (ordering and ranges) have a significant impact on query
execution speed.
Digging into the internals of Jackrabbit, we have noticed that there is an
implementation of RangeQuery that essentially walks the results if the # of
query terms is greater than what Lucene can handle. Reading the Lucene
documentation, it looks like Filters are the recommended method of
implementing "large" range queries, and also seem like a natural for
matching node types - i.e., select * from Column
Is there any ongoing work on query optimization and performance. We would
be very interested in such work, including offering any help that we can.
-Dave
Re: Query Performance and Optimization
Posted by David Johnson <db...@gmail.com>.
Any pointers and thoughts from the developers who have worked on the
LuceneQueryBuilder would be very appreciated. As an idea, I was thinking of
running the Query AST through an optimization before it is passed the the
query builder. Perhaps in
org.apache.jackrabbit.core.query.lucene.QueryImpl.execute() right before the
LueceneQueryBuilder.createQuery call.
Has anyone done any profiling on queries? I have some data that I have
gathered with the Netbeans profiler that I could share if anyone is
interested. Some highlights:
org.apache.lucene.search.Searcher.search(...) and children are taking 96%
time
of the children the first "hit" into jackrabbit code is at
org.apache.jackrabbit.core.query.lucene.SharedFiledSortComparator.newComparator(...)
with 58% time
with its child -
org.apache.jackrabbit.core.query.lucene.SharedFieldCache.getStringIndex(...)
taking all of its time.
At that point the biggest child is
org.apache.lucene.index.MultiTermDocs.next() taking the majority of the time
from then on out.
Any pointers/thoughts on either writing an optimizer for Lucene, alternate
indexing engines or even how to optimize queries would be appreciated.
-Dave
On 3/1/07, Christoph Kiehl <ki...@subshell.com> wrote:
>
> David Johnson wrote:
>
> > Digging into the internals of Jackrabbit, we have noticed that there is
> an
> > implementation of RangeQuery that essentially walks the results if the #
> of
> > query terms is greater than what Lucene can handle. Reading the Lucene
> > documentation, it looks like Filters are the recommended method of
> > implementing "large" range queries, and also seem like a natural for
> > matching node types - i.e., select * from Column
>
> As we are expecting to reach a count of 1.000.000+ nodes in one of our
> repositories I'm always interested in any performance improvements. Is
> anyone
> investigating in this proposal? Or could at least anyone tell me if it's
> worth
> investigating? ;)
>
> Cheers,
> Christoph
>
>
Re: Query Performance and Optimization
Posted by Christoph Kiehl <ki...@subshell.com>.
David Johnson wrote:
> Digging into the internals of Jackrabbit, we have noticed that there is an
> implementation of RangeQuery that essentially walks the results if the # of
> query terms is greater than what Lucene can handle. Reading the Lucene
> documentation, it looks like Filters are the recommended method of
> implementing "large" range queries, and also seem like a natural for
> matching node types - i.e., select * from Column
As we are expecting to reach a count of 1.000.000+ nodes in one of our
repositories I'm always interested in any performance improvements. Is anyone
investigating in this proposal? Or could at least anyone tell me if it's worth
investigating? ;)
Cheers,
Christoph
Re: Query Performance and Optimization
Posted by Christoph Kiehl <ch...@sulu3000.de>.
David Johnson wrote:
> Do you have a patch for the file, I would love to check it out and run it
> against my query suite.
For the eager I uploaded a quick an dirty patch for the calculateDocFilter()
caching:
http://download.yousendit.com/C4FC14DA01183678
I'll of course provide a complete patch set when I'm done with my 'research' ;).
Cheers,
Christoph
Re: Query Performance and Optimization
Posted by David Johnson <db...@gmail.com>.
Do you have a patch for the file, I would love to check it out and run it
against my query suite.
-Dave
On 3/13/07, Christoph Kiehl <ch...@sulu3000.de> wrote:
>
> Christoph Kiehl wrote:
>
> > I was digging a bit into Jackrabbit today and found another place where
> > some caching did provide a substantial performance gain to queries which
> > check one attribute for more than one value (like /foo/*[@foo:bar='john'
> > or foo:bar='doe']). The BitSet in calculateDocFilter() is right now
> > created twice for the query above. On large repositories this takes
> > about 200ms per BitSet on my machine for a particular field. Caching
> > these BitSets per IndexReader and field in a WeakHashMap with the
> > IndexReader as a key gave me some real improvements.
>
> Replying to myself ;):
>
> - I was referring to calculateDocFilter() in
> org.apache.jackrabbit.core.query.lucene.MatchAllScorer
> - The achieved performance improvement varied between 30-60% depending on
> the
> actual query
>
> Cheers,
> Christoph
>
>
Re: Query Performance and Optimization
Posted by David Johnson <db...@gmail.com>.
Both of these proposals sound great - particularly the additional caching in
DescendantSelfAxisQuery. I think this would address the scenario that I
suggested additional indexing earlier in this thread. As I mentioned, in my
query test set DescendantSelfAxisQuery.DescendantSelfAxisScorer.next() is
taking the most time, so any speed-up there would be great.
-Dave
On 3/14/07, Christoph Kiehl <ch...@sulu3000.de> wrote:
>
> Marcel Reutegger wrote:
> > Christoph Kiehl wrote:
> >>> I've created a jira issue:
> http://issues.apache.org/jira/browse/JCR-791
> >>
> >> Are you working on this issue? Or should I try to implement something?
> >
> > I just started working on it ;)
>
> Great news ;)
>
> Now that you are working on implementing this cache on a per index reader
> basis,
> I got another suggestion for improvement ;)
>
> As I understand in DescendantSelfAxisQuery.DescendantSelfAxisScorer the
> contextHits are used to filter the subHits result to only include nodes of
> the
> given context. The context is something like /foo/bar//*, which means all
> descendents of /foo/bar. Is that right?
> In our application the context for most of our queries is the same, so it
> would
> make a lot of sense to cache the contextHits for this context. There is
> already
> a todo in the constructor of DescendantSelfAxisScorer which probably aims
> at this.
> I would go even further and not only cache these contextHits, but cache
> contextHits per _node_ in a hierarchy, which means there is a BitSet for
> /foo/bar/bla[1], /foo/bar/bla[2] and so on. If I need the BitSet for
> /foo/bar//*
> I could just join the BitSets of the descendents. This would allow reuse
> the
> BitSets for different contexts. What do you think about this? It should
> improve
> performance a lot the larger the resultset is an the less specific your
> context is.
>
> >> It seems like if I rewrite the following query from
> >>
> >> /foo/*[@foo:bar!='john' and @foo:bar!='doe']
> >>
> >> to
> >>
> >> /foo/*[not(@foo:bar='john' or @foo:bar='doe')]
> >>
> >> I get a better performance. Can you confirm this?
> >
> > Yes, I can. Basically because any != comparison is translated into: get
> > all nodes with the given property, then exclude the ones that match the
> > literal. Which is obviously much more expensive than just: get all nodes
> > that match a given literal.
>
> Wouldn't it make sense to rewrite all @foo:bar!='john' queries to
> not(@foo:bar!='john') by default instead of using creating a
> MatchAllQuery?
>
> Cheers,
> Christoph
>
>
Re: Query Performance and Optimization
Posted by Marcel Reutegger <ma...@gmx.net>.
Christoph Kiehl wrote:
> Marcel Reutegger wrote:
>
>>> Wouldn't it make sense to rewrite all @foo:bar!='john' queries to
>>> not(@foo:bar!='john') by default instead of using creating a
>>> MatchAllQuery?
>>
>> do you mean rewrite: @foo:bar!='john' to not(@foo:bar='john') ?
>
> Yes, of course. My mistake. Do you think that's an option?
no I don't, because those are not equivalent. while @foo:bar!='john' only
matches those nodes that *have* a @foo:bar property with a value not equal to
'john', not(@foo:bar='john') in addition also matches all nodes which do not
even have a @foo:bar property.
not(@foo:bar='john') and @foo:bar
is equivalent to @foo:bar!='john'. at least from a result perspective. the
lucene queries that are executed are probably not the same.
regards
marcel
Re: Query Performance and Optimization
Posted by Christoph Kiehl <ch...@sulu3000.de>.
Marcel Reutegger wrote:
>> Wouldn't it make sense to rewrite all @foo:bar!='john' queries to
>> not(@foo:bar!='john') by default instead of using creating a
>> MatchAllQuery?
>
> do you mean rewrite: @foo:bar!='john' to not(@foo:bar='john') ?
Yes, of course. My mistake. Do you think that's an option?
Cheers,
Christoph
ps: I'll respond to the rest of your mail in the next days
Re: Query Performance and Optimization
Posted by Marcel Reutegger <ma...@gmx.net>.
Christoph Kiehl wrote:
> As I understand in DescendantSelfAxisQuery.DescendantSelfAxisScorer the
> contextHits are used to filter the subHits result to only include nodes
> of the given context. The context is something like /foo/bar//*, which
> means all descendents of /foo/bar. Is that right?
yes, that's correct.
> In our application the context for most of our queries is the same, so
> it would make a lot of sense to cache the contextHits for this context.
> There is already a todo in the constructor of DescendantSelfAxisScorer
> which probably aims at this.
no, not exactly. because the size of the BitSets used are equal to the overall
size of the index they may become quite large. that's why I once thought it may
be useful to reuse BitSet instances, which is not the same as caching the result
of a query.
> I would go even further and not only cache these contextHits, but cache
> contextHits per _node_ in a hierarchy, which means there is a BitSet for
> /foo/bar/bla[1], /foo/bar/bla[2] and so on. If I need the BitSet for
> /foo/bar//* I could just join the BitSets of the descendents. This would
> allow reuse the BitSets for different contexts. What do you think about
> this? It should improve performance a lot the larger the resultset is an
> the less specific your context is.
hmm, I'm not sure how you would implement that. joining the BitSets you
mentioned may as well be expensive if you reach a certain amount of them.
furthermore a BitSet for /foo/bar//* is very unstable in a sense that it will
change very frequently. with every change under /foo/bar a node gets a new
document number and we would have to create a new BitSet. I guess we would need
to find a way to efficiently modify an existing BitSet when:
- the index is updated (because of a change)
- index segments are merged (caused by a background thread)
> Wouldn't it make sense to rewrite all @foo:bar!='john' queries to
> not(@foo:bar!='john') by default instead of using creating a MatchAllQuery?
do you mean rewrite: @foo:bar!='john' to not(@foo:bar='john') ?
regards
marcel
Re: Query Performance and Optimization
Posted by Christoph Kiehl <ch...@sulu3000.de>.
Marcel Reutegger wrote:
> Christoph Kiehl wrote:
>>> I've created a jira issue: http://issues.apache.org/jira/browse/JCR-791
>>
>> Are you working on this issue? Or should I try to implement something?
>
> I just started working on it ;)
Great news ;)
Now that you are working on implementing this cache on a per index reader basis,
I got another suggestion for improvement ;)
As I understand in DescendantSelfAxisQuery.DescendantSelfAxisScorer the
contextHits are used to filter the subHits result to only include nodes of the
given context. The context is something like /foo/bar//*, which means all
descendents of /foo/bar. Is that right?
In our application the context for most of our queries is the same, so it would
make a lot of sense to cache the contextHits for this context. There is already
a todo in the constructor of DescendantSelfAxisScorer which probably aims at this.
I would go even further and not only cache these contextHits, but cache
contextHits per _node_ in a hierarchy, which means there is a BitSet for
/foo/bar/bla[1], /foo/bar/bla[2] and so on. If I need the BitSet for /foo/bar//*
I could just join the BitSets of the descendents. This would allow reuse the
BitSets for different contexts. What do you think about this? It should improve
performance a lot the larger the resultset is an the less specific your context is.
>> It seems like if I rewrite the following query from
>>
>> /foo/*[@foo:bar!='john' and @foo:bar!='doe']
>>
>> to
>>
>> /foo/*[not(@foo:bar='john' or @foo:bar='doe')]
>>
>> I get a better performance. Can you confirm this?
>
> Yes, I can. Basically because any != comparison is translated into: get
> all nodes with the given property, then exclude the ones that match the
> literal. Which is obviously much more expensive than just: get all nodes
> that match a given literal.
Wouldn't it make sense to rewrite all @foo:bar!='john' queries to
not(@foo:bar!='john') by default instead of using creating a MatchAllQuery?
Cheers,
Christoph
Re: Query Performance and Optimization
Posted by Marcel Reutegger <ma...@day.com>.
Hi Christoph,
Christoph Kiehl wrote:
>> I've created a jira issue: http://issues.apache.org/jira/browse/JCR-791
>
> Are you working on this issue? Or should I try to implement something?
I just started working on it ;)
> Actually it's /foo/*[@foo:bar!='john']
ah, yes. that makes sense.
>> @foo:bar='john' should be translated into a term query.
>
> You are right. "="-comparisons translate into term queries whereas
> "!="-comparisons gets translated into MatchAllQueries.
>
> It seems like if I rewrite the following query from
>
> /foo/*[@foo:bar!='john' and @foo:bar!='doe']
>
> to
>
> /foo/*[not(@foo:bar='john' or @foo:bar='doe')]
>
> I get a better performance. Can you confirm this?
Yes, I can. Basically because any != comparison is translated into: get all
nodes with the given property, then exclude the ones that match the literal.
Which is obviously much more expensive than just: get all nodes that match a
given literal.
regards
marcel
Re: Query Performance and Optimization
Posted by Christoph Kiehl <ch...@sulu3000.de>.
Marcel Reutegger wrote:
> Christoph Kiehl wrote:
>> Christoph Kiehl wrote:
>>
>>> I was digging a bit into Jackrabbit today and found another place
>>> where some caching did provide a substantial performance gain to
>>> queries which check one attribute for more than one value (like
>>> /foo/*[@foo:bar='john' or foo:bar='doe']). The BitSet in
>>> calculateDocFilter() is right now created twice for the query above.
>>> On large repositories this takes about 200ms per BitSet on my machine
>>> for a particular field. Caching these BitSets per IndexReader and
>>> field in a WeakHashMap with the IndexReader as a key gave me some
>>> real improvements.
>
> agreed, this should definitively be cached per index segment and is
> doable with reasonable effort.
>
> I've created a jira issue: http://issues.apache.org/jira/browse/JCR-791
Are you working on this issue? Or should I try to implement something?
>> - I was referring to calculateDocFilter() in
>> org.apache.jackrabbit.core.query.lucene.MatchAllScorer
>> - The achieved performance improvement varied between 30-60% depending
>> on the actual query
>
> but that means your query is rather:
>
> /foo/*[@foo:bar]
>
> right?
Actually it's /foo/*[@foo:bar!='john']
> @foo:bar='john' should be translated into a term query.
You are right. "="-comparisons translate into term queries whereas
"!="-comparisons gets translated into MatchAllQueries.
It seems like if I rewrite the following query from
/foo/*[@foo:bar!='john' and @foo:bar!='doe']
to
/foo/*[not(@foo:bar='john' or @foo:bar='doe')]
I get a better performance. Can you confirm this?
Cheers,
Christoph
Re: Query Performance and Optimization
Posted by Marcel Reutegger <ma...@gmx.net>.
Christoph Kiehl wrote:
> Christoph Kiehl wrote:
>
>> I was digging a bit into Jackrabbit today and found another place
>> where some caching did provide a substantial performance gain to
>> queries which check one attribute for more than one value (like
>> /foo/*[@foo:bar='john' or foo:bar='doe']). The BitSet in
>> calculateDocFilter() is right now created twice for the query above.
>> On large repositories this takes about 200ms per BitSet on my machine
>> for a particular field. Caching these BitSets per IndexReader and
>> field in a WeakHashMap with the IndexReader as a key gave me some real
>> improvements.
agreed, this should definitively be cached per index segment and is doable with
reasonable effort.
I've created a jira issue: http://issues.apache.org/jira/browse/JCR-791
> Replying to myself ;):
>
> - I was referring to calculateDocFilter() in
> org.apache.jackrabbit.core.query.lucene.MatchAllScorer
> - The achieved performance improvement varied between 30-60% depending
> on the actual query
but that means your query is rather:
/foo/*[@foo:bar]
right?
@foo:bar='john' should be translated into a term query.
regards
marcel
Re: Query Performance and Optimization
Posted by Christoph Kiehl <ch...@sulu3000.de>.
Christoph Kiehl wrote:
> I was digging a bit into Jackrabbit today and found another place where
> some caching did provide a substantial performance gain to queries which
> check one attribute for more than one value (like /foo/*[@foo:bar='john'
> or foo:bar='doe']). The BitSet in calculateDocFilter() is right now
> created twice for the query above. On large repositories this takes
> about 200ms per BitSet on my machine for a particular field. Caching
> these BitSets per IndexReader and field in a WeakHashMap with the
> IndexReader as a key gave me some real improvements.
Replying to myself ;):
- I was referring to calculateDocFilter() in
org.apache.jackrabbit.core.query.lucene.MatchAllScorer
- The achieved performance improvement varied between 30-60% depending on the
actual query
Cheers,
Christoph
Re: Query Performance and Optimization
Posted by Christoph Kiehl <ch...@sulu3000.de>.
David Johnson wrote:
> Out of the Jackrabbit code,
> DescendantSelfAxisQuery.DescendantSelfAxisScorer.next()
> is now taking the most time while executing my query suite - taking 68% of
> the time, within it, calls to
> DescendantSelfAxisQuery.DescendantSelfAxisScorer.calculateSubHits() taking
> the majority of time (basically all of the time). Then calls to
> BooleanScorer2.score(HitCollector) - back to Lucene code - is taking the
> majority of time. If more specific profiling data is desired, please feel
> free to ask. I can also share the profile data in the form of a Netbeans
> profile snapshot.
To my understanding, calculateSubHits() can be divided into to parts:
- The first part queries all nodes that are directly addressed by your xpath
(for /foo/bar//* this will be /foo/bar[1], /foo/bar[2], ...). This query is
quite fast in my experience.
- The second part does the actual work, i.e. the lucene query on the node
attributes. I don't think there is much potential for improvement here unless
you dig into lucene itself.
On the contrary to DescendantSelfAxisScorer.next(). This method takes the result
from part two (subHits) and filters all nodes that are not part of the result of
part one (contextHits) or a child node of one of the nodes in contextHits. To
filter these nodes a lot of parent-child relations have to resolved. I think
there should be some caching potential for contextHits here if you use the same
basis like /foo/bar//* for a lot of queries. But this cache would only be valid
for a particular IndexReader, that is to say it will only be beneficial if your
repository is quite stable.
I was digging a bit into Jackrabbit today and found another place where some
caching did provide a substantial performance gain to queries which check one
attribute for more than one value (like /foo/*[@foo:bar='john' or
foo:bar='doe']). The BitSet in calculateDocFilter() is right now created twice
for the query above. On large repositories this takes about 200ms per BitSet on
my machine for a particular field. Caching these BitSets per IndexReader and
field in a WeakHashMap with the IndexReader as a key gave me some real
improvements. But this caching is as well only beneficial for repositories that
are not heavily changing, as this will lead to the creation of new IndexReaders
and invalidate the Cache.
As both mentioned caches rely heavily on IndexReader reuse it would probably be
better to have caches per index segment as someone mentioned in the thread about
using Lucene filters, as segment are relatively stable.
That's what I've found out until now. I'll do some more research the next days,
as we definitely need to improve query performance for our application.
I would like to hear some comments from the JackRabbit gurus and feel free to
correct me - I just started ;)
Cheers,
Christoph
Re: Query Performance and Optimization
Posted by Marcel Reutegger <ma...@gmx.net>.
David Johnson wrote:
> While I can see how my suggested optimization could severely impact some
> use
> cases. Nevertheless, "our use case" :-) is mostly querying a stable
> hierarchy structure - i.e., we rarely, if ever, would move a tree with even
> 1000s of sub-nodes (famous last words). And we use the node hierarchy in
> our queries --- well, always!
>
> The balance between use cases is suggesting to me the need for
> administratively defined indexes that could be used by the query processing
> engine if they existed. So that users (repository administrators) could
> define the indexes - like this one - with the knowledge that certain
> operations (a move) would require a fairly expensive rebuild of the
> indexing
> structures.
>
> Could you give some more detail on how ChildAxisQuery and
> DescendantSelfAxisQuery work. On first read, the comment at the beginning
> were not completely clear to me - more than likely related to me. I
> think I
> get what they are doing, I just would like a little more overview, to help
> me before jumping in and attempting to understand the code. It seems that
> the query parser breaks down a path into its pieces, and this is then fed
> into the LuceneQueryParser as location steps - and these get changed into
> ChildAxisQuery or DescendantSelfAxisQuery as appropriate?
that's correct. both queries have a context query. the matches of the context
query is then used as a starting point to 'calculate' the child nodes or
descendant-or-self nodes.
regards
marcel
Re: Query Performance and Optimization
Posted by David Johnson <db...@gmail.com>.
DescendantSelfAxisQuery is now taking the most time in the profiling that I
have recently done.
>From my earlier post:
Out of the Jackrabbit code,
DescendantSelfAxisQuery.DescendantSelfAxisScorer.next()
is now taking the most time while executing my query suite - taking 68% of
the time, within it, calls to
DescendantSelfAxisQuery.DescendantSelfAxisScorer.calculateSubHits() taking
the majority of time (basically all of the time). Then calls to
BooleanScorer2.score(HitCollector) - back to Lucene code - is taking the
majority of time. If more specific profiling data is desired, please feel
free to ask. I can also share the profile data in the form of a Netbeans
profile snapshot.
Any magic patch that might address these performance bottlenecks? At this
point it doesn't look like the range queries are the issue anymore.
--- end of earlier post ---
While I can see how my suggested optimization could severely impact some use
cases. Nevertheless, "our use case" :-) is mostly querying a stable
hierarchy structure - i.e., we rarely, if ever, would move a tree with even
1000s of sub-nodes (famous last words). And we use the node hierarchy in
our queries --- well, always!
The balance between use cases is suggesting to me the need for
administratively defined indexes that could be used by the query processing
engine if they existed. So that users (repository administrators) could
define the indexes - like this one - with the knowledge that certain
operations (a move) would require a fairly expensive rebuild of the indexing
structures.
Could you give some more detail on how ChildAxisQuery and
DescendantSelfAxisQuery work. On first read, the comment at the beginning
were not completely clear to me - more than likely related to me. I think I
get what they are doing, I just would like a little more overview, to help
me before jumping in and attempting to understand the code. It seems that
the query parser breaks down a path into its pieces, and this is then fed
into the LuceneQueryParser as location steps - and these get changed into
ChildAxisQuery or DescendantSelfAxisQuery as appropriate?
-Dave
On 3/13/07, Marcel Reutegger <ma...@gmx.net> wrote:
>
> well, the problem with that approach is the following:
>
> assume you have a tree of nodes under /a, let's say 10 million nodes. then
> a
> user renames /a to /b. the index would have to re-index 10 million nodes.
> this
> operation is currently very efficient and takes just a couple of
> milliseconds,
> because the nodes in the index are just linked with a parent uuid.
> renaming a
> node simply means an update of one node (document) in the index.
>
> but I agree with both of you that there is a lot of potential in
> optimizing
> path/hierarchy resolution in the lucene query handler in jackrabbit. some
> optimization is already done by caching the child->parent link
> information. e.g.
> see:
>
> http://svn.apache.org/repos/asf/jackrabbit/tags/1.2.3/jackrabbit-core/src/main/java/org/apache/jackrabbit/core/query/lucene/CachingIndexReader.java
> (-> the field called 'parents')
>
> That's in the end what the ChildAxisQuery and DescendantSelfAxisQuery use.
>
> regards
> marcel
>
> Michael Neale wrote:
> > Yeah I would +1 to that, its something I do fairly often (there is often
> a
> > lot of info in a path that is relevant to a query - given that we have
> gone
> > ahead and nicely partitioned our content !).
> >
> > On 3/13/07, David Johnson <db...@gmail.com> wrote:
> >>
> >> As another example, for each node, perhaps every potential parent path
> >> could
> >> be added to the index - as an example a node at /a/b/c/d/e/f/g would
> have
> >> index entries:
> >>
> >> path1: /a
> >> path2: /a/b
> >> path3: /a/b/c
> >> path4: /a/b/c/d
> >> path5: /a/b/c/d/e
> >> path6: /a/b/c/d/e/f
> >>
> >> so queries for specific sub-paths - e.g., select * from my:type where
> >> jcr:path like '/a/b/c/%' could be mapped to a direct lucene match
> query
> >> i.e.,
> >> path3 = /a/b/c
> >>
> >> The index entry to use for the Lucene query could be determined easily
> by
> >> simple parsing of the path specified in the query.
> >>
> >> Perhaps something like this is already in the code. Is ChildAxisQuery
> >> and
> >> DescendantSelfAxisQuery currently used for cases like this?
> >>
> >> -Dave
>
>
Re: Query Performance and Optimization
Posted by Marcel Reutegger <ma...@gmx.net>.
well, the problem with that approach is the following:
assume you have a tree of nodes under /a, let's say 10 million nodes. then a
user renames /a to /b. the index would have to re-index 10 million nodes. this
operation is currently very efficient and takes just a couple of milliseconds,
because the nodes in the index are just linked with a parent uuid. renaming a
node simply means an update of one node (document) in the index.
but I agree with both of you that there is a lot of potential in optimizing
path/hierarchy resolution in the lucene query handler in jackrabbit. some
optimization is already done by caching the child->parent link information. e.g.
see:
http://svn.apache.org/repos/asf/jackrabbit/tags/1.2.3/jackrabbit-core/src/main/java/org/apache/jackrabbit/core/query/lucene/CachingIndexReader.java
(-> the field called 'parents')
That's in the end what the ChildAxisQuery and DescendantSelfAxisQuery use.
regards
marcel
Michael Neale wrote:
> Yeah I would +1 to that, its something I do fairly often (there is often a
> lot of info in a path that is relevant to a query - given that we have gone
> ahead and nicely partitioned our content !).
>
> On 3/13/07, David Johnson <db...@gmail.com> wrote:
>>
>> As another example, for each node, perhaps every potential parent path
>> could
>> be added to the index - as an example a node at /a/b/c/d/e/f/g would have
>> index entries:
>>
>> path1: /a
>> path2: /a/b
>> path3: /a/b/c
>> path4: /a/b/c/d
>> path5: /a/b/c/d/e
>> path6: /a/b/c/d/e/f
>>
>> so queries for specific sub-paths - e.g., select * from my:type where
>> jcr:path like '/a/b/c/%' could be mapped to a direct lucene match query
>> i.e.,
>> path3 = /a/b/c
>>
>> The index entry to use for the Lucene query could be determined easily by
>> simple parsing of the path specified in the query.
>>
>> Perhaps something like this is already in the code. Is ChildAxisQuery
>> and
>> DescendantSelfAxisQuery currently used for cases like this?
>>
>> -Dave
Re: Query Performance and Optimization
Posted by Michael Neale <mi...@gmail.com>.
Yeah I would +1 to that, its something I do fairly often (there is often a
lot of info in a path that is relevant to a query - given that we have gone
ahead and nicely partitioned our content !).
On 3/13/07, David Johnson <db...@gmail.com> wrote:
>
> As another example, for each node, perhaps every potential parent path
> could
> be added to the index - as an example a node at /a/b/c/d/e/f/g would have
> index entries:
>
> path1: /a
> path2: /a/b
> path3: /a/b/c
> path4: /a/b/c/d
> path5: /a/b/c/d/e
> path6: /a/b/c/d/e/f
>
> so queries for specific sub-paths - e.g., select * from my:type where
> jcr:path like '/a/b/c/%' could be mapped to a direct lucene match query
> i.e.,
> path3 = /a/b/c
>
> The index entry to use for the Lucene query could be determined easily by
> simple parsing of the path specified in the query.
>
> Perhaps something like this is already in the code. Is ChildAxisQuery and
> DescendantSelfAxisQuery currently used for cases like this?
>
> -Dave
>
> On 3/12/07, Marcel Reutegger <ma...@gmx.net> wrote:
> >
> > David Johnson wrote:
> > > I think I was again focusing on range queries and giving Lucene some
> way
> > of
> > > filtering out subsets of the document set, so that the whole document
> > set
> > > wouldn't have to be walked. For the date range query the from and to
> > dates
> > > would most likely share some set of most significant bytes - these
> bytes
> > > could just be passed to Lucene as a direct match thereby reducing the
> > > subset
> > > of the collection that would by walked. If the range query is fixed
> > this
> > > "optimization" would be unnecessary. Nevertheless, I still wonder if
> > there
> > > is additional information that could be stored in Lucene to augment
> the
> > > index and improve query processing.
> >
> > ah, now I see. yes, that might help in some cases. e.g. you could say
> get
> > me all
> > documents with a year value of 2007 and month value of 7. which would be
> > equivalent to a range query 2007-07-01 to 2007-07-31
> >
> > > In this case I was considering using the node UUID as the cross-index
> > join
> > > parameter. Still, there is the problem of combining the results from
> > two
> > > different indexes.
> >
> > there are two issues with this approach:
> > 1) getting the UUID requires lucene to load the document
> > 2) implementing an *efficient* join across system boundaries is not
> easy,
> > even
> > if the documents are sorted.
> >
> > >> 3) Use the database to provide the indexing structures.
> > >>
> > >> To me this seems to be a very interesting option, though it requires
> > >> considerable effort.
> > >
> > > Yes, I agree, this is an interesting option, and does seem that it
> would
> > > take a fair amount of effort. Your comments on the user list to this
> > same
> > > thread seems like a start to the thought process needed. I am not
> very
> > > familiar with the details of the PM, although I do think that bringing
> > > together data storage and indexing will help with improving query
> > > processing
> > > speed, as well as help with some data integrity issues that have been
> > > discussed in other threads.
> > >
> > > Over the weekend, I will see if I can come up with a solution to the
> > range
> > > query issue discussed above.
> >
> > great.
> >
> > regards
> > marcel
> >
>
Re: Query Performance and Optimization
Posted by David Johnson <db...@gmail.com>.
As another example, for each node, perhaps every potential parent path could
be added to the index - as an example a node at /a/b/c/d/e/f/g would have
index entries:
path1: /a
path2: /a/b
path3: /a/b/c
path4: /a/b/c/d
path5: /a/b/c/d/e
path6: /a/b/c/d/e/f
so queries for specific sub-paths - e.g., select * from my:type where
jcr:path like '/a/b/c/%' could be mapped to a direct lucene match query i.e.,
path3 = /a/b/c
The index entry to use for the Lucene query could be determined easily by
simple parsing of the path specified in the query.
Perhaps something like this is already in the code. Is ChildAxisQuery and
DescendantSelfAxisQuery currently used for cases like this?
-Dave
On 3/12/07, Marcel Reutegger <ma...@gmx.net> wrote:
>
> David Johnson wrote:
> > I think I was again focusing on range queries and giving Lucene some way
> of
> > filtering out subsets of the document set, so that the whole document
> set
> > wouldn't have to be walked. For the date range query the from and to
> dates
> > would most likely share some set of most significant bytes - these bytes
> > could just be passed to Lucene as a direct match thereby reducing the
> > subset
> > of the collection that would by walked. If the range query is fixed
> this
> > "optimization" would be unnecessary. Nevertheless, I still wonder if
> there
> > is additional information that could be stored in Lucene to augment the
> > index and improve query processing.
>
> ah, now I see. yes, that might help in some cases. e.g. you could say get
> me all
> documents with a year value of 2007 and month value of 7. which would be
> equivalent to a range query 2007-07-01 to 2007-07-31
>
> > In this case I was considering using the node UUID as the cross-index
> join
> > parameter. Still, there is the problem of combining the results from
> two
> > different indexes.
>
> there are two issues with this approach:
> 1) getting the UUID requires lucene to load the document
> 2) implementing an *efficient* join across system boundaries is not easy,
> even
> if the documents are sorted.
>
> >> 3) Use the database to provide the indexing structures.
> >>
> >> To me this seems to be a very interesting option, though it requires
> >> considerable effort.
> >
> > Yes, I agree, this is an interesting option, and does seem that it would
> > take a fair amount of effort. Your comments on the user list to this
> same
> > thread seems like a start to the thought process needed. I am not very
> > familiar with the details of the PM, although I do think that bringing
> > together data storage and indexing will help with improving query
> > processing
> > speed, as well as help with some data integrity issues that have been
> > discussed in other threads.
> >
> > Over the weekend, I will see if I can come up with a solution to the
> range
> > query issue discussed above.
>
> great.
>
> regards
> marcel
>
Re: Query Performance and Optimization
Posted by Marcel Reutegger <ma...@gmx.net>.
David Johnson wrote:
> I think I was again focusing on range queries and giving Lucene some way of
> filtering out subsets of the document set, so that the whole document set
> wouldn't have to be walked. For the date range query the from and to dates
> would most likely share some set of most significant bytes - these bytes
> could just be passed to Lucene as a direct match thereby reducing the
> subset
> of the collection that would by walked. If the range query is fixed this
> "optimization" would be unnecessary. Nevertheless, I still wonder if there
> is additional information that could be stored in Lucene to augment the
> index and improve query processing.
ah, now I see. yes, that might help in some cases. e.g. you could say get me all
documents with a year value of 2007 and month value of 7. which would be
equivalent to a range query 2007-07-01 to 2007-07-31
> In this case I was considering using the node UUID as the cross-index join
> parameter. Still, there is the problem of combining the results from two
> different indexes.
there are two issues with this approach:
1) getting the UUID requires lucene to load the document
2) implementing an *efficient* join across system boundaries is not easy, even
if the documents are sorted.
>> 3) Use the database to provide the indexing structures.
>>
>> To me this seems to be a very interesting option, though it requires
>> considerable effort.
>
> Yes, I agree, this is an interesting option, and does seem that it would
> take a fair amount of effort. Your comments on the user list to this same
> thread seems like a start to the thought process needed. I am not very
> familiar with the details of the PM, although I do think that bringing
> together data storage and indexing will help with improving query
> processing
> speed, as well as help with some data integrity issues that have been
> discussed in other threads.
>
> Over the weekend, I will see if I can come up with a solution to the range
> query issue discussed above.
great.
regards
marcel
Re: Query Performance and Optimization
Posted by David Johnson <db...@gmail.com>.
On 3/9/07, David Johnson <db...@gmail.com> wrote:
>
>
> -- snip --
>
> yes, this should ensure that caching in lucene is used wherever possible.
> > Even
> > though there might be bugs that prevent this. Just like this one:
> >
> > http://svn.apache.org/viewvc?view=rev&revision=506908
> >
> > which prevented the re-use of SharedFiledSortComparator even if nothing
> > changed
> > between two query execution calls. You might want to check if this patch
> > improves the situation for you.
>
>
I thought I would report some profiling results with the above patch
installed. Like I reported earlier, this patch helps query performance
significantly, and I hope it will make it into a release very soon.
With the patch, profiling is showing a very different picture than what I
reported earlier in this thread:
Out of the Jackrabbit code,
DescendantSelfAxisQuery.DescendantSelfAxisScorer.next() is now taking the
most time while executing my query suite - taking 68% of the time, within
it, calls to
DescendantSelfAxisQuery.DescendantSelfAxisScorer.calculateSubHits() taking
the majority of time (basically all of the time). Then calls to
BooleanScorer2.score(HitCollector) - back to Lucene code - is taking the
majority of time. If more specific profiling data is desired, please feel
free to ask. I can also share the profile data in the form of a Netbeans
profile snapshot.
Any magic patch that might address these performance bottlenecks? At this
point it doesn't look like the range queries are the issue anymore.
-Dave
Re: Query Performance and Optimization
Posted by David Johnson <db...@gmail.com>.
Done:
https://issues.apache.org/jira/browse/JCR-787
I did file it as a bug - as it really is an incorrect implementation (i.e.,
missing implementation) of equals and hashcode.
-Dave
On 3/12/07, Jukka Zitting <ju...@gmail.com> wrote:
>
> Hi,
>
> On 3/10/07, David Johnson <db...@gmail.com> wrote:
> > Will making an associated JIRA issue speed the inclusion of the change?
> > From my understanding it is fixing a real bug.
>
> I'm currently not planning to cut any more releases from the 1.2
> branch, as I'd like to focus on releasing 1.3 from svn trunk
> relatively quickly. I would also classify the change as a performance
> improvement rather than a bug fix, and thus wouldn't include it in a
> patch release even if one more was cut from the 1.2 branch.
>
> > I can create an issue if that will bring it into a release faster.
>
> I would appreciate an issue being filed in any case, because then we
> have something about the change to include in the 1.3 release notes.
> You can open an new issue and just mark it as resolved for 1.3. Please
> include the revision number in the resolution comment for future
> reference.
>
> BR,
>
> Jukka Zitting
>
Re: Query Performance and Optimization
Posted by Jukka Zitting <ju...@gmail.com>.
Hi,
On 3/10/07, David Johnson <db...@gmail.com> wrote:
> Will making an associated JIRA issue speed the inclusion of the change?
> From my understanding it is fixing a real bug.
I'm currently not planning to cut any more releases from the 1.2
branch, as I'd like to focus on releasing 1.3 from svn trunk
relatively quickly. I would also classify the change as a performance
improvement rather than a bug fix, and thus wouldn't include it in a
patch release even if one more was cut from the 1.2 branch.
> I can create an issue if that will bring it into a release faster.
I would appreciate an issue being filed in any case, because then we
have something about the change to include in the 1.3 release notes.
You can open an new issue and just mark it as resolved for 1.3. Please
include the revision number in the resolution comment for future
reference.
BR,
Jukka Zitting
Re: Query Performance and Optimization
Posted by David Johnson <db...@gmail.com>.
Will making an associated JIRA issue speed the inclusion of the change?
>From my understanding it is fixing a real bug. I can create an issue if
that will bring it into a release faster.
-Dave
On 3/9/07, Jukka Zitting <ju...@gmail.com> wrote:
>
> Hi,
>
> On 3/9/07, David Johnson <db...@gmail.com> wrote:
> > > Even though there might be bugs that prevent this. Just like this one:
> > >
> > > http://svn.apache.org/viewvc?view=rev&revision=506908
> > >
> > > which prevented the re-use of SharedFiledSortComparator even if
> nothing
> > > changed between two query execution calls. You might want to check
> > > if this patch improves the situation for you.
> >
> > That "patch" actually helped a fair amount - from 60 seconds to run 100
> > queries down to around 40 seconds. Not too bad. Any reason why this
> hasn't
> > made it into the current releases?
>
> The commit didn't have an associated Jira issue. I'm using the issue
> tracker for release management so any changes that don't have a a
> related Jira issue will only go out when the next release is cut from
> the svn trunk.
>
> BR,
>
> Jukka Zitting
>
Re: Query Performance and Optimization
Posted by Jukka Zitting <ju...@gmail.com>.
Hi,
On 3/9/07, David Johnson <db...@gmail.com> wrote:
> > Even though there might be bugs that prevent this. Just like this one:
> >
> > http://svn.apache.org/viewvc?view=rev&revision=506908
> >
> > which prevented the re-use of SharedFiledSortComparator even if nothing
> > changed between two query execution calls. You might want to check
> > if this patch improves the situation for you.
>
> That "patch" actually helped a fair amount - from 60 seconds to run 100
> queries down to around 40 seconds. Not too bad. Any reason why this hasn't
> made it into the current releases?
The commit didn't have an associated Jira issue. I'm using the issue
tracker for release management so any changes that don't have a a
related Jira issue will only go out when the next release is cut from
the svn trunk.
BR,
Jukka Zitting
Re: Query Performance and Optimization
Posted by David Johnson <db...@gmail.com>.
-- snip --
yes, this should ensure that caching in lucene is used wherever possible.
> Even
> though there might be bugs that prevent this. Just like this one:
>
> http://svn.apache.org/viewvc?view=rev&revision=506908
>
> which prevented the re-use of SharedFiledSortComparator even if nothing
> changed
> between two query execution calls. You might want to check if this patch
> improves the situation for you.
That "patch" actually helped a fair amount - from 60 seconds to run 100
queries down to around 40 seconds. Not too bad. Any reason why this hasn't
made it into the current releases?
> What would be the expected
> > performance change if I have ongoing updates while querying the system?
>
> depending on the query the performance will be just a bit slower or
> significantly slower. e.g. an order by in your query on a property that is
> nearly on every node will significantly slow down the query (-> see my
> previous
> post about lucenes ability to cache the SharedFiledSortComparator).
>
> > So far the experiments that I have done with Lucene filters and
> Jackrabbit
> > have been disappointing. I essentially used a QueryFilter and passed
> that
> > to the IndexSearcher in SearchIndex.executeQuery. I created filters for
> > one
> > sub-part of a query - NodeType matching created in public Object
> > visit(NodeTypeQueryNode node, Object data) of the LuceneQueryBuilder
> class.
> > Rather than adding its part of the query to the larger Lucene query, I
> had
> > the function create a QueryFilter using the sub-part of the Lucene query
> > that would have been created by the function, and then returning null
> > instead of the query. The filter was then later combined with the rest
> of
> > the query in IndexSearcher. Finally, the filter was only created once,
> and
> > added to a filter map, so that it could be reused for queries against
> the
> > same nodetype.
>
> I still think that caching the documents that match a node type will not
> help
> much because those are simple term query, which are very efficient in
> lucene.
Yes, it didn't seem to help that much.
> I also noticed that a new IndexSearcher was created for each query
> > processed
> > - is this breaking the Lucene cache of filters?
>
> No, lucene uses the index reader as the key for its caches. e.g. in
> QueryFilter.bits():
>
> synchronized (cache) { // check cache
> BitSet cached = (BitSet) cache.get(reader);
> if (cached != null) {
> return cached;
> }
> }
>
>
> > This is what I was hoping for, although I am wondering if the new
> > IndexSearcher that is created for each query execution is eliminating
> the
> > filter cache?
>
> afaics all filter caches are tied to the index reader and not the searcher
> instance.
>
> > I needed to "modify" the query tree so that the parts I "optimized" (i.e
> .,
> > removed) wouldn't create additional Lucene query terms. It seems that
> > having the visitor function return null effectively removes any terms
> that
> > may have been created by that part of the visitor - in
> LuceneQueryBuilder.
> > Is this correct?
>
> I think writing your own customized LuceneQueryBuilder is easier than
> modifying
> the query tree.
Ok, I will give that a try.thethe
> In my explorations, I have noticed that a range query e.g., (myDate >
> > startDate AND myDate < endDate) seem to be translated into two Lucene
> range
> > queries that are ANDed together that looks like - ((startDate < myDate <
> > MAX_DATE) AND (MIN_DATE < myDate < endDate)). I am guessing that Lucene
> > calculates each of the two range queries in isolation, and then ANDs the
> > results together - in a sense forcing the walk of the entire document
> set.
>
> that's correct.
>
> > Just from the look of it, it seems inefficient, although I am not sure
> how
> > much better it would be to translate the query to a single range query -
> > this transformation would also require a little analysis on the Query
> > Syntax
> > Tree - or a post optimization on the Lucene query.
>
> yes, this would be very useful and probably improve the performance
> significantly for certain cases. the LuceneQueryBuilder should probably do
> this
> optimization by analyzing the query tree.
I will see if I can puzzle out a solution to this scenario, and modify the
LuceneQueryBuilder code.
> Finally, some ideas on indexing. It seems that there are two or perhaps
> > three choices that could be made for improving indexing:
> >
> > 1) Augment the already existing Lucene index with additional information
> to
> > speed certain types of queries - e.g., range queries. For example, it
> > might
> > be possible to index each of the "bytes" of a date or long and optimize
> the
> > query using this additional information.
>
> Can you please elaborate on how this would work?
I think I was again focusing on range queries and giving Lucene some way of
filtering out subsets of the document set, so that the whole document set
wouldn't have to be walked. For the date range query the from and to dates
would most likely share some set of most significant bytes - these bytes
could just be passed to Lucene as a direct match thereby reducing the subset
of the collection that would by walked. If the range query is fixed this
"optimization" would be unnecessary. Nevertheless, I still wonder if there
is additional information that could be stored in Lucene to augment the
index and improve query processing.
> 2) Create an external indexing structure for nodetypes and fields that
> > would
> > mirror similar structures in a database. Again this data could be used
> to
> > optimize range queries as well as "sorted" results.
>
> I'm not sure how you would connect those two structures because lucene
> uses
> ephemeral document number:
>
> "IndexReader: For efficiency, in this API documents are often referred to
> via
> document numbers, non-negative integers which each name a unique document
> in the
> index. These document numbers are ephemeral--they may change as documents
> are
> added to and deleted from an index. Clients should thus not rely on a
> given
> document having the same number between sessions."
In this case I was considering using the node UUID as the cross-index join
parameter. Still, there is the problem of combining the results from two
different indexes.
> 3) Use the database to provide the indexing structures.
>
> To me this seems to be a very interesting option, though it requires
> considerable effort.
Yes, I agree, this is an interesting option, and does seem that it would
take a fair amount of effort. Your comments on the user list to this same
thread seems like a start to the thought process needed. I am not very
familiar with the details of the PM, although I do think that bringing
together data storage and indexing will help with improving query processing
speed, as well as help with some data integrity issues that have been
discussed in other threads.
Over the weekend, I will see if I can come up with a solution to the range
query issue discussed above.
-Dave
Re: Query Performance and Optimization
Posted by Marcel Reutegger <ma...@gmx.net>.
David Johnson wrote:
> In my last tests, I think I have done this - through parameters in the
> repository.xml file and recreating the entire repository. Nevertheless, I
> did not see that significant of a speed change in query response.
> Perhaps I
> wasn't using a small enough resultFetchSize (128)? I also set
> respectDocumentOrder to false. Other suggestions?
no, those two parameters will affect the performance most notably.
> In my tests so far, I am not making any changes to the repository while
> running the queries. Could this be considered a best case scenario - i.e.,
> the Lucene indexes are not being updated?
yes, this should ensure that caching in lucene is used wherever possible. Even
though there might be bugs that prevent this. Just like this one:
http://svn.apache.org/viewvc?view=rev&revision=506908
which prevented the re-use of SharedFiledSortComparator even if nothing changed
between two query execution calls. You might want to check if this patch
improves the situation for you.
> What would be the expected
> performance change if I have ongoing updates while querying the system?
depending on the query the performance will be just a bit slower or
significantly slower. e.g. an order by in your query on a property that is
nearly on every node will significantly slow down the query (-> see my previous
post about lucenes ability to cache the SharedFiledSortComparator).
> So far the experiments that I have done with Lucene filters and Jackrabbit
> have been disappointing. I essentially used a QueryFilter and passed that
> to the IndexSearcher in SearchIndex.executeQuery. I created filters for
> one
> sub-part of a query - NodeType matching created in public Object
> visit(NodeTypeQueryNode node, Object data) of the LuceneQueryBuilder class.
> Rather than adding its part of the query to the larger Lucene query, I had
> the function create a QueryFilter using the sub-part of the Lucene query
> that would have been created by the function, and then returning null
> instead of the query. The filter was then later combined with the rest of
> the query in IndexSearcher. Finally, the filter was only created once, and
> added to a filter map, so that it could be reused for queries against the
> same nodetype.
I still think that caching the documents that match a node type will not help
much because those are simple term query, which are very efficient in lucene.
> I also noticed that a new IndexSearcher was created for each query
> processed
> - is this breaking the Lucene cache of filters?
No, lucene uses the index reader as the key for its caches. e.g. in
QueryFilter.bits():
synchronized (cache) { // check cache
BitSet cached = (BitSet) cache.get(reader);
if (cached != null) {
return cached;
}
}
> This is what I was hoping for, although I am wondering if the new
> IndexSearcher that is created for each query execution is eliminating the
> filter cache?
afaics all filter caches are tied to the index reader and not the searcher instance.
> I needed to "modify" the query tree so that the parts I "optimized" (i.e.,
> removed) wouldn't create additional Lucene query terms. It seems that
> having the visitor function return null effectively removes any terms that
> may have been created by that part of the visitor - in LuceneQueryBuilder.
> Is this correct?
I think writing your own customized LuceneQueryBuilder is easier than modifying
the query tree.
> In my explorations, I have noticed that a range query e.g., (myDate >
> startDate AND myDate < endDate) seem to be translated into two Lucene range
> queries that are ANDed together that looks like - ((startDate < myDate <
> MAX_DATE) AND (MIN_DATE < myDate < endDate)). I am guessing that Lucene
> calculates each of the two range queries in isolation, and then ANDs the
> results together - in a sense forcing the walk of the entire document set.
that's correct.
> Just from the look of it, it seems inefficient, although I am not sure how
> much better it would be to translate the query to a single range query -
> this transformation would also require a little analysis on the Query
> Syntax
> Tree - or a post optimization on the Lucene query.
yes, this would be very useful and probably improve the performance
significantly for certain cases. the LuceneQueryBuilder should probably do this
optimization by analyzing the query tree.
> Finally, some ideas on indexing. It seems that there are two or perhaps
> three choices that could be made for improving indexing:
>
> 1) Augment the already existing Lucene index with additional information to
> speed certain types of queries - e.g., range queries. For example, it
> might
> be possible to index each of the "bytes" of a date or long and optimize the
> query using this additional information.
Can you please elaborate on how this would work?
> 2) Create an external indexing structure for nodetypes and fields that
> would
> mirror similar structures in a database. Again this data could be used to
> optimize range queries as well as "sorted" results.
I'm not sure how you would connect those two structures because lucene uses
ephemeral document number:
"IndexReader: For efficiency, in this API documents are often referred to via
document numbers, non-negative integers which each name a unique document in the
index. These document numbers are ephemeral--they may change as documents are
added to and deleted from an index. Clients should thus not rely on a given
document having the same number between sessions."
> 3) Use the database to provide the indexing structures.
To me this seems to be a very interesting option, though it requires
considerable effort.
> Short term, I would like to see if there is any low hanging fruit in #1
> that
> could be used to significantly speed up several types of queries. I would
> also like to create some "theoretical" benchmarks to shoot for - i.e., what
> is the fastest that we could expect to access a node by UUID? through a
> range query? matching a particular node type and value? Since database
> technology is mature and well optimized, this might be a good place to look
> for "the best that would could ever expect" as far as query speed goes and
> then compare Jackrabbit's results (query processing time). I am not
> interested in matching DB speeds one-to-one, although I think it can be a
> useful benchmark that might get us thinking about why certain mismatches
> occur.
Agreed. Such benchmarks would be very useful.
regards
marcel
Re: Query Performance and Optimization
Posted by David Johnson <db...@gmail.com>.
On 3/6/07, Marcel Reutegger <ma...@gmx.net> wrote:
>
> Hi David,
>
> David Johnson wrote:
> > Yes, I am using Jackrabbit 1.2.x and I am not seeing that dramatic of a
> > difference between 1.1.x and the 1.2.x, although I have not done a
> direct
> > comparison between the two with the same query suite.
>
> please note that you have to change the configuration to get the
> performance
> improvement mentioned by jukka. See:
> http://issues.apache.org/jira/browse/JCR-651
In my last tests, I think I have done this - through parameters in the
repository.xml file and recreating the entire repository. Nevertheless, I
did not see that significant of a speed change in query response. Perhaps I
wasn't using a small enough resultFetchSize (128)? I also set
respectDocumentOrder to false. Other suggestions? I am also using the
1.2.3 code base w/ the bundle persistence manager patch. Interestingly, I
did see a 15% speed improvement using the bundle persistence manager patch
alone.
> It looks like adding
> > ordering and or large range queries can significantly impact the query
> > time.
>
> I've also noticed this performance hit, but so far didn't find a way to
> improve
> the situation.
>
> For the interested readers, here are the details:
>
> sorting on custom fields in lucene is imo not very well integrated into
> the core
> because it was added in a later version (1.4). in addition lucene
> primarily
> focuses on and is optimized for sorting by relevance.
>
> lucene is also not very well suited for incremental index updates, it is
> optimized for large batch updates and then assumes that an index reader is
> kept
> open for a long time. to accommodate this limitation, jackrabbit uses its
> own
> index segments to keep index readers open as long as possible. this allows
> lucene to use internal caches and speeds up index updates.
In my tests so far, I am not making any changes to the repository while
running the queries. Could this be considered a best case scenario - i.e.,
the Lucene indexes are not being updated? What would be the expected
performance change if I have ongoing updates while querying the system?
in certain cases however lucene caches are bound to the top level multi
> reader
> that spans the jackrabbit index segments. this is unfortunate because this
> multi
> reader will change whenever the index is updated and as an effect will
> invalidate caches that have a dependency to this multi index.
>
> on of the them is the ScoreDocComparator created in FieldSortedHitQueue
> which is
> cached using a dependency to the top level multi reader. A
> ScoreDocComparator
> for relevance sorting is trivial and light weight, but a
> ScoreDocComparator on a
> field is quite heavy weight and is therefore cached in lucene.
>
> some time ago I thought jackrabbit could use one ScoreDocComparator per
> index
> segment and keep the comparators on index segments but that requires an
> even
> more heavy weight version of the comparator that keeps the values of the
> index
> terms.
>
> > I would be very interested in working on and/or looking into
> > optimization strategies and/or experiments. While I can puzzle out the
> > code, and the structure of the query syntax tree, any
> > pointers/documentation
> > would be very much appreciated.
>
> any help is very much appreciated. currently there is only one document
> that
> gives an overview over the query implementation [1] plus the javadoc in
> the
> sources of course.
So far, I am just coming up with ideas and if the implementation is simple
enough giving them a try. So far, no good news to report, although see
below for additional information on experiments with filters.
if you have any questions, feel free to ask on the dev list.
>
> > In the case of an order by query and large range queries, it looks like
> > significant time is spent in the
> > org.apache.jackrabbit.core.query.lucene.SharedFiledSortComparator
> > class, and that the result set is being walked in order to make sure
> that
> > each result matches the query parameters and probably for sorting
> purposes.
> > In the best case it would be good to have a pre-sorted index so that
> sorted
> > results could be returned much more quickly. Similarly, the ability to
> > define indexes on node properties could be used to speed results. Both
> of
> > these types of indexes could be modeled and probably implemented
> similarly
> > to well-known database indexing schemes.
>
> jackrabbit already indexes properties using lucene. that is, the lucene
> query
> handler in jackrabbit already has the ability to enumerate documents (or
> nodes
> respectively) in sorted order for a certain property. however, the problem
> here
> is, that lucene initially orders documents using document numbers while it
> calculates the matching documents. This is different to database systems
> where
> the dbms already uses presorted sets according to the order by clause. so,
> I'm
> not sure how to optimize sorting in lucene in a dbms way.
>
> hmm, maybe we could use scoring, but I'm not sure how reliable this would
> work... instead of using a regular score how well a document matches
> against a
> certain term we could implements a score value according to the term value
> and
> the sort order. this would much better leverage the internal structure and
> workings of lucene. anyone tried this before?
>
> > Finally, there are also optimizations - more tailored to JSR-170 needs -
> > that could be implemented with Lucene filters - specifically Node Type
> > query
> > constraints - e.g., select * from my:mixin - the Filter could be defined
> to
> > limit query searches / results to only those nodes that fulfill the
> > specific
> > node type constraint. These filters could be defined for each custom
> node
> > type defined in the system and pre-calculated.
>
> again filters are one of the components in a lucene query that have a
> dependency
> on the top level index reader. because in jackrabbit the index reader
> changes
> with every update the filter will have to be re-calculated with every
> update.
> unless we find a way to create and use filters per index segments.
>
> I'm not sure if this would even give much of a performance improvement,
> because
> node type constraints are basically simple term queries that are very fast
> in
> lucene because those are the building blocks of more complex queries.
So far the experiments that I have done with Lucene filters and Jackrabbit
have been disappointing. I essentially used a QueryFilter and passed that
to the IndexSearcher in SearchIndex.executeQuery. I created filters for one
sub-part of a query - NodeType matching created in public Object
visit(NodeTypeQueryNode node, Object data) of the LuceneQueryBuilder class.
Rather than adding its part of the query to the larger Lucene query, I had
the function create a QueryFilter using the sub-part of the Lucene query
that would have been created by the function, and then returning null
instead of the query. The filter was then later combined with the rest of
the query in IndexSearcher. Finally, the filter was only created once, and
added to a filter map, so that it could be reused for queries against the
same nodetype.
I also noticed that a new IndexSearcher was created for each query processed
- is this breaking the Lucene cache of filters?
As a side note, all tests passed during the normal build. In any case,
performance with this modification was significantly worse.
> I have not worked with Lucene Filters before, so I am not familiar with
> > their speed - it might be interesting to get input from someone that has
> > more experience with the run time behavior of Filters. Nevertheless,
> the
> > Lucene docs mention Filters as a method to get around large range
> queries
> > that inevitably break the 1024 Lucene query term limit.
>
> filters in lucene are fast once you have them, but usually expensive to
> calculate. because you need to re-calculate them again after you changed
> the
> index it is important to minimize the number of time you do that. as
> mentioned
> above I think using filters only makes sense if we can use one filter per
> index
> segment and only re-calculate those filters for the youngest segment or
> for
> merged segments.
This is what I was hoping for, although I am wondering if the new
IndexSearcher that is created for each query execution is eliminating the
filter cache?
> Further information on the Query Syntax Tree that is used by the
> > LuceneQueryBuilder - and "safe" ways to modify it i.e., I would rather
> > only
> > modify the Query Syntax Tree and continue to use the LuceneQueryBuilder
> for
> > most of the query processing - would be appreciated.
>
> why do you want to modify the query tree?
I needed to "modify" the query tree so that the parts I "optimized" (i.e.,
removed) wouldn't create additional Lucene query terms. It seems that
having the visitor function return null effectively removes any terms that
may have been created by that part of the visitor - in LuceneQueryBuilder.
Is this correct?
regards
> marcel
>
> [1] http://jackrabbit.apache.org/doc/arch/operate/query.html
In my explorations, I have noticed that a range query e.g., (myDate >
startDate AND myDate < endDate) seem to be translated into two Lucene range
queries that are ANDed together that looks like - ((startDate < myDate <
MAX_DATE) AND (MIN_DATE < myDate < endDate)). I am guessing that Lucene
calculates each of the two range queries in isolation, and then ANDs the
results together - in a sense forcing the walk of the entire document set.
Just from the look of it, it seems inefficient, although I am not sure how
much better it would be to translate the query to a single range query -
this transformation would also require a little analysis on the Query Syntax
Tree - or a post optimization on the Lucene query.
Finally, some ideas on indexing. It seems that there are two or perhaps
three choices that could be made for improving indexing:
1) Augment the already existing Lucene index with additional information to
speed certain types of queries - e.g., range queries. For example, it might
be possible to index each of the "bytes" of a date or long and optimize the
query using this additional information.
2) Create an external indexing structure for nodetypes and fields that would
mirror similar structures in a database. Again this data could be used to
optimize range queries as well as "sorted" results.
3) Use the database to provide the indexing structures.
For ease of development, I tend to prefer #1, although I am worried that
ultimately this is becoming a fitting a square peg in a round hole - i.e.,
Lucene really was not developed to serve as a general purpose database
indexing engine. I am also worried about #3 since the ultimate db
persistence engine can be changed depending how the repository is
configured. #2 seems to also want "language" extensions so that a user or
administrator can define indexes and thereby tune the query processing of
the repository, similarly to how a database addresses these problems.
Short term, I would like to see if there is any low hanging fruit in #1 that
could be used to significantly speed up several types of queries. I would
also like to create some "theoretical" benchmarks to shoot for - i.e., what
is the fastest that we could expect to access a node by UUID? through a
range query? matching a particular node type and value? Since database
technology is mature and well optimized, this might be a good place to look
for "the best that would could ever expect" as far as query speed goes and
then compare Jackrabbit's results (query processing time). I am not
interested in matching DB speeds one-to-one, although I think it can be a
useful benchmark that might get us thinking about why certain mismatches
occur.
-Dave
Re: Query Performance and Optimization
Posted by Marcel Reutegger <ma...@gmx.net>.
Hi David,
David Johnson wrote:
> Yes, I am using Jackrabbit 1.2.x and I am not seeing that dramatic of a
> difference between 1.1.x and the 1.2.x, although I have not done a direct
> comparison between the two with the same query suite.
please note that you have to change the configuration to get the performance
improvement mentioned by jukka. See: http://issues.apache.org/jira/browse/JCR-651
> It looks like adding
> ordering and or large range queries can significantly impact the query
> time.
I've also noticed this performance hit, but so far didn't find a way to improve
the situation.
For the interested readers, here are the details:
sorting on custom fields in lucene is imo not very well integrated into the core
because it was added in a later version (1.4). in addition lucene primarily
focuses on and is optimized for sorting by relevance.
lucene is also not very well suited for incremental index updates, it is
optimized for large batch updates and then assumes that an index reader is kept
open for a long time. to accommodate this limitation, jackrabbit uses its own
index segments to keep index readers open as long as possible. this allows
lucene to use internal caches and speeds up index updates.
in certain cases however lucene caches are bound to the top level multi reader
that spans the jackrabbit index segments. this is unfortunate because this multi
reader will change whenever the index is updated and as an effect will
invalidate caches that have a dependency to this multi index.
on of the them is the ScoreDocComparator created in FieldSortedHitQueue which is
cached using a dependency to the top level multi reader. A ScoreDocComparator
for relevance sorting is trivial and light weight, but a ScoreDocComparator on a
field is quite heavy weight and is therefore cached in lucene.
some time ago I thought jackrabbit could use one ScoreDocComparator per index
segment and keep the comparators on index segments but that requires an even
more heavy weight version of the comparator that keeps the values of the index
terms.
> I would be very interested in working on and/or looking into
> optimization strategies and/or experiments. While I can puzzle out the
> code, and the structure of the query syntax tree, any
> pointers/documentation
> would be very much appreciated.
any help is very much appreciated. currently there is only one document that
gives an overview over the query implementation [1] plus the javadoc in the
sources of course.
if you have any questions, feel free to ask on the dev list.
> In the case of an order by query and large range queries, it looks like
> significant time is spent in the
> org.apache.jackrabbit.core.query.lucene.SharedFiledSortComparator
> class, and that the result set is being walked in order to make sure that
> each result matches the query parameters and probably for sorting purposes.
> In the best case it would be good to have a pre-sorted index so that sorted
> results could be returned much more quickly. Similarly, the ability to
> define indexes on node properties could be used to speed results. Both of
> these types of indexes could be modeled and probably implemented similarly
> to well-known database indexing schemes.
jackrabbit already indexes properties using lucene. that is, the lucene query
handler in jackrabbit already has the ability to enumerate documents (or nodes
respectively) in sorted order for a certain property. however, the problem here
is, that lucene initially orders documents using document numbers while it
calculates the matching documents. This is different to database systems where
the dbms already uses presorted sets according to the order by clause. so, I'm
not sure how to optimize sorting in lucene in a dbms way.
hmm, maybe we could use scoring, but I'm not sure how reliable this would
work... instead of using a regular score how well a document matches against a
certain term we could implements a score value according to the term value and
the sort order. this would much better leverage the internal structure and
workings of lucene. anyone tried this before?
> Finally, there are also optimizations - more tailored to JSR-170 needs -
> that could be implemented with Lucene filters - specifically Node Type
> query
> constraints - e.g., select * from my:mixin - the Filter could be defined to
> limit query searches / results to only those nodes that fulfill the
> specific
> node type constraint. These filters could be defined for each custom node
> type defined in the system and pre-calculated.
again filters are one of the components in a lucene query that have a dependency
on the top level index reader. because in jackrabbit the index reader changes
with every update the filter will have to be re-calculated with every update.
unless we find a way to create and use filters per index segments.
I'm not sure if this would even give much of a performance improvement, because
node type constraints are basically simple term queries that are very fast in
lucene because those are the building blocks of more complex queries.
> I have not worked with Lucene Filters before, so I am not familiar with
> their speed - it might be interesting to get input from someone that has
> more experience with the run time behavior of Filters. Nevertheless, the
> Lucene docs mention Filters as a method to get around large range queries
> that inevitably break the 1024 Lucene query term limit.
filters in lucene are fast once you have them, but usually expensive to
calculate. because you need to re-calculate them again after you changed the
index it is important to minimize the number of time you do that. as mentioned
above I think using filters only makes sense if we can use one filter per index
segment and only re-calculate those filters for the youngest segment or for
merged segments.
> Further information on the Query Syntax Tree that is used by the
> LuceneQueryBuilder - and "safe" ways to modify it i.e., I would rather
> only
> modify the Query Syntax Tree and continue to use the LuceneQueryBuilder for
> most of the query processing - would be appreciated.
why do you want to modify the query tree?
regards
marcel
[1] http://jackrabbit.apache.org/doc/arch/operate/query.html
Re: Query Performance and Optimization
Posted by David Johnson <db...@gmail.com>.
Hi Jukka,
Thanks for the reply.
Yes, I am using Jackrabbit 1.2.x and I am not seeing that dramatic of a
difference between 1.1.x and the 1.2.x, although I have not done a direct
comparison between the two with the same query suite. It looks like adding
ordering and or large range queries can significantly impact the query
time. I would be very interested in working on and/or looking into
optimization strategies and/or experiments. While I can puzzle out the
code, and the structure of the query syntax tree, any pointers/documentation
would be very much appreciated.
In the case of an order by query and large range queries, it looks like
significant time is spent in the
org.apache.jackrabbit.core.query.lucene.SharedFiledSortComparator
class, and that the result set is being walked in order to make sure that
each result matches the query parameters and probably for sorting purposes.
In the best case it would be good to have a pre-sorted index so that sorted
results could be returned much more quickly. Similarly, the ability to
define indexes on node properties could be used to speed results. Both of
these types of indexes could be modeled and probably implemented similarly
to well-known database indexing schemes.
Finally, there are also optimizations - more tailored to JSR-170 needs -
that could be implemented with Lucene filters - specifically Node Type query
constraints - e.g., select * from my:mixin - the Filter could be defined to
limit query searches / results to only those nodes that fulfill the specific
node type constraint. These filters could be defined for each custom node
type defined in the system and pre-calculated.
I have not worked with Lucene Filters before, so I am not familiar with
their speed - it might be interesting to get input from someone that has
more experience with the run time behavior of Filters. Nevertheless, the
Lucene docs mention Filters as a method to get around large range queries
that inevitably break the 1024 Lucene query term limit.
Further information on the Query Syntax Tree that is used by the
LuceneQueryBuilder - and "safe" ways to modify it i.e., I would rather only
modify the Query Syntax Tree and continue to use the LuceneQueryBuilder for
most of the query processing - would be appreciated.
-Dave
On 3/2/07, Jukka Zitting <ju...@gmail.com> wrote:
>
> Hi,,
>
> On 2/28/07, David Johnson <db...@gmail.com> wrote:
> > "select * from Column where jcr:path like 'Gossip/ColumnName/Columns/%'
> and
> > status <> 'hidden' order by publishDate desc" takes 500 ms to execute -
> this
> > is just the execution time, I am not actually using or accessing the
> > NodeIterator.
>
> Are you using Jackrabbit 1.2.x? Jackrabbit 1.2 uses lazy loading of
> query results, which should considerably reduce query execution time
> by moving the effort to the resulting Node- or RowIterator.
>
> In general my rule of thumb so far has been to use the query feature
> when you want a narrow selection of nodes from a large source set, and
> to use explicit traversal with filtering when the expected result set
> includes a considerable percentage of the source set. Optimally the
> query feature should in all cases be at least equal to traversal speed
> plus a small constant query parsing and setup overhead. I don't think
> we are there yet.
>
> > Digging into the internals of Jackrabbit, we have noticed that there is
> an
> > implementation of RangeQuery that essentially walks the results if the #
> of
> > query terms is greater than what Lucene can handle. Reading the Lucene
> > documentation, it looks like Filters are the recommended method of
> > implementing "large" range queries, and also seem like a natural for
> > matching node types - i.e., select * from Column
>
> I'm not too familiar with Lucene details to comment on whether Filters
> would cover everything we need. It would be great if you're interested
> in pursuing such alternatives!
>
> > Is there any ongoing work on query optimization and performance. We
> would
> > be very interested in such work, including offering any help that we
> can.
>
> Not apart from the recent lazy loading improvements. Any help would be
> much appreciated.
>
> BR,
>
> Jukka Zitting
>
Re: Query Performance and Optimization
Posted by Jukka Zitting <ju...@gmail.com>.
Hi,,
On 2/28/07, David Johnson <db...@gmail.com> wrote:
> "select * from Column where jcr:path like 'Gossip/ColumnName/Columns/%' and
> status <> 'hidden' order by publishDate desc" takes 500 ms to execute - this
> is just the execution time, I am not actually using or accessing the
> NodeIterator.
Are you using Jackrabbit 1.2.x? Jackrabbit 1.2 uses lazy loading of
query results, which should considerably reduce query execution time
by moving the effort to the resulting Node- or RowIterator.
In general my rule of thumb so far has been to use the query feature
when you want a narrow selection of nodes from a large source set, and
to use explicit traversal with filtering when the expected result set
includes a considerable percentage of the source set. Optimally the
query feature should in all cases be at least equal to traversal speed
plus a small constant query parsing and setup overhead. I don't think
we are there yet.
> Digging into the internals of Jackrabbit, we have noticed that there is an
> implementation of RangeQuery that essentially walks the results if the # of
> query terms is greater than what Lucene can handle. Reading the Lucene
> documentation, it looks like Filters are the recommended method of
> implementing "large" range queries, and also seem like a natural for
> matching node types - i.e., select * from Column
I'm not too familiar with Lucene details to comment on whether Filters
would cover everything we need. It would be great if you're interested
in pursuing such alternatives!
> Is there any ongoing work on query optimization and performance. We would
> be very interested in such work, including offering any help that we can.
Not apart from the recent lazy loading improvements. Any help would be
much appreciated.
BR,
Jukka Zitting
Re: Query Performance and Optimization
Posted by Marcel Reutegger <ma...@gmx.net>.
Jukka Zitting wrote:
> Note that the hierarchical content model of JCR is fundamentally
> different from the relational model. There is no way you could achieve
> such a mapping without major compromizes especially with residual item
> definitions but also with things like type inheritance and even
> handling of the tree structure.
there is a third way: use the database but do not expose/store nodes of
different types in separate tables. it would be similar to what the current DB
PMs do, but in addition make the following information available to queries:
- hierarchy information
- property values and types
- node name
currently this information is buried in the serialized item state.
the lucene query handler already does this because that's what is required to
execute a query: the node name, the hierarchy information and values of properties.
yes, that's all you need. the type information is already present in the
jcr:primaryType property value and the type hierarchy is given by the node type
registry. IMO the node types do not have to be represented as separate tables.
> In fact I think the use of relational databases in the current
> database persistence managers and the incoming bundle persistence
> managers is a necessary evil until we come up with a "native"
> persistence model that achieves the required ACID features without the
> need for an external component to "do its thing". :-)
but will this native persistence model also have query support? the current
jackrabbit design offers separation between data storage and indexing, but wrt
data integrity and performance it is maybe beneficial to have both in one pot
(at least in some cases).
regards
marcel
Re: Query Performance and Optimization
Posted by Jukka Zitting <ju...@gmail.com>.
Hi,
On 3/8/07, Michael Neale <mi...@gmail.com> wrote:
> On 3/7/07, Stefan Guggisberg <st...@gmail.com> wrote:
> > On 3/7/07, Michael Neale <mi...@gmail.com> wrote:
> > > The DDL generation kind of scares me, in terms of complexity, but I
> > > think its necessary to let RDBMS "do its thing" so to speak?
> >
> > why?
>
> Mainly for queries. if we have a node type def that has something:title,
> something:size etc... then if they map to columns in a table called
> something_title, something_age we can get the RDBMS to do indexing.
Note that the hierarchical content model of JCR is fundamentally
different from the relational model. There is no way you could achieve
such a mapping without major compromizes especially with residual item
definitions but also with things like type inheritance and even
handling of the tree structure.
> However, this is turning jackrabbit into a kind of ORM itself - probably
> not one of the aims ;)
Exactly. There are already a number of mature ORM tools out there,
each with the fundamental limitations of the ORM approach. Jackrabbit
is actually breaking those barriers by offering an alternative storage
model and imposing the relational model on Jackrabbit would IMHO be a
major step backwards.
In fact I think the use of relational databases in the current
database persistence managers and the incoming bundle persistence
managers is a necessary evil until we come up with a "native"
persistence model that achieves the required ACID features without the
need for an external component to "do its thing". :-)
BR,
Jukka Zitting
Re: Query Performance and Optimization
Posted by Michael Neale <mi...@gmail.com>.
On 3/7/07, Stefan Guggisberg <st...@gmail.com> wrote:
>
> On 3/7/07, Michael Neale <mi...@gmail.com> wrote:
> > Hi Marcel - yes it would be interesting - I guess to get the most out of
> it,
> > the node type definitions would have to come into play to generate DDL
> for
> > the database - so the node type definitions will map to a more "tuned"
> > database schema - of course some concepts may not work that way, like
>
> i guess by "tuned" you mean a normalized schema. why do you think that
> such a normalized schema would improve performance?
Mainly allowing the RDBMS to perform queries - natively.
> hierarchies, or "nt:unstructured" in which case it would need to use the
> > current style.
> >
> > As for fulltext - database support varies with each vendor, so I would
> > hazard a guess that lucene would still need to be part of it (that is
> the
> > approach that the newer versions of hibernate have taken - take full
> text
> > out of the hands of the database).
> >
> > The DDL generation kind of scares me, in terms of complexity, but I
> think
> > its necessary to let RDBMS "do its thing" so to speak?
>
> why?
Mainly for queries. if we have a node type def that has something:title,
something:size etc... then if they map to columns in a table called
something_title, something_age we can get the RDBMS to do indexing. However,
this is turning jackrabbit into a kind of ORM itself - probably not one of
the aims ;)
> ORM tools can certainly help here - can avoid programmatically generating
> > DDL by instead generating a meta model that ORM tools work off - just a
> > thought (let the ORMs generate DB specific schemas).
> >
> > I know RDBMS are a proven way to scale up - but as for content, I am a
> > novice, so I am happy to follow the lead of those in the know in how
> best to
> > help jackrabbit scale. So far I have not been that "whelmed" by the
> query
> > performance - I am using the SQL dialect cause its familiar, but I think
> its
> > familiarity makes me want to do things that it is perhaps not optimised
> for,
> > maybe that is my problem.
> >
> > I should and will join the dev list, so as to not pollute the user list
> with
> > ponderings over jackrabbit internals ;)
> >
> > Thoughts?
> >
> > Michael.
> >
> > On 3/6/07, Marcel Reutegger <ma...@gmx.net> wrote:
> > >
> > > Michael Neale wrote:
> > > > I know from previous discussions that it is a design decision of
> > > Jackrabbit
> > > > to not exlcusively work with RDBMS - if it was, I would be all in
> favour
> > > of
> > > > leaning on it to do the hardwork.
> > >
> > > please note that it is possible to exclusively use an RDBMS for
> storing
> > > and
> > > querying content, though you have to create your own persistence
> manager
> > > and
> > > query handler. the jackrabbit core does not force you to separate the
> > > store and
> > > the index.
> > >
> > > but you are right that it was a design decision to allow separation if
> you
> > > want
> > > to. because jackrabbit initially only had plain file based persistence
> > > managers
> > > and because lucene provides very good fulltext indexing we decided to
> go
> > > with
> > > lucene.
> > >
> > > coming back to the RDBMS only approach. you would have to implement a
> > > persistence manager that stores nodes and properties in a way that
> allows
> > > the
> > > database to use its indexes. then create a query handler that
> translates
> > > an
> > > abstract query tree into a SQL statement based on the database schema.
> > >
> > > there are some obstacles you will have to overcome (or actually the
> > > database):
> > > 1) handle node hierarchies (e.g. get all ancestors of a certain node)
> > > 2) provide fulltext indexing
> > >
> > > I think this would be a very useful extension for jackrabbit. so, if
> > > anyone is
> > > interested in implementing this, I'm very curious how well it performs
> > > compared
> > > to the current implementation using lucene.
> > >
> > > regards
> > > marcel
> > >
> >
>
Re: Query Performance and Optimization
Posted by Stefan Guggisberg <st...@gmail.com>.
On 3/7/07, Michael Neale <mi...@gmail.com> wrote:
> Hi Marcel - yes it would be interesting - I guess to get the most out of it,
> the node type definitions would have to come into play to generate DDL for
> the database - so the node type definitions will map to a more "tuned"
> database schema - of course some concepts may not work that way, like
i guess by "tuned" you mean a normalized schema. why do you think that
such a normalized schema would improve performance?
> hierarchies, or "nt:unstructured" in which case it would need to use the
> current style.
>
> As for fulltext - database support varies with each vendor, so I would
> hazard a guess that lucene would still need to be part of it (that is the
> approach that the newer versions of hibernate have taken - take full text
> out of the hands of the database).
>
> The DDL generation kind of scares me, in terms of complexity, but I think
> its necessary to let RDBMS "do its thing" so to speak?
why?
> ORM tools can certainly help here - can avoid programmatically generating
> DDL by instead generating a meta model that ORM tools work off - just a
> thought (let the ORMs generate DB specific schemas).
>
> I know RDBMS are a proven way to scale up - but as for content, I am a
> novice, so I am happy to follow the lead of those in the know in how best to
> help jackrabbit scale. So far I have not been that "whelmed" by the query
> performance - I am using the SQL dialect cause its familiar, but I think its
> familiarity makes me want to do things that it is perhaps not optimised for,
> maybe that is my problem.
>
> I should and will join the dev list, so as to not pollute the user list with
> ponderings over jackrabbit internals ;)
>
> Thoughts?
>
> Michael.
>
> On 3/6/07, Marcel Reutegger <ma...@gmx.net> wrote:
> >
> > Michael Neale wrote:
> > > I know from previous discussions that it is a design decision of
> > Jackrabbit
> > > to not exlcusively work with RDBMS - if it was, I would be all in favour
> > of
> > > leaning on it to do the hardwork.
> >
> > please note that it is possible to exclusively use an RDBMS for storing
> > and
> > querying content, though you have to create your own persistence manager
> > and
> > query handler. the jackrabbit core does not force you to separate the
> > store and
> > the index.
> >
> > but you are right that it was a design decision to allow separation if you
> > want
> > to. because jackrabbit initially only had plain file based persistence
> > managers
> > and because lucene provides very good fulltext indexing we decided to go
> > with
> > lucene.
> >
> > coming back to the RDBMS only approach. you would have to implement a
> > persistence manager that stores nodes and properties in a way that allows
> > the
> > database to use its indexes. then create a query handler that translates
> > an
> > abstract query tree into a SQL statement based on the database schema.
> >
> > there are some obstacles you will have to overcome (or actually the
> > database):
> > 1) handle node hierarchies (e.g. get all ancestors of a certain node)
> > 2) provide fulltext indexing
> >
> > I think this would be a very useful extension for jackrabbit. so, if
> > anyone is
> > interested in implementing this, I'm very curious how well it performs
> > compared
> > to the current implementation using lucene.
> >
> > regards
> > marcel
> >
>
Re: Query Performance and Optimization
Posted by Michael Neale <mi...@gmail.com>.
Hi Marcel - yes it would be interesting - I guess to get the most out of it,
the node type definitions would have to come into play to generate DDL for
the database - so the node type definitions will map to a more "tuned"
database schema - of course some concepts may not work that way, like
hierarchies, or "nt:unstructured" in which case it would need to use the
current style.
As for fulltext - database support varies with each vendor, so I would
hazard a guess that lucene would still need to be part of it (that is the
approach that the newer versions of hibernate have taken - take full text
out of the hands of the database).
The DDL generation kind of scares me, in terms of complexity, but I think
its necessary to let RDBMS "do its thing" so to speak?
ORM tools can certainly help here - can avoid programmatically generating
DDL by instead generating a meta model that ORM tools work off - just a
thought (let the ORMs generate DB specific schemas).
I know RDBMS are a proven way to scale up - but as for content, I am a
novice, so I am happy to follow the lead of those in the know in how best to
help jackrabbit scale. So far I have not been that "whelmed" by the query
performance - I am using the SQL dialect cause its familiar, but I think its
familiarity makes me want to do things that it is perhaps not optimised for,
maybe that is my problem.
I should and will join the dev list, so as to not pollute the user list with
ponderings over jackrabbit internals ;)
Thoughts?
Michael.
On 3/6/07, Marcel Reutegger <ma...@gmx.net> wrote:
>
> Michael Neale wrote:
> > I know from previous discussions that it is a design decision of
> Jackrabbit
> > to not exlcusively work with RDBMS - if it was, I would be all in favour
> of
> > leaning on it to do the hardwork.
>
> please note that it is possible to exclusively use an RDBMS for storing
> and
> querying content, though you have to create your own persistence manager
> and
> query handler. the jackrabbit core does not force you to separate the
> store and
> the index.
>
> but you are right that it was a design decision to allow separation if you
> want
> to. because jackrabbit initially only had plain file based persistence
> managers
> and because lucene provides very good fulltext indexing we decided to go
> with
> lucene.
>
> coming back to the RDBMS only approach. you would have to implement a
> persistence manager that stores nodes and properties in a way that allows
> the
> database to use its indexes. then create a query handler that translates
> an
> abstract query tree into a SQL statement based on the database schema.
>
> there are some obstacles you will have to overcome (or actually the
> database):
> 1) handle node hierarchies (e.g. get all ancestors of a certain node)
> 2) provide fulltext indexing
>
> I think this would be a very useful extension for jackrabbit. so, if
> anyone is
> interested in implementing this, I'm very curious how well it performs
> compared
> to the current implementation using lucene.
>
> regards
> marcel
>
Re: Query Performance and Optimization
Posted by Marcel Reutegger <ma...@gmx.net>.
Michael Neale wrote:
> I know from previous discussions that it is a design decision of Jackrabbit
> to not exlcusively work with RDBMS - if it was, I would be all in favour of
> leaning on it to do the hardwork.
please note that it is possible to exclusively use an RDBMS for storing and
querying content, though you have to create your own persistence manager and
query handler. the jackrabbit core does not force you to separate the store and
the index.
but you are right that it was a design decision to allow separation if you want
to. because jackrabbit initially only had plain file based persistence managers
and because lucene provides very good fulltext indexing we decided to go with
lucene.
coming back to the RDBMS only approach. you would have to implement a
persistence manager that stores nodes and properties in a way that allows the
database to use its indexes. then create a query handler that translates an
abstract query tree into a SQL statement based on the database schema.
there are some obstacles you will have to overcome (or actually the database):
1) handle node hierarchies (e.g. get all ancestors of a certain node)
2) provide fulltext indexing
I think this would be a very useful extension for jackrabbit. so, if anyone is
interested in implementing this, I'm very curious how well it performs compared
to the current implementation using lucene.
regards
marcel
Re: Query Performance and Optimization
Posted by Michael Neale <mi...@gmail.com>.
I would like to second this.
I know from previous discussions that it is a design decision of Jackrabbit
to not exlcusively work with RDBMS - if it was, I would be all in favour of
leaning on it to do the hardwork.
But I presume Lucene is leaned on to do all the hard work instead (and it is
certainly capable) - but for me query performance seems to be a bit of
voodoo and random without diving into jackrabbit. I definately think a lot
of work can be done in that regards.
On 3/1/07, David Johnson <db...@gmail.com> wrote:
>
> We are exploring using Jackrabbit in a production environment. I have a
> repository that we have created from our content that has > 100K nodes.
> Several of our use case need to use date range queries and also use 'order
> by' frequently. We have noticed that the query time is significantly
> slower
> than necessary. After warming up the repository ( i.e., running the suite
> of queries once), as an example:
>
> "select * from Column where jcr:path like 'Gossip/ColumnName/Columns/%'
> and
> status <> 'hidden' order by publishDate desc" takes 500 ms to execute -
> this
> is just the execution time, I am not actually using or accessing the
> NodeIterator.
>
> Whereas: "select * from Column where jcr:path like
> 'Gossip/ColumnName/Columns/%' and status <> 'hidden'" takes only 33 ms to
> execute.
>
> /jcr:root/Gossip/ColumnName/Columns//element(*,Column)[@publishDate >
> xs:dateTime("way in the past") and @publishDate < xs:dateTime("way in the
> future") and (@status != 'hidden')] order by @publishDate descending takes
> 1096 ms to execute.
>
> Clearly dates (ordering and ranges) have a significant impact on query
> execution speed.
>
> Digging into the internals of Jackrabbit, we have noticed that there is an
> implementation of RangeQuery that essentially walks the results if the #
> of
> query terms is greater than what Lucene can handle. Reading the Lucene
> documentation, it looks like Filters are the recommended method of
> implementing "large" range queries, and also seem like a natural for
> matching node types - i.e., select * from Column
>
> Is there any ongoing work on query optimization and performance. We would
> be very interested in such work, including offering any help that we can.
>
> -Dave
>
Fwd: Query Performance and Optimization
Posted by David Johnson <db...@gmail.com>.
We are exploring using Jackrabbit in a production environment. I have a
repository that we have created from our content that has > 100K nodes.
Several of our use case need to use date range queries and also use 'order
by' frequently. We have noticed that the query time is significantly slower
than necessary. After warming up the repository ( i.e., running the suite
of queries once), as an example:
"select * from Column where jcr:path like 'Gossip/ColumnName/Columns/%' and
status <> 'hidden' order by publishDate desc" takes 500 ms to execute - this
is just the execution time, I am not actually using or accessing the
NodeIterator.
Whereas: "select * from Column where jcr:path like
'Gossip/ColumnName/Columns/%' and status <> 'hidden'" takes only 33 ms to
execute.
/jcr:root/Gossip/ColumnName/Columns//element(*,Column)[@publishDate >
xs:dateTime("way in the past") and @publishDate < xs:dateTime("way in the
future") and (@status != 'hidden')] order by @publishDate descending takes
1096 ms to execute.
Clearly dates (ordering and ranges) have a significant impact on query
execution speed.
Digging into the internals of Jackrabbit, we have noticed that there is an
implementation of RangeQuery that essentially walks the results if the # of
query terms is greater than what Lucene can handle. Reading the Lucene
documentation, it looks like Filters are the recommended method of
implementing "large" range queries, and also seem like a natural for
matching node types - i.e., select * from Column
Is there any ongoing work on query optimization and performance. We would
be very interested in such work, including offering any help that we can.
-Dave