Feature request (linq): when using Take (or TakeLast), use limit() (or tail) before pivot()

[amitrotner]

Proposed Changes
When using Take or TakeLast operators, translate the query to Flux such as limit() and tail() will be before pivot() to achieve better performance.

A simple experiment conducted locally (see the time difference):

Limit():
Before:

After:

Tail():
Before:

After:

[bednar]

Hi @amitrotner,

thanks for using our client and your suggestion.

Unfortunately we can't do this optimisation as a default behaviour because it doesn't works for all cases. Check out this:

Data

m1 f1=1,f2=2 1
m1 f1=3,f2=4 2
m1 f1=5,f2=6 3
m1 f1=7,f2=8 4
m1 f1=9,f2=10,f3=11 5
m1 f1=12,f2=13,f3=14 6
m1 f1=15,f2=16,f3=17 7
m1 f1=18,f2=19,f3=20 8

Query: select *

from(bucket: "my-bucket") 
	|> range(start: 0)
	|> drop(columns: ["_start", "_stop", "_measurement"])
	|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")

Query: select * limit 2

from(bucket: "my-bucket") 
	|> range(start: 0)
	|> drop(columns: ["_start", "_stop", "_measurement"])
	|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
	|> limit(n: 2) 

But if we move limit before drop than the results are inconsistent:

from(bucket: "my-bucket") 
	|> range(start: 0)
	|> limit(n: 2) 
	|> drop(columns: ["_start", "_stop", "_measurement"])
	|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")

What do you think about a new option in QueryableOptimizerSettings?

Regards

[amitrotner]

@bednar
Thanks for your reply.

I see the problem of using my suggestion in this case.
I think a new option in QueryableOptimizerSettings will be good too.

Thanks!