2 Replies Latest reply: Jul 26, 2011 1:24 AM by 655560 RSS

    Having problem with datetime index queries

    761557
      Hello,

      I'm having problems with dbxml not picking an index when I expect it, not sure what I am doing wrong. First here is my test document structure
      <root><when>2011-10-02T02:22:29.766383</when><otherStuff>does not matter</otherStuff></root>
      I have 100,000 of them:
      dbxml> cquery "/*"
      100000 objects returned for eager expression '/*'
      Here's my edge dateTime index on 'when':
      dbxml> listIndexes
      Index: unique-node-metadata-equality-string for node {http://www.sleepycat.com/2002/dbxml}:name
      Index: edge-element-equality-dateTime for node {}:when
      2 indexes found.
      First of all, here's a simple query done with a string literal cast to a xs:dateTime. Excellent performance!! Simple query plan too
      dbxml> time query "collection('test.dbxml')/root[when > xs:dateTime('2011-10-02T01:58:29.766383')]"
      36 objects returned for eager expression 'collection('test.dbxml')/root[when > xs:dateTime('2011-10-02T01:58:29.766383')]'
      
      Time in seconds for command 'query': 0.005022
      
      dbxml> queryPlan "collection('test.dbxml')/root[when > xs:dateTime('2011-10-02T01:58:29.766383')]"
      <XQuery>
        <QueryPlanToAST>
          <LevelFilterQP>
            <StepQP axis="parent-of-child" name="root" nodeType="element">
              <ValueQP container="test.dbxml" index="edge-element-equality-dateTime" operation="gt" parent="root" child="when" value="2011-10-02T01:58:29.766383"/>
            </StepQP>
          </LevelFilterQP>
        </QueryPlanToAST>
      </XQuery>
      OK, so if I'm going to do this safely in my code, I need to use a variable. So lets create a dateTime variable with the same value as the above query:
      dbxml> setTypedVariable when dateTime 2011-10-02T01:58:29.766383
      Setting $when as dateTime = 2011-10-02T01:58:29.766383
      Though strangely this is A LOT slower and the query plan indicates that it is doing a sequential scan:
      dbxml> time query "collection('test.dbxml')/root[when > $when]"
      36 objects returned for eager expression 'collection('test.dbxml')/root[when > $when]'
      
      Time in seconds for command 'query': 9.15405
      
      <XQuery>
        <QueryPlanToAST>
          <NodePredicateFilterQP uri="" name="#tmp2">
            <StepQP axis="child" name="root" nodeType="element">
              <SequentialScanQP container="test.dbxml" nodeType="document"/>
            </StepQP>
            <ValueFilterQP comparison="gt" general="true">
              <StepQP axis="child" name="when" nodeType="element">
                <VariableQP name="#tmp2"/>
              </StepQP>
              <Variable name="when"/>
            </ValueFilterQP>
          </NodePredicateFilterQP>
        </QueryPlanToAST>
      </XQuery>
      So then I thought maybe I screwed up and I should cast the value to dateTime as well as having it as a dateTime variable and it got even slower. The query plan didn't change much, apart from it is using the extra time to apply the cast.
      dbxml> time query "collection('test.dbxml')/root[when > xs:dateTime($when)]"                                         
      36 objects returned for eager expression 'collection('test.dbxml')/root[when > xs:dateTime($when)]'
      
      Time in seconds for command 'query': 24.1274
      
      <XQuery>
        <QueryPlanToAST>
          <NodePredicateFilterQP uri="" name="#tmp2">
            <StepQP axis="child" name="root" nodeType="element">
              <SequentialScanQP container="test.dbxml" nodeType="document"/>
            </StepQP>
            <ValueFilterQP comparison="gt" general="true">
              <StepQP axis="child" name="when" nodeType="element">
                <VariableQP name="#tmp2"/>
              </StepQP>
              <CastAs>
                <Variable name="when"/>
                <SequenceType type="xs:dateTime?"/>
              </CastAs>
            </ValueFilterQP>
          </NodePredicateFilterQP>
        </QueryPlanToAST>
      </XQuery>
      So then I thought, lets try a string type:
      dbxml> setTypedVariable when string 2011-10-02T01:58:29.766383
      Setting $when as string = 2011-10-02T01:58:29.766383
      This time it used the index, but only for presence test, it was still a lot faster than using a dateTime typed variable:
      dbxml> queryPlan "collection('test.dbxml')/root[when > $when]"
      <XQuery>
        <QueryPlanToAST>
          <LevelFilterQP>
            <StepQP axis="parent-of-child" name="root" nodeType="element">
              <ValueFilterQP comparison="gt" general="true">
                <PresenceQP container="test.dbxml" index="edge-element-equality-dateTime" operation="prefix" child="when"/>
                <Variable name="when"/>
              </ValueFilterQP>
            </StepQP>
          </LevelFilterQP>
        </QueryPlanToAST>
      </XQuery>
      
      dbxml> time query "collection('test.dbxml')/root[when > $when]"
      36 objects returned for eager expression 'collection('test.dbxml')/root[when > $when]'
      
      Time in seconds for command 'query': 1.6318
      using
      collection('test.dbxml')/root[when > xs:dateTime($when)]
      let to another sequential scan query plan
      Any ideas anyone?
        • 1. Re: Having problem with datetime index queries
          761557
          This is also troubling:
          dbxml> time query "
          subsequence((
          for $x in collection('test.dbxml')/root
              order by $x/when
              return $x), 1, 10)
          "
          10 objects returned for eager expression '
          subsequence((
          for $x in collection('test.dbxml')/root
              order by $x/when
              return $x), 1, 10)
          '
          
          Time in seconds for command 'query': 3.79192
          
          
          <XQuery>
            <Function name="{http://www.w3.org/2005/xpath-functions}:subsequence">
              <Return>
                <OrderByTuple direction="ascending" empty="least" stable="false" collation="http://www.w3.org/2005/xpath-functions/collation/codepoint">
                  <ForTuple uri="" name="x">
                    <ContextTuple/>
                    <QueryPlanToAST>
                      <StepQP axis="child" name="root" nodeType="element">
                        <SequentialScanQP container="test.dbxml" nodeType="document"/>
                      </StepQP>
                    </QueryPlanToAST>
                  </ForTuple>
                  <TreatAs>
                    <PromoteUntyped uri="http://www.w3.org/2001/XMLSchema" name="string">
                      <Atomize>
                        <QueryPlanToAST>
                          <StepQP axis="child" name="when" nodeType="element">
                            <VariableQP name="x"/>
                          </StepQP>
                        </QueryPlanToAST>
                      </Atomize>
                    </PromoteUntyped>
                    <SequenceType type="item()?"/>
                  </TreatAs>
                </OrderByTuple>
                <QueryPlanToAST>
                  <VariableQP name="x"/>
                </QueryPlanToAST>
              </Return>
              <NumericLiteral value="1.0E0" typeuri="http://www.w3.org/2001/XMLSchema" typename="double"/>
              <NumericLiteral value="1.0E1" typeuri="http://www.w3.org/2001/XMLSchema" typename="double"/>
            </Function>
          </XQuery>
          • 2. Re: Having problem with datetime index queries
            655560
            Hi,

            These look like queryplan generator issues. I'm looking into it. Thanks.

            Best regards,
            Rucong Zhao
            Oracle Berkeley DB XML