Hi Chenhui Mo,
It's been a while since you posted the patch but it still applies
cleanly on latest master.
> While profiling the execution of MAX(), MIN(), SUM(), AVG(), and
> VARIANCE() on numeric columns, I noticed that a significant amount of
> CPU time is spent on heap allocations and varlena detoasting.
> Specifically, PG_GETARG_NUMERIC() unconditionally detoasts
> short-header datums, which incurs continuous palloc/memcpy overhead in
> tight aggregation loops.
>
Yes, the code is badly optimized and there's lots of potential to make
it faster.
But it's not only the functions you mentioned but pretty much all code.
While I think it's a good idea to improve NUMERIC performance, I don't
think it's
a good idea to only do it for a narrow subset of the functionality.
Especially, as
you're introducing new custom code that is only used by these functions
and makes
them work differently than the rest of numeric.c.
cmp_numerics_packed() now duplicates cmp_numerics() and on top can no
longer use
the existing helper functions / macros such as NUMERIC_IS_NAN(), etc. I
think we
should instead tackle the problem in a way that all the other code can
leverage the
optimizations as well - with minor but similar/identical changes.
I don't know if that can truly work for all code. Probably there's some
custom code
in certain places needed but a big portion should be able to profit from
a more
generalized abstraction of "being able to work with packed values".
Changing all code at once is likely too big of a patch. What we can do
instead is
introduce new helpers that eventually can be used by all code. And then
migrate the
functionality step by step to the new helpers and eventually remove the
old ones.
> Here are the benchmark results executing on 20M rows using different
> precisions (NUMERIC(18,2), NUMERIC(38,2), NUMERIC(9,2)):
>
I took your queries and put some scripting around them to more easily
run and compare
optimizations. We can also extend the query basket as we go to cover
more and more
functionality that we optimize. The script does a "best of three runs"
for each query.
Use the scripts as:
SET max_parallel_workers_per_gather = 0
\i benchmark_queries.sql -- run once to load the benchmark function
\i benchmark_queries_run.sql
My results with a 9700X CPU are attached.
The total runtime dropped from 87,271 ms to 74,480 ms. Nice!
--
David Geier
