add some RealNest benchmarks #13345

hmeriann · 2024-08-07T14:36:42Z

This PR adds some benchmark queries to the RealNest JSON data as it discussed in the issue.

For now there are queries to only one of the files - hep-adl-ethz-Run2012B_SingleMu, which is not a part of the PR, but can be found here and should be placed in duckdb_benchmark_data/ directory.
Probably, the JSON file path should be changed after it becomes clear where to store RealNest test data (maybe somewhere outside of the mount-point of aws machine? )

taniabogatsch

Hey @hmeriann! Thanks for starting to work on this! :) I've left some comments and added a more extensive comment to the original issue.

Currently, this PR focuses heavily on the UNNEST. In future PRs, it would be nice to see more operations directly on the nested types without unnesting them.

benchmark/micro/json/RealNest/1_unnest_and_sum_one_json_struct.benchmark

benchmark/micro/json/RealNest/2_create_table_combined_all_json_fields.benchmark

benchmark/micro/json/RealNest/3_select_avg_from_unnested_structure.benchmark

benchmark/micro/json/RealNest/5_list_filter_on_unnested_structure.benchmark

benchmark/micro/json/RealNest/4_filter_unnested_struct.benchmark

benchmark/micro/json/RealNest/6_select_sum_from_unnested_structure.benchmark

Tmonster

just the one comment for now, will continue tomorrow

benchmark/micro/json/RealNest/2_create_table_combined_all_json_fields.benchmark

Tmonster

Thanks!
This looks good for now. Let's wait until Tania gets back to see if we should add more.

Also, could you rename all of the single digit benchmarks so that they are prefixed with a 0?

I.e, instead of 1_list_sort_text.benchmark it's 01_list_sort_text.benchmark, then they show up as sorted in the file system

benchmark/micro/json/RealNest/2_create_table_combined_all_json_fields.benchmark

benchmark/realnest/3_list_transform_plus_list_aggregate.benchmark

benchmark/realnest/8_list_intersect_hashtags.benchmark

benchmark/realnest/9_select_sum_from_unnested_structure.benchmark

benchmark/realnest/3_list_transform_plus_list_aggregate.benchmark

taniabogatsch

Looks good from my side now 😄 - I only added a small comment.

benchmark/realnest/09_array_agg.benchmark

Tmonster

Also can you add this as a regression test to the Regression.yml
You can add it after the regression test csv step that looks like this

    - name: Regression Test CSV
      if: always()
      shell: bash
      run: |
        python scripts/regression_test_runner.py --old=duckdb/build/release/benchmark/benchmark_runner --new=build/release/benchmark/benchmark_runner --benchmarks=.github/regression/csv.csv --verbose --threads=2

benchmark/realnest/01_aggregate-on-one-deep-struct-members.benchmark

benchmark/realnest/05_list_filters.benchmark

benchmark/realnest/08_count_map_keys.benchmark

hmeriann · 2024-08-22T08:33:26Z

Also can you add this as a regression test to the Regression.yml

Done

benchmark/realnest/12_map_two_list_values.benchmark

benchmark/realnest/14_list_sort_flatten_slice.benchmark

taniabogatsch

Hi @hmeriann, I had a look at the changes and added a few more comments. Great work!

benchmark/realnest/load.sql

benchmark/realnest/15_list_sort.benchmark

benchmark/realnest/14_list_slice.benchmark

benchmark/realnest/load.sql

benchmark/realnest/04_list_transform_plus_list_aggregate.benchmark

benchmark/realnest/03_create_table_combined_all_json_fields.benchmark

Tmonster

Looks good to me! Thanks 👍

taniabogatsch

Looks good to me now! Great work.

benchmark/realnest/load.sql

…w row group segment tree after vacuum

…db#14038) To further override `EnumTypeInfoTemplated` it needs to be either vendored (bad) or we move it to a header. Relevant `pg_duckdb` PR: <duckdb/pg_duckdb#193>

…w row group segment tree after vacuum (duckdb#14092) Fixes duckdb#14077

Add some spaces and other seperators. I spotted in the docs that the formatting of the sniffer's error messages is inconsistent, see https://duckdb.org/docs/data/csv/reading_faulty_csv_files#anatomy-of-a-csv-error ``` file= people.csv delimiter = , (Auto-Detected) quote = " (Auto-Detected) escape = " (Auto-Detected) new_line = \r\n (Auto-Detected) header = true (Auto-Detected) skip_rows = 0 (Auto-Detected) date_format = (DD-MM-YYYY) (Auto-Detected) timestamp_format = (Auto-Detected) null_padding=0 sample_size=20480 ignore_errors=false all_varchar=0 ``` This PR adds a bunch of whitespace and also other separators for similar error messages.

Seems like some `NULL` values snuck in there. This was causing issues on the weekly regression runner

hannes · 2024-10-02T10:30:06Z

thanks!

hmeriann requested review from Tmonster and taniabogatsch August 7, 2024 14:37

duckdb-draftbot marked this pull request as draft August 7, 2024 14:47

hmeriann force-pushed the real-nest-benchmarks branch from b7e48af to de07922 Compare August 7, 2024 14:49

taniabogatsch suggested changes Aug 7, 2024

View reviewed changes

Tmonster reviewed Aug 7, 2024

View reviewed changes

benchmark/micro/json/RealNest/2_create_table_combined_all_json_fields.benchmark Outdated Show resolved Hide resolved

hmeriann requested review from Tmonster and taniabogatsch August 8, 2024 14:53

Tmonster reviewed Aug 13, 2024

View reviewed changes

taniabogatsch reviewed Aug 19, 2024

View reviewed changes

benchmark/realnest/09_array_agg.benchmark Show resolved Hide resolved

hmeriann requested review from taniabogatsch and Tmonster August 20, 2024 09:30

hmeriann marked this pull request as ready for review August 20, 2024 11:08

Tmonster reviewed Aug 21, 2024

View reviewed changes

benchmark/realnest/01_aggregate-on-one-deep-struct-members.benchmark Outdated Show resolved Hide resolved

Tmonster reviewed Aug 21, 2024

View reviewed changes

benchmark/realnest/05_list_filters.benchmark Outdated Show resolved Hide resolved

Tmonster reviewed Aug 21, 2024

View reviewed changes

benchmark/realnest/08_count_map_keys.benchmark Outdated Show resolved Hide resolved

duckdb-draftbot marked this pull request as draft August 22, 2024 07:30

hmeriann marked this pull request as ready for review August 22, 2024 07:35

hmeriann requested a review from Tmonster August 22, 2024 07:35

duckdb-draftbot marked this pull request as draft August 22, 2024 07:46

hmeriann marked this pull request as ready for review August 22, 2024 08:44

Tmonster reviewed Aug 22, 2024

View reviewed changes

benchmark/realnest/12_map_two_list_values.benchmark Outdated Show resolved Hide resolved

Tmonster reviewed Aug 22, 2024

View reviewed changes

benchmark/realnest/14_list_sort_flatten_slice.benchmark Outdated Show resolved Hide resolved

hmeriann requested a review from Tmonster August 23, 2024 12:59

duckdb-draftbot marked this pull request as draft August 23, 2024 13:23

hmeriann marked this pull request as ready for review August 27, 2024 07:48

duckdb-draftbot marked this pull request as draft August 27, 2024 08:04

hmeriann marked this pull request as ready for review August 27, 2024 09:33

taniabogatsch reviewed Aug 28, 2024

View reviewed changes

mkdir -p

2404516

duckdb-draftbot marked this pull request as draft September 13, 2024 12:11

hmeriann marked this pull request as ready for review September 13, 2024 14:43

hmeriann requested review from Tmonster and taniabogatsch September 14, 2024 10:49

Tmonster approved these changes Sep 16, 2024

View reviewed changes

hmeriann requested a review from Tmonster September 16, 2024 11:08

taniabogatsch approved these changes Sep 18, 2024

View reviewed changes

benchmark/realnest/load.sql Outdated Show resolved Hide resolved

Tishj and others added 2 commits September 19, 2024 15:54

move the definition into a header file so it can be further derived

6342cdb

Merge branch 'main' into real-nest-benchmarks

a7b52a8

duckdb-draftbot marked this pull request as draft September 23, 2024 08:06

uppercase SQL keywords in load.sql

e3d67e4

hmeriann marked this pull request as ready for review September 23, 2024 08:08

taniabogatsch approved these changes Sep 23, 2024

View reviewed changes

Mytherin and others added 11 commits September 24, 2024 13:30

Fix duckdb#14077: correctly reset next pointer when reconstructing ne…

c043195

…w row group segment tree after vacuum

No print

4814d33

Write bytes_read

2bb4328

added the answer

0c9699c

CSV sniffer: Add spaces to make the error message consistent

67881c7

CSV sniffer: Add more separators to error messages

ffcc413

[Dev] Move EnumTypeInfoTemplated definition into a hpp file (duck…

4a55e23

…db#14038) To further override `EnumTypeInfoTemplated` it needs to be either vendored (bad) or we move it to a header. Relevant `pg_duckdb` PR: <duckdb/pg_duckdb#193>

Fix duckdb#14077: correctly reset next pointer when reconstructing ne…

12e9777

…w row group segment tree after vacuum (duckdb#14092) Fixes duckdb#14077

Fix the answer file for tpcds q67 at sf100 (duckdb#14096)

b5743d0

Seems like some `NULL` values snuck in there. This was causing issues on the weekly regression runner

Merge branch 'main' into real-nest-benchmarks

2f89024

duckdb-draftbot marked this pull request as draft September 25, 2024 10:31

hmeriann marked this pull request as ready for review September 25, 2024 10:32

hannes changed the base branch from main to feature October 2, 2024 10:29

hannes merged commit 36f3716 into duckdb:feature Oct 2, 2024
42 checks passed

add some RealNest benchmarks #13345

add some RealNest benchmarks #13345

Uh oh!

Conversation

hmeriann commented Aug 7, 2024 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

taniabogatsch left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Tmonster left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Tmonster left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

taniabogatsch left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Tmonster left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

hmeriann commented Aug 22, 2024

Uh oh!

Uh oh!

Uh oh!

taniabogatsch left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Tmonster left a comment

Choose a reason for hiding this comment

Uh oh!

taniabogatsch left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

hannes commented Oct 2, 2024

Uh oh!

Uh oh!

hmeriann commented Aug 7, 2024 •

edited

Loading