-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Roché Compaan wrote:
> On Mon, 2008-10-27 at 11:32 -0500, Alan Runyan wrote:
>> I agree with Tres.  A lot more can be done with Indexes and Catalog
>> without caching.
>>
>> The most exiciting development in Catalog optimizations comes out
>> Jarn.  Helge Tesdal (iirc) did a buncha work  at a RDBMS company when
>> he was in college.  He has a protoype of a query plan for ZCatalog.
>>
>> http://www.jarn.com/blog/catalog-query-plan
>>
>> I would like to ask Roche and others to look at the Query Plan.
> 
> We looked at query plan but it didn't help us in any way. Some catalog
> indexes are performing very badly and most of our content is in a
> published state which doesn't help the query plan much.
> 
>> Caching is a total PITA because invalidation machinery becomes
>> overwhelming complex and unwieldly quickly in production.
>>
> 
> I agree but this was the only thing that we could do to even go into
> production.
> 
> Since I'm in full agreement that we need to fix indexes that are
> problematic, I started doing some benchmarks on the large data set that
> gave us so many headaches. It is probably not surprising that the more
> complex indexes are performing badly. DateRangeIndex, KeywordIndex and
> Plone's ExtendedPathIndex performed the worst. Below are some stats
> showing timings around the "apply_index" call in Catalog.py that was
> done while testing the application with real data:
> 
> 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?

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

> SearchableText       |ZCTextIndex      |0.0007645|     1308.04
> sourceUID            |FieldIndex       |0.0004886|     2046.31

Probably bogus, but I don't know how it is used.

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

> targetUID            |FieldIndex       |0.0002287|     4372.12

I don't know what this one is used for, but it should probably be
scrapped as well.

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

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

> getArtistTitle       |FieldIndex       |0.0000259|    38495.53
> review_state         |FieldIndex       |0.0000259|    38582.22
> 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?

> getDaysOfTheWeek     |KeywordIndex     |0.0000247|    40465.98
> meta_type            |FieldIndex       |0.0000199|    50116.64
> exact_getGroupId     |FieldIndex       |0.0000162|    61417.51
> getVideoURL          |FieldIndex       |0.0000155|     64447.5
> year                 |FieldIndex       |0.0000155|    64460.43
> Title                |FieldIndex       |0.0000136|    73381.01
> getId                |FieldIndex       |0.0000131|    76056.97
> 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.

> startendrange        |DateRangeIndex   |0.0000127|    78485.82
> expires              |DateIndex        |0.0000126|    79001.59
> getObjPositionInParen|FieldIndex       |0.0000124|     80675.9
> targetId             |FieldIndex       |0.0000122|    81418.68
> effective            |DateIndex        |0.0000121|     82651.7
> getProvince          |FieldIndex       |0.0000117|    85198.54
> month                |FieldIndex       |0.0000116|    85762.56
> Description          |ZCTextIndex      |0.0000116|    86241.39

Again, should be removed.

> Type                 |FieldIndex       |0.0000115|    86345.17
> getLast_login_time   |DateIndex        |0.0000115|    86698.98
> Creator              |FieldIndex       |0.0000113|    87840.03
> getEmail             |ZCTextIndex      |0.0000113|    87849.05

Should *definitely* be removed:  how can you do full-text search on an
e-mail address?

> cmf_uid              |FieldIndex       |0.0000113|    88352.13
> getDuration          |FieldIndex       |0.0000113|    88454.29
> SearchableText       |TextIndex        |0.0000113|    88466.69

Where did this one come from?  The 'SearchableText' above is a ZCTextIndex.

> sortable_title       |FieldIndex       |0.0000112|    88698.49
> getRating            |FieldIndex       |0.0000112|     88747.5
> getGenres            |KeywordIndex     |0.0000112|    88796.55
> object_provides      |KeywordIndex     |0.0000112|    88919.43
> getEventType         |KeywordIndex     |0.0000112|     88953.9
> in_reply_to          |FieldIndex       |0.0000112|    89057.46
> getReview_state      |FieldIndex       |0.0000112|    89124.63
> is_folderish         |FieldIndex       |0.0000112|    89240.51
> getRawRelatedItems   |KeywordIndex     |0.0000111|    89568.91
> getThumbSize         |FieldIndex       |0.0000111|    89653.89
> getStudioCamURL      |FieldIndex       |0.0000111|    89678.92
> Date                 |DateIndex        |0.0000111|    89799.23
> getHash              |FieldIndex       |0.0000111|    90111.54
> getNumberOfComments  |FieldIndex       |0.0000110|    90141.88
> start                |DateIndex        |0.0000110|    90400.59
> getPercentage        |FieldIndex       |0.0000110|    90420.94
> is_default_page      |FieldIndex       |0.0000110|     90446.4
> modified             |DateIndex        |0.0000106|    93506.29
> created              |DateIndex        |0.0000106|    93678.59
> getGroupId           |ZCTextIndex      |0.0000105|    94962.39
> getUserId            |ZCTextIndex      |0.0000105|    95165.88
> getFullname          |ZCTextIndex      |0.0000104|    95313.06

Scrap these.

> getRoles             |FieldIndex       |0.0000104|    95385.31
> getUserName          |ZCTextIndex      |0.0000103|    96692.46

Scrap this one.



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


Tres.
- --
===================================================================
Tres Seaver          +1 540-429-0999          [EMAIL PROTECTED]
Palladion Software   "Excellence by Design"    http://palladion.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJGFxi+gerLs4ltQ4RAikKAJ93J3XyhecUq6kogTFsJnLXxgjh8QCgrZEw
xdycjo0+4WVdSGFKhVtenms=
=kYd+
-----END PGP SIGNATURE-----

_______________________________________________
Zope-Dev maillist  -  Zope-Dev@zope.org
http://mail.zope.org/mailman/listinfo/zope-dev
**  No cross posts or HTML encoding!  **
(Related lists - 
 http://mail.zope.org/mailman/listinfo/zope-announce
 http://mail.zope.org/mailman/listinfo/zope )

Reply via email to