From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Roberto Mello <roberto(dot)mello(at)gmail(dot)com> |
Cc: | Kevin Grittner <kgrittn(at)ymail(dot)com>, testman1316 <danilo(dot)ramirez(at)hmhco(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong? |
Date: | 2014-08-06 19:41:27 |
Message-ID: | CAFj8pRBTZErJz3RyEPgAQ_Yzv0H2oeWVkiuu=rrs=Ksc3Tfbmw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi
I returned to this issue and maybe I found a root issue. It is PL/pgSQL
implicit IO cast
Original text:
postgres=# DO LANGUAGE plpgsql $$ DECLARE n real;
DECLARE f integer;
BEGIN
FOR f IN 1..10000000 LOOP
if 0=0 then
n = SQRT (f);
end if;
END LOOP;
RAISE NOTICE 'Result => %',n;
END $$;
NOTICE: Result => 3162.28
DO
Time: 31988.720 ms
Little bit modified
postgres=# DO LANGUAGE plpgsql $$ DECLARE n real;
DECLARE f integer;
BEGIN
FOR f IN 1..10000000 LOOP
if 0=0 then
n = SQRT (f)::real;
end if;
END LOOP;
RAISE NOTICE 'Result => %',n;
END $$;
NOTICE: Result => 3162.28
DO
Time: 9660.592 ms
It is 3x faster
there is invisible IO conversion from double precision::real via libc
vfprintf
https://github.com/okbob/plpgsql_check/ can raise a performance warning in
this situation, but we cannot do too much now without possible breaking
compatibility
Regards
Pavel
2014-08-05 16:02 GMT+02:00 Roberto Mello <roberto(dot)mello(at)gmail(dot)com>:
> On Tue, Aug 5, 2014 at 9:50 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> >
> > Since that is outside the loop, the difference should be nominal;
>
> Apologies. I misread on my phone and though it was within the loop.
>
> > and in a quick test it was. On the other hand, reducing the
> > procedural code made a big difference.
>
> <snip>
>
> > test=# DO LANGUAGE plpgsql $$ DECLARE n real;
> > BEGIN
> > PERFORM SQRT(f) FROM generate_series(1, 10000000) x(f);
> > END $$;
> > DO
> > Time: 3916.815 ms
>
> That is a big difference. Are you porting a lot of code from PL/SQL,
> and therefore evaluating the performance difference of running this
> code? Or is this just a general test where you wish to assess the
> performance difference?
>
> PL/pgSQL could definitely use some loving, as far as optimization
> goes, but my feeling is that it hasn't happened because there are
> other suitable backends that give the necessary flexibility for the
> different use cases.
>
> Roberto
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2014-08-06 19:46:18 | Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong? |
Previous Message | Fabien COELHO | 2014-08-06 19:22:30 | Re: add modulo (%) operator to pgbench |