Faster joins

[antonmks]

I added an option to sort database segments locally on a join key. The best performance can be reached by sorting the data file on a filter key and then sorting segments locally on a join key. This way the expensive sorting of the datasets can be avoided in join operations.
So, for example, the line
STORE A INTO 'lineitem' BINARY;
now becomes
STORE A INTO 'lineitem' BINARY SORT SEGMENTS BY orderkey;

Running time of Q3 changed from 15 to 7 seconds,
Q5 from 68 to 17 seconds.

[AlexeyAB]

Good news! It sorts the data in every segment seperately, and can be used an additional to "sort -t "|" -k11 lineitem.tbl > lineitems.tbl" to sort all data by field "shipdate" which reduces time for FILTER?

And by using thrust::is_sorted() before join we can know, do we need to sort data or no, is it correct?

[antonmks]

Yes.
It creates a file *.sort where it stores the indexes of sorted columns. So
when doing a join it checks if a segment is already sorted by looking into
the file.
Using thrust::is_sorted probably would be more expensive, it would have to
check all data in a column.

Anton

Подольск ?

On Thu, Jul 4, 2013 at 3:44 PM, AlexeyAB notifications@github.com wrote:

Good news! It sorts the data in every segment seperately, and can be used
an additional to "sort -t "|" -k11 lineitem.tbl > lineitems.tbl" to sort
all data by field "shipdate" which reduces time for FILTER?

And by using thrust::is_sorted() before join we can know, do we need to
sort data or no, is it correct?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/38#issuecomment-20475062
.

[AlexeyAB]

Stored in the "*.sort-file" indexes, which uses with "thrust::gather/scatter" to produce sorted segment?

Yes, and you, Moscow?

[antonmks]

The sort file simply has one or more numbers like 0 - meaning that a segment is already presorted on first field.
03 - presorted on first and third fields.

Yes.

[AlexeyAB]

.sort is a metadata-file. Why not to use bitmask, an example 5(101) mean that on 1st and 3d fields is already sorted?

А как догадались, что с Подольска? :)

[antonmks]

Yes, it is possible to use a bitmask, although the effect on performance would be nil. It is just a list of sorted fields.

Посмотрел Google analytics, там есть города посетителей. А почему Подольск ? С такими интересами в Сан Франциско было бы интереснее и лучше :-)

[AlexeyAB]

I can't read it :)

[antonmks]

Updated.

[AlexeyAB]

Вариант. Но не так хорош разговорный английский, и сейчас не так далеко от Москвы, пока смотрю найдется ли здесь достойное применение. А сами не думали о Сан Франциско? :)

[antonmks]

Английский - ерунда, учится быстро. А мне никак по семейным обстоятельствам :-(