Skip to content

Avoid unnecessarily projecting the input columns of the UNPIVOT operator in the UNNEST #16221

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
merged 2 commits into from
Feb 13, 2025
Merged

Avoid unnecessarily projecting the input columns of the UNPIVOT operator in the UNNEST #16221

merged 2 commits into from
Feb 13, 2025
+72 −9

Conversation

Mytherin
Copy link
Collaborator

Fixes #13552

This greatly improves performance when unpivoting on many columns. Previously these columns would be projected out and then immediately discarded - leading to a lot of wasted time.

Below is a benchmark where we are unpivoting 10,000 columns:

create table r as select id, concat('column_', id) as column_name, concat('value_', id) as  column_value from (select unnest(generate_series(1,10000)) as id);
create table pivoted as pivot r on column_name using first(id) group by id;
select count(*) from (unpivot pivoted on columns(* exclude id) into name 'column_name' value 'column_value');
v1.2.0 New
145.4s 0.77s

@duckdb-draftbot duckdb-draftbot marked this pull request as draft February 13, 2025 09:47
@Mytherin Mytherin marked this pull request as ready for review February 13, 2025 09:48
@Mytherin Mytherin merged commit 1857feb into duckdb:main Feb 13, 2025
49 checks passed
@Mytherin Mytherin mentioned this pull request Feb 13, 2025
Closed
2 tasks
Antonov548 added a commit to Antonov548/duckdb-r that referenced this pull request Feb 27, 2025
@Antonov548
vendor: Update vendored sources to duckdb/duckdb@1857feb
008008f 
Avoid unnecessarily projecting the input columns of the UNPIVOT operator in the UNNEST (duckdb/duckdb#16221)
krlmlr pushed a commit to duckdb/duckdb-r that referenced this pull request Mar 5, 2025
@Antonov548 @krlmlr
vendor: Update vendored sources to duckdb/duckdb@1857feb
9c43fc3 
Avoid unnecessarily projecting the input columns of the UNPIVOT operator in the UNNEST (duckdb/duckdb#16221)
@Mytherin Mytherin deleted the unpivotmanycolumns branch April 2, 2025 09:24
krlmlr added a commit to duckdb/duckdb-r that referenced this pull request May 15, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@1857feb
300a9c2 
Avoid unnecessarily projecting the input columns of the UNPIVOT operator in the UNNEST (duckdb/duckdb#16221)
krlmlr added a commit to duckdb/duckdb-r that referenced this pull request May 15, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@1857feb
6226051 
Avoid unnecessarily projecting the input columns of the UNPIVOT operator in the UNNEST (duckdb/duckdb#16221)
krlmlr added a commit to duckdb/duckdb-r that referenced this pull request May 17, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@1857feb
7afe79b 
Avoid unnecessarily projecting the input columns of the UNPIVOT operator in the UNNEST (duckdb/duckdb#16221)
krlmlr added a commit to duckdb/duckdb-r that referenced this pull request May 18, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@1857feb
41d76ea 
Avoid unnecessarily projecting the input columns of the UNPIVOT operator in the UNNEST (duckdb/duckdb#16221)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers
No reviews
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.

UNPIVOT statement is extremely slow when working with many columns
1 participant
@Mytherin