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

Thank you very much everyone.

Improvement was confirmed even if PG12_STABLE was built with gcc 4.8.5.

* PG_12_STABLE
* gcc 4.8.5

postgres=# EXPLAIN (ANALYZE on, VERBOSE on, BUFFERS on)
 select (2 * a) , (2 * b) , (2 * c), (2 * d),  (2 * e)
 from realtest;

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.realtest  (cost=0.00..288692.14 rows=9999873 width=40)
(actual time=0.012..4118.432 rows=10000001 loops=1)
   Output: ('2'::double precision * a), ('2'::double precision * b),
('2'::double precision * c), ('2'::double precision * d), ('2'::double
precision * e)
   Buffers: shared hit=63695
 Planning Time: 0.034 ms
 Execution Time: 4811.957 ms
(5 rows)

  32.03%  postgres  postgres           [.] ExecInterpExpr
  12.28%  postgres  postgres           [.] float84mul
   9.62%  postgres  [vdso]             [.] __vdso_clock_gettime
   6.45%  postgres  libc-2.17.so       [.] __isinf
   5.15%  postgres  postgres           [.] tts_buffer_heap_getsomeattrs
   3.83%  postgres  postgres           [.] ExecScan

Best Regards,
Keisuke Kuroda

2020年2月14日(金) 13:29 Amit Langote <amitlangote09@gmail.com>:

> On Fri, Feb 14, 2020 at 3:47 AM Andres Freund <andres@anarazel.de> wrote:
> > On 2020-02-13 13:40:43 -0500, Tom Lane wrote:
> > > ... and pushed.  One other change I made beyond those suggested
> > > was to push the zero-divide ereport's out-of-line as well.
> >
> > Thanks!
>
> Thank you all.
>
> I repeated some of the tests I did earlier and things look good.
>
> gcc-8
> =====
>
> HEAD
>
> latency average = 296.842 ms
>
>     42.05%  postgres  postgres           [.] ExecInterpExpr
>     15.14%  postgres  postgres           [.] float8_accum
>      9.32%  postgres  libc-2.17.so       [.] __isinf
>      7.32%  postgres  postgres           [.] dsqrt
>      5.67%  postgres  postgres           [.] float8mul
>      4.20%  postgres  postgres           [.] ftod
>
> 11.7
>
> latency average = 289.439 ms
>
>     41.52%  postgres  postgres           [.] ExecInterpExpr
>     13.59%  postgres  libc-2.17.so       [.] __isinf
>     10.98%  postgres  postgres           [.] float8_accum
>      8.26%  postgres  postgres           [.] dsqrt
>      6.17%  postgres  postgres           [.] float8mul
>      3.65%  postgres  postgres           [.] ftod
>
> clang-7
> =======
>
> HEAD
>
> latency average = 233.735 ms
>
>     43.84%  postgres  postgres           [.] ExecInterpExpr
>     15.17%  postgres  postgres           [.] float8_accum
>      8.25%  postgres  postgres           [.] dsqrt
>      7.35%  postgres  postgres           [.] float8mul
>      5.84%  postgres  postgres           [.] ftod
>      3.78%  postgres  postgres           [.] tts_buffer_heap_getsomeattrs
>
> 11.7
>
> latency average = 221.009 ms
>
>     49.55%  postgres  postgres           [.] ExecInterpExpr
>     12.05%  postgres  postgres           [.] float8_accum
>      8.97%  postgres  postgres           [.] dsqrt
>      6.72%  postgres  postgres           [.] float8mul
>      5.62%  postgres  postgres           [.] ftod
>      2.18%  postgres  postgres           [.] slot_deform_tuple
>
> HEAD and PG 11 are now comparable even when built with gcc.
>
> Regards,
> Amit
>