where clauses do not appear to use indexes as they should, query consequently very slow

[alanpaxton]

Note: Please use Issues only for bug reports. For questions, discussions, feature requests, etc. post to dev group: https://groups.google.com/forum/#!forum/rocksdb or https://www.facebook.com/groups/rocksdb.dev

Expected behavior

I run this query against a large database (the auction site generated by the xmlgen tool) xmlgen

xquery version "3.1";

for $p in /site/people/person
    let $a :=
        for $t in /site/closed_auctions/closed_auction
        where $t/buyer/@person = $p/@id
        return $t
    let $c := count($a)
    where $c > 0
    return <item person="{$p/name/text()}" id="{$p/@id}">{count ($a)}</item>

I have created and loaded the following indexes (sample from collection.xconf)

<index>
        <!-- Range indexes -->
        <range>
            <create qname="@id" type="xs:string"/>
            <create qname="@person" type="xs:string"/>
        </range>
    </index>

Actual behavior

This query should be fast. It is correct, but extremely slow. Monex (and the very slow run time) tell me that the indices are not being used.

By rewriting the query I can get the same result with the expected performance and index usage

xquery version "3.1";

for $p in /site/people/person
    let $a := /site/closed_auctions/closed_auction[buyer/@person = $p/@id]
    let $c := count($a)
    where $c gt 0
    return <item person="{$p/name/text()}" id="{$p/@id}">{count($a)}</item>

To quote @adamretter :

Basically, the Query Rewriter in eXist-db is very bad at re-writing where clauses to use indexes as an optimisation, it is better at re-writing predicates. So in general, when working with eXist-db, I advise people to use predicates instead of where clauses.

Steps to reproduce the behavior

• Generate the default auction database with xmlgen ./xmlgen-unix -f 1 -o file-auction.xml
• Install the above indexes in collection.xconf
• load file-auction.xml from eXide using xmldb:store-files-from-pattern
• run the supplied query in eXide

[alanpaxton]

I'm sorry, wrong repo.