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

On Sat, Feb 8, 2020 at 3:13 AM Andres Freund <andres@anarazel.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