Sorting

[Jozef Wagner]

Having table with millions of rows, we'd like to sort the results by e.g. date using ES. Is it possible to do it with zombodb, without having to use SQL's order by, which is quite expensive in our case?

something like

SELECT * FROM documents WHERE zdb('documents', documents.ctid) ==> 'title:foo #sort-by(date, desc)';

that would essentially be the same as https://www.elastic.co/guide/en/elasticsearch/guide/1.x/_sorting.html#_sorting_by_field_values 

Thanks,

Jozef

[Eric Ridge]

Hi Jozef!

Unfortunately, no, ZDB doesn't support sorting inside Elasticsearch for two major reasons.

The first is that ZDB uses ES' scan+scroll API to retrieve matching documents, and the scan+scroll API does not support sorting.

Secondly, since the SQL standard doesn't guarantee ordering (without an ORDER BY clause), Postgres is free to pick any execution plan it wants.  If Postgres decides on an Index Scan, theoretically ZDB could control the order if it weren't for the above reasons, but if instead Postgres decides on a Sequential Scan then ZDB would have no control over ordering.

You're "stuck" doing sorting in Postgres with an ORDER BY clause on your query.

eric

--
You received this message because you are subscribed to the Google Groups "ZomboDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to zombodb+u...@googlegroups.com.
To post to this group, send email to zom...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/zombodb/1b64119d-80bd-4618-ac51-353343a4c633%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jozef Wagner]

Thanks for the explanation and the quick reply!

I guess I'll use date range filters to slice the results into chunks and order them in psql, as for my use case (display result in an infinite scroll way) I can live with lazy sorting. More bookkeeping work for me but it should give ordered results much faster.

Thanks,

Jozef

On Thu, Nov 10, 2016 at 8:47 PM, Eric Ridge <eeb...@gmail.com> wrote:

Hi Jozef!

Unfortunately, no, ZDB doesn't support sorting inside Elasticsearch for two major reasons.

The first is that ZDB uses ES' scan+scroll API to retrieve matching documents, and the scan+scroll API does not support sorting.

Secondly, since the SQL standard doesn't guarantee ordering (without an ORDER BY clause), Postgres is free to pick any execution plan it wants.  If Postgres decides on an Index Scan, theoretically ZDB could control the order if it weren't for the above reasons, but if instead Postgres decides on a Sequential Scan then ZDB would have no control over ordering.

You're "stuck" doing sorting in Postgres with an ORDER BY clause on your query.

eric

On Thu, Nov 10, 2016 at 6:09 AM Jozef Wagner <jozef....@gmail.com> wrote:

Having table with millions of rows, we'd like to sort the results by e.g. date using ES. Is it possible to do it with zombodb, without having to use SQL's order by, which is quite expensive in our case?

something like

SELECT * FROM documents WHERE zdb('documents', documents.ctid) ==> 'title:foo #sort-by(date, desc)';

that would essentially be the same as https://www.elastic.co/guide/en/elasticsearch/guide/1.x/_sorting.html#_sorting_by_field_values 

Thanks,

Jozef

--
You received this message because you are subscribed to the Google Groups "ZomboDB" group.

To unsubscribe from this group and stop receiving emails from it, send an email to zombodb+unsubscribe@googlegroups.com.

To post to this group, send email to zom...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/zombodb/1b64119d-80bd-4618-ac51-353343a4c633%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

To post to this group, send email to zom...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/zombodb/CANcm6wb8M16NmKSMmXwV5nZ-PFbtNJYbKNVeRje-em7W6Mh2SA%40mail.gmail.com.

[Eric B. Ridge]

ZDB also works well with cursors, if that's a thing you might can take advantage of.  "WITH HOLD" cursors take a bit of time up front to materialize the results, but can be used across transactions.  You can limit the columns returned from the cursor to the primary key and then re-query for each page of results as you're paging through.

You might also find this an interesting read:  https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/

eric

To unsubscribe from this group and stop receiving emails from it, send an email to zombodb+u...@googlegroups.com.

To post to this group, send email to zom...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/zombodb/CA%2B%3D2m4iFktZ6d_CenBaVtpZm%2B-egMGwY1Nb9MhxvYUbv53q26A%40mail.gmail.com.

[Jozef Wagner]

Hi,

With the support for ES 5.6, is this still true that we cannot utilize ES sorting? We'd like to use the ranking feature of ES to display most relevant (best match) documents first.

Thanks,

Jozef

[Eric Ridge]

This is probably what you're looking for:

https://github.com/zombodb/zombodb/blob/master/SYNTAX.md#limitoffset-with-sorting

eric

To view this discussion on the web visit https://groups.google.com/d/msgid/zombodb/b0c28ba9-27d4-4fb2-8884-5da5025ffd36%40googlegroups.com.