Repository navigation
Convert indexed ARRAY_AGG to NTH_VALUE #9213
Description
Is your feature request related to a problem or challenge?
Query below
SELECT a, ARRAY_AGG(c ORDER BY c)[1] as result
FROM multiple_ordered_table
GROUP BY a;and
SELECT a, NTH_VALUE(c, 1 ORDER BY c) as result
FROM multiple_ordered_table
GROUP BY a;produces same results. However, first query generates following plan
"ProjectionExec: expr=[a@0 as a, (ARRAY_AGG(multiple_ordered_table.c) ORDER BY [multiple_ordered_table.c ASC NULLS LAST]@1).[1] as result]",
" AggregateExec: mode=Single, gby=[a@0 as a], aggr=[ARRAY_AGG(multiple_ordered_table.c)], ordering_mode=Sorted",
" CsvExec: file_groups={1 group: [[CSV_PATH]]}, projection=[a, c], output_orderings=[[a@0 ASC NULLS LAST], [c@1 ASC NULLS LAST]], has_header=true",
whereas second query generates following plan
"ProjectionExec: expr=[a@0 as a, NTH_VALUE(multiple_ordered_table.c,Int64(1)) ORDER BY [multiple_ordered_table.c ASC NULLS LAST]@1 as result]",
" AggregateExec: mode=Single, gby=[a@0 as a], aggr=[NTH_VALUE(multiple_ordered_table.c,Int64(1))], ordering_mode=Sorted",
" CsvExec: file_groups={1 group: [[CSV_PATH]]}, projection=[a, c], output_orderings=[[a@0 ASC NULLS LAST], [c@1 ASC NULLS LAST]], has_header=true",
Describe the solution you'd like
we can rewrite first query as second one, which executes faster with less memory. Because it no longer needs to keep all results in the array_agg.
Describe alternatives you've considered
No response
Additional context
No response
Activity
Lordworms commentedon Feb 18, 2024
I can do this one
Lordworms commentedon Feb 19, 2024
Hello @mustafasrepo could you give me the actual table creation SQL for this issue?
mustafasrepo commentedon Feb 19, 2024
Sure,
you can create the table in the queries with above snippet
Lordworms commentedon Feb 20, 2024
take
fix: issue apache#9213 substitute ArrayAgg to NthValue to optimize qu…
alamb commentedon Feb 21, 2024
As @Lordworms points out, maybe we can try to implement this feature in some way that is general and not special cased in the optimzer -- aka #9289
This would look like
ARRAY_AGGanAggregateUDF(which probably would mean making adatafusion-aggregatescrateThis would certainly take more work and thus more time than just implementing a special case for the BuiltInAggregateFunction, so I don't think it is necessary
However, if we think this is a reasonbale approach I can file some tickets with the basic ideas sketeched out (I didn't want to sketch out too many things at once and we already have a bunch of work related to pulling out scalar UDFs)
Lordworms commentedon Feb 21, 2024
Yes, I truely want to add some general approach instead of writing some "if else", but I agree that currently we have more ticket than that, I could do this one later and do some current-ticket...
fix: issue #9213 substitute ArrayAgg to NthValue to optimize query pl…