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 getEffective_or_creat|DateIndex |0.1941770| 5.15 effectiveRange |DateRangeIndex |0.0086295| 115.88 allowedRolesAndUsers |KeywordIndex |0.0069754| 143.36 path |ExtendedPathIndex|0.0040614| 246.22 portal_type |FieldIndex |0.0025984| 384.84 SearchableText |ZCTextIndex |0.0007645| 1308.04 sourceUID |FieldIndex |0.0004886| 2046.31 UID |FieldIndex |0.0003070| 3257.1 targetUID |FieldIndex |0.0002287| 4372.12 exact_getUserId |FieldIndex |0.0001931| 5177.79 exact_getUserName |FieldIndex |0.0001816| 5504.39 relationship |FieldIndex |0.0000822| 12153.1 id |FieldIndex |0.0000822| 12161.81 end |DateIndex |0.0000623| 16027.48 getGroups |FieldIndex |0.0000278| 35973.45 getArtistTitle |FieldIndex |0.0000259| 38495.53 review_state |FieldIndex |0.0000259| 38582.22 Subject |KeywordIndex |0.0000253| 39413.57 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 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 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 cmf_uid |FieldIndex |0.0000113| 88352.13 getDuration |FieldIndex |0.0000113| 88454.29 SearchableText |TextIndex |0.0000113| 88466.69 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 getRoles |FieldIndex |0.0000104| 95385.31 getUserName |ZCTextIndex |0.0000103| 96692.46 -- Roché Compaan Upfront Systems http://www.upfrontsystems.co.za _______________________________________________ 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 )