Indexed query takes 25s

[Vitor Enes Duarte]

Hi,

I have 3 indexed boolean properties with NOTUNIQUE_HASH_INDEX, each in a different class.

a) select from Post where isFeatured = true limit -1        [returns  975  of    1M records]

b) select from User where isFeatured = true limit -1        [returns  195  of  200k records]
c) select from Tag where isFeatured = true limit -1         [returns    4  of    74 recrods] 


I ran these queries 3 times (in console using a remote connection), right after I started OrientDB Server.
It's all here. 

I'm running these in a Amazon machine with SSD disks.
I'm starting OrientDB with:
MAXHEAP=-Xmx4096m
MAXDISKCACHE=""


First:
a) 7.339s
b) 4.377s
c) 25.81s

Second:
a) 1.239s
b) 0.621s
c) 5.873s

Third:
a) 0.653s
b) 0.633s
c) 6.051s


Why the third query takes this long? (only 4 records in 74)
I'm guessing OrientDB is caching the results from the first query. 
But there's anything I can do to improve performance when they aren't cached?

[Keith Freeman]

Might be helpful to see the EXPLAIN output for your queries, but also: why are you indexing boolean properties?  That's a pretty well-known anti-pattern in the SQL world (since you've only got 2 possible hash values), does orientdb somehow do something better than normal SQL with those?

[Vitor Enes Duarte]

Here is the EXPLAIN output, sorry I forgot.

I know what you're saying. I decided to use indexes, probably because the queries were taking too long. And from here and here I was led to believe they are really required.

I dropped the indexes and:

a) 31.717s

b) 8.439s

c) 9.919s

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/o9Z4cGTEdE4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Luca Garulli]

Hi Vitor,

Look at the times in profiled output. Last query took only in 0.002000 secs to be executed. How do you measure timing? Seems there is other thing processed after OrientDB.

Lvc@

On 6 April 2015 at 11:54, Vitor Enes Duarte <vitoren...@gmail.com> wrote:

Hi,

Here is the EXPLAIN output, sorry I forgot.

I know what you're saying. I decided to use indexes, probably because the queries were taking too long. And from here and here I was led to believe they are really required.

I dropped the indexes and:

a) 31.717s

b) 8.439s

c) 9.919s

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.

[Vitor Enes Duarte]

Hi Luca,

The times I pasted before were extracted from [console output](https://gist.github.com/vitorenesduarte/b01b7922b7e8e6075117) that I linked before. Aren't those reliable?

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/o9Z4cGTEdE4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.

[Luca Garulli]

Hi Vitor,

I'm thinking the culprit could reside in console. Please could you retry the same command via curl (HTTP api)?

curl -u admin:admin "http://localhost:2480/query/MYDB/select from Tag where isFeatured = true limit -1"

Lvc@

[Vitor Enes Duarte]

Hey,

I ran that and the curl output is this.

I changed the limit to 0 but no improvements.

[Vitor Enes Duarte]

I just took a look to HTTP API. Seems that the language was missing.

I changed the command to:

curl -u admin:admin "http://localhost:2480/query/MYDB/sql/select from Tag where isFeatured = true limit -1"

and

curl -u admin:admin "http://localhost:2480/query/MYDB/sql/select from Tag where isFeatured = true/-1"

But nothing came back.  The ouput.

[Luca Garulli]

Sorry,I forgot to mention URL should be encoded. Try this:

curl -u admin:admin "http://localhost:2480/query/MYDB/sql/select%20from%20Tag%20where%20isFeatured%20=%20true%20limit%20-1"

Lvc@

[Vitor Enes Duarte]

The output.
There's a lot of connected edges to these records. Is that the problem?

[Vitor Enes Duarte]

If I define a fetchplan I got much better results.

For example, the following query takes only 0.006s.

select from Tag where isFeatured = true limit -1 fetchplan in_*:-2 out_*:-2

Luca, can you just help me with making sure that I have fetchplan working using Java API? I'm talking about this question in SO.

Thanks for the help,

[Luca Garulli]

Hi Vitor,

How many edges do you have on the records part of the result set?

In Java by default all the edges are loaded in lazy way. I think console takes so much time because displays the number of edges, that in some configuration require a count() operation.

Lvc@

[Vitor Enes Duarte]

Hi Luca,

About 40000 for each tag.

Here's the query that shows that.

[Vitor Enes Duarte]

And if the edges are load in a lazy way, there's no need to define the fetchplan above, right?

Thanks again,

Vitor,