[Zope-dev] catalog performance: query plan

[Hedley Roos]

>> 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.

The app is Plone :)

>> 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)?

portal_type queries are usually multivalued in Plone.

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


Plone's reference_catalog

>
>> UID                  |FieldIndex       |0.0003070|      3257.1
>
> Note that this is the worst-case scenario for a FieldIndex:  there is
> exactly one value for every key.  This shouldn't be "indexed" at all, in
> fact, beyond a simple BTree (UID -> rid).

I've never even thought of that. Perhaps the catalog is used to
present a familiar API.

>
>> targetUID            |FieldIndex       |0.0002287|     4372.12
>
> I don't know what this one is used for, but it should probably be
> scrapped as well.

More reference_catalog.

>
>> exact_getUserId      |FieldIndex       |0.0001931|     5177.79
>> exact_getUserName    |FieldIndex       |0.0001816|     5504.39
>
> I don't know how the application uses either of those indexes, but they
> are almost certainly bogus in any normal catalog.

Membrane and remember. They're currently tied to Plone but efforts are
being made to make them work with CMF.

>> relationship         |FieldIndex       |0.0000822|     12153.1
>> id                   |FieldIndex       |0.0000822|    12161.81
>> end                  |DateIndex        |0.0000623|    16027.48
>> getGroups            |FieldIndex       |0.0000278|    35973.45
>
> This is almost certainly bogus:  FieldIndex is not supposed to be used
> with multi-valued terms.

Plone stuff, but I am intrigued by your statement. Why can FieldIndex
not be used with multi-valued terms?

>> Subject              |KeywordIndex     |0.0000253|    39413.57
>
> This is the use-case for which KeywordIndex is designed.  Was the query
> just a single term, by chance?

The simplest term is a list with only a single term (not counting the
trivial case). It should be worse with more terms right?


>> Title                |ZCTextIndex      |0.0000128|    77809.46
>
> This should be removed:  there is no valid use case for doing a
> full-text search restricted only to the title.

Plone specific.


>> Description          |ZCTextIndex      |0.0000116|    86241.39
>
> Again, should be removed.

Again, Plone specific :)


>> getEmail             |ZCTextIndex      |0.0000113|    87849.05
>
> Should *definitely* be removed:  how can you do full-text search on an
> e-mail address?

I think membrane is responsible for this, but you're right.


>> SearchableText       |TextIndex        |0.0000113|    88466.69
>
> Where did this one come from?  The 'SearchableText' above is a ZCTextIndex.

Membrane!

Kinda pointless for me to continue since this is turning into a
Plone-specific discussion on zope-dev. But at least the whole exercise
has forced us to look in detail into how all these indexes affect
performance with a zodb with many many objects.

Roche investigated Tesdal's queryplan today end it seems to solve
nearly all our performance problems. He'll have to elaborate.

Hedley