Re: bug in zcatalog queryplan causing very slow first query after startup

On 10/16/13 7:50 AM, Dylan Jay wrote:
> Hi,
>
> Discovered a nasty-ish bug in the zcatalog the other day which I need some advice fixing.
> During startup zope will work out it's query plan including evaluating each index to see which is VALUE_INDEX. It's looking for indexes with uniquevalues of < 10. Unfortunately if you have a large primary key type index of say 10k values then this will do a lot of network calls and take over an hour. The bug is that it really doesn't need all the keys, just the number of unique ones.
>
> The code in plan.py in question is in valueindexes(self)
>
> ...
>          value_indexes = set()
>          for name, index in indexes.items():
>              if IUniqueValueIndex.providedBy(index):
>                  values = index.uniqueValues()
>                  if values and len(list(values)) < MAX_DISTINCT_VALUES:
>                      # Only consider indexes which actually return a number
>                      # greater than zero
>                      value_indexes.add(name)
> ...
>
>
> The interface the plan code is using is
>
> class IUniqueValueIndex(IPluggableIndex):
>      """An index which can return lists of unique values contained in it"""
>
>      def hasUniqueValuesFor(name):
>          """Return true if the index can return the unique values for name"""
>
>      def uniqueValues(name=None, withLengths=0):
>          """Return the unique values for name.
>
>          If 'withLengths' is true, returns a sequence of tuples of
>          (value, length)."""
>
>
>
>
> My options are to either
> 1. Change the UnIndex implementation to be lazy so the sequence returned can be used to determine the length of the keys without returning all the keys. This would only fix the problem for UnIndex implementations but I think that would cover most cases.
> 2. Change the IUniqueValueIndex interface to include another method "uniqueValuesCount".
>
> I'm included to go for option #1 since a lazy list doesn't break the interface whereas #2 would break any implementations that already have the interface IUniqueValueIndex but I was interested to know what others thought.

I'm inclined to say #1 too, but it's not clear how you plan to make the 
list lazy, so I would want to see your pull request.

> BTW. For any that come across this issue before I fix it, the work around is create a new package which includes a copy of the queryplan and then reference that in an environment variable. e.g. ZCATALOGQUERYPLAN pretaweb.myqueryplan.queryplan and ensure the queryplan has a value like 'VALUE_INDEXES': frozenset(['review_state', 'expires', 'Creator', 'Language', 'Subject']), for the catalog with the large index in it.
>
> I would also like to make a fix to make queryplans persistent into the ZODB which would further help to prevent slow queries on startup. Does anyone know this wasn't done in the originally?
>
Presumably because you don't want to write to the ZODB after every query.

------------------------------------------------------------------------------
October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from 
the latest Intel processors and coprocessors. See abstracts and register >
http://pubads.g.doubleclick.net/gampad/clk?id=60135031&iu=/4140/ostg.clktrk