[Zope-dev] catalog performance: query plan

[Roché Compaan]

On Mon, 2008-11-10 at 11:08 -0500, Tres Seaver wrote:
> > Index Name           |Type             |Avg Time |Calls/second
> > ==============================================================
> > object_implements    |KeywordIndex     |0.2172234|         4.6
> 
> This is clearly not the same issue as the other KeywordIndexes:  in
> fact, I am astonished that anybody would be using a KeywordIndex for
> this at all.  I would suspect that the real problem here is in the
> appliation, rather than the index itself.
> 
> > getEffective_or_creat|DateIndex        |0.1941770|        5.15
> > effectiveRange       |DateRangeIndex   |0.0086295|      115.88
> > allowedRolesAndUsers |KeywordIndex     |0.0069754|      143.36
> 
> Hmm, I'm surprised there:  what query is being passed to 'apply_index'
> for this call?

Well it is not really performing badly at 6ms?

> 
> > path                 |ExtendedPathIndex|0.0040614|      246.22
> 
> I don't trust the EPI implementation at all.
> 
> > portal_type          |FieldIndex       |0.0025984|      384.84
> 
> This one is surprising:  its performance should be pretty similar to
> the
>  other FieldIndexes (e.g., 'review_state') which map a controlled
> vocabulary onto the entire corpus.  Was the query different than
> 'review_state' (e.g., multi-valued vs. single-valued)?

It's still not bad at 2ms. It has a lot more keys than review_state
though.

> 
> > SearchableText       |ZCTextIndex      |0.0007645|     1308.04
> > sourceUID            |FieldIndex       |0.0004886|     2046.31
> 
> Probably bogus, but I don't know how it is used.

I'm not really worried about indexes beyond this point - they're all
returning results in less than a millisecond.

> Can you provide information on the corpus / configuration / test plan
> you used to generate these results?

It's basically a Plone site with 300,000 remember based users and
roughly 150,000 documents and images indexed.

-- 
Roché Compaan
Upfront Systems                   http://www.upfrontsystems.co.za