Re: In PG12, query with float calculations is slower than PG11

On Sat, Feb 8, 2020 at 3:13 AM Andres Freund <andres(at)anarazel(dot)de> wrote:
> On 2020-02-07 17:17:21 +0900, Amit Langote wrote:
> > I did some tests using two relatively recent compilers: gcc 8 and
> > clang-7 and here are the results:
>
> Hm, these very much look like they've been done in an unoptimized build?
>
> > 40.62% postgres postgres [.] ExecInterpExpr
> > 9.74% postgres postgres [.] float8_accum
> > 6.12% postgres libc-2.17.so [.] __isinf
> > 5.96% postgres postgres [.] float8mul
> > 5.33% postgres postgres [.] dsqrt
> > 3.90% postgres postgres [.] ftod
> > 3.53% postgres postgres [.] Float8GetDatum
> > 2.34% postgres postgres [.] DatumGetFloat8
> > 2.15% postgres postgres [.] AggCheckCallContext
> > 2.03% postgres postgres [.] slot_deform_tuple
> > 1.95% postgres libm-2.17.so [.] __sqrt
> > 1.19% postgres postgres [.] check_float8_array
>
> > HEAD
> >
> > latency average = 549.071 ms
> >
> > 31.74% postgres postgres [.] ExecInterpExpr
> > 11.02% postgres libc-2.17.so [.] __isinf
> > 10.58% postgres postgres [.] float8_accum
> > 4.84% postgres postgres [.] check_float8_val
> > 4.66% postgres postgres [.] dsqrt
> > 3.91% postgres postgres [.] float8mul
> > 3.56% postgres postgres [.] ftod
> > 3.26% postgres postgres [.] Float8GetDatum
> > 2.91% postgres postgres [.] float8_mul
> > 2.30% postgres postgres [.] DatumGetFloat8
> > 2.19% postgres postgres [.] slot_deform_heap_tuple
> > 1.81% postgres postgres [.] AggCheckCallContext
> > 1.31% postgres libm-2.17.so [.] __sqrt
> > 1.25% postgres postgres [.] check_float8_array
>
> Because DatumGetFloat8, Float8GetDatum, etc aren't functions that
> normally stay separate.

Okay, fair.

Here are numbers after compiling with -O3:

gcc 8
=====

HEAD

latency average = 350.187 ms

34.67% postgres postgres [.] ExecInterpExpr
20.94% postgres libc-2.17.so [.] __isinf
10.74% postgres postgres [.] float8_accum
8.22% postgres postgres [.] dsqrt
6.63% postgres postgres [.] float8mul
3.45% postgres postgres [.] ftod
2.32% postgres postgres [.] tts_buffer_heap_getsomeattrs

HEAD + reverse-if-condition patch

latency average = 346.710 ms

34.48% postgres postgres [.] ExecInterpExpr
21.00% postgres libc-2.17.so [.] __isinf
12.26% postgres postgres [.] float8_accum
8.31% postgres postgres [.] dsqrt
6.32% postgres postgres [.] float8mul
3.23% postgres postgres [.] ftod
2.25% postgres postgres [.] tts_buffer_heap_getsomeattrs

HEAD + revert-to-macro patch

latency average = 297.493 ms

39.25% postgres postgres [.] ExecInterpExpr
14.44% postgres postgres [.] float8_accum
11.02% postgres libc-2.17.so [.] __isinf
8.21% postgres postgres [.] dsqrt
5.55% postgres postgres [.] float8mul
4.15% postgres postgres [.] ftod
2.78% postgres postgres [.] tts_buffer_heap_getsomeattrs

11.6

latency average = 290.301 ms

42.78% postgres postgres [.] ExecInterpExpr
12.27% postgres postgres [.] float8_accum
12.12% postgres libc-2.17.so [.] __isinf
8.96% postgres postgres [.] dsqrt
5.77% postgres postgres [.] float8mul
3.94% postgres postgres [.] ftod
2.61% postgres postgres [.] AggCheckCallContext

clang-7
=======

HEAD

latency average = 246.278 ms

44.47% postgres postgres [.] ExecInterpExpr
14.56% postgres postgres [.] float8_accum
7.25% postgres postgres [.] float8mul
7.22% postgres postgres [.] dsqrt
5.40% postgres postgres [.] ftod
4.09% postgres postgres [.] tts_buffer_heap_getsomeattrs
2.20% postgres postgres [.] check_float8_val

HEAD + reverse-if-condition patch

latency average = 240.212 ms

45.49% postgres postgres [.] ExecInterpExpr
13.69% postgres postgres [.] float8_accum
8.32% postgres postgres [.] dsqrt
5.28% postgres postgres [.] ftod
5.19% postgres postgres [.] float8mul
3.68% postgres postgres [.] tts_buffer_heap_getsomeattrs
2.90% postgres postgres [.] float8_mul

HEAD + revert-to-macro patch

latency average = 240.620 ms

44.04% postgres postgres [.] ExecInterpExpr
13.72% postgres postgres [.] float8_accum
9.26% postgres postgres [.] dsqrt
5.30% postgres postgres [.] ftod
4.66% postgres postgres [.] float8mul
3.53% postgres postgres [.] tts_buffer_heap_getsomeattrs
3.39% postgres postgres [.] float8_mul

11.6

latency average = 237.045 ms

46.85% postgres postgres [.] ExecInterpExpr
11.39% postgres postgres [.] float8_accum
8.02% postgres postgres [.] dsqrt
7.29% postgres postgres [.] slot_deform_tuple
6.04% postgres postgres [.] float8mul
5.49% postgres postgres [.] ftod

PG 12 is worse than PG 11 when compiled with gcc.

Thanks,
Amit