ArangoSearch view: sort after pagination breaks view performance about arangodb HOT 3 OPEN

Hi @matcho!

This indeed looks a little bit strange. I will investigate it and then get back to you.

from arangodb.

Hi @matcho !

This behavior is expected since your index doesn't contain field _key: Therefore it should be materialized directly from the storage. As a result, it is way slower as expected.

Could you please try to add _key field to primarySort as well? Here is the documentation about how such view with 2 fields in primarySort could be created: https://docs.arangodb.com/3.11/index-and-search/arangosearch/performance/#primary-sort-order

from arangodb.

Hi @alexbakharew

Thank you for your answer.

I understand that using a 2-field primarySort would certainly work − we'll try that as soon as possible, although it seems that primarySort cannot be changed on an existing view : const error: /primarySort must be equal to constant. Schema: {"allowedValue":[{"field":"date_obs","asc":false}]} , and view has to be re-created from scratch (no big deal).

What still seems strange is that returning a projection that includes _key is very fast, although _key is not an indexed field in the view : the following query runs in 165ms !

FOR o in obs_geo_view
	SEARCH ANALYZER(GEO_CONTAINS(GEO_POLYGON(france), o.geoloc), "geopoint_pn")
	SORT o.date_obs DESC
	LIMIT 20000, 1000
	RETURN {
	    date_obs: o.date_obs,
	    _key: o._key
	}

Once the 1000 resulting items, including date_obs and _key fields, are returned so fast, what keeps AQL from re-sorting them in-memory ? Sorting 1000 items should take almost no time. In queries mentioned in the original post above, it seems that the optimizer forces the second SORT statement to be executed before pagination, which is not what is expressed in the AQL query, especially query 3 with the sub-query.

So it still looks like an optimizer / AQL issue to me. We should be able to apply a secondary sort after pagination without modifying the view's primarySort. Besides, what if we want to sort after pagination on different fields, in different cases ? We would then have to build multiple views with a different primarySort in each of them, which doesn't seem reasonable.

Thank you

from arangodb.