bug in zcatalog queryplan causing very slow first query after startup

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.

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?



Dylan Jay

---
www.pretagov.com - Secure SaaS for Government hosted locally.
P: +61-2-9955-2830  +44-87-0392-7071 | linkedin.com/in/djay75




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