Use of indexes with Existdb

Question

Coming back with my question on indexes usage in Exist-db..... I want to optimize response times of the following query:

for $cana in doc("Events_sample.xml")//canal
for $prog in doc("Programs_sample.xml")//program [number(temporada)>1960 ][tipo_programa="Series"] [$cana//id_programa = number(@id_programa)] 
        order by $prog/titulo        
        return 
            {$cana/string(@id_canal)} 
            {$prog/titulo} 
            {$prog/titulo_episodio}
            {$prog/generos}
            {$prog/id_serie}
            {$prog/episodio}
            {$prog/temporada}

Basically I have 2 xml files, one holding information of all the programs scheduled within a number of channels (>100), with the second one including detailed info of these programs. And I want to list all the programs of type 'Series' with the season number being the year of production. And this per all channels listed in the first file.

Evaluation of this query takes more than 2 minutes in my PC. I tested different alternatives of the query with similar results, one of them is the following, which shows data in a slightly different way and with no performance improvement:

    for $prog in doc("programs_sample.xml")//program [number(temporada)>1960 ][tipo_programa="Series"]
  return
     
                {doc("Events_sample.xml")//canal[$prog/number(@id_programa)=evento/id_programa]/string(@id_canal)} 
                {$prog/titulo} 
                {$prog/titulo_episodio}
                {$prog/generos}
                {$prog/id_serie}
                {$prog/episodio}
                {$prog/temporada}

Since I'm not an expert with xquery, maybe I'm still missing some extra optimization....

Samples of the XML files follow:

Programs.xml

    

    1.0
    
        
          Master
          tit1
          2018
        
        
          Master
          tit1
          2018
        
           
          Master
          tit2
          2018
        
        
          1
          Series
          8
          Episod xx
          2016
          serie comedia
          tit1
          2018
        
        
          2
          Series
          8
          Episod yy
          2017
          tit1
          serie comedia
          2018
        
        
          3
          Series
          8
          2004
          tit2
          Episod zz
          serie comedia
          2018

Events.xml

The collection.xconf file (saved to db/system/config/db/apps/MyApp) I'm using is:

Unfortunately this is not working, the query goes as slow as before. I have also checked that indexes are created with MONEX but there is no proper use of them, just a Basic usage for the Series index (screen shots attached).

I don't know what I'm doing wrong..... any hints will be welcomed.

duncdrum · Accepted Answer

Since your first query is not well formed, i can't adapt it, but I could run your second query. Without any modifications, this query runs in less then a second on the sample data. We can see that there are three calls to the range index, one successful two failing.

The reason for this is twofold:

Your index configuration uses the wrong types, which you convert in your query with number(). This means you first create a string index and then force a conversion which cannot use the index, instead of simply indexing the field as number in the first place and saving yourself the roundtrip.
You are missing some of the attributes that you are querying inside []. To make full use of the index, all the expressions used in your query should be indexed.

Given the following conf.xml:

and this modified query:

xquery version "3.1";

(: Follow the standard layout of exist's expath packages :)
 import module namespace config="http://so-53557816/config" at "config.xqm";

 declare variable $events := doc($config:data-root || '/Events.xml');
 declare variable $programs := doc($config:data-root || '/Programs.xml');

(: temporada is a number no need to convert :)
(: go to al programms after 1960 that are a series :)
 for $prog in $programs//program[temporada >1960 ][tipo_programa="Series"]

(: complex xpath expressions should be evaluated once inside a let statement for greater legibiliy :)
(: look up channel id for each $prog :)
 let $cana := $events//id_programa[. = $prog/@id_programa]/../../@id_canal

  return
     
                {$cana} 
                {$prog/titulo} 
                {$prog/titulo_episodio}
                {$prog/generos}
                {$prog/id_serie}
                {$prog/episodio}
                {$prog/temporada}

results in all three lookups to make use of the range index (no need for lucene full text indexes here)

You can download a .xar sample app of your code here, where I've also adopted a more standard application layout. With the data files inside a /data/ collections, and your query code in /modules/join.xql.

The query returns:


    
     
        tit1
    
    
        Episod xx
    
    
        serie comedia
    
    
        1
    
    
        8
    
    
        2016
    
 
 
    
    
        tit1
    
    
        Episod yy
    
    
        serie comedia
    
    
        2
    
    
        8
    
    
        2017
    
 
 
    
    
        tit2
    
    
        Episod zz
    
    
        serie comedia
    
    
        3
    
    
        8
    
    
        2004

Use of indexes with Existdb

Answers (2)

Related Questions