Huge performance degradation (order by, skip/limit)

[benjaminbillet]

I updated my 2.1rc4 to the 2.1.2 version and I noticed a huge performance loss on my skip/limit requets.
My tests follow this steps:

• create 10000 vertices with a single non-indexed property (an int between [0 and 9])
• measure the time for executing the same request 100 hundred times

The following query takes approx. 6s with 2.1rc4 and approx. 30s with 2.1.2).

OCommandSQL query = new OCommandSQL("SELECT FROM VertexType ORDER BY @rid SKIP 8000 LIMIT 8020);

The following query takes approx. 7s with 2.1rc4 and approx. 37s with 2.1.2).

OCommandSQL query = new OCommandSQL("SELECT FROM VertexType WHERE property1 = 1 ORDER BY @rid SKIP 800 LIMIT 820);

The following query takes approx. 11s with 2.1rc4 and approx. 45s with 2.1.2). The performance loss is similar with a key index on property1: approx. 6s with 2.1rc4 and approx. 25s with 2.1.2.

OCommandSQL query = new OCommandSQL("SELECT FROM VertexType ORDER BY property1 SKIP 8000 LIMIT 8020);

Basically, each performance test I run with "order by" or "skip/limit" is 3 times slower in 2.1.2, with and without indexation.

[martingg88]

yes..i have this bad experience too.

the follow query take about 14833 in Total Working Time.
i get this log from studio server.

SELECT COUNT(*) FROM share WHERE (out = "#16:11704" AND in = "#56:1240")

[popduke]

same thing happened under 2.1. seems 'order by' cannot work with 'skip/limit'

[benjaminbillet]

Hi,
This issue shouldn't be neglected. What are the possible causes for this problem, knowing that rc4 was perfectly fine?

[lvca]

The query should be:

SELECT COUNT(*) FROM share WHERE (out = #16:11704 AND in = #56:1240

And you should have a composite index against share class and "out" + "in" properties. Do you?

[benjaminbillet]

Probably a composite index would solve the problem of martingg88. However, the main problem remains: the execution time of "order by" and "skip/limit" requests increased (3x) between rc4 and 2.1 (see my first post).

Do you see any change that could have caused this problem? I didn't test rc5, rc6 and 2.1.1, so I can't tell exactly when the problem appeared :/

[lvca]

@luigidellaquila, could it be the new parsing time?

[martingg88]

@lvca.. i don't think i can create composite index as out and in are automatically created by edge

[martingg88]

i feel strange following composite index doesn't work in right way. I thought any one of properties in where clause will work for composite index if they are specified in index. right? But it's not working for what i have expected. any idea?

1. place.country NOTUNIQUE ["country_name","country"] SBTREE

a. explain select country, country_name from place
where country_name = 'US'

working fine

b. explain select country, country_name, lat, lng from place
where country = 'US' and country_name = 'US'

working fine

c. explain select country, country_name from place
where country = 'US'

not working as not index is involved.

[martingg88]

indexed property in order by clause for vertex doesn't work.

I do index 'ctime' property in vertex but it won't using that index when doing sorting. any idea ?

explain select expand(inV()) from list
order by ctime desc

not working for index.

explain select from user
order by ctime desc

working for index

[luigidellaquila]

@lvca I'll take a look at this, but I don't think it's the case, 3x is too much (we could also test it in 2.2 snapshot, there we have parsed statement cache, so after the first execution there is no parse)

@martingg88 in your query, the indexed field (ctime) is on the vertex, but the query target is the edge ('list'), right now indexing is used only on the query target

[martingg88]

@luigidellaquila thanks. is it bug for composite index stated above?

[luigidellaquila]

Hi @martingg88

no, it's not a bug. Indexes are defined on classes, so when the query target is a class, available indexes can be easily identified.
When the target is a subquery or a traversal/expansion the target can be a mix of records on different classes, so there is no way to identify indexes to be used

[martingg88]

@luigidellaquila. sorry for make you confused. composite index should refer to following scenario. However composite index doesn't work out for direct query target.

i feel strange following composite index doesn't work in right way. I thought any one of properties in where clause will work for composite index if they are specified in index. right? But it's not working for what i have expected. any idea?

place.country NOTUNIQUE ["country_name","country"] SBTREE
a. explain select country, country_name from place
where country_name = 'US'

working fine

b. explain select country, country_name, lat, lng from place
where country = 'US' and country_name = 'US'

working fine

c. explain select country, country_name from place
where country = 'US'

not working as not index is involved.

[luigidellaquila]

@martingg88 it's a limitation of current index implementation (actually it's very common in index implementations, also in RDBMS): partial match is allowed on composite indexes only in the "left" part of the index, eg. if you have an index defined on A, B, C, it will be used when the query is filtering on A or on A, B, but not on B or C or B, C

[martingg88]

any idea how to improve the performance for order clause that is created/aliased on the fly?

order clause without index will have Huge performance degradation.

[martingg88]

there is a terrible bad performance when you have order clause without index. hope there could have a nice solution to solve this issue.

[sivan123]

Is the order by performance issue getting fixed in any of the upcoming version?

[martingg88]

any update on this?