ibis
ibis copied to clipboard
ibis-project
perf(expressions): speed up `.describe()` and `.info()` expression construction
Speed up .info() and .describe() methods. This approach builds an array containing each aggregate, and then jointly unnests them in a single select. This safely bypasses the validation that happens in .agg method, and only requires a single .agg call at the end, to unnest the built arrays.
Closes #9639.
- Does this mean that backends that don't support arrays won't support
.describe()/.info()anymore? - Looking at the benchmark in https://github.com/ibis-project/ibis/issues/9639#issuecomment-2246906904, while expression construction time isn't cheap, the execution time dominates by-far (so much so that for larger datasets duckdb just errors out). Does this new formulation perform better?
- Thinking about how the backend would actually implement this thing, I wonder if we should switch to using approximate quantiles instead (for backends that support them). Exact quantile computations can be expensive if the data isn't pre-sorted, while approximate quantiles can run in fixed memory.
Does this mean that backends that don't support arrays won't support .describe()/.info() anymore?
Nope, I'm working on an improvement that will be both faster than the array optimization for expression construction and continue to support the backends that don't support arrays.
Does this new formulation perform better?
Much better, but I haven't quantified it yet, just ran it locally on the original case of 1.5m rows and 450 columns.
Thinking about how the backend would actually implement this thing, I wonder if we should switch to using approximate quantiles instead (for backends that support them). Exact quantile computations can be expensive if the data isn't pre-sorted, while approximate quantiles can run in fixed memory.
Seems like a solid follow up!
@jcrist Here are the benchmarks comparing main to this PR:
--------------------------------------------------------------------------- benchmark 'test_summarize_compile[describe]': 2 tests ----------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_compile[describe] (0001_c8fbfe2) 2.7835 (6.41) 2.9301 (5.39) 2.8846 (5.74) 0.0615 (1.49) 2.9105 (5.70) 0.0806 (1.99) 1;0 0.3467 (0.17) 5 1
test_summarize_compile[describe] (NOW) 0.4340 (1.0) 0.5439 (1.0) 0.5026 (1.0) 0.0413 (1.0) 0.5105 (1.0) 0.0405 (1.0) 2;0 1.9898 (1.0) 5 1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------- benchmark 'test_summarize_compile[info]': 2 tests ----------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_compile[info] (0001_c8fbfe2) 4.7146 (10.65) 4.7415 (8.53) 4.7302 (9.65) 0.0115 (1.0) 4.7347 (10.33) 0.0192 (1.0) 1;0 0.2114 (0.10) 5 1
test_summarize_compile[info] (NOW) 0.4425 (1.0) 0.5558 (1.0) 0.4899 (1.0) 0.0534 (4.65) 0.4584 (1.0) 0.0932 (4.86) 1;0 2.0410 (1.0) 5 1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------- benchmark 'test_summarize_construct[describe]': 2 tests --------------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_construct[describe] (0001_c8fbfe2) 13.9033 (>1000.0) 14.0586 (>1000.0) 13.9981 (>1000.0) 0.0640 (>1000.0) 13.9973 (>1000.0) 0.0982 (>1000.0) 1;0 0.0714 (0.00) 5 1
test_summarize_construct[describe] (NOW) 0.0001 (1.0) 0.0001 (1.0) 0.0001 (1.0) 0.0000 (1.0) 0.0001 (1.0) 0.0000 (1.0) 283;318 18,308.8220 (1.0) 3669 1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------ benchmark 'test_summarize_construct[info]': 2 tests ------------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_construct[info] (0001_c8fbfe2) 5.3707 (>1000.0) 5.3992 (>1000.0) 5.3845 (>1000.0) 0.0123 (367.49) 5.3844 (>1000.0) 0.0219 (>1000.0) 2;0 0.1857 (0.00) 5 1
test_summarize_construct[info] (NOW) 0.0000 (1.0) 0.0008 (1.0) 0.0001 (1.0) 0.0000 (1.0) 0.0000 (1.0) 0.0000 (1.0) 609;933 19,765.8752 (1.0) 6988 1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------- benchmark 'test_summarize_execute[describe]': 2 tests ----------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_execute[describe] (0001_c8fbfe2) 5.5988 (1.0) 5.6951 (1.0) 5.6457 (1.0) 0.0359 (1.0) 5.6494 (1.0) 0.0471 (1.0) 2;0 0.1771 (1.0) 5 1
test_summarize_execute[describe] (NOW) 7.6022 (1.36) 8.0862 (1.42) 7.9262 (1.40) 0.1954 (5.45) 7.9952 (1.42) 0.2417 (5.13) 1;0 0.1262 (0.71) 5 1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------- benchmark 'test_summarize_execute[info]': 2 tests ----------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_execute[info] (0001_c8fbfe2) 7.2877 (4.19) 7.4595 (2.04) 7.3480 (2.55) 0.0677 (1.0) 7.3166 (2.42) 0.0780 (1.0) 1;0 0.1361 (0.39) 5 1
test_summarize_execute[info] (NOW) 1.7379 (1.0) 3.6651 (1.0) 2.8830 (1.0) 0.7073 (10.44) 3.0200 (1.0) 0.6771 (8.68) 2;0 0.3469 (1.0) 5 1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The describe execution being 36% slower is pretty curious but the others are all expected.
Now to see about failing tests ...
Ah, the slowdown is because I changed the implementation to always produce all aggregates!
Found some more performance laying around in the form of not converting all memtables to pyarrow (which is noticeably expensive for wide tables). I'll put that in a separate PR.
Latest benchmarks show improvements across the board for describe() and info():
--------------------------------------------------------------------------- benchmark 'test_summarize_compile[describe]': 2 tests ----------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_compile[describe] (0001_915a8cb) 3.0292 (6.91) 3.1847 (5.92) 3.0916 (5.99) 0.0648 (1.49) 3.1043 (5.82) 0.0982 (3.50) 1;0 0.3235 (0.17) 5 1
test_summarize_compile[describe] (NOW) 0.4384 (1.0) 0.5383 (1.0) 0.5160 (1.0) 0.0434 (1.0) 0.5336 (1.0) 0.0281 (1.0) 1;1 1.9379 (1.0) 5 1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------- benchmark 'test_summarize_compile[info]': 2 tests ----------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_compile[info] (0001_915a8cb) 4.6372 (10.16) 4.7635 (8.36) 4.7202 (9.08) 0.0514 (1.0) 4.7199 (8.67) 0.0656 (1.0) 1;0 0.2119 (0.11) 5 1
test_summarize_compile[info] (NOW) 0.4565 (1.0) 0.5700 (1.0) 0.5198 (1.0) 0.0549 (1.07) 0.5442 (1.0) 0.1024 (1.56) 2;0 1.9239 (1.0) 5 1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------- benchmark 'test_summarize_construct[describe]': 2 tests --------------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_construct[describe] (0001_915a8cb) 13.9248 (>1000.0) 14.1328 (>1000.0) 13.9937 (>1000.0) 0.0836 (>1000.0) 13.9702 (>1000.0) 0.1021 (>1000.0) 1;0 0.0715 (0.00) 5 1
test_summarize_construct[describe] (NOW) 0.0001 (1.0) 0.0003 (1.0) 0.0001 (1.0) 0.0000 (1.0) 0.0001 (1.0) 0.0000 (1.0) 470;805 17,870.8609 (1.0) 5796 1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------ benchmark 'test_summarize_construct[info]': 2 tests ------------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_construct[info] (0001_915a8cb) 5.3645 (>1000.0) 5.5276 (>1000.0) 5.4284 (>1000.0) 0.0615 (>1000.0) 5.4068 (>1000.0) 0.0679 (>1000.0) 2;0 0.1842 (0.00) 5 1
test_summarize_construct[info] (NOW) 0.0000 (1.0) 0.0002 (1.0) 0.0000 (1.0) 0.0000 (1.0) 0.0000 (1.0) 0.0000 (1.0) 306;418 28,183.6391 (1.0) 4585 1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------- benchmark 'test_summarize_execute[describe]': 2 tests ----------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_execute[describe] (0001_915a8cb) 5.5646 (4.85) 5.9250 (4.59) 5.7279 (4.67) 0.1301 (2.08) 5.7228 (4.62) 0.1345 (1.23) 2;0 0.1746 (0.21) 5 1
test_summarize_execute[describe] (NOW) 1.1464 (1.0) 1.2922 (1.0) 1.2262 (1.0) 0.0625 (1.0) 1.2383 (1.0) 0.1098 (1.0) 2;0 0.8156 (1.0) 5 1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------- benchmark 'test_summarize_execute[info]': 2 tests ----------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_execute[info] (0001_915a8cb) 7.2768 (11.98) 7.6663 (10.25) 7.4856 (11.56) 0.1386 (2.39) 7.4888 (12.10) 0.1152 (2.08) 2;0 0.1336 (0.09) 5 1
test_summarize_execute[info] (NOW) 0.6073 (1.0) 0.7482 (1.0) 0.6473 (1.0) 0.0581 (1.0) 0.6188 (1.0) 0.0554 (1.0) 1;0 1.5449 (1.0) 5 1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Going to benchmark this with pyarrow to properly measure the speedup of these ops without the overhead of pandas memtable conversion
Added a benchmark for "end-to-end" which corresponds to construction + compilation + execution.
Table.describe()
- End-to-end is faster by about ~7x.
- Construction is faster by >1000x because we're not spelling the operation in Ibis anymore, and the schema is static so it's extremely cheap to construct the expression.
- Compilation is faster by ~5x because we're constructing sqlglot expressions directly instead of going through the compiler loop.
- Execution is just under ~3x faster in some cases due to use of ungrouped aggregates + unnest.
Table.info()
- End-to-end is faster by about ~13x.
-
Construction is faster by >1000x for the same reasons as
Table.describe(). -
Compilation is faster by ~10x for the same reasons as
Table.describe(). The additional 2x is probably becauseinfohas fewer aggregates to compile. - Execution is a bit shy of ~9x faster in some cases.
Benchmarks:
--------------------------------------------------------------------------- benchmark 'test_summarize_compile[describe]': 2 tests ----------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_compile[describe] (0003_561f8b5) 2.6513 (5.51) 2.7888 (5.45) 2.7161 (5.45) 0.0495 (3.48) 2.7185 (5.36) 0.0506 (2.04) 2;0 0.3682 (0.18) 5 1
test_summarize_compile[describe] (NOW) 0.4812 (1.0) 0.5115 (1.0) 0.4986 (1.0) 0.0142 (1.0) 0.5069 (1.0) 0.0248 (1.0) 1;0 2.0057 (1.0) 5 1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------- benchmark 'test_summarize_compile[info]': 2 tests ----------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_compile[info] (0003_561f8b5) 4.4634 (10.30) 4.6225 (8.72) 4.5289 (9.28) 0.0801 (1.74) 4.4844 (8.75) 0.1484 (1.80) 2;0 0.2208 (0.11) 5 1
test_summarize_compile[info] (NOW) 0.4333 (1.0) 0.5300 (1.0) 0.4880 (1.0) 0.0460 (1.0) 0.5127 (1.0) 0.0826 (1.0) 1;0 2.0490 (1.0) 5 1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------- benchmark 'test_summarize_construct[describe]': 2 tests --------------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_construct[describe] (0003_561f8b5) 13.4096 (>1000.0) 13.5585 (>1000.0) 13.4821 (>1000.0) 0.0579 (>1000.0) 13.4813 (>1000.0) 0.0882 (>1000.0) 2;0 0.0742 (0.00) 5 1
test_summarize_construct[describe] (NOW) 0.0001 (1.0) 0.0001 (1.0) 0.0001 (1.0) 0.0000 (1.0) 0.0001 (1.0) 0.0000 (1.0) 536;277 17,818.4959 (1.0) 4601 1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------ benchmark 'test_summarize_construct[info]': 2 tests ------------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_construct[info] (0003_561f8b5) 5.0067 (>1000.0) 5.2422 (>1000.0) 5.1017 (>1000.0) 0.0872 (>1000.0) 5.0948 (>1000.0) 0.0895 (>1000.0) 2;0 0.1960 (0.00) 5 1
test_summarize_construct[info] (NOW) 0.0000 (1.0) 0.0002 (1.0) 0.0000 (1.0) 0.0000 (1.0) 0.0000 (1.0) 0.0000 (1.0) 540;717 26,613.5042 (1.0) 10123 1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------ benchmark 'test_summarize_end_to_end[describe]': 2 tests -----------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_end_to_end[describe] (0003_561f8b5) 18.8347 (6.94) 19.0805 (5.54) 18.9656 (6.04) 0.0937 (1.0) 18.9437 (5.88) 0.1250 (1.0) 2;0 0.0527 (0.17) 5 1
test_summarize_end_to_end[describe] (NOW) 2.7131 (1.0) 3.4470 (1.0) 3.1411 (1.0) 0.2699 (2.88) 3.2204 (1.0) 0.2748 (2.20) 2;0 0.3184 (1.0) 5 1
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------ benchmark 'test_summarize_end_to_end[info]': 2 tests -----------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_end_to_end[info] (0003_561f8b5) 12.2544 (12.94) 12.4415 (7.72) 12.3425 (10.11) 0.0677 (1.0) 12.3463 (10.67) 0.0737 (1.0) 2;0 0.0810 (0.10) 5 1
test_summarize_end_to_end[info] (NOW) 0.9472 (1.0) 1.6122 (1.0) 1.2204 (1.0) 0.2442 (3.61) 1.1574 (1.0) 0.2401 (3.26) 2;0 0.8194 (1.0) 5 1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------- benchmark 'test_summarize_execute[describe]': 2 tests ----------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_execute[describe] (0003_561f8b5) 5.4739 (2.91) 5.6354 (1.66) 5.5332 (1.92) 0.0660 (1.0) 5.5400 (1.87) 0.0897 (1.0) 1;0 0.1807 (0.52) 5 1
test_summarize_execute[describe] (NOW) 1.8818 (1.0) 3.3976 (1.0) 2.8843 (1.0) 0.6026 (9.14) 2.9643 (1.0) 0.7045 (7.86) 1;0 0.3467 (1.0) 5 1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------- benchmark 'test_summarize_execute[info]': 2 tests ----------------------------------------------------------------------------
Name (time in s) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_summarize_execute[info] (0003_561f8b5) 7.1148 (8.72) 7.2397 (4.53) 7.1821 (5.86) 0.0505 (1.0) 7.2042 (5.67) 0.0762 (1.0) 2;0 0.1392 (0.17) 5 1
test_summarize_execute[info] (NOW) 0.8158 (1.0) 1.5973 (1.0) 1.2250 (1.0) 0.2921 (5.78) 1.2697 (1.0) 0.3888 (5.11) 2;0 0.8163 (1.0) 5 1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This is turning out to be kind of a mess. I'm having to move the translation down a layer in some cases which is getting annoying. I'll take the weekend to mull it over.
I would like to get this into 9.3, but I'm not sure it'll happen. Just need to grind on it some more.